Persistent Stored Modules Stored Procedures PSM cs 3431

Persistent Stored Modules (Stored Procedures) : PSM cs 3431

Stored Procedures l What is stored procedure? l l l SQL allows you to define procedures and functions and store them in the database server Executed by the database server Advantages l l l Complex application logic executed while “close” to the data: usually implies efficiency Contrast with tuple-at-a time processing by JDBC through “cursors” Reuse the application logic cs 3431

Stored Procedures in Oracle l l Oracle supports a slightly different version of PSM called PL/SQL my. SQL support is only in later versions cs 3431

Defining a stored procedure CREATE PROCEDURE <procedure. Name> [(<param. List>)] <local. Declarations> <procedure. Body>; A parameter in the param. List is specified as: <name> <mode> <type> <mode> is one of {IN, OUT, INOUT} eg: val 1 IN int You can drop procedure by DROP PROCEDURE <procedure. Name> In PL/SQL, you can replace procedure by CREATE OR REPLACE PROCEDURE <procedure. Name> … cs 3431

PL/SQL Engine cs 3431

Example: Procedure in PSM CREATE PROCEDURE test. Procedure BEGIN INSERT INTO Student VALUES (5, ‘Joe’); END; Oracle PL/SQL: CREATE PROCEDURE test. Procedure IS BEGIN INSERT INTO Student VALUES (5, ‘Joe’); END; . run; cs 3431

More about Procedures l Calling Procedures call <procedure. Name> [(<param. List>)]; cs 3431

Example CREATE PROCEDURE test. Procedure (num IN int, name IN varchar) IS BEGIN /* Insert values */ INSERT INTO Student VALUES (num, name); END; cs 3431

Local Declarations Example: CREATE PROCEDURE test. Procedure (num IN int, name IN varchar) IS num 1 int; -- local variable BEGIN num 1 : = 10; INSERT INTO Student VALUES (num 1, name); END; cs 3431

Other PSM features Assignment statements: PL/SQL <var. Name> : = <expression> cs 3431

Control Structures: IF THEN ELSE IF <condition> THEN <statement. List> ELSIF … ELSE <statement. List> END IF; cs 3431

Loops LOOP <statement. List> END LOOP; To exit from a loop use EXIT; cs 3431

Loops: Example CREATE PROCEDURE test. Procedure (num IN int, name IN varchar) IS num 1 int; BEGIN num 1 : = 10; LOOP INSERT INTO Student VALUES (num 1, name); num 1 : = num 1 + 1; IF (num 1 > 15) THEN EXIT; END IF; END LOOP; END; cs 3431

Functions CREATE FUNCTION <function. Name> [(<param. List>)] RETURNS type IS <local. Declarations> BEGIN <function. Body>; END; You can call a function as part of an sql expression Drop a function: drop function <function. Name> cs 3431

Functions: Example CREATE FUNCTION test. Function RETURN int IS num 1 int; BEGIN SELECT MAX (s. Number) INTO num 1 FROM Student; RETURN num 1; END; SELECT * from Student where s. Number = test. Function (); cs 3431

Other useful tips l Oracle stores procedures and functions in catalog as relational tables. l l l Check user_procedures Check user_functions You may run queries against them such as l l describe user_procedures; select object_name from user_procedures; cs 3431

Summary : Stored Procedures l l l Used stand-alone on explicit call Used functions in WHERE clause of SQL statement Used in body of triggers cs 3431