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

SQL Commands

Here DDL stands for Data Definition Language.
DML stands for Data Manipulation Language.
DCL stands for Data Control Language.
TCL stands for Tansaction Control Language.

One another DQL category is known as Data Query Language which contains command Select.
It is also known as sub category of DML category.

Difference between SQL and PL/SQL

No.	SQL	PL/SQL
1.	SQL stands for Structured Query Language, which does not have procedural programming capability.	PL/SQL stands for Procedural Structured Query Language. Which have advantage over SQL.
2.	SQL does not support looping.	PL/SQL supports looping concept.
3.	SQL does not provide user friendly error message.	Through PL/SQL user can give user friendly error messages.
4.	SQL supports single line interactive mode.	PL/SQL supports multiline interactive mode, it means through single key stroke any statement can executed multiple times.
5.	If you send a series of SQL statements to the server in standard SQL, the server executes them one at a time in sequential order. So it takes more time than PL/SQL.	PL/SQL allows you to write interactive, user-friendly programs that can pass values into variables. SQL statements can be processed simultaneously for better overall performance. So it takes less time than SQL.
6.	No Programming flexibility available with SQL.	Programmers can divide functions into logical blocks of code. Modular programming techniques support flexibility during the application development.

Disadvantages of SQL

Tough SQL has number of advantages but it contains some of the disavntages as listed below.
(1) It does not contains programming capabilities because we can not use loop with this.\
(2) It does not give user friendly error messages.

Advantages of SQL

Following are various advantages of using SQL.
(1) It is a portable.
(2) It is a high level language. So easy to learn it.
(3) It is highly secure.
(4) In oracle using one command you can update whole table, so less effort required to udate more records.

Oracle History

It was found in year 1970.
The first version was oracle 5.
Later on they find Oracle 6, Oracle 7, Oracle 8, Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11i.
Latest is Oracle 11g.
Here i stand for Internet Commands and g stands for Graphical Commands or Grid view.
Oracle mainly contains two parts SQL and PL/SQL.
SQL stands for Structure Query Language while PL/SQL stands Programming Language Structure Query Language.

Oracle

---------> History of Oracle
---------> Difference between SQL and PL/SQL
---------> Advantages of SQL
---------> Disadvantages of SQL
---------> SQL Commands
---------> SQL Constraint
---------> SQL Join
---------> Example of SQL Queries
---------> PL/SQL Block Syntax
---------> Example of Simple PL/SQL Block
---------> Example of Cursor
---------> Example of trigger