Transcript PL/SQL - Database Principles | IT222 & CAP261

PL / SQL
Procedural Language / Structured Query Language
Chapter 7 in Lab Reference
Introduction to PL/SQL
 PL/SQL is the procedural extension to SQL.
 PL/SQL is a programming language like C, Java or Pascal, used to
access the database from various environments.
 e.g. Forms, Reports to create triggers, procedures, functions, etc…
 SQL-DML can be natively embedded in PL/SQL programs.
PL/SQL
2
Introduction to PL/SQL
 PL/SQL provides high-level language features such as block structure,
conditional statements, loop statements, variable types, structured data
and customized error handling.
 PL/SQL is integrated with the database server. It does not exist as a
standalone language.
PL/SQL
3
Basic Structure of PL/SQL
 The basic unit in PL/SQL is a block.
 All PL/SQL programs are made up of blocks, which can be nested
within each other.
PL/SQL
4
Basic Structure of PL/SQL
DECLARE
/* Declarative section: variables, types, and local subprograms. */
BEGIN
/* Executable section: procedural and SQL-DML statements go here. */
/* This section of the block is required. */
EXCEPTION
/* Exception handling section: error handling statements go here. */
END;
PL/SQL
5
Basic Structure of PL/SQL
 Only the executable section is required. The other sections are optional.
 The only SQL statements allowed in a PL/SQL program are SELECT,
INSERT, UPDATE, DELETE and several other data manipulation
statements plus some transaction control.
 Data definition statements like CREATE, ALTER, or DROP are not
allowed.
PL/SQL
6
Basic Structure of PL/SQL
 The executable section also contains constructs such as assignments,
branches, loops, procedure calls, and triggers.
 PL/SQL is not case sensitive.
 C style comments (/* ... */) may be used, and for a one line comment
precede it with (--).
PL/SQL
7
Variables and Types
 Types in PL/SQL are the same as in SQL.
 Variables can be declared in the following ways:
1.
Declare
id
name
PL/SQL
NUMBER;
VARCHAR(20);
8
Variables and Types
2.
Declare
id
emp.empno%TYPE;
name emp.ename%TYPE;
/* TableName.ColumnName%TYPE */
3.
Declare
depttuple dept%ROWTYPE;
/* depttuple will be a record that contains fields that represent the
columns in table dept. */
/* depttuple(deptno,dname,location) */
PL/SQL
9
Variables and Types
 A variable can be initialized:
Declare
a
NUMBER :=3;
 A variable can be constrained to not null:
Declare
a
NUMBER NOT NULL :=3;
a
NUMBER NOT NULL DEFAULT 3;
or
Declare
PL/SQL
10
Select Statement
 Select statement has a different form:
SELECT column INTO variables
FROM table
WHERE condition ;
 Select should return a single tuple, if several tuples are required,
use a Cursor.
PL/SQL
11
Simple Program
 Using the following table declaration:
CREATE TABLE T1(
e NUMBER,
f NUMBER
);
INSERT INTO T1 VALUES ( 1, 3 );
INSERT INTO T1 VALUES ( 2, 4 );
PL/SQL
T1
e
f
1
3
2
4
12
Simple Program
DECLARE
a
b
T1 before
NUMBER;
NUMBER;
BEGIN
SELECT e, f INTO a, b
FROM T1
WHERE e>1;
INSERT INTO T1 VALUES ( b, a );
END;
PL/SQL
e
f
1
3
2
4
T1 after
e
f
1
3
2
4
4
2
13
Control Flow in PL/SQL
 IF – THEN – END IF
 LOOP – EXIT WHEN – END LOOP
 FOR – END LOOP
 WHILE – END LOOP
PL/SQL
14
IF – THEN – END IF
1.
IF condition_1 THEN actions_1;
[ ELSE actions_last ; ]
END IF;
2.
IF condition_1 THEN actions_1;
[ ELSIF condition_2 THEN actions_2 ; ]
[ ELSIF condition_n THEN actions_n ; ]
[ ELSE actions_last ; ]
END IF;
PL/SQL
Syntax
15
IF – THEN – END IF
Example
T1 before
DECLARE
a NUMBER;
b NUMBER;
BEGIN
e
f
1
3
2
4
SELECT e, f INTO a, b FROM T1 WHERE e>1;
IF b=1 THEN INSERT INTO T1 VALUES ( b, a );
ELSE INSERT INTO T1 VALUES ( b+10, a+10 );
END IF;
END;
PL/SQL
T1 after
e
f
1
3
2
4
14
12
16
LOOP – EXIT WHEN – END LOOP
Syntax
LOOP
/* list of statements */
EXIT WHEN condition ;
/* list of statements */
END LOOP;
PL/SQL
17
LOOP – EXIT WHEN – END LOOP
Example
 Insert each of the pairs (1, 1) through (100, 100) into T1.
DECLARE
i NUMBER := 1;
BEGIN
LOOP
INSERT INTO T1 VALUES ( i, i );
i := i+1;
EXIT WHEN i>100;
END LOOP;
END;
PL/SQL
18
FOR – END LOOP
Syntax
FOR counter_variable IN [REVERSE] lower_bound .. upper_bound
LOOP
/* list of statements for FOR loop body */
END LOOP;
-- counter_variable will be declared implicitly.
PL/SQL
19
WHILE – END LOOP
Syntax
WHILE condition
LOOP
/* list of statements for WHILE loop body */
END LOOP;
PL/SQL
20
Cursors
 Cursor stands for “Current set of records”.
 A cursor is a variable that runs through the tuples of some
relation.
 By fetching into the cursor each tuple of the relation, we can
write a program to read and process the value of each such
tuple.
PL/SQL
21
Cursor Example
 For example, delete every tuple whose first component is less
than the second, and insert the reverse tuple into T1.
DECLARE
/* Output variables to hold the result of the query */
a T1.e%TYPE;
b T1.f%TYPE;
/* Cursor declaration */
CURSOR T1Cursor IS
SELECT e, f FROM T1 WHERE e < f
FOR UPDATE;
PL/SQL
T1
e
f
1
3
2
4
22
Cursor Example
BEGIN
T1 before
OPEN T1Cursor;
LOOP
/* Retrieve each row of the result of the above query into
PL/SQL variables */
FETCH T1Cursor INTO a, b;
/* If there are no more rows to fetch, exit the loop */
EXIT WHEN T1Cursor%NOTFOUND;
e
f
1
3
2
4
/* Delete the current tuple */
DELETE FROM T1 WHERE CURRENT OF T1Cursor;
/* Insert the reverse tuple */
INSERT INTO T1 VALUES ( b, a );
END LOOP;
CLOSE T1Cursor; /* Free cursor used by the query */
T1 after
e
f
3
1
4
2
END;
PL/SQL
23
Procedures Syntax
CREATE [OR REPLACE] PROCEDURE procedure_name (parameters) IS
/* No declare keyword */
local_var_declarations
BEGIN
/* list of statements for procedure body */
END procedure_name ;
 Parameters: variable_name mode data_type
 Modes: IN, OUT or INOUT.
 Types should be unconstrained, i.e. char and varchar should be used
instead of char(10) and varchar(20).
PL/SQL
24
Procedure Example
 Example:
CREATE PROCEDURE addtuple1( i IN NUMBER, j IN NUMBER ) IS
BEGIN
INSERT INTO T1 VALUES ( i, j );
END addtuple1;
 This creates the procedure, to execute it:
BEGIN
addtuple1( 99,100 );
END;
PL/SQL
25
Procedure Example
 Example:
CREATE PROCEDURE addtuple3( a IN NUMBER, b OUT NUMBER ) IS
BEGIN
b := 4;
INSERT INTO T1 VALUES ( a, b );
END addtuple3;
 This creates the procedure, to execute it:
DECLARE v NUMBER;
BEGIN
addtuple3 (10, v );
END;
PL/SQL
26
Functions Syntax
CREATE [OR REPLACE] FUNCTION function_name ( parameters )
RETURN return_type
IS
/* No declare keyword */
Local_var_declarations
BEGIN
/* list of statements for procedure body */
Return return_variable;
END function_name ;
PL/SQL
27
Procedures & Functions
 To find out what procedures and functions you have created, use the
following SQL query:
Select object_type, object_name
From user_objects
Where object_type = 'PROCEDURE' or object_type = 'FUNCTION';
 To drop a stored procedure/function:
Drop procedure procedure_name ;
Drop function function_name ;
PL/SQL
28
Error Handling
 Two types of exceptions:
 Predefined exceptions.
 User-Defined exceptions.
 Predefined exceptions:
 INVALID_CURSOR, NO_DATA_FOUND, INVALID_NUMBER,
ZERO_DIVIDE,TOO_MANY_ROWS, ROWTYPE_MISMATCH, etc.
 Exceptions must be:
 Declared in the declarative section of the block,
 Raised in the executable section,
 and Handled in the exception section.
PL/SQL
29
User Defined Exceptions
 User-defined exceptions are declared in the declarative section.
DECLARE
exception_name EXCEPTION;
-- exceptions are declared here
executable_statements
-- exceptions are raised here
BEGIN
[ EXCEPTION
exception_handling ]
-- exceptions are handled here
END;
PL/SQL
30
User Defined Exceptions
DECLARE
e_toomany EXCEPTION;
-- exception are declared here
BEGIN
IF num_students > 100 THEN
RAISE e_toomany;
-- exception is raised here
-- Any code here is not executed, if the condition is true
EXCEPTION
WHEN e_toomany THEN -- Control passes to the exception handler
...
-- Code here will be executed
END;
PL/SQL
31
Exceptions
 When an exception is raised, control immediately passes to the
exception section of the block.
 Once control passes to the exception handler, there is no way to return
to the executable section of the block.
 Predefined exceptions are automatically raised when the associated
error occurs.
PL/SQL
32
Handling Exceptions
….
EXCEPTION
WHEN exception_name1 THEN
Statements1;
-- do something here
WHEN exception_name2 THEN
Statements2;
WHEN OTHERS THEN
Statements3;
END;
PL/SQL
33
Handling Exceptions
 A single handler can also be executed for more than one
exception:
….
EXCEPTION
WHEN NO_DATA_FOUND
OR
TOO_MANY_ROWS
THEN
INSERT INTO log_table (info) VALUES ( 'A select error occurred.‘ );
END;
PL/SQL
34
Triggers
 A trigger defines an action the database should take when some
database-related event occurs (events such as inserts, updates, deletes).
 Triggers are similar to procedures, in that they are named PL/SQL blocks.
 Differences between Procedures and Triggers:
 A procedure is executed explicitly from another block via a procedure call with
passing arguments,
 while a trigger is executed (or fired) implicitly whenever the triggering event
happens (DML: INSERT, UPDATE, or DELETE), and a trigger doesn't accept
arguments.
PL/SQL
35
Basic Trigger Syntax
CREATE [OR REPLACE] TRIGGER trigger_name
{ BEFORE | AFTER | INSTEAD OF }
{ INSERT [OR] | DELETE [OR] | UPDATE }
[ OF column_name ] ON table_name
[ REFERENCING
[ NEW AS new_row_name ] [ OLD AS old_row_name ] ]
[ FOR EACH ROW [ WHEN ( trigger_condition ) ] ]
BEGIN
/* list of statements for trigger body */
END trigger_name ;
PL/SQL
36
Basic Trigger Syntax
 You may specify up to three triggering events using the keyword OR.
 Furthermore, UPDATE can be optionally followed by the keyword OF and a list
of attribute(s) in <table_name>. If present, the OF clause defines the event to
be only an update of the attribute(s) listed after OF. For example:
... INSERT ON R ...
... INSERT OR DELETE OR UPDATE ON R
... UPDATE OF A, B OR INSERT ON R ...
 If FOR EACH ROW option is specified, the trigger is row-level; otherwise, the
trigger is statement-level.
PL/SQL
37
Basic Trigger Syntax
 Only for row-level triggers:
 The special variables NEW and OLD are available to refer to new and old tuples
respectively. Note: In the trigger body, NEW and OLD must be preceded by a
colon (":"), but in the WHEN clause, they do not have a preceding colon!
 The REFERENCING clause can be used to assign aliases to the variables NEW
and OLD.
 A trigger restriction can be specified in the WHEN clause, enclosed by
parentheses. The trigger restriction is a SQL condition that must be satisfied in
order for Oracle to fire the trigger. This condition cannot contain subqueries.
Without the WHEN clause, the trigger is fired for each row.
PL/SQL
38
Trigger Example
CREATE TABLE T4 ( a INTEGER, b CHAR(10) );
CREATE TABLE T5 ( c CHAR(10), d INTEGER );
 We create a trigger that may insert a tuple into T5 when a tuple is inserted into T4.
Specifically, the trigger checks whether the new tuple has a first component 10 or
less, and if so inserts the reverse tuple into T5:
CREATE TRIGGER trig1
AFTER INSERT ON T4
REFERENCING NEW AS newRow
FOR EACH ROW
WHEN ( newRow.a <= 10 )
BEGIN
INSERT INTO T5 VALUES ( :newRow.b, :newRow.a );
END trig1;
PL/SQL
39