This chapter introduces the main features of the PL/SQL language. It shows how PL/SQL meets the challenges of database programming, and how you can reuse techniques that you know from other programming languages.
This chapter contains these topics:
SQL has become the standard database language because it is flexible, powerful, and easy to learn. A few English-like commands such as
DELETE make it easy to manipulate the data stored in a relational database.
PL/SQL lets you use all the SQL data manipulation, cursor control, and transaction control commands, as well as all the SQL functions, operators, and pseudocolumns. This extensive SQL support lets you manipulate Oracle data flexibly and safely. Also, PL/SQL fully supports SQL datatypes, reducing the need to convert data passed between your applications and the database.
The PL/SQL language is tightly integrated with SQL. You do not have to translate between SQL and PL/SQL datatypes; a
VARCHAR2 column in the database is stored in a
VARCHAR2 variable in PL/SQL. This integration saves you both learning time and processing time. Special PL/SQL language features let you work with table columns and rows without specifying the datatypes, saving on maintenance work when the table definitions change.
Running a SQL query and processing the result set is as easy in PL/SQL as opening a text file and processing each line in popular scripting languages. Using PL/SQL to access metadata about database objects and handle database error conditions, you can write utility programs for database administration that are reliable and produce readable output about the success of each operation. Many database features, such as triggers and object types, make use of PL/SQL. You can write the bodies of triggers and methods for object types in PL/SQL.
PL/SQL supports both static and dynamic SQL. The syntax of static SQL statements is known at precompile time and the preparation of the static SQL occurs before runtime, where as the syntax of dynamic SQL statements is not known until runtime. Dynamic SQL is a programming technique that makes your applications more flexible and versatile. Your programs can build and process SQL data definition, data control, and session control statements at run time, without knowing details such as table names and
WHERE clauses in advance. For information on the use of static SQL with PL/SQL, see Chapter 6, "Performing SQL Operations from PL/SQL". For information on the use of dynamic SQL, see Chapter 7, "Performing SQL Operations with Native Dynamic SQL". For additional information about dynamic SQL, see Oracle Database Application Developer's Guide - Fundamentals.
Without PL/SQL, Oracle must process SQL statements one at a time. Programs that issue many SQL statements require multiple calls to the database, resulting in significant network and performance overhead.
With PL/SQL, an entire block of statements can be sent to Oracle at one time. This can drastically reduce network traffic between the database and an application. As Figure 1-1 shows, you can use PL/SQL blocks and subprograms to group SQL statements before sending them to the database for execution. PL/SQL also has language features to further speed up SQL statements that are issued inside a loop.
PL/SQL stored procedures are compiled once and stored in executable form, so procedure calls are efficient. Because stored procedures execute in the database server, a single call over the network can start a large job. This division of work reduces network traffic and improves response times. Stored procedures are cached and shared among users, which lowers memory requirements and invocation overhead.
PL/SQL lets you write very compact code for manipulating data. In the same way that scripting languages such as Perl can read, transform, and write data from files, PL/SQL can query, transform, and update data in a database. PL/SQL saves time on design and debugging by offering a full range of software-engineering features, such as exception handling, encapsulation, data hiding, and object-oriented datatypes.
PL/SQL extends tools such as Oracle Forms. With PL/SQL in these tools, you can use familiar language constructs to build applications. For example, you can use an entire PL/SQL block in an Oracle Forms trigger, instead of multiple trigger steps, macros, or user exits. PL/SQL is the same in all environments. After you learn PL/SQL with one Oracle tool, you can transfer your knowledge to other tools.
Applications written in PL/SQL can run on any operating system and platform where the Oracle database runs. With PL/SQL, you can write portable program libraries and reuse them in different environments.
PL/SQL stored procedures move application code from the client to the server, where you can protect it from tampering, hide the internal details, and restrict who has access. For example, you can grant users access to a procedure that updates a table, but not grant them access to the table itself or to the text of the
UPDATE statement. Triggers written in PL/SQL can control or record changes to data, making sure that all changes obey your business rules.
For information on wrapping, or hiding, the source of a PL/SQL unit, see Appendix A, "Obfuscating PL/SQL Source Code".
Oracle provides product-specific packages that define APIs you can call from PL/SQL to perform many useful tasks. These packages include
DBMS_ALERT for using database triggers,
DBMS_FILE for reading and writing operating system (OS) text files,
DBMS_HTTP for making hypertext transfer protocol (HTTP) callouts,
DBMS_OUTPUT for display output from PL/SQL blocks and subprograms, and
DBMS_PIPE for communicating over named pipes. For additional information on these packages, see "Overview of Product-Specific Packages".
For complete information on the packages supplied by Oracle, see Oracle Database PL/SQL Packages and Types Reference.
Object types are an ideal object-oriented modeling tool, which you can use to reduce the cost and time required to build complex applications. Besides allowing you to create software components that are modular, maintainable, and reusable, object types allow different teams of programmers to develop software components concurrently.
By encapsulating operations with data, object types let you move data-maintenance code out of SQL scripts and PL/SQL blocks into methods. Also, object types hide implementation details, so that you can change the details without affecting client programs. See Chapter 12, "Using PL/SQL With Object Types".
In addition, object types allow for realistic data modeling. Complex real-world entities and relationships map directly into object types. This direct mapping helps your programs better reflect the world they are trying to simulate. For information on object types, see Oracle Database Application Developer's Guide - Object-Relational Features.
You can use PL/SQL to develop Web applications and Server Pages (PSPs). For an overview of the use of PL/SQL with the Web, see "Using PL/SQL to Create Web Applications and Server Pages".
PL/SQL combines the data-manipulating power of SQL with the processing power of procedural languages. You can control program flow with statements like
LOOP. As with other procedural programming languages, you can declare variables, define procedures and functions, and trap runtime errors.
PL/SQL lets you break complex problems down into easily understandable procedural code, and reuse this code across multiple applications. When a problem can be solved through plain SQL, you can issue SQL commands directly inside your PL/SQL programs, without learning new APIs. PL/SQL's data types correspond with SQL's column types, making it easy to interchange PL/SQL variables with data inside a table.
The basic units (procedures, functions, and anonymous blocks) that make up a PL/SQL program are logical blocks, which can be nested inside one another. A block groups related declarations and statements. You can place declarations close to where they are used, such as inside a large subprogram. The declarations are local to the block and cease to exist when the block completes, helping to avoid cluttered namespaces for variables and procedures. For a syntax description of the block structure, see "Block Declaration".
As Figure 1-2 shows, a PL/SQL block has three basic parts: a declarative part (DECLARE), an executable part (BEGIN .. END), and an exception-handling (EXCEPTION) part that handles error conditions. Only the executable part is required. The optional declarative part is written first, where you define types, variables, and similar items. These items are manipulated in the executable part. Exceptions raised during execution can be dealt with in the exception-handling part. For an example of PL/SQL block structure, see Example 1-3.
You can nest blocks in the executable and exception-handling parts of a PL/SQL block or subprogram but not in the declarative part. You can define local subprograms in the declarative part of any block. You can call local subprograms only from the block in which they are defined.
PL/SQL lets you declare variables and constants, then use them in SQL and procedural statements anywhere an expression can be used. You must declare a constant or variable before referencing it in any other statements. For additional information, see "Declarations".
Variables can have any SQL datatype, such as
NUMBER, or a PL/SQL-only datatype, such as
PLS_INTEGER. For example, assume that you want to declare variables for part data, such as
part_no to hold 6-digit numbers and
in_stock to hold the Boolean value
FALSE. You declare these and related part variables as shown in Example 1-1. Note that there is a semi-colon (;) at the end of each line in the declaration section.
DECLARE part_no NUMBER(6); part_name VARCHAR2(20); in_stock BOOLEAN; part_price NUMBER(6,2); part_desc VARCHAR2(50);
You can also declare nested tables, variable-size arrays (varrays for short), and records using the
RECORD composite datatypes. See Chapter 5, "Using PL/SQL Collections and Records".
You can assign values to a variable in three ways. The first way uses the assignment operator (
:=), a colon followed by an equal sign, as shown in Example 1-2. You place the variable to the left of the operator and an expression, including function calls, to the right. Note that you can assign a value to a variable when it is declared.
DECLARE wages NUMBER; hours_worked NUMBER := 40; hourly_salary NUMBER := 22.50; bonus NUMBER := 150; country VARCHAR2(128); counter NUMBER := 0; done BOOLEAN; valid_id BOOLEAN; emp_rec1 employees%ROWTYPE; emp_rec2 employees%ROWTYPE; TYPE commissions IS TABLE OF NUMBER INDEX BY PLS_INTEGER; comm_tab commissions; BEGIN wages := (hours_worked * hourly_salary) + bonus; country := 'France'; country := UPPER('Canada'); done := (counter > 100); valid_id := TRUE; emp_rec1.first_name := 'Antonio'; emp_rec1.last_name := 'Ortiz'; emp_rec1 := emp_rec2; comm_tab(5) := 20000 * 0.15; END; /
The second way to assign values to a variable is by selecting (or fetching) database values into it. In Example 1-3, 10% of an employee's salary is selected into the
bonus variable. Now you can use the
bonus variable in another computation or insert its value into a database table.
DECLARE bonus NUMBER(8,2); emp_id NUMBER(6) := 100; BEGIN SELECT salary * 0.10 INTO bonus FROM employees WHERE employee_id = emp_id; END; /
The third way to assign a value to a variable is by passing it as an
OUT parameter to a subprogram, and then assigning the value inside the subprogram. Example 1-4 passes the
sal variable to a subprogram, and the subprogram updates the variable.
In the example,
DBMS_OUTPUT.PUT_LINE is used to display output from the PL/SQL program. For more information, see "Inputting and Outputting Data with PL/SQL". For information on the
DBMS_OUTPUT package, see "About the DBMS_OUTPUT Package".
REM SERVEROUTPUT must be set to ON to display output with DBMS_OUTPUT SET SERVEROUTPUT ON FORMAT WRAPPED DECLARE new_sal NUMBER(8,2); emp_id NUMBER(6) := 126; PROCEDURE adjust_salary(emp_id NUMBER, sal IN OUT NUMBER) IS emp_job VARCHAR2(10); avg_sal NUMBER(8,2); BEGIN SELECT job_id INTO emp_job FROM employees WHERE employee_id = emp_id; SELECT AVG(salary) INTO avg_sal FROM employees WHERE job_id = emp_job; DBMS_OUTPUT.PUT_LINE ('The average salary for ' || emp_job || ' employees: ' || TO_CHAR(avg_sal)); sal := (sal + avg_sal)/2; -- adjust sal value which is returned END; BEGIN SELECT AVG(salary) INTO new_sal FROM employees; DBMS_OUTPUT.PUT_LINE ('The average salary for all employees: ' || TO_CHAR(new_sal)); adjust_salary(emp_id, new_sal); -- assigns a new value to new_sal DBMS_OUTPUT.PUT_LINE ('The adjusted salary for employee ' || TO_CHAR(emp_id) || ' is ' || TO_CHAR(new_sal)); -- sal has new value END; /
When you embed an
SELECT SQL statement directly in your PL/SQL code, PL/SQL turns the variables in the
VALUES clauses into bind variables automatically. Oracle can reuse these SQL statement each time the same code is executed. To run similar statements with different variable values, you can save parsing overhead by calling a stored procedure that accepts parameters, then issues the statements with the parameters substituted in the appropriate places.
You do need to specify bind variables with dynamic SQL, in clauses like
VALUES where you normally use variables. Instead of concatenating literals and variable values into a single string, replace the variables with the names of bind variables (prefixed by a colon) and specify the corresponding PL/SQL variables with the
USING clause. Using the
USING clause, instead of concatenating the variables into the string, reduces parsing overhead and lets Oracle reuse the SQL statements. For example:
'DELETE FROM employees WHERE employee_id = :id' USING emp_id;
For an example of the use of bind variables, see Example 7-1.
Declaring a constant is like declaring a variable except that you must add the keyword
CONSTANT and immediately assign a value to the constant. No further assignments to the constant are allowed. The following example declares a constant:
credit_limit CONSTANT NUMBER := 5000.00;
Processing a SQL query with PL/SQL is like processing files with other languages. For example, a Perl program opens a file, reads the file contents, processes each line, then closes the file. In the same way, a PL/SQL program issues a query and processes the rows from the result set as shown in Example 1-5.
BEGIN FOR someone IN (SELECT * FROM employees WHERE employee_id < 120 ) LOOP DBMS_OUTPUT.PUT_LINE('First name = ' || someone.first_name || ', Last name = ' || someone.last_name); END LOOP; END; /
You can use a simple loop like the one shown here, or you can control the process precisely by using individual statements to perform the query, retrieve data, and finish processing.
Subprograms are named PL/SQL blocks that can be called with a set of parameters. PL/SQL has two types of subprograms: procedures and functions. The following is an example of a declaration of a PL/SQL procedure:
DECLARE in_string VARCHAR2(100) := 'This is my test string.'; out_string VARCHAR2(200); PROCEDURE double ( original IN VARCHAR2, new_string OUT VARCHAR2 ) AS BEGIN new_string := original || original; END;
You can create standalone subprograms with SQL statements that are stored in the database. See Subprograms: Procedures and Functions.
As part of the declaration for each PL/SQL variable, you declare its datatype. Usually, this datatype is one of the types shared between PL/SQL and SQL, such as
VARCHAR2. For easier maintenance of code that interacts with the database, you can also use the special qualifiers
%ROWTYPE to declare variables that hold table columns or table rows. For more information on datatypes, see Chapter 3, "PL/SQL Datatypes".
%TYPE attribute provides the datatype of a variable or database column. This is particularly useful when declaring variables that will hold database values. For example, assume there is a column named
last_name in a table named
employees. To declare a variable named
v_last_name that has the same datatype as column
title, use dot notation and the
%TYPE attribute, as follows:
%TYPE has two advantages. First, you need not know the exact datatype of
last_name. Second, if you change the database definition of
last_name, perhaps to make it a longer character string, the datatype of
v_last_name changes accordingly at run time.
In PL/SQL, records are used to group data. A record consists of a number of related fields in which data values can be stored. The
%ROWTYPE attribute provides a record type that represents a row in a table. The record can store an entire row of data selected from the table or fetched from a cursor or cursor variable. See "Cursors".
Columns in a row and corresponding fields in a record have the same names and datatypes. In the following example, you declare a record named
dept_rec. Its fields have the same names and datatypes as the columns in the
dept_rec departments%ROWTYPE; -- declare record variable
You use dot notation to reference fields, as the following example shows:
v_deptid := dept_rec.department_id;
If you declare a cursor that retrieves the last name, salary, hire date, and job class of an employee, you can use
%ROWTYPE to declare a record that stores the same information as shown in Example 1-6. When you execute the
FETCH statement, the value in the
last_name column of the
employees table is assigned to the
last_name field of
employee_rec, the value in the
salary column is assigned to the
salary field, and so on.
DECLARE CURSOR c1 IS SELECT last_name, salary, hire_date, job_id FROM employees WHERE employee_id = 120; -- declare record variable that represents a row fetched from the employees table employee_rec c1%ROWTYPE; BEGIN -- open the explicit cursor and use it to fetch data into employee_rec OPEN c1; FETCH c1 INTO employee_rec; DBMS_OUTPUT.PUT_LINE('Employee name: ' || employee_rec.last_name); END; /
Control structures are the most important PL/SQL extension to SQL. Not only does PL/SQL let you manipulate Oracle data, it lets you process the data using conditional, iterative, and sequential flow-of-control statements such as
GOTO. For additional information, see Chapter 4, "Using PL/SQL Control Structures".
Often, it is necessary to take alternative actions depending on circumstances. The
IF-THEN-ELSE statement lets you execute a sequence of statements conditionally. The
IF clause checks a condition, the
THEN clause defines what to do if the condition is true and the
ELSE clause defines what to do if the condition is false or null. Example 1-7 shows the use of IF-THEN-ELSE to determine the salary raise an employee receives based on the current salary of the employee.
To choose among several values or courses of action, you can use
CASE constructs. The CASE expression evaluates a condition and returns a value for each case. The case statement evaluates a condition and performs an action, such as an entire PL/SQL block, for each case. See Example 1-7.
DECLARE jobid employees.job_id%TYPE; empid employees.employee_id%TYPE := 115; sal employees.salary%TYPE; sal_raise NUMBER(3,2); BEGIN SELECT job_id, salary INTO jobid, sal from employees WHERE employee_id = empid; CASE WHEN jobid = 'PU_CLERK' THEN IF sal < 3000 THEN sal_raise := .12; ELSE sal_raise := .09; END IF; WHEN jobid = 'SH_CLERK' THEN IF sal < 4000 THEN sal_raise := .11; ELSE sal_raise := .08; END IF; WHEN jobid = 'ST_CLERK' THEN IF sal < 3500 THEN sal_raise := .10; ELSE sal_raise := .07; END IF; ELSE BEGIN DBMS_OUTPUT.PUT_LINE('No raise for this job: ' || jobid); END; END CASE; UPDATE employees SET salary = salary + salary * sal_raise WHERE employee_id = empid; COMMIT; END; /
A sequence of statements that uses query results to select alternative actions is common in database applications. Another common sequence inserts or deletes a row only if an associated entry is found in another table. You can bundle these common sequences into a PL/SQL block using conditional logic.
LOOP statements let you execute a sequence of statements multiple times. You place the keyword
LOOP before the first statement in the sequence and the keywords
LOOP after the last statement in the sequence. The following example shows the simplest kind of loop, which repeats a sequence of statements continually:
-- sequence of statements
FOR-LOOP statement lets you specify a range of integers, then execute a sequence of statements once for each integer in the range. In Example 1-8 the loop inserts 100 numbers, square roots, squares, and the sum of squares into a database table:
CREATE TABLE sqr_root_sum (num NUMBER, sq_root NUMBER(6,2), sqr NUMBER, sum_sqrs NUMBER); DECLARE s PLS_INTEGER; BEGIN FOR i in 1..100 LOOP s := (i * (i + 1) * (2*i +1)) / 6; -- sum of squares INSERT INTO sqr_root_sum VALUES (i, SQRT(i), i*i, s ); END LOOP; END; /
WHILE-LOOP statement associates a condition with a sequence of statements. Before each iteration of the loop, the condition is evaluated. If the condition is true, the sequence of statements is executed, then control resumes at the top of the loop. If the condition is false or null, the loop is bypassed and control passes to the next statement.
In Example 1-9, you find the first employee who has a salary over $15000 and is higher in the chain of command than employee 120:
CREATE TABLE temp (tempid NUMBER(6), tempsal NUMBER(8,2), tempname VARCHAR2(25)); DECLARE sal employees.salary%TYPE := 0; mgr_id employees.manager_id%TYPE; lname employees.last_name%TYPE; starting_empid employees.employee_id%TYPE := 120; BEGIN SELECT manager_id INTO mgr_id FROM employees WHERE employee_id = starting_empid; WHILE sal <= 15000 LOOP -- loop until sal > 15000 SELECT salary, manager_id, last_name INTO sal, mgr_id, lname FROM employees WHERE employee_id = mgr_id; END LOOP; INSERT INTO temp VALUES (NULL, sal, lname); -- insert NULL for tempid COMMIT; EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO temp VALUES (NULL, NULL, 'Not found'); -- insert NULLs COMMIT; END; /
EXIT-WHEN statement lets you complete a loop if further processing is impossible or undesirable. When the
EXIT statement is encountered, the condition in the
WHEN clause is evaluated. If the condition is true, the loop completes and control passes to the next statement. In Example 1-10, the loop completes when the value of
total exceeds 25,000:
DECLARE total NUMBER(9) := 0; counter NUMBER(6) := 0; BEGIN LOOP counter := counter + 1; total := total + counter * counter; -- exit loop when condition is true EXIT WHEN total > 25000; END LOOP; DBMS_OUTPUT.PUT_LINE('Counter: ' || TO_CHAR(counter) || ' Total: ' || TO_CHAR(total)); END; /
GOTO statement lets you branch to a label unconditionally. The label, an undeclared identifier enclosed by double angle brackets, must precede an executable statement or a PL/SQL block. When executed, the
GOTO statement transfers control to the labeled statement or block, as shown in Example 1-11.
DECLARE total NUMBER(9) := 0; counter NUMBER(6) := 0; BEGIN <<calc_total>> counter := counter + 1; total := total + counter * counter; -- branch to print_total label when condition is true IF total > 25000 THEN GOTO print_total; ELSE GOTO calc_total; END IF; <<print_total>> DBMS_OUTPUT.PUT_LINE('Counter: ' || TO_CHAR(counter) || ' Total: ' || TO_CHAR(total)); END; /
Using conditional compilation, you can customize the functionality in a compiled PL/SQL application by conditionalizing functionality rather than removing any source code. For example, conditional compilation enables you to determine which PL/SQL features in a PL/SQL application are used for specific database releases. The latest PL/SQL features in an application can be run on a new database release while at the same time those features can be conditionalizing so that the same application is compatible with a previous database release. Conditional compilation is also useful when you want to execute debugging procedures in a development environment, but want to turn off the debugging routines in a production environment. See "Conditional Compilation".
PL/SQL lets you break an application down into manageable, well-defined modules. PL/SQL meets this need with program units, which include blocks, subprograms, and packages. You can reuse program units by loading them into the database as triggers, stored procedures, and stored functions. For additional information, see Chapter 8, "Using PL/SQL Subprograms" and Chapter 9, "Using PL/SQL Packages".
There are two types of subprograms called procedures and functions, which can accept parameters and be invoked (called). See "What Are Subprograms?".
PROCEDURE statement lets you create standalone procedures that are stored in the database. For information, see
PROCEDURE in Oracle Database SQL Reference. The SQL
FUNCTION statement lets you create standalone functions that are stored in an Oracle database. For information, see
FUNCTION in Oracle Database SQL Reference. These stored (schema level) subprograms can be accessed from SQL.
As shown in Example 1-12, a subprogram is like a miniature program, beginning with a header followed by an optional declarative part, an executable part, and an optional exception-handling part.
-- including OR REPLACE is more convenient when updating a subprogram CREATE OR REPLACE PROCEDURE award_bonus (emp_id NUMBER, bonus NUMBER) AS commission REAL; comm_missing EXCEPTION; BEGIN -- executable part starts here SELECT commission_pct / 100 INTO commission FROM employees WHERE employee_id = emp_id; IF commission IS NULL THEN RAISE comm_missing; ELSE UPDATE employees SET salary = salary + bonus*commission WHERE employee_id = emp_id; END IF; EXCEPTION -- exception-handling part starts here WHEN comm_missing THEN DBMS_OUTPUT.PUT_LINE('This employee does not receive a commission.'); commission := 0; WHEN OTHERS THEN NULL; -- for other exceptions do nothing END award_bonus; / CALL award_bonus(150, 400);
When called, this procedure accepts an employee Id and a bonus amount. It uses the Id to select the employee's commission percentage from a database table and, at the same time, convert the commission percentage to a decimal amount. Then, it checks the commission amount. If the commission is null, an exception is raised; otherwise, the employee's salary is updated.
PL/SQL lets you bundle logically related types, variables, cursors, and subprograms into a package, a database object that is a step above regular stored procedures. The packages defines a simple, clear, interface to a set of related procedures and types that can be accessed by SQL statements.
Packages usually have two parts: a specification and a body. The specification defines the application programming interface; it declares the types, constants, variables, exceptions, cursors, and subprograms. The body fills in the SQL queries for cursors and the code for subprograms.
To create package specs, use the SQL statement
CREATE PACKAGE BODY statement defines the package body. For information on the
PACKAGE SQL statement, see Oracle Database SQL Reference. For information on the
BODY SQL statement, see Oracle Database SQL Reference.
In Example 1-13, the
emp_actions package contain two procedures that update the
employees table and one function that provides information.
CREATE OR REPLACE PACKAGE emp_actions AS -- package specification PROCEDURE hire_employee (employee_id NUMBER, last_name VARCHAR2, first_name VARCHAR2, email VARCHAR2, phone_number VARCHAR2, hire_date DATE, job_id VARCHAR2, salary NUMBER, commission_pct NUMBER, manager_id NUMBER, department_id NUMBER); PROCEDURE fire_employee (emp_id NUMBER); FUNCTION num_above_salary (emp_id NUMBER) RETURN NUMBER; END emp_actions; / CREATE OR REPLACE PACKAGE BODY emp_actions AS -- package body -- code for procedure hire_employee PROCEDURE hire_employee (employee_id NUMBER, last_name VARCHAR2, first_name VARCHAR2, email VARCHAR2, phone_number VARCHAR2, hire_date DATE, job_id VARCHAR2, salary NUMBER, commission_pct NUMBER, manager_id NUMBER, department_id NUMBER) IS BEGIN INSERT INTO employees VALUES (employee_id, last_name, first_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id); END hire_employee; -- code for procedure fire_employee PROCEDURE fire_employee (emp_id NUMBER) IS BEGIN DELETE FROM employees WHERE employee_id = emp_id; END fire_employee; -- code for function num_above salary FUNCTION num_above_salary (emp_id NUMBER) RETURN NUMBER IS emp_sal NUMBER(8,2); num_count NUMBER; BEGIN SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id; SELECT COUNT(*) INTO num_count FROM employees WHERE salary > emp_sal; RETURN num_count; END num_above_salary; END emp_actions; /
Applications that call these procedures only need to know the names and parameters from the package specification. You can change the implementation details inside the package body without affecting the calling applications.
To call the procedures of the
emp_actions package created in Example 1-13, you can execute the statements in Example 1-14. The procedures can be executed in a
END block or with the SQL
CALL statement. Note the use of the package name as a prefix to the procedure name.
CALL emp_actions.hire_employee(300, 'Belden', 'Enrique', 'EBELDEN', '555.111.2222', '31-AUG-04', 'AC_MGR', 9000, .1, 101, 110); BEGIN DBMS_OUTPUT.PUT_LINE( 'Number of employees with higher salary: ' || TO_CHAR(emp_actions.num_above_salary(120))); emp_actions.fire_employee(300); END; /
Packages are stored in the database, where they can be shared by many applications. Calling a packaged subprogram for the first time loads the whole package and caches it in memory, saving on disk I/O for subsequent calls. Thus, packages enhance reuse and improve performance in a multiuser, multi-application environment. For information on packages, see Chapter 9, "Using PL/SQL Packages".
If a subprogram does not take any parameters, you can include an empty set of parentheses or omit the parentheses, both in PL/SQL and in functions called from SQL queries. For calls to a method that takes no parameters, an empty set of parentheses is optional within PL/SQL scopes but required within SQL scopes.
Most PL/SQL input and output is through SQL statements, to store data in database tables or query those tables. All other PL/SQL I/O is done through APIs that interact with other programs. For example, the
DBMS_OUTPUT package has procedures such as
PUT_LINE. To see the result outside of PL/SQL requires another program, such as SQL*Plus, to read and display the data passed to
SET SERVEROUTPUT ON
For information on the
SEVEROUTPUT setting, see the "SQL*Plus Command Reference" chapter in SQL*Plus User's Guide and Reference.
Other PL/SQL APIs for processing I/O are:
HTP for displaying output on a web page
DBMS_PIPE for passing information back and forth between PL/SQL and operating-system commands
UTL_FILE for reading and writing operating-system files
UTL_HTTP for communicating with web servers
UTL_SMTP for communicating with mail servers
See "Overview of Product-Specific Packages". Although some of these APIs can accept input as well as output, there is no built-in language facility for accepting data directly from the keyboard. For that, you can use the
ACCEPT commands in SQL*Plus.
Data abstraction lets you work with the essential properties of data without being too involved with details. After you design a data structure, you can focus on designing algorithms that manipulate the data structure.
A cursor is a name for a specific private SQL area in which information for processing the specific statement is kept. PL/SQL uses both implicit and explicit cursors. PL/SQL implicitly declares a cursor for all SQL data manipulation statements on a set of rows, including queries that return only one row. For queries that return more than one row, you can explicitly declare a cursor to process the rows individually. For example, Example 1-6 declares an explicit cursor.
For information on managing cursors with PL/SQL, see "Managing Cursors in PL/SQL".
PL/SQL collection types let you declare high-level datatypes similar to arrays, sets, and hash tables found in other languages. In PL/SQL, array types are known as varrays (short for variable-size arrays), set types are known as nested tables, and hash table types are known as associative arrays. Each kind of collection is an ordered group of elements, all of the same type. Each element has a unique subscript that determines its position in the collection. When declaring collections, you use a
TYPE definition. See "Defining Collection Types and Declaring Collection Variables".
To reference an element, use subscript notation with parentheses, as shown in Example 1-15.
DECLARE TYPE staff_list IS TABLE OF employees.employee_id%TYPE; staff staff_list; lname employees.last_name%TYPE; fname employees.first_name%TYPE; BEGIN staff := staff_list(100, 114, 115, 120, 122); FOR i IN staff.FIRST..staff.LAST LOOP SELECT last_name, first_name INTO lname, fname FROM employees WHERE employees.employee_id = staff(i); DBMS_OUTPUT.PUT_LINE ( TO_CHAR(staff(i)) || ': ' || lname || ', ' || fname ); END LOOP; END; /
Collections can be passed as parameters, so that subprograms can process arbitrary numbers of elements.You can use collections to move data into and out of database tables using high-performance language features known as bulk SQL.
For information on collections, see Chapter 5, "Using PL/SQL Collections and Records".
Records are composite data structures whose fields can have different datatypes. You can use records to hold related items and pass them to subprograms with a single parameter. When declaring records, you use a
TYPE definition. See "Defining and Declaring Records".
Example 1-16 shows how are records are declared.
DECLARE TYPE timerec IS RECORD (hours SMALLINT, minutes SMALLINT); TYPE meetin_typ IS RECORD ( date_held DATE, duration timerec, -- nested record location VARCHAR2(20), purpose VARCHAR2(50)); BEGIN -- NULL does nothing but allows unit to be compiled and tested NULL; END; /
You can use the
%ROWTYPE attribute to declare a record that represents a row in a table or a row from a query result set, without specifying the names and types for the fields.
For information on records, see Chapter 5, "Using PL/SQL Collections and Records".
PL/SQL supports object-oriented programming through object types. An object type encapsulates a data structure along with the functions and procedures needed to manipulate the data. The variables that form the data structure are known as attributes. The functions and procedures that manipulate the attributes are known as methods.
Object types reduce complexity by breaking down a large system into logical entities. This lets you create software components that are modular, maintainable, and reusable. Object-type definitions, and the code for the methods, are stored in the database. Instances of these object types can be stored in tables or used as variables inside PL/SQL code. Example 1-17 shows an object type definition for a bank account.
CREATE TYPE bank_account AS OBJECT ( acct_number NUMBER(5), balance NUMBER, status VARCHAR2(10), MEMBER PROCEDURE open (SELF IN OUT NOCOPY bank_account, amount IN NUMBER), MEMBER PROCEDURE close (SELF IN OUT NOCOPY bank_account, num IN NUMBER, amount OUT NUMBER), MEMBER PROCEDURE deposit (SELF IN OUT NOCOPY bank_account, num IN NUMBER, amount IN NUMBER), MEMBER PROCEDURE withdraw (SELF IN OUT NOCOPY bank_account, num IN NUMBER, amount IN NUMBER), MEMBER FUNCTION curr_bal (num IN NUMBER) RETURN NUMBER ); /
For information on object types, see Oracle Database Application Developer's Guide - Object-Relational Features. For information on the use of PL/SQL with objects, see Chapter 12, "Using PL/SQL With Object Types".
PL/SQL makes it easy to detect and process error conditions known as exceptions. When an error occurs, an exception is raised: normal execution stops and control transfers to special exception-handling code, which comes at the end of any PL/SQL block. Each different exception is processed by a particular exception handler.
PL/SQL's exception handling is different from the manual checking you might be used to from C programming, where you insert a check to make sure that every operation succeeded. Instead, the checks and calls to error routines are performed automatically, similar to the exception mechanism in Java programming.
Predefined exceptions are raised automatically for certain common error conditions involving variables or database operations. For example, if you try to divide a number by zero, PL/SQL raises the predefined exception
You can declare exceptions of your own, for conditions that you decide are errors, or to correspond to database errors that normally result in ORA- error messages. When you detect a user-defined error condition, you execute a
RAISE statement. See the exception
comm_missing in Example 1-12. In the example, if the commission is null, the exception
comm_missing is raised.
By default, you put an exception handler at the end of a subprogram to handle exceptions that are raised anywhere inside the subprogram. To continue executing from the spot where an exception happens, enclose the code that might raise an exception inside another
BEGIN-END block with its own exception handler. For example, you might put separate
BEGIN-END blocks around groups of SQL statements that might raise
NO_DATA_FOUND, or around arithmetic operations that might raise
DIVIDE_BY_ZERO. By putting a
BEGIN-END block with an exception handler inside a loop, you can continue executing the loop even if some loop iterations raise exceptions. See Example 5-38.
The PL/SQL compilation and run-time system is an engine that compiles and executes PL/SQL blocks and subprograms. The engine can be installed in an Oracle server or in an application development tool such as Oracle Forms.
In either environment, the PL/SQL engine accepts as input any valid PL/SQL block or subprogram. Figure 1-3 shows the PL/SQL engine processing an anonymous block. The PL/SQL engine executes procedural statements but sends SQL statements to the SQL engine in the Oracle database.
Typically, the Oracle database server processes PL/SQL blocks and subprograms.
Anonymous PL/SQL blocks can be submitted to interactive tools such as SQL*Plus and Enterprise Manager, or embedded in an Oracle Precompiler or OCI program. At run time, the program sends these blocks to the Oracle database, where they are compiled and executed.
Subprograms can be compiled and stored in an Oracle database, ready to be executed. Once compiled, it is a schema object known as a stored procedure or stored function, which can be referenced by any number of applications connected to that database.
PROCEDURE statement lets you create standalone procedures that are stored in the database. For information, see
PROCEDURE in Oracle Database SQL Reference. The SQL
FUNCTION statement lets you create standalone functions that are stored in an Oracle database. For information, see
FUNCTION in Oracle Database SQL Reference.
Subprograms are stored in a compact compiled form. When called, they are loaded and processed immediately. Subprograms take advantage of shared memory, so that only one copy of a subprogram is loaded into memory for execution by multiple users.
Stored subprograms defined within a package are known as packaged subprograms. Those defined independently are called standalone subprograms. Subprograms nested inside other subprograms or within a PL/SQL block are known as local subprograms, which cannot be referenced by other applications and exist only inside the enclosing block.
Stored subprograms are the key to modular, reusable PL/SQL code. Wherever you might use a JAR file in Java, a module in Perl, a shared library in C++, or a DLL in Visual Basic, you should use PL/SQL stored procedures, stored functions, and packages.
You can call stored subprograms from a database trigger, another stored subprogram, an Oracle Precompiler or OCI application, or interactively from SQL*Plus or Enterprise Manager. You can also configure a web server so that the HTML for a web page is generated by a stored subprogram, making it simple to provide a web interface for data entry and report generation.
CALL award_bonus(179, 1000); BEGIN award_bonus(179, 10000); END; / -- using named notation BEGIN award_bonus(emp_id=>179, bonus=>10000); END; /
END block is recommended in several situations. Calling the subprogram from a
END block allows named or mixed notation for parameters which the
CALL statement does not support. For information on named parameters, see "Using Positional, Named, or Mixed Notation for Subprogram Parameters". In addition, using the
CALL statement can suppress an
found error that has not been handled in the PL/SQL subprogram.
For additional examples on calling PL/SQL procedures, see Example 8-5, "Subprogram Calls Using Positional, Named, and Mixed Notation" and "Passing Schema Object Names As Parameters". For information on the use of the
CALL statement, see Oracle Database SQL Reference
A database trigger is a stored subprogram associated with a database table, view, or event. The trigger can be called once, when some event occurs, or many times, once for each row affected by an
DELETE statement. The trigger can be called after the event, to record it or take some followup action. Or, the trigger can be called before the event to prevent erroneous operations or fix new data so that it conforms to business rules. In Example 1-19 the table-level trigger fires whenever salaries in the
employees table are updated, such as the processing in Example 1-7. For each update, the trigger writes a record to the
CREATE TABLE emp_audit ( emp_audit_id NUMBER(6), up_date DATE, new_sal NUMBER(8,2), old_sal NUMBER(8,2) ); CREATE OR REPLACE TRIGGER audit_sal AFTER UPDATE OF salary ON employees FOR EACH ROW BEGIN -- bind variables are used here for values INSERT INTO emp_audit VALUES( :old.employee_id, SYSDATE, :new.salary, :old.salary ); END; /
The executable part of a trigger can contain procedural statements as well as SQL data manipulation statements. Besides table-level triggers, there are instead-of triggers for views and system-event triggers for schemas. For more information on triggers, see Oracle Database Concepts and Oracle Database Application Developer's Guide - Fundamentals. For information on the
TRIGGER SQL statement, see Oracle Database SQL Reference.
An application development tool that contains the PL/SQL engine can process PL/SQL blocks and subprograms. The tool passes the blocks to its local PL/SQL engine. The engine executes all procedural statements inside the application and sends only SQL statements to the database. Most of the work is done inside the application, not on the database server. If the block contains no SQL statements, the application executes the entire block. This is useful if your application can benefit from conditional and iterative control.
Frequently, Oracle Forms applications use SQL statements to test the value of field entries or to do simple computations. By using PL/SQL instead, you can avoid calls to the database. You can also use PL/SQL functions to manipulate field entries.