COMP 101 Lecture Notes - Lecture 8: Declarative Programming, Procedural Programming, Stored Procedure
Chapter 8
Advanced SQL
Chapter 8 - Objectives
How to use the SQL programming language
How to use SQL cursors
How to create stored procedures
How to create triggers
How to use triggers to enforce integrity constraints
Chapter 8 – Objectives (continued)
The advantages and disadvantages of triggers
How to use recursive queries
The SQL Programming Language
Impedance mismatch
– Mixing different programming paradigms
– SQL is a declarative language
–High-level language such as C is a procedural language
–SQL and 3GLs use different models to represent data
The SQL Programming Language (continued)
SQL/PSM (Persistent Stored Modules)
PL/SQL (Procedural Language/SQL)
– Oracle’s procedural extension to SQL
– Two versions
Declarations
Variables and constant variables must be declared before they can be referenced
Possible to declare a variable as NOT NULL
%TYPE and %ROWTYPE
Declarations (continued)
Examples
Assignments
Variables can be assigned in three ways:
– Using the normal assignment statement (:=)
– SET
– SQL SELECT or FETCH statement
Examples
vX NUMBER;
….
vStaffNo := ‘SG14’;
vRent := 500;
SELECT COUNT(*) INTO vX
FROM PropertyForRent
WHERE staffNo = vStaffNo;
SET vStaffNo = ‘SG14’;
Control Statements
Conditional IF statement
Conditional CASE statement
Iteration statement (LOOP)
Iteration statement (WHILE and REPEAT)
Iteration statement (FOR)
IF Statement
IF ( vPosition = ‘Manager’) THEN
vSalary := vSalary * 1.05;
ELSE //optional
vSalary := vSalary * 1.08;
END IF;
CASE Statement
CASE lowercase(input)
WHEN ‘a’ THEN x := 1;
WHEN ‘b’ THEN x := 2;
y := 3;
WHEN ‘default’ THEN x := 3;
END CASE;
LOOP Statement
x := 1;
myLoop:
LOOP
x := x+1;
IF (x > 3) THEN
EXIT myLoop;
END IF;
END LOOP myLoop;
…
WHILE/REPEAT Statement
myLoop:
x := 1;
WHILE (x < 4) DO
x := x+1;
END WHILE myLoop;
myLoop:
x := 1;
REPEAT
x := x+1;
UNTIL (x>3)
END REPEAT myLoop;
FOR Statement
SELECT COUNT(*) INTO numberOfStaff
FROM ….
myLoop:
FOR iStaff IN 1..numberOfStaff LOOP
….
END LOOP myLoop;
Document Summary
How to use the sql programming language. How to use triggers to enforce integrity constraints. High-level language such as c is a procedural language. Sql and 3gls use different models to represent data. Variables and constant variables must be declared before they can be referenced. Possible to declare a variable as not null. Variables can be assigned in three ways: Vx number; vstaffno := sg14"; vrent := 500; If ( vposition = manager") then vsalary := vsalary * 1. 05; While (x < 4) do x := x+1; Raised during the execution of a block. Defined in the declarative part of a pl/sql block. Exception and completion conditions it can resolve. When it is the most appropriate handler for the condition that has been raised by the sql statement. Allows the rows of a query result to be accessed one at a time. Must be declared and opened before use.
