Stored procedures and functions

1. Stored procedures 1 Stored procedures and functions Procedures and functions stored in the database

2. Stored procedures 2 Layered models vs. stored procedures Ordinary layered model user interface functions model data Layered model with stored procedures user interface functions in DB model = data in DB

3. Stored procedures 3 Stored procedures / functions 313 Stored procedures /functions are Named blocks of PL/SQL SQL DDL Assignments, if statements, loops, etc. Syntax checked and compiled into p-code The p-code is stored in the database Stored procedures� syntax General syntax, page 314 Example, page 315 Stored functions� syntax General syntax, page 319 Example fig. 14-6, page 321

4. Stored procedures 4 3 types of parameters, 314 Procedures can have 3 types of parameters IN used for input OUT used for output side effects ? hard to read / debug the code INOUT used for input + output Examples scottTigerStoredProcedures.sql Functions standard: only IN parameters Oracle: all kinds of parameters Don�t use OUT and INOUT with functions!

5. Stored procedures 5 Calling a stored procedure, 314 Syntax procedureName(formalParameter1, formalParameter2, �) Examples page 314 2 ways to link formal and actual parameters Position Like Java: 1st parameter formal parameter linked to 1st actual parameter, etc. Examples: Figure 14-2, page 316 calling insertDept in scottTigerStoredProcedures.sql Named Syntax: formalParameterName => value Example: calling insertDept in scottTigerStoredProcedures.sql

6. Stored procedures 6 Some PL/SQL to use in the body of stored procedures and functions call pName(parameters) call another procedure return value return from a function variable := value assignment begin � end statement group if condition then statements else statements end if Example page 329 For loop While loop General loop Inner exit statement

7. Stored procedures 7 SQL statements Stored procedures / functions can contain SQL statements select, insert, update, delete Select syntax [result: one value] select attr into variable from � Example figure 14-4, page 318 Insert example insertDept (my own example)

8. Stored procedures 8 Cursors, 268 Cursor points to the current row. Very much like JDBC Example fig. 14-3, page 317 DECLARE cName CURSOR FOR select statement declares the select statement JDBC statement object OPEN cName Executes the select statement JDBC ResultSet rs = statement.executeQuery(�)

9. Stored procedures 9 Exception handling Stored procedures can handles exception Similar to Java try � catch � Syntax page 314 Example fig. 14-4, page 318 Fig. 12-7, page 282 Predefined/named system exceptions When others Catches exceptions not already caught General strategy Don�t catch exceptions if you don�t know how to handle them properly Writing to the screen is usually not enough

10. Stored procedures 10 Calling a function, 320 Functions can be called from PL/SQL block (like the body of another procedure / function) Example fig. 14-7, page 321 SQL statement Example page 323

11. Stored procedures 11 Compilation errors in stored procedures / functions, 317

12. Stored procedures 12 Compiling and recompiling stored procedures, 317 Stored procedures / functions are automatically compiled when recreated. If one of the tables used in a procedures is altered the procedure / function must be recompiled Alter procedure procedureName compile;

13. Stored procedures 13 Packages, 323 A packages groups a set of logically connected stored procedures, functions, etc. Kind of module Built-in packages in Oracle STANDARD Many functions used in Oracle DBMS_OUTPUT Put_line and other procedures Example fig. 14-7, page 321 You can create your own packages!

14. Stored procedures 14 Package structure, 324 Package specification Specification of procedures, functions, etc. Public part of the package Syntax page 324 Example fig. 14-10, page 325 Package body Implementation of procedures, functions, etc. Private part of the package Syntax page 325 Example fig. 14-11, page 327

15. Stored procedures 15 Calling a procedure / function in a package, 326 Name of procedure / function must be prefixed with the name of the package PackageName.ProcedureName(�) DBMS_OUTPUT.PUT_LINE(�) myPackage.myProcedure(�) Example fig. 14-12, page 328

16. Stored procedures 16 Java JDBC API Pakken java.sql interface CallableStatement "factoried" by a connection object CallableStatement prepareCall(String�sql) cst = prepareCall("{call insertDept(?, ?, ?)}"); CallableStatement extends PreparedStatement PreparedStatement extends Statement

17. Stored procedures 17 JDBC examples Stored procedure with IN parameters Stored procedure with IN and OUT parameters Stored function CallableStatementExample.java

18. Stored procedures 18 Handling parameters IN paramaters handled like parameters to prepared statements cstm.setString(1, 'Anders'); cstm.setXxx(position, value); OUT parameters + results from functions register type before executing the call cstm.registerOutParameter(position, type) results can be obtained after executing the call value = cstm.getXxx(position)