ORACLE PL/SQL
(CONTINUED)
TOPICS:
Stored Procedures and Functions.
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 ('Zero
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.