Stored procedures and functions

1
Stored procedures and functions

• Procedures and functions stored in the database

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 / 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
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
• Dont use OUT and INOUT with functions!

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.s
  ql
• Named
• Syntax formalParameterName gt value
• Example calling insertDept in scottTigerStoredPro
  cedures.sql

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
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
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
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
• Dont catch exceptions if you dont know how to
  handle them properly
• Writing to the screen is usually not enough

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
Compilation errors in stored procedures /
functions, 317
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
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
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
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
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
JDBC examples

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

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)