isys 365 sql plus environment
Download
Skip this Video
Download Presentation
ISYS 365 – SQL*Plus Environment

Loading in 2 Seconds...

play fullscreen
1 / 53

ISYS 365 – SQL*Plus Environment - PowerPoint PPT Presentation


  • 131 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' ISYS 365 – SQL*Plus Environment' - echo-hobbs


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

(

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)

);

example tables debug table
Example Tablesdebug_table

CREATE TABLE debug_table

(

linecount NUMBER,

debug_str VARCHAR2 (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

(

code NUMBER,

message VARCHAR2 (200),

info VARCHAR2 (100)

);

example tables major stats
Example Tablesmajor_stats

CREATE TABLE major_stats

(

major VARCHAR2 (30),

total_credits NUMBER,

total_students NUMBER

);

example tables registered students
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)

);

example tables rooms
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)

);

example tables rs audit
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)

);

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_col NUMBER,

char_col VARCHAR2 (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
ad