ISYS 365 – SQL*Plus Environment

1. ISYS 365 – SQL*Plus Environment

2. Agenda • What is SQL*Plus? • Command Line Editor • Useful SQL*Plus Commands • Useful System Tables • What is PL/SQL? • PL/SQL Constructs

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

4. Command Line Editor • list or list # • change • Example: /Featuer/Feature (can use any delimiter) • del • 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

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

6. Useful SQL*Plus Commands • set headsep: identifies the character that tells SQL*Plus when to split a title or column onto 2 or more lines • Default character: | • SQL> SELECT LNAME "Last_Name|Of|Employee" • will display • Last_Name • Of • Employee • --------------- • Set headsep ! • ttitle: sets the title at the top of each page • 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

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

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

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

10. Useful SQL*Plus Commands • Basic rules for computing subtotals & totals: • 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 • 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

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

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

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

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

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

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

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

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

19. Introduction to PL / SQL Chapter 1

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

21. File 3gl_4gl.sqlDemonstrates both SQL and PL/SQL commands DECLARE v_NewMajor VARCHAR2(10) := 'History'; v_FirstName VARCHAR2(10) := 'Scott'; v_LastName VARCHAR2(10) := 'Urman'; BEGIN UPDATE students SET major = v_NewMajor WHERE first_name = v_FirstName AND last_name = v_LastName; IF SQL%NOTFOUND THEN INSERT INTO students (ID, first_name, last_name, major) VALUES (student_sequence.NEXTVAL, v_FirstName, v_LastName, v_NewMajor); END IF; END; /

22. Client-Server Model • SQL results in many network trips, one for each SQL statement • PL/SQL permits several SQL statements to be bundled into a single block • Results in fewer calls to database • Less network traffic • faster response time

23. Features of PL / SQL • Block Structure • Error Handling • Variables and Types • Looping Constructs • Cursors

24. Features of PL / SQLBlock Structure • Basic unit of PL/SQL is a block • Three possible sections of a block • Declarative section • Executable section • Exception handling • A block performs a logical unit of work in the program • Blocks can be nested

25. Features of PL / SQLError Handling • Exception handling section permits the user to trap and respond to run-time errors • Exceptions can be associated with • Predefined Oracle errors • User-defined errors

26. File Error.sqlIllustrates an exception handler DECLARE v_ErrorCode NUMBER; -- Code for the error v_ErrorMsg VARCHAR2(200); -- Message text for the error v_CurrentUser VARCHAR2(8); -- Current database user v_Information VARCHAR2(100); -- Information about the error BEGIN /* Code which processes some data here */ NULL; -- (continued)

27. File Error.sqlIllustrates an exception handler EXCEPTION WHEN OTHERS THEN v_ErrorCode := SQLCODE; v_ErrorMsg := SQLERRM; v_CurrentUser := USER; v_Information := 'Error encountered on ' || TO_CHAR(SYSDATE) || ' by database user ' || v_CurrentUser; INSERT INTO log_table (code, message, info) VALUES (v_ErrorCode, v_ErrorMsg, v_Information); END; /

28. Features of PL / SQLVariables and Types • A variable is a named location in memory that: • can be read from • assigned a value • Declared in the declaration section • Variables have a specific type associated with them • Can be same type as database columns

29. Features of PL / SQLLooping Constructs • A loop allows execution of a set of statements repeatedly • Types of loops • Simple loop • Numeric For loop • While loop

30. File SimpleLoop.sqlDemonstrates a simple loop DECLARE v_LoopCounter BINARY_INTEGER := 1; BEGIN LOOP INSERT INTO temp_table (num_col) VALUES (v_LoopCounter); v_LoopCounter := v_LoopCounter + 1; EXIT WHEN v_LoopCounter > 50; END LOOP; END; /

31. File NumericLoop.sqlDemonstrates a numeric FOR loop BEGIN FOR v_LoopCounter IN 1..50 LOOP INSERT INTO temp_table (num_col) VALUES (v_LoopCounter); END LOOP; END; /

32. Features of PL / SQLCursors • A cursor creates a named context area as a result of executing an associated SQL statement • Permits the program to step through the multiple rows displayed by an SQL statement

33. File CursorLoop.sqlDemonstrates a cursor fetch loop DECLARE v_FirstName VARCHAR2(20); v_LastName VARCHAR2(20); CURSOR c_Students IS SELECT first_name, last_name FROM students; BEGIN OPEN c_Students; LOOP FETCH c_Students INTO v_FirstName, v_LastName; EXIT WHEN c_Students%NOTFOUND; /* Process data here */ END LOOP; CLOSE c_Students; END;

34. File Conditional.sqlIllustrates a conditional statement DECLARE v_TotalStudents NUMBER; BEGIN SELECT COUNT(*) INTO v_TotalStudents FROM students; -- (continued)

35. File Conditional.sqlIllustrates a conditional statement IF v_TotalStudents = 0 THEN INSERT INTO temp_table (char_col) VALUES ('There are no students registered'); ELSIF v_TotalStudents < 5 THEN INSERT INTO temp_table (char_col) VALUES ('There are only a few students registered'); ELSIF v_TotalStudents < 10 THEN INSERT INTO temp_table (char_col) VALUES ('There are a little more students registered'); ELSE INSERT INTO temp_table (char_col) VALUES ('There are many students registered'); END IF; END; /

36. File PrintStudents.sqlIllustrates a stored procedure CREATE OR REPLACE PROCEDURE PrintStudents( p_Major IN students.major%TYPE) AS CURSOR c_Students IS SELECT first_name, last_name FROM students WHERE major = p_Major; BEGIN FOR v_StudentRec IN c_Students LOOP DBMS_OUTPUT.PUT_LINE(v_StudentRec.first_name || ' ' || v_StudentRec.last_name); END LOOP; END; /

37. File PrintStudents.sqlIllustrates a stored procedure BEGIN PrintStudents ('Computer Science'); END; /

38. Online Code • All of the named examples used in the book are on the accompanying CD

39. Example Tables / Views • Tables used throughout text • classes – describes the classes available for the students to take • debug_table – used during debugging sessions • log_table – records Oracle errors • major_stats – holds statistics generated about different majors • registered_students – contains information about the classes students are currently taking

40. Example Tables / Views • Tables used throughout text • rooms – holds information about the classrooms available • RS_audit – used to record changes made to registered students • student_sequence – generates unique values for the primary key of the students • students – contains information about students attending the school • temp_table – stores temporary data

41. File tables.sql PROMPT student_sequence... DROP SEQUENCE student_sequence; CREATE SEQUENCE student_sequence START WITH 10000 INCREMENT BY 1; PROMPT students table... DROP TABLE students CASCADE CONSTRAINTS; CREATE TABLE students ( id NUMBER(5) PRIMARY KEY, first_name VARCHAR2(20), last_name VARCHAR2(20), major VARCHAR2(30), current_credits NUMBER(3) ); …

42. Example Tablesclasses CREATE TABLE classes ( department CHAR (3), course NUMBER (3), description VARCHAR2 (2000), max_students NUMBER (3), current_students NUMBER (3), num_credits NUMBER (1), room_id NUMBER (5), CONSTRAINT classes_department_course PRIMARY KEY (department, course), CONSTRAINT classes_room_id FOREIGN KEY (room_id) REFERENCES rooms (room_id) );

43. Example Tablesdebug_table CREATE TABLE debug_table ( linecount NUMBER, debug_str VARCHAR2 (100) );

44. Example Tablesexception_view CREATE VIEW exception_view AS SELECT exception exception_description, date_occurred FROM exception_table;

45. Example Tableslog_table CREATE TABLE log_table ( code NUMBER, message VARCHAR2 (200), info VARCHAR2 (100) );

46. Example Tablesmajor_stats CREATE TABLE major_stats ( major VARCHAR2 (30), total_credits NUMBER, total_students NUMBER );

47. Example Tablesregistered_students CREATE TABLE registered_students ( student_id NUMBER (5) NOT NULL, department CHAR (3) NOT NULL, course NUMBER (3) NOT NULL, grade CHAR (1), CONSTRAINT rs_grade CHECK (grade IN ('A', 'B', 'C', 'D', 'E')), CONSTRAINT rs_student_id FOREIGN KEY (student_id) REFERENCES students (id), CONSTRAINT rs_department_course FOREIGN KEY (department, course) REFERENCES classes (department, course) );

48. Example Tablesrooms CREATE TABLE rooms ( room_id NUMBER (5) PRIMARY KEY, building VARCHAR2 (15), room_number NUMBER (4), number_seats NUMBER (4), description VARCHAR2 (50) );

49. Example TablesRS_audit CREATE TABLE RS_audit ( change_type CHAR (1) NOT NULL, changed_by VARCHAR2 (8) NOT NULL, timestamp DATE NOT NULL, old_student_id NUMBER (5), old_department CHAR (3), old_course NUMBER (3), old_grade CHAR (1), new_student_id NUMBER (5), new_department CHAR (3), new_course NUMBER (3), new_grade CHAR (1) );

50. Example Tablesstudent_sequence CREATE SEQUENCE student_sequence START WITH 10000 INCREMENT BY 1;