1 / 24

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

iwalls
Télécharger la présentation

Database Application Development

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database Application Development Chapter 6 PSM (Stored Procedures)

  2. 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)

  3. 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;

  4. 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;

  5. 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;

  6. 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”

  7. 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.

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

  9. 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:

  10. Stored Procedures :Persistent Stored Modules (PSM)

  11. 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

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

  13. 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;

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

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

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

  17. 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;

  18. 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;

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

  20. 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>

  21. 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 ();

  22. 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

  23. 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;

  24. 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

More Related