ORACLE 1 SQLPlus Environment What is SQLPlus n

ORACLE 1

SQL*Plus Environment

What is SQL*Plus? n Oracle’s development environment n n Used to write, test and debug SQL and PL/SQL code Hasn’t changed much in 20 years 3

Command Line Editor n n list or list # change n n del n n n Example: /Featuer/Feature (can use any delimiter) del (current line only) del 3 7 (range of lines) del 2 LAST (deletes from line 2 to the end of the buffer) Do NOT use the word “delete” clear buffer: clears out the SQL statement 4

Command Line Editor n append n n Places text at the end of the current line without any spaces between the existing text & the appended text input 5

Useful SQL*Plus Commands n set headsep: identifies the character that tells SQL*Plus when to split a title or column onto 2 or more lines n Default character: | n n n n Set headsep ! ttitle: sets the title at the top of each page n n n SQL> SELECT LNAME "Last_Name|Of|Employee" will display Last_Name Of Employee -------- ttitle ‘Sales by Product During 1901!Second Six Months’ If title should display an apostrophe, then use two single quotes btitle: sets the title at the bottom of each page 6

Useful SQL*Plus Commands n column: tells SQL*Plus how to handle columns n Can be used to re-label column headings n n column Item heading ‘What Was!Sold’ Can be used to specify column format n n column Item format a 18 column Rate format 90. 99 n n n Can be used to truncate data in column n n zero tells SQL*Plus to pad the number with a zero (if necessary) 999, 999 99. 90 column Item truncated (OR column Item trunc) Can be used to wrap info column Item word_wrapped 7

Useful SQL*Plus Commands n column: tells SQL*Plus how to handle columns n Can be used to specify column format n n Alphanumeric: column Item format a 18 Numeric: column Rate format 90. 99 n n Use nines and zeros to specify the numeric pattern Examples: (a) 999, 999 (b) 99. 90 n COLUMN Salary FORMAT $999, 999. 99 n See “numeric formatting” in Oracle Complete Reference 8

Useful SQL*Plus Commands n break on: tells SQL*Plus where to break for subtotals and totals n break on Item skip 2 n n n break on Item duplicate skip 2 n n n will not repeat the value in the Item column Create one line for each unique Item value and skip 2 lines will repeat the value in the Item column must be coordinated with the order by clause break on report n tells SQL*Plus to provide a grand total for the report n Example: break on Item skip 2 on report n n break on report on Item skip 2 compute sum: tells SQL*Plus to calculate subtotals n works in conjunction with the break on command 9

Useful SQL*Plus Commands n Basic rules for computing subtotals & totals: n n n Every break on must have a related order by Consecutive break on commands will override the previous break on command To create both subtotals & totals, combine the break on instructions as follows n n n break on X skip # on report OR break on report on X skip # where X = column name & # = lines to skip between sections Every compute sum must have a related break on Clear breaks and computes before setting up new ones 10

Useful SQL*Plus Commands n set linesize n n set pagesize n n sets the maximum number of characters allowed on any line; usually 70 or 80 sets the maximum number of lines per page; usually 66 lines set newpage n sets the number of blank lines between pages 11

Useful SQL*Plus Commands n spool & spool off n n Example: spool test. sql run (/) start (@) save n n saves the SQL statements, but not the SQL*Plus commands Example: save example. sql n n (or save example. sql replace) store n n saves the current SQL*Plus environment Example: store set my_settings. sql create n (or …replace or …append) 12

Useful SQL*Plus Commands n To check the current settings n n n n n column (or column_name) ttitle break compute show headsep show linesize show pagesize show newpage 13

Useful SQL*Plus Commands n To clear the current settings n n n ttitle off btitle off clear columns clear breaks clear computes 14

Useful System Tables n User_Constraints n n n User_Cons_Columns n n n Useful fields: constraint_name, table_name, constraint_type: C, P, R & U Useful fields: constraint_name, column_name, position SELECT column_name FROM user_cons_columns WHERE constraint_name=‘SYS_C 0008791’; Retrieving constraints defined by the user WHERE CONSTRAINT_NAME NOT LIKE '%SYS%'; 15

Useful System Tables n user_sequences n n Contains sequences owned by the current user_errors n n Contains compilation errors for the current user Use the ‘show errors’ SQL*Plus command to view the errors in the user_errors table 16

What Is PL/SQL? n n n PL/SQL stands for Procedural Language operating on or using SQL Combines the flexibility of SQL (4 GL) with the power and configurability of the procedural constructs of a 3 GL Extends SQL by adding 3 GL constructs such as: n n Variables and types (predefined and user defined) Control Structures (IF-THEN-ELSE, Loops) Procedures and functions Object types and methods 17

PL/SQL Constructs n PL/SQL based on Ada language constructs n n n Block Structure Error Handling Variables and Types Conditionals Looping Constructs Cursors 18

Introduction to PL / SQL

What Is PL / SQL n n n PL/SQL stands for Procedural Language operating on or using SQL Combines power and flexibility of SQL (4 GL) with procedural constructs of a 3 GL Extends SQL by adding n n Variables and types Control Structures Procedures and functions Object types and methods 20

File 3 gl_4 gl. sql Demonstrates both SQL and PL/SQL commands DECLARE v_New. Major VARCHAR 2(10) : = 'History'; v_First. Name VARCHAR 2(10) : = 'Scott'; v_Last. Name VARCHAR 2(10) : = 'Urman'; BEGIN UPDATE students SET major = v_New. Major WHERE first_name = v_First. Name AND last_name = v_Last. Name; IF SQL%NOTFOUND THEN INSERT INTO students (ID, first_name, last_name, major) VALUES (student_sequence. NEXTVAL, v_First. Name, v_Last. Name, v_New. Major); END IF; END; / 21