Isys 365 sql plus environment
This presentation is the property of its rightful owner.
Sponsored Links
1 / 53

ISYS 365 – SQL*Plus Environment PowerPoint PPT Presentation


  • 88 Views
  • Uploaded on
  • Presentation posted in: General

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

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Isys 365 sql plus environment

ISYS 365 – SQL*Plus Environment


Agenda

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

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


Command line editor

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


Command line editor1

Command Line Editor

  • append

    • Places text at the end of the current line without any spaces between the existing text & the appended text

  • input


Useful sql plus commands

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


Useful sql plus commands1

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,99999.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


Useful sql plus commands2

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


Useful sql plus commands3

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


  • Useful sql plus commands4

    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


    Useful sql plus commands5

    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


    Useful sql plus commands6

    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)


  • Useful sql plus commands7

    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


    Useful sql plus commands8

    Useful SQL*Plus Commands

    • To clear the current settings

      • ttitle off

      • btitle off

      • clear columns

      • clear breaks

      • clear computes


    Useful system tables

    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%';


    Useful system tables1

    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


    What is pl sql

    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


    Pl sql constructs

    PL/SQL Constructs

    • PL/SQL based on Ada language constructs

      • Block Structure

      • Error Handling

      • Variables and Types

      • Conditionals

      • Looping Constructs

      • Cursors


    Introduction to pl sql

    Introduction to PL / SQL

    Chapter 1


    What is pl sql1

    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


    File 3gl 4gl sql demonstrates both sql and pl sql commands

    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;

    /


    Client server model

    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


    Features of pl sql

    Features of PL / SQL

    • Block Structure

    • Error Handling

    • Variables and Types

    • Looping Constructs

    • Cursors


    Features of pl sql block structure

    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


    Features of pl sql error handling

    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


    File error sql illustrates an exception handler

    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)


    File error sql illustrates an exception handler1

    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;

    /


    Features of pl sql variables and types

    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


    Features of pl sql looping constructs

    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


    File simpleloop sql demonstrates a simple loop

    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;

    /


    File numericloop sql demonstrates a numeric for loop

    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;

    /


    Features of pl sql cursors

    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


    File cursorloop sql demonstrates a cursor fetch loop

    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;


    File conditional sql illustrates a conditional statement

    File Conditional.sqlIllustrates a conditional statement

    DECLARE

    v_TotalStudents NUMBER;

    BEGIN

    SELECT COUNT(*)

    INTO v_TotalStudents

    FROM students;

    -- (continued)


    File conditional sql illustrates a conditional statement1

    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;

    /


    File printstudents sql illustrates a stored procedure

    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;

    /


    File printstudents sql illustrates a stored procedure1

    File PrintStudents.sqlIllustrates a stored procedure

    BEGIN

    PrintStudents ('Computer Science');

    END;

    /


    Online code

    Online Code

    • All of the named examples used in the book are on the accompanying CD


    Example tables views

    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


    Example tables views1

    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


    File tables sql

    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)

    );


    Example tables classes

    Example Tablesclasses

    CREATE TABLE classes

    (

    departmentCHAR (3),

    courseNUMBER (3),

    description VARCHAR2 (2000),

    max_studentsNUMBER (3),

    current_studentsNUMBER (3),

    num_creditsNUMBER (1),

    room_idNUMBER (5),

    CONSTRAINT classes_department_course

    PRIMARY KEY (department, course),

    CONSTRAINT classes_room_id

    FOREIGN KEY (room_id) REFERENCES rooms (room_id)

    );


    Example tables debug table

    Example Tablesdebug_table

    CREATE TABLE debug_table

    (

    linecountNUMBER,

    debug_strVARCHAR2 (100)

    );


    Example tables exception view

    Example Tablesexception_view

    CREATE VIEW exception_view AS

    SELECT exception exception_description,

    date_occurred

    FROM exception_table;


    Example tables log table

    Example Tableslog_table

    CREATE TABLE log_table

    (

    codeNUMBER,

    messageVARCHAR2 (200),

    infoVARCHAR2 (100)

    );


    Example tables major stats

    Example Tablesmajor_stats

    CREATE TABLE major_stats

    (

    majorVARCHAR2 (30),

    total_creditsNUMBER,

    total_studentsNUMBER

    );


    Example tables registered students

    Example Tablesregistered_students

    CREATE TABLE registered_students

    (

    student_idNUMBER (5)NOT NULL,

    departmentCHAR (3)NOT NULL,

    courseNUMBER (3)NOT NULL,

    gradeCHAR (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)

    );


    Example tables rooms

    Example Tablesrooms

    CREATE TABLE rooms

    (

    room_idNUMBER (5)PRIMARY KEY,

    buildingVARCHAR2 (15),

    room_numberNUMBER (4),

    number_seatsNUMBER (4),

    descriptionVARCHAR2 (50)

    );


    Example tables rs audit

    Example TablesRS_audit

    CREATE TABLE RS_audit

    (

    change_typeCHAR (1)NOT NULL,

    changed_byVARCHAR2 (8)NOT NULL,

    timestampDATENOT NULL,

    old_student_idNUMBER (5),

    old_departmentCHAR (3),

    old_courseNUMBER (3),

    old_gradeCHAR (1),

    new_student_idNUMBER (5),

    new_departmentCHAR (3),

    new_courseNUMBER (3),

    new_gradeCHAR (1)

    );


    Example tables student sequence

    Example Tablesstudent_sequence

    CREATE SEQUENCE student_sequence

    START WITH 10000

    INCREMENT BY 1;


    Example tables students

    Example Tablesstudents

    CREATE TABLE students

    (

    id NUMBER(5) PRIMARY KEY,

    first_name VARCHAR2 (20),

    last_name VARCHAR2 (20),

    major VARCHAR2 (30),

    current_credits NUMBER(3)

    );


    Example tables temp table

    Example Tablestemp_table

    CREATE TABLE temp_table

    (

    num_colNUMBER,

    char_colVARCHAR2 (60)

    );


    In conclusion

    In Conclusion

    • PL/SQL is a sophisticated programming language used to access an Oracle database

    • Procedural constructs are integrated seamlessly with SQL, resulting in a structured, powerful language

    • Combines flexibility of SQL with the configure ability of a 3GL


  • Login