Practical Web Programming

Tuesday, February 05, 2008

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;

0 comments:

Recent Post