PL/SQL Block Syntax

Following is syntax for general block,

declare

          variable, constant, cursor etc. declaration

begin

           (SQL, PLSQL) executable statements

exception

           error handling code

end;

·       The different parts/sections of a PL/SQL block are discussed as below.

·       The declare section contains the definitions of variables and other objects such as constants and cursors etc.

·       This section is an optional part of a PL/SQL block.

·       The procedure section contains conditional commands and SQL statements and is where the block is controlled.

·       This section is the only mandatory part of a PL/SQL block.

·       The exception section tells the PL/SQL block how to handle specified errors and user-defined exceptions.

·       This section is an optional part of a PL/SQL block.

·       Following is example.

SQL> set serveroutput on;

SQL> declare

                pi constant number(9,7) := 3.1415926;

r number(5);

                area number(14,2);

    begin

                R:= &r;

                area := pi * power(r,2);

                dbms_output.put_line('area='||area);

    end;

            .

SQL> /

Enter value for r: 2

old   6: R:= &r;

new  6: R:= 2;

area=12.57

PL/SQL procedure successfully completed.

Example of trigger

[1] Write a database trigger before update / delete for each statement not allowing any of these operations on the table item for qty greater than 100.

              create or replace trigger chk_qty

before update or delete on item

declare

                q item.qty % type;

                id item.ino % type;

begin

                        id:= &id;

                        select qty in q from item where ino = id;

                        if q > 100 then

dbms_output.put_line (‘can not delete or update’);

                        end if;

  end;   

Example of Cursor in PL/SQL

 (1) Write a PL/SQL block which display all employee name and salary using cursor.

declare

            ename emp.empname%type;

            sal emp.salary%type;

            cursor employee is select empname,salary from emp;

     begin

            open employee;

            loop

                    fetch employee into ename,sal;

                    exit when employee%notfound;

                    dbms_output.put_line(ename || sal);

            end loop;

            close employee;

    end;

Example of Simple PL/SQL Block

1. Write a PL-SQL Block which takes name as input and then display that name.

Declare

Name Char(100);

Begin

                Name:='&Name';

                dbms_output.put_line('Name='||Name);

End;

Output:

        Enter value for name: Ankur N Shah

old   4: Name:='&Name';

new   4: Name:='Ankur N Shah';

Name=Ankur N Shah

PL/SQL procedure successfully completed.

Examples of SQL Queries

Question: Solve the following query using the below tables.

Tables:

1. Suppliers(Sid: integer, Sname: string, address: string)
2. Parts(Pid: integer, Pname: string, color: string)
3. Catalog(Sid: integer, Pid: integer, cost: real)

(1)               Find the name of suppliers who supply some red part.

Solution(1):      

            Select Sname from Suppliers where Sid in

            (Select Sid from Catalog Where Pid in

(Select Pid from Parts where lower(color)=’red’));

Solution(2):

Select S.Sname from Suppliers S, Catalog C, Parts P Where S.Sid = C.Sid and C.Pid = P.Pid and lower(P.color)=’red’;

(2)        Find the Sids of suppliers who supply some red or some green parts.

Solution(1):

            Select Sname from Suppliers where Sid in

            (Select Sid from Catalog Where Pid in

(Select Pid from Parts where lower(color)=’red’ or lower(color)=’green’));

Solution(2):

Select S.Sname from Suppliers S, Catalog C, Parts P Where S.Sid = C.Sid and C.Pid = P.Pid and (lower(P.color)=’red’ or lower(P.color)=’green’);

(3)        Find the Sids of suppliers who supply some red part or are at 220 Packer street.

Solution(1):

            Select Sname from Suppliers where Sid in

            (Select Sid from Catalog Where Pid in

(Select Pid from Parts where lower(color)=’red’)) or

lower(address)=lower(’220 Packer street’);

Solution(2):

Select S.Sname from Suppliers S, Catalog C, Parts P Where (S.Sid = C.Sid and C.Pid = P.Pid and lower(P.color)=’red’) or (lower(S.address)=lower(’220 Packer street’));

(4)               Find Sids of Suppliers who supply some red and some green parts.

Solution(1):      

            Select Sname from Suppliers where Sid in

            (Select Sid from Catalog Where Pid in

(Select Pid from Parts where lower(color)=’red’)) and Sid in

            (Select Sid from Catalog Where Pid in

(Select Pid from Parts where lower(color)=’green’));

SQL Joins

How to give alias name to table?

Ans: Select * from sailor S;        

Here S is given alias name to sailor table.

How to give alias name to column?

Ans: Select Sid as id from sailor;

Here id is given as alias name to sid column of sailor table.

Types of Join:

(1)   Self Join

(2)   Simple Join

(3)   Outer Join

(1) Self Join:

            When table join it self then it is known as self join.

Examples:

(1) Select r1.day from reserves r1, reserves r2 where r1.sid = r2.bid;

(2) Select e1.ename as employee, e2.ename as manager from emp e1, emp e2

where e1.empno = e2.mgrno;

(2) Simple Join:

            When two table joins to each other for extracting data it is known as simple join.

Examples:

(1) Select S.Sname from sailor S, Reserves R Where S.sid = R.sid and R.bid=1;

(2) Select B.Color from Boat B, Sailor S, Reserves R Where B.Bid = R.Bid and

R.Sid = S.Sid and S.Sname = 'abc';

(3) Outer Join:

            When + operator is use with join it is known as outer join.

Examples:

(1) Select S.Sname from Sailor S, Reserves R where S.sid = R.sid (+);

// This Query display only those records whose does not belong to reserves table but belongs to sailor table.

(2) Select S.Sname from Sailor S, Reserves R where S.sid (+) = R.sid;

// This Query display only those records whose does not belong to sailor table but belongs to reserves table.

Note: We can not put + at both side of condition.

SQL Constraint