PL/SQL SIMPLE PROGRAMS

1) PL/SQL PROGRAM TO CUALCULATE THE NET SALARY IF DA IS 30% OF BASIC,HRA IS 10% OF BASIC AND PF IS 7% IF BASIC SALARY IS LESS THAN 8000 ,PF IS 10% IF BASIC SAL BETEEN 8000 TO 160000

declare
    ename varchar2(15);
    basic number;
    da number;
    hra number;
    pf number;
    netsalary number;
begin
    ename:='&ename';
    basic:=&basic;
    da:=basic * (30/100);
    hra:=basic * (10/100);
        if (basic < 8000)
    then
        pf:=basic * (8/100);
        elsif (basic >= 8000 and basic <= 16000)
    then
        pf:=basic * (10/100);
           
end if;
        netsalary:=basic + da + hra -pf;

dbms_output.put_line('Employee name : ' || ename);
dbms_output.put_line('Providend Fund : ' || pf);
dbms_output.put_line('Net salary : ' || netsalary);
end;

2) Looping(Print Even Number 1 to 100)
Declare
NUM1 number:=0;
begin
loop
NUM1 := NUM1+2;
dbms_output.put_line (NUM1||',');
exit when NUM1=100;
end loop;
end;

3) Find the greatest among three numbers
Declare
    a number;
    b number;
    c number;
Begin
    dbms_output.put_line('Enter a:');
        a:=&a;
    dbms_output.put_line('Enter b:');
        b:=&b;
    dbms_output.put_line('Enter c:');
        c:=&C;
if (a>b) and (a>c)
    then
    dbms_output.put_line('A is GREATEST'||A);
elsif (b>a) and (b>c)
    then
    dbms_output.put_line('B is GREATEST'||B);
else
    dbms_output.put_line('C is GREATEST'||C);
end if;
End;

4) Print Odd numbers between 1 to 100
Declare
NUM1 number:=1;
begin
loop
NUM1 := NUM1+2;
dbms_output.put_line (NUM1||',');
exit when NUM1=100;
end loop;
end;

5) Sum of number between 1 to 100
Declare
a number;
sum1 number :=0;
Begin
a:=1;
loop
sum1:=sum1+a;
exit when (a=100);
a:=a+1;
end loop;
dbms_output.put_line('Sum between 1 to 100 is '||sum1);
End;

6) Sum of odd number between 1 to 100
Declare
NUM number:=1;
Sum  number:=0;
begin
loop
NUM1 := NUM+2;
Sum:=Sum+Num1;
exit when NUM1=100;
end loop;
dbms_output.put_line (sum);
end;

7) Simple Cursor Example
Select Record of emp table with Cursor
DECLARE
   CURSOR c_emp IS
      SELECT *
        FROM Scott.emp
       WHERE sal< 5000;
BEGIN
   FOR i IN c_emp
   LOOP
      dbms_output.put_line(i.sal);
insert into e1 values (i.sal);
   END LOOP;
END;

8) Using Cursor Select the Common deptno In Emp and Dept Table
DECLARE
   CURSOR c_emp IS
      SELECT *
        FROM emp;
cursor c_dept is select * from dept;
BEGIN
   FOR i IN c_emp
   LOOP
for j in c_dept
   loop
   if(i.deptno=j.deptno) then
      dbms_output.put_line(i.deptno);
end if;
end loop;
   END LOOP;
END;

9) Find the area and perimeter of circle
Declare
    pi constant number(3,2) := 3.14;
    radius number(7);
    area number(13,2);
    perimeter number(13,2);
Begin
    radius := &n;
    perimeter:=2*pi*radius;
    area := pi* power(radius,2);
DBMS_OUTPUT.PUT_LINE('Area'||area||' '||'perimeter'||perimeter);
end;

10) This example shows how to use Comosite type
DECLARE
      CURSOR myEmpCursor IS
       SELECT empno, ename
         FROM emp
        ORDER BY empno;
 
       myID     emp.empno%TYPE;
       myName   emp.ename%TYPE;
    BEGIN
 
   OPEN myEmpCursor;
 
   
 
  LOOP
       FETCH myEmpCursor INTO myID, myName;
 
        EXIT WHEN myEmpCursor%NOTFOUND;
 
        DBMS_OUTPUT.PUT_LINE(myID||' '||myName);
        END LOOP;
        CLOSE myEmpCursor;
   END;

11) This example shows how to use Comosite Rowtype in pl/sql
declare
    e_name emp%rowtype;
cursor emp_cur is select * from emp;
begin
    for i in emp_cur
loop
    dbms_output.put_line(i.ename||','||i.job);
end loop;
end;

12) Composite Record Type
DECLARE
    TYPE emp_record_type is RECORD
        (name   char(20),
         sal         number(10),
         hiredate        date);
   emp_rec    emp_record_type;
BEGIN
    select ename, sal,hire_date
    into emp_record
    from Scott.emp
    where employee_id = 1047;
         dbms_output.put_line(emp_rec.name||'        '||
         emp_rec.sal||'       '||
         emp_rec.hiredate);
  END;

13) Insert Update Delete in a Table in PL/SQL
DECLARE
    v_sal emp.sal%TYPE;
BEGIN

    INSERT INTO emp VALUES (6, 'Tomcat', 1000);

    UPDATE emp SET sal = sal + 5000 WHERE empno = 6;

      SELECT sal INTO v_sal FROM employee WHERE empno = 6;

    DBMS_OUTPUT.PUT_LINE('Salary increased to ' || v_sal);

    DELETE FROM emp WHERE empno = 6;

      COMMIT;
END;