PL / SQL Procedural Language / Structured Query Language

1
PL / SQLProcedural Language / Structured Query
Language

• Chapter 7 in Lab Reference

2
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.

3
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.

4
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.

5
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

6
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.

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

8
Variables and Types

• Types in PL/SQL are the same as in SQL.
• Variables can be declared in the following ways

• Declare
• id NUMBER
• name VARCHAR(20)

1.
9
Variables and Types

• Declare
• id emp.empnoTYPE
• name emp.enameTYPE
• / TableName.ColumnNameTYPE
  /

2.

• Declare
• depttuple deptROWTYPE
• / depttuple will be a record that contains
  fields that represent the columns in table
  dept. /
• / depttuple(deptno,dname,location) /

3.
10
Variables and Types

• A variable can be initialized
• A variable can be constrained to not null

• Declare
• a NUMBER 3

• Declare
• a NUMBER NOT NULL 3

or

• Declare
• a NUMBER NOT NULL DEFAULT 3

11
Select Statement

• Select statement has a different form
• Select should return a single tuple, if several
  tuples are required, use a Cursor.

SELECT column INTO variables FROM table WHERE
condition
12
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 )
T1
e f
1 3
2 4
13
Simple Program
DECLARE     a NUMBER     b NUMBER
BEGIN     SELECT e, f INTO a, b FROM
T1 WHERE egt1     INSERT INTO T1 VALUES ( b,
a ) END
T1 before
e f
1 3
2 4
T1 after
e f
1 3
2 4
4 2
14
Control Flow in PL/SQL

• IF THEN END IF
• LOOP EXIT WHEN END LOOP
• FOR END LOOP
• WHILE END LOOP

15
IF THEN END IF
Syntax
IF condition_1 THEN actions_1 ELSE
actions_last END IF
1.
IF condition_1 THEN actions_1 ELSIF
condition_2 THEN actions_2 ELSIF
condition_n THEN actions_n ELSE
actions_last END IF
2.
16
IF THEN END IF
Example
DECLARE     a NUMBER     b NUMBER
BEGIN     SELECT e, f INTO a, b FROM T1
WHERE egt1     IF b1 THEN  INSERT INTO T1
VALUES ( b, a )     ELSE  INSERT INTO T1
VALUES ( b10, a10 )     END IF END
T1 before
e f
1 3
2 4
T1 after
e f
1 3
2 4
14 12
17
LOOP EXIT WHEN END LOOP
Syntax
LOOP     / list of statements /
EXIT WHEN condition / list of
statements / END LOOP
18
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 i1         EXIT WHEN igt100
    END LOOP END
19
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.
20
WHILE END LOOP
Syntax
WHILE condition LOOP         /
list of statements for WHILE loop body / END
LOOP
21
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.

22
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.eTYPE     b T1.fTYPE        
/ Cursor declaration /
  CURSOR T1Cursor IS           SELECT e, f
FROM T1 WHERE e lt f          FOR UPDATE
T1
e f
1 3
2 4
23
Cursor Example
BEGIN     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
T1CursorNOTFOUND         / 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 / END
T1 before
e f
1 3
2 4
T1 after
e f
3 1
4 2
24
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).

25
Procedure Example

• Example
• This creates the procedure, to execute it

CREATE PROCEDURE addtuple1( i IN NUMBER, j IN
NUMBER ) IS BEGIN     INSERT INTO T1 VALUES
( i, j ) END addtuple1
BEGIN addtuple1( 99,100 ) END
26
Procedure Example

• Example
• This creates the procedure, to execute it

CREATE PROCEDURE addtuple3( a IN NUMBER, b OUT
NUMBER ) IS BEGIN     b 4     INSERT
INTO T1 VALUES ( a, b ) END addtuple3
DECLARE     v NUMBER BEGIN     addtuple3
(10, v ) END
27
Functions Syntax
CREATE OR REPLACE FUNCTION function_name (
parameters ) RETURN return_type IS / No
declare keyword / Local_var_declarations BEGI
N / list of statements for procedure body /
Return return_variable END function_name
28
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
29
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.

30
User Defined Exceptions

• User-defined exceptions are declared in the
  declarative section.

DECLARE exception_name EXCEPTION --
exceptions are declared here BEGIN
executable_statements --
exceptions are raised here EXCEPTION
exception_handling --
exceptions are handled here END
31
User Defined Exceptions
DECLARE e_toomany EXCEPTION -- exception
are declared here BEGIN IF num_students gt 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
32
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.

33
Handling Exceptions
. EXCEPTION WHEN exception_name1 THEN
Statements1 -- do something here WHEN
exception_name2 THEN Statements2 WHEN
OTHERS THEN Statements3 END
34
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
35
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.

36
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
37
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
  lttable_namegt. If present, the OF clause defines
  the event to be only an update of the
  attribute(s) listed after OF. For example    
• If FOR EACH ROW option is specified, the trigger
  is row-level otherwise, the trigger is
  statement-level.

... INSERT ON R ...     ... INSERT OR DELETE
OR UPDATE ON R ... UPDATE OF A, B OR INSERT
ON R ...
38
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.

39
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 lt 10 )     BEGIN        
INSERT INTO T5 VALUES ( newRow.b, newRow.a
)     END trig1