9i PL/SQL Fundamentals

Judi Hotsinpiller, OCP

The University of Utah
Administrative Computing Services
Paper 600
http://www.acs.utah.edu/acs/
jhotsinpiller@acs.utah.edu
Session Agenda
 PL/SQL Principles
 PL/SQL Definition
 Compile Environment
 Subprograms
 Block Flow
 Data Types
 Scalar
 Composite Data types
 SQL Cursors
 Implicit
 Explicit
Session Agenda Continued
 Exceptions
 Merge
 Conditional Logic
 Case Expressions
 Loops
 Executing PL/SQL
 Summary
PL/SQL Principles
 Procedural Language/SQL
 Procedural Language/Structured Query
Language
 Oracle’s procedural extension to SQL
 Modern software engineering features
 Looping
 Conditional logic
 Exception handling
 Data encapsulation
 Information hiding
 Object orientation
PL/SQL Definition

 Procedural Language extensions to SQL

 Oracle product extension to other tools
 Extension of SQL
 Power to create complex 3GL type programs
 Cache results of SQL statements with cursors
 Loop through the results allowing manipulation
 Object Oriented Programming with Packages
Compile Environment

 Three execution engines in the Oracle Server

 SQL Statement Executor
 Procedural Statement Executor
 Java Statement Executor
 PL/SQL is an interpreted language
 Syntax errors caught logic errors are not
 SQL> show errors
 Use bind variables to return results to SQL*Plus
Flow PL/SQL

PL/SQL Engine
PL/SQL Block Procedural
Statement
Procedural Statements
Executor

SQL Statements

SQL Engine

SQL Statement Executor

Subprograms

 Anonymous blocks
 Three sections

 Declaration

 Execution

 Exception
Anonymous Block Example
DECLARE
emp_name emp.ename%TYPE;
total_salary NUMBER;
invalid_emp EXCEPTION;
BEGIN
total_salary :=
get_sal (emp_name, ‘TOTAL’);
IF total_salary IS NULL
THEN
RAISE invalid_emp;
ELSE
DBMS_OUTPUT.PUT_LINE
(‘Annual Salary to date ‘ ||
TO_CHAR (total_salary));
END IF;
EXCEPTION
WHEN invalid_employee
THEN
...
END;
Named PL/SQL Blocks
Procedures, Functions, Packages, and Triggers
 Stored at DB Server with name—shared
 Procedures
 Named block of PL/SQL code
 iSQL*Plus example of invoking a procedure
SQL> EXECUTE emp_raise (176)
 Invoking from within another procedure
emp_raise (176);
 Invoking from within an Anonymous PL/SQL block
DECLARE
v_empid emp.empno%type;
BEGIN
emp_raise (v_empid);
COMMIT;
…
END;
Named PL/SQL Blocks

 Functions
 PL/SQL block that accepts arguments and

computes a value and returns it

 Packages
 Packages are the way you can bundle

subprograms, items, and types into one

location
 Triggers
 Application Trigger or database Trigger
Calling a Function from a SQL
CREATE OR REPLACE FUNCTION tax_rate(p_amount in number)
RETURN NUMBER IS
BEGIN
RETURN (p_amount * 0.675);
END tax_rate;
/
SELECT empno, name, sal, tax_rate(salary)
FROM emp
WHERE tax_rate(sal) > (SELECT MAX(tax_rate(sal)
FROM emp
WHERE deptno = 10)
ORDER BY tax_rate(sal) DESC;
Built-in Functions
 Character Functions
 Functions manipulate and modify CHAR and VARCHAR2
 Date functions
 The date functions provide utilities that give programmers
the ability to perform manipulations
 Numeric functions
 numeric functions allow you to manipulate numbers
 Conversion functions
 convert form one data type to another
Packages
 Bundle subprograms, items, and types into one location
 Built-in Packages
 dbms_SQL

OPEN_CURSOR
PARSE
BIND_VARIABLE
EXECUTE
FETCH_ROWS
CLOSE_CURSOR
CREATE OR REPLACE PROCEDURE ddlproc (tablename varchar2, cols
varchar2) AS
cursor1 INTEGER;
BEGIN
cursor1 := dbms_sql.open_cursor;
dbms_sql.parse(cursor1, 'CREATE TABLE ' || tablename ||
' ( ' || cols || ' )', dbms_sql.v7);
dbms_sql.close_cursor(cursor1);
end;
/ (Note:1008453.6 Metalink)
Built in Packages Continued
 dbms_ddl
 Embed SQL commands ALTER, COMPILE, and ANALYZE

 dbms_ddl.alter_compile

 dbms_ddl.analyze_object

 dbms_job
 Scheduling of PL/SQL Programs

 dbms_job.submit

VARIABLE job_no is NUMBER

BEGIN
DBMS_JOB.SUMIT(
job => :job_no,
what => ‘OVER_PACK.ADD_DEPT(‘EDUCATION’, 2710);’,
next_date => TRUNC(SYSDATE + 3),
interval => ‘TRUNC(SYSDATE + 3)’);
 New PL/SQL supplied packages in 9i
Triggers
 Database Trigger
 DML Trigger

 Row

 Fires each time the table is affected

 Statement

 These are useful when trigger action does not

depend on the data
 Form Builder Triggers

 Executed only within a particular Form Builder

application
Block Flow
 Basic element of the code structure
 Declaration
 Contains all of the variables, constants, cursors, and user-
defined exceptions
 Executable
 SQL statements to manipulate the data
 Exception
 What to perform when an error is raised
Data Types
 Scalar
 Four basic scalar data types

 Number

 Character

 Boolean

 Date/Time
Data Types--Scalar
 Number [(precision, scale)]
 Number without length will allocated 23 spaces

 Char[(maximum_length)]
 Base type variable for fixed-length character

 VarChar2(Maximum_length)
 Base type for variable-length character
Data Types Continued
 Long
 LONG is used to store variable-length strings

 Long Raw
 Is not interpreted in PL/SQL.

 Boolean
 TRUE, FALSE, or NULL.

 LOB
 Graphic objects — stored out of line.

 Date
 Base type for date and time
Data Types Continued

Example:
DECLARE
v_job_title VARCHAR2(15);
v_number_employ BINARY_INTEGER :=0;
v_total_tax NUMBER(9,2) :=0;
v_orderdate DATE := SYSDATE + 7;
c_tax_rate CONSTANT NUMBER
(3,2) := 5.75;
v_flag BOOLEAN NOT
NULL := TRUE;
%TYPE Attribute
 Handy attribute--declare a data type to match
underlying database object column
Example:
DELARE
v_emp_name emp.name%TYPE;
v_bal NUMBER(7,2);
v_min_bal v_bal%TYPE := 15;
Data Types--Composite
 Composite
 Records—Composite data types

 RECORD

 Group of related data items stored as fields

 TABLE

 Holds an array

 NESTED TABLE

 VARRAY
%ROWTYPE Attribute
 Declare a record based on a collection of columns
Example:
DELARE
emp_record emp%ROWTYPE;
…
Data Types—SQL Cursors
 Implicit
 opens an area of memory in which SQL is parsed

and executed
 Implicit cursors attributes
 SQL%ROWCOUNT

 SQL%FOUND

 SQL%NOTFOUND

 SQL%ISOPEN
Implicit Cursor
Example:
VARIABLE rows_deleted VARCHAR2(30)
DECLARE
v_empid emp.empid%TYPE := 176;
BEGIN
DELETE FROM emp
WHERE empid = v_empid;
:rows_deleted := (SQL%ROWCOUNT || ‘ rows deleted ‘);
END;
/
PRINT rows_deleted
Data Types—SQL Cursors
 Explicit
 Results of queries that return one or more rows of

data
 Explicit cursors attributes
 CursorName%ROWCOUNT

 CursorName%FOUND

 CursorName%NOTFOUND

 CursorName%ISOPEN
Explicit Cursor Example
DECLARE
------------------ MAIN PL/SQL VARIABLES --------------
V_PS_UU_RPT_LDGR PS_UU_RPT_LDGR%ROWTYPE;
V_ERROR_CODE ps_uu_rpt_ldgr_error.UU_ERR_CODE := SQLCODE;
V_ERROR_MSG ps_uu_rpt_ldgr_error.UU_ERR_MSG := SQLERRM;
V_BLOCK_NAME ps_uu_rpt_ldgr_error.UU_BLOCK_NAME := 'PERIOD 0 BLOCK INSERT INTO
ps_uu_rpt_ldgr_tmp_1';
/* CURSOR TO RETRIEVE DATA TO POPULATE THE V_ACCOUNTING_PERIOD */
CURSOR CUR_VAR IS
SELECT ledger
,BUSINESS_UNIT
,FUND_CODE
,DEPTID
,PROGRAM_CODE
,PROJECT_ID
,ACCOUNT
,DESCR
,UU_CUST_DESCR
,CLASS_FLD
,uu_deptid_tran
,UU_FIN_PROG_CLAS
,UU_PROG_NME
,FISCAL_YEAR
,ACCOUNTING_PERIOD
,UU_ORIG_BUD_AMT
,UU_RVSD_BUD_AMT
,UU_ENCB_AMT
,UU_ACTL_AMT
FROM ps_uu_rpt_ldgr_new;
Explicit Cursor Example Continued
/* CURSOR TO CREATE THE UU_CM_BUDG, UU_FYTD_BUDG, UU_FY_BUD,
UU_CM_ENC, UU_FYTD_ENC, UU_FY_ENC,
UU_CM_ACTL, UU_FYTD_ACTL, UU_FY_ACTL*/
CURSOR CUR_SUM_DATA IS
SELECT
--------- BUDGET SUMS ---------
-- cm budget
SUM (CASE
WHEN accounting_period = V_ACCOUNTING_PERIOD
THEN UU_rvsd_BUD_AMT
ELSE 0 END) uu_cm_budg
-- fytd budget
,SUM (CASE
WHEN (accounting_period >= 1 AND accounting_period <= V_ACCOUNTING_PERIOD)
THEN UU_rvsd_BUD_AMT
ELSE 0 END ) uu_fytd_budg
….
FROM ps_uu_rpt_ldgr_new
WHERE ledger = v_PS_UU_RPT_LDGR .ledger
AND business_unit = v_PS_UU_RPT_LDGR .BUSINESS_UNIT
AND fund_code = v_PS_UU_RPT_LDGR .FUND_CODE
AND deptid = v_PS_UU_RPT_LDGR .DEPTID
AND program_code = v_PS_UU_RPT_LDGR .PROGRAM_CODE
AND project_id = v_PS_UU_RPT_LDGR .PROJECT_ID
AND account = v_PS_UU_RPT_LDGR .ACCOUNT
AND class_fld = v_PS_UU_RPT_LDGR .CLASS_FLD
AND uu_deptid_tran = v_PS_UU_RPT_LDGR .uu_deptid_tran
AND fiscal_year = v_PS_UU_RPT_LDGR FISCAL_YEAR
GROUP BY BUSINESS_UNIT
,FUND_CODE
,DEPTID
,PROGRAM_CODE
,PROJECT_ID
,ACCOUNT
,CLASS_FLD
,uu_deptid_tran
,fiscal_year ;
Explicit Cursor Example Continued
BEGIN
------------------ MAIN SUBPROGRAM ----------------------
/*OPEN CUR_VAR TO POPULATE THE VALUES FOR
ACCOUNTING_PERIOD, FISCAL_YEAR, AND PROGRAM_CODE
MUST DO THIS BEFORE YOU OPEN CUR_SUM_DATA OTHERWISE
YOU WILL NOT HAVE THE VARIABLE VALUES IN PL/SQL TO
RUN THE NEXT CURSOR */
IF CUR_VAR%ISOPEN THEN
CLOSE CUR_VAR;
END IF;
OPEN CUR_VAR;
LOOP
FETCH CUR_VAR
INTO v_PS_UU_RPT_LDGR .ledger
, v_PS_UU_RPT_LDGR .BUSINESS_UNIT
, v_PS_UU_RPT_LDGR .FUND_CODE
, v_PS_UU_RPT_LDGR .DEPTID
, v_PS_UU_RPT_LDGR .PROGRAM_CODE
, v_PS_UU_RPT_LDGR .PROJECT_ID
, v_PS_UU_RPT_LDGR .ACCOUNT
, v_PS_UU_RPT_LDGR .DESCR
, v_PS_UU_RPT_LDGR .UU_CUST_DESCR
, v_PS_UU_RPT_LDGR .CLASS_FLD
, v_PS_UU_RPT_LDGR .uu_deptid_tran
, v_PS_UU_RPT_LDGR .UU_FIN_PROG_CLAS
, v_PS_UU_RPT_LDGR .UU_PROG_NME
, v_PS_UU_RPT_LDGR .FISCAL_YEAR
, v_PS_UU_RPT_LDGR .ACCOUNTING_PERIOD
, v_PS_UU_RPT_LDGR .UU_ORIG_BUD_AMT
, v_PS_UU_RPT_LDGR .UU_RVSD_BUD_AMT
, v_PS_UU_RPT_LDGR .UU_ENCB_AMT
, v_PS_UU_RPT_LDGR .UU_ACTL_AMT;
EXIT WHEN CUR_VAR%NOTFOUND;
Explicit Cursor Example Continued
/*OPEN CUR_SUM_DATA WHICH RUNS THE SQL CACHES DATA
AND FETCH THE CACHED DATA OR THE RESULTS FROM
THE SQL INTO THE VARIABLES*/
IF CUR_SUM_DATA%ISOPEN THEN
CLOSE CUR_SUM_DATA;
END IF;
OPEN CUR_SUM_DATA;
LOOP
v_counter := v_counter +1;
FETCH CUR_SUM_DATA
INTO
v_PS_UU_RPT_LDGR .UU_CM_BUDG
, v_PS_UU_RPT_LDGR .uu_fytd_budg
, v_PS_UU_RPT_LDGR .uu_fy_budg
, v_PS_UU_RPT_LDGR .uu_ptd_budg
, v_PS_UU_RPT_LDGR .uu_cm_enc
, v_PS_UU_RPT_LDGR .uu_fytd_enc
, v_PS_UU_RPT_LDGR .uu_ptd_enc
, v_PS_UU_RPT_LDGR .uu_cm_actl
, v_PS_UU_RPT_LDGR .uu_fytd_actl
, v_PS_UU_RPT_LDGR .uu_ptd_actl;
EXIT WHEN CUR_SUM_DATA%NOTFOUND;
Explicit Cursor Example Continued
/*INSERT NEW DATA INTO THE TABLE */
INSERT INTO ps_uu_rpt_ldgr_fnl
(ledger
,BUSINESS_UNIT
,FUND_CODE
,DEPTID
,PROGRAM_CODE
…. )
VALUES
(v_PS_UU_RPT_LDGR .ledger
, v_PS_UU_RPT_LDGR .BUSINESS_UNIT
, v_PS_UU_RPT_LDGR ._FUND_CODE
, v_PS_UU_RPT_LDGR .DEPTID
, v_PS_UU_RPT_LDGR .PROGRAM_CODE
….);
IF mod(c_CUR_OTHER_VALUES%ROWCOUNT, 1000)=0
THEN
COMMIT;
END IF;
END LOOP;
END LOOP;
COMMIT;
IF c_CUR_SUM_DATA%ISOPEN THEN
CLOSE c_CUR_SUM_DATA;
END IF;
IF c_CUR_OTHER_VALUES%ISOPEN THEN
CLOSE c_CUR_OTHER_VALUES;
END IF;
Exceptions
Example
EXCEPTION
WHEN CURSOR_ALREADY_OPEN
THEN
CLOSE c_CUR_SUM_DATA;
CLOSE c_CUR_OTHER_VALUES;
WHEN OTHERS
THEN
IF V_ERROR_CODE > 0
THEN
ROLLBACK;
INSERT INTO ps_uu_rpt_ldgr_error(UU_BLOCK_NAME
,UU_ERR_CODE
,UU_ERR_MSG
,UU_DATE)
VALUES (V_BLOCK_NAME
,V_ERROR_CODE
,V_ERROR_MSG
,SYSDATE);
END IF;
CLOSE c_CUR_SUM_DATA;
CLOSE c_CUR_OTHER_VALUES;
END;
/
Merge
Example:
DELARE
v_empno emp.empno%TYPE := 120;
BEGIN
MERGE INTO copy_emp c
USING emp e
ON (e.empno = v_empno)
WHEN MATCHED THEN
UPDATE SET
c.first_name = e.first_name,
c.last_name = e.last_name,
c.email = e.email,
….
WHEN NOT MATCHED THEN
INSERT VALUES(e.empid, e.first_name, e.last_name, …, e.deptno);
Conditional Logic
 IF-THEN-END IF
 IF-THEN-ELSE-END IF
 IF-THEN-ELSIF-END IF
Conditional Logic Example
Example
IF-THEN-END IF
IF v_ename = ‘SMITH’ THEN
v_job := ‘SALES’;
v_deptno := 100;
END IF:
IF-THEN-ELSE-END IF
IF v_ename = ‘CLARK’ THEN
v_job := ‘ADVERTISING’;
ELSE
v_job := ‘CLERK’;
END IF;
IF-THEN-ELSIF-END IF
IF v_deptno = 80 THEN
v_bonus := 1000;
ELSIF v_depton = 90 THEN
v_bonus :=1200;
ELSE
v_bonus :=1100;
Case Expressions
 Choose the result from a list of items
 The result the CASE expression uses a selector
 Each selector is followed by one or more WHEN
clauses
Case Expressions Example
Example
DECLARE
v_score student.score%TYPE;
v_grade CHAR(1);
BEGIN
v_grade :=
CASE
WHEN v_score <=100 and >= 90 THEN ‘A’
WHEN v_score <=89 and >= 80 THEN ‘B’
WHEN v_score <=79 and >= 70 THEN ‘C’
WHEN v_score <=69 and >= 60 THEN ‘D’
WHEN v_score <=59 THEN ‘F’
ELSE ‘W’
END;
Loops
 Simplest form BASIC LOOP
Example
DECLARE
v_country_id location.country_id%TYPE := ‘WA’;
v_location_id location.location_id%TYPE;
v_city location.city%TYPE := ‘SEATTLE’;
v_counter NUMBER(2) := 1;
BEGIN
SELECT MAX(location_id) INTO v_location_id
FROM location
WHERE country_id = v_country_id;
LOOP
INSERT INTO location(location_id, city, country_id)
VALUES((v_location_id + v_counter), v_city, v_country_id);
v_counter := v_counter + 1;
EXIT WHEN v_counter > 3;
END LOOP;
END;
/
While Loop

 Pre-test for the condition and execute while a

condition is TRUE
 Post-test for the condition having the WHILE
LOOP until the condition tests for FALSE
While Loop Example
DECLARE
v_country_id location.country_id%TYPE := ‘WA’;
v_location_id location.location_id%TYPE;
v_city location.city%TYPE := ‘SEATTLE’;
v_counter NUMBER(2) := 1;
BEGIN
SELECT MAX(location_id) INTO v_location_id
FROM location
WHERE country_id = v_country_id;
WHILE v_counter <= 3 LOOP
INSERT INTO location(location_id, city, country_id)
VALUES((v_location_id + v_counter), v_city, v_country_id);
v_counter := v_counter + 1;
END LOOP;
END;
/
For Loop
 FOR LOOP has a control statement before the
LOOP keyword to determine the number of
iterations
 FOR LOOPS are used when the number of
iterations is known prior to processing
 Great to use in cursors because the FOR LOOP will
automatically OPEN and FETCH
For Loop Example
DECLARE
CURSOR sal_cursor IS
SELECT e.deptno, empno, last_name, sal
FROM emp e, dept d
WHERE
d.deptno = e.deptno
AND d.deptno = 60;
FOR UPDATE OF sal NOWAIT;
BEGIN
FOR emp_record in sal_cursor
LOOP
IF emp_record.sal < 6000 THEN
UPDATE emp
SET sal = emp_record.sal * 1.10
WHERE CURRENT OF sal_cursor;
END IF;
END LOOP;
END;
/
Executing PL/SQL
 Invoke PL/SQL anonymous blocks straight from
SQL*PLUS
 Run PL/SQL anonymous blocks by embedding the
inside other programs
 Stored procedure or function you can call it in the
following ways:
 SQL*PLUS or iSQL*Plus
 Oracle Development tools (Oracle Forms Developer)
 Another procedure
Executing PL/SQL Example
 Anonymous Block
@scriptname.sql
 From SQL*Plus
EXECUTE execution_one
 Stored Procedure
execution_one;
 From Another Procedure
CREATE OR REPLACE PROCEDURE emp_leave
(p_id IN emp.emp_id%TYPE)
IS
BEGIN
DELETE FROM emp
WHERE emp_id = p_id;
log_execution;
END
leave_emp;
PL/SQL Tips
 Naming Conventions
 V_ variables
 P_ parameters
 C_ constants
 CU_ cursors
 Assignment Statement
 :=
 Comparison Operators
 =
 <
 >
 <>
 Termination Statement
 ;
 PL/SQL is not Case Sensitive
 Note: Only Conditional Statements in WHERE Clause are Case Sensitive
 WHEN OTHERS
 List Last or it Will not Process any other Exceptions
Summary
 Powerful language native to the Oracle Server
 Move your programs to any host environment
 OS
 or platform that supports Oracle server.
 Grow in your database regardless of the front-end
development platform chosen
 PL/SQL can be written Object Oriented with the use
of packages
Summary Continued
 Language able to handle
 Conditional logic
 Iterative control
 Cursors
 Exception handling
 LOB data types
 Dynamic variable
 Easy maintenance
 Improved data security and integrity
 Performance, and code clarity