Oracle PL/SQL (10.2)

Posted on September 1, 2010 by Jianming Li

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;
SET SERVEROUT ON
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;
  LOOP
    FETCH c1 INTO employee_rec;
    EXIT WHEN c1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Employee name: ' || employee_rec.last_name);
  END LOOP;
  CLOSE c1;
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 IN 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
    err_num := SQLCODE;
    err_msg := SUBSTR(SQLERRM, 1, 100);
    INSERT INTO errors VALUES (err_num, err_msg);
    NULL; -- for other exceptions do nothing

References

* PL/SQL User’s Guide and Reference 10g Release 2 (10.2)

This entry was posted in pl/sql. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

CAPTCHA

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.