1 / 53

ISYS 365 – SQL*Plus Environment

ISYS 365 – SQL*Plus Environment. Agenda. What is SQL*Plus? Command Line Editor Useful SQL*Plus Commands Useful System Tables What is PL/SQL? PL/SQL Constructs. What is SQL*Plus?. Oracle’s development environment Used to write, test and debug SQL and PL/SQL code

echo-hobbs
Download Presentation

ISYS 365 – SQL*Plus Environment

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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;

More Related