Exercise Examples

1.Write a program to print Hello World.

SQL> begin
  2  dbms_output.put_line('Hello World');
  3  End;
  4  /

PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL> /
Hello World

PL/SQL procedure successfully completed.


2. Write a program to find sum of two numbers from 1 to 100

DECLARE

 N NUMBER(3):=1;

 S NUMBER(4):=0;

 BEGIN

 WHILE N<=100

 LOOP

 S := S+N;

 N :=N+1;

 END LOOP;

 DBMS_OUTPUT.PUT_LINE('THE SUM IS '||S);

 END;

Output:-
The sum of Two Numbers is 15


3.   Print Fibonnaci series using for loop.

declare
a number(3) := 0;
b number(3) :=1;
c number(3) :=0;
begin
dbms_output.put_line('Fibonacci series are upto 10 ');
for m in 1..10
loop
c:=a+b;
dbms_output.put_line(c);
a:=b;
b:=c;
end loop;
end;


Example 4:- Write a pl sql function for calculate  a factorial of given number.

A Recursive function is one that calls itself.Each recursive creates a new instance of any

items declared in the subprogram.
Example:-

create or replace function fac_number(num number)
return number as
begin
if num=1 then
return 1;
else
return(num*fac_number(num-1));
end if;
end;

/

SQL> select fac_number(5) from dual;

FAC_NUMBER(5)
-------------
          120


Example 5:


--WRITE A PROGRAM TO PRINT EVEN NUMBERS FROM 1 TO 100
 DECLARE
 N NUMBER(3) :=0;
 BEGIN
 WHILE N<=100
 LOOP
 N :=N+2;
 DBMS_OUTPUT.PUT_LINE(N);
 END LOOP;
 END;


Example 6  .--Write a program to calculate the sum of 10 natural numbers.


Declare
a number;
tot number default 0;
Begin
a:=1;
loop
tot:=tot+a;
exit when (a=10);
a:=a+1;
end loop;
dbms_output.put_line('Sum between 1 to 100 is '||tot);
End;
/

Example 7 .Write a program to accept a number and print it in reverse order

DECLARE
x NUMBER(5):=&x;
REV NUMBER(5):=0;
R NUMBER(5):=0;
BEGIN
WHILE x !=0
LOOP
R:=MOD(x,10);
REV:=REV*10+R;
x:=TRUNC(x/10);
END LOOP;
DBMS_OUTPUT.PUT_LINE('THE REVERSE OF A GIVEN NUMBER IS '||REV);
END;


Example 8:-

Read More

Merging Rows

Merging Rows

The MERGE statement inserts or updates rows in one table by using data from another table. Each row is inserted or updated in the target table depending on an equijoin

condition.


The example shown matches the employee_id in the  tab_new table to the employee_id in the employees table. If a match is found, the row is updated to match the row in the

employees table. If the row is not found, it is inserted into the tab_new table.

SQL> create table tab_new as select employee_id eid,first_name fname,last_name lname from employees;

SQL> desc tab_new;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EID                                                NUMBER(6)
 FNAME                                         VARCHAR2(20)
 LNAME                                     NOT NULL VARCHAR2(25)

SQL> truncate table tab_new;

SQL> insert into tab_new values(101,'nasim','khan');

SQL> insert into tab_new values(102,'Easha','Deo');
SQL> commit;

SQL> insert into tab_new values(103,'rafat','shaikh');

SQL> commit;

   merge into tab_new t
   using employees e
    on (t.eid=e.employee_id)
    when matched then
    update set
    t.fname=e.first_name,
    t.lname=e.last_name
   when not matched then
  insert values (e.employee_id,e.first_name,e.last_name)

SQL> select * from tab_new;
       EID FNAME                LNAME
---------- -------------------- -------------------------
       101 nasim                khan
       102 Eash                Deo
       103 rafat                shaikh




select * from tab_new;

       EID FNAME                LNAME
---------- -------------------- -------------------------
       101 Neena                Kochhar
       102 Lex                  De Haan
       103 Alexander            Hunold
       198 Donald               OConnell
       199 Douglas              Grant
:

Read More

The anonymous block / What is Plsql

Pl sql is stands for Procedural Language & is use to processes the PL-Sql  statements . it is developed by oracle corporation.

you can use these free online training with examples.

A PLSQL  code can be stored on client side or in the database  that is server side.

In this tutoral you will learn about the PL-SQL block & how to execute the first

PL/SQL Block Structure. The anonymous block has three basic sections that are the declaration, execution, and exception handling. Only the execution section is necessary and the others are optional. 

pl-sql block.
PLSQl block is organize the entrire code into block. & block without name is

called anonymous block. it does not saved in the oracle database.
This anonymous is only one use .
The Follwing is the syntax of annonymous block.
[declare]
Declaration statements;
Begin
Execution statements;
[Exception]
Exception handling statements;
End;
/

If you execute above code you will see a message on the screen that
PL_SQL procedure successfuly completed.

Let us take the example of annonymous block.



DECLARE
  employee_id NUMBER(6);
BEGIN
  SELECT employee_id
  INTO   employee_id
  FROM   employees
  WHERE  last_name='Kochhar';
  DBMS_OUTPUT.PUT_LINE(employee_id);
END;


To display database output on the screen you need to use set serveroutput on

command to instruct sql *plus to echo database output after executiong pl sql

block.
sql> SET SERVEROUTPUT ON

In above example we use dbms_output.put_line  built in package to output a string
on the screen

Example 2.

VARIABLE g_annsal NUMBER

BEGIN
  SELECT (salary+salary*nvl(commission_pct,0))*12
  INTO   :g_annsal
  FROM   employees
  WHERE  employee_id=&empid;
END;
/
PRINT g_annsal

Now you login to the  oracle database by providing username & password.
username :-hr
password :- hr

Login Screen

Example 2 :

DECLARE
  V_NAME VARCHAR2(20);
  V_SAL  NUMBER;
BEGIN
  SELECT LAST_NAME,SALARY
  INTO   V_NAME,V_SAL
  FROM   EMPLOYEES
  WHERE  EMPLOYEE_ID=&EMPNO;

  DBMS_OUTPUT.PUT_LINE(V_NAME||' GETS '||V_SAL);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
       DBMS_OUTPUT.PUT_LINE('NO SUCH EMPLOYEE...');
END;

Example 3:-

DECLARE
  V_NAME VARCHAR2(20);
  V_SAL  NUMBER;
BEGIN
  SELECT LAST_NAME,SALARY
  INTO   V_NAME,V_SAL
  FROM   EMPLOYEES
  WHERE  EMPLOYEE_ID=&EMPNO;

  DBMS_OUTPUT.PUT_LINE(V_NAME||' GETS '||V_SAL);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
       DBMS_OUTPUT.PUT_LINE('NO SUCH EMPLOYEE...');
END;

Read More

Odd No Even No

1.How to fetch alternate records from a table.Like Even /Odd Records.

Even Record :-
select * from (select  rownum rn,employee_id,last_name from employees order by rn) where mod(rn,2)=0

        RN EMPLOYEE_ID LAST_NAME
---------- ----------- -------------------------
         2         199 Grant
         4         201 Hartstein
         6         203 Mavris
         8         205 Higgins
        10         100 King
        12         102 De Haan
        14         104 Ernst
        16         106 Pataballa
        18         108 Greenberg
        20         110 Chen
        22         112 Urman



Odd Record
select * from (select  rownum rn,employee_id,last_name from employees order by rn) where mod(rn,2)!=0

        RN EMPLOYEE_ID LAST_NAME
---------- ----------- -------------------------
         1         198 OConnell
         3         200 Whalen
         5         202 Fay
         7         204 Baer
         9         206 Gietz
        11         101 Kochhar
        13         103 Hunold
        15         105 Austin
        17         107 Lorentz
        19         109 Faviet
        21         111 Sciarra

Read More

Insert into select statement

1.How can U call Pl Sql Procedure From Sql
We can type  execute procedure (short term EXEC)
like EXEC procedure_name;


2.Write a query to find Nth highest salary from a table.

select distinct(e.salary)
from employees e
where &eno=(select count(distinct(e1.salary))
from employees e1
where e.salary<=e1.salary)

Output:- Suppose You want to find second highest salary

Enter value for eno: 2
old   3: where &eno=(select count(distinct(e1.salary))
new   3: where 2=(select count(distinct(e1.salary))

    SALARY
----------
     17000

3.Explain Insert into select statement .

In order to insert a data from one table to another table we use below query.

First We need to create a table

create table emp1 (empno number primary key,deptno number,job_id varchar2(50))
/
 insert into emp1(empno,deptno,job_id)
 select employee_id,department_id,job_id
 from employees
 where department_id=20;

2 rows created.
sql> select * from emp1;

     EMPNO     DEPTNO JOB_ID
---------- ---------- --------------------------------------------------
       201         20 MK_MAN
       202         20 MK_REP




4.Inline view:-It is a subquery that comes with from clause of the sql statement.
The inline view where you can put your query in sql from clause.

Suppose we want to select top highest salary 4 records use below query.

select employee_id,last_name,salary
from(select employee_id,last_name,salary
from employees
order by salary desc )
where rownum <=4

EMPLOYEE_ID LAST_NAME                     SALARY
----------- ------------------------- ----------
        100 King                           24000
        101 Kochhar                        17000
        102 De Haan                        17000
        145 Russell                        14000

Read More

PLSQL functions

A Function is a named pl sql subprogarm.A function is always return single value.

|| Operator in oracle is can be used to concatenate multiple strings to form a single string.

A syntax to create a function in oracle is as follows:-

Create [or replace] function function_name[(parameter[,parameter])]

return  return_datatype

is|As

[declaration_section]

begin

executable_section

[Exception exception_section]

End[function_name];

Example:-

create or replace function fun1 (x varchar2, y varchar2)

return  varchar2

is begin

return x|| '   '||y;

end;

Output:- You could reference your function by in a  Sql statement.

select fun1(first_name,job_id) from employees;

Read More

Zero_divide

In this example , a Pl sql block attempts to divide by 0.
Zero_divide is a predefined exception & it is used for to trap the error in an exception block.


declare
num number;
Begin
num := 200/0;
Exception
when zero_divide then
dbms_output.put_line('You are trying to divide number by zero');
End;
/


SQL> set serveroutput on
Output:-

You are trying to divide number by zero

Example 2:- Invalid Number Exception.

In the following example,the insert statement implcitly raises the
predefined exception Invalid Number.
We need to create table for example t1.

Create table t1(id number);

SQL> Declare
  2  num number :=0;
  3  begin
  4  insert into t1 values(to_number('150.00','9G999'));
  5  Exception
  6  when invalid_number then
  7  Dbms_output.put_line('Invalid Number');
  8  Insert into t1 values (num);
  9  End;
 10  /
Invalid Number

PL/SQL procedure successfully completed.

SQL> select * from t1;

        ID
----------
         0

1 row selected.

Read More

Cursor For Loop Using Subquery

Example:- 

BEGIN
  FOR EMPREC IN (SELECT * FROM EMPLOYEES) LOOP
     DBMS_OUTPUT.PUT_LINE(EMPREC.LAST_NAME||' GETS '||EMPREC.SALARY);
  END LOOP;
END;


Output

SQL> set serveroutput on
SQL> /
OConnell GETS 2600
Grant GETS 2600
Whalen GETS 4400
Hartstein GETS 15000
Fay GETS 8000
Mavris GETS 6500
Baer GETS 10000
Higgins GETS 12000
Gietz GETS 8300
King GETS 24000
Kochhar GETS 17000
De Haan GETS 17000
Hunold GETS 9000
Ernst GETS 6000
Austin GETS 4800
Pataballa GETS 4800
Lorentz GETS 4200
Greenberg GETS 12000
Faviet GETS 9000
Chen GETS 8200
Sciarra GETS 7700
Urman GETS 7800
Popp GETS 6900
Raphaely GETS 11000
Khoo GETS 3100
Baida GETS 2900
Tobias GETS 2800
Himuro GETS 2600
Colmenares GETS 2500
Weiss GETS 8000
Fripp GETS 8200


Example 2:-

DECLARE
  CURSOR EMPCUR IS SELECT * FROM EMPLOYEES;
BEGIN
  FOR EMPREC IN EMPCUR LOOP
     DBMS_OUTPUT.PUT_LINE(EMPREC.LAST_NAME||' GETS '||EMPREC.SALARY);
     EXIT WHEN EMPCUR%ROWCOUNT>=10;
  END LOOP;
END;

Read More

Cursor For Loop

We can use Cursor for loop when we want to fetch & precess every record  in a cursor.Cursor for loop will terminate when  all of the records in the cursor have been fetched.

Syntax:-
For record_index in cursor_name
loop
{Statements...}
End loop;


Record Index is is the name of record.

Cursor Name is the name of cursor that you wish to fetch records from it.Statements are the

statements of code to execute through the cursor for loop.

Here is an example of Cursor for loop.


DECLARE
  CURSOR EMPCUR IS SELECT * FROM EMPLOYEES;
BEGIN
  FOR EMPREC IN EMPCUR LOOP
     DBMS_OUTPUT.PUT_LINE(EMPREC.LAST_NAME||' Earning '||EMPREC.SALARY);
  END LOOP;
END;
/
In this example we have created a cursor Empcur & the Cursor will terminate after all records

have been fetched.
sql> set serveroutput on

Output:- OConnell Earning 2600
Grant Earning 2600
Whalen Earning 4400
Hartstein Earning 15000
Fay Earning 8000
Mavris Earning 6500
Baer Earning 10000
Higgins Earning 12000
Gietz Earning 8300
King Earning 24000
Kochhar Earning 17000
De Haan Earning 17000
Hunold Earning 9000
Ernst Earning 6000
Austin Earning 4800
Pataballa Earning 4800
Lorentz Earning 4200
Greenberg Earning 12000
Faviet Earning 9000
Chen Earning 8200
Sciarra Earning 7700
Urman Earning 7800
Popp Earning 6900
Raphaely Earning 11000
Khoo Earning 3100
Baida Earning 2900
Tobias Earning 2800
Himuro Earning 2600
Colmenares Earning 2500
Weiss Earning 8000
Fripp Earning 8200
Kaufling Earning 7900
Vollman Earning 6500
Mourgos Earning 5800
Nayer Earning 3200
Mikkilineni Earning 2700
Landry Earning 2400
Markle Earning 2200
Bissot Earning 3300
Atkinson Earning 2800
Marlow Earning 2500
Olson Earning 2100
Mallin Earning 3300
Rogers Earning 2900
Gee Earning 2400
Philtanker Earning 2200
Ladwig Earning 3600
Stiles Earning 3200
Seo Earning 2700
Patel Earning 2500
Rajs Earning 3500
Davies Earning 3100
Matos Earning 2600
Vargas Earning 2500
Russell Earning 14000
Partners Earning 13500
Errazuriz Earning 12000

Read More

Nested Loop

Nested loop is a easy way of combining data from two row sources.it takes all the rows from outer loop and for each of them it looks up row matching
the join condition from other  inner row source.
Write a pl sql program to calculate the yearly bonus that the company gives to its employees.
The company has some criteria is as follows:-
1. If a department_id is 80  then no bonus.
2. if an employee receives >20000 then he/she receives 0.02% bonus,
   >15000 then he/she receives 0.03% bonus,
   >10000 then he/she receives 0.04% bonus.
Display calculated bonus for an each employee using Nested Loop.


Example:-

DECLARE

  V_DEPT  EMPLOYEES.DEPARTMENT_ID%TYPE;

  lname  EMPLOYEES.LAST_NAME%TYPE;

  V_SAL   EMPLOYEES.SALARY%TYPE;

  V_BONUS V_SAL%TYPE;

BEGIN

  SELECT LAST_NAME,SALARY,DEPARTMENT_ID

  INTO   lname,V_SAL,V_DEPT

  FROM   EMPLOYEES

  WHERE  EMPLOYEE_ID=&EMPNO;

  IF V_DEPT=80 THEN

     V_BONUS :=0;

  ELSE

     IF V_SAL   >= 20000 THEN

        V_BONUS := V_SAL*.2;

     ELSIF V_SAL>=15000 THEN

        V_BONUS := V_SAL*.3;

     ELSIF V_SAL>= 10000 THEN

        V_BONUS := V_SAL *.4;

     ELSE

        V_BONUS := V_SAL*.5;

     END IF;

  END IF;

  DBMS_OUTPUT.PUT_LINE('BONUS OF '||lname||' IS '||V_BONUS);

END;

/

Example 2 :-

BEGIN
         FOR i IN 1..2 LOOP
              FOR j IN 1..4 LOOP
                  DBMS_OUTPUT.PUT_LINE('Outer Loop counter is ' ||
                       i ||
                       ' Inner Loop counter is ' || j);
             END LOOP;
         END LOOP;
  END;
/

Output:-

Outer Loop counter is 1 Inner Loop counter is 1

Outer Loop counter is 1 Inner Loop counter is 2

Outer Loop counter is 1 Inner Loop counter is 3

Outer Loop counter is 1 Inner Loop counter is 4

Outer Loop counter is 2 Inner Loop counter is 1

Outer Loop counter is 2 Inner Loop counter is 2

Outer Loop counter is 2 Inner Loop counter is 3

Outer Loop counter is 2 Inner Loop counter is 4

Read More

Basic Loop In Oracle

Basic Loop Statement:-

The Loops means run the same statements with a series of differant values.


Structure of basic loop is
[label  ] loop
Stetements
end loop[label];

with each iteration of the loop statement run and control returns to the top of the loop.
To avoid an infinite loop ,we must use exit loop.exit statement exists the current iteration of a loop,eigher conditionally,or

unconditionally.

Example :-Write a pl sql block to print 1..10 numbers using basic loop.

DECLARE
  NUM NUMBER := 1;
BEGIN
  LOOP
    DBMS_OUTPUT.PUT_LINE(NUM);
    NUM := NUM+1;
    EXIT WHEN NUM>10;
  END LOOP;
END;


Example :- 2. List the Name,salary  of an employees from Employee_id 100 ...To 109 using basic loop.

DECLARE
  V_NAME EMPLOYEES.LAST_NAME%TYPE;
  V_SAL  EMPLOYEES.SALARY%TYPE;
BEGIN
  FOR V_ID IN 100..109 LOOP
      SELECT LAST_NAME,SALARY
      INTO   V_NAME,V_SAL
      FROM   EMPLOYEES
      WHERE  EMPLOYEE_ID=V_ID;
      DBMS_OUTPUT.PUT_LINE(V_NAME||' GETS '||V_SAL);
  END LOOP;
END;

Output:-
King GETS 24000
Kochhar GETS 17000
De Haan GETS 17000
Hunold GETS 9000
Ernst GETS 6000
Austin GETS 4800
Pataballa GETS 4800
Lorentz GETS 4200
Greenberg GETS 12000
Faviet GETS 9000

PL/SQL procedure successfully completed.

Example 3:-  List the Name,salary,bonus based on salary  of an employees from Employee_id 100 ...To 109 using basic loop.

DECLARE
  lNAME EMPLOYEES.LAST_NAME%TYPE;
  V_SAL  EMPLOYEES.SALARY%TYPE;
  V_BONUS V_SAL%TYPE;
BEGIN
  FOR EMPID IN 100..109 LOOP
           SELECT  LAST_NAME,SALARY
            INTO       lNAME,V_SAL
            FROM     EMPLOYEES
           WHERE   EMPLOYEE_ID=EMPID;
           IF V_SAL   >= 20000 THEN
                V_BONUS := V_SAL*.2;
           ELSIF V_SAL>=15000 THEN
               V_BONUS := V_SAL*.3;
           ELSIF V_SAL>= 10000 THEN
               V_BONUS := V_SAL *.4;
           ELSE
               V_BONUS := V_SAL*.5;
           END IF;
           DBMS_OUTPUT.PUT_LINE(lNAME||' GETS '||V_SAL);
           DBMS_OUTPUT.PUT_LINE('BONUS OF '||lNAME||' IS '||V_BONUS);
  END LOOP;
END;
/

For Loop:-

Pl sql for loop is an iterative statement that allows to execute
a series of statements a fixed number of times.
The foll is a syntax for loop statement.

FOR loop_counter IN [REVERSE] lower_bound .. higher_bound
LOOP
   sequence_of_statements;
END LOOP;

SQL> declare
  2  n1 number;
  3  begin
  4  for  i in 1.. 10
  5  loop
  6  dbms_output.put_line(i);
  7  end loop;
  8  end;
  9  /

Output :-

1
2
3
4
5
6
7
8
9
10

in the

PL/SQL procedure successfully completed.

in the next example we will use the RESERVE keyword to print a list of integers in

descending order.

declare
n1 number;
begin
for  i in reverse 1.. 10
loop
dbms_output.put_line(i);
end loop;
end;
/
Output

SQL> /
10
9
8
7
6
5
4
3
2
1



Example 2.  write a PL/SQL block to display names and salary of employees.
.
 DECLARE
   V_NAME EMPLOYEES.LAST_NAME%TYPE;
   V_SAL  EMPLOYEES.SALARY%TYPE;
 BEGIN
   FOR V_ID IN 100..109 LOOP
       SELECT LAST_NAME,SALARY
       INTO   V_NAME,V_SAL
       FROM   EMPLOYEES
       WHERE  EMPLOYEE_ID=V_ID;
       DBMS_OUTPUT.PUT_LINE(V_NAME||' GETS '||V_SAL);
   END LOOP;
 END;

Output:-

King GETS 24000
Kochhar GETS 17000
De Haan GETS 17000
Hunold GETS 9000
Ernst GETS 6000
Austin GETS 4800
Pataballa GETS 4800
Lorentz GETS 4200
Greenberg GETS 12000
Faviet GETS 9000

PL/SQL procedure successfully completed.



While Loop:-





The while statement again and again executes a block of statements while a particular condition is true.
it's sysntax can be expressed as
While(expression)
{
statements
}
while statement evaluate expression which must return a boolean value.if expression is true while statement executes statements in the while block & it continues testing the expression until the expression evaluates to false.

Example:-Print 1..10 numbers using while loop.

DECLARE
NUM NUMBER := 1;
BEGIN
WHILE NUM <=10 LOOP
DBMS_OUTPUT.PUT_LINE(NUM);
NUM := NUM+1;
END LOOP;
END;

Output

1
2
3
4
5
6
7
8
9
10

PL/SQL procedure successfully completed.

Read More

Can not Insert null value

Definition:- You are trying to insert a null value which have not null constraint for department_name column define for it.
you will have to specified value for this column or give a default value for it.

Example:-

 DECLARE
  insert_excep EXCEPTION;
  PRAGMA EXCEPTION_INIT   (insert_excep, -01400);
 BEGIN
  INSERT INTO departments (department_id, department_name) VALUES (280,  NULL);
 EXCEPTION
  WHEN insert_excep THEN
  DBMS_OUTPUT.PUT_LINE('INSERT OPERATION FAILED');
  DBMS_OUTPUT.PUT_LINE(SQLERRM);
 END;

Read More