PL/SQL is the Oracle technology that enables SQL to act like a procedural language. By the end of today, you should
One way to introduce PL/SQL is to begin by describing standard Structured Query Language, or SQL. SQL is the language that enables relational database users to communicate with the database in a straightforward manner. You can use SQL commands to query the database and modify tables within the database. When you write an SQL statement, you are telling the database what you want to do, not how to do it. The query optimizer decides the most efficient way to execute your statement. If you send a series of SQL statements to the server in standard SQL, the server executes them one at a time in chronological order.
PL/SQL is Oracle's procedural language; it comprises the standard language of SQL and a wide array of commands that enable you to control the execution of SQL statements according to different conditions. PL/SQL can also handle runtime errors. Options such as loops and IF...THEN statements give PL/SQL the power of third-generation programming languages. PL/SQL allows you to write interactive, user-friendly programs that can pass values into variables. You can also use several predefined packages, one of which can display messages to the user.
Day 18 covers these key features of PL/SQL:
Most data types are obviously similar, but each implementation has unique storage and internal-processing requirements. When writing PL/SQL blocks, you will be declaring variables, which must be valid data types. The following subsections briefly describe the data types available in PL/SQL.
In PL/SQL Oracle provides subtypes of data types. For example, the data type NUMBER has a subtype called INTEGER. You can use subtypes in your PL/SQL program to make the data types compatible with data types in other programs, such as a COBOL program, particularly if you are embedding PL/SQL code in another program. Subtypes are simply alternative names for Oracle data types and therefore must follow the rules of their associated data type.
NOTE: As in most implementations of SQL, case sensitivity is not a factor in the syntax of a statement. PL/SQL allows either uppercase or lowercase with its commands.
Character string data types in PL/SQL, as you might expect, are data types generally defined as having alpha-numeric values. Examples of character strings are names, codes, descriptions, and serial numbers that include characters.
CHAR stores fixed-length character strings. The maximum length of CHAR is 32,767 bytes, although it is hard to imagine a set of fixed-length values in a table being so long.
CHAR ( max_length )
Subtype: CHARACTER
VARCHAR2 stores variable-length character strings. You would normally user VARCHAR2 instead of CHAR to store variable-length data, such as an individual's name. The maximum length of VARCHAR2 is also 32,767 bytes.
VARCHAR2 ( max_length )
Subtypes: VARCHAR, STRING
LONG also stores variable-length character strings, having a maximum length of 32,760 bytes. LONG is typically used to store lengthy text such as remarks, although VARCHAR2 may be used as well.
NUMBER stores any type of number in an Oracle database.
NUMBER ( max_length )
You may specify a NUMBER's data precision with the following syntax:
NUMBER (precision, scale)
Subtypes: DEC, DECIMAL, DOUBLE PRECISION, INTEGER, INT, NUMERIC, REAL, SMALLINT, FLOAT
PLS_INTEGER defines columns that may contained integers with a sign, such as negative numbers.
Binary data types store data that is in a binary format, such as graphics or photographs. These data types include RAW and LONGRAW.
DATE is the valid Oracle data type in which to store dates. When you define a column as a DATE, you do not specify a length, as the length of a DATE field is implied. The format of an Oracle date is, for example, 01-OCT-97.
BOOLEAN stores the following values: TRUE, FALSE, and NULL. Like DATE, BOOLEAN requires no parameters when defining it as a column's or variable's data type.
ROWID is a pseudocolumn that exists in every table in an Oracle database. The ROWID is stored in binary format and identifies each row in a table. Indexes use ROWIDs as pointers to data.
PL/SQL is a block-structured language, meaning that PL/SQL programs are divided and written in logical blocks of code. Within a PL/SQL block of code, processes such as data manipulation or queries can occur. The following parts of a PL/SQL block are discussed in this section:
NOTE: A block is a logical unit of PL/SQL code, containing at the least a PROCEDURE section and optionally the DECLARE and EXCEPTION sections.
Here is the basic structure of a PL/SQL block:
BEGIN -- optional, denotes beginning of block DECLARE -- optional, variable definitions BEGIN -- mandatory, denotes beginning of procedure section EXCEPTION -- optional, denotes beginning of exception section END -- mandatory, denotes ending of procedure section END -- optional, denotes ending of block
Notice that the only mandatory parts of a PL/SQL block are the second BEGIN and the first END, which make up the PROCEDURE section. Of course, you will have statements in between. If you use the first BEGIN, then you must use the second END, and vice versa.
What would a program be without comments? Programming languages provide commands that allow you to place comments within your code, and PL/SQL is no exception. The comments after each line in the preceding sample block structure describe each command. The accepted comments in PL/SQL are as follows:
-- This is a one-line comment.
/* This is a multiple-line comment.*/
NOTE: PL/SQL directly supports Data Manipulation Language (DML) commands and database queries. However, it does not support Data Dictionary Language (DDL) commands. You can generally use PL/SQL to manipulate the data within database structure, but not to manipulate those structures.
The DECLARE section of a block of PL/SQL code consists of variables, constants, cursor definitions, and special data types. As a PL/SQL programmer, you can declare all types of variables within your blocks of code. However, you must assign a data type, which must conform to Oracle's rules of that particular data type, to every variable that you define. Variables must also conform to Oracle's object naming standards.
Variables are values that are subject to change within a PL/SQL block. PL/SQL variables must be assigned a valid data type upon declaration and can be initialized if necessary. The following example defines a set of variables in the DECLARE portion of a block:
DECLARE owner char(10); tablename char(30); bytes number(10); today date;
The DECLARE portion of a block cannot be executed by itself. The DECLARE section starts with the DECLARE statement. Then individual variables are defined on separate lines. Notice that each variable declaration ends with a semicolon.
Variables may also be initialized in the DECLARE section. For example:
DECLARE customer char(30); fiscal_year number(2) := '97';
You can use the symbol := to initialize, or assign an initial value, to variables in the DECLARE section. You must initialize a variable that is defined as NOT NULL.
DECLARE customer char(30); fiscal_year number(2) NOT NULL := '97';
The NOT NULL clause in the definition of fiscal_year resembles a column definition in a CREATE TABLE statement.
Constants are defined the same way that variables are, but constant values are static; they do not change. In the previous example, fiscal_year is probably a constant.
NOTE: You must end each variable declaration with a semicolon.
A cursor is another type of variable in PL/SQL. Usually when you think of a variable, a single value comes to mind. A cursor is a variable that points to a row of data from the results of a query. In a multiple-row result set, you need a way to scroll through each record to analyze the data. A cursor is just that. When the PL/SQL block looks at the results of a query within the block, it uses a cursor to point to each returned row. Here is an example of a cursor being defined in a PL/SQL block:
DECLARE
cursor employee_cursor is
select * from employees;
A cursor is similar to a view. With the use of a loop in the PROCEDURE section, you can scroll a cursor. This technique is covered shortly.
%TYPE is a variable attribute that returns the value of a given column of a table. Instead of hard-coding the data type in your PL/SQL block, you can use %TYPE to maintain data type consistency within your blocks of code.
DECLARE
cursor employee_cursor is
select emp_id, emp_name from employees;
id_num employees.emp_id%TYPE;
name employees.emp_name%TYPE;
The variable id_num is declared to have the same data type as emp_id in the EMPLOYEES table. %TYPE declares the variable name to have the same data type as the column emp_name in the EMPLOYEES table.
Variables are not limited to single values. If you declare a variable that is associated with a defined cursor, you can use the %ROWTYPE attribute to declare the data type of that variable to be the same as each column in one entire row of data from the cursor. In Oracle's lexicon the %ROWTYPE attribute creates a record variable.
DECLARE
cursor employee_cursor is
select emp_id, emp_name from employees;
employee_record employee_cursor%ROWTYPE;
This example declares a variable called employee_record. The %ROWTYPE attribute defines this variable as having the same data type as an entire row of data in the employee_cursor. Variables declared using the %ROWTYPE attribute are also called aggregate variables.
The PL/SQL %ROWCOUNT attribute maintains a count of rows that the SQL statements in the particular block have accessed in a cursor.
DECLARE
cursor employee_cursor is
select emp_id, emp_name from employees;
records_processed := employee_cursor%ROWCOUNT;
In this example the variable records_processed represents the current number of rows that the PL/SQL block has accessed in the employee_cursor.
WARNING: Beware of naming conflicts with table names when declaring variables. For instance, if you declare a variable that has the same name as a table that you are trying to access with the PL/SQL code, the local variable will take precedence over the table name.
The PROCEDURE section is the only mandatory part of a PL/SQL block. This part of the block calls variables and uses cursors to manipulate data in the database. The PROCEDURE section is the main part of a block, containing conditional statements and SQL commands.
In a block, the BEGIN statement denotes the beginning of a procedure. Similarly, the END statement marks the end of a procedure. The following example shows the basic structure of the PROCEDURE section:
BEGIN
open a cursor;
condition1;
statement1;
condition2;
statement2;
condition3;
statement3;
.
.
.
close the cursor;
END
Now that you have learned how to define cursors in a PL/SQL block, you need to know how to access the defined cursors. This section explains the basic cursor control commands: DECLARE, OPEN, FETCH, and CLOSE.
Earlier today you learned how to define a cursor in the DECLARE section of a block. The DECLARE statement belongs in the list of cursor control commands.
Now that you have defined your cursor, how do you use it? You cannot use this book unless you open it. Likewise, you cannot use a cursor until you have opened it with the OPEN command. For example:
BEGIN open employee_cursor; statement1; statement2; . . . END
FETCH populates a variable with values from a cursor. Here are two examples using FETCH: One populates an aggregate variable, and the other populates individual variables.
DECLARE
cursor employee_cursor is
select emp_id, emp_name from employees;
employee_record employee_cursor%ROWTYPE;
BEGIN
open employee_cursor;
loop
fetch employee_cursor into employee_record;
end loop;
close employee_cursor;
END
The preceding example fetches the current row of the cursor into the aggregate variable employee_record. It uses a loop to scroll the cursor. Of course, the block is not actually accomplishing anything.
DECLARE
cursor employee_cursor is
select emp_id, emp_name from employees;
id_num employees.emp_id%TYPE;
name employees.emp_name%TYPE;
BEGIN
open employee_cursor;
loop
fetch employee_cursor into id_num, name;
end loop;
close employee_cursor;
END
This example fetches the current row of the cursor into the variables id_num and name, which was defined in the DECLARE section.
When you have finished using a cursor in a block, you should close the cursor, as you normally close a book when you have finished reading it. The command you use is CLOSE.
BEGIN open employee_cursor; statement1; statement2; . . . close employee_cursor; END
After a cursor is closed, the result set of the query no longer exists. You must reopen the cursor to access the associated set of data.
Now we are getting to the good stuff--the conditional statements that give you control over how your SQL statements are processed. The conditional statements in PL/SQL resemble those in most third-generation languages.
The IF...THEN statement is probably the most familiar conditional statement to most programmers. The IF...THEN statement dictates the performance of certain actions if certain conditions are met. The structure of an IF...THEN statement is as follows:
IF condition1 THEN statement1; END IF;
If you are checking for two conditions, you can write your statement as follows:
IF condition1 THEN statement1; ELSE statement2; END IF;
If you are checking for more than two conditions, you can write your statement as follows:
IF condition1 THEN statement1; ELSIF condition2 THEN statement2; ELSE statement3; END IF;
The final example states: If condition1 is met, then perform statement1; if condition2 is met, then perform statement2; otherwise, perform statement3. IF...THEN statements may also be nested within other statements and/or loops.
Loops in a PL/SQL block allow statements in the block to be processed continuously for as long as the specified condition exists. There are three types of loops.
LOOP is an infinite loop, most often used to scroll a cursor. To terminate this type of loop, you must specify when to exit. For example, in scrolling a cursor you would exit the loop after the last row in a cursor has been processed:
BEGIN open employee_cursor; LOOP FETCH employee_cursor into employee_record; EXIT WHEN employee_cursor%NOTFOUND; statement1; . . . END LOOP; close employee_cursor; END;
%NOTFOUND is a cursor attribute that identifies when no more data is found in the cursor. The preceding example exits the loop when no more data is found. If you omit this statement from the loop, then the loop will continue forever.
The WHILE-LOOP executes commands while a specified condition is TRUE. When the condition is no longer true, the loop returns control to the next statement.
DECLARE
cursor payment_cursor is
select cust_id, payment, total_due from payment_table;
cust_id payment_table.cust_id%TYPE;
payment payment_table.payment%TYPE;
total_due payment_table.total_due%TYPE;
BEGIN
open payment_cursor;
WHILE payment < total_due LOOP
FETCH payment_cursor into cust_id, payment, total_due;
EXIT WHEN payment_cursor%NOTFOUND;
insert into underpay_table
values (cust_id, 'STILL OWES');
END LOOP;
close payment_cursor;
END;
The preceding example uses the WHILE-LOOP to scroll the cursor and to execute the commands within the loop as long as the condition payment < total_due is met.
You can use the FOR-LOOP in the previous block to implicitly fetch the current row of the cursor into the defined variables.
DECLARE
cursor payment_cursor is
select cust_id, payment, total_due from payment_table;
cust_id payment_table.cust_id%TYPE;
payment payment_table.payment%TYPE;
total_due payment_table.total_due%TYPE;
BEGIN
open payment_cursor;
FOR pay_rec IN payment_cursor LOOP
IF pay_rec.payment < pay_rec.total_due THEN
insert into underpay_table
values (pay_rec.cust_id, 'STILL OWES');
END IF;
END LOOP;
close payment_cursor;
END;
This example uses the FOR-LOOP to scroll the cursor. The FOR-LOOP is performing an implicit FETCH, which is omitted this time. Also, notice that the %NOTFOUND attribute has been omitted. This attribute is implied with the FOR-LOOP; therefore, this and the previous example yield the same basic results.
The EXCEPTION section is an optional part of any PL/SQL block. If this section is omitted and errors are encountered, the block will be terminated. Some errors that are encountered may not justify the immediate termination of a block, so the EXCEPTION section can be used to handle specified errors or user-defined exceptions in an orderly manner. Exceptions can be user-defined, although many exceptions are predefined by Oracle.
Exceptions are raised in a block by using the command RAISE. Exceptions can be raised explicitly by the programmer, whereas internal database errors are automatically, or implicitly, raised by the database server.
BEGIN
DECLARE
exception_name EXCEPTION;
BEGIN
IF condition THEN
RAISE exception_name;
END IF;
EXCEPTION
WHEN exception_name THEN
statement;
END;
END;
This block shows the fundamentals of explicitly raising an exception. First exception_name is declared using the EXCEPTION statement. In the PROCEDURE section, the exception is raised using RAISE if a given condition is met. The RAISE then references the EXCEPTION section of the block, where the appropriate action is taken.
The preceding example handled an exception in the EXCEPTION section of the block. Errors are easily handled in PL/SQL, and by using exceptions, the PL/SQL block can continue to run with errors or terminate gracefully.
EXCEPTION
WHEN exception1 THEN
statement1;
WHEN exception2 THEN
statement2;
WHEN OTHERS THEN
statement3;
This example shows how the EXCEPTION section might look if you have more than one exception. This example expects two exceptions (exception1 and exception2) when running this block. WHEN OTHERS tells statement3 to execute if any other exceptions occur while the block is being processed. WHEN OTHERS gives you control over any errors that may occur within the block.
PL/SQL statements are normally created using a host editor and are executed like normal SQL script files. PL/SQL uses semicolons to terminate each statement in a block--from variable assignments to data manipulation commands. The forward slash (/)is mainly associated with SQL script files, but PL/SQL also uses the forward slash to terminate a block in a script file. The easiest way to start a PL/SQL block is by issuing the START command, abbreviated as STA or @.
Your PL/SQL script file might look like this:
/* This file is called proc1.sql */
BEGIN
DECLARE
...
BEGIN
...
statements;
...
EXCEPTION
...
END;
END;
/
You execute your PL/SQL script file as follows:
SQL> start proc1 or SQL> sta proc1 or SQL> @proc1
NOTE: PL/SQL script files can be executed using the START command or the character @. PL/SQL script files can also be called within other PL/SQL files, shell scripts, or other programs.
Particularly when handling exceptions, you may want to display output to keep users informed about what is taking place. You can display output to convey information, and you can display your own customized error messages, which will probably make more sense to the user than an error number. Perhaps you want the user to contact the database administrator if an error occurs during processing, rather than to see the exact message.
PL/SQL does not provide a direct method for displaying output as a part of its syntax, but it does allow you to call a package that serves this function from within the block. The package is called DBMS_OUTPUT.
EXCEPTION
WHEN zero_divide THEN
DBMS_OUTPUT.put_line('ERROR: DIVISOR IS ZERO. SEE YOUR DBA.');
ZERO_DIVIDE is an Oracle predefined exception. Most of the common errors that occur during program processing will be predefined as exceptions and are raised implicitly (which means that you don't have to raise the error in the PROCEDURE section of the block).
If this exception is encountered during block processing, the user will see:
SQL> @block1 ERROR: DIVISOR IS ZERO. SEE YOUR DBA. PL/SQL procedure successfully completed.
Doesn't that message look friendly than:
SQL> @block1 begin * ERROR at line 1: ORA-01476: divisor is equal to zero ORA-06512: at line 20
On Day 11, "Controlling Transactions," we discussed the transactional control commands COMMIT, ROLLBACK, and SAVEPOINT. These commands allow the programmer to control when transactions are actually written to the database, how often, and when they should be undone.
BEGIN
DECLARE
...
BEGIN
statements...
IF condition THEN
COMMIT;
ELSE
ROLLBACK;
END IF;
...
EXCEPTION
...
END;
END;
The good thing about PL/SQL is that you can automate the use of transactional control commands instead of constantly monitoring large transactions, which can be very tedious.
So far, you have been introduced to PL/SQL, have become familiar with the supported data types, and are familiar with the major features of a PL/SQL block. You know how to declare local variables, constants, and cursors. You have also seen how to embed SQL in the PROCEDURE section, manipulate cursors, and raise exceptions. When a cursor has been raised, you should have a basic understanding of how to handle it in the EXCEPTION section of the block. Now you are ready to work with some practical examples and create blocks from BEGIN to END. By the end of this section, you should fully understand how the parts of a PL/SQL block interact with each other.
We will be using two tables to create PL/SQL blocks. PAYMENT_TABLE identifies a customer, how much he or she has paid, and the total amount due. PAY_STATUS_TABLE does not yet contain any data. Data will be inserted into PAY_STATUS_TABLE according to certain conditions in the PAYMENT_TABLE.
SQL> select * 2 from payment_table;
CUSTOMER PAYMENT TOTAL_DUE -------- -------- --------- ABC 90.50 150.99 AAA 79.00 79.00 BBB 950.00 1000.00 CCC 27.50 27.50 DDD 350.00 500.95 EEE 67.89 67.89 FFF 555.55 455.55 GGG 122.36 122.36 HHH 26.75 0.00 9 rows selected.
SQL> describe pay_status_table
Name Null? Type ------------------------------ --------- ---- CUST_ID NOT NULL CHAR(3) STATUS NOT NULL VARCHAR2(15) AMT_OWED NUMBER(8,2) AMT_CREDIT NUMBER(8,2)
DESCRIBE is an Oracle SQL command that displays the structure of a table without having to query the data dictionary. DESCRIBE and other Oracle SQL*Plus commands are covered on Day 20, "SQL*Plus."
This is how the PL/SQL script (block1.sql) file looks:
set serveroutput on
BEGIN
DECLARE
AmtZero EXCEPTION;
cCustId payment_table.cust_id%TYPE;
fPayment payment_table.payment%TYPE;
fTotalDue payment_table.total_due%TYPE;
cursor payment_cursor is
select cust_id, payment, total_due
from payment_table;
fOverPaid number(8,2);
fUnderPaid number(8,2);
BEGIN
open payment_cursor;
loop
fetch payment_cursor into
cCustId, fPayment, fTotalDue;
exit when payment_cursor%NOTFOUND;
if ( fTotalDue = 0 ) then
raise AmtZero;
end if;
if ( fPayment > fTotalDue ) then
fOverPaid := fPayment - fTotalDue;
insert into pay_status_table (cust_id, status, amt_credit)
values (cCustId, 'Over Paid', fOverPaid);
elsif ( fPayment < fTotalDue ) then
fUnderPaid := fTotalDue - fPayment;
insert into pay_status_table (cust_id, status, amt_owed)
values (cCustId, 'Still Owes', fUnderPaid);
else
insert into pay_status_table
values (cCustId, 'Paid in Full', null, null);
end if;
end loop;
close payment_cursor;
EXCEPTION
when AmtZero then
DBMS_OUTPUT.put_line('ERROR: amount is Zero. See your supervisor.');
when OTHERS then
DBMS_OUTPUT.put_line('ERROR: unknown error. See the DBA');
END;
END;
/
The DECLARE section defines six local variables, as well as a cursor called payment_cursor. The PROCEDURE section starts with the second BEGIN statement in which the first step is to open the cursor and start a loop. The FETCH command passes the current values in the cursor into the variables that were defined in the DECLARE section. As long as the loop finds records in the cursor, the statement compares the amount paid by a customer to the total amount due. Overpayments and underpayments are calculated according to the amount paid, and we use those calculated amounts to insert values into the PAY_STATUS_TABLE. The loop terminates, and the cursor closes. The EXCEPTION section handles errors that may occur during processing.
Now start the PL/SQL script file and see what happens.
SQL> @block1
Input truncated to 1 characters ERROR: amount is Zero. See your supervisor. PL/SQL procedure successfully completed.
Now that you know that an incorrect amount appears in the total due column, you can fix the amount and run the script again.
SQL> update payment_table 2 set total_due = 26.75 3 where cust_id = 'HHH'; 1 row updated. SQL> commit; Commit complete. SQL> truncate table pay_status_table; Table truncated.
NOTE: This example truncates the PAY_STATUS_TABLE to clear the table's contents; the next run of the statement will repopulate the table. You may want to add the TRUNCATE TABLE statement to your PL/SQL block.
SQL> @block1 Input truncated to 1 characters PL/SQL procedure successfully completed.
Now you can select from the PAY_STATUS_TABLE and see the payment status of each customer.
SQL> select * 2 from pay_status_table 3 order by status;
CUSTOMER STATUS AMT_OWED AMT_CREDIT -------- -------------- --------- ----------- FFF Over Paid 100.00 AAA Paid in Full CCC Paid in Full EEE Paid in Full GGG Paid in Full HHH Paid in Full ABC Still Owes 60.49 DDD Still Owes 150.95 BBB Still Owes 50.00 9 rows selected.
A row was inserted into PAY_STATUS_TABLE for every row of data that is contained in the PAYMENT_TABLE. If the customer paid more than the amount due, then the difference was input into the amt_credit column. If the customer paid less than the amount owed, then an entry was made in the amt_owed column. If the customer paid in full, then no dollar amount was inserted in either of the two columns.
This example uses a table called PAY_TABLE:
SQL> desc pay_table
Name Null? Type ------------------------------ -------- ---- NAME NOT NULL VARCHAR2(20) PAY_TYPE NOT NULL VARCHAR2(8) PAY_RATE NOT NULL NUMBER(8,2) EFF_DATE NOT NULL DATE PREV_PAY NUMBER(8,2)
First take a look at the data:
SQL> select * 2 from pay_table 3 order by pay_type, pay_rate desc;
NAME PAY_TYPE PAY_RATE EFF_DATE PREV_PAY -------------------- -------- --------- --------- --------- SANDRA SAMUELS HOURLY 12.50 01-JAN-97 ROBERT BOBAY HOURLY 11.50 15-MAY-96 KEITH JONES HOURLY 10.00 31-OCT-96 SUSAN WILLIAMS HOURLY 9.75 01-MAY-97 CHRISSY ZOES SALARY 50000.00 01-JAN-97 CLODE EVANS SALARY 42150.00 01-MAR-97 JOHN SMITH SALARY 35000.00 15-JUN-96 KEVIN TROLLBERG SALARY 27500.00 15-JUN-96 8 rows selected.
Situation: Sales are up. Any individual who has not had a pay increase for six months (180 days) will receive a raise effective today. All eligible hourly employees will receive a 4 percent increase, and eligible salary employees will receive a 5 percent increase.
Today is:
SQL> select sysdate 2 from dual; SYSDATE -------- 20-MAY-97
Before examining the next PL/SQL block, we will perform a manual select from the PAY_TABLE that flags individuals who should receive a raise.
SQL> select name, pay_type, pay_rate, eff_date, 2 'YES' due 3 from pay_table 4 where eff_date < sysdate - 180 5 UNION ALL 6 select name, pay_type, pay_rate, eff_date, 7 'No' due 8 from pay_table 9 where eff_date >= sysdate - 180 10 order by 2, 3 desc;
NAME PAY_TYPE PAY_RATE EFF_DATE DUE -------------------- --------- -------- --------- --- SANDRA SAMUELS HOURLY 12.50 01-JAN-97 No ROBERT BOBAY HOURLY 11.50 15-MAY-96 YES KEITH JONES HOURLY 10.00 31-OCT-96 YES SUSAN WILLIAMS HOURLY 9.75 01-MAY-97 No CHRISSY ZOES SALARY 50000.00 01-JAN-97 No CLODE EVANS SALARY 42150.00 01-MAR-97 No JOHN SMITH SALARY 35000.00 15-JUN-96 YES KEVIN TROLLBERG SALARY 27500.00 15-JUN-96 YES 8 rows selected.
The DUE column identifies individuals who should be eligible for a raise. Here's the PL/SQL script:
set serveroutput on
BEGIN
DECLARE
UnknownPayType exception;
cursor pay_cursor is
select name, pay_type, pay_rate, eff_date,
sysdate, rowid
from pay_table;
IndRec pay_cursor%ROWTYPE;
cOldDate date;
fNewPay number(8,2);
BEGIN
open pay_cursor;
loop
fetch pay_cursor into IndRec;
exit when pay_cursor%NOTFOUND;
cOldDate := sysdate - 180;
if (IndRec.pay_type = 'SALARY') then
fNewPay := IndRec.pay_rate * 1.05;
elsif (IndRec.pay_type = 'HOURLY') then
fNewPay := IndRec.pay_rate * 1.04;
else
raise UnknownPayType;
end if;
if (IndRec.eff_date < cOldDate) then
update pay_table
set pay_rate = fNewPay,
prev_pay = IndRec.pay_rate,
eff_date = IndRec.sysdate
where rowid = IndRec.rowid;
commit;
end if;
end loop;
close pay_cursor;
EXCEPTION
when UnknownPayType then
dbms_output.put_line('=======================');
dbms_output.put_line('ERROR: Aborting program.');
dbms_output.put_line('Unknown Pay Type for Name');
when others then
dbms_output.put_line('ERROR During Processing. See the DBA.');
END;
END;
/
Are you sure that you want to give four employees a pay raise? (The final SELECT statement has four Yes values in the DUE column.) Why not...let's give all four employees a raise. You can apply the appropriate pay increases by executing the PL/SQL script file, named block2.sql:
SQL> @block2 Input truncated to 1 characters PL/SQL procedure successfully completed.
You can do a quick select to verify that the changes have been made to the pay_rate of the appropriate individuals:
SQL> select * 2 from pay_table 3 order by pay_type, pay_rate desc;
NAME PAY_TYPE PAY_RATE EFF_DATE PREV_PAY -------------------- --------- -------- -------- ----------- SANDRA SAMUELS HOURLY 12.50 01-JAN-97 ROBERT BOBAY HOURLY 11.96 20-MAY-97 11.5 KEITH JONES HOURLY 10.40 20-MAY-97 10 SUSAN WILLIAMS HOURLY 9.75 01-MAY-97 CHRISSY ZOES SALARY 50000.00 01-JAN-97 CLODE EVANS SALARY 42150.00 01-MAR-97 JOHN SMITH SALARY 36750.00 20-MAY-97 35000 KEVIN TROLLBERG SALARY 28875.00 20-MAY-97 27500 8 rows selected.
Four employees received a pay increase. If you compare this output to the output of the original SELECT statement, you can see the changes. The current pay rate was updated to reflect the pay increase, the original pay rate was inserted into the previous pay column, and the effective date was updated to today's date. No action was taken on those individuals who did not qualify for a pay increase.
Wait--you didn't get a chance to see how the defined exception works. You can test the EXCEPTION section by inserting an invalid PAY_TYPE into PAY_TABLE.
SQL> insert into pay_table values
2 ('JEFF JENNINGS','WEEKLY',71.50,'01-JAN-97',NULL);
1 row created.
The moment of truth:
SQL> @block2 Input truncated to 1 characters ======================= ERROR: Aborting program. Unknown Pay Type for: JEFF JENNINGS PL/SQL procedure successfully completed.
An error message told you that JEFF JENNINGS had a Pay Type with a value other than SALARY or HOURLY. That is, the exception was handled with an error message.
Using PL/SQL, you can create stored objects to eliminate having to constantly enter monotonous code. Procedures are simply blocks of code that perform some sort of specific function. Related procedures can be combined and stored together in an object called a package. A trigger is a database object that is used with other transactions. You might have a trigger on a table called ORDERS that will insert data into a HISTORY table each time the ORDERS table receives data. The basic syntax of these objects follows.
PROCEDURE procedure_name IS variable1 datatype; ... BEGIN statement1; ... EXCEPTION when ... END procedure_name;
CREATE PACKAGE package_name AS
PROCEDURE procedure1 (global_variable1 datatype, ...);
PROCEDURE procedure2 (global_variable1 datatype, ...);
END package_name;
CREATE PACKAGE BODY package_name AS
PROCEDURE procedure1 (global_variable1 datatype, ...) IS
BEGIN
statement1;
...
END procedure1;
PROCEDURE procedure2 (global_variable1 datatype, ...) IS
BEGIN
statement1;
...
END procedure2;
END package_name;
CREATE TRIGGER trigger_name
AFTER UPDATE OF column ON table_name
FOR EACH ROW
BEGIN
statement1;
...
END;
The following example uses a trigger to insert a row of data into a transaction table when updating PAY_TABLE. The TRANSACTION table looks like this:
SQL> describe trans_table
Name Null? Type ------------------------------ -------- ---- ACTION VARCHAR2(10) NAME VARCHAR2(20) PREV_PAY NUMBER(8,2) CURR_PAY NUMBER(8,2) EFF_DATE DATE
Here's a sample row of data:
SQL> select * 2 from pay_table 3 where name = 'JEFF JENNINGS'; NAME PAY_TYPE PAY_RATE EFF_DATE PREV_PAY -------------------- -------- --------- -------- ---------- JEFF JENNINGS WEEKLY 71.50 01-JAN-97
Now, create a trigger:
SQL> CREATE TRIGGER pay_trigger
2 AFTER update on PAY_TABLE
3 FOR EACH ROW
4 BEGIN
5 insert into trans_table values
6 ('PAY CHANGE', :new.name, :old.pay_rate,
7 :new.pay_rate, :new.eff_date);
8 END;
9 /
Trigger created.
The last step is to perform an update on PAY_TABLE, which should cause the trigger to be executed.
SQL> update pay_table 2 set pay_rate = 15.50, 3 eff_date = sysdate 4 where name = 'JEFF JENNINGS'; 1 row updated. SQL> select * 2 from pay_table 3 where name = 'JEFF JENNINGS'; NAME PAY_TYPE PAY_RATE EFF_DATE PREV_PAY -------------------- -------- --------- -------- ---------- JEFF JENNINGS WEEKLY 15.50 20-MAY-97 SQL> select * 2 from trans_table; ACTION NAME PREV_PAY CURR_PAY EFF_DATE ---------- -------------------- ---------- ---------- --------- PAY CHANGE JEFF JENNINGS 71.5 15.5 20-MAY-97
PREV_PAY is null in PAY_TABLE but PREV_PAY appears in TRANS_TABLE. This approach isn't as confusing as it sounds. PAY_TABLE does not need an entry for PREV_PAY because the PAY_RATE of 71.50 per hour was obviously an erroneous amount. Rather, we inserted the value for PREV_PAY in TRANS_TABLE because the update was a transaction, and the purpose of TRANS_PAY is to keep a record of all transactions against PAY_TABLE.
NOTE: If you are familiar with network technologies, you might notice similarities between PL/SQL and Java stored procedures. However, some differences should be noted. PL/SQL is an enhancement of standard SQL, implementing the commands of a procedural language. Java, which is much more advanced than PL/SQL, allows programmers to write more complex programs than are possible with PL/SQL. PL/SQL is based on the database-intensive functionality of SQL; Java is more appropriate for CPU-intensive programs. Most procedural languages, such as PL/SQL, are developed specifically for the appropriate platform. As procedural language technology evolves, a higher level of standardization will be enforced across platforms.
PL/SQL extends the functionality of standard SQL. The basic components of PL/SQL perform the same types of functions as a third-generation language. The use of local variables supports dynamic code; that is, values within a block may change from time to time according to user input, specified conditions, or the contents of a cursor. PL/SQL uses standard procedural language program control statements. IF...THEN statements and loops enable you to search for specific conditions; you can also use loops to scroll through the contents of a defined cursor.
Errors that occur during the processing of any program are a major concern. PL/SQL enables you to use exceptions to control the behavior of a program that encounters either syntax errors or logical errors. Many exceptions are predefined, such as a divide-by-zero error. Errors can be raised any time during processing according to specified conditions and may be handled any way the PL/SQL programmer desires.
Day 18 also introduces some practical uses of PL/SQL. Database objects such as triggers, stored procedures, and packages can automate many job functions. Today's examples apply some of the concepts that were covered on previous days.
A Most definitely not. Today's introduction just scratched the surface of one of the greatest concepts of SQL. We have simply tried to highlight some of the major features to give you a basic knowledge of PL/SQL.
Q Can I get by without using PL/SQL?
A Yes, you can get by, but to achieve the results that you would get with PL/SQL, you may have to spend much more time coding in a third-generation language. If you do not have Oracle, check your implementation documentation for procedural features like those of PL/SQL.
The Workshop provides quiz questions to help solidify your understanding of the material covered, as well as exercises to provide you with experience in using what you have learned. Try to answer the quiz and exercise questions before checking the answers in Appendix F, "Answers to Quizzes and Exercises."
2. Can related procedures be stored together?
3. True or False: Data Manipulation Language can be used in a PL/SQL statement.
4. True or False: Data Definition Language can be used in a PL/SQL statement.
5. Is text output directly a part of the PL/SQL syntax?
6. List the three major parts of a PL/SQL statement.
7. List the commands that are associated with cursor control.
2. Define a cursor whose content is all the data in the CUSTOMER_TABLE where the CITY is INDIANAPOLIS.
3. Define an exception called UnknownCode.
4. Write a statement that will set the AMT in the AMOUNT_TABLE to 10 if CODE is A, set the AMT to 20 if CODE is B, and raise an exception called UnknownCode if CODE is neither A nor B. The table has one row.
© Copyright, Macmillan Computer Publishing. All rights reserved.