* »»]]> 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

References

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

* Time: Mondays and Wednesdays from 6:30 - 8:00pm. * Location: EDS field near the »»]]> Players

* Anirudh
* Sam
* Joseph
* Justin
* Collin
* James
* Kenan
* CJ
* Gabriel
* Richard
* Patrick

Weekly Practices

* Time: Mondays and Wednesdays from 6:30 - 8:00pm.
* Location: EDS field near the Wall Rd.

Game Schedule

* 9/11/2010: 10:15 am
* 9/18/2010: 10:15 am
* 9/25/2010: 11:30 am
* 10/2/2010: 10:15 am

* 10/16/2010: 9:00 am (Line the field)
* 10/23/2010: 11:30 am
* 10/30/2010: 10:15 am
* 11/6/2010: 9:00 am

Please Bring

* Soccer cleats
* Shinguard
* Soccer socks
* Size 4 soccer ball
* Uniform
* Water bottles
* Sun screen

* Compile the following PL/SQL »»]]> Here is a nice thread describing how to generate XML schema from Oracle tables. I tried the following function and it worked great.

* Compile the following PL/SQL function:

 
FUNCTION GEN_XML_SCHEMA(target_table varchar2) RETURN xmltype
AS
  xmlSchema XMLTYPE;
begin
  SELECT
    xmlElement(
      "xsd:schema",
      xmlAttributes(
        'http://www.w3.org/2001/XMLSchema' AS "xmlns:xsd",
        'http://xmlns.oracle.com/xdb' AS "xmlns:xdb",
        'http://' || target_table || '/xsd' AS "targetNamespace",
        'http://' || target_table || '/xsd' AS "xmlns:tns",
        'qualified' AS "elementFormDefault"
      ),
      xmlElement(
        "xsd:element",
        xmlAttributes(
          table_name || 'S' AS "name",
          'tns:' || table_name || 'S_T' AS "type"
        )
      ),
      xmlElement(
        "xsd:complexType",
        xmlAttributes (
          table_name || 'S_T' AS "name"
        ),
        xmlElement (
          "xsd:sequence",
          xmlElement (
            "xsd:element",
            xmlAttributes (
              table_name AS "name",
              'tns:' || table_name || '_T' AS "type",
              'unbounded' AS "maxOccurs"
            )
          )
        )
      ),
      xmlElement (
        "xsd:complexType",
        xmlAttributes (
          table_name || '_T' AS "name"
        ),
        xmlElement (
          "xsd:sequence",
          (
            xmlAgg(ELEMENT)
          )
        )
      )
    )
  INTO xmlSchema
  FROM (
    SELECT TABLE_NAME, INTERNAL_COLUMN_ID,
      case
        when DATA_TYPE IN ('VARCHAR2', 'CHAR') then
          xmlElement (
            "xsd:element",
            xmlattributes (
              column_name AS "name",
              decode(NULLABLE, 'Y', 0, 1) AS "minOccurs",
              column_name AS "xdb:SQLName",
              DATA_TYPE AS "xdb:SQLTYPE"
            ),
            xmlElement (
              "xsd:simpleType",
              xmlElement (
                "xsd:restriction",
                xmlAttributes (
                  'xsd:string' AS "base"
                ),
                xmlElement (
                  "xsd:maxLength",
                  xmlAttributes (
                  DATA_LENGTH AS "value"
                  )
                )
              )
            )
          )
        when DATA_TYPE = 'DATE' then
          xmlElement (
            "xsd:element",
            xmlattributes (
              column_name AS "name",
              --'xsd:dateTime' as "type",
              'xsd:date' AS "type",
              decode(NULLABLE, 'Y', 0, 1) AS "minOccurs",
              column_name AS "xdb:SQLName",
              DATA_TYPE AS "xdb:SQLTYPE"
            )
          )
        when DATA_TYPE = 'NUMBER' then
          xmlElement (
            "xsd:element",
            xmlattributes (
              column_name AS "name",
              decode(DATA_SCALE, 0, 'xsd:integer', 'xsd:double') AS "type",
              decode(NULLABLE, 'Y', 0, 1) AS "minOccurs",
              column_name AS "xdb:SQLName",
              DATA_TYPE AS "xdb:SQLTYPE"
            )
          )
        else
          xmlElement (
            "xsd:element",
            xmlattributes (
              column_name AS "name",
              'xsd:anySimpleType' AS "type",
              decode(NULLABLE, 'Y', 0, 1) AS "minOccurs",
              column_name AS "xdb:SQLName",
              DATA_TYPE AS "xdb:SQLTYPE"
            )
          )
      end ELEMENT
    FROM user_tab_cols c
    WHERE TABLE_NAME = target_table
    ORDER BY internal_column_id
  )
  GROUP BY TABLE_NAME;
 
  RETURN xmlSchema;
end;
 

* Run the function:

 
-- Complete schema
SELECT gen_xml_schema('MY_TABLE').extract('/*') FROM dual;
 
-- Select element and complexType
SELECT gen_xml_schema('RF_TAG_TXN').extract('/schema/element | /schema/complexType',
'xmlns="http://www.w3.org/2001/XMLSchema"') FROM dual;
 

* Generate schema from multiple tables:

 
CREATE OR REPLACE DIRECTORY dir_temp AS
    'C:\TEMP';
 
DECLARE
  TYPE tableNameArray IS varray(100) of VARCHAR2(64);
  tableNames tableNameArray;
 
  outputFileName VARCHAR2(100) := 'MY_SCHEMA.xsd';
  outputFileHandler UTL_FILE.file_type;
 
  tableXmlType xmlType;
  xmlRootElementBegin VARCHAR2(500) :=
    '<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    xmlns:xdb="http://xmlns.oracle.com/xdb"
    targetNamespace="http://my.db/xsd"
    xmlns:tns="http://my.db/xsd"
    elementFormDefault="qualified">';
  xmlRootElementEnd VARCHAR2(500) := '</xsd:schema>';
BEGIN
  -- Database tables
  tableNames := tableNameArray(
    'TABLE1',
    'TABLE2');
 
  -- Open schema file to write
  outputFileHandler := UTL_FILE.fopen('DIR_TEMP', outputFileName, 'W');
  -- Write root element begin tag
  UTL_FILE.put_line(outputFileHandler, xmlRootElementBegin);
 
  -- Loop through all tables
  FOR tblName IN 1 .. tableNames.count loop
    DBMS_OUTPUT.put_line('Processing table: ' || tableNames(tblName));
 
    -- Generate xml schema from each table
    SELECT gen_xml_schema(tableNames(tblName)).extract(
        '/schema/element|/schema/complexType',
        'xmlns="http://www.w3.org/2001/XMLSchema"')
    INTO tableXmlType
    FROM dual;
 
    -- Write xml schema
    IF tableXmlType IS NOT NULL THEN
      UTL_FILE.put_line(outputFileHandler, tableXmlType.getStringVal());
    END IF;
  END loop;
 
  -- Write root element end tag
  UTL_FILE.put_line(outputFileHandler, xmlRootElementEnd);
  -- Close file
  UTL_FILE.fclose(outputFileHandler);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line (DBMS_UTILITY.FORMAT_ERROR_STACK);
END;
/

* Partner link failover locations can be specified in bpel.xml