Join the OracleApps88 Telegram group @OracleApps88to get more information on Oracle EBS R12/Oracle Fusion applications.

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.

Tuesday, December 20, 2016

Oracle PL/SQL Programs

PL SQL Program For to check the given number is armstrong or not.
DECLARE
   n   NUMBER (3);
   s   NUMBER (3) := 0;
   t   NUMBER (3);
BEGIN
   n := &n;
   t := n;

   WHILE t > 0
   LOOP
      s := s + POWER ((t MOD 10), 3);
      t := TRUNC (t / 10);
   END LOOP;

   IF (s = n)
   THEN
      DBMS_OUTPUT.put_line ('The given number ' || n
                            || 'is an armstrong number'
                           );
   ELSE
      DBMS_OUTPUT.put_line (   'The given number '
                            || n
                            || 'is not an armstrong number'
                           );
   END IF;
END;

PL SQL Program For to find biggest no.
DECLARE
   a   NUMBER (2);
   b   NUMBER (2);
   c   NUMBER (2);
BEGIN
   a := &a;
   b := &b;
   c := &c;

   IF (a < b)
   THEN
      IF (b < c)
      THEN
         DBMS_OUTPUT.put_line (c || ' is the biggest number');
      ELSE
         DBMS_OUTPUT.put_line (b || ' is the biggest number');
      END IF;
   ELSE
      IF (a < c)
      THEN
         DBMS_OUTPUT.put_line (c || ' is the biggest number');
      ELSE
         DBMS_OUTPUT.put_line (a || ' is the biggest number');
      END IF;
   END IF;
END;

PL SQL Program For counting record using trigger.
CREATE OR REPLACE TRIGGER counts
   AFTER INSERT
   ON tristudent
DECLARE
   c   interger;
BEGIN
   SELECT COUNT (*)
     INTO c
     FROM tristudent;

   DBMS_OUTPUT.put_line ('total records : ' || c);
END;

PL SQL Program For using Cursor.
DECLARE
   CURSOR c
   IS
      SELECT NAME, job, salary
        FROM employee;

   empname   employee.NAME%TYPE;
   jobs      employee.job%TYPE;
   sal       employee.salary%TYPE;
BEGIN
   OPEN c;

   DBMS_OUTPUT.put_line (' NAME DESIGNATION SALARY');

   LOOP
      FETCH c
       INTO empname, jobs, sal;

      EXIT WHEN c%NOTFOUND;
      DBMS_OUTPUT.put_line (empname || ' ' || jobs || ' ' || sal);
   END LOOP;

   CLOSE c;
END;

PL SQL Program For Pre Defined Error.
DECLARE
   a   NUMBER (3);
   b   NUMBER (3);
   c   NUMBER (3);
BEGIN
   a := &a;
   b := &b;
   c := a / b;
   DBMS_OUTPUT.put_line ('The Value of ' || a || ' / ' || b || ' is ' || c);
EXCEPTION
   WHEN ZERO_DIVIDE
   THEN
      DBMS_OUTPUT.put_line ('DIVIDE BY ZERO ERROR');
END;

PL SQL Program For handle user defined error.
DECLARE
   a              NUMBER (3);
   b              NUMBER (3);
   c              NUMBER (3);
   divide_error   EXCEPTION;
BEGIN
   a := &a;
   b := &b;

   IF (b = 0)
   THEN
      RAISE divide_error;
   END IF;

   c := a / b;
   DBMS_OUTPUT.put_line ('The Value of ' || a || ' / ' || b || ' is ' || c);
EXCEPTION
   WHEN divide_error
   THEN
      DBMS_OUTPUT.put_line ('A Number can not be divide by zero');
END;

PL SQL Program For EB AMOUNT CALCULATION.
CREATE OR REPLACE TRIGGER calc
   AFTER INSERT OR UPDATE OR DELETE
   ON ebfirst
   FOR EACH ROW
DECLARE
   unit   NUMBER (3);
   amt    NUMBER (8, 3);
BEGIN
   IF INSERTING
   THEN
      unit := :NEW.cur - :NEW.prev;

      IF (unit >= 500)
      THEN
         amt := unit * 5;
      ELSIF (unit >= 400)
      THEN
         amt := unit * 4;
      ELSIF (unit >= 300)
      THEN
         amt := unit * 3;
      ELSIF (unit > 50)
      THEN
         amt := unit * 2;
      ELSE
         amt := 50;
      END IF;

      INSERT INTO ebsecond
           VALUES (:NEW.cusno, :NEW.NAME, unit, amt);

      DBMS_OUTPUT.put_line (   :NEW.cusno
                            || ' '
                            || :NEW.NAME
                            || ' '
                            || unit
                            || ' '
                            || amt
                           );
      DBMS_OUTPUT.put_line ('amt calculated and records inserted');
   END IF;

   IF UPDATING
   THEN
      unit := :NEW.cur - :NEW.prev;
      amt := unit * 2.5;

      UPDATE ebsecond
         SET units = unit,
             amount = amt
       WHERE cusno = :OLD.cusno;

      DBMS_OUTPUT.put_line (   :OLD.cusno
                            || ' '
                            || :OLD.NAME
                            || ' '
                            || unit
                            || ' '
                            || amt
                           );
      DBMS_OUTPUT.put_line ('amt calculated and records updated');
   END IF;

   IF DELETING
   THEN
      DELETE FROM ebsecond
            WHERE cusno = :OLD.cusno;

      DBMS_OUTPUT.put_line (' Record ' || :OLD.cusno
                            || ' deleted successfully'
                           );
   END IF;
END;

PL SQL Program For TO RETRIEVE THE RECORDS OF THE EMPLOYEE.
DECLARE
   NO     emp.empno%TYPE;
   NAME   emp.ename%TYPE;
   sal    emp.sal%TYPE;
BEGIN
   NO := &no;

   SELECT ename, sal
     INTO NAME, sal
     FROM emp
    WHERE empno = NO;

   DBMS_OUTPUT.put_line (' DETAILS OF THE EMPLOYEE WHOSE EMPLOYEE NO IS '
                         || NO
                        );
   DBMS_OUTPUT.put_line ('Name of the Employee : ' || NAME);
   DBMS_OUTPUT.put_line ('Salary of the Employee : ' || sal);
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      DBMS_OUTPUT.put_line ('The Employee Number ' || NO || ' Not Found');
END;

PL SQL Program For TO COMPUTE FACTORIAL.
DECLARE
   n   NUMBER;
   i   NUMBER;
   p   NUMBER := 1;
BEGIN
   n := &n;

   FOR i IN 1 .. n
   LOOP
      p := p * i;
   END LOOP;

   DBMS_OUTPUT.put_line (n || ' ! = ' || p);
END;

PL SQL Program For function using exception.
CREATE OR REPLACE FUNCTION stinfo (NO IN NUMBER)
   RETURN VARCHAR2
IS
   snam   VARCHAR2 (30);
BEGIN
   SELECT NAME
     INTO snam
     FROM student
    WHERE stuid = NO;

   RETURN snam;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      RETURN (' Record does not exist ');
END;

PL SQL Program For function to return the grade of the student.
CREATE OR REPLACE FUNCTION ret_grade (NO IN NUMBER)
   RETURN VARCHAR2
IS
   grade     VARCHAR2 (20);
   mark1     INTEGER;
   mark2     INTEGER;
   mark3     INTEGER;
   average   NUMBER (5, 2);
BEGIN
   SELECT m1, m2, m3
     INTO mark1, mark2, mark3
     FROM student
    WHERE stuid = NO;

   average := (mark1 + mark2 + mark3) / 3;

   IF (mark1 < 50 OR mark2 < 50 OR mark3 < 50)
   THEN
      RETURN ('FAIL');
   ELSIF (average >= 90)
   THEN
      RETURN ('A+');
   ELSIF (average >= 80)
   THEN
      RETURN ('A');
   ELSIF (average >= 70)
   THEN
      RETURN ('B');
   ELSIF (average >= 60)
   THEN
      RETURN ('C');
   ELSE
      RETURN ('D');
   END IF;
END;

PL SQL Program For function to return the grade of the student using cursor.
CREATE OR REPLACE FUNCTION gradeall
   RETURN VARCHAR2
IS
   CURSOR c
   IS
      SELECT m1, m2, m3
        FROM student;

   mark1     INTEGER;
   mark2     INTEGER;
   mark3     INTEGER;
   average   NUMBER (5, 2);
BEGIN
   OPEN c;

   LOOP
      FETCH c
       INTO mark1, mark2, mark3;

      EXIT WHEN c%NOTFOUND;
      average := (mark1 + mark2 + mark3) / 3;

      IF (mark1 < 50 OR mark2 < 50 OR mark3 < 50)
      THEN
         RETURN ('FAIL');
      ELSIF (average >= 90)
      THEN
         RETURN ('A+');
      ELSIF (average >= 80)
      THEN
         RETURN ('A');
      ELSIF (average >= 70)
      THEN
         RETURN ('B');
      ELSIF (average >= 60)
      THEN
         RETURN ('C');
      ELSE
         RETURN ('D');
      END IF;
   END LOOP;

   CLOSE c;
END;

PL SQL Program For adding total using funtion.
CREATE OR REPLACE FUNCTION fn (NO IN NUMBER)
   RETURN VARCHAR2
IS
   msg   VARCHAR2 (19);

   CURSOR c1
   IS
      SELECT m1, m2, m3
        FROM student
       WHERE stuid = NO;

   er    c1%ROWTYPE;
BEGIN
   FOR er IN c1
   LOOP
      RETURN ('total scored' || (c1.m1 + c1.m2 + c1.m3));
   END LOOP;
END;

PL SQL Program For TO RETRIEVE THE NAME OF THE EMPLOYEE.
CREATE OR REPLACE FUNCTION return_name (NO NUMBER)
   RETURN VARCHAR2
IS
   nam   VARCHAR2 (25);
BEGIN
   SELECT NAME
     INTO nam
     FROM employee
    WHERE empno = NO;

   RETURN nam;
END;

PL SQL Program For invoice.
DECLARE
   NO        invoices.invno%TYPE;
   actsale   invoices.actualsales%TYPE;
   ts        invoices.targetsales%TYPE;
   err       EXCEPTION;
BEGIN
   NO := &no;
   actsale := &actsale;
   ts := &ts;

   IF (actsale - ts <= 0)
   THEN
      RAISE err;
   ELSE
      INSERT INTO invoices
                  (invno, actualsales, targetsales
                  )
           VALUES (NO, actsale, ts
                  );

      DBMS_OUTPUT.put_line (   'Commission '
                            || (actsale - ts) * .25
                            || ' paid and record added sucessfully'
                           );
   END IF;
EXCEPTION
   WHEN err
   THEN
      DBMS_OUTPUT.put_line
                          ('ACTUAL SALES SHOULD BE GREATER THAN TARGET SALES');
END;

PL SQL Program For to find the person whose getting maximum salary using cursor.
DECLARE
   CURSOR c
   IS
      SELECT NAME, job, salary
        FROM employee;

   empname   employee.NAME%TYPE;
   jobs      employee.job%TYPE;
   sal       employee.salary%TYPE;
   maxname   employee.NAME%TYPE     := 0;
   maxjob    employee.job%TYPE      := 0;
   maxsal    employee.salary%TYPE   := 0;
BEGIN
   OPEN c;

   FETCH c
    INTO empname, jobs, sal;

   LOOP
      IF maxsal = 5000
      THEN
         maxsal := sal;
         maxjob := jobs;
         maxname := empname;
      END IF;

      EXIT WHEN c%NOTFOUND;
   END LOOP;

   DBMS_OUTPUT.put_line ('DETAILS OF THE PERSON GETTING MAXIMUM SALARY');
   DBMS_OUTPUT.put_line ('EMPLOYEE NAME : ' || maxname);
   DBMS_OUTPUT.put_line ('DESIGNATION : ' || maxjob);
   DBMS_OUTPUT.put_line ('SALARY : ' || maxsal);

   CLOSE c;
END;

PL SQL Program For to generate multiplication table.
DECLARE
   i   NUMBER (2);
   n   NUMBER (2);
BEGIN
   n := &n;

   FOR i IN 1 .. 10
   LOOP
      DBMS_OUTPUT.put_line (n || ' * ' || i || ' = ' || n * i);
   END LOOP;
END;

PL SQL Program For to store odd date and even date records on the separate table.
DECLARE
   ID        invoice.invno%TYPE;
   movieno   invoice.mvno%TYPE;
   cid       invoice.cusid%TYPE;
   dat1      invoice.issdate%TYPE;
   dat2      invoice.retdate%TYPE;
   t         INTEGER;

   CURSOR c
   IS
      SELECT *
        FROM invoice;
BEGIN
   OPEN c;

   DELETE FROM odd;

   DELETE FROM even;

   LOOP
      FETCH c
       INTO ID, movieno, cid, dat1, dat2;

      EXIT WHEN c%NOTFOUND;
      t := TO_CHAR (dat1, 'dd');

      IF t MOD 2 = 0
      THEN
         INSERT INTO even
                     (ID, mvnum, cid, issue, RETURN
                     )
              VALUES (ID, movieno, cid, dat1, dat2
                     );
      ELSE
         INSERT INTO odd
                     (ID, mvnum, cid, issue, RETURN
                     )
              VALUES (ID, movieno, cid, dat1, dat2
                     );
      END IF;
   END LOOP;

   CLOSE c;
END;

PL SQL Program For Preating Package.
CREATE OR REPLACE PACKAGE prcpack
IS
   PROCEDURE ins (NO IN NUMBER, nam IN VARCHAR2);

   PROCEDURE delt (NO IN NUMBER);

   PROCEDURE updat (NO NUMBER, nam VARCHAR2);
END;

PL SQL Program For to insert records using procedures.
CREATE OR REPLACE PACKAGE BODY prcpack
IS
   PROCEDURE ins (NO IN NUMBER, nam IN VARCHAR2)
   IS
   BEGIN
      INSERT INTO employ
           VALUES (NO, nam);

      DBMS_OUTPUT.put_line (' Record inserted successfully');
   END ins;

   PROCEDURE delt (NO IN NUMBER)
   IS
   BEGIN
      DELETE FROM employ
            WHERE empid = NO;

      DBMS_OUTPUT.put_line (' Record ' || NO || ' deleted succesfullly');
   END delt;

   PROCEDURE updat (NO IN NUMBER, nam VARCHAR2)
   IS
   BEGIN
      UPDATE employ
         SET empname = nam
       WHERE empid = NO;

      DBMS_OUTPUT.put_line (' Record ' || NO || ' updated succesfullly');
   END updat;
END;


PL/SQL PROGRAM FOR TO DELETE RECORDS USING PROCEDURE.
CREATE OR REPLACE PROCEDURE prcupdate (ID IN NUMBER, nam IN VARCHAR2)
IS
   ROW   employ%ROWTYPE;
BEGIN
   DBMS_OUTPUT.put_line (' Before Updation');

   SELECT *
     INTO ROW
     FROM employ
    WHERE empid = ID;

   DBMS_OUTPUT.put_line ('EMP ID : ' || ROW.empid || ' EMPNAME : '
                         || ROW.empname
                        );

   UPDATE employ
      SET empname = nam
    WHERE empid = ID;

   COMMIT;
   DBMS_OUTPUT.put_line ('Updated Record');

   SELECT *
     INTO ROW
     FROM employ
    WHERE empid = ID;

   DBMS_OUTPUT.put_line ('EMP ID : ' || ROW.empid || ' EMPNAME : '
                         || ROW.empname
                        );
END;


PL SQL Program For adam or not.
DECLARE
   n      NUMBER (3);
   t1     NUMBER (3) := 0;
   t2     NUMBER (3);
   s      NUMBER (3) := 0;
   revn   NUMBER (3) := 0;
   t      NUMBER (3);
BEGIN
   n := &n;
   t := n;

   WHILE (t > 0)
   LOOP
      revn := (revn * 10) + (t MOD 10);
      t := TRUNC (t / 10);
   END LOOP;

   DBMS_OUTPUT.put_line ('Reversal of the number is ' || revn);
   t1 := n * n;
   t2 := revn * revn;
   t := t2;

   WHILE (t > 0)
   LOOP
      s := (s * 10) + (t MOD 10);
      t := TRUNC (t / 10);
   END LOOP;

   IF (s = t1)
   THEN
      DBMS_OUTPUT.put_line ('The Given Number ' || n || ' is an Adam Number');
   ELSE
      DBMS_OUTPUT.put_line ('The Given Number ' || n
                            || ' is not an Adam Number'
                           );
   END IF;
END;

PL SQL Program For to add two numbers.
DECLARE
   a   NUMBER (2);
   b   NUMBER (2);
   c   NUMBER (2);
BEGIN
   a := &a;
   b := &b;
   c := a + b;
   DBMS_OUTPUT.put_line (a || ' + ' || b || ' = ' || c);
END;


Best Blogger TemplatesBest Blogger Tips
Best Blogger Gadgets
Posted by Raju Chinthapatla at 11:07:00 PM
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)
If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.
Best Blogger TipsGet Flower Effect