ORACLE
PL/SQL (CONTINUED)
TOPICS:
·
Input/Output
Functions.
·
Stored
Procedures and Functions.
·
Packages
·
Triggers
(Insert, Delete, Update).
·
Commonly
used SQL functions including DECODE.
Oracle provides three packages that provide I/O functions. They are used mostly for debugging purposes, they are:
-
DBMS_PIPE:
Communications between processes using pipes.
-
DBMS_OUTPUT:
Write to and read from a buffer, which is displayed on END
-
UTL_FILE:
Read from and write to files.
Note: Within Procedure Builder in Developer 2000, TEXT_IO does all
three.
Using
DBMS_OUTPUT package:
The
most commonly used of the three packages is the DBMS_OUTPUT package, so we will
look at some of it’s functionality in more depth.
-
ENABLE:
Enable the output buffer and set its size.
-
DISABLE:
Disable message output.
-
PUT_LINE:
Place a line in the buffer, includes return.
-
PUT: Put
a partial line in the buffer.
-
NEWLINE:
Terminates a line created with PUT.
-
SET
SERVEROUTPUT ON: Tells SQL*Plus
that you want to write values.
Example (DBMS_OUTPUT):
ACCEPT
p_cust_nr PROMPT 'Enter Customer #:';
DECLARE
V_cust_nr
customer.customer_number%TYPE := &p_cust_nr;
V_bal
customer.balance%TYPE;
E_zero_balance EXCEPTION;
BEGIN
-- Start of PL/SQL Main Block.
SELECT balance INTO v_bal FROM customer
WHERE
customer_number = v_cust_nr;
If v_total is equal to zeror then
Raise an Error
Message.
*/
DBMS_OUTPUT.PUT_LINE ('Negative Balance');
RAISE e_zero_balance;
END IF;
COMMIT WORK;
EXCEPTION
WHEN e_zero_balance THEN
DBMS_OUTPUT.PUT_LINE ('Zero Balance Error');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE (SQLERRM);
ROLLBACK WORK;
END;
-- End Main Block.
/
Note
the indentation above, failure to indent for readability will result in one full
letter grade deduction and your work being returned to you to fix.
ERRORS:
1. Syntax Errors: Either Compile Time or Run Time
2. Logic Errors: Everything compiles and runs fine, but the program
doesn't behave the way you expect it to.
Example of a Logic Error
iValue
:= 10;
WHILE
iValue < 0 LOOP -- This is the loop and it is fine.
iValue = iValue +1;
END LOOP;
WHILE
iValue >= 0 LOOP -- This is what you really meant.
iValue = iValue -1;
END LOOP;
COMMENTS:
Use -- for single line comments in SQL*Plus.
-- rtimlin(3/6/01): Next four lines added for ...
Use /* … */ for multi-line comments in SQL*Plus.
/** get_balance(p_cust_nr): retrieve
customer balance.
@author: rtimlin
@original: 06-Mar-2001
@updated:
@arguments: p_cust_nr Customer number from the Customer
Table:
@version: 1.0
**** Modification History ****
Date
User Description
===================================================
3/6/01 rtimlin
Original.
3/8/01 flast
Added Exception handler.
*/
All of your PL/SQL functions and
procedures must have the above as a header section
STORED
PROCEDURES, FUNCTIONS:
PL/SQL code that is stored directly in the database instead of in a file.
Stored procedures/functions
have several advantages over sql files. First,
the processing of commands may be done in the DBMS and therefore on the server.
In client-server applications, processing is often shared between the
Client and Server, which leads to Network traffic.
Secondly stored procedures/ functions are available to all leading to
consistency of business rules. They
may also reduce development time by reducing the amount of code that must be
written into applications.
STORED
FUNCTIONS (CREATE FUNCTION):
·
Only
accept input parameters (IN).
·
Always
return one value.
·
Invoked
within expressions just as any SQL function might be.
like (sum, average, count, etc.)
CREATE
[OR REPLACE] FUNCTION function_name (argument1 [mode1] datatype1,
Argument2 [mode2] datatype2, …)
RETURN
datatype
IS|AS
Variable declarations;
BEGIN
Statements;
[EXCEPTION]
END;
Note if you need to create the customer table copy/paste the following into SQL*Plus
CREATE TABLE customer (customer_number number(4) primary
key, lname
char(20), fname char(20), address char(30), city char(15), state char(2), zip_code char(10),
credit_limit number(9,2), balance number(9,2), slsrep_number number(4));
INSERT INTO customer (customer_number, lname, fname, address, city, state,
zip_code, credit_limit, balance, slsrep_number)
VALUES (1, 'Adams', 'John', '123 Main', 'Boston', 'MA', '12345',
1000, 500, 6);
INSERT INTO customer (customer_number, lname, fname, address, city, state,
zip_code, credit_limit, balance, slsrep_number)
VALUES (2, 'Bentson', 'Lloyd', '12 Main', 'Austin', 'TX', '54321',
1500, 750, 6);
Example:
/* get_balance(p_customer_number): retrieve
customer balance.
@author: rtimlin
@original: 06-Mar-2001
@updated:
@arguments: p_cust_nr Customer number from the Customer
Table:
@version: 1.0
**** Modification History ****
Date
User Description
===================================================
3/6/01 rtimlin
Original.
3/8/01 flast
Added Exception handler.
*/
CREATE
OR REPLACE FUNCTION get_balance (p_customer_number
IN
customer.customer_number%TYPE)
RETURN
NUMBER
IS
V_balance customer.balance%TYPE;
BEGIN
SELECT balance INTO v_balance FROM customer
WHERE
customer_number = p_customer_number;
RETURN v_balance;
END
get_balance;
/
Note
the indentation above, failure to indent for readability will result in one full
letter grade deduction and your work being returned to you to fix.
--
Create the above in a file, such as c:\temp\get_balance.sql
SQL> @c:\temp\get_balance.sql -- This compiles into the database,
it doesn't run it.
-- If you get a message about "compiled with errors" at SQL> type
Show Errors.
SQL> show errors
SQL>
VARIABLE g_balance NUMBER;
SQL>
EXECUTE :g_balance := get_balance (124);
SQL> PRINT :g_balance;
SQL>
SELECT get_balance(124) FROM dual;
Example with an Exception Handler:
/** get_balance(p_customer_number): retrieve
customer balance.
@author: rtimlin
@original: 06-Mar-2001
@updated:
@arguments: p_cust_nr Customer number from the Customer
Table:
@version: 1.0
**** Modification History ****
Date
User Description
===================================================
3/6/01 rtimlin
Original.
3/8/01 flast
Added Exception handler.
*/
CREATE
OR REPLACE FUNCTION get_balance (p_customer_number
IN
customer.customer_number%TYPE)
RETURN
NUMBER
V_balance customer.balance%TYPE;
BEGIN
SELECT balance INTO v_balance FROM customer
WHERE
customer_number = p_customer_number;
RETURN v_balance;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN -1;
END get_balance;
/
All your PL/SQL functions, procedures and triggers must have an exception handler or you will receive one full letter grade deduction and your work will be returned to be fixed.
Note
the indentation above, failure to indent for readability will result in one full
letter grade deduction and your work being returned to you to fix.
All your PL/SQL functions, procedures and triggers must have comments similar to the above or you will receive one full letter grade deduction and your work will be returned to be fixed.
Try it with an errorlog table:
CREATE TABLE errorlog (error_dt DATE, user_id VARCHAR2(50), error_msg VARCHAR2(2000), error_code INTEGER);
/** get_balance(p_customer_number): retrieve
customer balance.
@author: rtimlin
@original: 06-Mar-2001
@updated:
@arguments: p_cust_nr Customer number from the Customer
Table:
@version: 1.0
**** Modification History ****
Date
User Description
===================================================
3/6/01 rtimlin
Original.
3/8/01 flast
Added Exception handler.
*/
CREATE
OR REPLACE FUNCTION get_balance (p_customer_number
IN
customer.customer_number%TYPE)
RETURN
NUMBER
v_error_code INTEGER;
v_error_msg
VARCHAR2(2000);
V_balance customer.balance%TYPE;
BEGIN
SELECT balance INTO v_balance FROM customer
WHERE
customer_number = p_customer_number;
RETURN v_balance;
EXCEPTION
WHEN OTHERS THEN
v_error_code := SQLCODE;
v_error_msg := SQLERRM;
INSERT INTO errorlog (error_dt, user_id, error_msg, error_code)
VALUES (SYSDATE, USER, v_error_msg, v_error_code);
RETURN 0;
END get_balance;
/
All your PL/SQL functions, procedures and triggers must have an exception handler or you will receive one full letter grade deduction and your work will be returned to be fixed.
Note
the indentation above, failure to indent for readability will result in one full
letter grade deduction and your work being returned to you to fix.
All your PL/SQL functions, procedures and triggers must have comments similar to the above or you will receive one full letter grade deduction and your work will be returned to be fixed.
SQL>
VARIABLE g_balance NUMBER;
SQL>
EXECUTE :g_balance := get_balance (124);
SQL> PRINT :g_balance;
SQL>
SELECT * FROM errorlog;
ERROR_DT USER_ID
--------- --------------------------
ERROR_MSG
------------------------------------
ERROR_CODE
----------
24-FEB-01 SCOTT
ORA-01403: no data found
100
STORED
PROCEDURES (CREATE OR REPLACE PROCEDURE):
·
Invoked
in place of anonymous PL/SQL blocks or SQL statements
·
Do NOT
use DECLARE keyword, parameters and variable declarations follow the IS key
word.
·
Invoked
in SQL*Plus using the EXECUTE command.
·
Can have
IN, OUT, IN OUT modes of parameters.
·
The IN
qualifier is used for arguments for which values must be specified when calling
the procedure. In the prior CREATE
FUNCTION example, v_customer_number is an IN parameter and therefor must be
specified.
·
The OUT
qualifier signifies that the procedure passes a value back to caller through
this argument.
·
The IN
OUT qualifier signifies that this argument is both an IN and an OUT parameter.
Syntax:
CREATE
[OR REPLACE] PROCEDURE procedure (argument1 [IN|OUT|IN OUT] datatype1, argument2
[IN|OUT|IN OUT] datatype2, … )
IS|AS
Variable declarations;
BEGIN
Statements;
[EXCEPTION]
END;
CREATE TABLE errorlog (error_dt DATE, user_id VARCHAR2(50), error_msg VARCHAR2(2000), error_code INTEGER);
Example:
/** get_balance_proc(p_customer_number):
retrieve customer balance.
@author: rtimlin
@original: 06-Mar-2001
@updated:
@arguments: p_cust_nr Customer number from the Customer
Table:
@version: 1.0
**** Modification History ****
Date
User Description
===================================================
3/6/01 rtimlin
Original.
3/8/01 flast
Added Exception handler.
*/
CREATE OR REPLACE PROCEDURE get_balance_proc (
p_customer_number IN customer.customer_number%TYPE,
p_balance OUT customer.balance%TYPE)
IS
v_error_code INTEGER;
v_error_msg VARCHAR2(2000);
BEGIN
SELECT balance INTO p_balance FROM customer
WHERE customer_number =
p_customer_number;
RETURN;
EXCEPTION
WHEN OTHERS THEN
v_error_code := SQLCODE;
v_error_msg := SQLERRM;
INSERT INTO errorlog (error_dt, user_id, error_msg, error_code)
VALUES (SYSDATE, USER, v_error_msg, v_error_code);
RETURN;
END get_balance_proc;
/
SQL>
VARIABLE g_balance NUMBER;
SQL> EXECUTE get_balance_proc (4, :g_balance);
SQL> PRINT :g_balance;
--
Procedures and Functions can call each other:
CREATE OR REPLACE PROCEDURE get_balance_proc (
p_customer_number IN
customer.customer_number%TYPE,
p_balance OUT customer.balance%TYPE)
IS
v_error_code INTEGER;
v_error_msg VARCHAR2(2000);
BEGIN
-- Call the function I created
earlier.
p_balance :=
get_balance(p_customer_number);
RETURN;
EXCEPTION
WHEN OTHERS THEN
v_error_code := SQLCODE;
v_error_msg := SQLERRM;
INSERT INTO errorlog (error_dt, user_id, error_msg, error_code)
VALUES (SYSDATE, USER, v_error_msg, v_error_code);
RETURN;
END get_balance_proc;
/
CREATE OR REPLACE FUNCTION get_balance (p_customer_number IN
customer.customer_number%TYPE)
RETURN NUMBER IS
V_balance customer.balance%TYPE;
BEGIN
get_balance_proc(p_customer_number,
v_balance);
RETURN v_balance;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN -1;
END get_balance;
/
All your PL/SQL functions, procedures and triggers must have an exception handler or you will receive one full letter grade deduction and your work will be returned to be fixed.
Note
the indentation above, failure to indent for readability will result in one full
letter grade deduction and your work being returned to you to fix.
All your PL/SQL functions, procedures and triggers must have comments similar to the above or you will receive one full letter grade deduction and your work will be returned to be fixed.
SQL>
VARIABLE get_balance NUMBER;
SQL> EXECUTE get_balance_proc (124, :get_balance);
SQL> PRINT :get_balance;
NOTE: If you get something like: Warning: Procedure created with compilation errors.
Enter the following command at the SQL> prompt
SQL> show errors
/** get_balance_proc(p_customer_number):
retrieve customer balance.
@author: rtimlin
@original: 06-Mar-2001
@updated:
@arguments: p_cust_nr Customer number from the Customer
Table:
@version: 1.0
**** Modification History ****
Date
User Description
===================================================
3/6/01 rtimlin
Original.
3/8/01 flast
Added Exception handler.
*/
CREATE OR REPLACE PROCEDURE get_balance_proc (
p_customer_number IN customer.customer_number%TYPE,
p_balance OUT customer.balance%TYPE,
p_lname OUT customer.lname%TYPE,
p_fname OUT customer.fname%TYPE)
IS
v_error_code INTEGER;
v_error_msg VARCHAR2(2000);
BEGIN
SELECT balance, lname, fname
INTO p_balance, p_lname, p_fname FROM customer
WHERE customer_number =
p_customer_number;
RETURN;
EXCEPTION
WHEN OTHERS THEN
v_error_code := SQLCODE;
v_error_msg := SQLERRM;
INSERT INTO errorlog (error_dt, user_id, error_msg, error_code)
VALUES (SYSDATE, USER, v_error_msg, v_error_code);
RETURN;
END get_balance_proc;
/
All your PL/SQL
functions, procedures and triggers must have an exception handler or you will
receive one full letter grade deduction and your work will be returned to be
fixed.
Note
the indentation above, failure to indent for readability will result in one full
letter grade deduction and your work being returned to you to fix.
All your PL/SQL functions, procedures and triggers must have comments similar to the above or you will receive one full letter grade deduction and your work will be returned to be fixed.
SQL> VARIABLE g_lname VARCHAR2(50)
SQL> VARIABLE g_fname VARCHAR2(50)
SQL> EXECUTE get_balance_proc (2, :g_balance, :g_lname, :g_fname);
SQL> PRINT :g_balance
G_BALANCE
-----------
500
SQL> print :g_lname
G_LNAME
---------------------------------------------------------
Timlin
SQL> print :g_fname
G_FNAME
---------------------------------------------------------
Robert
STORED PROCEDURE vs. STORED FUNCTION.
1. Stored Function input Arguments Only, Stored Procedure input and output Arguments.
2. Stored Function one return value via the return statement,
- Stored Procedure multiple return values via output argument
3. Stored Functions can usually be used in SQL Statements, Stored Procedures
cannot.
SET ECHO ON (OFF);
Indicates that want your statement repeated when
Oracle interprets and processes your statement. Very useful in SQL*Scripts.
SPOOL <path>
<filename>;
SPOOL OFF;
Indicates that you
want to store to a text file a log
of everything that has taken place in the SQL*Plus display:
Your commands, Oracle’s
messages indicating its actions concerning your commands.
SET HEAD ON (OFF).
Indicates whether you want SQL*Plus to display
headings in output returned from a query.
SQL> SET HEAD OFF
SQL> SPOOL c:\temp\get_bal2.sql
SQL> SELECT text FROM user_source WHERE name =
'GET_BALANCE';
/* get_balance(p_customer_number):
retrieve customer balance.
@author: rtimlin
@original: 06-Mar-2001
@updated:
@arguments: p_cust_nr Customer number from the Customer
Table:
@version: 1.0
**** Modification History ****
Date
User Description
===================================================
3/6/01 rtimlin
Original.
3/8/01 flast
Added Exception handler.
*/
FUNCTION get_balance (v_customer_number IN
customer.customer_number%TYPE)
RETURN NUMBER IS
v_error_code INTEGER;
v_error_msg VARCHAR2(2000);
v_balance customer.balance%TYPE;
BEGIN
SELECT balance INTO v_balance FROM customer
WHERE customer_number = v_customer_number;
RETURN v_balance;
EXCEPTION
WHEN OTHERS THEN
v_error_code := SQLCODE;
v_error_msg := SQLERRM;
INSERT INTO errorlog (error_dt, user_id, error_msg, error_code)
VALUES (SYSDATE, USER, v_error_msg, v_error_code);
RETURN 0;
END get_balance;
All your
PL/SQL functions, procedures and triggers must have an exception handler or you
will receive one full letter grade deduction and your work will be returned to
be fixed.
Note
the indentation above, failure to indent for readability will result in one full
letter grade deduction and your work being returned to you to fix.
All your PL/SQL functions, procedures and triggers must have comments similar to the above or you will receive one full letter grade deduction and your work will be returned to be fixed.
24 rows selected.
SQL> spool off
Commonly
used SQL functions including DECODE.
UTL_FILE:
CREATE OR REPLACE PROCEDURE file_cust
AS
lv_file_id_num UTL_FILE.FILE_TYPE;
test UTL_FILE.FILE_TYPE;
v_error_code INTEGER;
v_error_msg VARCHAR2(2000);
BEGIN
test := UTL_FILE.FOPEN('/tmp',
'rtimlin_test.txt', 'W');
UTL_FILE.PUT_LINE (test, 'Test File Openned');
lv_file_id_num := UTL_FILE.FOPEN('/tmp',
'rtimlin_customer.out', 'A');
UTL_FILE.PUT_LINE (test, 'Customer File Openned');
FOR r_cust IN (SELECT * FROM customer) LOOP
UTL_FILE.PUT_LINE (lv_file_id_num, r_cust.customer_number || ',' ||
r_cust.lname || ',' || r_cust.fname || ',' || r_cust.street || ',' ||
r_cust.city || ',' || r_cust.state || ',' || r_cust.zip_code);
END LOOP;
UTL_FILE.PUT_LINE (test, 'Customer Record Written');
UTL_FILE.FCLOSE (lv_file_id_num);
UTL_FILE.PUT_LINE (test, 'Customer File Closed');
UTL_FILE.FCLOSE (test);
END;
/
customer.out:
5,Doe ,John ,,S.F. ,CA ,94118
6,Test ,Again ,,,,
Other:
PAUSE;
Indicates that you want Oracle to stop executing
statements within a Script until the user has hit the enter key.
DESCRIBE <table name>;
Indicates that you want Oracle to display a
description of a existing Oracle table.
START or RUN or @<path>
<file name>.
Indicates that you want SQL*Plus to run a script
file.