OracleBlog

Since there was some interest in the "object" template,

http://thinkoracle.blogspot.com/2005/06/oop-in-plsql-yep.html

and since I've been dry on ideas lately because I haven't been working on non-Oracle things the last week or two, I decided to share another one of my templates. This time: stored procedures. Enjoy!

SET SERVEROUTPUT ON;

-- 'NOCOPY' makes it a pointer (pass by reference) - faster.
-- 'DETERMINISTIC' means the output is the same for every input - allows caching (for return type tables)
-- CREATE OR REPLACE FUNCTION MyFunc (p_something IN OUT NOCOPY Transactions.number%TYPE)
-- RETURN BOOLEAN DETERMINISTIC
-- 'DEFAULT' is the same as ':=' for both here and DECLARE
CREATE OR REPLACE PROCEDURE MyProc (p_something IN Transactions.number%TYPE DEFAULT 1)
-- The following says it is independent of anything calling it (eg: rollbacks, etc)
-- IS PRAGMA AUTONOMOUS_TRANSACTION
AS
[[BlockName]]
-- If its an anonymous block:
-- DECLARE
v_datetime TIMESTAMP;
v_transaction Transactions.number%TYPE := 0;
v_the_date_is CONSTANT VARCHAR2(12) := 'The date is ';

-- Create an exception, the pragma is optional
v_my_exception EXCEPTION;
PRAGMA EXCEPTION_INIT(v_my_exception, 100);

-- subprograms (functions) MUST be declared last
PROCEDURE NothingReally IS BEGIN NULL; END NothingReally;
BEGIN
-- SET TRANSACTION READ ONLY; -- Use consistent snapshot of the database
-- SET TRANSACTION READ WRITE; -- The default. Turns "off" the Read Only
-- SET TRANSACTION ISOLATION LEVEL READ COMMITTED
-- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
-- SET TRANSACTION USE ROLLBACK SEGMENT

-- This is a comment
SELECT systime INTO BlockName.v_datetime FROM dual;
DBMS_OUTPUT.PUT_LINE(v_the_date_is || v_datetime);

[[LoopName]]
IF 1=1
THEN NULL;
ELSIF 1=2
THEN RAISE NO_DATA_FOUND;
ELSE
THEN NothingReally;
END IF;

CASE
WHEN 1=1 THEN NULL;
-- Application Errors have to be -20000 to -20999 inclusive and can't be caught specifically
WHEN 1=2 THEN RAISE_APPLICATION_ERROR(-20000, 'Error: '||v_the_date_is||' BAH');
ELSE NULL;
END CASE;

LOOP
EXIT WHEN 1=1;
END LOOP;

FOR v_transaction IN 0 .. 10
LOOP
NULL;
END LOOP;

WHILE 1=2
LOOP
RAISE v_my_exception;
END LOOP;

COMMIT;
EXCEPTION
-- This only covers errors raised after BEGIN (but NOT in 'DECLARE'!)
-- You can "OR" exceptions.
-- An exception can't be in more than 1 block
WHEN v_my_exception
THEN NULL;
-- This is optional but good practice.
-- Unhandled exceptions fall through to the next block or statement
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
END MyProc;
/