PL SQL 20/12/22

Print Hello
BEGIN
   DBMS_OUTPUT.put_line('Hello');
   END;
   /
SUM OF Two numbers
SQL> DECLARE
  2  a INTEGER;
  3  b INTEGER;
  4  c INTEGER;
  5  BEGIN
  6     a:=&a;
  7     b:=&b;
  8     c:=a+b;
  9     DBMS_OUTPUT.put_line('The Sum is : '||c);
 10  END;
 11  /
Enter VALUE FOR a: 3
old   6:    a:=&a;
NEW   6:    a:=3;
Enter VALUE FOR b: 5
old   7:    b:=&b;
NEW   7:    b:=5;
The SUM IS : 8

Volume OF a cube
SQL> DECLARE
  2  len INTEGER := &len;
  3  bre INTEGER := &bre;
  4  hei INTEGER := &hei;
  5  vol INTEGER;
  6  BEGIN
  7     vol:=len*bre*hei;
  8     DBMS_OUTPUT.put_line('The volume is '||vol);
  9  END;
 10  /
Enter VALUE FOR len: 3
old   2: len INTEGER := &len;
NEW   2: len INTEGER := 3;
Enter VALUE FOR bre: 4
old   3: bre INTEGER := &bre;
NEW   3: bre INTEGER := 4;
Enter VALUE FOR hei: 5
old   4: hei INTEGER := &hei;
NEW   4: hei INTEGER := 5;
The volume IS 60

PL/SQL PROCEDURE successfully completed.

factorial
SQL> DECLARE
  2  n INTEGER :=&n;
  3  i INTEGER;
  4  fact INTEGER :=1;
  5  BEGIN
  6     FOR i IN 1 .. n
  7         LOOP
  8         fact:=fact*i;
  9         END LOOP;
 10     DBMS_OUTPUT.put_line('The Factorial of '||n||' is '||fact);
 11  END;
 12  /
Enter VALUE FOR n: 5
old   2: n INTEGER :=&n;
NEW   2: n INTEGER :=5;
The Factorial OF 5 IS 120

PL/SQL PROCEDURE successfully completed.

REVERSE OF a String
SQL> DECLARE
  2   str1 VARCHAR2(50):='&str';
  3   str2 VARCHAR(50);
  4   len NUMBER;
  5   i NUMBER;
  6  BEGIN
  7     len:=LENGTH(str1);
  8     FOR i IN REVERSE 1..len
  9     LOOP
 10         str2:=str2||SUBSTR(str1,i,1);
 11     END LOOP;
 12  DBMS_OUTPUT.put_line('Reverse of the String is : '||str2);
 13  END;
 14  /
Enter VALUE FOR str: Backspace
old   2:  str1 VARCHAR2(50):='&str';
NEW   2:  str1 VARCHAR2(50):='Backspace';
REVERSE OF the String IS : ecapskcaB

PL/SQL PROCEDURE successfully completed.

prime NUMBER

DECLARE
  2  i NUMBER(3);
  3  j NUMBER(3);
  4  BEGIN
  5  DBMS_OUTPUT.put_line('The Prime Numbers are : ');
  6  DBMS_OUTPUT.new_line;
  7  i:=2;
  8  LOOP
  9   j:=2;
 10   LOOP
 11     EXIT WHEN((MOD(i,j)=0) OR (j=i));
 12     j:=j+1;
 13  END LOOP;
 15  IF(j=i)THEN
 16     DBMS_OUTPUT.put(i||'  ');
 17  END IF;
 18  i:= i+1;
 19  EXIT WHEN i=100;
 20  END LOOP;
 21  DBMS_OUTPUT.new_line;
 22  END;
 23  /
The Prime Numbers are :
2  3  5  7  11  13  17  19  23  29  31  37  41  43  47  53  59  61  67  71  73
79  83  89  97

PL/SQL PROCEDURE successfully completed.

REVERSE OF NUMBER
SQL> DECLARE
  2  num NUMBER:=#
  3  rev NUMBER;
  4  BEGIN
  5  rev:=0;
  6  WHILE num>0 LOOP
  7  rev:=(rev*10)+MOD(num,10);
  8  num:=FLOOR(num/10);
  9  END LOOP;
 10  DBMS_OUTPUT.put_line('Reverse is : '||rev);
 11  END;
 12  /
Enter VALUE FOR num: 264431
old   2: num NUMBER:=#
NEW   2: num NUMBER:=264431;
REVERSE IS : 134462

PL/SQL PROCEDURE successfully completed.

ARRAY

DECLARE
  2  TYPE namesarray IS varray(5) OF VARCHAR2(10);
  3  TYPE grades IS varray(5) OF INTEGER;
  4  names namesarray;
  5  marks grades;
  6  total INTEGER;
  7  BEGIN
  8  names namesarray;
  9  .
SQL>  DECLARE
  2  TYPE namesarray IS varray(5) OF VARCHAR2(10);
  3  TYPE grades IS varray(5) OF INTEGER;
  4  names namesarray;
  5  marks grades;
  6  total INTEGER;
  7  BEGIN
  8  names := namesarray('Kavitha','Priyam','Ayan','Rishav','Aziz');
  9  marks:=grades(98,97,78,87,92);
 10  total:=names.COUNT;
 11  DBMS_OUTPUT.put_line('Total '||total||' Students');
 12  FOR i IN 1..total LOOP
 13     DBMS_OUTPUT.put_line('Student: '||names(i)||' Marks: '||marks(i));
 14     END LOOP;
 15  END;
 16  /
Total 5 Students
Student: Kavitha Marks: 98
Student: Priyam Marks: 97
Student: Ayan Marks: 78
Student: Rishav Marks: 87
Student: Aziz Marks: 92

PL/SQL PROCEDURE successfully completed.