RDBMS-Procedures

  Assignment 1: Procedures

• A subprogram is a program unit/module that performs a particular task. These subprograms are combined to form larger programs.
•  This is basically called the 'Modular design'. A subprogram can be invoked by another subprogram or program which is called the calling program.
• A subprogram can be created at the schema level, Inside a package, Inside a PL/SQL block.
• At the schema level, subprogram is a standalone subprogram.
•  It is created with the CREATE PROCEDURE or the CREATE FUNCTION statement. It is stored in the database and can be deleted with the DROP PROCEDURE or DROP FUNCTION statement. 
• A subprogram created inside a package is a packaged subprogram. It is stored in the database and can be deleted only when the package is deleted with the DROP PACKAGE statement. 
• 
  
• PL/SQL subprograms are named PL/SQL blocks that can be invoked with a set of parameters.
• PL/SQL provides two kinds of subprograms −
•  Functions − These subprograms return a single value; mainly used to compute and return a value.
• Procedures − These subprograms do not return a value directly; mainly used to perform an action. Each PL/SQL subprogram has a name, and may also have a parameter list.

1. Declarative Part:-

It is an optional part. However, the declarative part for a subprogram does not start with the DECLARE keyword. It contains declarations of types, cursors, constants, variables, exceptions, and nested subprograms. These items are local to the subprogram and cease to exist when the subprogram completes execution.

2. Executable Part:-

This is a mandatory part and contains statements that perform the designated action.

3 .Exception-handling:-

This is again an optional part. It contains the code that handles run-time errors.

Create Procedure Syntax:-

CREATE [OR REPLACE] PROCEDURE procedure_name(parameter_list)

LANGUAGE language_name

AS $$

stored_procedure_body;

$$;

Assignment 

SET A

a. Write a procedure to display sum and product of two numbers using IN and OUT.

CREATE OR REPLACE FUNCTION sum_n_product3(IN x int,IN y int, OUT sum int, OUT prod int) AS $$

BEGIN

 IF x < 2 THEN

 RAISE WARNING 'information message %', now();

 RAISE NOTICE 'information message %', now();

 RAISE INFO 'information message %', now();

 END IF;

 sum := x + y;

 prod := x * y;

 END;

 $$ LANGUAGE plpgsql ;

CREATE FUNCTION

postgres=# select sum_n_product3(2,5);

 sum_n_product3 

----------------

 (7,10)

(1 row)

b. Write a procedure to display division of two numbers use raise to display error messages.

Create or replace function div_no(IN x int,IN y int, OUT div int) AS $$

BEGIN 

div :=x/y;

 END;

 $$ LANGUAGE plpgsql;

CREATE FUNCTION

postgres=# select div_no(4,2);

 div_no 

--------

      2

(1 row)

SET B

Create table employee (empno, ename, dept, salary).

a. Write a procedure to insert values in employee table.

CREATE OR REPLACE FUNCTION AddEmployee  

    (  

       INOUT Empno INT,  

       IN EmpName VARCHAR(100),  

       IN EmpDept VARCHAR(100),  

       IN EmpSalary INT

    )  

    LANGUAGE plpgsql AS  

    $$  

    BEGIN         

       INSERT INTO Employee (eno,ename,dept,salary) VALUES   

        (Empno,

  EmpName,  

         EmpDept,  

         EmpSalary  

         );  

    END  

    $$;  

CREATE FUNCTION

postgres=# select AddEmployee(101,'abc','xyz',50000);

 addemployee 

-------------

         101

(1 row)

b. Write a procedure to accept dept and display all employees working in that dept.

postgres=# CREATE OR REPLACE FUNCTION GetEmployee1(EmpDept varchar(50))  

    RETURNS setof employee  

    LANGUAGE SQL   

    AS   

    $$  

        SELECT * FROM employee WHERE dept = EmpDept;  

    $$; 

CREATE FUNCTION

postgres=# select GetEmployee1('HR');

    getemployee1     

---------------------

 (104,ccc,HR,50000)

 (105,ddd,HR,600000)

(2 rows)

SET C

Route(route_no, source, destination, no_of_station)

Bus (bus_no, capacity, depot_name)

Relationship between Route and Bus is one-to-many

postgres=# create table Route (rno int primary key, source varchar(50),dest varchar(50),no_of_station int);

CREATE TABLE

postgres=# create table Bus(bno int primary key, capacity int, depot_name varchar(20), rno int references Route(rno));

CREATE TABLE

postgres=# insert into Route values(11,'Nashik','ozar',5);

INSERT 0 1

postgres=# insert into Route values(12,'Nashik','lasalgaon',10);

INSERT 0 1

postgres=# insert into Route values(13,'Nashik','satana',8);

INSERT 0 1

postgres=# insert into Bus values(101,100,'nashik',11);

INSERT 0 1

postgres=# insert into Bus values(102,80,'nashik',12);

INSERT 0 1

postgres=# insert into Bus values(103,80,'nashik',12);

INSERT 0 1

postgres=# insert into Bus values(104,80,'satana',13);

INSERT 0 1

a. Write a procedure which will display all bus details for a given route.

postgres=# CREATE OR REPLACE FUNCTION getBusDetail(Routeno int)  

postgres-#     RETURNS setof Bus  

postgres-#     LANGUAGE SQL   

postgres-#     AS   

postgres-#     $$  

postgres$#         SELECT * FROM Bus WHERE rno = Routeno;  

postgres$#     $$; 

CREATE FUNCTION

postgres=# select getBusDetail(12) ;

    getbusdetail    

--------------------

 (102,80,nashik,12)

 (103,80,nashik,12)

(2 rows)

postgres=# select getBusDetail(11);

    getbusdetail     

---------------------

 (101,100,nashik,11)

(1 row)

b. Write a procedure to update source of route no 101.

CREATE OR REPLACE Function UpdateRoute(INOUT RouteNo int,RouteSource varchar(50) )  

    LANGUAGE plpgsql AS   $$  

    BEGIN         

       UPDATE Route SET   

       source=RouteSource  

       Where rno = RouteNo;  

    END  

    $$;

CREATE FUNCTION

postgres=# select UpdateRoute(11,'pune');

 updateroute 

-------------

          11

(1 row)