| Oracle® Database Express Edition 2 Day Developer Guide 10g Release 2 (10.2) Part Number B25108-01 |
|
|
View PDF |
| Oracle® Database Express Edition 2 Day Developer Guide 10g Release 2 (10.2) Part Number B25108-01 |
|
|
View PDF |
This section discusses the PL/SQL language, which can be use to develop applications for Oracle Database Express Edition.
This section contains the following topics:
See Also:
Oracle Database PL/SQL User's Guide and Reference for detailed information about PL/SQL
Oracle Database PL/SQL Packages and Types Reference for information about packages supplied by Oracle
Oracle Database Application Developer's Guide - Fundamentals for information about dynamic SQL
Oracle Database Application Developer's Guide - Fundamentals for information about using PL/SQL to develop Web applications
PL/SQL is an Oracle's procedural language extension to SQL. It is a server-side, stored procedural language that is easy-to-use, seamless with SQL, portable, and secure.
PL/SQL enables you to mix SQL statements with procedural constructs. With PL/SQL, you can create and run PL/SQL program units such as procedures, functions, and packages. PL/SQL program units generally are categorized as anonymous blocks, stored functions, stored procedures, and packages.
The following can be constructed with the PL/SQL language:
Anonymous block
An anonymous block is a PL/SQL block that appears in your application and is not named or stored in the database. In many applications, PL/SQL blocks can appear wherever SQL statements can appear. A PL/SQL block groups related declarations and statements. Because these blocks are not stored in the database, they are generally for one-time use.
Stored or standalone procedure and function
A stored procedure or function is a PL/SQL block that Oracle Database XE stores in the database and can be called by name from an application. Functions are different than procedures in that functions return a value when executed. When you create a stored procedure or function, Oracle Database XE parses the procedure or function, and stores its parsed representation in the database. See Chapter 5, "Using Procedures, Functions, and Packages".
Package
A package is a group of procedures, functions, and variable definitions that Oracle Database XE stores in the database. Procedures, functions, and variables in packages can be called from other packages, procedures, or functions. See Chapter 5, "Using Procedures, Functions, and Packages".
Trigger
A database trigger is a stored procedure associated with a database table, view, or event. The trigger can be called after the event, to record it, or take some follow-up action. The trigger can be called before the event, to prevent erroneous operations or fix new data so that it conforms to business rules. See Chapter 6, "Using Triggers".
You can enter and run PL/SQL code from the SQL Commands page, Script Editor page, or SQL Command Line (SQL*Plus).
Using the SQL Commands page is described in this section. The SQL Commands page is a simpler interface and easier to use.
Both SQL Commands and Script Editor pages enable you to save your SQL statements as a script file in a database repository for future use. You can run multiple SQL statements in the Script Editor page. Script Editor also enables you to download the script to the local file system. For information about using the Script Editor page, see "Running SQL Statements in the Script Editor Page".
You can create a text file of the PL/SQL code with the Script Editor page or a text editor to run as a SQL script from SQL Command Line. Using a script makes correcting mistakes easier because you only need to make the necessary updates to correct the problem, rather than entering again all the PL/SQL code at the SQL Command Line prompt. For information about using SQL Command Line and running SQL scripts from SQL Command Line, see Appendix A, "Using SQL Command Line".
This section contains the following topic:
To enter and run PL/SQL code in the SQL Commands page:
Log in to the Database Home Page. See "Logging in to the Database Home Page".
On the home page, click the SQL icon to display the SQL page.
Click the SQL Commands icon to display the SQL Commands page.
On the SQL Commands page, enter the PL/SQL code in Example 4-1. Note that some of the lines of code are terminated with a semi colon (;) and the entire code unit is terminated with a slash (/). The slash is required when running the PL/SQL in a SQL script or at the SQL Command Line prompt, but it is optional on the SQL Commands page.
Click the Run button to run the PL/SQL code. If necessary, select (highlight) only the PL/SQL code block before clicking the Run button. Any comments outside the PL/SQL code block are not legal in the SQL Commands page.
If you want to save the PL/SQL code for future use, click the Save button.
In the Name field, enter a name for the saved PL/SQL code. You can also enter an optional description. Click the Save button to save the SQL.
To access saved PL/SQL code, click the Saved SQL tab, and select the name of the saved PL/SQL code that you want to access.
See Also:
Oracle Database Express Edition Application Express User's Guide for detailed information about using SQL CommandsPL/SQL combines the data-manipulating power of SQL with the processing power of procedural languages. You can control program flow with statements, such as IF and LOOP. As with other procedural programming languages, you can declare variables, define procedures and functions, and trap run time errors.
PL/SQL lets you break complex problems down into understandable procedural code, and reuse this code across multiple applications. When a problem can be solved through plain SQL, you can issue SQL statements directly inside your PL/SQL programs, without learning new APIs. PL/SQL datatypes correspond with SQL column types, enabling you to interchange PL/SQL variables with data inside a table.
This section contains the following topics:
Declaring Variables With the DEFAULT Keyword or NOT NULL Constraint
Assigning Values to a Variable With the PL/SQL SELECT INTO Statement
Using %TYPE and %ROWTYPE Attributes to Declare Identical Datatypes
Using Local PL/SQL Procedures and Functions in PL/SQL Blocks
As Example 4-1 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. For a discussion about exception handling, see "Handling PL/SQL Errors".
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. Errors that occur during execution can be dealt with in the exception-handling part.
Note the comments that are added to the PL/SQL code. See "Using Comments". Also, note the use of DBMS_OUTPUT.PUT_LINE to display output. See "Inputting and Outputting Data with PL/SQL".
Example 4-1 Using a Simple PL/SQL Block
-- the following is an optional declarative part DECLARE monthly_salary NUMBER(6); number_of_days_worked NUMBER(2); pay_per_day NUMBER(6,2); -- the following is the executable part, from BEGIN to END BEGIN monthly_salary := 2290; number_of_days_worked := 21; pay_per_day := monthly_salary/number_of_days_worked; -- the following displays output from the PL/SQL block DBMS_OUTPUT.PUT_LINE('The pay per day is ' || TO_CHAR(pay_per_day)); -- the following is an optional exception part that handles errors EXCEPTION WHEN ZERO_DIVIDE THEN pay_per_day := 0; END; /
For another example of a PL/SQL block structure, see Example 4-13.
See Also:
Oracle Database PL/SQL User's Guide and Reference for information about PL/SQL language elementsMost PL/SQL input and output is through SQL statements, to store data in database tables or to 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 the SQL Commands page or SQL Command Line (SQL*Plus), to read and display the data passed to DBMS_OUTPUT.
The SQL Commands page is configured to display output with DBMS_OUTPUT. SQL Command Line does not display DBMS_OUTPUT data unless you first issue the SQL*Plus command SET SERVEROUTPUT ON. For information about SQL Command Line SET command, see "SQL Command Line SET Commands" .
Example 4-2 show the use of DBMS_OUTPUT.PUTLINE. Note the use of SET SERVEROUTPUT ON to enable output.
Example 4-2 Using DBMS_OUTPUT.PUT_LINE to Display PL/SQL Output
-- enable SERVEROUTPUT in SQL Command Line (SQL*Plus) to display output with -- DBMS_OUTPUT.PUT_LINE, this enables SERVEROUTPUT for this SQL*Plus session only SET SERVEROUTPUT ON DECLARE answer VARCHAR2(20); -- declare a variable BEGIN -- assign a value to a variable answer := 'Maybe'; -- use PUT_LINE to display data from the PL/SQL block DBMS_OUTPUT.PUT_LINE( 'The answer is: ' || answer ); END; /
The DBMS_OUTPUT package is a predefined Oracle package. For information about Oracle supplied packages, see "Oracle Provided Packages".
See Also:
SQL*Plus User's Guide and Reference for information SQL*Plus commands
Oracle Database PL/SQL Packages and Types Reference for information about Oracle supplied packages
The PL/SQL compiler ignores comments, but you should not. Adding comments to your program improves readability and helps others understand your code. Generally, you use comments to describe the purpose and use of each code segment. PL/SQL supports single-line and multiple-line comment styles.
Single-line comments begin with a double hyphen (--) anywhere on a line and extend to the end of the line. Multiple-line comments begin with a slash and an asterisk (/*), end with an asterisk and a slash (*/), and can span multiple lines. See Example 4-3.
Example 4-3 Using Comments in PL/SQL
DECLARE -- Declare variables here. monthly_salary NUMBER(6); -- This is the monthly salary. number_of_days_worked NUMBER(2); -- This is the days in one month. pay_per_day NUMBER(6,2); -- Calculate this value. BEGIN -- First assign values to the variables. monthly_salary := 2290; number_of_days_worked := 21; -- Now calculate the value on the following line. pay_per_day := monthly_salary/number_of_days_worked; -- the following displays output from the PL/SQL block DBMS_OUTPUT.PUT_LINE('The pay per day is ' || TO_CHAR(pay_per_day)); EXCEPTION /* This is a simple example of an exeception handler to trap division by zero. In actual practice, it would be best to check whether a variable is zero before using it as a divisor. */ WHEN ZERO_DIVIDE THEN pay_per_day := 0; -- set to 0 if divisor equals 0 END; /
While testing or debugging a program, you might want to disable a line of code. The following example shows how you can disable a single line by making it a comment:
-- pay_per_day := monthly_salary/number_of_days_worked;
You can use multiple-line comment delimiters to comment out large sections of code.
Variables can have any SQL datatype, such as VARCHAR2, DATE, or NUMBER, or a PL/SQL-only datatype, such as a BOOLEAN or PLS_INTEGER. You can also declare nested tables, variable-size arrays (varrays for short), and records using the TABLE, VARRAY, and RECORD composite datatypes. See "Working With PL/SQL Data Structures".
Declaring a constant is similar to declaring a variable except that you must add the CONSTANT keyword and immediately assign a value to the constant. No further assignments to the constant are allowed. For an example, see the avg_days_worked_month constant in Example 4-4.
For example, assume that you want to declare variables for employee data, such as employee_id to hold 6-digit numbers and active_employee to hold the Boolean value TRUE or FALSE. You declare these and related employee variables and constants, as shown in Example 4-4.
Note that there is a semi colon (;) at the end of each line in the declaration section. Also, note the use of the NULL statement that enables you to run and test the PL/SQL block.
You can choose any naming convention for variables that is appropriate for your application, but the names must be valid PL/SQL identifiers. See "Using Identifiers in PL/SQL".
Example 4-4 Declaring Variables in PL/SQL
DECLARE -- declare the variables in this section last_name VARCHAR2(30); first_name VARCHAR2(25); employee_id NUMBER(6); active_employee BOOLEAN; monthly_salary NUMBER(6); number_of_days_worked NUMBER(2); pay_per_day NUMBER(6,2); avg_days_worked_month CONSTANT NUMBER(2) := 21; -- a constant variable BEGIN NULL; -- NULL statement does nothing, allows this block to executed and tested END; /
See Also:
Oracle Database PL/SQL User's Guide and Reference for information about datatypes used with PL/SQL, including the PL/SQL
BOOLEAN and PLS_INTEGER datatypesYou use identifiers to name PL/SQL program items and units, such as constants, variables, exceptions, and subprograms. An identifier consists of a letter optionally followed by more letters, numerals, dollar signs, underscores, and number signs. Characters such as ampersands (&), hyphens (-), slashes (/), and spaces ( ) are not allowed.
You can use uppercase, lowercase, or mixed case to write identifiers. PL/SQL is not case-sensitive except within string and character literals. Every character, including dollar signs, underscores, and number signs, is significant. If the only difference between identifiers is the case of corresponding letters, PL/SQL considers the identifiers the same.
The declaration section in Example 4-5 show some PL/SQL identifiers. You can see additional examples of identifiers for variable names in Example 4-3 and Example 4-4.
Example 4-5 Using Identifiers for Variables in PL/SQL
DECLARE lastname VARCHAR2(30); -- valid identifier last_name VARCHAR2(30); -- valid identifier, _ allowed last$name VARCHAR2(30); -- valid identifier, $ allowed last#name VARCHAR2(30); -- valid identifier, # allowed -- last-name is invalid, hypen not allowed -- last/name is invalid, slash not allowed -- last name is invalid, space not allowed -- LASTNAME is invalid, same as lastname and LastName -- LastName is invalid, same as lastname and LASTNAME BEGIN NULL; -- NULL statement does nothing, allows this block to executed and tested END; /
The size of an identifier cannot exceed 30 characters. Identifiers should be descriptive. When possible, avoid obscure names such as cpm. Instead, use meaningful names such as cost_per_million. You can use prefixes for more clarification. For example, you could begin each variable name with the var_ and each constant name with con_.
Some identifiers, called reserved words or keywords, have a special syntactic meaning to PL/SQL. For example, the words BEGIN and END are reserved. Often, reserved words and keywords are written in upper case for readability. Neither reserved words or keywords should be used as identifiers and the use can cause compilation errors. For a list of PL/SQL reserved words and keywords, see Appendix B, "Reserved Words".
You can assign values to a variable in several ways. One way uses the assignment operator (:=), a colon followed by an equal sign, as shown in Example 4-6. 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.
Example 4-6 Assigning Values to Variables With the PL/SQL Assignment Operator
DECLARE -- declare and assiging variables wages NUMBER(6,2); hours_worked NUMBER := 40; hourly_salary NUMBER := 22.50; bonus NUMBER := 150; country VARCHAR2(128); counter NUMBER := 0; done BOOLEAN := FALSE; valid_id BOOLEAN; BEGIN wages := (hours_worked * hourly_salary) + bonus; -- compute wages country := 'France'; -- assign a string literal country := UPPER('Canada'); -- assign an uppercase string literal done := (counter > 100); -- assign a BOOLEAN, in this case FALSE valid_id := TRUE; -- assign a BOOLEAN END; /
A literal is an explicit numeric, character, string, or Boolean value not represented by an identifier. For example, 147 is a numeric literal, and FALSE is a Boolean literal.
Numeric Literals
Two kinds of numeric literals can be used in arithmetic expressions: integer and real. An integer literal is an optionally signed whole number without a decimal point, such as +6. A real literal is an optionally signed whole or fractional number with a decimal point, such as -3.14159. PL/SQL considers a number such as 25. to be real, even though it has an integral value.
Numeric literals cannot contain dollar signs or commas, but can be written using scientific notation. Add an E (or e) after the base number, followed by an optionally signed integer, for example -9.5e-3. The E (or e) represents the base number ten and the following integer represents the exponent.
Example 4-7 shows some examples of numeric literals.
Example 4-7 Using Numeric Literals in PL/SQL
DECLARE -- declare and assign variables number1 PLS_INTEGER := 32000; -- numeric literal number2 NUMBER(8,3); BEGIN number2 := 3.125346e3; -- numeric literal number2 := -8300.00; -- numeric literal number2 := -14; -- numeric literal END; /
Character Literals
A character literal is an individual character enclosed by single quotation marks (apostrophes), such as '(' or '7'. Character literals include all the printable characters in the PL/SQL character set: letters, numbers, spaces, and special symbols.
PL/SQL is case-sensitive within character literals. For example, PL/SQL considers the character literals 'Z' and 'z' to be different. The character literals '0'...'9' are not equivalent to integer literals, but can be used in arithmetic expressions because they are implicitly convertible to integers.
Example 4-8 shows some examples of character literals.
Example 4-8 Using Character Literals in PL/SQL
DECLARE -- declare and assign variables char1 VARCHAR2(1) := 'x'; -- character literal char2 VARCHAR2(1); BEGIN char2 := '5'; -- character literal END; /
String Literals
A character value can be represented by an identifier or explicitly written as a string literal, which is a sequence of zero or more characters enclosed by single quotation marks, such as 'Hello, world!' and '$1,000,000'.
PL/SQL is case-sensitive within string literals. For example, PL/SQL considers the string literals 'baker' and 'Baker' to be different:
To represent an apostrophe within a string, you can use two single quotation marks (''), which is not the same as a quotation mark ("). You can also use the quote-delimiter mechanism, which enables you to specify q or Q followed by a single quotation mark and then another character to be used as the quotation mark delimiter. See "Using Character Literals in SQL Statements".
Example 4-9 shows some examples of string literals.
Example 4-9 Using String Literals in PL/SQL
DECLARE -- declare and assign variables string1 VARCHAR2(1000); string2 VARCHAR2(32767); BEGIN string1 := '555-111-2323'; -- the following needs two single quotation marks to represent one in the string string2 := 'Here''s an example of two single quotation marks used in a string.'; END; /
BOOLEAN Literals
BOOLEAN literals are the predefined values: TRUE, FALSE, and NULL. NULL is a missing, unknown, or inapplicable value. BOOLEAN literals are values, not strings.
Example 4-10 shows some examples of BOOLEAN literals.
Example 4-10 Using BOOLEAN Literals in PL/SQL
DECLARE -- declare and assign variables finished BOOLEAN := TRUE; -- BOOLEAN literal complete BOOLEAN; -- BOOLEAN literal true_or_false BOOLEAN; BEGIN finished := FALSE; -- BOOLEAN literal set to FALSE complete := NULL; -- BOOLEAN literal with unknown value true_or_false := (3 = 4); -- BOOLEAN literal set to FALSE true_or_false := (3 < 4); -- BOOLEAN literal set to TRUE END; /
Date-time literals have various formats depending on the date-time datatype used, such as '14-SEP-05' or '14-SEP-05 09:24:04 AM'.
Example 4-11 shows some examples of date-time literals.
Example 4-11 Using Date-time Literals in PL/SQL
DECLARE -- declare and assign variables date1 DATE := '11-AUG-2005'; -- DATE literal time1 TIMESTAMP; time2 TIMESTAMP WITH TIME ZONE; BEGIN time1 := '11-AUG-2005 11:01:01 PM'; -- TIMESTAMP literal time2 := '11-AUG-2005 09:26:56.66 PM +02:00'; -- TIMESTAMP WITH TIME ZONE END; /
See Also:
Oracle Database SQL Reference for information about the syntax for literals and the date and time types
Oracle Database Application Developer's Guide - Fundamentals for examples of performing date and time arithmetic
Oracle Database PL/SQL User's Guide and Reference for information about using literals with PL/SQL
You can use the DEFAULT keyword instead of the assignment operator to initialize variables when they are declared. Use DEFAULT for variables that have a typical value. Use the assignment operator for variables (such as counters and accumulators) that have no typical value. You can also use DEFAULT to initialize subprogram parameters, cursor parameters, and fields in a user-defined record.
In addition to assigning an initial value, declarations can impose the NOT NULL constraint so that assigning a NULL causes an error. The NOT NULL constraint must be followed by an initialization clause.
In Example 4-12 the declaration for the avg_days_worked_month variable uses the DEFAULT to assign a value of 21 and the declarations for the active_employee and monthly_salary variables use the NOT NULL constraint.
Example 4-12 Using DEFAULT and NOT NULL in PL/SQL
DECLARE -- declare and assign variables last_name VARCHAR2(30); first_name VARCHAR2(25); employee_id NUMBER(6); active_employee BOOLEAN NOT NULL := TRUE; -- value cannot be NULL monthly_salary NUMBER(6) NOT NULL := 2000; -- value cannot be NULL number_of_days_worked NUMBER(2); pay_per_day NUMBER(6,2); employee_count NUMBER(6) := 0; avg_days_worked_month NUMBER(2) DEFAULT 21; -- assign a default value BEGIN NULL; -- NULL statement does nothing, allows this block to executed and tested END; /
Another way to assign values to a variable is by selecting (or fetching) database values into it. With the PL/SQL SELECT INTO statement, you can retrieve data from one row in a table. In Example 4-13, 10 percent of the salary of an employee is selected into the bonus variable. Now, you can use the bonus variable in another computation, or insert its value into a database table.
In the example, the DBMS_OUTPUT.PUT_LINE procedure is used to display output from the PL/SQL program. For more information, see "Inputting and Outputting Data with PL/SQL".
Example 4-13 Assigning Values to Variables Using PL/SQL SELECT INTO
DECLARE -- declare and assign values bonus_rate CONSTANT NUMBER(2,3) := 0.05; bonus NUMBER(8,2); emp_id NUMBER(6) := 120; -- assign a test value for employee ID BEGIN -- retreive a salary from the employees table, then calculate the bonus and -- assign the value to the bonus variable SELECT salary * bonus_rate INTO bonus FROM employees WHERE employee_id = emp_id; -- display the employee_id, bonus amount, and bonus rate DBMS_OUTPUT.PUT_LINE ( 'Employee: ' || TO_CHAR(emp_id) || ' Bonus: ' || TO_CHAR(bonus) || ' Bonus Rate: ' || TO_CHAR(bonus_rate)); END; /
See Also:
Oracle Database PL/SQL User's Guide and Reference for information aboutSELECT INTO syntaxAs 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 NUMBER or VARCHAR2. For easier code maintenance that interacts with the database, you can also use the special qualifiers %TYPE and %ROWTYPE to declare variables that hold table columns or table rows.
This section contains the following topics:
The %TYPE attribute provides the datatype of a variable or table column. This is particularly useful when declaring variables that will hold values of a table column. For example, suppose you want to declare variables as the same datatype as the employee_id and last_name columns in employees table. To declare variables named empid and emplname that have the same datatype as the table columns, use dot notation and the %TYPE attribute. See Example 4-14.
Example 4-14 Using %TYPE With Table Columns in PL/SQL
DECLARE -- declare variables using %TYPE attribute empid employees.employee_id%TYPE; -- employee_id datatype is NUMBER(6) emplname employees.last_name%TYPE; -- last_name datatype is VARCHAR2(25) BEGIN empid := 100301; -- this is OK because it fits in NUMBER(6) -- empid := 3018907; -- this is too large and will cause an overflow emplname := 'Patel'; -- this is OK because it fits in VARCHAR2(25) DBMS_OUTPUT.PUT_LINE('Employee ID: ' || empid); -- display data DBMS_OUTPUT.PUT_LINE('Employee name: ' || emplname); -- display data END; /
Declaring variables with the %TYPE attribute has two advantages. First, you do not need to know the exact datatype of the table columns. Second, if you change the database definition of columns, such as employee_id or last_name, the datatypes of empid and emplname in Example 4-14 change accordingly at run time.
See Also:
Oracle Database PL/SQL User's Guide and Reference for information about the%TYPE attributeFor easier maintenance of code that interacts with the database, you can use the %ROWTYPE attribute to declare a variable that represents a row in a table. A PL/SQL record is the datatype that stores the same information as a row in a table.
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 record can store an entire row of data selected from the table or fetched from a cursor or cursor variable. For information about records, see "Using Record Types".
Columns in a row and corresponding fields in a record have the same names and datatypes. In Example 4-15, you declare a record named emp_rec. Its fields have the same names and datatypes as the columns in the employees table. You use dot notation to reference fields, such as emp_rec.last_name.
In Example 4-15, the SELECT statement is used to store row information from the employees table into the emp_rec record. When you run the SELECT INTO statement, the value in the first_name column of the employees table is assigned to the first_name field of emp_rec; the value in the last_name column is assigned to the last_name field of emp_rec; and so on.
Example 4-15 Using %ROWTYPE with a PL/SQL Record
DECLARE -- declare variables
-- declare record variable that represents a row fetched from the employees table
emp_rec employees%ROWTYPE; -- declare variable with %ROWTYPE attribute
BEGIN
SELECT * INTO emp_rec FROM EMPLOYEES WHERE employee_id = 120; -- retrieve record
DBMS_OUTPUT.PUT_LINE('Employee name: ' || emp_rec.first_name || ' '
|| emp_rec.last_name); -- display
END;
/
Declaring variables with the %ROWTYPE attribute has several advantages. First, you do not need to know the exact datatype of the table columns. Second, if you change the database definition of any of the table columns, the datatypes associated with the %ROWTYPE declaration change accordingly at run time.
See Also:
Oracle Database PL/SQL User's Guide and Reference for information about the%ROWTYPE attributeControl 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 IF-THEN-ELSE, CASE, FOR-LOOP, WHILE-LOOP, EXIT-WHEN, and GOTO.
This section contains the following topics:
Often, it is necessary to take alternative actions depending on circumstances. The IF-THEN statement lets you run a sequence of statements conditionally. The forms of the statement can be IF-THEN, IF-THEN-ELSE, or IF-THEN-ELSEIF-ELSE. 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 4-16 shows a simple use of the IF-THEN statement.
Example 4-16 Using a Simple IF-THEN Statement in PL/SQL
DECLARE
sal NUMBER(8,2);
bonus NUMBER(6,2);
hiredate DATE;
empid NUMBER(6) := 128; -- use employee 120 for testing
BEGIN
-- retrieve the salary and the date that employee was hired, the date is checked
-- to calculate the amount of the bonus for the employee
SELECT salary, hire_date INTO sal, hiredate FROM employees
WHERE employee_id = empid;
IF hiredate > TO_DATE('01-JAN-00') THEN
bonus := sal/20;
DBMS_OUTPUT.PUT_LINE('Bonus for employee: ' || empid || ' is: ' || bonus );
END IF;
END;
/
Example 4-17 shows the use of IF-THEN-ELSEIF-ELSE to determine the salary raise an employee receives based on the hire date of the employee.
Example 4-17 Using the IF-THEN-ELSEIF Statement in PL/SQL
DECLARE bonus NUMBER(6,2); empid NUMBER(6) := 120; hiredate DATE; BEGIN -- retrieve the date that employee was hired, the date is checked -- to determine the amount of the bonus for the employee SELECT hire_date INTO hiredate FROM employees WHERE employee_id = empid; IF hiredate > TO_DATE('01-JAN-98') THEN bonus := 500; ELSIF hiredate > TO_DATE('01-JAN-96') THEN bonus := 1000; ELSE bonus := 1500; END IF; DBMS_OUTPUT.PUT_LINE('Bonus for employee: ' || empid || ' is: ' || bonus ); END; /
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. When possible, rewrite lengthy IF-THEN-ELSIF statements as CASE statements because the CASE statement is more readable and more efficient.
Example 4-18 shows a simple CASE statement.
Example 4-18 Using the CASE-WHEN Statement in PL/SQL
DECLARE grade CHAR(1); BEGIN grade := 'B'; CASE grade WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent'); WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good'); WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('Good'); WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair'); WHEN 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor'); ELSE DBMS_OUTPUT.PUT_LINE('No such grade'); END CASE; END; /
Example 4-19 determines the salary raise an employee receives based on the current salary of the employee and the job ID. This complex example combines the CASE expression with IF-THEN-ELSE statements.
Example 4-19 Using the IF-THEN_ELSE and CASE Statement in PL/SQL
DECLARE -- declare variables empid NUMBER(6) := 115; jobid VARCHAR2(10); sal NUMBER(8,2); sal_raise NUMBER(3,2); -- this is the rate of increase for the raise BEGIN -- retrieve the job ID and salary for the employee and -- assign the values to variables jobid and sal SELECT job_id, salary INTO jobid, sal from employees WHERE employee_id = empid; CASE -- determine the salary raise rate based on employee job ID WHEN jobid = 'PU_CLERK' THEN IF sal < 3000 THEN sal_raise := .08; ELSE sal_raise := .07; END IF; WHEN jobid = 'SH_CLERK' THEN IF sal < 4000 THEN sal_raise := .06; ELSE sal_raise := .05; END IF; WHEN jobid = 'ST_CLERK' THEN IF sal < 3500 THEN sal_raise := .04; ELSE sal_raise := .03; END IF; ELSE BEGIN -- if no conditions met, then the following DBMS_OUTPUT.PUT_LINE('No raise for this job: ' || jobid); END; END CASE; -- display the percent raise for the employee DBMS_OUTPUT.PUT_LINE('Percent salary raise for employee: ' || empid || ' is: ' || sal_raise ); 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 run a sequence of statements multiple times. You place the keyword LOOP before the first statement in the sequence and the keywords END LOOP after the last statement in the sequence.
The FOR-LOOP statement lets you specify a range of integers, then run a sequence of statements once for each integer in the range. In Example 4-20, the loop displays the number and the square of the number for numbers 1 to 10. Note that you do not have to declare or initialize the counter in the FOR-LOOP and any valid identifier can be used for the name, such as loop_counter.
Example 4-20 Using the FOR-LOOP in PL/SQL
BEGIN -- use a FOR loop to process a series of numbers FOR loop_counter IN 1..10 LOOP DBMS_OUTPUT.PUT_LINE('Number: ' || TO_CHAR(loop_counter) || ' Square: ' || TO_CHAR(loop_counter**2)); END LOOP; END; /
The 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 4-21, the loop displays the number and the cube of the number while the number is less than or equal to 10.
Example 4-21 Using WHILE-LOOP for Control in PL/SQL
DECLARE -- declare variables i NUMBER := 1; -- loop counter, initialize to one i_cubed NUMBER; BEGIN -- use WHILE LOOP to process data WHILE i <= 10 LOOP i_cubed := i**3; DBMS_OUTPUT.PUT_LINE('Number: ' || TO_CHAR(i) || ' Cube: ' || TO_CHAR(i_cubed)); i := i + 1; END LOOP; END; /
The 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 4-22, the loop completes when the value of total exceeds 25,000:
Example 4-22 Using the EXIT-WHEN Statement in PL/SQL
DECLARE -- declare and assign values to variables total NUMBER(9) := 0; counter NUMBER(6) := 0; BEGIN LOOP counter := counter + 1; -- increment counter variable total := total + counter * counter; -- compute total -- exit loop when condition is true EXIT WHEN total > 25000; -- LOOP until condition is met END LOOP; DBMS_OUTPUT.PUT_LINE('Counter: ' || TO_CHAR(counter) || ' Total: ' || TO_CHAR(total)); -- display results END; /
The GOTO statement lets you branch to a label unconditionally; however, you would usually try to avoid exiting a loop in this manner. 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.
Example 4-23 shows the use of the GOTO statement in a loop that is testing for prime numbers. When a number can be divided into evenly (no remainder), then it is not a prime and the loop is immediately exited. Note the use of the SQL numeric function MOD to check for no (zero) remainder. See "Using Numeric Functions" for information about SQL numeric functions.
Example 4-23 Using the GOTO Statement in PL/SQL
DECLARE -- declare variables
p VARCHAR2(30);
n PLS_INTEGER := 37; -- test any integer > 2 for prime, here 37
BEGIN
-- loop through divisors to determine if a prime number
FOR j in 2..ROUND(SQRT(n))
LOOP
IF n MOD j = 0 THEN -- test for prime
p := ' is NOT a prime number'; -- not a prime number
GOTO print_now;
END IF;
END LOOP;
p := ' is a prime number';
<<print_now>>
DBMS_OUTPUT.PUT_LINE(TO_CHAR(n) || p); -- display results
END;
/
Procedures and functions (subprograms) are named PL/SQL blocks that can be called with a set of parameters from inside of a PL/SQL block.
A procedure is a subprogram that performs a specific action. You specify the name of the procedure, its parameters, its local variables, and the BEGIN-END block that contains its code and handles any exceptions. A function is a subprogram that computes and returns a value. Functions and procedures are structured alike, except that functions return a value.
When passing parameters to functions and procedures, the parameters can be declared as IN or OUT or IN OUT parameters.
IN indicates that you must supply a value for the argument when calling the function or procedure. This is the default.
OUT indicates that the function or procedure will set the value of the argument.
IN OUT indicates that a value for the argument can be supplied by you and can be set by the function or procedure.
Example 4-24 is an example of a declaration of a PL/SQL procedure in a PL/SQL block. Note that the v1 and v2 variables are declared as IN OUT parameters to a subprogram.
Example 4-24 Declaring a Local PL/SQL Procedure With IN OUT Parameters
DECLARE -- declare variables and subprograms fname VARCHAR2(20) := 'randall'; lname VARCHAR2(25) := 'dexter'; -- declare a local procedure which can only be used in this block PROCEDURE upper_name ( v1 IN OUT VARCHAR2, v2 IN OUT VARCHAR2) AS BEGIN v1 := UPPER(v1); -- change the string to uppercase v2 := UPPER(v2); -- change the string to uppercase END upper_name; -- start of executable part of block BEGIN DBMS_OUTPUT.PUT_LINE(fname || ' ' || lname ); -- display initial values upper_name (fname, lname); -- call the procedure with parameters DBMS_OUTPUT.PUT_LINE(fname || ' ' || lname ); -- display new values END; /
Example 4-25 is an example of a declaration of a PL/SQL function in a PL/SQL block. Note that the value returned by the function is used directly in the DBMS_OUTPUT.PUT_LINE statement. Note that the v1 and v2 variables are declared as IN parameters to a subprogram. An IN parameter passes an initial value that is read inside of a subprogram. Any update to the value of the parameter inside of the subprogram is not accessible outside of the subprogram.
Example 4-25 Declaring a Local PL/SQL Function With IN Parameters
DECLARE -- declare variables and subprograms fname VARCHAR2(20) := 'randall'; lname VARCHAR2(25) := 'dexter'; -- declare local function which can only be used in this block FUNCTION upper_name ( v1 IN VARCHAR2, v2 IN VARCHAR2) RETURN VARCHAR2 AS v3 VARCHAR2(45); -- this variable is local to the function BEGIN -- build a string that will be returned as the function value v3 := v1 || ' + ' || v2 || ' = ' || UPPER(v1) || ' ' || UPPER(v2); RETURN v3; -- return the value of v3 END upper_name; -- start of executable part of block BEGIN -- call the function and display results DBMS_OUTPUT.PUT_LINE(upper_name (fname, lname)); END; /
In Example 4-26, both a variable and a numeric literal are passed as a parameter to a more complex procedure.
Example 4-26 Declaring a Complex Local Procedure in a PL/SQL Block
DECLARE -- declare variables and subprograms empid NUMBER; -- declare local procedure for this block PROCEDURE avg_min_max_sal (empid IN NUMBER) IS jobid VARCHAR2(10); avg_sal NUMBER; min_sal NUMBER; max_sal NUMBER; BEGIN -- determine the job ID for the employee SELECT job_id INTO jobid FROM employees WHERE employee_id = empid; -- calculate the average, minimum, and maximum salaries for that job ID SELECT AVG(salary), MIN(salary), MAX(salary) INTO avg_sal, min_sal, max_sal FROM employees WHERE job_id = jobid; -- display data DBMS_OUTPUT.PUT_LINE ('Employee ID: ' || empid || ' Job ID: ' || jobid); DBMS_OUTPUT.PUT_LINE ('The average salary for job ID: ' || jobid || ' is ' || TO_CHAR(avg_sal)); DBMS_OUTPUT.PUT_LINE ('The minimum salary for job ID: ' || jobid || ' is ' || TO_CHAR(min_sal)); DBMS_OUTPUT.PUT_LINE ('The maximum salary for job ID: ' || jobid || ' is ' || TO_CHAR(max_sal)); END avg_min_max_sal; -- end of local procedure -- start executable part of block BEGIN -- call the procedure with several employee IDs empid := 125; avg_min_max_sal(empid); avg_min_max_sal(112); END; /
Subprograms can also be declared in packages. For an example of a subprogram declaration in a package, see Example 5-9. You can create standalone subprograms that are stored in the database. These subprograms can be called from other subprograms, packages, and SQL statements. See Chapter 5, "Using Procedures, Functions, and Packages".
A cursor is a name for a private SQL area in which information for processing the specific statement is kept. PL/SQL uses both implicit and explicit cursors. Cursor attributes return useful information about the status of cursors in the execution of SQL statements.
PL/SQL implicitly creates a cursor for all SQL data manipulation statements on a set of rows, including queries that return only one row. Implicit cursors are managed automatically by PL/SQL so you are not required to write any code to handle these cursors. However, you can track information about the execution of an implicit cursor through its cursor attributes.
You can explicitly declare a cursor for one row or multiple rows if you want precise control over query processing. You must declare an explicit cursor for queries that return more than one row. For queries that return multiple rows, you can process the rows individually.
A cursor variable (REF CURSOR) is similar to a cursor and points to the current row in the result set of a multi-row query.
This section contains the following topics:
Example 4-27 is an example of explicit cursor used to process one row of a table.You should explicitly open and close a cursor before and after use.
Example 4-27 Fetching a Single Row With a Cursor in PL/SQL
DECLARE -- declare variables for first_name and last_name fetched from the employees table firstname employees.first_name%TYPE; -- variable for first_name lastname employees.last_name%TYPE; -- variable for last_name -- declare a cursor to fetch data from a row (employee 120) in the employees table CURSOR cursor1 IS SELECT first_name, last_name FROM employees WHERE employee_id = 120; BEGIN OPEN cursor1; -- open the cursor FETCH cursor1 INTO firstname, lastname; -- fetch data into local variables DBMS_OUTPUT.PUT_LINE('Employee name: ' || firstname || ' ' || lastname); CLOSE cursor1; -- close the cursor END; /
Example 4-28 shows examples of the use of a cursor to process multiple rows in a table. The FETCH statement retrieves the rows in the result set one at a time. Each fetch retrieves the current row and advances the cursor to the next row in the result set. Note the use of the cursor attributes %ROWCOUNT and %NOTFOUND. For information about cursor attributes, see "Cursor Attributes".
Example 4-28 Fetching Multiple Rows With a Cursor in PL/SQL
DECLARE -- declare variables for data fetched from cursors empid employees.employee_id%TYPE; -- variable for employee_id jobid employees.job_id%TYPE; -- variable for job_id lastname employees.last_name%TYPE; -- variable for last_name rowcount NUMBER; -- declare the cursors CURSOR cursor1 IS SELECT last_name, job_id FROM employees WHERE job_id LIKE '%CLERK'; CURSOR cursor2 is SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%MAN' OR job_id LIKE '%MGR'; BEGIN -- start the processing with cursor1 OPEN cursor1; -- open cursor1 before fetching DBMS_OUTPUT.PUT_LINE( '---------- cursor 1-----------------' ); LOOP FETCH cursor1 INTO lastname, jobid; -- fetches 2 columns into variables -- check the cursor attribute NOTFOUND for the end of data EXIT WHEN cursor1%NOTFOUND; -- display the last name and job ID for each record (row) fetched DBMS_OUTPUT.PUT_LINE( RPAD(lastname, 25, ' ') || jobid ); END LOOP; rowcount := cursor1%ROWCOUNT; DBMS_OUTPUT.PUT_LINE('The number of rows fetched is ' || rowcount ); CLOSE cursor1; -- start the processing with cursor2 OPEN cursor2; DBMS_OUTPUT.PUT_LINE( '---------- cursor 2-----------------' ); LOOP -- fetch 3 columns into the variables FETCH cursor2 INTO empid, lastname, jobid; EXIT WHEN cursor2%NOTFOUND; -- display the employee ID, last name, and job ID for each record (row) fetched DBMS_OUTPUT.PUT_LINE( empid || ': ' || RPAD(lastname, 25, ' ') || jobid ); END LOOP; rowcount := cursor2%ROWCOUNT; DBMS_OUTPUT.PUT_LINE('The number of rows fetched is ' || rowcount ); CLOSE cursor2; END; /
In Example 4-28, the LIKE condition operator is used to specify the records to return with the query. For information about LIKE, see "Restricting Data Using the WHERE Clause".
Example 4-29 shows how to pass a parameter to an explicit cursor. In the example, the current month value is passed to the cursor to specify that only those employees hired during this month are displayed. This provides a list of employees that have their yearly anniversary dates and their bonus amount.
Example 4-29 Passing Parameters to a Cursor in PL/SQL
DECLARE -- declare variables for data fetched from cursor empid employees.employee_id%TYPE; -- variable for employee_id hiredate employees.hire_date%TYPE; -- variable for hire_date firstname employees.first_name%TYPE; -- variable for first_name lastname employees.last_name%TYPE; -- variable for last_name rowcount NUMBER; bonusamount NUMBER; yearsworked NUMBER; -- declare the cursor with a parameter, CURSOR cursor1 (thismonth NUMBER)IS SELECT employee_id, first_name, last_name, hire_date FROM employees WHERE EXTRACT(MONTH FROM hire_date) = thismonth; BEGIN -- open and pass a parameter to cursor1, select employees hired on this month OPEN cursor1(EXTRACT(MONTH FROM SYSDATE)); DBMS_OUTPUT.PUT_LINE('----- Today is ' || TO_CHAR(SYSDATE, 'DL') || ' -----'); DBMS_OUTPUT.PUT_LINE('Employees with yearly bonus amounts:'); LOOP -- fetches 4 columns into variables FETCH cursor1 INTO empid, firstname, lastname, hiredate; -- check the cursor attribute NOTFOUND for the end of data EXIT WHEN cursor1%NOTFOUND; -- calculate the yearly bonus amount based on months (years) worked yearsworked := ROUND( (MONTHS_BETWEEN(SYSDATE, hiredate)/12) ); IF yearsworked > 10 THEN bonusamount := 2000; ELSIF yearsworked > 8 THEN bonusamount := 1600; ELSIF yearsworked > 6 THEN bonusamount := 1200; ELSIF yearsworked > 4 THEN bonusamount := 800; ELSIF yearsworked > 2 THEN bonusamount := 400; ELSIF yearsworked > 0 THEN bonusamount := 100; END IF; -- display the employee Id, first name, last name, hire date, and bonus -- for each record (row) fetched DBMS_OUTPUT.PUT_LINE( empid || ' ' || RPAD(firstname, 21, ' ') || RPAD(lastname, 26, ' ') || hiredate || TO_CHAR(bonusamount, '$9,999')); END LOOP; rowcount := cursor1%ROWCOUNT; DBMS_OUTPUT.PUT_LINE('The number of rows fetched is ' || rowcount ); CLOSE cursor1; END; /
See Also:
Oracle Database PL/SQL User's Guide and Reference for information about managing cursors with PL/SQLCursor variables (REF CURSORs) are like pointers to result sets. A cursor variable is more flexible than a cursor because it is not tied to a specific query. You can open a cursor variable for any query that returns the correct set of columns.
Cursor variables are used when you want to perform a query in one function or procedure, and process the results in a different subprogram, possibly in a different language. A cursor variable has the datatype REF CURSOR, and is often referred to informally as a REF CURSOR.
A REF CURSOR can be declared with a return type (strong type) or without a return type (weak type). A strong REF CURSOR type is less error prone because the PL/SQL compiler lets you associate a strongly typed cursor variable only with queries that return the right set of columns. A weak REF CURSOR types is more flexible because the compiler lets you associate a weakly typed cursor variable with any query. Because there is no type checking with a weak REF CURSOR, all such types are interchangeable. Instead of creating a new type, you can use the predefined type SYS_REFCURSOR.
Example 4-30 show how to declare a cursor variable of REF CURSOR datatype, then use that cursor variable as a formal parameter in a procedure. For additional examples of the use of REF CURSOR, see "Accessing Types in Packages". For an example of the use of a REF CURSOR with a PHP program, see Appendix C, "Using a PL/SQL Procedure With PHP". For an example of the use of a REF CURSOR with a Java program, see Appendix D, "Using a PL/SQL Procedure With JDBC".
Example 4-30 Using a Cursor Variable (REF CURSOR)
DECLARE -- declare a REF CURSOR that returns employees%ROWTYPE (strongly typed) TYPE emp_refcur_typ IS REF CURSOR RETURN employees%ROWTYPE; emp_cursor emp_refcur_typ; -- use the following local procedure to process all the rows after -- the result set is built, rather than calling a procedure for each row PROCEDURE process_emp_cv (emp_cv IN emp_refcur_typ) IS person employees%ROWTYPE; BEGIN DBMS_OUTPUT.PUT_LINE('-- Here are the names from the result set --'); LOOP FETCH emp_cv INTO person; EXIT WHEN emp_cv%NOTFOUND; DBMS_OUTPUT.PUT_LINE(person.last_name || ', ' || person.first_name); END LOOP; END; BEGIN -- find employees whose employee ID is less than 108 OPEN emp_cursor FOR SELECT * FROM employees WHERE employee_id < 108; process_emp_cv(emp_cursor); -- pass emp_cursor to the procedure for processing CLOSE emp_cursor; -- find employees whose last name starts with R OPEN emp_cursor FOR SELECT * FROM employees WHERE last_name LIKE 'R%'; process_emp_cv(emp_cursor); -- pass emp_cursor to the procedure for processing CLOSE emp_cursor; END; /
See Also:
Oracle Database PL/SQL User's Guide and Reference for information about using cursor variables (REF CURSORs)Cursor attributes return information about the execution of DML and DDL statements, such INSERT, UPDATE, DELETE, SELECT INTO, COMMIT, or ROLLBACK statements. The cursor attributes are %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT. These attributes return useful information about the most recently executed SQL statement. When using an explicit cursor, add the explicit cursor or cursor variable name to the beginning of the attribute, such as cursor1%FOUND, to return information for the most recently executed SQL statement for that cursor.
The attributes provide the following information:
%FOUND Attribute: Has a Row Been Fetched?
After a cursor or cursor variable is opened but before the first fetch, %FOUND returns NULL. After any fetches, it returns TRUE if the last fetch returned a row, or FALSE if the last fetch did not return a row.
%ISOPEN Attribute: Is the Cursor Open?
If a cursor or cursor variable is open, then %ISOPEN returns TRUE ; otherwise, %ISOPEN returns FALSE.
Note that implicit cursors are automatically opened before and closed after executing the associated SQL statement so %ISOPEN always returns FALSE.
%NOTFOUND Attribute: Has a Fetch Failed?
If the last fetch returned a row, then %NOTFOUND returns FALSE. If the last fetch failed to return a row, then %NOTFOUND returns TRUE. %NOTFOUND is the logical opposite of %FOUND.
%ROWCOUNT Attribute: How Many Rows Fetched So Far?
After a cursor or cursor variable is opened, %ROWCOUNT returns 0 before the first fetch. Thereafter, it returns the number of rows fetched so far. The number is incremented if the last fetch returned a row.
See Also:
Oracle Database PL/SQL User's Guide and Reference for information about cursor attributesData structure are composite datatypes that let 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.
This section contains the following topics:
Record types 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 the TYPE definition, as shown in Example 4-31.
Usually you would use a record to hold data from an entire row of a database table. 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. When using %ROWTYPE, the record type definition is implied, and the TYPE keyword is not necessary, as shown in Example 4-32.
Example 4-31 shows how are records are declared and initialized.
Example 4-31 Declaring and Initializing a PL/SQL Record Type
DECLARE -- declare RECORD type variables -- the following is a RECORD declaration to hold address information TYPE location_rec IS RECORD ( room_number NUMBER(4), building VARCHAR2(25) ); -- you use the %TYPE attribute to declare the datatype of a table column -- you can include (nest) a record inside of another record TYPE person_rec IS RECORD ( employee_id employees.employee_id%TYPE, first_name employees.first_name%TYPE, last_name employees.last_name%TYPE, location location_rec ); person person_rec; -- declare a person variable of type person_rec BEGIN -- insert data in a record, one field at a time person.employee_id := 20; person.first_name := 'James'; person.last_name := 'Boynton'; person.location.room_number := 100; person.location.building:= 'School of Education'; -- display data in a record DBMS_OUTPUT.PUT_LINE( person.last_name || ', ' || person.first_name ); DBMS_OUTPUT.PUT_LINE( TO_CHAR(person.location.room_number) || ' ' || person.location.building ); END; /
Example 4-32 shows the use of %ROWTYPE in a record type declaration. This record is used with a cursor that fetches an entire row.
Example 4-32 Using %ROWTYPE With a Cursor When Declaring a PL/SQL Record
DECLARE -- declare variables
CURSOR cursor1 IS
SELECT * FROM employees
WHERE department_id = 60; -- declare cursor
-- declare record variable that represents a row fetched from the employees table
-- do not need to use TYPE .. IS RECORD with %ROWTYPE attribute
employee_rec cursor1%ROWTYPE;
BEGIN
-- open the explicit cursor c1 and use it to fetch data into employee_rec
OPEN cursor1;
LOOP
FETCH cursor1 INTO employee_rec; -- retrieve entire row into record
EXIT WHEN cursor1%NOTFOUND;
-- the record contains all the fields for a row in the employees table
-- the following displays the data from the row fetched into the record
DBMS_OUTPUT.PUT_LINE( ' Department ' || employee_rec.department_id
|| ', Employee: ' || employee_rec.employee_id || ' - '
|| employee_rec.last_name || ', ' || employee_rec.first_name );
END LOOP;
CLOSE cursor1;
END;
/
Example 4-34 shows the use of record as an element in a varray.
See Also:
Oracle Database PL/SQL User's Guide and Reference for information about PL/SQL recordsPL/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. To reference an element, use subscript notation with parentheses.
Example 4-33 shows the use of a varray with elements of character type. A varray must be initialized before use. When initializing a varry, you can also insert values into the elements. After initialization, you need to use EXTEND to add additional elements before inserting more values into the varray.
Example 4-33 Using a PL/SQL VARRAY Type With Character Elements
DECLARE -- declare variables TYPE jobids_array IS VARRAY(20) OF VARCHAR2(10); -- declare VARRAY jobids jobids_array; -- declare a variable of type jobids_array howmany NUMBER; -- declare a variable to hold employee count BEGIN -- initialize the arrary with some job ID values jobids := jobids_array('AC_ACCOUNT', 'AC_MGR', 'AD_ASST', 'AD_PRES', 'AD_VP', 'FI_ACCOUNT', 'FI_MGR', 'HR_REP', 'IT_PROG', 'PU_MAN', 'SH_CLERK', 'ST_CLERK', 'ST_MAN'); -- display the current size of the array with COUNT DBMS_OUTPUT.PUT_LINE('The number of elements (current size) in the array is ' || jobids.COUNT); -- display the maximum number of elements for the array LIMIT DBMS_OUTPUT.PUT_LINE('The maximum number (limit) of elements in the array is ' || jobids.LIMIT); -- check whether another element can be added to the array IF jobids.LIMIT - jobids.COUNT >= 1 THEN jobids.EXTEND(1); -- add one more element jobids(14) := 'PU_CLERK'; -- assign a value to the element END IF; -- loop through all the varray values, starting -- with the FIRST and ending with the LAST element FOR i IN jobids.FIRST..jobids.LAST LOOP -- determine the number of employees for each job ID in the array SELECT COUNT(*) INTO howmany FROM employees WHERE job_id = jobids(i); DBMS_OUTPUT.PUT_LINE ( 'Job ID: ' || RPAD(jobids(i), 10, ' ') || ' Number of employees: ' || TO_CHAR(howmany)); END LOOP; -- display the current size of the array with COUNT DBMS_OUTPUT.PUT_LINE('The number of elements (current size) in the array is ' || jobids.COUNT); END; /
Example 4-34 shows the use of a varray with record type elements.
Example 4-34 Using a PL/SQL VARRAY Type With Record Type Elements
DECLARE -- declare variables CURSOR cursor1 IS SELECT * FROM jobs; -- create a cursor for fetching the rows jobs_rec cursor1%ROWTYPE; -- create a record to hold the row data -- declare VARRAY with enough elements to hold all the rows in the jobs table TYPE jobs_array IS VARRAY(25) OF cursor1%ROWTYPE; jobs_arr jobs_array; -- declare a variable of type jobids_array howmany NUMBER; -- declare a variable to hold employee count i NUMBER := 1; -- counter for the number of elements in the array BEGIN jobs_arr := jobs_array(); -- initialize the array before using OPEN cursor1; -- open the cursor before using LOOP FETCH cursor1 INTO jobs_rec; -- retrieve a row from the jobs table EXIT WHEN cursor1%NOTFOUND; -- exit when no data is retrieved jobs_arr.EXTEND(1); -- add another element to the varray with EXTEND jobs_arr(i) := jobs_rec; -- assign the fetched row to an element the array i := i + 1; -- increment the element count END LOOP; CLOSE cursor1; -- close the cursor when finished with it FOR j IN jobs_arr.FIRST..jobs_arr.LAST LOOP -- loop through the varray elements -- determine the number of employees for each job ID in the array SELECT COUNT(*) INTO howmany FROM employees WHERE job_id = jobs_arr(j).job_id; DBMS_OUTPUT.PUT_LINE ( 'Job ID: ' || RPAD(jobs_arr(j).job_id, 11, ' ') || RPAD(jobs_arr(j).job_title, 36, ' ') || ' Number of employees: ' || TO_CHAR(howmany)); END LOOP; END; /
Collections can be passed as parameters, so that subprograms can process arbitrary numbers of elements.
See Also:
Oracle Database PL/SQL User's Guide and Reference for information about PL/SQL collectionsWhen you embed an INSERT, UPDATE, DELETE, or SELECT SQL statement directly in your PL/SQL code, PL/SQL turns the variables in the WHERE and VALUES clauses into bind variables automatically. Oracle Database XE can reuse these SQL statement each time the same code is executed. When running similar statements with different variable values, you can improve performance by calling a stored procedure that accepts parameters, then issues the statements with the parameters substituted in the appropriate places.
You need to specify bind variables with dynamic SQL, in clauses such as WHERE and 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 (preceded 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 Database XE reuse the SQL statements.
In Example 4-35, :dptid, :dptname, :mgrid, and :locid are examples of bind variables.
See Also:
"About Bind Variables" in Oracle Database Express Edition Application Express User's GuidePL/SQL supports both dynamic and static SQL. Dynamic SQL enables you to build SQL statements dynamically at run time while static SQL statements are known in advance. You can create more general-purpose, flexible applications by using dynamic SQL because the full text of a SQL statement may be unknown at compilation time.
To process most dynamic SQL statements, you use the EXECUTE IMMEDIATE statement. Dynamic SQL is especially useful for executing SQL statements to create database objects, such as CREATE TABLE.
Example 4-35 shows an example of the use of dynamic SQL to manipulate data in a table.
Example 4-35 Using Dynamic SQL to Manipulate Data in PL/SQL
DECLARE sql_stmt VARCHAR2(200); -- variable to hold SQL statement column_name VARCHAR2(30); -- variable for column name dept_id NUMBER(4); dept_name VARCHAR2(30); mgr_id NUMBER(6); loc_id NUMBER(4); BEGIN -- create a SQL statement (sql_stmt) to execute with EXECUTE IMMEDIATE -- the statement INSERTs a row into the departments table using bind variables -- note that there is no semi-colon (;) inside the quotation marks '...' sql_stmt := 'INSERT INTO departments VALUES (:dptid, :dptname, :mgrid, :locid)'; dept_id := 46; dept_name := 'Special Projects'; mgr_id := 200; loc_id := 1700; -- execute the sql_stmt using the values of the variables in the USING clause -- for the bind variables EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, mgr_id, loc_id; -- use EXECUTE IMMEDIATE to delete the row that was previously inserted, -- substituting for the column name and using a bind variable column_name := 'DEPARTMENT_ID'; EXECUTE IMMEDIATE 'DELETE FROM departments WHERE ' || column_name || ' = :num' USING dept_id; END; /
Example 4-36 is an example of the use of dynamic SQL to create a table. For a more complete example, see Example 5-3.
Example 4-36 Using Dynamic SQL to Create a Table in PL/SQL
DECLARE
tabname VARCHAR2(30); -- variable for table name
current_date VARCHAR2(8); -- varible for current date
BEGIN
-- extract, format, and insert the year, month, and day from SYSDATE into
-- the current_date variable
SELECT TO_CHAR(EXTRACT(YEAR FROM SYSDATE)) ||
TO_CHAR(EXTRACT(MONTH FROM SYSDATE),'FM09') ||
TO_CHAR(EXTRACT(DAY FROM SYSDATE),'FM09') INTO current_date FROM DUAL;
-- construct the table name with the current date as a suffix
tabname := 'log_table_' || current_date;
-- use EXECUTE IMMEDIATE to create a table with tabname as the table name
EXECUTE IMMEDIATE 'CREATE TABLE ' || tabname ||
'(op_time VARCHAR2(10), operation VARCHAR2(50))' ;
DBMS_OUTPUT.PUT_LINE(tabname || ' has been created');
-- now drop the table
EXECUTE IMMEDIATE 'DROP TABLE ' || tabname;
END;
/
See Also:
Oracle Database Application Developer's Guide - Fundamentals for additional information about dynamic SQLPL/SQL makes it easy to detect and process error conditions known as exceptions. When an error occurs, an exception is raised: normal processing 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.
The exception handling for PL/SQL 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 ZERO_DIVIDE automatically. See "Summary of Predefined PL/SQL Exceptions".
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 "Declaring PL/SQL Exceptions".
This section contains the following topics:
See Also:
Oracle Database PL/SQL User's Guide and Reference for information about handling PL/SQL errorsAn internal exception is raised automatically if your PL/SQL program violates an Oracle rule or exceeds a system-dependent limit. In PL/SQL common Oracle errors are predefined as exceptions. For example, PL/SQL raises the predefined exception NO_DATA_FOUND if a SELECT INTO statement returns no rows. To handle unexpected Oracle errors, you can use the OTHERS handler.
PL/SQL declares predefined exceptions globally in package STANDARD so you do not need to declare them. You can write handlers for predefined exceptions using the predefined names. Table 4-1 lists some of the predefined exceptions.
Table 4-1 Predefined PL/SQL Exceptions
| Exception | Description |
|---|---|
|
A program attempts to assign values to the attributes of an uninitialized object |
|
|
None of the choices in the |
|
|
A program attempts to apply collection methods other than |
|
|
A program attempts to open a cursor that is already open. A cursor must be closed before it can be reopened. A cursor |
|
|
A program attempts to store duplicate values in a column that is constrained by a unique index. |
|
|
A program attempts a cursor operation that is not allowed, such as closing an unopened cursor. |
|
|
In a SQL statement, the conversion of a character string into a number fails because the string does not represent a valid number. (In procedural statements, |
|
|
A program attempts to log on to Oracle Database XE with a user name or password that is not valid. |
|
|
A Because this exception is used internally by some SQL functions to signal completion, do not rely on this exception being propagated if you raise it within a function that is called as part of a query. |
|
|
A program issues a database call without being connected to Oracle Database XE. |
|
|
The host cursor variable and PL/SQL cursor variable involved in an assignment have incompatible return types. When an open host cursor variable is passed to a stored subprogram, the return types of the actual and formal parameters must be compatible. |
|
|
A program references a nested table or varray element using an index number larger than the number of elements in the collection. |
|
|
A program references a nested table or varray element using an index number (-1 for example) that is outside the legal range. |
|
|
A |
|
|
An arithmetic, conversion, truncation, or size-constraint error occurs. For example, when your program selects a column value into a character variable, if the value is longer than the declared length of the variable, PL/SQL cancels the assignment and raises |
|
|
A program attempts to divide a number by zero. |
Using exceptions for error handling has several advantages. With exceptions, you can reliably handle potential errors from many statements with a single exception handler, as shown in Example 4-37.
Example 4-37 Managing Multiple Errors With a Single PL/SQL Exception Handler
DECLARE -- declare variables
emp_column VARCHAR2(30) := 'last_name';
table_name VARCHAR2(30) := 'emp'; -- set value to raise error
temp_var VARCHAR2(30);
BEGIN
temp_var := emp_column;
SELECT COLUMN_NAME INTO temp_var FROM USER_TAB_COLS
WHERE TABLE_NAME = 'EMPLOYEES' AND COLUMN_NAME = UPPER(emp_column);
-- processing here
temp_var := table_name;
SELECT OBJECT_NAME INTO temp_var FROM USER_OBJECTS
WHERE OBJECT_NAME = UPPER(table_name) AND OBJECT_TYPE = 'TABLE';
-- processing here
EXCEPTION
WHEN NO_DATA_FOUND THEN -- catches all 'no data found' errors
DBMS_OUTPUT.PUT_LINE ('No Data found for SELECT on ' || temp_var);
END;
/
Exceptions can be declared only in the declarative part of a PL/SQL block, subprogram, or package. You declare an exception by introducing its name, followed by the EXCEPTION keyword. In Example 4-38, you declare an exception named past_due that is raised when the due_date is less than the today's date.
Exception and variable declarations are similar. But remember, an exception is an error condition, not a data item. Unlike variables, exceptions cannot appear in assignment statements or SQL statements. However, the same scope rules apply to variables and exceptions.
You cannot declare an exception twice in the same block. You can, however, declare the same exception in two different blocks.
Exceptions declared in a block are considered local to that block and global to all its subblocks. Because a block can reference only local or global exceptions, enclosing blocks cannot reference exceptions declared in a subblock.
If you redeclare a global exception in a subblock, the local declaration prevails. The subblock cannot reference the global exception, unless the exception is declared in a labeled block and you qualify its name with the block label, for example:
block_label.exception_name
Example 4-38 shows the scope rules.
Example 4-38 Determining the Scope of PL/SQL Exceptions
DECLARE past_due EXCEPTION; acct_num NUMBER; BEGIN DECLARE ---------- subblock begins past_due EXCEPTION; -- this declaration prevails acct_num NUMBER; due_date DATE := SYSDATE - 1; -- set on purpose to raise exception todays_date DATE := SYSDATE; BEGIN IF due_date < todays_date THEN RAISE past_due; -- this is not handled END IF; END; ------------- subblock ends EXCEPTION WHEN past_due THEN -- does not handle raised exception DBMS_OUTPUT.PUT_LINE('Handling PAST_DUE exception.'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Could not recognize PAST_DUE_EXCEPTION in this scope.'); END; /
The enclosing block does not handle the raised exception because the declaration of past_due in the subblock prevails. Although they share the same name, the two past_due exceptions are different, just as the two acct_num variables share the same name but are different variables. Thus, the RAISE statement and the WHEN clause refer to different exceptions. To have the enclosing block handle the raised exception, you must remove its declaration from the subblock or define an OTHERS handler.
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 execution from the spot where an exception occurred, enclose the code that might raise an exception inside another BEGIN-END block with its own exception handler. For example, 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 of a loop, you can continue executing the loop if some loop iterations raise exceptions.
You can still handle an exception for a statement, then continue with the next statement. Place the statement in its own subblock with its own exception handlers. If an error occurs in the subblock, a local handler can catch the exception. When the subblock ends, the enclosing block continues to execute at the point where the subblock ends, as shown in Example 4-39.
Example 4-39 Continuing After an Exception in PL/SQL
-- create a temporary table for this example
CREATE TABLE employees_temp AS
SELECT employee_id, salary, commission_pct FROM employees;
DECLARE
sal_calc NUMBER(8,2);
BEGIN
INSERT INTO employees_temp VALUES (303, 2500, 0);
BEGIN -- subblock begins
SELECT salary / commission_pct INTO sal_calc FROM employees_temp
WHERE employee_id = 303;
EXCEPTION
WHEN ZERO_DIVIDE THEN
sal_calc := 2500;
END; -- subblock ends
INSERT INTO employees_temp VALUES (304, sal_calc/100, .1);
EXCEPTION
WHEN ZERO_DIVIDE THEN
NULL;
END;
/
-- view the results
SELECT * FROM employees_temp WHERE employee_id = 303 OR employee_id = 304;
-- drop the temporary table
DROP TABLE employees_temp;
In this example, if the SELECT INTO statement raises a ZERO_DIVIDE exception, the local handler catches it and sets sal_calc to 2500. Execution of the handler is complete, so the subblock terminates, and execution continues with the INSERT statement.
