Database Programming with PL/SQL Lab Manual
Experiment 1:
Write a PL/SQL program using for loop to insert ten rows into a data base
table
-- create a table
CREATE TABLE students (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
gender TEXT NOT NULL
);
-- insert some values
INSERT INTO students VALUES (1, 'Ryan', 'M');
INSERT INTO students VALUES (2, 'Joanna', 'F');
-- fetch some values
SELECT * FROM students WHERE gender = 'F';
Experiment 2:
Given the table EMPLOYEE (EmpNo, Name, Salary, Designation, DeptID)
write a cursor to select the five highest paid employees from the table using
PL/SQL
Input Table
SQL> SELECT ename, empno, sal FROM emp ORDER BY sal DESC;
ename EMPNO sal
---------- ----------- --------
KING 7839 5000
SCOTT 7788 3000
FORD 7902 3000
JONES 7566 2975
BLAKE 7698 2850
CLARK 7782 2450
ALLEN 7499 1600
TURNER 7844 1500
MILLER 7934 1300
WARD 7521 1250
MARTIN 7654 1250
ADAMS 7876 1100
JAMES 7900 950
SMITH 7369 800
declare
highest number;
name char(20);
cursor c1 is
select ename,sal from emp;
z c1%rowtype;
begin
highest:=0;
open c1;
fetch c1 into z;
for z in c1 loop
if sal>highest
highest:=salary;
name:=ename;
fetch c1 into z;
end if;
end loop;
dbms_output.put_line('Name of Highest Paid Salary = '||
name);
dbms_output.put_line('Highest Salary = '||highest);
close c1;
end;
Experiment 3:
Illustrate how you can embed PL/SQL in a high level host language such as
C/Java and demonstrate how a banking debit transaction might be done
-- DECLARING VARIABLES
DECLARE
xacct_no number(5);
-- here, minimum balance is set to 1000;
xmin_bal number(5):=1000;
xbalance number(5);
BEGIN
-- taking input from user
xacct_no:=&xacct_no;
-- selecting balance of that user INTO "xbalance";
select balance into xbalance
from acct_master
where acct_no=xacct_no;
-- if condition true, updating balance
-- with balance = balance - 100
IF(xbalance < xmin_bal) THEN --condition check
update acct_master
set balance=balance-100
where acct_no=xacct_no;
-- remaining amount
xbalance:=xbalance-100;
dbms_output.put_line('Rs 100 is deducted
and current balance is '||xbalance);
-- if condition is false
ELSE
dbms_output.put_line('Current balance is '||xbalance);
--ENDING IF
END IF;
-- ENDING OF BEGIN
END;
/ -- FOR DISPLAYING OUTPUT IN SCREEN
Output::
Enter value for xacct_no: 2
old 6: xacct_no:=&xacct_no;
new 6: xacct_no:=2;
Rs 100 is deducted and current balance is 0
PL/SQL procedure successfully completed.
SQL> /
Enter value for xacct_no: 3
old 6: xacct_no:=&xacct_no;
new 6: xacct_no:=3;
Current balance is 1100
PL/SQL procedure successfully completed.
Experiment 4:
Given an integer I, write a PL/SQL procedure to insert the tuple (I,’XXX’)
into a given relation.
CREATE TABLE T2 (
a INTEGER,
b CHAR(10)
);
CREATE PROCEDURE addtuple1(i IN NUMBER) AS
BEGIN
INSERT INTO T2 VALUES(i, 'xxx');
END addtuple1;
.
run;
Experiment 5:
Write a PL/SQL program to demonstrate exceptions
DECLARE
c_id customers.id%type := 8;
c_name customers.name%type;
c_addr customers.address%type;
BEGIN
SELECT name, address INTO c_name, c_addr
FROM customers
WHERE id = c_id;
DBMS_OUTPUT.PUT_LINE ('Name: '|| c_name);
DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr);
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('No such customer!');
WHEN others THEN
dbms_output.put_line('Error!');
END;
/
After the execution of above code at SQL Prompt, it produces the following result:
No such customer!
PL/SQL procedure successfully completed.
