Hello World WHERE employee_id = emp_id;

DECLARE END;
message varchar2(20):= 'Hello, World!'; /
BEGIN
dbms_output.put_line(message);
END; DECLARE
/ sales NUMBER(8,2) := 20000;
bonus NUMBER(6,2);
I\p from User emp_id NUMBER(6) := 120;
declare BEGIN
a number; IF sales > 50000 THEN
begin bonus := 1500;
a := :a; ELSIF sales > 35000 THEN
dbms_output.put_line('Inputed Number is >> '|| a); bonus := 500;
end; ELSE
/ bonus := 100;
END IF;
Select input UPDATE employees SET salary = salary + bonus
DECLARE WHERE employee_id = emp_id;
    i NUMBER := 1; END;
BEGIN /
    LOOP
        INSERT INTO T1 VALUES(i,i);
DECLARE
        i := i+1;
grade CHAR(1);
        EXIT WHEN i>100;
    END LOOP;
BEGIN
END;
grade := 'B';
IF grade = 'A' THEN
If, then, else DBMS_OUTPUT.PUT_LINE('Excellent');
ELSIF grade = 'B' THEN
DECLARE DBMS_OUTPUT.PUT_LINE('Very Good');
sales NUMBER(8,2) := 12100; ELSIF grade = 'C' THEN
quota NUMBER(8,2) := 10000; DBMS_OUTPUT.PUT_LINE('Good');
bonus NUMBER(6,2); ELSIF grade = 'D' THEN
emp_id NUMBER(6) := 120; DBMS_OUTPUT. PUT_LINE('Fair');
BEGIN ELSIF grade = 'F' THEN
IF sales > (quota + 200) THEN DBMS_OUTPUT.PUT_LINE('Poor');
bonus := (sales - quota)/4; ELSE
ELSE DBMS_OUTPUT.PUT_LINE('No such grade');
bonus := 50; END IF;
END IF; ENd;
UPDATE employees SET salary = salary + bonus /
WHERE employee_id = emp_id;
END;
/ DECLARE
a number(3) := 100;
BEGIN
DECLARE IF ( a = 10 ) THEN
sales NUMBER(8,2) := 12100; dbms_output.put_line('Value of a is 10' );
quota NUMBER(8,2) := 10000; ELSIF ( a = 20 ) THEN
bonus NUMBER(6,2); dbms_output.put_line('Value of a is 20' );
emp_id NUMBER(6) := 120; ELSIF ( a = 30 ) THEN
BEGIN dbms_output.put_line('Value of a is 30' );
IF sales > (quota + 200) THEN ELSE
bonus := (sales - quota)/4; dbms_output.put_line('None of the values is matching');
ELSE END IF;
IF sales > quota THEN dbms_output.put_line('Exact value of a is: '|| a );
bonus := 50; END;
ELSE /
bonus := 0;
END IF;
END IF;
DECLARE
UPDATE employees SET salary = salary + bonus
x NUMBER := 100;
BEGIN Aim: To write a pl/sql program to finding factorial of given
FOR i IN 1..10 LOOP number.
IF MOD(i,2) = 0 THEN -- i is even  declare
INSERT INTO temp VALUES (i, x, 'i is even');        i number(4):=1;
ELSE        n number(4):=&n;
INSERT INTO temp VALUES (i, x, 'i is odd');        f number(4):=1;
END IF;  begin
x := x + 100;     for i in 1..n
END LOOP;     loop
COMMIT;        f:=f*i;
END;      end loop;
    Dbms_output.put_line('the factorial of '||n||' is:'||f);
    end;
Max between three no.s    /
Declare
    a number; Aim: To write a pl/sql program to generate fibinocci series.
    b number;
    c number;    declare
Begin       a number(3):=1;
    dbms_output.put_line('Enter a:');       b number(3):=1;
        a:=&a;       c number(3);
    dbms_output.put_line('Enter b:');       n number(3):=&n;
        b:=&b; begin
    dbms_output.put_line('Enter c:');   Dbms_output.put_line('the fibinocci series is:'); 
        c:=&C;   while a<=n
if (a>b) and (a>c)     loop
    then         dbms_output.put_line(a);
    dbms_output.put_line('A is GREATEST'||A);         c:=a+b;
elsif (b>a) and (b>c)         a:=b;
    then         b:=c;
    dbms_output.put_line('B is GREATEST'||B);      end loop;
else   end;
    dbms_output.put_line('C is GREATEST'||C);  /
end if;
End; For Loop

Aim: To write a pl/sql program to check weather given DECLARE

number is Prime or not. a number(2);
BEGIN
Procedure: FOR a in 10 .. 20 LOOP
dbms_output.put_line('value of a: ' || a);
  declare END LOOP;
       num number; END;
       i number:=1; /
       c number:=0;
  begin DECLARE
        num:=&num; x NUMBER := 100;
       for i in 1..num BEGIN
       loop FOR i IN 1..10 LOOP
          if((mod(num,i))=0) IF MOD(i,2) = 0 THEN -- i is even
           then INSERT INTO temp VALUES (i, x, 'i is even');
              c:=c+1; ELSE
         end if; INSERT INTO temp VALUES (i, x, 'i is odd');
      end loop; END IF;
     if(c>2) x := x + 100;
     then END LOOP;
         dbms_output.put_line(num||' not a prime'); COMMIT;
     else END;
        dbms_output.put_line(num||' is prime');
     end if; Exit Statement
  end; DECLARE
   / x NUMBER := 0;
BEGIN
 LOOP a number(2) := 10;
DBMS_OUTPUT.PUT_LINE ('Inside loop: x = ' || BEGIN
TO_CHAR(x)); <<loopstart>>
x := x + 1; -- while loop execution
IF x > 3 THEN WHILE a < 20 LOOP
EXIT; dbms_output.put_line ('value of a: ' || a);
END IF; a := a + 1;
END LOOP; IF a = 15 THEN
-- After EXIT, control resumes here a := a + 1;
DBMS_OUTPUT.PUT_LINE(' After loop: x = ' || GOTO loopstart;
TO_CHAR(x)); END IF;
END; END LOOP;
/ END;
/
Exit When
DECLARE BEGIN
x NUMBER := 0;   GOTO label_1;
BEGIN   DBMS_OUTPUT.PUT_LINE('Right after the GOTO
LOOP statement');
DBMS_OUTPUT.PUT_LINE('Inside loop: x = ' ||   <<label_1>>
TO_CHAR(x));   DBMS_OUTPUT.PUT_LINE('It is here!');
x := x + 1; -- prevents infinite loop END;
EXIT WHEN x > 3; /
END LOOP;
-- After EXIT statement, control resumes here Q1: Write a PL/SQL code block to find sum and average
DBMS_OUTPUT.PUT_LINE('After loop: x = ' || of three numbers.
TO_CHAR(x)); declare
END; a number:=&a;
/ b number:=&b;
c number:=&c;
sm number;
While loop av number;
DECLARE begin
a number(2) := 10; sm:=a+b+c;
BEGIN av:=sm/3;
WHILE a < 20 LOOP dbms_output.put_line('Sum = '||sm);
dbms_output.put_line('value of a: ' || a); dbms_output.put_line('Average = '||av);
a := a + 1; end;
END LOOP;
END; Q2: Write a PL/SQL code block to find Simple Interest.
/ declare
p number(9,2);
r number(9,2);
DECLARE
t number(9,2);
  n_counter   NUMBER := 10;
si number(9,2);
  n_factorial NUMBER := 1;
begin
  n_temp      NUMBER;
p:=&p;
BEGIN
r:=&r;
  n_temp := n_counter;
t:=&t;
  WHILE n_counter > 0
si:=(p*r*t)/100;
  LOOP
dbms_output.put_line('Simple Interest = '||si);
    n_factorial := n_factorial * n_counter;
end;
    n_counter   := n_counter - 1;
  END LOOP;
Q3: Write a PL/SQL code block to find area of circles
 
with radius greater than 3 and less than equal to 7 and
  DBMS_OUTPUT.PUT_LINE('factorial of ' || n_temp ||
store the result in a table with attributes radius and
                       ' is ' || n_factorial);
area.
 END;
declare
/
area number(5,2);
radius number(1):=3;
GO TO
pi constant number(3,2):=3.14;
DECLARE begin
while radius<=7 b number:= 1;
loop c number;
area:=pi*radius*radius; begin
insert into areas values (radius,area); dbms_output.put(a||' '||b||' ');
radius:=radius+1; for i in 3..10 loop
end loop; c := a + b;
end; dbms_output.put(c||' ');
a := b;
Q4: Write a PL/SQL code block to find factorial of a b := c;
number. end loop;
declare dbms_output.put_line(' ');
n number; end;
i number;
f number:=1; Q8: Write a PL/SQL code block to find sum of digits of a
begin number.
n:=&n; declare
for i in 1..n N number ;
loop S number:=0;
f:=f*i; R number;
end loop; begin
dbms_output.put_line(n||'! = '||f); N:=&N;
end; WHILE N<>0 LOOP
R := MOD(N,10);
Q5: Write a PL/SQL code block to find reverse of a S := S + R;
number. N := TRUNC(N/10);
declare end loop;
N number; dbms_output.put_line('THE SUM OF THE DIGITS = '||S);
S NUMBER := 0; end;
R NUMBER;
K number; Sum of odd number between 1 to 100
begin Declare
N := &N; NUM number:=1;
K := N; Sum  number:=0;
loop begin
exit WHEN N = 0; loop
S := S * 10; NUM1 := NUM+2;
R := MOD(N,10); Sum:=Sum+Num1;
S := S + R; exit when NUM1=100;
N := TRUNC(N/10); end loop;
end loop; dbms_output.put_line (sum);
dbms_output.put_line('THE REVERSED DIGITS OF '||K||' end;
= '||S);
end; Write a Pl/Sql Program to implement Sum of First 100
Odd and Even Numbers
Q6: Write a PL/SQL code block to find greatest of three declare
numbers. odd number:=0;
declare even number:=0;
a number := &a; i number;
b number := &b; begin
c number := &c; for i in 1..100
begin loop
if a>b and a>c then if(i mod 2=0) then
dbms_output.put_line(a||' is greatest.'); even:=even+i;
elsif b>a and b>c then else
dbms_output.put_line(b||' is greatest.'); odd:=odd+i;
else end if;
dbms_output.put_line(c||' is greatest.'); end loop;
end if; dbms_output.put_line('the sum of 100 even no is'||even);
end; dbms_output.put_line('the sum of 100 odd no is'||odd);
end;
Q7: Write a PL/SQL code block to generate Fibonacci /
series.
declare How to Print Even Number beteen 1 to 100
a number:= 0 ; through SQL
Declare begin
NUM1 number:=0; num1:=&num1;
begin rev:=0;
loop while num1>0
NUM1 := NUM1+2; loop
dbms_output.put_line (NUM1||’,’); num2:=num1 mod 10;
exit when NUM1=100; rev:=num2+(rev*10);
end loop; num1:=floor(num1/10);
end; end loop;
dbms_output.put_line('Reverse number is: '||rev);
PL/SQL Program for Reverse of a Number end;

Aim - PL/SQL Program to reverse a string.

declare
str1 varchar2(30);
len number(3);
str2 varchar2(30);
declare i number(3);
    n number; begin
    i number; str1:='&str1';
    rev number:=0; len:=length(str1);
    r number; for i in reverse 1..len
 begin loop
    n:=&n; str2:=str2 || substr(str1,i,1);
        while n>0 end loop;
    loop dbms_output.put_line('Reverse string is: '||str2);
        r:=mod(n,10); end;
        rev:=(rev*10)+r;
        n:=trunc(n/10); Aim: To Write a pl/sql program to print mark list using
    end loop; cursors.
     dbms_output.put_line('reverse is '||rev);
 end; declare
/     stu student%rowtype;
    total number(4);
PL/SQL Program to Reverse a String     result varchar(4);
    cursor c is select * From student;
begin
  for stu in c
    loop
         Dbms_output.put_line('STUDENT MARKLIST');
         Dbms_output.put_line('----------------------------');
declare
         Dbms_output.put_line('sno:'||stu.sno||' sname:'||
    str1 varchar2(50):='&str';
stu.sname);
    str2 varchar2(50);
         total:=stu.m1+stu.m2+stu.m3;
    len number;
         if stu.M1>35 and stu.M2>35 and stu.M3>35
    i number;
         then
 begin
             result:='pass';
    len:=length(str1);
         else
    
             result:='fail';
    for i in reverse 1..len
         end if;
    loop
          Dbms_output.put_line('m1:'||stu.M1||' m2:'||
        str2:=str2 || substr(str1,i,1);
stu.M2||' m3:'||stu.M3);
    end loop;
          Dbms_output.put_line('total:'||total||' result:'||
        dbms_output.put_line('Reverse of String is:'||str2);
result);
end;
       end loop;
/
end;
/
Aim - PL/SQL Program to accept a number from user
and print number in reverse order. Record from Table
declare Aim: To Write a pl/sql program to to displaying top 10
num1 number(5); employee details based  on salary using cursors
num2 number(5); Source code:
rev number(5);    declare
        i number(2);         dbms_output.put_line('Grade:');
        E emp%rowtype;        case Grd
        cursor ec is select * from emp  order by salary desc;            when 'A' then Dbms_output.Put_line('Very Good');
    begin            when 'B' then Dbms_output.Put_line('Good');
         Dbms_output.put_line('Eno Ename   Job  Salary');            when 'C' then Dbms_output.Put_line('Avrage');
         Dbms_output.put_line('----------------------');           else
        i:=1;            Dbms_output.Put_line('Fail');
       open ec;         end case;
       loop        end;
         fetch ec into E;    /
            i:=i+1;
           Dbms_output.put_line(E.eno||' '||rpad(E.ename,8,' ')||' --EXAMPLE OF SQL%FOUND (IMPLICIT
'|| rpad(E.job,5,' ')||'  '||E.salary); CURSORS)
      exit when i>10 or ec%notfound;
     end loop; To Write a pl/sql program to display employees using
   close ec; parameterized cursor.
  end; Source code:
   /               declare
     n number;
ECLARE       er emp%rowtype;
     cursor c(d number) is
customer_rec customers%rowtype;      select * from emp where emp.eno=d;
begin
BEGIN     n:=&n;
SELECT * into customer_rec     open c(n);
    if c%isopen then
FROM customers       loop
WHERE id = 5;           fetch c into er;
          exit when c%notfound;
dbms_output.put_line('Customer ID: ' || customer_rec.id);              Dbms_output.put_line('Employee No:'||
er.eno
dbms_output.put_line('Customer Name: ' ||
              ||'Employee Name:'||er.ename);
customer_rec.name);
          end loop;
dbms_output.put_line('Customer Address: ' ||       else
customer_rec.address);        DBMS_OUTPUT.PUT_LINE('not found');
      end if;
dbms_output.put_line('Customer Salary: ' ||     close c;
customer_rec.salary); end;
END;   /

/ Aim: To Write a pl/sql program to update the commission

values for all employees with salary less than 2000 by
adding Rs.1000 to existing employees.
Aim:  To write a pl/sql program to display the employee Source code:
details   declare
SQL> declare        cursor c is select *From emp for update;
  2  E emp%rowtype;         emp_rec emp%rowtype;
  3  begin begin
  4  select * into E      for emp_rec in c
  5  from emp      loop
  6  where eno=100;           if emp_rec.salary<2000 then
  7  dbms_output.put_line('eno:'||E.eno);              update emp set comm=comm+1000
  8  dbms_output.put_line('ename:'||E.ename);              where current of c;
  9  end;            end if;
 10  /         end loop;
   end;
Aim: To Write a pl/sql program to calculate the student /
grade using case statement.
Aim: To Write a pl/sql program to delete employees whose
 declare experience  is less then 2   years.
          Grd student.grade%type;
     begin Source code:
        select grade into Grd from student declare
        where sno=100;     cursor c is select *From emp for update;
     emp_rec emp%rowtype; rows_affected := to_char(sql%rowcount);
begin if sql%rowcount > 0 then
  for emp_rec in c dbms_output.put_line(rows_affected || 'employee records
  loop modified successfully');
     if emp_rec.experience<2 then else
       delete emp dbms_output.put_line('There are no employees working as
       where current of c; programmers');
      end if; end if;
   end loop; end;
end;
/
DECLARE
Aim: To write a pl/sql program to displaying employee total_rows number(2);
details using cursors. BEGIN
Source code: UPDATE customers
SET salary = salary + 500;
   declare IF sql%notfound THEN
      no emp.eno%type; dbms_output.put_line('no customers selected');
      name emp.ename%type; ELSIF sql%found THEN
      cursor emp_cur is select eno,ename from emp; total_rows := sql%rowcount;
    begin dbms_output.put_line( total_rows || ' customers selected
       open emp_cur;           ');
       loop END IF;
         fetch emp_cur into no,name; END;
         exit when emp_cur%notfound;      /
         DBMS_output.put_line('Employee No:'||no||'Employee
Name:'||name);
       end loop;
      close emp_cur; BEGIN
    end; UPDATE emp_information SET
    / emp_dept='Web Developer'
WHERE emp_name='Saulin';
begin IF SQL%FOUND THEN
update employee set salary=salary *0.15 dbms_output.put_line('Updated - If
where emp_code = &emp_code; Found');
if sql%found then END IF;
dbms_output.put_line('employee record modified IF SQL%NOTFOUND THEN
successfully'); dbms_output.put_line('NOT Updated
else - If NOT Found');
dbms_output.put_line('employee no does not exist'); END IF;
end if; IF SQL%ROWCOUNT>0 THEN
end; dbms_output.put_line(SQL
%ROWCOUNT||' Rows Updated');
--EXAMPLE FOR SQL%NOTFOUND (IMPLICIT ELSE
CURSORS) dbms_output.put_line('NO Rows
begin Updated Found');
update employee set salary = salary*0.15 where emp_code END;
= &emp_code; /
if sql%notfound then
dbms_output.put_line('employee no . does not exist'); --EXPLICIT CURSOR EG
else
dbms_output.put_line('employee record modified DECLARE
successfully'); CURSOR c1 is SELECT * FROM emp;
end if; str_empno emp.empno%type;
end; str_ename emp.ename%type;
str_job emp.job%type;
--EXAMPLE FOR SQL%ROWCOUNT (IMPLICIT str_mgr emp.mgr%type;
CURSORS) str_hiredate emp.hiredate%type;
str_sal emp.sal%type;
declare str_comm emp.comm%type;
rows_affected char(4); str_deptno emp.deptno%type;
begin rno number;
update employee set salary = salary*0.15 where BEGIN
job='programmers'; rno := &rno;
FOR e_rec IN c1 BEGIN
LOOP dn := &deptno;
IF c1%rowcount = rno THEN OPEN ecur FOR SELECT * FROM emp WHERE
DBMS_OUTPUT.PUT_LINE (str_empno || ' ' deptno = dn;
|| str_ename || ' ' || str_job || ' ' || str_mgr || ' ' FOR e_rec IN ecur
|| str_hiredate || ' ' || str_sal || ' ' || str_comm || ' ' || LOOP
str_deptno); DBMS_OUTPUT.PUT_LINE ('Employee No : ' ||
END IF; e_rec.empno);
END LOOP; DBMS_OUTPUT.PUT_LINE ('Employee Salary: ' ||
END; e_rec.salary);
END LOOP;
--ANOTHER EG DISPLAYING VALUE OF A TABLE END;
DECLARE
CURSOR c1 IS SELECT * FROM emp; --REF WEAK CURSORS
e_rec emp%rowtype; DECLARE
BEGIN TYPE tcursor IS REF CURSOR;
OPEN c1; tcur tcursor;
LOOP e1 emp%ROWTYPE;
FETCH c1 INTO e_rec; d1 dept%ROWTYPE;
DBMS_OUTPUT.PUT_LINE('Number: ' || ' ' || tname VARCHAR2(20);
e_rec.empno); BEGIN
DBMS_OUTPUT.PUT_LINE('Name : ' || ' ' || e_rec.ename); tname := &tablename;
DBMS_OUTPUT.PUT_LINE('Salary: ' || ' ' || e_rec.sal); IF tname = 'emp' THEN
EXIT WHEN c1%NOTFOUND; OPEN tcur FOR SELECT * FORM emp;
END LOOP; DBMS_OUTPUT.PUT_LINE ('Emp table opened.');
CLOSE c1; close tcur;
END; DBMS_OUTPUT.PUT_LINE ('Emp table closed.');
ELSE IF tname = 'dept' THEN
-- Display details of Highest 10 salary paid employee OPEN tcur FOR SELECT * FROM dept;
DECLARE DBMS_OUTPUT.PUT_LINE ('Dept table opened.');
CURSOR c1 IS SELECT * FROM emp ORDER BY sal close tcur;
DESC; DBMS_OUTPUT.PUT_LINE ('Emp table closed.');
e_rec emp%rowtype; ELSE
BEGIN RAISE_APPLICATION_ERROR (-20004, 'Table name is
FOR e_rec IN c1 wrong');
LOOP END IF;
DBMS_OUTPUT.PUT_LINE('Number: ' || ' ' || END;
e_rec.empno);
DBMS_OUTPUT.PUT_LINE('Name : ' || ' ' || e_rec.ename); --CURSOR FOR LOOP WITH PARAMETERS
DBMS_OUTPUT.PUT_LINE('Salary: ' || ' ' || e_rec.sal); Declare
EXIT WHEN c1%ROWCOUNT >= 10; Cursor c1(Dno number) is select * from emp where deptno
END LOOP; = dno;
END; begin
for empree in c1(10) loop;
-- EXAMPLE OF CURSOR FOR LOOP dbms_output.put_line(empree.ename);
declare cursor c1 is select * from somdutt; end loop;
begin end;
for outvariable in c1 DECLARE
loop c_id customers.id%type;
exit when c1%notfound; c_name customerS.No.ame%type;
if outvariable.age < 21 then c_addr customers.address%type;
dbms_output.put_line(outvariable.age || ' ' || CURSOR c_customers is
outvariable.name); SELECT id, name, address FROM customers;
end if; BEGIN
end loop; OPEN c_customers;
end; LOOP
FETCH c_customers into c_id, c_name, c_addr;
--ref STRONG CURSORS EXIT WHEN c_customers%notfound;
DECLARE dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);
TYPE ecursor IS REF CURSOR RETURN emp END LOOP;
%ROWTYPE; CLOSE c_customers;
ecur ecursor; END;
e_rec emp%ROWTYPE;
dn NUMBER;
/ 4> emp_rec emp_cur%rowtype;
5> BEGIN
6> FOR emp_rec in sales_cur
1> DECLARE 7> LOOP
2> emp_rec emp_tbl%rowtype; 8> dbms_output.put_line(emp_cur.first_name || ' ' ||
3> CURSOR emp_cur IS emp_cur.last_name
4> SELECT * 9> || ' ' ||emp_cur.salary);
5> FROM 10> END LOOP;
6> WHERE salary > 10; 11>END;
7> BEGIN 12> /
8>  OPEN emp_cur;
9>  FETCH emp_cur INTO emp_rec; display all information about employees
10>     dbms_output.put_line (emp_rec.first_name || '  ' || create or replace PROCEDURE
emp_rec.last_name); get_employee_info_by_employee_id
11>  CLOSE emp_cur; (
12> END; p_employee_id NUMBER DEFAULT -1
)
Cursor with a Simple Loop: AS
1> DECLARE v_count NUMBER;
2> CURSOR emp_cur IS BEGIN
3> SELECT first_name, last_name, salary FROM SELECT COUNT(*)
emp_tbl; INTO v_count
4> emp_rec emp_cur%rowtype; FROM employee
5> BEGIN WHERE employee_id = p_employee_id;
6> IF NOT sales_cur%ISOPEN THEN
7> OPEN sales_cur; IF p_employee_id IS NULL THEN
8> END IF; DBMS_OUTPUT.PUT_LINE('Employee ID: ' ||
9> LOOP employee_id);
10> FETCH emp_cur INTO emp_rec; DBMS_OUTPUT.PUT_LINE('NAME: ' || name);
11> EXIT WHEN emp_cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE('EMAIL_ADDRESS: ' ||
12> dbms_output.put_line(emp_cur.first_name || ' ' || email_address);
emp_cur.last_name DBMS_OUTPUT.PUT_LINE('HIRE_DATE: ' ||
13> || ' ' ||emp_cur.salary); hire_date);
14> END LOOP; DBMS_OUTPUT.PUT_LINE('UPDATE_DATE: ' ||
15> END; update_date);
16> /
ELSE
SELECT COUNT(*)
Cursor with a While Loop:
INTO v_count
Lets modify the above program to use while loop.
FROM employee
1> DECLARE
WHERE employee_id = p_employee_id;
2> CURSOR emp_cur IS
END IF;
3> SELECT first_name, last_name, salary FROM emp_tbl;
END;
4> emp_rec emp_cur%rowtype;
5> BEGIN
6> IF NOT sales_cur%ISOPEN THEN Aim: To Write a pl/sql program to handle a predefined
7> OPEN sales_cur; exception.
8> END IF; Source code:
9> FETCH sales_cur INTO sales_rec;
10> WHILE sales_cur%FOUND THEN   declare
11> LOOP       n number(4);
12> dbms_output.put_line(emp_cur.first_name || ' ' ||       d number(4);
emp_cur.last_name   begin
13> || ' ' ||emp_cur.salary);       n:=&n;
15> FETCH sales_cur INTO sales_rec;       d:=n/0;
16> END LOOP;        EXCEPTION
17> END;          WHEN ZERO_DIVIDE THEN
18> /            DBMS_OUTPUT.PUT_LINE('Divide by error
exception is caught');
Let’s use the above example to learn how to use for loops in   end;
cursors.  /
1> DECLARE
2> CURSOR emp_cur IS
3> SELECT first_name, last_name, salary FROM emp_tbl;
Aim: To Write a pl/sql program to handle a user defined BEFORE DELETE OR INSERT OR UPDATE ON
exception. customers
Source code:
FOR EACH ROW
declare WHEN (NEW.ID > 0)
       others Exception; DECLARE
       n dept.dno%type; sal_diff number;
       name dept.dname%type; BEGIN
       n1 dept.dno%type; sal_diff := :NEW.salary - :OLD.salary;
 begin dbms_output.put_line('Old salary: ' || :OLD.salary);
       n1:=&dept_no; dbms_output.put_line('New salary: ' || :NEW.salary);
       select dno,dname into n,name dbms_output.put_line('Salary difference: ' || sal_diff);
       from dept END;
        where dno=n1; /
      if n<>n1 then
         raise others;
Create the price_history_trigger and execute it.
     else
        update dept set dname='&dname'
CREATE or REPLACE TRIGGER price_history_trigger
         where dno=n;
BEFORE UPDATE OF unit_price
         Dbms_output.put_line('dname updated.');
ON product
       end if;
FOR EACH ROW
    Exception
BEGIN
       when others then
INSERT INTO product_price_history
            Dbms_output.put_line('given dno is not
VALUES
found');
(:old.product_id,
     end;
:old.product_name,
    /
:old.supplier_name,
:old.unit_price);
Aim: To Write a pl/sql program for creating a procedure for END;
calculating /
          sum of two numbers. Lets update the price of a product.
Source code: UPDATE PRODUCT SET unit_price = 800 WHERE
create or replace procedure "SUM"(n1 IN product_id = 100
NUMBER,n2 IN NUMBER) is Once the above update query is executed, the trigger fires
total number(6); and updates the 'product_price_history' table.
begin This trigger execute BEFORE to convert ename field
total:=n1+n2; lowercase to uppercase.
  dbms_output.put_line('the sum is:'||total); CREATE or REPLACE TRIGGER trg1
end; BEFORE
INSERT ON emp1
Execution: FOR EACH ROW
     Method1: BEGIN
         SQL> exec sum(10,20); :new.ename := upper(:new.ename);
         the sum is:30
         END;
           PL/SQL procedure successfully completed.
/
   
      Method 2:
          SQL> begin This trigger is preventing to deleting row.
  2  sum(12,13);
  3  end; CREATE or REPLACE TRIGGER trg1
  4  / AFTER
the sum is:25 DELETE ON emp1
FOR EACH ROW
BEGIN
This trigger will display the salary difference between IF :old.eno = 1 THEN
the old values and new values raise_application_error(-20015, 'You
can't delete this row');
CREATE OR REPLACE TRIGGER END IF;
display_salary_changes END;
/
Example 9-1 Trigger Uses Conditional Predicates to IF x > y THEN
Detect Triggering Statement z:= x;
ELSE
CREATE OR REPLACE TRIGGER t Z:= y;
BEFORE END IF;
INSERT OR RETURN z;
UPDATE OF salary, department_id OR END;
DELETE BEGIN
ON employees a:= 23;
BEGIN b:= 45;
CASE c := findMax(a, b);
WHEN INSERTING THEN dbms_output.put_line(' Maximum of (23,45): ' || c);
DBMS_OUTPUT.PUT_LINE('Inserting'); END;
WHEN UPDATING('salary') THEN /
DBMS_OUTPUT.PUT_LINE('Updating salary');
WHEN UPDATING('department_id') THEN
DBMS_OUTPUT.PUT_LINE('Updating department
ID'); the following program calculates the factorial of a given
WHEN DELETING THEN number by calling itself recursively −
DBMS_OUTPUT.PUT_LINE('Deleting');
END CASE; DECLARE
END; num number;
/ factorial number;

FUNCTION fact(x number)

RETURN number
to create and call a standalone function IS
f number;
CREATE OR REPLACE FUNCTION totalCustomers BEGIN
RETURN number IS IF x=0 THEN
total number(2) := 0; f := 1;
BEGIN ELSE
SELECT count(*) into total f := x * fact(x-1);
FROM customers; END IF;
RETURN f;
RETURN total; END;
END;
/ BEGIN
num:= 6;
Calling function factorial := fact(num);
DECLARE dbms_output.put_line(' Factorial '|| num || ' is ' || factorial);
c number(2); END;
BEGIN /
c := totalCustomers(); This program finds the minimum of two values
dbms_output.put_line('Total no. of Customers: ' || c);
END;
DECLARE
/
a number;
b number;
c number;
example demonstrates Declaring, Defining, and PROCEDURE findMin(x IN number, y IN number, z OUT
Invoking a Simple PL/SQL Function that computes and number) IS
returns the maximum of two values. BEGIN
IF x < y THEN
DECLARE z:= x;
a number; ELSE
b number; z:= y;
c number; END IF;
FUNCTION findMax(x IN number, y IN number) END;
RETURN number BEGIN
IS a:= 23;
z number; b:= 45;
BEGIN findMin(a, b, c);
 dbms_output.put_line(' Minimum of (23, 45) : ' || c);
END;
/
computes the square of value of a passed value
DECLARE
a number;
PROCEDURE squareNum(x IN OUT number) IS
BEGIN
x := x * x;
END;
BEGIN
a:= 23;
squareNum(a);
dbms_output.put_line(' Square of (23): ' || a);
END;
/

The below example creates a procedure

‘employer_details’ which gives the details of the
employee.
1> CREATE OR REPLACE PROCEDURE
employer_details
2> IS
3> CURSOR emp_cur IS
4> SELECT first_name, last_name, salary FROM emp_tbl;
5> emp_rec emp_cur%rowtype;
6> BEGIN
7> FOR emp_rec in sales_cur
8> LOOP
9> dbms_output.put_line(emp_cur.first_name || ' ' ||
emp_cur.last_name
10> || ' ' ||emp_cur.salary);
11> END LOOP;
12>END;
13> /

How to execute a Stored Procedure?

There are two ways to execute a procedure.

1) From the SQL prompt.

EXECUTE [or EXEC] procedure_name;
2) Within another procedure – simply use the procedure
name.

procedure_name;