Assignment 1: PL/SQL stored procedure
Objective: To comprehend how to write, compile and execute a PL/SQL stored
procedure and display the required result to the SQL prompt.
Problem Description: The creation of PL/SQL stored procedure has been explained to
you in the classroom. This step by step guide will let you understand the procedure of
creation of a PL/SQL stored procedure.
Estimated time: 20 Minutes
Note: Before executing this block please set the SERVEROUTPUT option.
Type 'SET SERVEROUTPUT ON' at the SQL Prompt in SQL* Plus
Step 1: Consider the following employee table in your Oracle schema:
Create table employee (
empNo number,
empName varchar2(20),
empSalary number(8,2),
grade char(1)
);
Step 2: Write the following code in a notepad to create the stored procedure sp_Get_Grade
and save the file with name “getgrade.sql”.Note that the file extension should be .sql.
CREATE OR REPLACE PROCEDURE sp_Get_Grade( p_eNo IN EMPLOYEE.EmpNo%TYPE
:=0 , p_eGrade OUT EMPLOYEE.Grade%TYPE) IS
BEGIN
SELECT grade into p_eGrade FROM employee WHERE EmpNo = p_eNo;
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_eGrade := 'Z';
WHEN OTHERS THEN
p_eGrade :='Z';
dbms_output.put_line('*** Error occurred ***');
dbms_output.put_line('SQLCODE: '||to_char(SQLCODE));
dbms_output.put_line('SQLERRM: '||SQLERRM);
END;
/
Step 4: Stored procedure is compiled and ready to use. Now create an anonymous PL/SQL
block as given below to call the stored procedure sp_Get_Grade.
DECLARE
v_employeeNo EMPLOYEE.EmpNo%TYPE;
v_employeeGrade EMPLOYEE.Grade%TYPE;
BEGIN
v_ employeeNo := 1;
sp_Get_Grade(v_employeeNo, v_employeeGrade);
IF v_employeeGrade = 'Z' THEN
dbms_output.put_line('Employee No Not Found');
ELSE
dbms_output.put_line('Employee Grade is '||v_employeeGrade);
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('*** Error occurred ***');
dbms_output.put_line('SQLCODE: '||to_char(SQLCODE));
dbms_output.put_line('SQLERRM: '||SQLERRM);
END;
/
You will get the following output (assuming the grade of employee no 1 is ‘A’):
Employee Grade is A
PL/SQL procedure successfully completed.
Assignment 2: Creating PL/SQL stored procedures
Objective: To learn how to write PL/SQL stored procedures and calling those objects
from anonymous blocks or from SQL prompt.
Problem Description: Create following tables and write PL/SQL codes for the
following requirements.
Note: Use provided sql file to create the tables and insert the records.
a) Login (This table stores the authentication information)
1. Create a sequence named “seq_transationid” that starts with 7000005 and increments
by 1.
Note: Sequence creation syntax:
CREATE SEQUENCE seqname START WITH initialvalue INCREMENT BY
incrementvalue;
Example: CREATE SEQUENCE seq1 START WITH 1 INCREMENT BY 1;
The next value can be generated using :
SELECT seq1.NEXTVAL from dual;
The current value can be displayed using:
SELECT seq1.CURRVAL from dual;
To assign to a PL/SQL variable inside a PL/SQL block:
SELECT seq1.NEXTVAL into v_productno from dual;
The sequence can be used in INSERT statement as follows:
INSERT INTO sample VALUES(seq1.NEXTVAL,…….);
2. Create a Stored Procedure for Account Transaction
The SP should accept AccountNo, Amount, Type of Transaction and Description and
insert them into the AccountTransaction table. The TransactionId should be generated
using the sequence that was created before. The TransactionDate should be the system
date. This insertion should happen only if the following conditions are met:
a. The AccountNo should exist.
b. The Amount should be a positive number.
c. The Type of transaction should be valid (‘D’ for Deposit or ‘W’ for
Withdraw).
d. If the Type is ‘W’, sufficient amount should be available for the
transaction. That is, the balance amount should be greater than or
equal to 500 after withdrawal.
The SP should also update CurrentBalance in the Account table. The SP should
return an integer as explained below
i) -1, if the AccountNo is invalid.
ii) -2, if the Amount is not a positive number.
iii) -3, if the fund is not sufficient
iv) -4, if the account is a fixed account
v) -5, if the type of transaction is other than ‘D’ or ‘W’
vi) <TransactionId>, if the transaction is successful.
vii) -6,other exceptions
Note: Calling a stored procedure from SQL prompt
Step 1. Declare host variables for Out parameters in SQL Prompt. E.g.
VARIABLE x Number
Step 2. Execute the procedure.
E.g. EXEC sp_transaction (2000,500,’D’,’Self’);
Step3. Print value of x in SQL Prompt. E.g. PRINT x;
Note: To display the current date
SELECT SYSDATE from dual;
To insert the current date into a table
INSERT INTO sample VALUES(SYSDATE,……);
To display the current date and time:
SELECT to_char(SYSDATE,'DD-MON-YYYY HH:MI:SS') FROM dual;
3. Create a PL/SQL Stored Procedure for FundTransfer.
The Stored Procedure should accept FromAccountNo , ToAccountNo, Description and
Amount and call the Account Transaction Stored Procedure ( “Creating PL/SQL stored
procedures” Assignment) for withdrawing amount from the FromAccountNo and
depositing amount to the ToAccountNo. The SP should return an integer as explained
below:
i) -1, if the AccountNo is invalid.
ii) -2, if the Amount is not a positive number.
iii) -3, if the fund is not sufficient
iv) -4, if the account is a fixed account
v) 0, if the fund transfer is successful.
i) -6,other exceptions
Write an anonymous PL/SQL block to invoke the stored procedure. The anonymous block
should display appropriate messages based on the return value from the procedure.