Database Application Development

Database Application Development Chapter 6 PSM (Stored Procedures)

Stored Procedures • What is a stored procedure: • SQL allows you to define procedures and functions and store in the DB server • Program executed through single SQL statement • Executed in process space by the database server • Advantages: • Can encapsulate application logic while staying “close” to the data (usually implies efficiency) • Reuse of application logic by different users • Avoid tuple-at-a-time return of records through cursors (as would be done by JDBC)

SQL/PSM Declare a stored procedure: CREATE PROCEDURE name(p1, p2, …, pn) local variable declarations procedure code for body; Declare a function: CREATE FUNCTION name (p1, …, pn) RETURNS sqlDataTypelocal variable declarations function code;

Stored Procedures: Examples CREATE PROCEDURE ShowNumReservations SELECT S.sid, S.sname, COUNT(*)FROM Sailors S, Reserves RWHERE S.sid = R.sidGROUP BY S.sid, S.sname;

Stored Procedures: Examples Stored procedures can have parameters: • <name> <mode> <type> <mode> is one of {IN, OUT, INOUT} eg: IN val1 int CREATE PROCEDURE IncreaseRating(IN sailor_sid INTEGER, IN increase INTEGER) UPDATE Sailors SET rating = rating + increaseWHERE sid = sailor_sid;

Stored Procedures: Examples Stored procedure do not have to be in SQL: CREATE PROCEDURE TopSailors(IN num INTEGER) LANGUAGE JAVA EXTERNAL NAME “file:///c:/storedProcs/rank.jar”

Example: Procedure in PSM CREATE PROCEDURE testProcedure BEGIN INSERT INTO Student VALUES (5, ‘Joe’); END; Oracle’s version of PSM : CREATE PROCEDURE testProcedure IS BEGIN INSERT INTO Student VALUES (5, ‘Joe’); END; . run; NOTE: mySQL later versions have some initial support.

Also You can drop procedure by DROP PROCEDURE <procedureName> In PL/SQL, you can replace procedure by CREATE OR REPLACE PROCEDURE <procedureName> …

Calling Stored Procedures Calling Procedures call <procedureName> [(<paramList>)]; EXEC SQL BEGIN DECLARE SECTION Int sid; Int rating; EXEC SQL END DECLARE SECTION // now increase rating of this sailor EXEC CALL IncreaseRating(:sid,:rating); Embedded SQL:

Stored Procedures :Persistent Stored Modules (PSM)

SQL/PSM • Most DBMSs allow users to write stored procedures in a simple general-purpose language (close to SQL) • SQL/PSM standard is a representative of vendor-specific languages. • Oracle supports a slightly different version of PSM called PL/SQL

Local Declarations CREATE PROCEDURE testProcedure (num IN int, name IN varchar) IS BEGIN INSERT INTO Student VALUES (num, name); END;

Local Declarations CREATE PROCEDURE testProcedure (num IN int, name IN varchar) IS num1 int; -- local variable BEGIN num1 := 10; INSERT INTO Student VALUES (num1, name); END;

Other PSM features Assignment statements: PL/SQL <varName> := <expression>

Control Structures: IF THEN ELSE IF <condition> THEN <statementList> ELSIF <condition> THEN <statementList> ELSIF … ELSE <statementList> END IF;

Loops LOOP <statementList> END LOOP; To exit from a loop use EXIT;

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

FOR Loops FOR i in [REVERSE] <lowerBound> .. <upperBound> LOOP <statementList> END LOOP Example: FOR i in 1 .. 5 LOOP INSERT INTO Student (sNumber) values (10 + i); END LOOP;

WHILE LOOPS WHILE <condition> LOOP <statementList> END LOOP;

Functions CREATE FUNCTION <functionName> [(<paramList>)] RETURNS type AS <localDeclarations> BEGIN <functionBody>; END; You can call a function as part of an SQL expression Drop a function: drop function <functionName>

Functions: Example CREATE FUNCTION testFunction RETURN int AS num1 int; BEGIN SELECT MAX (sNumber) INTO num1 FROM Student; RETURN num1; END; SELECT * from Student where sNumber = testFunction ();

SQL/PSM Example CREATE FUNCTION rate Sailor (IN sailorId INTEGER) RETURNS INTEGER DECLARE rating INTEGER; DECLARE numRes INTEGER; BEGIN SET numRes = (SELECT COUNT(*) FROM Reserves R WHERE R.sid = sailorId); IF (numRes > 10) THEN rating =1; ELSE rating = 0; END IF; RETURN rating; END

Other Info Re Oracle : • Oracle stores procedures and functions in catalog as relational tables: • Check user_procedures • Check user_functions • You may run queries against them such as: • describe user_procedures; • select object_name from user_procedures;

Summary : Stored Procedures • Stored procedures execute application logic directly at the server • SQL/PSM standard for writing stored procedural logic, namely, • Used stand-alone on explicit call • Used functions in WHERE clause of SQL statement • Used in body of triggers

Database Application Development