Chapter 1 Overview of PL/SQL

Based on PL/SQL User s Guide

Example Database Tables

CREATE TABLE dept ( deptno NUMBER (2) NOT NULL,

Dname CHAR(14),

Loc CHAR(13) )

CREATE TABLE emp ( empno NUMBER(4) NOT NULL,

ename CHAR(10) ,

job CHAR(9) ,

mgr NUMBER(4) ,

sal NUMBER(4) ,

deptno NUMBER(2) )

1.1 PL/SQL Language

A Oracle PL/SQL is a block structured language in which the functions, procedures and anonymous blocks are the basic blocks. Blocks can be defined with another block.

A block consists of three parts

• DECLARATION part: declare variables, constants, cursors, user-defined exceptions The reserved word DECLARE must be removed when a PL/SQL block is used to define a stored procedure.
• EXECUTABLE part: this part consists of SQL, SQL/PLUS statements.
• EXCEPTION HANDLING part: A warning or error is called exception. There is set of predefined error such as data not found. Users can also define their errors or exceptions.

1.2 Examples

Example 1: Raise_Salary.SQL

--Raise employee salary based on the previous salary

DECLARE -- begin the declaration part. Replace DECLARE by subprogram heading to make a

-- procedure or function.

rate number(5, 2) ;

salary number(5) ;

BEGIN

SELECT sal INTO salary FROM scott.emp

WHERE empno = 9999

FOR UPDATE OF sal ;

IF salary > 1000 THEN

rate := 0.05 ;

ELSE

rate := 0.02 ;

END IF;

UPDATE scott.emp SET sal = salary * ( 1 + rate )

WHERE empno = 9999;

COMMIT ;

END;

1.3 Variables, Constants and Assignment Statements

Variables:

• Variables are defined in declaration part.
• Only one variable can be define per line.
• Variable can be initialized when it is defined:

var TYPE := expression ;

Constants:

• A constant is defined by the following syntax

DECLARE

Identifier CONSTANT TYPE := value ;

Cursors:

• A cursor is a SQL construct allowing you access one row at time from a multiple row result set.
• For every data manipulation SQL statements or SELECT statements which return n rows, Oracle create an implicit cursor.
• For SELECT statement which returns multiple rows, a explicit cursor must be declared to access individual row at a time.
• A cursor is defined by the following syntax:

DECLARE

 .

CURSOR cursor_name [ ( parameter_name TYPE [, parameter_name TYPE  ] ) ]

IS select_statement ;

• Examples

-- pl_cursor.sql: Count the number of employees and total salary in

-- department 40.

DECLARE

cnt number(2) := 0;

salaryTotal NUMBER(5) := 0;

CURSOR cur_emp ( dnum NUMBER )

IS SELECT sal FROM emp WHERE deptno = dnum ;

BEGIN

FOR c1 IN cur_emp (40) LOOP

salaryTotal := salaryTotal + c1.sal ;

cnt := cnt + 1;

END LOOP ;

INSERT INTO wang.tmp VALUES(cnt, salaryTotal);

COMMIT ;

END ;

/

1.4 PL/SQL Data Types, Column and Row Attributes

• The following is a list of data types which can be used to define variables and constants

BINARY_INTTEGER

DEC

DECIMAL

DOUBLE PRECISION

FLOAT

INT

INTEGER

NATURAL

NATURALN

NUMBER

NUMERIC

PLS_INTEGER

POSITIVE

POSITIVEN

REAL

SMALLINT

CHAR

CHARACTER

LONG

LONG RAW

RAW

ROWID

STRING

VARCHAR

VARCHAR2

DATE

BOOLEAN

table%ROWTYPE provide a type which is the same as the type of a table or a cursor record structure.

Column%TYPE provide a type which is the same as a table s column., a constant or a variable.

1.5 Control Structures

• Conditional Controls:

IF condition THEN

Statements

END IF

IF condition THEN

Statements;

ELSEIF condition THEN

Statements ;

ELSE

Statements;

END IF ;

EXIT-WHEN condition ;

• Iterative Controls

LOOP

Statements

END LOOP;

FOR i IN lowerbound .. upperbound LOOP

Statements

END LOOP ;

FOR cursor_variable IN cursor_name LOOP

Statements ;

END LOOP;

WHILE condition LOOP

Statements;

END LOOP ;

• Sequential Control

GOTO lablel ;

 .

<< label>>

1.6 Exception Handling

• A predefined or user define error or warning (called exceptions) can be handled by your PL/SQL program.
• A predefined exception is implicitly raised by the system while a user-defined exception must be raised by RAISE statement.
• A list of predefined exceptions are given in later chapter.
• A user-defined exception is defined by the following format:

User_defined_exception EXCEPTION ;

In the declaration part of a block or a subprogram (a block consists of three parts: declaration, executable and exception handling and a subprogram is a function or procedure).

• A user-defined exception is raised by:

IF condistion THEN -- condition is true when error occurred.

RAISE uer_defined_exception ;

END IF ;

• A user-defined or predefined exception is handled in the exception handling part by the following format:

EXCEPTION -- start exception part.

WHEN exception1 THEN

pl_statements ;

WHEN exception2 THEN

pl_statements ;

• Examples

The following procedure shows how to handle user and predefined exceptions. The stored procedure does the following:

(1)Create the commission if the comm field of emp is not NULL and store the date/time into wang.msg table.

(2)If a employee doesn t have commission, then data-time and a message is stored in the wang.msg.

(3)If the employee is not in the table, insert the data/time and message into wang.msg.

CREATE OR REPLACE PROCEDURE cal_commission ( empID IN NUMBER) AS

-- Notice that the reserved word DECLARE is removed in creating

-- procedure.

salary NUMBER(7,2);

commission NUMBER(7,2) ;

no_commission EXCEPTION ; -- missing comission error

-- The way of using no_such_emp does catch the error, predefined error

-- DATA_NOT_FOUND must be used.

-- no_such_emp EXCEPTION ;

BEGIN

SELECT sal, comm INTO salary, commission

FROM scott.emp WHERE empno = empID ;

-- The following can be used in fetch cursor statement.

-- IF SQL%NOTFOUND THEN -- if a employee has no comm.

-- RAISE no_such_emp ;

-- END IF ;

IF commission IS NULL THEN -- if a employee has no comm.

RAISE no_commission ;

END IF ;

INSERT INTO wang.msg

VALUES (sysdate, 'Bouns for employee ' || to_char(empID)

|| ':   || to_char(salary * 0.5 + commission * 0.15) ) ;

COMMIT;

-- Starting exception handling part

EXCEPTION -- Begin exception handling

WHEN no_commission THEN

INSERT INTO wang.msg

VALUES (sysdate, 'Employee ' || to_char(empID) ||

' has No commission') ;

WHEN NO_DATA_FOUND THEN

INSERT INTO wang.msg

VALUES (sysdate, 'No such Employee ' || to_char(empID)) ;

COMMIT ;

END ;

The resultant wang.msg table can be displayed by the following SQL/PLUS statements:

COLUMN Date_Time FORMAT A20

select to_char(dt, 'mm/dd/yy:hh:mm:ss') Date_Time, MSG Message from msg

/

1.7 Modularity

Beside the control structures which can be used to write more complicated program. PL/SQL blocks are used to make program units such as functions, procedure, packages and stored functions, procedures and stored procedures. The program units allow you organize your codes better and easier.

The PL/SQL blocks are used

Stored procedures, functions, and packages, triggers. A package is program unit which usually contains a set of related functions and procedures.

Within stored procedures, functions and packages, you can define functions, procedures called from the PL/BLOCK in which the subprograms are defined.

Make a complicated embedded SQL in host languages.

1.8 Advantages of PL/SQL

The Advantages of using PL/SQL can be seen in the following aspects:

(a). PL/SQL Support SQL: The powerful SQL statement makes code easy. Allowing PL/SQL to issue SQL statement makes PL/SQL an enhanced tool

(b). Higher Productivity: PL/SQL adds functionality to non-procedural tools such as SQL*Form, SQL*Menu and SQL*ReportWriter. In those non-procedural tools, you can write PL/SQL blocks, functions, procedures. That is the procedural constructs in PL/SQL is available in those tools which allow you make PL/SQL blocks.

( C ). Better Performance: If you send a SQL statement to server, each SQL statement is a call. The next call will not start until the previous one is completed. However, if you make several SQL statements into a PL/SQL block, and send the block to the Oracle server. One call is needed.

It reduces the network traffic, and gives much better results. Call to stored procedures (RPC) will further increase the performance by compiling and storing the procedures as database objects instead of compiling it each time.

(d) PL/SQL support all data types of SQL and all SQL statements. The code you write in PL/SQL can run on any machine where Oracle is stored