Stored procedures and functions Procedures and functions stored

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

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

Stored procedures / functions • 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 • Function – Returns a result • Procedure – Does not return a result – Comparable to a void method in C# or Java Stored procedures 3

3 types of parameters • 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 • scott. Tiger. Stored. Procedures. sql • Functions – standard: only IN parameters – Oracle: all kinds of parameters • Don’t use OUT and INOUT with functions! Stored procedures 4

Calling a stored procedure • Syntax – procedure. Name(formal. Parameter 1, formal. Parameter 2, …) • 2 ways to link formal and actual parameters – Position • 1 st parameter formal parameter linked to 1 st actual parameter, etc. – Named • Syntax: formal. Parameter. Name => value Stored procedures 5

Some PL/SQL to use in the body of stored procedures and functions • call p. Name(parameters) • if condition then statements else – call another procedure statements end if • return value – return from a function – Example • Add. Course. And. Language 2 • variable : = value – Assignment • begin … end – statement group • For loop • While loop • General loop – Inner exit statement Stored procedures 6

SQL statements • Stored procedures / functions can contain SQL statements – select, insert, update, delete • Select syntax [result: one value] – select attr into variable from … Stored procedures 7

Cursors • Cursor points to the current row. – Very much like JDBC • Example. total. Salary, • DECLARE c. Name CURSOR FOR select statement – declares the select statement – JDBC statement object • OPEN c. Name – Executes the select statement Stored procedures 8

Exception handling • Stored procedures can handles exception – Similar to try … catch … – Example: Add. Course. And. Language – 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 – Example: Add. Course. And. Language Stored procedures 9

Calling a function • Functions can be called from – PL/SQL block (like the body of another procedure / function) – SQL statement • Example – select NUMBER_OF_COURSES(1) from teacher; Stored procedures 10

Compiling and recompiling stored procedures • 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 Stored procedures 11

Packages • 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 • You can create your own packages! Stored procedures 12

Package structure • Package specification – Specification of procedures, functions, etc. • Public part of the package • Package body – Implementation of procedures, functions, etc. • Private part of the package Stored procedures 13