7.1 Introduction to PL/SQL
PL/SQL is the procedure extension to Oracle SQL. It is used to access an
Oracle database from various environments (e.g. Forms, Reports to create
triggers, procedures, functions, etc.).
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 allows the user to store compiled code in the database, thereby
providing the access and sharing of the same subprograms by multiple
application
7.2 Block Structure
Basic unit in PL/SQL; can be nested.
DECLARE
Declarative section -- variables and types, cursors, and local subprograms
here (optional)
PL/SQL is a strongly typed language. All variables must be defined before uses
and types should be matched.
BEGIN
Executable section -- statements here; this is the main section that is required
EXCEPTION
Exception handling section -- error handling statements here (optional)
END;
Example:
DECLARE
Comp_id NUMBER;
Comp_name CHAR(20) := 'ACME SHOWERS';
BEGIN
SELECT company_id_seq.NEXTVAL INTO comp_id
FROM dual;
INSERT INTO company (company_id, company_name)
VALUES (comp_id, comp_name);
END;
7.2.1 Several kinds of blocks
Anonymous blocks (generally constructed dynamically and executed only once)
Named blocks (anonymous blocks with a label)
Subprograms (procedures, functions, packages)
Triggers (named blocks that are also stored in the database). Subprograms and
triggers can be treated as named blocks
7.2.2 Identifiers
Valid names in PL/SQL (e.g., variables, cursors, subprograms) are similar to
SQL's (30 characters starting with alphabets, and numbers, #, $, _, quoted
identifiers), many reserved words.
7.2.3 Delimiters
+, -, *, /, ** (exponentiation), = (equality), := (assignment), -- (single line
comment), /* */ (multi-line comment), .. (range), << >> (label), ; (statement
separator), and so on.
7.2.4 Literals (constant) and Constant declaration
Number (400 or NULL), String ('houston' or NULL), Boolean literals(TRUE, FALSE,
NULL)
Constant declaration:
E.g. maxVal constant integer(2) := 20;
7.2.5 Variable declarations
Syntax: Variable_name [CONSTANT] data_type [NOT NULL] [:= (or DEFAULT) value];
All declarations must be made in the declaration section of the block.
Variable Scopes: Block structure rule (similar to Pascal based language such as
C, Ada, Java, etc.)
Example:
num NUMBER(2)
next_tax_filing_date CONSTANT DATE := '15-APR-00';
company_name VARCHAR2(60) NOT NULL DEFAULT 'PCS R US';
bid number(5) not null := 11111; --must be initialized at declaration
once declared not null
hired_date date; --intialized to null
7.2.6 The basic PL/SQL types
Scalar type (defined in package STANDARD): NUMBER, CHAR, VARCHAR2, BOOLEAN,
BINARY_INTEGER, LONG
Composite types (user-defined types): TABLE, RECORD
Converting between data types
Implicit conversion -- may be confusing or erroneous (hard to debug)
sometimes PL/SQL can convert between characters and numbers, characters and
dates
Explicit conversion -- this is a better idea. TO_CHAR(), TO_DATE(),
TO_NUMBER(), ...
7.2.7 Boolean expression
E.g. 'scott' LIKE 'sc%t' returns TRUE, 115 BETWEEN 110 AND 120 returns TRUE,
'scott' IN ('mike', 'scott') returns TRUE, etc.
Boolean expression with NULL: NOT NULL returns NULL (FALSE in True/False only
test), FALSE AND NULL returns FALSE, TRUE OR NULL returns TRUE, Otherwise
returns NULL
7.2.8 Anonymous Blocks
A block contains three parts: a declarative part, an executable part between the
"declare" and "begin" keyword, and execution part and optional
exception-handling part between the "begin" and "end" keyword.
Example:
DECLARE
c_table Books%rowtype;
cursor c is select bookId, title from Books; -- cursor definition in
PL/SQL
c_rec c%rowtype;
i binary_integer; -- basically an integer
BEGIN
i := 1;
for c_rec in c loop
exit when c%notfound;
end loop;
for i in 1..c_table.count loop
dbms_output.put_line('c_table(' || i || ').title = ' ||
c_table(i).title);
end loop;
END;
Note: Save the above codes in a script file, say pA.sql
SQL> start pA.sql should execute the anonymous block
7.2.9 Nested blocks -- the idea of nested blocks is borrowed from PASCAL
DECLARE
CURSOR emp_cur IS ...;
BEGIN
DECLARE
Total_sales NUMBER;
BEGIN
DECLARE
Hiredata DATE;
BEGIN
...
END;
END;
END;
Each block has its own scope.
7.3 PL/SQL Control Structures
7.3.1 IF THEN ELSE statement
IF boolean_exp1 THEN Statement1; -- executed when TRUE
[ELSIF boolean_exp2 THEN -- Note that ELSIF not ELSEIF
Statement2;]
...
[ELSE statementN;] -- executed when NOT TRUE
END IF;
Example:
if (score >= 90) then
na := na +1;
elsif (score >= 80) then
nb : = nb +1 ;
elsif (score >= 70) then
nc := nc + 1;
else
nd := nd + 1;
end if
NULL as a statement:
IF report.selection = 'DETAIL' THEN NULL; -- Do nothing
ELSE Exec_detail_report;
END IF;
7.3.2 LOOPS statements
LOOP -- simple loop
[EXIT [WHEN condition]];
END LOOP;
WHILE condition -- while loop
LOOP
Statements; -- [EXIT [WHEN condition]] can still be used for premature exit
END LOOP;
FOR LOOPS: numeric FOR LOOP and cursor FOR LOOP. Here is the general syntax of
the numeric FOR LOOP (the cursor FOR LOOP will be discussed later).
FOR loop_index IN [REVERSE] low_bound .. high_bound
LOOP
Statements;
END LOOP;
Rules for Numeric FOR LOOPs
Do not declare the loop index. PL/SQL automatically and implicitly declares it
as a local variable with data type INTEGER. The scope of this index is the
loop itself; you can't reference the loop index outside the loop.
Expressions used in the range scheme are evaluated once, when the loop starts.
If you make changes within the loop to the variables, those changes will have
no effect.
The loop index is always incremented or decrement by one. There is no STEP.
Never change the values of either the loop index or the range boundary from
within the loop.
Example:
FOR cnt IN 1..10 -- this loop executes 10 times counting cnt from 1 to 10
LOOP ... statements ... END LOOP;
FOR cnt IN REVERSE 1..10 -- this loop executes 10 times counting cnt from 10 to
1
LOOP ... statements ... END LOOP;
FOR cnt IN 10..1 -- this loop DOES NO executes at all since 10 > 1
LOOP ... statements ... END LOOP;
FOR cnt IN start_period..end_period -- this loop executes the period times
specified in the expression
LOOP ... statements ... END LOOP;
GOTO and Labels -- label is defined as <
Labeling loops: -- <
It is illegal to branch into an inner block, loop, or IF statement.
Loop Example 1:
loop
i := i + 1;
if i > 10 then
exit;
end if;
sum := sum + i;
end loop;
Loop Example 2:
for i in 1..10 loop --increment i
dbms_output.putline ('i= '|| i);
sum := sum + i;
end loop;
for i in reverse 1..10 loop --decrement i
dbms_output.putline('i= '|| i);
sum := sum + i;
end loop;
Loop Example 3:
i := 1;
sum := 0;
while ( i < 1000) loop
sum := sum + i;
i := 2*i;
end loop;
7.4 %TYPE, %ROWTYPE, and User-defined TYPES
7.4.1 %TYPE (anchored type variable declaration)
E.g. name Books.title%type; /* name is defined as the same type as column
'title' of table Books*/
commission number(5,2) := 12.5;
x commission%type; -- x is defined as the same type as variable 'commission'
Note:
1. anchored variable allows the automatic synchronization of the type of
anchored variable with the type of
type.
2. anchored type are evaluated at compile type, so recompile the program to
reflect the change of
7.4.2 User-defined subtypes
Syntax: SUBTYPE new_type IS original_type;
Example: SUBTYPE num IS NUMBER; -- NUMBER(4) not allowed (constraint)
mynum num; -- num is a user-defined type name (number)
SUBTYPE nametype IS customer.fname%TYPE;
7.4.3 Records structure
Example:
TYPE student_rec_type IS RECORD ( -- student_rec_type is a record type name
studentid NUMBER(5),
first_name VARCHAR2(20),
last_name VARCHAR2(20)
);
student_a student_rec_type;
student_b student_rec_type;
student_a.first_name := 'Walker'; -- reference to first_name in the record
Record assignment is fine if two record types are matched. E.g. student_b :=
student_a;
7.4.4 Using %ROWTYPE for declaring records
Syntax: RecordName TableName%ROWTYPE;
%ROWTYPE is used to declare the same record type as all the column types defined
in a table (since the row is basically a record). It provides a record type that
represents a row in a table (or view). The record can store an entire row of
data selected from the table or fetched by a cursor. E.g., mycust
customer%ROWTYPE; -- mycust is a record name, customer is a table name. Once we
defined a row type record variable, we can use all the field names in the table,
e.g., customer in this case, mycust.name, mycust.age, etc.
%TYPE and %ROWTYPE are really handy when you handle the columns and rows in a
database table in PL/SQL program.
7.5 Array (Table)
In PL/SQL, unlike other languages, you need to create an array type first before
you use it.
Syntax: TYPE tabletype_name IS TABLE OF element_type [NOT NULL] INDEX BY
BINARY_INTEGER;
E.g. TYPE mytable IS TABLE OF student.first_name%TYPE INDEX BY BINARY_INTEGER;
names mytable; -- two tables in DECLARE section
newNames mytable;
names(10) = 'scott'; -- table statement in BEGIN ... END
PL/SQL Arrays are not like Arrays in C (it is more similar to the Iterator in
C++, the Array in Java, or the Collection in VB).
Key (index) is BINARY_INTEGER, Value (table element), the number of elements
is only limited to the size of BINARY_INTEGER, elements are not necessarily in
any order
Names(0) := 'aaa'; -- key is 0, value is 'aaa'
Names(-5) := 'bbb'; -- key is -5, value is 'bbb'
Names(3) := 'ccc'; -- key is 3, value is 'ccc'
Names(15.45) := 'ddd' -- 15.45 is converted to 16 automatically by ORACLE.
NewNames : = names; -- table assignment (actually this is an object assignment)
Array of structure:
TYPE student_table IS TABLE OF students%ROWTYPE INDEX BY BINARY_INTEGER;
mystudents student_table;
mystudents(100).first_name := 'tommy';
7.5.1 Characteristics of PL/SQL Tables
One-dimensional: A PL/SQL table can have only one column (one-dimensional
array).
Unbounded or Unconstrained: There is no predefined limit to the number of rows
in a PL/SQL table. The table grows dynamically as you add more rows to the
table.
Sparse: A row exists in the table only when a value is assigned to that row.
Rows do not have to be defined sequentially.
Homogeneous elements: All rows in a table contain values of the same data
type.
Indexed by integers (32 bit BINARY_INTEGER): You can have that many of rows.
7.5.2 Clearing the PL/SQL table
Define an empty table and then assign the empty table to the table you want to
clear.
E.g. empty_tab mytable; -- empty table
names := empty_tab; -- this is more like freeing memory space
This is the easiest way.
7.5.3 Table Attributes in array (table.attribute)
Attributes are ORACLE defined methods or properties. E.g. COUNT, DELETE, EXISTS,
FIRST, LAST, NEXT, PRIOR (COUNT returns number, DELETE returns n/a but delete,
EXISTS returns boolean, FIRST, LAST, NEXT, PRIOR all return index)
tableName.COUNT -- number of elements currently contained in the table
tableName.EXISTS(i) -- check if ith row exists
tableName.DELETE(i) -- deletes the ith row
tableName.DELETE(i,j) -- deletes all rows between index i and j
tableName.NEXT(i) -- next to ith row
tableName.PRIOR(i) -- prior of ith row
mystudents(100) := NULL; -- delete everything in mystudents table but not remove
element space
Index is better to be start with 1, 2, .... -- for possible compatibility with
other language
7.5.4 Example PL/SQL code
DECLARE -- anonymous block
/* Declare variables to be used in the block. */
v_custid NUMBER(3) := 222;
v_first VARCHAR2(20) := 'john';
v_last VARCHAR2(20) := 'smith';
v_city VARCHAR2(20) := 'New York';
BEGIN
/* Add a row to the customer table, using the values of the variables. */
INSERT INTO customer (customer_number, last, first, city)
VALUES (v_custid, v_first, v_last, v_city)
END;
7.6 SQL within PL/SQL
Usually DML statements (SELECT, INSERT, UPDATE, DELETE) or transaction control
statements (COMMIT, ROLLBACK, SAVEPOINT) are allowed in PL/SQL program (except
for using a package, DBMS_SQL in v2.1 or higher) -- since PL/SQL intentionally
designed to use early binding.
7.6.1 Syntax of SELECT statement:
SELECT field_list INTO PL/SQL record or variable FROM table_list WHERE
condition;
The SELECT statement should return no more than one row, otherwise it returns
error.
Example:
DECLARE
Student_record students%ROWTYPE;
v_department class.department%TYPE
BEGIN
SELECT * INTO student_record FROM students WHERE id = 1111;
SELECT department INTO v_department FROM classes WHERE room_id = 999;
END;
7.6.2 Syntax for the other statements (INSERT INTO ... VALUES, UPDATE, DELETE)
are the same.
SQL> CREATE SEQUENCE student_sequence START WITH 100
BEGIN
INSERT INTO students (id, first_name, last_name)
VALUES (student_sequence.NEXTVAL, 'john', 'smith');
END;
7.6.3 Transaction control
A transaction is a series of SQL statements that either succeed or fail as a
unit.
The use of COMMIT, ROLLBACK, SAVEPOINT in PL/SQL is the same as in SQL*PLUS
Transactions vs. Blocks: When a block starts, it does not mean that a
transaction starts. A single block can contain multiple transactions.
7.7 Subprograms
What Are Subprograms?
Subprograms are named PL/SQL blocks that can take parameters and be invoked.
PL/SQL has two types of subprograms called procedures and functions. Generally,
you use a procedure to perform an action and a function to compute a value.
Subprograms aid application development by isolating operations. They are like
building blocks, which you can use to construct reliable, modular, maintainable
applications.
Like unnamed or anonymous PL/SQL blocks, subprograms have a declarative part, an
executable part, and an optional exception-handling part. The declarative part
contains declarations of types, cursors, constants, variables, exceptions, and
nested subprograms. These objects are local and cease to exist when you exit the
subprogram. The executable part contains statements that assign values, control
execution, and manipulate ORACLE data. The exception-handling part contains
exception handlers, which deal with exceptions raised during execution.
Procedures, Functions, and Packages
They can be stored in the database and run when appropriate.
They share many of the same properties in the other languages like C -- more
reusable, more manageable, more readable, more reliable.
Types of procedures or functions:
Local procedures/functions (local subprograms),
Stored procedures/functions (stored subprograms)
Subprogram locations
Subprograms can be stored in the data dictionary (in the form of object code,
also called p-code) as well as within the declarative section of a block (in
this case, it is called, Local subprogram).
Related data dictionary views: user_objects, user_source, user_errors
DESC[RIBE] now supports the following objects: TABLE/VIEW, PROCEDURE/FUNCTION,
SYNONYM, PACKAGE, OBJECT TYPE
Stored vs. Local subprogram
A stored subprogram become invalid (means it needs recompiled) if a DDL
operation is performed on its dependent objects.
To recompile it: ALTER PROCEDURE procedure/function name COMPILE
How does ORACLE know whether dependent objects are changed or not? ORACLE uses
timestamp or signature.
Use subprogram in other blocks or subprograms
Subprograms can be defined using any ORACLE tool that supports PL/SQL. They can
be declared in PL/SQL blocks, procedures, functions. However, subprograms must
be declared at the end of a declarative section after all other program objects.
To become available for general use by all tools, subprograms must be stored in
an ORACLE database. For more information, see the section "Stored Subprograms"
later in this tutorial.
7.7.1 Creating a Procedure
Stored procedures or Local Procedures:
CREATE [OR REPLACE] PROCEDURE procedure_name [(argument [{IN | OUT | IN OUT}]
argument_type, ...)] {IS | AS}
Procedure_body;
Procedure_body is a PL/SQL block, must have at least one statement (can be
NULL).
Creating a procedure is a DDL operation -- so implicit COMMIT is done
Either IS or AS can be used
Parameter mode (Ada style of call-by-value or reference): IN (read-only), OUT
(write-only value is ignored and NOT allowed at RHS value), IN OUT
(read-write), default is IN mode. The argument_type must be unconstrained,
e.g. CHAR(20) is not allowed.
Through parameters, procedure can return values
If you omit CREATE OR REPLACE, the PROCEDURE become a local procedure.
The structure of a procedure
CREATE OR REPLACE PROCEDURE procedure_name [(...)] IS
Declarative section -- there is NO DECLARE keyword
BEGIN
Execution section
EXCEPTION
Exception section
END [procedure_name]; -- procedure_name is optional (good style) but matched
with if used
If there are no parameters in the procedure, no parenthesis is needed in the
procedure head.
Example 1:
PROCEDURE apply_discount -- local procedure
(company_id IN company.company_id%TYPE, discount_in IN NUMBER) IS
min_discount CONSTANT NUMBER := .05;
max_discount CONSTANT NUMBER := .25;
invalid_discount EXCEPTION;
BEGIN
IF discount_in BETWEEN min_discount AND max_discount THEN
UPDATE item
SET item_amount := item_amount*(1-discount_in);
WHERE EXISTS (SELECT 'x' FROM order
WHERE order.order_id = item.order_id AND
order.company_id=company_id_in);
IF SQL%ROWCOUNT = 0 THEN
RAISE NO_DATA_FOUND;
END IF;
ELSE
RAISE invalid_discount;
END IF;
EXCEPTION
WHEN invalid_discount THEN
DBMS_OUTPUT.PUT_LINE('The specified discount is invalid.');
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No orders in the system for company:' ||
TO_CHAR(company_id_in));
END apply_discount;
Example 2: . Debits a bank account: When invoked or called, this procedure
accepts an account number and a debit amount. It uses the account number to
select the account balance from the accts database table. Then, it uses the
debit amount to compute a new balance. If the new balance is less than zero, an
exception is raised; otherwise, the bank account is updated. The example also
illustrate the use of Exception
PROCEDURE debit_account (acct_id INTEGER, amount REAL) IS
old_balance REAL;
new_balance REAL;
overdrawn EXCEPTION;
BEGIN
SELECT bal INTO old_balance FROM accts WHERE acctno = acct_id;
new_balance := old_balance - amount;
IF new_balance < 0 THEN
RAISE overdrawn;
ELSE
UPDATE accts SET bal = new_balance WHERE acctno = acct_id;
END IF;
EXCEPTION
WHEN overdrawn THEN
dbms_output.putline("overdrawn");
END debit_account;
Example 3: procedure raise_salary, which increases the salary of an employee:
When called, this procedure accepts an employee number and a salary increase
amount. It uses the employee number to select the current salary from the emp
database table. If the employee number is not found or if the current salary is
null, an exception is raised. Otherwise, the salary is updated.
PROCEDURE raise_salary (emp_id INTEGER, increase REAL) IS
current_salary REAL;
salry_missing EXCEPTION;
BEGIN
SELECT sal INTO current_salary FROM emp WHERE empno = emp_id;
IF current_salary IS NULL THEN
RAISE salary_missing;
ELSE
UPDATE emp SET sal = sal + increase WHERE empno = emp_id;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO emp_audit VALUES (emp_id, 'No such number');
WHEN salary_missing THEN
INSERT INTO emp_audit VALUES (emp_id, 'Salary is null');
END;
How to call a procedure within a PL/SQL block as an executable statement:
procedure_name(arguments);
E.g. apply_discount(new_company_id, 0.15); -- 15% discount
display_store_summary; -- no parenthesis needed
Defining local procedures
Local subprograms are defined in the declarative section.
The keyword CREATE [OR REPLACE] is omitted, and start with PROCEDURE keyword.
Debugging Procedures
SQL*Plus SHOW ERRORS command will display all of the errors (e.g., line and
column number for each error as well as text error message) associated with the
most recently created procedural object. This command will check the USER_ERRORS
data dictionary view for the errors associated with the most recent compilation
attempt for that procedural object.
7.7.2 Formal Parameters
Parameter Modes
Use parameter modes to define the behavior of formal parameters. The three
parameter modes, IN (the
default), OUT, and IN OUT, can be used with any subprogram. However, avoid using
the OUT and IN
OUT modes with functions. The purpose of a function is to take zero or more
arguments and return a single
value. It is a poor programming practice to have a function return multiple
values. Also, functions should be
free from side effects, i.e. change the values of variables not local to the
subprogram.
IN: An IN parameter lets you pass values to the subprogram being called. Inside
the subprogram, an IN
parameter acts like a constant. Therefore, it cannot be assigned a value. For
example, the following
assignment statement causes a compilation error:
PROCEDURE debit_account (acct_id INTEGER, amount REAL) IS
minimum_purchase CONSTANT REAL := 10.0;
service_charge CONSTANT REAL := 0.50;
BEGIN
IF amount < minimum_purchase THEN
amount := amount + service_charge; -- illegal
END IF;
...
The actual parameter that corresponds to an IN formal parameter can be a
constant, literal, initialized
variable, or expression. Unlike OUT and IN OUT parameters, IN parameters can be
initialized to default values.
OUT: An OUT parameter lets you return values to the caller of a subprogram.
Inside the subprogram, an OUT
parameter acts like an un-initialized variable. Therefore, its value cannot be
assigned to another variable or
reassigned to itself. For instance, the following assignment statement causes a
compilation error:
PROCEDURE calc_bonus (emp_id INTEGER, bonus OUT REAL) IS
hire_date DATE;
BEGIN
SELECT sal * 0.10, hiredate INTO bonus, hire_date FROM emp WHERE empno =
emp_id;
IF MONTHS_BETWEEN(SYSDATE, hire_date) 60 THEN
bonus := bonus + 500; -- syntax error
END IF;
...
The actual parameter that corresponds to an OUT formal parameter must be a
variable; it cannot be a
constant or expression. For example, the following procedure call is illegal:
calc_bonus(7499, salary + commission); -- syntax error
PL/SQL checks for this syntax error at compile time to prevent the overwriting
of constants and expressions. An OUT actual parameter can (but need not) have a
value before the subprogram is called. However, the value is lost when you call
the subprogram. Inside the subprogram, an OUT formal parameter cannot be used in
an expression; the only operation allowed on the parameter is to assign it a
value.
Before exiting a subprogram, explicitly assign values to all OUT formal
parameters. Otherwise, the values of
corresponding actual parameters are indeterminate. If you exit successfully,
PL/SQL assigns values to the
actual parameters. However, if you exit with an un-handled exception, PL/SQL
does not assign values to the
actual parameters.
IN OUT: An IN OUT parameter lets you pass initial values to the subprogram being
called and return updated values to the caller. Inside the subprogram, an IN OUT
parameter acts like an initialized variable. Therefore, it can
be assigned a value and its value can be assigned to another variable. That
means you can use an IN OUT
formal parameter as if it were a normal variable. You can change its value or
reference the value in any way,
as the following example shows:
PROCEDURE calc_bonus (emp_id INTEGER, bonus IN OUT REAL) IS
hire_date DATE;
bonus_missing EXCEPTION;
BEGIN
SELECT sal * 0.10, hiredate INTO bonus, hire_date FROM emp WHERE empno =
emp_id;
IF bonus IS NULL THEN
RAISE bonus_missing;
END IF;
IF MONTHS_BETWEEN(SYSDATE, hire_date) 60 THEN
bonus := bonus + 500;
END IF;
...
EXCEPTION
WHEN bonus_missing THEN
...
END calc_bonus;
The actual parameter that corresponds to an IN OUT formal parameter must be a
variable; it cannot be a
constant or expression.
Constraints on Formal Parameters
It is illegal to constrain parameters with a length, precision, and scale,
e.g., VARCHAR2(20), NUMBER(2) except for anchored types such as %TYPE
Parameter can have default values using DEFAULT or := (in this case, you can
omit certain argument).
7.7.3 Creating a Function
CREATE [OR REPLACE] FUNCTION function_name [(...this part is same as the
procedure...)] RETURN return_type {IS|AS}
Function_body;
RETURN expression;
Inside the function body, the RETURN statement is used to return control to the
caller with a value.
A function is very similar to a procedure. Both can be stored in the database or
declared within a block. However, function should return a value (or can return
multiple values through parameters like procedure) -- good style is to return
only single value, use procedure if multiple values or no values should be
returned.
Calls to user-defined functions can appear in procedural statements, but not in
SQL statements. For example, the following INSERT statement is illegal:
DECLARE
empnum INTEGER;
...
FUNCTION bonus (emp_id INTEGER) RETURN REAL IS
BEGIN ... END bonus;
BEGIN
...
INSERT INTO payroll VALUES (empnum, ..., bonus(empnum)); -- illegal call
END;
Example 1:
FUNCTION balance (acct_id INTEGER) RETURN REAL IS
acct_bal REAL;
BEGIN
SELECT bal INTO acct_bal FROM accts WHERE acctno = acct_id;
RETURN acct_bal;
END balance;
Example 2: We create a procedure 'add_new_student' and store it into database.
CREATE OR REPLACE PROCEDURE add_new_student( p_firstName
students.firstName%TYPE,
p_lastName students.lastName%TYPE, p_major students.major%TYPE DEFAULT
'phycology') AS
BEGIN
INSERT INTO students
VALUES(student_sequence.nextval, p_firstName, p_lastName, p_major);
COMMIT;
END add_new_student;
In the another PL/SQL block, we call the procedure 'add_new_student' as follow:
BEGIN
add_new_student('Barbara', 'Blues');
END;
We create a function 'almostFull' and store it into database.
CREATE OR REPLACE FUNCTION almostFull(
p_department classes.department%TYPE,
p_course classes.course%TYPE) RETURN BOOLEAN IS
v_currStudents NUMBER;
v_maxStudents NUMBER;
v_returnValue BOOLEAN;
BEGIN
SELECT current_students, max_students INTO v_currentStudents, v_maxStudents
FROM classes
WHERE department = p_department AND course = p_course;
IF v_currentStudents >= v_maxStudents
v_returnValue = TRUE;
ELSE v_returnValue = FALSE;
RETURN v_returnValue;
END allmostFull;
In the another PL/SQL block, we call the function as follow:
BEGIN
IF allmostFull('Computer Science', 'Oracle') THEN
DBMS_OUTPUT.PUT_LINE('Class is Full');
ELSE DBMS_OUTPUT.PUT_LINE('Class is not Full yet');
END;
An example of Local function
DECLARE
CURSOR c_allStudents IS SELECT firstName, lastName FROM students;
v_formattedName VARCHAR2(50);
-- Function definition should come last - all variables should be defined in the
above
FUNCTION formatName(p_firstName IN VARCHAR2, p_lastName IN VARCHAR2)
RETURN VARCHAR2 IS
BEGIN
RETURN p_firstName || ' ' || p_lastName;
END formatName;
-- Begin main block here
BEGIN
FOR v_studentRecord IN c_allStudents LOOP
v_formattedName := formatName(v_studentRecord.firstName,
v_studentRecord.lastName);
INSERT INTO temp_table(fname) VALUES (v_formattedName);
END LOOP;
COMMIT;
END;
Example 3: function sal_ok, which determines if an employee salary is out of
range:
FUNCTION sal_ok (salary REAL, title REAL)
RETURN BOOLEAN IS
min_sal REAL;
max_sal REAL;
BEGIN
SELECT losal, hisal INTO min_sal, max_sal FROM sals
WHERE job = title;
RETURN (salary = min_sal) AND (salary <= max_sal);
END sal_ok;
7.7.4 Packages
What are Packages? Packages are PL/SQL constructs that allow related objects to
be stored together.
What are the advantages? Enforced information hiding, Object-Oriented design,
Object persistence, Performance improvement, Less restrictive on dependency
A package has two separate parts: specification and body. Each of them is
stored separately.
A package can only be stored -- NOT be local in a block
A package is essentially a named declarative section
Package Specification (or package header):
It contains information about the contents of the package, NOT the code itself.
CREATE [OR REPLACE] PACKAGE package_name {IS|AS} -- no parameters
Procedure_specification | function_specification | variable_declaration |
type_definition |
Exception_declaration | cursor_declaration
END [package_name];
Procedure or function specification means procedure or function header (similar
to function prototype in C)
Example:
CREATE OR REPLACE PACKAGE sp_timer IS
PROCEDURE capture(context_in IN VARCHAR2); -- no begin keyword
PROCEDURE show_elapsed;
END sp_timer;
Like a module, the package specification contains all the information that is
needed for a developer to understand how to call the objects in the package. A
developer should never have to examine the code behind the specification (which
is the body) in order to understand how to use and benefit from the package.
Package Body (separate data dictionary object from the header)
It contains the actual code for the forward subprogram declarations in the
package header -- so it can not be compiled without the header.
Package body is optional (if no procedure or function defined in the header)
The specification for the procedure or function must be the same in both.
CREATE OR REPLACE PACKAGE BODY package_name {IS|AS}
...
[BEGIN]
... -- usually you put maintenance routines here
END [package_name];
CREATE OR REPLACE PACKAGE BODY sp_timer IS
Last_timing NUMBER := NULL; -- private element
PROCEDURE capture (context_in IN VARCHAR2) IS
BEGIN
Last_timing := DBMS_UTILITY.GET_TIME;
END;
PROCEDURE show_elapsed IS
BEGIN
DBMS_OUTPUT_PUT_LINE(DBMS_UTILITY.GET_TIME - last_timing);
END;
END sp_timer;
The body may also contain elements that do not appear in the specification.
These are called the private elements of the package. A private element cannot
be referenced outside of the package, since it does not appear in the
specification.
Packages and Scope
Any object declared in a package header is in scope and is visible outside the
package. This may be useful for declaring global variables, and can be
accessed by qualifying the object with the package name. E.g.
DBMS_OUTPUT.PUT_LINE('hello');
The procedure call is the same as it would be for a stand-alone procedure.
Package Initialization: The first time a package is called, it is instantiated
-- the package is read from disk into memory (in p-code form), and run.
Packages and Dependencies
The package body depends on the header and referenced tables
The package header does not depend on anything (this is the advantage!) -- we
can change the package body without changing the header.
If the header is changed, this automatically invalidates the body.
How to reference Package elements
A package owns its objects, just as a table owns its columns. You use the same
dot notation to provide a fully qualified specification for a package's object
as you would for a table's column. The following package specification declares
a constant, an exception, a cursor, and several modules:
CREATE OR REPLACE PACKAGE pets_inc IS
Max_pets_in_facility CONSTANT INTEGER := 120;
Pet_is_sick EXCEPTION;
CURSOR pet_cur RETURN pet%ROWTYPE;
FUNCTION next_pet_shots(pet_id_in IN NUMBER) RETURN DATE;
PROCEDURE set_schedule(pet_id_in IN NUMBER);
END pets_inc;
... the package body is omitted here...
To reference any of these objects in another PL/SQL block, I preface the object
name with the package name, as follows:
BEGIN
IF pets_inc.max_pets_in_facility > 100 THEN
...
END IF;
...
OPEN pets_inc.pet_cur;
...
:pet_master.next_appointment := pets_inc.next_pet_shots(:pet_master.pet_id);
...
EXCEPTION
WHEN pets_inc.pet_is_sick THEN
...
END;
If you do not preface the call to next_pet_shots with the package name,
pets_inc, PL/SQL is not able to resolve the reference and the compile fails.
However, within a package, you don't need to qualify reference to other
elements of that package. PL/SQL will automatically resolve your reference
within the scope of the package.
The cursor RETURN clause
Packages introduce an enhancement to the way you can declare a cursor: the
RETURN clause. The RETURN clause allows you to create a specification for a
cursor which is separate from its body (the SELECT statement).
You may then place cursors in packages and hide the implementation details from
developers. Consider the following cursor declaration with RETURN clause:
CURSOR caller_cur (id_in IN NUMBER) RETURN caller%ROWTYPE
IS SELECT * FROM caller WHERE caller_id = id_in;
The specification of the caller_cur cursor is:
CREATE PACKAGE company IS
CURSOR caller_cur(id_in NUMBER) RETURN caller%ROWTYPE
END company;
While the body of the caller_cur cursor is:
CREATE PACKAGE BODY company IS
CURSOR caller_cur (id_in NUMBER) RETURN caller_cur %ROWTYPE IS
SELECT * FROM caller WHERE caller_id = id_in;
END company;
You can include a RETURN clause for any cursor you write in PL/SQL. The RETURN
clause may be made up of any of the following datatype structures:
A record defined from a database table, using the %ROWTYPE
A record defined from a programmer-defined record.
Recompiling/dropping package
ALTER PACKAGE package_name COMPILE [PACKAGE | BODY];
DROP PACKAGE package_name;
DROP PACKAGE BODY package_name;
7.8 Stored Subprograms
Subprograms can be compiled separately and stored permanently in an ORACLE
database, ready to be executed.
7.8.1 Advantages of Stored Subprograms
Higher Productivity : By designing applications around a library of stored
subprograms, you can avoid redundant coding and increase your productivity. For
example, suppose various applications call a stored procedure that manages the
emp database table. If the management method changes, only the procedure must be
revised, not the
applications.
Better Performance: Using subprograms can reduce calls from your application to
ORACLE. For example, to execute ten individual SQL statements, ten calls are
required, but to execute a subprogram containing ten SQL
statements, only one call is required. Reducing calls can boost performance,
especially if your application
communicates with ORACLE over a network.
Memory Savings: Stored subprograms take advantage of the ORACLE shared memory
capability. So, only one copy of a subprogram need be loaded into memory for
execution by multiple users. As a result, your applications
require less memory.
Application Integrity: Stored subprograms improve the integrity and consistency
of your applications. By developing all your applications around a library of
stored subprograms, you reduce the likelihood of coding errors. For example, a
subprogram must be validated only once. Then, it can be used with confidence in
any number of
applications.
Tighter Security: Stored subprograms can help enforce data security. Your DBA
can restrict users to specific database operations by granting access only
through subprograms. For example, your DBA might grant users
EXECUTE access to a stored procedure that updates the emp table, but not grant
them access to the table
itself. That way, users can call the procedure, but cannot arbitrarily
manipulate table data.
7.8.2 Creating Stored Subprograms
The Procedural Database Extension allows you to CREATE subprograms and store
them permanently in an
ORACLE database for general use. You can issue the CREATE PROCEDURE and CREATE
FUNCTION statements interactively from SQL*Plus.
CREATE PROCEDURE fire_employee (emp_id NUMBER) AS
BEGIN
DELETE FROM emp WHERE empno = emp_id;
END;
CREATE PROCEDURE create_user(ssn char(9), user_name varchar2(30), user_type
varchar2(8)) AS
BEGIN
INSERT INTO Users VALUES(ssn,user_name,user_type);
END;
Notice that when creating subprograms, you use the keyword AS instead of IS in
the specification.
When you CREATE a subprogram for storage in the database, ORACLE automatically
compiles the source
code, caches the object code in a shared SQL area in the System Global Area
(SGA), and stores the source and object code in the data dictionary. The object
code stays cached in the SGA, where it can be executed quickly. When necessary,
ORACLE applies a least-recently-used algorithm that selects shared SQL areas to
be flushed from the SGA to make room for others.
When you call a stored subprogram, ORACLE checks to see if an existing shared
SQL area holds the object code for that subprogram. If not, ORACLE allocates a
shared SQL area and loads the subprogram object code into it. Then, ORACLE
allocates a private SQL area, which holds session-specific subprogram values. If
multiple users execute a subprogram simultaneously, only one shared SQL area is
used, but multiple private SQL areas are maintained--one for each user.
SQL statements within the subprogram are processed in the same way. They use
shared SQL areas to hold
their parsed representations, and private SQL areas to hold session-specific
information. The shared SQL
area used by a subprogram is called a parent cursor; the shared SQL areas used
by SQL statements within
the subprogram are called child cursors.
7.8.3 Calling Stored Subprograms
You can call stored subprograms from a database trigger, another stored
subprogram, an ORACLE
Precompiler application such as Pro*C, or an ORACLE tool such as SQL*Plus.
From SQL*Plus
SQL> execute fire_employee(11111);
SQL>
From a Proc*C Program
EXEC SQL EXECUTE
BEGIN
create_dept(:id,:name, :usertype);
END;
END-EXEC;
An invalid subprogram must be recompiled before it can be executed.
Recompilation occurs in two phases.
First, ORACLE determines if any referenced subprograms or packages are
themselves invalid. If so, they
must be recompiled. This can lead to a cascade of recompilations, all of which
must succeed; otherwise,
ORACLE returns a runtime error and the subprogram remains invalid. Second, the
referencing subprogram
is recompiled, after which it can be executed.
Calling Stored Procedures at SQL*Plus prompt
Procedures or Functions are normally called within executable or
exception-handling section. However, we can call a stored procedure at SQL*PLUS
prompt with EXECUTE command as follow: EXECUTE procedure_name(arg1, arg2, ..)
**Calling PL/SQL stored functions in SQL (> 2.1)
Looking at the problem
Suppose, for example, you need to calculate and use an employee's total
compensation in native SQL. The computation itself is straightforward enough:
Total compensation = salary + bonus. My SQL statement would include this
formula:
SELECT employee_name, salary + NVL(bonus, 0)
FROM employee;
In this case, the calculation is very simple, but the fact remains that if you
need to change the total compensation formula for any reason (different kinds of
bonuses, for example), you would then have to change all of these hardcoded
calculations in all the SQL statements.
A far better approach is to create a function that returns the total
compensation:
CREATE OR REPLACE FUNCTION total_comp
(salary_in IN employee.salary%TYPE, bonus_in IN employee.bonus%TYPE)
RETURN NUMBER IS
BEGIN
RETURN salary_in + NVL(bonus_in, 0);
END;
Then I could replace the formulas in my code as follows:
SELECT employee_name, total_comp(salary, bonus)
FROM employee;
The ability to place programmer-defined PL/SQL functions inside SQL is a very
powerful enhancement to the Oracle development environment. With these functions
you will be able to do the following:
Consolidate business rule logic into a smaller number of well-tuned and easily
maintained functions.
Improve the performance of your SQL statements - declarative statements are
inefficient sometimes.
Simplify your SQL statements.
Perform actions (procedural) in SQL which are otherwise impossible.
Syntax for calling stored functions in SQL
[schema_name.][pkg_name.][function_name[@db_link_name][parameter_list]]
Schema_name is usually your own Oracle account, e.g., scott.
For example, suppose that the calc_sales function is defined in database. Here
are some different ways it might be called inside SQL:
As a standalone function call:
SELECT calc_sales(1001, 'O') -- 'O' can represent Order_type here
FROM orders; -- what will happen with this query?
As a package-based function call:
SELECT sales_pkg.calc_sales(order_num, 'O')
FROM orders;
As a remote, package-based function call:
SELECT sales_pkg.calc_sales@NEW_YORK(order_num, 'O')
FROM orders;
As a standalone function in a specific schema:
SELECT scott.calc_sales(order_num, 'O')
FROM orders;
Requirements for Stored Functions in SQL
The function must be stored in the database
The function must be a row-specific function, not a column or group function.
The function can apply only to a single row of data, not an entire column of
data that crosses rows.
All of the function's parameters must use the IN mode.
Functions defined in packages must have RESTRICT_REFERENCES pragma.
Restrictions on PL/SQL Functions in SQL
Stored functions in SQL offer tremendous power. As you might expect, however,
power introduces the possibility of abuse and the need for responsible action
(e.g., side effects). General recommendation for a function is that it should be
narrowly focused on computing and returning a value.
But a recommendation is not enough when it comes to database integrity: in
order to guard against nasty side effects and unpredictable behavior, the Oracle
Server makes it impossible for your stored function in SQL to take any of the
following actions:
Purity-level
The stored function may not modify database tables. It cannot execute an
INSERT, DELETE, or UPDATE statement.
The stored function that is called remotely may not read or write the values
of package variables. The Oracle Server does not support side effects that
cross user sessions.
... There are more other technical restrictions ...about purity-level...
7.8.4 Dropping Procedures and Functions
DROP PROCEDURE procedure_name;
DROP FUNCTION function_name;
7.9 Database Triggers
What are triggers?
A trigger defines an action the database should take when some database-related
event (such as inserts, updates, deletes) occurs.
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 (DML: INSERT, UPDATE, or DELETE) happens, and a trigger doesn't accept
arguments.
When triggers are used?
Maintaining complex integrity constraints (referential integrity)
Auditing information in a table by recording the changes
Automatically signaling other programs that action needs to take place when
changes are made to a table
Enforcing complex business rules
CREATE [OR REPLACE] TRIGGER trigger_name {BEFORE | AFTER} {INSERT|UPDATE|DELETE}
[OF column] ON table_reference
[FOR EACH ROW [WHEN trigger_condition]]
[DECLARE] -- optional, for declaring local variables
trigger_body;
The Trigger_body is executed when an event (Insert, Update, Delete operation)
occurs.
Trigger names
Triggers exist in a separate namespace from procedure, package, tables (that
share the same namespace), which means that a trigger can have the same name as
a table or procedure.
Types of triggers: row-level triggers and statement-level triggers
Row-level triggers
Row-level triggers execute once for each row in a transaction.
Row-level triggers are the most common type of triggers; they are often used
in data auditing applications.
Row-level trigger is identified by the FOR EACH ROW clause in the CREATE
TRIGGER command.
Statement-level triggers
Statement-level triggers execute once for each transaction. For example, if a
single transaction inserted 500 rows into the Customer table, then a
statement-level trigger on that table would only be executed once.
Statement-level triggers therefore are not often used for data-related
activities; they are normally used to enforce additional security measures on
the types of transactions that may be performed on a table.
Statement-level triggers are the default type of triggers created and are
identified by omitting the FOR EACH ROW clause in the CREATE TRIGGER command.
Before and After Triggers
Since triggers occur because of events, they may be set to occur immediately
before or after those events. The events that execute triggers are database
transactions, triggers can be executed immediately BEFORE or AFTER the
statements INSERTs, UPDATEs, DELETEs.
AFTER row-level triggers are frequently used in auditing applications, since
they do not fire until the row has been modified.
Clearly, there is a great deal of flexibility in the design of a trigger.
Valid trigger types (possible combination of triggers)
Statement (INSERT, DELETE, UPDATE), Timing (BEFORE, AFTER), Level (Row-level,
Statement-level)
The values for the statement, timing, and level determine the types of the
triggers. There are total of 12 possible types of triggers: 3*2*2 = 12
Combining Trigger Types:
Triggers for multiple INSERT, UPDATE, DELETE commands on a table can be
combined into a single trigger (using OR), provided they are all at the same
level (row-level or statement-level), e.g., INSERT OR UPDATE OR DELETE.
However, you can not combine BEFORE or AFTER, e.g., BEFORE OR AFTER is
illegal.
Example 1: -- for the table major_stats(major, total_credits, total_students);
CREATE OR REPLACE TRIGGER updateMajorStats
AFTER INSERT OR DELETE OR UPDATE ON students -- Oracle will check the status of
this table
DECLARE -- unlike a procedure, use DECLARE keyword
CURSOR c_statistics IS
SELECT major, COUNT(*) total_students, SUM(current_credits) total_credits
FROM students
GROUP BY major;
BEGIN
FOR v_statsRecord IN c_statistics LOOP
UPDATE major_stats
SET total_credits = v_statsRecord.total_credits,
total_students = v_statsRecord.total_students
WHERE major = v_statsRecord.major;
IF SQL%NOTFOUND THEN
INSERT INTO major_stats(major, total_credits, total_students)
VALUES(v_statsRecord.major,
v_statsRecord.total_credits, v_statsRecord.total_students);
END IF;
END LOOP;
END updateMajorStats;
Example 2: The following event logging keep records of how many times a specific
user has checked out a book
/* suppose the table is
create table CheckoutLog {
SSN char(9) references Users.SSN
bookId number(5) references Book.bookId,
outTimes number(2),
primary key ( SSN, bookId) }
*/
CREATE TRIGGER
/* triggering event */
AFTER INSERT OF SSN, bookId ON Checkout
/* FOR EACH ROW*/
/* trigger constraint */
/* WHEN (new.bookId = 11111) */
DECLARE
times NUMBER;
BEGIN
/* trigger action */
times := 0;
SELECT outTimes INTO times FROM CheckoutLog
WHERE CheckoutLog.bookId = :new.bookId and CheckoutLog.SSN = :new.SSN
IF times 0 THEN
UPDATE CheckoutLog SET outTimes = outTimes + 1
WHERE CheckoutLog.bookId = :new.bookId and CheckoutLog.SSN = :new.SSN
ELSE
INSERT INTO Renew
VALUES (:new.SSN, :new.bookId, 1);
END IF;
END;
Order of Trigger Firing
Triggers are fired as the DML statement is executed. The algorithm for executing
a DML statement is given here:
Execute the BEFORE statement-level trigger, if present.
For each row affected by the statement:
Execute the BEFORE row-level trigger, if present
Execute the DML statement itself.
Execute the AFTER row-level trigger, if present.
Execute the AFTER statement-level trigger, if present.
To illustrate this, suppose we have all four kinds of UPDATE triggers defined on
the table, classes before and after at statement level and row level. Suppose we
then issue the following UPDATE statement that affects four rows:
UPDATE classes
SET num_credits = 4
WHERE department IN ('HIS', 'CS');
The before and after statement-level triggers are each executed once, and the
before and after row-level triggers are each executed four times. As each
trigger is fired, it will see the changes made by the earlier triggers, as
well as any database changes made by the statement so far.
The order in which triggers of the same type are fired is not defined. If the
order is important, combine all of the operations into one trigger.
Restrictions on Triggers
A trigger may not issue any transaction control statement (e.g., COMMIT,
ROLLBACK, ...)
The trigger body cannot declare any LONG or LONG LAW variables.
Using :old and :new in Row-level Triggers
A row-level trigger fires once per row processed by the triggering statement.
Inside the trigger, you can access the row that is currently being processed
through two pseudocodes -- :old and :new, e.g., :new.field or :old.field.
However, they are not true records.
:old refers to the data as it existed prior to the transaction. Updates and
Deletes usually reference :old values. The :new values are the data values
that the transaction creates (such as the columns in an inserted record).
:old is undefined for INSERT statements, :new is undefined for DELETE
statements.
If you need to set a column value in an inserted row via your trigger, then
you will need to use a BEFORE INSERT trigger in order to access the :new
values. Using an AFTER INSERT trigger would not allow you to set the inserted
value, since the row will already have been inserted into the table (and thus
be :old).
The colon in front of :new and :old is required. The reason is that :new and
:old are actually implemented as bind variables.
Operations that would normally be valid on records are not valid for :new and
:old. For example, they cannot be assigned as entire records, e.g., v_tmprec
:= :old; is not legal. Only the individual fields within them may be assigned,
e.g., v_tmprec.col := :old.col;.
Likewise, :old and :new cannot be passed to procedures or functions that take
arguments of triggering_table%ROWTYPE, since the actual parameter is assigned
to the formal parameter in the same way as a standard assignment.
For example, the GenerateStudentID trigger shown next uses :new. Its purpose is
to fill in the ID field of Students with a vlaue generated from the
student_sequence sequence.
CREATE OR REPLACE TRIGGER GenerateStudentID
BEFORE INSERT OR UPDATE ON students
FOR EACH ROW
BEGIN
SELECT student_sequence.nextval INTO :new.ID FROM dual;
END GenerateStudentID;
The trigger GenerateStudentID actually modifies the value of :new.ID. This is
one of the useful features of :new -- when the statement is actually executed,
whatever values are in :new will be used.
INSERT INTO students(first_name, last_name) VALUES('Lolita', 'Lazarus');
We can issue an INSERT statement without generating an error. Even though we
haven't specified a value for the primary column ID (which is required), the
trigger will supply it.
If we specify a value for ID, it will be ignored, since the trigger changes
it.
The WHEN clause -- WHEN condition
The WHEN clause is valid for row-level triggers only. If present, the trigger
body will be executed only for those rows that meet the condition specified by
the WHEN clause.
For example, suppose we want to monitor any adjustments to an Amount that are
greater than 10 percent.
CREATE TRIGGER customer_bef_upd_row BEFORE UPDATE ON customer
FOR EACH ROW
WHEN (new.amount/old.amount > 1.1)
BEGIN
INSERT INTO customer_audit -- different table from the triggering table
VALUES (:old.Action_date, :old.Action, :old.Item, :old.qty, :old.qtyType,
:old.rate, :old.amount)
END;
The above row-level BEFORE UPDATE trigger will be executed only if the new
value of the Amount column is more than 10 percent greater than its old value.
The When clause adds further criteria to the triggering condition. The
triggering event must not only be an UPDATE of the customer table, but also
must reflect an increase of over 10 percent in the value of the Amount column.
The statements in the BEGIN and END is the trigger body. The commands shown in
the BEGIN and END are to be executed for every UPDATE of the customer table
that passes the WHEN condition.
Using Trigger Predicates: INSERTING, UPDATING, and DELETING
We can use three boolean functions to determine what the operation is (since you
can have several triggering operations, sometimes you need to determine which
operation is performing now).
CREATE TRIGGER customer_bef_upd_ins_row
BEFORE INSERT OR UPDATE OF Amount ON CUSTOMER
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO customer_audit
VALUES(:new.Action_date, :new.Action, :new.Item, :new.qty, :new.qtyType,
:new.rate, :new.Amount);
ELSE -- if not inserting, then we are updating Amount
INSERT INTO customer_audit
VALUES(:old.Action_date, :old.Action, :old.Item, :old.qty, :old.qtyType,
:old.rate, :old.Amount);
END IF;
END;
If you look at the trigger body in the above PL/SQL program, the trigger
checks to see if the record is being inserted into the customer table. If it
is, then the first part of the trigger body is executed. The INSERTING portion
of the trigger body inserts the new values of the record into the
customer_audit table.
Other transaction types can be checked. In this example, since the trigger
executed, the transaction must either an INSERT or an UPDATE of the Amount
column.
Mutating Tables
There are restrictions on which tables and columns a trigger body may access. In
order to define these restrictions, it is necessary to understand mutating table
and constraining table.
A mutating table is a table that is currently being modified by a DML statement
(e.g., a table on which the trigger is defined).
A constraining table is a table that might need to be read from for a
referential integrity constraint.
To illustrate these, consider the Registered_students table, which is created
with
CREATE TABLE Registered_students (
Student_id NUMBER(5) NOT NULL,
Course NUMBER(3) NOT NULL,
Department CHAR(3) NOT NULL,
FOREIGN KEY(student_id) REFERENCES students(id),
FOREIGN KEY(department, course) REFERENCES classes(department, course)
);
Registered_students has two declarative referential integrity constraints.
Thus, both Students and Classes are constraining tables for
Registered_students.
Registered_students itself is mutating during execution of a DML statement
against it. Because of the constraints, Classes and Students also need to be
modified and/or queried by the DML statement.
For all row-level triggers and DELETE CASCADE operation at statement-level
triggers, SQL statement in a trigger body:
May not read from or modify any mutating table of the triggering statement.
This includes the triggering table itself.
May not read from or modify the primary, unique, or foreign key columns of a
constraining table of the triggering table. They may, however, modify the
other columns if desired.
If an INSERT statement affects only one row, then the BEFORE and AFTER row
triggers for that row do not treat the triggering table as mutating -- this is
the only case where a row-level trigger may read from or modify the triggering
table.
However, the statement such as INSERT INTO table_name SELECT ... which is
multi-row inserts, always treat the triggering table as mutating, even if the
subquery returns only one row.
As a simple example, consider the MYTRIGGER
CREATE OR REPLACE TRIGGER mytrigger
BEFORE INSERT OR UPDATE ON customer
FOR EACH ROW -- row-level trigger
BEGIN
UPDATE customer -- this trigger is updating the customer table
SET zip = 'ZIIIIP'
WHERE custid = 107;
END;
At SQL prompt, SQL>, if you try to update the customer table (mutating table) to
fire the trigger, mytrigger as follow:
UPDATE customer SET zip = 'MYZIIIP';
You will have the Oracle error like:
ERROR at line 1:
ORA-04091: table SYSTEM.CUSTOMER is mutating, trigger/function may not see it
ORA-06512: at "SYSTEM.MYTRIGGER", line 2
ORA-04088: error during execution of trigger 'SYSTEM.MYTRIGGER'
The same kinds of error may happen if you try to modify the constraining table
(e.g., primary or foreign key from the table). However, if you modify the
non-primary key or non-foreign key fields, then you may be able to modify the
constraining table.
Data dictionary views
USER_TRIGGERS (columns, trigger_type, table_name, triggering_event)
Dropping and disabling Triggers
DROP TRIGGER trigger_name;
ALTER TRIGGER trigger_name {DISABLE|ENABLE}; -- unlike procedures, packages
ALTER TABLE table_name {DISABLE|ENABLE} ALL TRIGGERS;
In the data dictionary, only the source code of the trigger is NOT stored, as
p-code (needs compilation).
The reason for this is obvious if you think the properties of the trigger,
that is dynamic binding, otherwise the trigger also requires dependency
information like procedures or packages, which is the case in v2.3 with
Oracle7).
Example: this example shows that the trigger action can include calls to the
built-in ORACLE procedure
raise_application_error, which lets you issue user-defined error messages:
CREATE TRIGGER check_salary BEFORE INSERT OR UPDATE OF sal, job ON emp
FOR EACH ROW
WHEN (new.job != 'PRESIDENT')
DECLARE
minsal NUMBER;
maxsal NUMBER;
BEGIN
/* Get salary range for a given job from table sals. */
SELECT losal, hisal INTO minsal, maxsal FROM sals WHERE job = :new.job;
/* If salary is out of range, increase is negative, *
* or increase exceeds 10%, raise an exception. */
IF (:new.sal < minsal OR :new.sal maxsal) THEN
raise_application_error(-20225, 'Salary out of range');
ELSIF (:new.sal < :old.sal) THEN
raise_application_error(-20230, 'Negative increase');
ELSIF (:new.sal 1.1 * :old.sal) THEN
raise_application_error(-20235, 'Increase exceeds 10%');
END IF;
END;
Cannot call stored subprograms that contain a COMMIT, ROLLBACK, or SAVEPOINT
statement.
7.10 Using cursor in PL/SQL
What is Cursor?
When Oracle process an SQL statement, it needs to allocate memory called context
area (which is part of the program global area (PGA) allocated on the server).
Cursor is a handle (or pointer), to the context area. PL/SQL program can control
the context area using Cursor.
Why do we need the Cursors?
SELECT statement should return only one row at a time in previous PL/SQL
programs. This is too restrictive in many applications.
We use the idea of Cursor to handle the above problem.
Different types of Cursors
Explicit Cursor and implicit Cursor (e.g., SQL statement in PL/SQL)
Static cursor and Dynamic cursor
Client side cursor and Server side cursor
Explicit cursor (the cursor name is explicitly assigned to a SELECT statement)
involves four steps
Declare the cursor (static part) Ã CURSOR cursor_name IS select_statement;
Open the cursor for a query à OPEN cursor_name;
Fetch the results into PL/SQL variables à FETCH cursor_name INTO
variable_list;
Close the cursor à CLOSE cursor_name;
The cursor declaration is the only step that goes in the PL/SQL declarative
section.
Actually PL/SQL engine takes care of the above four steps automatically
Implicit cursor is used for all other SQL statements (INSERT, UPDATE, DELETE,
and single-row SELECT...INTO)
Drawbacks of Implicit Cursors
It is less efficient than an explicit cursor -- it performs minimum two
fetches to see if it has more than one rows.
It is more vulnerable to data errors.
It gives you less programmatic control.
Even if your query returns only a single row, you might still decide to use an
explicit cursor.
However, there is no explicit cursor for UPDATE, DELETE, and INSERT statements.
Declaring a Cursor
E.g. in the DECLARE section
employee_rec employee%ROWTYPE;
CURSOR employee_cur IS -- employee_cur is not declared
SELECT * FROM employee; -- this is a static cursor
SELECT_statement contains no INTO clause.
A cursor declaration can reference PL/SQL variables in the WHERE clause.
The above cursor declaration associates the entire employee table with the
cursor named employee_cur.
The SQL is static (or static SQL), which means that the content of the SQL
statement is determined at compile time.
The name of an explicit cursor is not a PL/SQL variable. Instead, it is an
undeclared identifier used to refer to the query. You can't assign values to a
cursor, nor can you use it in an experssion.
PL/SQL variables in a Cursor
In native SQL, the SELECT list may contain both columns and expressions. In
PL/SQL, the SELECT list may contain PL/SQL variables, expressions, and even
functions as well as host language bind variables (> PL/SQL 2.1).
DECLARE
project_bonus NUMBER := 1000;
CURSOR employee_cur IS
SELECT employee_id, salary + project_bonus New_salary, :review
FROM employee
WHERE hiredate < ADD_MONTHS (SYSDATE, -36);
BEGIN
...
END;
Opening a Cursor
E.g. in the BEGIN section
OPEN employee_cur;
When a cursor is opened, the following things happen:
The values of the bind variables are examined.
Based on the values of the bind variables, the active set (the query result)
is determined.
The active set pointer is set to the first row.
Fetching from a Cursor
Syntax: FETCH cursor_name INTO record_or_variable_list;
E.g. FETCH employee_cur INTO employee_rec;
E.g. FETCH employee_cur INTO name, age, salary
When you fetch into a list of variables or record, the number of variables and
types in the record must match the number of expressions in the SELECT list of
the cursor.
After each FETCH, the active set pointer is increased to the next row.
You can FETCH from it until there are no more records left in the active set.
At this point the %NOTFOUND cursor attribute for the cursor is set to TRUE.
So, %NOTFOUND attribute is used to determine when the entire active set has
been retrieved.
The last fetch will assign NULL to the output variables.
Closing a Cursor
E.g. CLOSE employee_cur;
E.g. IF employee_cur%ISOPEN THEN
CLOSE employee_cur;
END IF
The resources associated with it (memory space) can be freed.
When execution of the block terminates, PL/SQL will automatically close any
local cursors. But don't depend on the runtime engine to do your cleaning up for
you.
Cursor attributes -- four attributes appended to a cursor name
%FOUND -- return true if a record was successfully fetchd from the cursor
otherwise return false;
error (not open or already closed); NULL (no fetch)
%NOTFOUND -- TRUE if record was not fetched successfully, FALSE otherwise.
%ISOPEN -- TRUE if cursor is open, FALSE otherwise.
%ROWCOUNT -- number of records fetched
E.g. EXIT WHEN employee_cur%NOTFOUND;
Processing Implicit Cursors
The same cursor attributes can be applied to the SQL cursor: e.g., SQL%NOTFOUND
SQL%NOTFOUND is not normally used with SELECT...INTO statements but used with
Exception handler (NO_DATA_FOUND) -- we will talk about this in the later
section.
Cursor Examples Using LOOP ... END LOOP
DECLARE -- Declare variables to hold information about the students majoring in
'History'.
v_StudentID students.id%TYPE;
v_FirstName students.first_name%TYPE;
v_LastName students.last_name%TYPE;
-- Cursor to retrieve the information about History students
CURSOR c_HistoryStudents IS -- c_HistoryStudents is not declared
SELECT id, first_name, last_name FROM students WHERE major = 'History';
BEGIN
OPEN c_HistoryStudents; -- Open the cursor and initialize the active set
LOOP -- Retrieve information for the next student
FETCH c_HistoryStudents INTO v_StudentID, v_FirstName, v_LastName;
EXIT WHEN c_HistoryStudents%NOTFOUND;
-- Exit loop when there are no more rows to fetch
-- Process the fetched rows, in this case sign up each student for History 301
by inserting them
-- into the registered_students table. Record the first and last names in
temp_table as well.
INSERT INTO registered_students (student_id, department, course)
VALUES (v_StudentID, 'HIS', 301);
INSERT INTO temp_table (num_col, char_col)
VALUES (v_StudentID, v_FirstName || ' ' || v_LastName);
END LOOP;
CLOSE c_HistoryStudents; -- Free resources used by the cursor
COMMIT; -- Commit our work
END;
Cursor Examples Using FOR LOOP ... END LOOP
DECLARE
-- Cursor to retrieve the information about History students
CURSOR c_HistoryStudents IS SELECT id, first_name, last_name FROM students
WHERE major = 'History';
BEGIN
FOR v_StudentData IN c_HistoryStudents -- Implicit OPEN of c_HistoryStudents is
done here.
LOOP
-- v_StudentData is implicitly Defined, and the scope of the variable is within
the loop
-- An implicit FETCH is done here. Now process the fetched rows as in the
followings.
INSERT INTO registered_students (student_id, department, course)
VALUES (v_StudentData.ID, 'HIS', 101);
INSERT INTO temp_table (num_col, char_col)
VALUES (v_StudentData.ID, v_StudentData.first_name || ' ' ||
v_StudentData.last_name);
-- Before the loop will continue, an implicit Check of c_HistoryStudents is done
here.
END LOOP;
-- Now that the loop is finished, an implicit CLOSE of c_HistoryStudents is
done.
COMMIT;
END;
Parameterized Cursors (Cursor parameters)
PL/SQL also allows you to pass parameters into cursors. The same rationale for
using parameters in modules applies to parameters for cursors:
A parameter makes the cursor more reusable.
A parameter avoids scoping problems.
Let's take a look at the difference between parameterized and unparameterized
cursors first:
-- First cursor
CURSOR joke_cur IS
SELECT name, category, last_used_date FROM joke WHERE category = 'POLITICIAN';
-- Second cursor
CURSOR joke_cur IS
SELECT name, category, last_used_date FROM joke WHERE category = 'HUSBAND';
Parameterized Cursor -- Generalizing Cursors with Parameters
DECLARE
CURSOR joke_cur(category_in VARCHAR2) IS
SELECT name, category, last_used_date FROM joke WHERE category =
UPPER(category_in);
joke_rec joke_cur%ROWTYPE;
BEGIN
/* Now when I open the cursor, I also pass the argument */
OPEN joke_cur('husband'); -- Or OPEN joke_cur('politician');
FETCH joke_cur INTO joke_rec;
...
END;
I can OPEN that same cursor with any category I like. Now I don't have to write
a separate cursor to accommodate this requirement:
The scope of the cursor parameter is confined to that cursor. You can't refer
to the cursor parameter outside of the SELECT statement associated with the
cursor.
The cursor parameter can be an IN parameter only (read only).
You can set the default values for parameters,
E.g. CURSOR joke_cur(category_in := 'husband')
Cursors with Transaction control
SELECT FOR UPDATE
There are times, when you will want to lock a set of records even before you
change them in your program. ORACLE offers the FOR UPDATE clause of the SELECT
statement to perform this locking.
CURSOR ...
SELECT ... FROM ...
FOR UPDATE [OF colum_reference] [NOWAIT];
...
UPDATE (or DELETE)
...
E.g.
CURSOR toys_cur IS
SELECT name, manufacturer
FROM my_collection
FOR UPDATE;
The WHERE CURRENT OF clause
This is to allow you to easily make changes to the most recently fetched row of
data. The ideal situation will be the cases: "Delete the record I just fetched"
or "Update these columns in that row I just fetched".
UPDATE table_name (or DELETE)
SET set_clause FROM table_name
WHERE CURRENT OF cursor_name
E.g.
DECLARE
Toys_rec my_collection%ROWTYPE;
CURSOR toys_cur IS
SELECT name, manufacturer FROM my_collection
FOR UPDATE; -- this is necessary for update with WHERE...
BEGIN
...
FETCH toys_cur INTO toys_rec;
...
UPDATE my_collection -- cursor table
SET manufacturer = 'Toys R them'
WHERE CURRENT OF toys_cur; -- current record
COMMIT;
...
END;
The most important advantage to using WHERE CURRENT OF where you need to change
the row fetched last is that you do not have to code in two (or more) places the
criteria used to uniquely identify a row in a table.
Cursor variables (Dynamic Cursors)
Cursor variable can be associated with different SELECT statements at run time
for flexibility.
DECLARE
/* Create the cursor type */
TYPE company_curtype IS REF CURSOR RETURN company%ROWTYPE;
/* Declare a cursor variable of that type */
Company_curvar company_curtype;
/* Declare a record with same structure as cursor variable. */
Company_rec company%ROWTYPE;
BEGIN
/* Open the cursor variable, associating with it a SQL statement */
OPEN company_curvar FOR SELECT * FROM company;
/* Fetch from the cursor variable */
FETCH company_curvar INTO company_rec;
/* Close the cursor object associated with variable */
CLOSE company_curvar;
END;
How to declare Cursor variable
Declare a cursor pointer type
TYPE type_name IS REF CURSOR [RETURN return_type];
-- return type is normally record
Declare a cursor variable using the above defined pointer type
OPEN the cursor variable with FOR key word and SELECT statement
CLOSE is the same as static cursor
Features of Cursor variables
It let you associate a cursor variable with different queries at different
times in your program execution.
It let you pass a cursor variable as an argument to a procedure or function.
It let you assign the contents of one cursor to another cursor variable (with
some restrictions).
Declaring REF CURSOR types and Cursor Variables
Just as with a PL/SQL table or a programmer-defined record, you must perform two
distinct declaration steps in order to create a cursor variable:
Create a referenced cursor TYPE.
Declare the actual cursor variable based on that type.
Opening cursor variables
You assign the cursor object to a cursor when you OPEN the cursor.
Syntax: OPEN cursor_name FOR select_statement;
For strong cursor type variable, the structure of the SELECT statement (the
number and datatypes of the columns) must match or be compatible with the
structure specified in the RETURN clause of the type statement.
DECLARE
TYPE emp_curtype IS REF CURSOR RETURN emp%ROWTYPE;
emp_curvar emp_curtype;
BEGIN
OPEN emp_curvar FOR SELECT * FROM emp; -- match with the emp%ROWTYPE
END;
For the weak cursor type variable, you can OPEN it for any query, with any
structure. In the following example, I open the cursor variable twice, with two
different queries:
DECLARE
TYPE emp_curtype IS REF CURSOR;
emp_curvar emp_curtype;
BEGIN
OPEN emp_curvar FOR SELECT * FROM emp;
OPEN emp_curvar FOR SELECT employee_id FROM emp;
OPEN emp_curvar FOR SELECT company_id, name FROM company;
...
END;
The last open didn't even have anything to do with the employee table!
The scope of a cursor variable is the same as that of a static cursor.
Error Handling
The error handling routines need to be separated from the program logic
Program logic is easier to understand (clean separation of error-processing
code).
No matter which statement fails, the program will detect (or catch) and handle
the error (This will improve the reliability of error handling).
Event-driven handling of errors.
Declaring Exception
Two types of exceptions: predefined exceptions, user-defined exceptions.
Exceptions are declared in the declarative section of the block, raised in the
executable section, and handled in the exception section.
Predefined exceptions
INVALID_CURSOR, NO_DATA_FOUND, INVALID_NUMBER, ZERO_DIVIDE (Oracle error code
e.g., ORA-1476 divide-by-zero error), TOO_MANY_ROWS, ROWTYPE_MISMATCH, etc.
User-defined exceptions
User-defined exceptions are declared in the declarative section
DECLARE
e_toomany EXCEPTION; -- exceptions are declared here
BEGIN
... executable statements ... -- exceptions are raised here
[EXCEPTION
... exception handlers ...] -- exceptions are handled here
END;
Raising Exceptions
When the error associated with an exception occurs or RAISE statement for the
user-defined exceptions
DECLARE
e_toomany EXCEPTION;
BEGIN
IF num_students > 100 THEN
RAISE e_toomany; -- Exception is raised here
... -- Any code here is not executed
EXCEPTION -- Exception section
WHEN e_toomany THEN -- Control passes to the exception handler.
... -- Code here will be executed
END;
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.
Handling Exceptions
EXCEPTION
WHEN exception_name1 THEN
Statements1; -- do something here
WHEN exception_name2THEN
Statements2;
WHEN OTHERS THEN
Statements3;
END;
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;
Named system exceptions
The exceptions which are already given names by PL/SQL are declared in the
STANDARD package in PL/SQL. You do not have to declare them in your own program.
SQLCODE and SQLERRM -- PL/SQL built in functions for error handling
SQLCODE returns the current error code, and SQLERRM returns the current error
message text.
SQLCODE return a negative value for Oracle error in most cases, except +100
for "no data found"
SQLERRM(100) returns "ORA-1403: no data found"
Name of exceptions Oracle Error SQLCODE value
INVALID_CURSOR ORA-01001 SQLCODE = -1001
NO_DATA_FOUND ORA-01403 SQLCODE = 100
ZERO_DIVIDE ORA-01476 SQLCODE = -1476
E.g.
DECLARE
my_data_notfound EXCEPTION;
EXCEPTION
WHEN my_data_notfound THEN
DBMS_OUTPUT.PUT_LINE('My own local exception');
WHEN STANDARD.NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('The predefined exception');
END;
More examples of cursor:
Example 1:
declare
cursor c1 return Users%rowtype is
select * from Users;
c1_rec c1%rowtype;
begin
if not c1%isopen then
open c1;
end if;
fetch c1 into c1_rec;
while c1%found loop
dbms_output.println('Row Number ' || c1%rowcount || ' '|| c1_rec.SSN ||
' '|| c1_rec.name || ' '|| ' '||
c1_rec.usertype);
fetch c into c1_rec;
end loop;
close c1;
end;
Example 2: same functionality as above, except use the variation of for loop
provided by PL/SQL notice there is no need to open, fetch, or close the cursor.
declare
cursor c1 return Users%rowtype is
select * from Users;
c1_rec c1%rowtype;
begin
for c1_rec in c1 loop
dbms_output.println('Row Number ' || c1%rowcount || ' '|| c1_rec.SSN ||
' '|| c1_rec.name || ' '|| ' '|| c1_rec.usertype);
end loop;
end;
Example 3:
declare
cursor c1 is
select * from salary
for update;
c1_rec c1%rowtype;
begin
for c1_rec in c1 loop
if (c1_rec.lowsal < 20000 ) then
update salary set lowsal = 1.1 * lowsal
where current of c1;
end if;
end loop;
end;
Example 4: parameterized cursors
cursor c1 (ctype in category.name%type ) is
select employees.id, employees.name, sum(video.videoNum)
from video, employees, orders, orderdetails
where employees.id = orders.emplyeeId and orders.orderNum =
orderdetails.orderNum and
orderdetails.videoNum = video.videoNum and category.cId = video.cId
and category.name = ctype
group by employees.id;
Home Index
No comments:
Post a Comment