Making Functions (PL/SQL)

Note PL is exclusive to oracle

Anonymous Block - a set of PL/SQL blocks that can be compiled and executed immediately

Stored Procedures - named and precompiled PL/SQL blocks that can be stored and executed later

Nested Block

DECLARE
	CODE
BEGIN
	CODE
EXCEPTION
	CODE
END;

Declare Function

# typical to start para names with 'the' - i.e. 'theAccountNumber'

CREATE OR REPLACE PROCEDURE name (parameter INT, parameter DEC) AS
# local variables
varName CONSTANT INT := 9;
BEGIN 
	code
EXCEPTION
	code
END;
/

Call

# no need for ; since it is SQL+
execute funcName(23, 34)
# or PL/SQL
CALL functName(23, 34);

SELECT funcName(colName) FROM tableName;

Control statments

IF condition THEN
	code;
ELSIF condition THEN
	code
ELSE
	code
END IF;

Variables

theVarName char(3);

theVarName CONSTANT INT := 8;

varName tableName.colName%TYPE;
# same as varName INT; if col type is INT

theRowVar tableName%ROWTYPE;
# access theRowVar.colName
# like making a table for 1 row

Return

CREATE OR REPLACE PROCEDURE name (parameter INT, parameter DEC)
RETURN INT IS

BEGIN 
	CODE
	RETURN localVar;

Assigning a var to func return

# SQL plus
execute :varName := funcName(45)

#PL/SQL
CALL funcName() INTO :varName;

Putting data into a var (doesn't work if multiple values are returned)

CREATE OR REPLACE PROCEDURE name (parameter INT, parameter DEC) AS
theColNameVar tableName.colName%TYPE;

BEGIN
# putting column into a local variable
	SELECT colName INTO theColNameVar FROM tableName
	WHERE ...;
	
# print - remember 'set serveroutput on'
	dbms_output.put_line('string of text'||theColNameVar) 

Cursors

# array of col or rows
# DELCARE
# OPEN
# FETCH
# CLOSE

CREATE OR REPLACE PROCEDURE name (parameter INT, parameter DEC) AS

# DELCARE - this is the array it will search through (can store rows)
CURSOR nameOfCur IS
SELECT colName FROM tableName
WHERE ...;

# local vars...
aLocalVar tableName.colName%TYPE;

BEGIN
	# OPEN - the array
	OPEN nameOfCur;
LOOP
	# FETCH - note here we are just populating nameOfCur
	FETCH nameOfCur INTO aLocalVar, aLocalVar2;
	EXIT WHEN nameOfCur%NOTFOUND;
	# code goes here
	# code...
	# code...
END LOOP;
# CLOSE - dump the memory 
CLOSE nameOfCur;
END;
/ 

Slides Example