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:
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.
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 ( 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.
Here is the basic structure of a PL/SQL block:
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.
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 blockNotice 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.
-- 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.
DECLARE owner char(10); tablename char(30); bytes number(10); today date;
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';
NOTE: You must end each variable declaration with a semicolon.
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.
DECLARE cursor employee_cursor is select emp_id, emp_name from employees; id_num employees.emp_id%TYPE; name employees.emp_name%TYPE;
DECLARE cursor employee_cursor is select emp_id, emp_name from employees; employee_record employee_cursor%ROWTYPE;
DECLARE cursor employee_cursor is select emp_id, emp_name from employees; records_processed := employee_cursor%ROWCOUNT;
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.
BEGIN open a cursor; condition1; statement1; condition2; statement2; condition3; statement3; . . . close the cursor; END
BEGIN open employee_cursor; statement1; statement2; . . . END
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
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
BEGIN open employee_cursor; statement1; statement2; . . . close employee_cursor; END
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;
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;
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;
BEGIN DECLARE exception_name EXCEPTION; BEGIN IF condition THEN RAISE exception_name; END IF; EXCEPTION WHEN exception_name THEN statement; END; END;
EXCEPTION WHEN exception1 THEN statement1; WHEN exception2 THEN statement2; WHEN OTHERS THEN statement3;
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.
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.');
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
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.
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)
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; /
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.
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-97Before 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.
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.
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 DATEHere'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-97Now, 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
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.
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.
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.