Oracle PL/SQL Basics: A Simple Tutorial on Oracle PL/SQL

1. Using SQL-Plus

SQL-Plus is a query command line utility which has some powerful formatting capabilities.

Basic SQL-Plus Command

;                     Command line terminator
/                     Execute the current batch of commands
SET SERVEROUTPUT ON   Allow messages from PL-SQL to be displayed
SHOW ERRORS           Show errors from last batch
EDIT                  Run editor, and load buffer
CLEAR BUFFER          Clear buffer commands
&                     Prompt for value
@                     Run commands in @filename

Prompt for process id, and kill

alter system kill session 'Victim';

Run commands in tables.sql

@tables.sql;

2. Creating a stored procedure

Below is a simple stored procedure which deletes an invoice.
Note:
- variable declaration placement
- the syntax for comments is /* --- */, or --
- ALL select statements must have an into statement for the result set. Oracle stored procedures must use "out" variables to return results to client programs.
- the declaration of INV_ID1 uses the column def as a prototype

CREATE OR REPLACE PROCEDURE PROC_DELETE_INVOICE(USERID1 VARCHAR2,
 INV_ID1 INVOICE.INV_ID%TYPE) AS INV_COUNT NUMBER;
BEGIN
 INV_COUNT := 0;
 /* check if invoice exists */
 SELECT COUNT(*) INTO INV_COUNT 
          FROM INVOICE WHERE INV_ID = INV_ID1;
 IF INV_COUNT > 0 THEN
 DELETE FROM INVOICE WHERE INV_ID = INV_ID1;
 COMMIT;
 END IF;
END;

3. Displaying output

All SELECT statements in PL-SQL must have an INTO clause; therefore another method is needed to display output to the console.

DBMS_OUTPUT.PUT_LINE('TEST OUTPUT');
salary := 24000;
dbms_output.put_line(salary);

4. Output variables

Output variables are used to return data to another procedure, or to an external application which has invoked the stored procedure.

Sample procedure header using output variables

TYPE INV_ARRAY IS TABLE OF NUMBER(8) INDEX BY BINARY_INTEGER;

CREATE OR REPLACE PROCEDURE PROC_GET_INV_NOS(
  USERID1 IN VARCHAR2,
  INV_IDS OUT INV_ARRAY)
AS . . .

5. Arrays and structures

Arrays and structures are implemented thought the use of "tables" and
"records" in PL-SQL.

Example of a simple record type

TYPE INVOICE_REC_TYPE IS RECORD(INV_ID   INVOICE.INV_ID%TYPE,
    INV_DT   INVOICE.INV_DT%TYPE);

Array declaration

TYPE NAME_TABLE_TYPE IS TABLE OF VARCHAR2(20) 
INDEX BY BINARY_INTEGER;
NAME_TABLE NAME_TABLE_TYPE;

Array subscripting

I := I + 1;
NAME_TABLE(I) := 'JSMITH';

6. Conditionals

Sample formats of conditional branching are given below:

IF statement

IF 'condition' THEN 'statement' ;

IF .. END IF statement

IF 'condition' THEN
'statements' ;
END IF;

IF .. ELSIF .. END IF statement

IF 'condition' THEN
 'statements';
ELSIF 'condition' THEN
 'statements';
END IF;

Sample statement, note the pipes for concatenation

IF (COUNT1 = 0) AND (COUNT2 > 0) THEN
 RETMSG := 'Security attributes have not been assigned,
     ' || 'you are restricted.';
ELSE
 RETMSG := 'You are OK';
END IF;

7. Looping

WHILE (I <>/* put command here */
 I = I + 1;
END LOOP;

8. Packages

A package is a construct which bounds related procedures and functions together. Variables declared in the declaration section of a package can be shared among the procedures/functions in the body of the package.

Package

CREATE OR REPLACE PACKAGE INVPACK IS
 /* function */
 FUNCTION COUNTINV(SALESREP IN VARCHAR2) RETURN INTEGER;
 /* procedure */
 PROCEDURE PURGEINV(INV_ID IN INTEGER);
END INVPACK;

Package body

CREATE OR REPLACE PACKAGE BODY INVPACK IS
 COUNT1 NUMBER;

 /* function */
 FUNCTION COUNTINV(SALESREP IN VARCHAR2) RETURN INTEGER IS
 BEGIN
  SELECT COUNT(*) INTO COUNT1
  FROM INVOICE
  WHERE SALES_REP_ID = SALESREP;

  RETURN COUNT1;
 END COUNTINV;

 /* procedure */
 PROCEDURE PURGEINV(INV_ID1 IN INTEGER) IS
 BEGIN
  DELETE FROM INVOICE WHERE INV_ID = INV_ID1;
 END PURGEINV;
END INVPACK;

9. Cursors

Sample #1: This example depicts dbase-style row processing.

PROCEDURE PROC_SCAN_INVOICES(EXPIRE_DT IN DATE) IS
 CURSOR INVOICE_CUR IS SELECT INV_ID, INV_DT FROM INVOICE;

 TYPE INVOICE_REC_TYPE IS RECORD(INV_ID INVOICE.INV_ID%TYPE,
   INV_DT INVOICE.INV_DT%TYPE);

 INVOICE_REC INVOICE_REC_TYPE;
 BEGIN
 FOR INVOICE_REC1 IN INVOICE_CUR LOOP
 IF INVOICE_REC.INV_DT <> EXPIRE_DT THEN
   DELETE FROM INVOICE WHERE INV_ID = INV_REC.INV_ID;
   DBMS_OUTPUT.PUT_LINE('INVOICE DELETETED:');
   DBMS_OUTPUT.PUT_LINE(INV_REC.INV_ID);
 END IF;
 END LOOP;
END PROC_SCAN_INVOICES;

Sample #2: This example is a more traditional "fetch" approach.

PROCEDURE UPDATE_LOAD_CENTER_WORK_ORDER(
    REC_NO IN MASTERTABLE.RECORDNUMBER%TYPE,
    LCID   IN MASTERTABLE.LC_NUMBER%TYPE,
    WO_NO  IN MASTERTABLE.WO_NUMBER%TYPE,
    TRS_NO IN MASTERTABLE.TRS_NUMBER%TYPE) IS

 CURSOR CURMAXID IS
  SELECT MAX(RECORDNUMBER) + 1 FROM AMFM.GIS_LCWOMASTER;
 NEXTRECNO MASTERTABLE.RECORDNUMBER%TYPE;

BEGIN
 IF REC_NO = 0 THEN
 OPEN CURMAXID;
 FETCH CURMAXID
 INTO NEXTRECNO;
 CLOSE CURMAXID;
 IF NEXTRECNO IS NULL THEN
   NEXTRECNO := 1;
 END IF;

 INSERT INTO AMFM.GIS_LCWOMASTER A
   (RECORDNUMBER, LC_NUMBER, WO_NUMBER, TRS_NUMBER, DATE_ENCODED)
 VALUES
   (NEXTRECNO, LCID, WO_NO, TRS_NO, SYSDATE);
 ELSE
 UPDATE AMFM.GIS_LCWOMASTER
   SET LC_NUMBER = LCID, WO_NUMBER = WO_NO, TRS_NUMBER = TRS_NO
 WHERE RECORDNUMBER = REC_NO;
 END IF;
END UPDATE_LOAD_CENTER_WORK_ORDER;

Sample #3: This example returns a cursor. Note: Execute this two sample separately.

/* package declaration */
CREATE OR REPLACE PACKAGE load_center_pkg  IS
 TYPE refCursor IS REF CURSOR;

 PROCEDURE get_all_transformers(pCursor OUT refCursor);
 END load_center_pkg;

 /* package body declaration */
 CREATE OR REPLACE PACKAGE BODY load_center_pkg  IS
 PROCEDURE GET_ALL_TRANSFORMERS(PCURSOR OUT REFCURSOR) IS
 BEGIN
 OPEN PCURSOR FOR
   SELECT A.XFORMER_ID,
    A.PRIVATE_OWN,
    A.PHASE, A.BRAND
   FROM TRANSFORMER_MAST A;
 END GET_ALL_TRANSFORMERS;
END load_center_pkg;

10. Exception Handling

The following block could appear at the end of a stored procedure:

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('End of data !!);
  WHEN OTHERS THEN
    BEGIN
      DBMS_OUTPUT.PUT_LINE('OTHER CONDITION OCCURRED !');
    END;