Assignment 1.Stored Procedure
SET A
a. Write a procedure to display addition, subtraction and multiplication of three numbers.
CREATE OR REPLACE PROCEDURE calc_three_numbers(a NUMERIC, b NUMERIC, c NUMERIC)
LANGUAGE plpgsql
AS $$
DECLARE
addition NUMERIC;
subtraction NUMERIC;
multiplication NUMERIC;
BEGIN
addition := a + b + c;
subtraction := a - b - c;
multiplication := a * b * c;
RAISE NOTICE 'Addition of %, %, % = %', a, b, c, addition;
RAISE NOTICE 'Subtraction of %, %, % = %', a, b, c, subtraction;
RAISE NOTICE 'Multiplication of %, %, % = %', a, b, c, multiplication;
END;
$$;
Run Query :-
CALL calc_three_numbers(10, 5, 2);
b. Write a procedure to display division of two numbers use raise to display error messages.
CREATE OR REPLACE PROCEDURE divide_two_numbers(a NUMERIC, b NUMERIC)
LANGUAGE plpgsql
AS $$
DECLARE
result NUMERIC;
BEGIN
IF b = 0 THEN
RAISE EXCEPTION 'Error: Division by zero is not allowed';
ELSE
result := a / b;
RAISE NOTICE 'Division of % by % = %', a, b, result;
END IF;
END;
$$;
Run Query:-
CALL divide_two_numbers(10, 2);
c.Create table Department (dno, dname, empname, city ).
i) Write a procedure to insert values in Department table.
ii) Write a procedure to display all employees working in ‘Pune’ city
CREATE TABLE Department (
dno INT,
dname VARCHAR(50),
empname VARCHAR(50),
city VARCHAR(30)
);
i) Write a procedure to insert values in Department table.
CREATE OR REPLACE PROCEDURE insert_department(
pdno INT,
pdname VARCHAR,
pempname VARCHAR,
pcity VARCHAR
)
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO Department (dno, dname, empname, city)
VALUES (pdno, pdname, pempname, pcity);
RAISE NOTICE 'Record inserted successfully';
END;
$$;
Run Query using :-
CALL insert_department(1, 'IT', 'Amit', 'Pune');
CALL insert_department(2, 'HR', 'Sneha', 'Mumbai');
CALL insert_department(3, 'Finance', 'Rahul', 'Pune');
ii) Write a procedure to display all employees working in ‘Pune’ city
CREATE OR REPLACE PROCEDURE display_pune_employees()
LANGUAGE plpgsql
AS $$
DECLARE
rec RECORD;
BEGIN
FOR rec IN
SELECT empname, dname
FROM Department
WHERE city = 'Pune'
LOOP
RAISE NOTICE 'Employee Name: %, Department: %',
rec.empname, rec.dname;
END LOOP;
END;
$$;
Run Query Using :-
CALL display_pune_employees();
SET B
A) Consider the following relationship
Route(route_no,source, destination, no_of_station) Bus
(bus_no, capacity, depot_name)
Relationship between Route and Bus is one-to-many
a. Write a procedure which display all bus details for a given route.
b. Write a procedure to update source of route no 101
Route(route_no, source, destination, no_of_station)
Bus(bus_no, capacity, depot_name, route_no)
CREATE TABLE Route (
route_no INT PRIMARY KEY,
source VARCHAR(50),
destination VARCHAR(50),
no_of_station INT
);
CREATE TABLE Bus (
bus_no INT PRIMARY KEY,
capacity INT,
depot_name VARCHAR(50),
route_no INT REFERENCES Route(route_no)
);
a) Write a procedure which display all bus details for a given route.
CREATE OR REPLACE PROCEDURE display_bus_by_route(rno INT)
LANGUAGE plpgsql
AS $$
DECLARE
rec RECORD;
BEGIN
FOR rec IN
SELECT bus_no, capacity, depot_name
FROM Bus
WHERE route_no = rno
LOOP
RAISE NOTICE 'Bus No: %, Capacity: %, Depot: %',
rec.bus_no, rec.capacity, rec.depot_name;
END LOOP;
IF NOT FOUND THEN
RAISE NOTICE 'No buses found for route no %', rno;
END IF;
END;
$$;
Execute :-
CALL display_bus_by_route(101);
b) PL/pgSQL Procedure to Update Source of Route No 101
CREATE OR REPLACE PROCEDURE update_route_source(new_source VARCHAR)
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE Route
SET source = new_source
WHERE route_no = 101;
IF FOUND THEN
RAISE NOTICE 'Source updated successfully for route no 101';
ELSE
RAISE NOTICE 'Route no 101 not found';
END IF;
END;
$$;
Execute :-
CALL update_route_source('Pune');
B) Consider the following relationship
Patient (p_no, p_name, p_addr) Doctor
(d_no, d_name, d_addr, city)
Relationship between Patient and Doctor is many-to-many with descriptive attribute disease and
no_of_visits.
a. Write a procedure which display patient detail who has visited more than 3 times to the
given doctor for ‘Diabetes’.
b. Write a procedure which displays total number of visits of Dr.Kumar.
Patient (p_no, p_name, p_addr)
Doctor (d_no, d_name, d_addr, city)
Consultation (
p_no,
d_no,
disease,
no_of_visits
)
CREATE TABLE Patient (
p_no INT PRIMARY KEY,
p_name VARCHAR(50),
p_addr VARCHAR(100)
);
CREATE TABLE Doctor (
d_no INT PRIMARY KEY,
d_name VARCHAR(50),
d_addr VARCHAR(100),
city VARCHAR(50)
);
CREATE TABLE Consultation (
p_no INT REFERENCES Patient(p_no),
d_no INT REFERENCES Doctor(d_no),
disease VARCHAR(50),
no_of_visits INT,
PRIMARY KEY (p_no, d_no, disease)
);
a) Procedure to Display Patient Details (Visited more than 3 times to a given doctor for
‘Diabetes’)
CREATE OR REPLACE PROCEDURE display_diabetes_patients(
doc_name VARCHAR
)
LANGUAGE plpgsql
AS $$
DECLARE
rec RECORD;
BEGIN
FOR rec IN
SELECT p.p_no, p.p_name, p.p_addr, c.no_of_visits
FROM Patient p
JOIN Consultation c ON p.p_no = c.p_no
JOIN Doctor d ON d.d_no = c.d_no
WHERE d.d_name = doc_name
AND c.disease = 'Diabetes'
AND c.no_of_visits > 3
LOOP
RAISE NOTICE 'Patient No: %, Name: %, Address: %, Visits: %',
rec.p_no, rec.p_name, rec.p_addr, rec.no_of_visits;
END LOOP;
IF NOT FOUND THEN
RAISE NOTICE 'No such patients found';
END IF;
END;
$$;
Execute
CALL display_diabetes_patients('Dr.Kumar');
b) Procedure to Display Total Number of Visits of Dr. Kumar
CREATE OR REPLACE PROCEDURE total_visits_of_dr_kumar()
LANGUAGE plpgsql
AS $$
DECLARE
total_visits INT;
BEGIN
SELECT SUM(c.no_of_visits)
INTO total_visits
FROM Consultation c
JOIN Doctor d ON d.d_no = c.d_no
WHERE d.d_name = 'Dr.Kumar';
IF total_visits IS NULL THEN
RAISE NOTICE 'Dr. Kumar has no visits recorded';
ELSE
RAISE NOTICE 'Total number of visits of Dr. Kumar = %', total_visits;
END IF;
END;
$$;
Execute :-
CALL total_visits_of_dr_kumar();
No comments:
Post a Comment