Stored procedures and functions

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

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

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 Stored procedures

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! Stored procedures

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 Stored procedures

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 Some PL/SQL to use in the body of stored procedures and functions Stored procedures

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

Cursors, 268 • Cursor points to the current row. • Very much like JDBC • Example. totalSalary, 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(…) Stored procedures

Exception handling • Stored procedures can handles exception • Similar to Java try … catch … • Syntax page 314 • Example search_emp + searchDept, 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 Stored procedures

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 Stored procedures

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; Stored procedures

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! Stored procedures

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 Stored procedures

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 Stored procedures

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

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

Handling parameters • IN parameters • 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) Stored procedures

Stored procedures and functions