Oracle PL/SQL (10.2)
Block Structure
[DECLARE -- declarations] BEGIN [EXCEPTION -- handlers] END;
Variables
Declaring Variables
* SQL data types
DECLARE part_no NUMBER(6); part_name VARCHAR2(20); in_stock BOOLEAN; part_price NUMBER(6,2); part_desc VARCHAR2(50);
* %TYPE, %ROWTYPE
v_last_name employees.last_name%TYPE; dept_rec departments%ROWTYPE;
DECLARE CURSOR c1 IS SELECT last_name, salary, hire_date, job_id FROM employees WHERE employee_id = 120; -- declare record variable that represents a row fetched from the employees table employee_rec c1%ROWTYPE; BEGIN -- open the explicit cursor and use it to fetch data into employee_rec OPEN c1; FETCH c1 INTO employee_rec; DBMS_OUTPUT.PUT_LINE('Employee name: ' || employee_rec.last_name); END;
Declaring Constants
Use 'CONSTANT' qualifier.
credit_limit CONSTANT NUMBER := 5000.00;
Assign Values to Variables
* Use assignment operator ':='
DECLARE wages NUMBER; hours_worked NUMBER := 40; hourly_salary NUMBER := 22.50; bonus NUMBER := 150; country VARCHAR2(128); counter NUMBER := 0; done BOOLEAN; valid_id BOOLEAN; emp_rec1 employees%ROWTYPE; emp_rec2 employees%ROWTYPE; TYPE commissions IS TABLE OF NUMBER INDEX BY PLS_INTEGER; comm_tab commissions;
* Use select into
DECLARE bonus NUMBER(8,2); emp_id NUMBER(6) := 100; BEGIN SELECT salary * 0.10 INTO bonus FROM employees WHERE employee_id = emp_id; END;
* Pass values into PL/SQL as OUT or IN OUT parameters
DECLARE new_sal NUMBER(8,2); emp_id NUMBER(6) := 126; PROCEDURE adjust_salary(emp_id NUMBER, sal IN OUT NUMBER) IS emp_job VARCHAR2(10); avg_sal NUMBER(8,2);
Bind Variables
Use bind variables to reuse SQL statements.
'DELETE FROM employees WHERE employee_id = :id' USING emp_id;
Control Structures
IF THEN ELSE And CASE
DECLARE jobid employees.job_id%TYPE; empid employees.employee_id%TYPE := 115; sal employees.salary%TYPE; sal_raise NUMBER(3,2); BEGIN SELECT job_id, salary INTO jobid, sal FROM employees WHERE employee_id = empid; CASE WHEN jobid = 'PU_CLERK' THEN IF sal < 3000 THEN sal_raise := .12; ELSE sal_raise := .09; END IF; WHEN jobid = 'SH_CLERK' THEN IF sal < 4000 THEN sal_raise := .11; ELSE sal_raise := .08; END IF; WHEN jobid = 'ST_CLERK' THEN IF sal < 3500 THEN sal_raise := .10; ELSE sal_raise := .07; END IF; ELSE BEGIN DBMS_OUTPUT.PUT_LINE('No raise for this job: ' || jobid); END; END CASE; UPDATE employees SET salary = salary + salary * sal_raise WHERE employee_id = empid; COMMIT; END;
FOR LOOP
CREATE TABLE sqr_root_sum (num NUMBER, sq_root NUMBER(6,2), sqr NUMBER, sum_sqrs NUMBER); DECLARE s PLS_INTEGER; BEGIN FOR i IN 1..100 LOOP s := (i * (i + 1) * (2*i +1)) / 6; -- sum of squares INSERT INTO sqr_root_sum VALUES (i, SQRT(i), i*i, s ); END LOOP; END;
WHILE LOOP
WHILE sal <= 15000 LOOP -- loop until sal > 15000 SELECT salary, manager_id, last_name INTO sal, mgr_id, lname FROM employees WHERE employee_id = mgr_id; END LOOP;
EXIT WHEN
LOOP counter := counter + 1; total := total + counter * counter; -- exit loop when condition is true EXIT WHEN total > 25000; END LOOP;
Subprograms: Procedures and Functions
* Subprograms can be procedures and functions.
- Functions can return a value, procedure not. Procedures can only have OUT parameters.
- Function can be used in a select statement, procedures not.
Packages
* Packages related types and procedures together.
* Define package
CREATE OR REPLACE PACKAGE emp_actions AS -- package specification PROCEDURE hire_employee (employee_id NUMBER, last_name VARCHAR2, first_name VARCHAR2, email VARCHAR2, phone_number VARCHAR2, hire_date DATE, job_id VARCHAR2, salary NUMBER, commission_pct NUMBER, manager_id NUMBER, department_id NUMBER); PROCEDURE fire_employee (emp_id NUMBER); FUNCTION num_above_salary (emp_id NUMBER) RETURN NUMBER; END emp_actions; / CREATE OR REPLACE PACKAGE BODY emp_actions AS -- package body -- code for procedure hire_employee PROCEDURE hire_employee (employee_id NUMBER, last_name VARCHAR2, first_name VARCHAR2, email VARCHAR2, phone_number VARCHAR2, hire_date DATE, job_id VARCHAR2, salary NUMBER, commission_pct NUMBER, manager_id NUMBER, department_id NUMBER) IS BEGIN INSERT INTO employees VALUES (employee_id, last_name, first_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id); END hire_employee; -- code for procedure fire_employee PROCEDURE fire_employee (emp_id NUMBER) IS BEGIN DELETE FROM employees WHERE employee_id = emp_id; END fire_employee; -- code for function num_above salary FUNCTION num_above_salary (emp_id NUMBER) RETURN NUMBER IS emp_sal NUMBER(8,2); num_count NUMBER; BEGIN SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id; SELECT COUNT(*) INTO num_count FROM employees WHERE salary > emp_sal; RETURN num_count; END num_above_salary; END emp_actions;
* Use package
CALL emp_actions.hire_employee(300, 'Belden', 'Enrique', 'EBELDEN', '555.111.2222', '31-AUG-04', 'AC_MGR', 9000, .1, 101, 110); BEGIN DBMS_OUTPUT.PUT_LINE( 'Number of employees with higher salary: ' || TO_CHAR(emp_actions.num_above_salary(120))); emp_actions.fire_employee(300); END;
Data Abstraction
Cursors
* Points to a private SQL area.
DECLARE CURSOR c1 IS SELECT last_name, salary, hire_date, job_id FROM employees WHERE employee_id = 120; -- declare record variable that represents a row fetched from the employees table employee_rec c1%ROWTYPE; BEGIN -- open the explicit cursor and use it to fetch data into employee_rec OPEN c1; FETCH c1 INTO employee_rec; DBMS_OUTPUT.PUT_LINE('Employee name: ' || employee_rec.last_name); END;
Collections
* Ordered group of elements, all of the same type.
* Declare using TYPE definition.
VARRAY
* PL/SQL equivalent of an Array
* Can not have gaps in subscripts, i.e. is dense.
TYPE Calendar IS VARRAY(366) OF DATE;
Nested Table
* PL/SQL equivalent of a Set
* One dimension array with no declared number of elements.
* Can have gaps in subscripts.
* Can be stored in database column.
TYPE nested_type IS TABLE OF VARCHAR2(30);
Associative Array
* aka index-by tables
* PL/SQL equivalent of Hashtable
* Can not be stored in database column.
DECLARE TYPE EmpTabTyp IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER; emp_tab EmpTabTyp; BEGIN /* Retrieve employee record. */ SELECT * INTO emp_tab(100) FROM employees WHERE employee_id = 100; END; /
DECLARE TYPE staff_list IS TABLE OF employees.employee_id%TYPE; staff staff_list; lname employees.last_name%TYPE; fname employees.first_name%TYPE; BEGIN staff := staff_list(100, 114, 115, 120, 122); FOR i IN staff.FIRST..staff.LAST LOOP SELECT last_name, first_name INTO lname, fname FROM employees WHERE employees.employee_id = staff(i); DBMS_OUTPUT.PUT_LINE ( TO_CHAR(staff(i)) || ': ' || lname || ', ' || fname ); END LOOP; END; /
DECLARE TYPE nested_type IS TABLE OF VARCHAR2(30); TYPE varray_type IS VARRAY(5) OF INTEGER; TYPE assoc_array_num_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER; TYPE assoc_array_str_type IS TABLE OF VARCHAR2(32) INDEX BY PLS_INTEGER; TYPE assoc_array_str_type2 IS TABLE OF VARCHAR2(32) INDEX BY VARCHAR2(64); v1 nested_type; v2 varray_type; v3 assoc_array_num_type; v4 assoc_array_str_type; v5 assoc_array_str_type2; BEGIN -- an arbitrary number of strings can be inserted v1 v1 := nested_type('Shipping','Sales','Finance','Payroll'); v2 := varray_type(1, 2, 3, 4, 5); -- Up to 5 integers v3(99) := 10; -- Just start assigning to elements Defining Collection Types AND Declaring Collection VARIABLES 5-8 Oracle DATABASE PL/SQL User’s Guide AND Reference v3(7) := 100; -- Subscripts can be any integer values v4(42) := 'Smith'; -- Just start assigning to elements v4(54) := 'Jones'; -- Subscripts can be any integer values v5('Canada') := 'North America'; -- Just start assigning to elements v5('Greece') := 'Europe'; -- Subscripts can be string values END;
Records
* Composite data structures (like C struct)
* Fields can have different data types.
* Use %ROWTYPE to declare a record that represents a row in a table.
DECLARE TYPE timerec IS RECORD (hours SMALLINT, minutes SMALLINT); TYPE meetin_typ IS RECORD ( date_held DATE, duration timerec, -- nested record location VARCHAR2(20), purpose VARCHAR2(50)); BEGIN -- NULL does nothing but allows unit to be compiled and tested NULL; END; /
Object Types
* Contains both data (attributes) and functions (methods).
CREATE TYPE bank_account AS OBJECT ( acct_number NUMBER(5), balance NUMBER, STATUS VARCHAR2(10), MEMBER PROCEDURE open (SELF IN OUT NOCOPY bank_account, amount IN NUMBER), MEMBER PROCEDURE close (SELF IN OUT NOCOPY bank_account, num IN NUMBER, amount OUT NUMBER), MEMBER PROCEDURE deposit (SELF IN OUT NOCOPY bank_account, num IN NUMBER, amount IN NUMBER), MEMBER PROCEDURE withdraw (SELF IN OUT NOCOPY bank_account, num IN NUMBER, amount IN NUMBER), MEMBER FUNCTION curr_bal (num IN NUMBER) RETURN NUMBER ); /
Exceptions
* Use RAISE statement to throw user defined exceptions.
* Use EXCEPTION to catch the exceptions.
EXCEPTION -- exception-handling part starts here WHEN comm_missing THEN DBMS_OUTPUT.PUT_LINE('This employee does not receive a commission.'); commission := 0; WHEN OTHERS THEN NULL; -- for other exceptions do nothing