Thursday, June 18, 2015

PLSQL Programmes


  1. Write a Program to print the following format 
    WELCOME TO PL/SQL PROGRAMMING
BEGIN
DBMS_OUTPUT.PUT_LINE('WELCOME TO PL/SQL PROGRAMMING');
END;
  2. Write a program to print the numbers from 1 to 100 
    DECLARE
N NUMBER(3):=1;
V VARCHAR2(1000);
BEGIN
WHILE N <=100
LOOP
V:=V||''||N;
N:=N+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
END;
  3. write a program to print the 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;
  4. Write a program to print the odd numbers from 1 to 100 
    DECLARE
N NUMBER(3):=1;
BEGIN
WHILE N <=100
LOOP
N:=N+2;
DBMS_OUTPUT.PUT_LINE(N);
END LOOP;
END;
  5. write a program for multiplication table 
    DECLARE
A NUMBER(2):=&A;
B NUMBER(2):=1;
C NUMBER(3);
BEGIN
WHILE B <=10
LOOP
C:=A*B;
DBMS_OUTPUT.PUT_LINE(A||'*'||B||'='||C);
B:=B+1;
END LOOP;
END;
  6. write a program to find the sum of 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 OF 1 TO 100 IS '||S);
END;
  7. Write a program to find the sum of all odd numbers from 1 to 100 
    DECLARE
N NUMBER(3):=1;
S NUMBER(4):=0;
BEGIN
WHILE N <=100
LOOP
S:=S+N;
N:=N+2;
END LOOP;
DBMS_OUTPUT.PUT_LINE('THE SUM OF 1 TO 100 ODD NUMBERS IS '||S);
END;
  8. Write a program to find the sum of all even numbers from 1 to 100 
    DECLARE
N NUMBER(3):=0;
S NUMBER(4):=0;
BEGIN
WHILE N <=100
LOOP
S:=S+N;
N:=N+2;
END LOOP;
DBMS_OUTPUT.PUT_LINE('THE SUM OF 1 TO 100 EVEN NUMBERS IS '||S);
END;
  9. Write a program to accept a number and find how many digits it contain 
    DECLARE
N NUMBER(5):=&N;
CNT NUMBER:=0;
R NUMBER(2):=0;
BEGIN
WHILE N !=0
LOOP
R:=MOD(N,10);
CNT:=CNT+1;
N:=TRUNC(N/10);
END LOOP;
DBMS_OUTPUT.PUT_LINE('NUMBER OF DIGITS OF GIVEN NUMBER IS '||CNT);
END;
  10. Write a program to accept a number and find the sum of the digits 
    DECLARE
N NUMBER(5):=&N;
S NUMBER:=0;
R NUMBER(2):=0;
BEGIN
WHILE N !=0
LOOP
R:=MOD(N,10);
S:=S+R;
N:=TRUNC(N/10);
END LOOP;
DBMS_OUTPUT.PUT_LINE('SUM OF DIGITS OF GIVEN NUMBER IS '||S);
END;
  11. Write a program to accept a number and print it in reverse order 
    DECLARE
N NUMBER(5):=&N;
REV NUMBER(5):=0;
R NUMBER(5):=0;
BEGIN
WHILE N !=0
LOOP
R:=MOD(N,10);
REV:=REV*10+R;
N:=TRUNC(N/10);
END LOOP;
DBMS_OUTPUT.PUT_LINE('THE REVERSE OF A GIVEN NUMBER IS '||REV);
END;
  12. Write a program to accept a no and check whether it is Armstrong number or not
  13. Write a porgram to generate all the Armstrong numbers from 1 to 1000
  14. Write a program to generate all prime numbers between 1 to 100
  15. Write a program to aceept a number and check whether it is prime number or not
  16. Write a program to display the fibonacci series from 1 to 10
  17. Write a program to aceept a number and print it in binary format
  18. Write a program to accept a number and find the factorial of the number
  19. Find the factorials of numbers from 1 to 10 
    DECLARE
FACT NUMBER:=1;
V VARCHAR2(100);
BEGIN
FOR I IN 1..10
LOOP
FOR J IN 1..I
LOOP
FACT:=FACT*J;
V:=J||'*'||V;
END LOOP;
DBMS_OUTPUT.PUT_LINE(RTRIM(V,'*')||'='||FACT);
FACT:=1;
V:=NULL;
END LOOP;
END;
  20. Write a program to aceept a number and display it in the Octal format 
    DECLARE
N NUMBER(2):=&N;
R NUMBER(2);
V VARCHAR2(1000);
BEGIN
WHILE N>0
LOOP
R:=MOD(N,8);
V:=R||V;
N:=TRUNC(N/8);
END LOOP;
DBMS_OUTPUT.PUT_LINE('OCTAL OF A GIVEN NUMBER IS '||V);
END;
  21. Write a program to accept a number and print the multiplication tables upto soo 
    DECLARE
N NUMBER(2):=&N;
M NUMBER;
BEGIN
FOR I IN N..N+5
LOOP
FOR J IN 1..10
LOOP
M:=I*J;
DBMS_OUTPUT.PUT_LINE(I||'*'||J||'='||M);
END LOOP;
DBMS_OUTPUT.PUT_LINE('*********************');
END LOOP;
END;
  22. Write a program to accept the temp in Centigrade and convert it into Fahrenheit(c=F-32/1.8) 
    DECLARE
C NUMBER:=&C;
F NUMBER;
BEGIN
F:=C*1.8+32;
DBMS_OUTPUT.PUT_LINE('THE FARENHETT OF GIVEN OC IS '||F);
END;
  23. Write a program to calculate the area of a triangle by accepting the 3 sides 
    (s=(a+b+c)/2 area=sqrt(s*(s-a)*(s-b)*(s-c)))
DECLARE
S NUMBER;
A NUMBER:=&A;
B NUMBER:=&B;
C NUMBER:=&C;
AREA NUMBER(7,2);
BEGIN
S:=(A+B+C)/2;
AREA:=SQRT(S*(S-A)*(S-B)*(S-C));
DBMS_OUTPUT.PUT_LINE('THE AREA OF TRIANGLE IS '||AREA);
END;
  24. Write a program to calculate the area of a circle by accepting the radius and unit of measurearea=PI*r2 
    DECLARE
R NUMBER:=&R;
AREA NUMBER(7,2);
BEGIN
AREA:=(22/7)*R*R;
DBMS_OUTPUT.PUT_LINE('THE AREA OF CIRCLE IS '||AREA);
END;
  25. Write a program to calculate the perimeter of a circle(perimeter=2*PI*r) 
    DECLARE
R NUMBER:=&R;
PERIMETER NUMBER(7,2);
BEGIN
PERIMETER:=2*(22/7)*R;
DBMS_OUTPUT.PUT_LINE('THE PERIMETER OF CIRCLE IS '||PERIMETER);
END;
  26. Write a program to accept the 3 sides of the triangle and display the type of triangle 
    DECLARE
A NUMBER(4,2):=&A;
B NUMBER(4,2):=&B;
C NUMBER(4,2):=&C;
PERIMETER NUMBER(7,2);
BEGIN
IF (A=B AND B=C AND C=A) THEN
DBMS_OUTPUT.PUT_LINE('EQUILATERAL TRIANGLE');
ELSIF A=B OR A=C OR C=B THEN
DBMS_OUTPUT.PUT_LINE('ISOSOCELESS TRIANGLE');
ELSE
DBMS_OUTPUT.PUT_LINE('SCALEN TRIANGLE');
END IF;
END;
  27. Write a program accept the value of A,B&C display which is greater 
    DECLARE
A NUMBER(4,2):=&A;
B NUMBER(4,2):=&B;
C NUMBER(4,2):=&C;
BEGIN
IF (A>B AND A>C) THEN
DBMS_OUTPUT.PUT_LINE('A IS GREATER '||''||A);
ELSIF B>C THEN
DBMS_OUTPUT.PUT_LINE('B IS GREATE '||''||B);
ELSE
DBMS_OUTPUT.PUT_LINE('C IS GREATER '||''||C);
END IF;
END;
  28. Write a program accept a string and check whether it is palindrome or not 
    DECLARE
S VARCHAR2(10):='&S';
L VARCHAR2(20);
TEMP VARCHAR2(10);
BEGIN
FOR I IN REVERSE 1..LENGTH(S)
LOOP
L:=SUBSTR(S,I,1);
TEMP:=TEMP||''||L;
END LOOP;
IF TEMP=S THEN
DBMS_OUTPUT.PUT_LINE(TEMP ||''||' IS PALINDROME');
ELSE
DBMS_OUTPUT.PUT_LINE(TEMP ||''||' IS NOT PALINDROME');
END IF;
END;
  29. .Write a program aceepts the value of A,B and swap the nos and print the values 
    DECLARE
A NUMBER(2):=&A;
B NUMBER(2):=&B;
FLAG NUMBER(2);
BEGIN
FLAG:=A;
A:=B;
B:=FLAG;
DBMS_OUTPUT.PUT_LINE('A '||'= '||A||' AND '||''||'B '||'= '||B);
END;
  30. Write a program to accept the values of A , B and swap the numbers and print the 
    values
without using third variable
DECLARE
A NUMBER(2):=&A;
B NUMBER(2):=&B;
FLAG NUMBER(2);
BEGIN
FLAG:=A;
A:=B;
B:=FLAG;
DBMS_OUTPUT.PUT_LINE('A '||'= '||A||' AND '||''||'B '||'= '||B);
END;
  31. Write a program to accept the side of a square and calculate the area area =a2 
    DECLARE
A NUMBER:=&A;
AREA NUMBER(5);
BEGIN
AREA:=A*A;
DBMS_OUTPUT.PUT_LINE('AREA OF A SQUARE IS '||''||AREA);
END;
  32. Write a program to accept principle amount ,rate,time calculate the simple interest si=(p*t*r)/100 
    DECLARE
P NUMBER(6,2):=&P;
R NUMBER(6,2):=&R;
T NUMBER(6,2):=&T;
SI NUMBER(6,2);
BEGIN
SI:=(P*R*T)/100;
DBMS_OUTPUT.PUT_LINE('SIMPLE INTEREST IS '||''||SI);
END;
  33. Erite a program to aceept the principle amount,rate,time and find the compound 
    interest
ci=p*(1+r/100)n
DECLARE
P NUMBER(6,2):=&P;
R NUMBER(6,2):=&R;
T NUMBER(6,2):=&T;
CI NUMBER(6,2);
BEGIN
CI:=P*POWER(1+(R/100),T);
DBMS_OUTPUT.PUT_LINE('COMPOUND INTEREST IS '||CI);
END;
  34. WAP to calculate the sum of 1!+2!+......+n! 
    DECLARE
N NUMBER:=&N;
S NUMBER:=0;
F NUMBER:=1;
BEGIN
FOR I IN 1..N
LOOP
FOR J IN 1..I
LOOP
F:=F*J;
END LOOP;
S:=S+F;
F:=1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('SUM OF FACT IS '||S);
END;
  35. WAP to calculate the sum of 1+1/2+1/3+......+1/n 
    DECLARE
N NUMBER:=&N;
A NUMBER;
S NUMBER(6,2):=0;
BEGIN
FOR I IN 1..N
LOOP
A:=1/I;
S:=S+A;
END LOOP;
DBMS_OUTPUT.PUT_LINE('SUM OF NO ARE '||S);
END;
  36. WAP to calculate the sum of 1/1!+1/2!+.....+1/n! 
    DECLARE
N NUMBER:=&N;
S NUMBER(6,2):=0;
F NUMBER:=1;
BEGIN
FOR I IN 1..N
LOOP
FOR J IN 1..I
LOOP
F:=F*J;
END LOOP;
S:=S+(1/F);
END LOOP;
DBMS_OUTPUT.PUT_LINE('SUM IS '||S);
END;
  37. WAP to calculate the sum of 1/1!+2/2!+......+n/n! 
    DECLARE
N NUMBER(4):=&N;
S NUMBER(6,2):=0;
F NUMBER(4):=1;
BEGIN
FOR I IN 1..N
LOOP
FOR J IN 1..I
LOOP
F:=F*J;
END LOOP;
S:=S+(I/F);
END LOOP;
DBMS_OUTPUT.PUT_LINE('SUM OF FACT IS '||S);
END;
  38. Write a program to display the months between two dates of a year 
    DECLARE
D DATE:='&D';
D1 DATE:='&D1';
BEGIN
WHILE D < D1
LOOP
DBMS_OUTPUT.PUT_LINE(TO_CHAR(D,'MONTH'));
D:=ADD_MONTHS(D,1);
END LOOP;
END;
  39. Write a program to accept the date and print the weekdays from the given date 
    DECLARE
D DATE:='&D';
WD DATE;
BEGIN
WD:=D+6;
WHILE D <= WD
LOOP
DBMS_OUTPUT.PUT_LINE(TO_CHAR(D,'DAY'));
D:=D+1;
END LOOP;
END;
  40. WAP to accept the date and print the weekdays from the given date along with date format 
    DECLARE
D DATE:='&D';
WD DATE;
BEGIN
WD:=D+6;
WHILE D <= WD
LOOP
DBMS_OUTPUT.PUT_LINE(TO_CHAR(D,'DAY')||D);
D:=D+1;
END LOOP;
END;
  41. Writa a program to accept a year and check whether it is leap year or not 
                          DECLARE
                      
    Y NUMBER:=&Y;
R NUMBER;
BEGIN
IF MOD(Y,4)=0 AND MOD(Y,100)!=0 OR MOD(Y,400)=0
THEN
DBMS_OUTPUT.PUT_LINE(Y ||' IS A LEAP YEAR');
ELSE
DBMS_OUTPUT.PUT_LINE(Y ||' IS NOT A LEAP YEAR');
END IF;
END;
  42. Write a program to accept a year and display all sundays along with the date 
    DECLARE
Y NUMBER(4):=&YYYY;
A DATE;
B DATE;
I NUMBER(2):=1;
BEGIN
A:=TO_DATE('01-JAN-'||Y,'DD-MON-YYYY');
B:=LAST_DAY(ADD_MONTHS(A,11));
WHILE A <= B
LOOP
IF TO_CHAR(A,'D')=1 THEN
DBMS_OUTPUT.PUT_LINE(LPAD(I,2,'0')||'-'||UPPER(TO_CHAR(A,'DAY'))||A);
I:=I+1;
END IF;
A:=A+1;
END LOOP;
END;
  43. WAP to accept a string and count how many vowels present in the string 
    DECLARE
V VARCHAR2(300):='&V';
CNT NUMBER(5):=0;
C CHAR;
BEGIN
FOR I IN 1..LENGTH(V)
LOOP
C:=SUBSTR(V,I,1);
IF C IN ('A','E','I','O','U') THEN
CNT:=CNT+1;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('NO OF VOWELS PRESENT = '||CNT);
END;
  44. Write a program to accept a year and check whether it is leap year or not . If it is leap year then display how many sundays present in that year 
    DECLARE
D DATE:='&YEAR';
Y VARCHAR2(20);
CNT NUMBER(5):=0;
V VARCHAR2(20);
BEGIN
Y:=TO_CHAR(D,'YYYY');
D:=TO_DATE('01-JAN-'||Y);
IF MOD(Y,4)=0 AND MOD(Y,100)!=0 OR MOD(Y,400)=0 THEN
FOR I IN 1..366
LOOP
V:=TO_CHAR(D,'D');
IF V=1 THEN
CNT:=CNT+1;
END IF;
D:=D+1;
DBMS_OUTPUT.PUT_LINE('NO OF VOWELS PRESENT = '||CNT);
END LOOP;
END;
  45. Write a program to accept a char and check it is vowel or consonant 
    DECLARE
C CHAR:='&C';
BEGIN
IF C='A' OR C='E' OR C='I' OR C='O' OR C='U' THEN
DBMS_OUTPUT.PUT_LINE('VOWEL');
ELSE
DBMS_OUTPUT.PUT_LINE('CONSONANT');
END IF;
END;
  46. WAP to accept A,B,C & D check whether it is Ramanujan number or not 
    DECLARE
A NUMBER:=&A;
B NUMBER:=&B;
C NUMBER:=&C;
D NUMBER:=&D;
BEGIN
IF
POWER(A,3)+POWER(B,3)=POWER(C,3)+POWER(D,3) THEN
DBMS_OUTPUT.PUT_LINE(A||CHR(179)||'+'||B||CHR(179)||'='||C||CHR(179)||'+'||D||CHR(179));
ELSE
DBMS_OUTPUT.PUT_LINE(A||CHR(179)||'+'||B||CHR(179)||'!='||C||CHR(179)||'+'||D||CHR(179));
END IF;
END;
  47. WAP to accept the CMR & LMR & find out the total bill amount
    1. 0-100 units Rs.50 per unit
    2. 101-200n units Rs.o.25 per unit
    3. >200 units Rs.1.25 per unit
    DECLARE
LMR NUMBER(5):=&LMR;
CMR NUMBER(5):=&CMR;
TOT NUMBER(5):=0;
BILL NUMBER(7,2):=0;
BEGIN
TOT:=CMR-LMR;
IF TOT <= 100 THEN
BILL:=TOT*.50;
ELSIF TOT >100 AND TOT <= 200 THEN
BILL:=(100*.50)+((TOT-100)*.75);
ELSE
BILL:=(100*.50)+(100*.75)+(TOT-200)*1.25;
END IF;
DBMS_OUTPUT.PUT_LINE('TOTAL UNIT CONSUMED '||TOT);
DBMS_OUTPUT.PUT_LINE('TOTAL BILL AMOUNT '||BILL);
END;
  48. WAP or accept marks of 3 subject as i/p and calculate the total marks and division of a student
    1. If totmark>=60 then division is First
    2. If totmark <60 and totmark>=50 then division is second
    3. If totmark< 50 and >=35 then division is third
    4. If totmark< 35 then fail
    DECLARE
M1 NUMBER(2):=&M1;
M2 NUMBER(2):=&M2;
M3 NUMBER(2):=&M3;
TOTMARK NUMBER(5,2);
AVE NUMBER(5,2):=0;
BEGIN
TOTMARK:=M1+M2+M3;
AVE:=TOTMARK/3;
IF AVE>=60 THEN
DBMS_OUTPUT.PUT_LINE('THE DIVISION IS FIRST '||AVE);
ELSIF AVE<60 AND AVE>=50 THEN
DBMS_OUTPUT.PUT_LINE('THE DIVISION IS SECOND '||AVE);
ELSIF AVE<50 AND AVE>=35 THEN
DBMS_OUTPUT.PUT_LINE('THE DIVISION IS THIRD '||AVE);
ELSE
DBMS_OUTPUT.PUT_LINE('FAIL '||AVE);
END IF;
END;
  49. WAP to accept a number and print its multiplication table horinzontally 
    DECLARE
J NUMBER:=&J;
V VARCHAR2(1000);
K NUMBER(3);
BEGIN
FOR I IN 1..10
LOOP
K:=J*I;
V:=V||J||'*'||I||'='||K||' ';
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
END;
  50. WAP to accept a string and print it in reverse order 
    DECLARE
STR VARCHAR2(100):='&sTR';
STR1 VARCHAR2(100);
N NUMBER(5);
L VARCHAR2(20);
BEGIN
N:=LENGTH(STR);
FOR I IN 1..N
LOOP
L:=SUBSTR(STR,I,1);
STR1:=L||STR1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(STR1);
END;
  51. Write a program to accept a number and find out the sum of first and last digits 
    DECLARE
A NUMBER(4):=&A;
B NUMBER(5):=0;
C NUMBER(5):=0;
S NUMBER(5);
BEGIN
IF A>9 THEN
C:=SUBSTR(A,1,1);
B:=SUBSTR(A,LENGTH(A),1);
S:=B+C;
ELSE
S:=A;
END IF;
DBMS_OUTPUT.PUT_LINE('SUM OF FIRST AND LAST DIGIT IS '||S);
END;
  52. WAP to accept the basic salary and find out the ta,da,hra,lic and gs i)ta 20% of basic, da 10% of basic, hra 30% of basic, lic 5% of basic 
    DECLARE
BS NUMBER(6,2):=&BS;
TA NUMBER(6,2);
DA NUMBER(6,2);
HRA NUMBER(6,2);
GS NUMBER(6,2);
LIC NUMBER(6,2);
NS NUMBER(8,2);
BEGIN
TA:=BS*(20/100);
HRA:=BS*(30/100);
DA:=BS*(10/100);
LIC:=BS*(5/100);
GS:=TA+HRA+DA;
NS:=GS-LIC;
DBMS_OUTPUT.PUT_LINE('EMPLOYEE BS IS '||BS);
DBMS_OUTPUT.PUT_LINE('GROSS SALARY IS '||GS);
DBMS_OUTPUT.PUT_LINE('NET SALARY IS '||NS);
END;
  53. WAP to accept the length and breadth of a rectangle and find out the perimeter 
    DECLARE
L NUMBER(4,2):=&L;
B NUMBER(4,2):=&B;
A NUMBER(4,2);
BEGIN
A:=2*(L+B);
DBMS_OUTPUT.PUT_LINE('THE PERIMETER OF RECTANGLE IS '||A);
END;
  54. WAP to accept the cost price and selling price of an item and find the loss or profit 
    DECLARE
CP NUMBER(25,2):=&CP;
SP NUMBER(25,2):=&SP;
AMT NUMBER(7,2);
BEGIN
IF CP < SP THEN
AMT:=SP-CP;
DBMS_OUTPUT.PUT_LINE('PROFIT IS '||AMT);
ELSE
AMT:=CP-SP;
DBMS_OUTPUT.PUT_LINE('LOSS IS '||AMT);
END IF;
END;
  55. Writ a program to generate the following series 53 53 53 53 53 43 43 43 43 33 33 33 23 23 13 
    DECLARE
V VARCHAR2(20);
BEGIN
FOR I IN REVERSE 1..5
LOOP
FOR J IN 1..I
LOOP
V:=V||I||CHR(179);
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
V:=NULL;
END LOOP;
END;
  56. WAP to accept a no in binary format and print it in decimal format 
    DECLARE
N VARCHAR2(20):=&N;
PRO NUMBER(10,4):=0;
L VARCHAR2(10);
BEGIN
FOR I IN 1..LENGTH(N)
LOOP
L:=SUBSTR(N,I,1);
PRO:=PRO+L*POWER(2,LENGTH(N)-I);
END LOOP;
DBMS_OUTPUT.PUT_LINE('THE DECIMAL NUMBER IS '||PRO);
END;
  57. WAP to accept two nos and input and find one no is raised to another one (without using any function) 
    DECLARE
A NUMBER:=&A;
B NUMBER:=&B;
R NUMBER:=1;
BEGIN
FOR I IN 1..B
LOOP
R:=R*A;
END LOOP;
DBMS_OUTPUT.PUT_LINE('A RAISED POWER B IS '||R);
END;
  58. WAP to accept a sentence and count the no of chars in that sentence 
    DECLARE
STR VARCHAR2(100):='&STR';
NO NUMBER(5):=0;
I NUMBER;
BEGIN
I:=INSTR(STR,'.');
DBMS_OUTPUT.PUT_LINE('NO OF CHAR IS '||I);
END;
  59. WAP to accept two strings and display the large one among those 
    DECLARE
STR1 VARCHAR2(100):='&STR1';
STR2 VARCHAR2(100):='&STR2';
BEGIN
IF LENGTH(STR1) >LENGTH(STR2) THEN
DBMS_OUTPUT.PUT_LINE(STR1 ||' IS GREATER');
ELSIF LENGTH(STR1) < LENGTH(STR2) THEN
DBMS_OUTPUT.PUT_LINE(STR2 ||' IS GREATER');
ELSE
DBMS_OUTPUT.PUT_LINE('BOTH STRINGS ARE EQUAL');
END IF;
END;
  60. WAP to display all the nos whose sum of digits is 9 from 1 to 9999 
    DECLARE
N NUMBER;
M NUMBER;
S NUMBER:=0;
BEGIN
FOR I IN 1..999
LOOP
N:=I;
WHILE N>0
LOOP
M:=MOD(N,10);
S:=S+M;
N:=TRUNC(N/10);
END LOOP;
IF S=9 THEN
DBMS_OUTPUT.PUT_LINE(I||' ');
END IF;
S:=0;
END LOOP;
END;
  61. WAP to accept a no and find the sum in a single digit 
    DECLARE
N NUMBER(4):=&N;
S NUMBER(10):=0;
BEGIN
WHILE LENGTH(N)>1
LOOP
FOR I IN 1..LENGTH(N)
LOOP
S:=S+SUBSTR(N,I,1);
END LOOP;
N:=S;
S:=0;
END LOOP;
DBMS_OUTPUT.PUT_LINE('THE SUM IN SINGLE DIGIT IS '||N);
END;
  62. Ente the no of days and find out the no of years and no of days and months 
    DECLARE
D NUMBER:=&D;
Y NUMBER;
M NUMBER;
BEGIN
Y:=TRUNC(D/365);
M:=TRUNC(MOD(D,365)/30);
D:=MOD(MOD(D,365),30);
DBMS_OUTPUT.PUT_LINE(Y||' YEARS '||M||' MONTHS '||D||' DAYS');
END;
  63. WAP to accept the date and print all the weekdays along with the given date 
    DECLARE
D DATE:='&D';
V VARCHAR2(20);
BEGIN
FOR I IN 1..7
LOOP
V:=TO_CHAR(D,'DAY')||D;
DBMS_OUTPUT.PUT_LINE(V);
D:=D+1;
END LOOP;
END;
  64. WAP while purchasing certain items,discount of each is as follows i) If qty purchased >1000 discount is 20% ii) If the qty and price per item are i/p then calculate the expenditure 
    DECLARE
QTY NUMBER(5):=&QTY;
UP NUMBER(6,2):=&UP;
DIS NUMBER(6,2):=0;
TAMT NUMBER(10,2);
BILL NUMBER(10,2);
BEGIN
BILL:=QTY*UP;
IF BILL >1000 THEN
DIS:=BILL*20/1000;
END IF;
TAMT:=BILL-DIS;
DBMS_OUTPUT.PUT_LINE('THE TOTAL AMOUNT IS '||TAMT);
END;
  65. Write a program to accept a string and count the no of individual chars 
    DECLARE
V VARCHAR2(100):='&V';
V1 VARCHAR2(100);
LB NUMBER;
LA NUMBER;
DIFF NUMBER;
C CHAR;
N NUMBER(5):=0;
BEGIN
V1:=V;
WHILE LENGTH(V1)>0
LOOP
C:=SUBSTR(V1,1,1);
LB:=LENGTH(V1);
V1:=REPLACE(V1,C);
LA:=NVL(LENGTH(V1),0);
DIFF:=LB-LA;
IF ASCII(C)=32 THEN
DBMS_OUTPUT.PUT_LINE('SPACE'||' EXISTS '||DIFF||' TIMES');
ELSE
DBMS_OUTPUT.PUT_LINE(C||' EXISTS '||DIFF||' TIMES');
END IF;
N:=N+DIFF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('TOTAL LENGTH OF THE GIVEN STRING '||V||'='||N);
END;
  66. Write a program to display all combination of 1,2,&3 
    BEGIN
FOR I IN 1..3
LOOP
FOR J IN 1..3
LOOP
FOR K IN 1..3
LOOP
DBMS_OUTPUT.PUT_LINE(I||J||K);
END LOOP;
END LOOP;
END LOOP;
END;
  67. Write a program to find out the series 12+22+32+42+....++n2 
    DECLARE
N NUMBER:=&N;
A NUMBER:=1;
B NUMBER:=2;
C NUMBER:=0;
D NUMBER:=0;
S NUMBER:=0;
BEGIN
WHILE A<=N
LOOP
C:=C+A*A;
A:=A+2;
END LOOP;
WHILE B<=N
LOOP
D:=D+B*B;
B:=B+2;
END LOOP;
S:=C-D;
DBMS_OUTPUT.PUT_LINE('RESULT IS '||S);
END;
  68. Write a program to accep the time in HH & MIN format and find the total senconds 
    DECLARE
H NUMBER:=&HOUR;
M NUMBER:=&MINUTE;
S NUMBER(10):=0;
BEGIN
S:=(H*60*60)+(M*60);
DBMS_OUTPUT.PUT_LINE(H||' HOURS '||M||' MINUTES '||'IS'||S||' SECONDS');
END;
  69. WAP to accept the distance between two cities in km and convert into mts ,cm & ft 
    DECLARE
D NUMBER:=&D;
M NUMBER:=0;
CM NUMBER:=0;
FT NUMBER:=0;
BEGIN
M:=D*1000;
CM:=M*100;
FT:=ROUND(CM/12.3);
DBMS_OUTPUT.PUT_LINE('DISTANCE IN METERS IS '||M);
DBMS_OUTPUT.PUT_LINE('DISTANCE IN CENTIMETERS IS '||CM);
DBMS_OUTPUT.PUT_LINE('DISTANCE IN FOOT IS '||FT);
END;
  70. Write a program to find the series x+x2/2!+x3/3!+.....+xn/n! 
    DECLARE
N NUMBER:=&N;
X NUMBER:=&X;
S NUMBER:=0;
F NUMBER:=1;
BEGIN
FOR I IN 1..N
LOOP
FOR J IN 1..I
LOOP
F:=F*J;
END LOOP;
S:=ROUND(s+(POWER(X,I)/F),3);
F:=1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('SUM OF NUMBER IS '||S);
END;
  71. Write a program to accept the population of hyderabad each year the population increases 2% after 4y what is the population of hyd 
    DECLARE
P NUMBER:=&P;
L NUMBER;
BEGIN
FOR J IN 1..4
LOOP
L:=P*2/100;
P:=P+L;
END LOOP;
DBMS_OUTPUT.PUT_LINE('POPULATION OF HYDERABAD AFTER 4 YEARS IS '||TRUNC(P));
END;
  72. WAP to accept the 3 dates and display the most recently month among 3 dates 
    DECLARE
D1 DATE:='&D1';
D2 DATE:='&D2';
D3 DATE:='&D3';
M1 NUMBER;
M2 NUMBER;
M3 NUMBER;
BEGIN
M1:=TO_CHAR(D1,'MM');
M2:=TO_CHAR(D2,'MM');
M3:=TO_CHAR(D3,'MM');
IF M1>M2 AND M1>M3 THEN
DBMS_OUTPUT.PUT_LINE(TO_CHAR(D1,'MON')||' IS RECENT MONTH');
ELSIF M2>M1 AND M2>M3 THEN
DBMS_OUTPUT.PUT_LINE(TO_CHAR(D2,'MON')||' IS RECENT MONTH');
ELSE
DBMS_OUTPUT.PUT_LINE(TO_CHAR(D3,'MON')||' IS RECENT MONTH');
END IF;
END;
  73. Accept a string and print in the following format O OR ORA ORAC ORACL ORACLE 
    DECLARE
V VARCHAR2(20):='&V';
C VARCHAR(20);
BEGIN
FOR I IN 1..LENGTH(V)
LOOP
C:=SUBSTR(V,1,I);
DBMS_OUTPUT.PUT_LINE(C);
END LOOP;
END;
  74. Write a program to accept the annual income of the emp and find the income tax i) If the annsal >60000 then tax is 10% of income ii) If the annsal >100000 then tax is Rs 800+16% of income iii) If the annsal >140000 then tax is Rs 2500+25% of income 
    DECLARE
AI NUMBER(10,2):=&ANNUALINCOME;
TAX NUMBER(10,3):=0;
BEGIN
IF AI BETWEEN 36000 AND 50000 THEN
TAX:=AI*10/100;
ELSIF AI BETWEEN 50000 AND 100000 THEN
TAX:=800+AI*16/100;
ELSIF AI >100000 THEN
TAX:=2500+AI*25/100;
END IF;
DBMS_OUTPUT.PUT_LINE('ANNUAL INCOME '||AI);
DBMS_OUTPUT.PUT_LINE('TAX '||TAX);
END;
  75. WAP to accept a year as i/p & find how many even number present in that year 
    DECLARE
Y NUMBER:=&YEAR;
A VARCHAR2(20);
CNT NUMBER(5):=0;
BEGIN
FOR I IN 1..LENGTH(Y)
LOOP
A:=SUBSTR(Y,I,1);
IF MOD(A,2)=0 THEN
CNT:=CNT+1;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('NUMBER OF EVEN DIGIT IS '||CNT);
END;
  76. WAP to accept a year as i/p & find how many odd number present in that year 
    DECLARE
Y NUMBER:=&YEAR;
A VARCHAR2(20);
CNT NUMBER(5):=0;
BEGIN
FOR I IN 1..LENGTH(Y)
LOOP
A:=SUBSTR(Y,I,1);
IF MOD(A,2)!=0 THEN
CNT:=CNT+1;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('NUMBER OF EVEN DIGIT IS '||CNT);
END;
  77. WAP to accept a number and calculate the sum of numbers in even places 
    DECLARE
N NUMBER:=&NUMBER;
A VARCHAR2(10);
S NUMBER:=0;
BEGIN
FOR I IN 1..LENGTH(N)
LOOP
A:=SUBSTR(N,I,1);
IF MOD(I,2)=0 THEN
S:=S+A;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('SUM OF EVEN PLACE IS '||S);
END;
  78. WAP to accept the emp details and calculate the bonus based on the following conditions i) If sal < 500 then bonus is 10% sal ii) If sal >3500 then bonus is 12% sal iii) If sal >1000 then bonus is 13.5% sal 
    DECLARE
EMPNOV NUMBER:=&EMPNOV;
SALV NUMBER;
B NUMBER(7,2);
BEGIN
SELECT SAL INTO SALV FROM EMP WHERE EMPNO=EMPNOV;
IF SALV BETWEEN 500 AND 3500 THEN
B:=SALV*10/100;
ELSIF SALV BETWEEN 3500 AND 10000 THEN
B:=SALV*12/100;
ELSIF SALV>10000 THEN
B:=SALV*13.5/100;
END IF;
DBMS_OUTPUT.PUT_LINE('EMPNO '||EMPNOV);
DBMS_OUTPUT.PUT_LINE('SALARY '||SALV);
DBMS_OUTPUT.PUT_LINE('BONUS '||B);
END;
  79. WAP to accept the empno and display ename,sal,hiredate and calculate ta,da,hra,lic,gross,exp and print all emp details. ta is 30% of sal,da is 20% of sal,hra is 15% of sal,lic is 5% of sal 
    DECLARE
EMPNOV NUMBER:=&EMPNOV;
ENAMEV EMP.ENAME%TYPE;
SALV EMP.SAL%TYPE;
HIREDATEV EMP.HIREDATE%TYPE;
EXP NUMBER(7,2);
TA NUMBER(7,2);
DA NUMBER(7,2);
HRA NUMBER(7,2);
LIC NUMBER(7,2);
GROSS NUMBER(7,2);
S NUMBER:=0;
BEGIN
SELECT ENAME,SAL,HIREDATE INTO ENAMEV,SALV,HIREDATEV FROM EMP WHERE EMPNO=EMPNOV;
EXP:=ROUND(MONTHS_BETWEEN(SYSDATE,HIREDATEV)/12,3);
TA:=SALV*30/100;
DA:=SALV*20/100;
HRA:=SALV*15/100;
LIC:=SALV*5/100;
GROSS:=SALV+TA+DA+HRA-LIC;
DBMS_OUTPUT.PUT_LINE('EMPNO '||EMPNOV);
DBMS_OUTPUT.PUT_LINE('ENAME '||ENAMEV);
DBMS_OUTPUT.PUT_LINE('SALARY '||SALV);
DBMS_OUTPUT.PUT_LINE('EXPERIENCE '||EXP);
DBMS_OUTPUT.PUT_LINE('TA '||TA);
DBMS_OUTPUT.PUT_LINE('DA '||DA);
DBMS_OUTPUT.PUT_LINE('HRA '||HRA);
DBMS_OUTPUT.PUT_LINE('LIC '||LIC);
DBMS_OUTPUT.PUT_LINE('GROSS '||GROSS);
END;
  80. WAP to accept the item no ,item name,qty,unit price and calculate the bill If the bill >500 then give discount 2% of bill amount and display the details 
    DECLARE
INO NUMBER:=&INO;
INAME VARCHAR2(50):='&INAME';
QTY NUMBER(5):=&QTY;
UP NUMBER(7,2):=&UP;
DIS NUMBER(7,2):=0;
BILL NUMBER(7,2);
NET NUMBER(7,2);
BEGIN
BILL:=QTY*UP;
IF BILL>500 THEN
DIS:=BILL*2/100;
END IF;
NET:=BILL-DIS;
DBMS_OUTPUT.PUT_LINE('ITEM NO '||INO);
DBMS_OUTPUT.PUT_LINE('ITEM NAME '||INAME);
DBMS_OUTPUT.PUT_LINE('QUANTITY '||QTY);
DBMS_OUTPUT.PUT_LINE('UNIT PRICE '||UP);
DBMS_OUTPUT.PUT_LINE('BILL AMT '||BILL);
DBMS_OUTPUT.PUT_LINE('DISCOUNT '||DIS);
DBMS_OUTPUT.PUT_LINE('NET AMT '||NET);
END;
  81. Write a program to generate sequence of numbers horizontally from 1 to 25 DECLARE V VARCHAR2(100); BEGIN FOR I IN 1..25 LOOP V:=V||' '||I; END LOOP; DBMS_OUTPUT.PUT_LINE(V); END;
  82. WAP to accept a empno and display empno,name,sal,exp,dname,grade and loc. 
    DECLARE
EMPNOV NUMBER:=&EMPNO;
ENAMEV EMP.ENAME%TYPE;
HIREDATEV DATE;
SALV EMP.SAL%TYPE;
EXP NUMBER;
DNAMEV DEPT.DNAME%TYPE;
GRADEV SALGRADE.GRADE%TYPE;
BEGIN
SELECT ENAME,SAL,HIREDATE,DNAME,GRADE INTO ENAMEV,SALV,HIREDATEV,DNAMEV,GRADEV FROM EMP,DEPT,SALGRADE
WHERE EMPNO=EMPNOV AND EMP.DEPTNO=DEPT.DEPTNO AND SAL BETWEEN LOSAL AND HISAL;
EXP:=ROUND(MONTHS_BETWEEN(SYSDATE,HIREDATEV)/12,3);
DBMS_OUTPUT.PUT_LINE('EMPNO '||EMPNOV);
DBMS_OUTPUT.PUT_LINE('ENAME '||ENAMEV);
DBMS_OUTPUT.PUT_LINE('SALARY '||SALV);
DBMS_OUTPUT.PUT_LINE('EXPERIENCE '||EXP||' YEARS');
DBMS_OUTPUT.PUT_LINE('DNAME '||DNAMEV);
DBMS_OUTPUT.PUT_LINE('GRADE '||GRADEV);
END;
  83. WAP to accept a empno and display empno,based on experience calculate the bonus and store it into the bonus table If exp >5 years then bonus is 1 month salary If exp between 5 and 9 years then bonus is 20% of annual salary If exp more than 9 years then bonus is 1 month sal plus 25% of annual salary 
    DECLARE
EMPNOV NUMBER:=&EMPNO;
ENAMEV EMP.ENAME%TYPE;
HIREDATEV DATE;
SALV EMP.SAL%TYPE;
EXP NUMBER;
DNAMEV DEPT.DNAME%TYPE;
GRADEV SALGRADE.GRADE%TYPE;
BEGIN
SELECT ENAME,SAL,HIREDATE,DNAME,GRADE INTO ENAMEV,SALV,HIREDATEV,DNAMEV,GRADEV FROM EMP,DEPT,SALGRADE
WHERE EMPNO=EMPNOV AND EMP.DEPTNO=DEPT.DEPTNO AND SAL BETWEEN LOSAL AND HISAL;
EXP:=ROUND(MONTHS_BETWEEN(SYSDATE,HIREDATEV)/12,3);
DBMS_OUTPUT.PUT_LINE('EMPNO '||EMPNOV);
DBMS_OUTPUT.PUT_LINE('ENAME '||ENAMEV);
DBMS_OUTPUT.PUT_LINE('SALARY '||SALV);
DBMS_OUTPUT.PUT_LINE('EXPERIENCE '||EXP||' YEARS');
DBMS_OUTPUT.PUT_LINE('DNAME '||DNAMEV);
DBMS_OUTPUT.PUT_LINE('GRADE '||GRADEV);
END;
  84. WAP to accept the empno, based upon the dname transfer the emps ie, make the changes in the emp table. Transfer the emps from Accounting dept to Research, Research dept to Operation, Opertion dept to Sales and Sales to Accounting dept 
    DECLARE
EMPNOV NUMBER:=&EMPNO;
DNAMEV VARCHAR2(20);
DNAMEVV VARCHAR2(20);
BEGIN
SELECT DNAME INTO DNAMEV FROM EMP,DEPT WHERE EMPNO=EMPNOV AND EMP.DEPTNO=DEPT.DEPTNO;
IF DNAMEV='ACCOUNTING' THEN
DNAMEVV:='RESEARCH';
ELSIF DNAMEV='RESEARCH' THEN
DNAMEVV:='SALES';
ELSIF DNAMEV='SALES' THEN
DNAMEVV:='OPERATIONS';
ELSIF DNAMEV='OPERATIONS' THEN
DNAMEVV:='ACCOUNTING';
END IF;
UPDATE EMP SET DEPTNO=(SELECT DEPTNO FROM DEPT WHERE DNAME=DNAMEVV) WHERE EMPNO=EMPNOV;
END;
  85. WAP to accept the empno and display all the details of emp. If emp doesnot exist display the appreciate message 
    DECLARE
EMPNOV NUMBER:=&EMPNO;
EMPV EMP%ROWTYPE;
BEGIN
SELECT * INTO EMPV FROM EMP WHERE EMPNO=EMPNOV;
DBMS_OUTPUT.PUT_LINE('EMPNO '||EMPV.EMPNO);
DBMS_OUTPUT.PUT_LINE('ENAME '||EMPV.ENAME);
DBMS_OUTPUT.PUT_LINE('JOB '||EMPV.JOB);
DBMS_OUTPUT.PUT_LINE('SALARY '||EMPV.SAL);
DBMS_OUTPUT.PUT_LINE('HIREDATE '||EMPV.HIREDATE);
DBMS_OUTPUT.PUT_LINE('DEPTNO '||EMPV.DEPTNO);
DBMS_OUTPUT.PUT_LINE('MGRNO '||EMPV.MGR);
DBMS_OUTPUT.PUT_LINE('COMMISSION '||EMPV.COMM);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('EMP NUMBER DOES NOT EXIST');
END;
  86. WAP to accept the empno and print all the details of emp,dept and salgrade 
    DECLARE
E EMP%ROWTYPE;
D DEPT%ROWTYPE;
S SALGRADE%ROWTYPE;
BEGIN
SELECT * INTO E FROM EMP WHERE EMPNO=&EMPNO;
SELECT * INTO D FROM DEPT WHERE E.DEPTNO=DEPT.DEPTNO;
SELECT * INTO S FROM SALGRADE WHERE E.SAL BETWEEN LOSAL AND HISAL;
DBMS_OUTPUT.PUT_LINE('EMPNO '||E.EMPNO);
DBMS_OUTPUT.PUT_LINE('DEPTNO '||D.DEPTNO);
DBMS_OUTPUT.PUT_LINE('DNAME '||D.DNAME);
DBMS_OUTPUT.PUT_LINE('LOCATION '||D.LOC);
DBMS_OUTPUT.PUT_LINE('GRADE '||S.GRADE);
DBMS_OUTPUT.PUT_LINE('HISALARY '||S.HISAL);
DBMS_OUTPUT.PUT_LINE('LOWSALARY '||S.LOSAL);
END;
  87. WAP to accept the mgrno and display the empno,ename,sal,dname and grade of all emps working under that mgr 
    DECLARE
MGRV NUMBER:=&MGRV;
CURSOR EMPCUR IS
SELECT EMPNO,ENAME,SAL,DEPTNO,GRADE FROM EMP,SALGRADE WHERE MGR=MGRV AND SAL BETWEEN LOSAL AND HISAL;
X EMPCUR%ROWTYPE;
BEGIN
OPEN EMPCUR;
LOOP
FETCH EMPCUR INTO X;
EXIT WHEN EMPCUR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('EMPNO '||X.EMPNO);
DBMS_OUTPUT.PUT_LINE('ENAME '||X.ENAME);
DBMS_OUTPUT.PUT_LINE('SALARY '||X.SAL);
DBMS_OUTPUT.PUT_LINE('DEPTNO '||X.DEPTNO);
DBMS_OUTPUT.PUT_LINE('GRADE '||X.GRADE);
DBMS_OUTPUT.PUT_LINE('******************');
END LOOP;
CLOSE EMPCUR;
END;
  88. WAP to accept the empno and display the exp with minimum 3 decimal places 
    DECLARE
EMPNOV NUMBER:=&EMPNOV;
HIREDATEV DATE;
EXPV NUMBER(10,5);
BEGIN
SELECT HIREDATE INTO HIREDATEV FROM EMP WHERE EMPNO=EMPNOV;
EXPV:=ROUND(MONTHS_BETWEEN(SYSDATE,HIREDATEV)/12,3);
DBMS_OUTPUT.PUT_LINE('EXPERIENCE OF EMP'||EMPNOV||' IS '||EXPV||' YEARS ');
END;
  89. Write a program to print the following series
    1
    1 2
    1 2 3
    1 2 3 4
    1 2 3 4 5
    DECLARE
V VARCHAR2(20);
BEGIN
FOR I IN 1..5
LOOP
FOR J IN 1..I
LOOP
V:=V||' '||J;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
V:=NULL;
END LOOP;
END;
  90. Write a program to print the following series
    1
    2 1
    3 2 1
    4 3 2 1
    5 4 3 2 1
    DECLARE
V VARCHAR2(20);
BEGIN
FOR I IN 1..5
LOOP
FOR J IN REVERSE 1..I
LOOP
V:=V||' '||J;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
V:=NULL;
END LOOP;
END;
  91. Write a program to print the following series
    1 2 3 4 5
    1 2 3 4
    1 2 3
    1 2
    1
    DECLARE
V VARCHAR2(20);
BEGIN
FOR I IN REVERSE 1..5
LOOP
FOR J IN 1..I
LOOP
V:=V||' '||J;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
V:=NULL;
END LOOP;
END;
  92. 92.Write a program to print the following series
    1 1 1 1 1
    2 2 2 2 2
    3 3 3 3 3
    4 4 4 4 4
    5 5 5 5 5
    DECLARE
V VARCHAR2(20);
BEGIN
FOR I IN 1..5
LOOP
FOR J IN 1..5
LOOP
V:=V||' '||I;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
V:=NULL;
END LOOP;
END;
  93. Write a program to print the following series
    1 2 3 4 5
    1 2 3 4 5
    1 2 3 4 5
    1 2 3 4 5
    1 2 3 4 5
    DECLARE
V VARCHAR2(20);
BEGIN
FOR I IN 1..5
LOOP
FOR J IN 1..5
LOOP
V:=V||' '||J;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
V:=NULL;
END LOOP;
END;
  94. Write a program to print the following series
    5 4 3 2 1
    5 4 3 2
    5 4 3
    5 4
    5
    DECLARE
V VARCHAR2(20);
BEGIN
FOR I IN 1..5
LOOP
FOR J IN REVERSE 1..5
LOOP
IF I<=J THEN
V:=V||' '||J;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
V:=NULL;
END LOOP;
END;
  95. Write a program to print the following series
    5 5 5 5 5
    4 4 4 4
    3 3 3
    2 2
    1
    DECLARE
V VARCHAR2(20);
BEGIN
FOR I IN REVERSE 1..5
LOOP
FOR J IN 1..5
LOOP
IF I>=J THEN
V:=V||' '||I;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
V:=NULL;
END LOOP;
END;
  96. Write a program to print the following series
    1
    2 2
    3 3 3
    4 4 4 4
    5 5 5 5 5
    DECLARE
V VARCHAR2(20);
BEGIN
FOR I IN 1..5
LOOP
FOR J IN 1..I
LOOP
V:=V||' '||I;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
V:=NULL;
END LOOP;
END;
  97. Write a program to print the following series
    1
    0 1
    1 0 1
    0 1 0 1
    1 0 1 0 1
    DECLARE
A NUMBER:=1;
V VARCHAR2(20):=1;
BEGIN
DBMS_OUTPUT.PUT_LINE(V);
FOR I IN 1..4
LOOP
IF SUBSTR(V,1,1)='1' THEN
V:='0'||V;
ELSE
V:='1'||V;
END IF;
DBMS_OUTPUT.PUT_LINE(V);
END LOOP;
END;
  98. Write a program to print the following series
    *
    * *
    * * *
    * * * *
    * * * * *
    DECLARE
V VARCHAR2(20);
BEGIN
FOR I IN 1..5
LOOP
FOR J IN 1..I
LOOP
V:=V||' '||'*';
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
V:=NULL;
END LOOP;
END;
  99. Write a program to print the following series
    *
    * *
    * * *
    * * * *
    * * * * *
    * * * *
    * * *
    * *
    *
    DECLARE
V VARCHAR2(20);
BEGIN
FOR I IN 1..5
LOOP
FOR J IN 1..I
LOOP
V:=V||' '||'*';
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
V:=NULL;
END LOOP;
FOR I IN REVERSE 1..5
LOOP
FOR J IN 2..I
LOOP
V:=V||' '||'*';
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
V:=NULL;
END LOOP;
END;
  100. Write a program to print the following series
    1 2 3 4 5
    2 3 4 5
    3 4 5
    4 5
    5
    DECLARE
V VARCHAR2(20);
BEGIN
FOR I IN 1..5
LOOP
FOR J IN I..5
LOOP
V:=V||' '||J;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
V:=NULL;
END LOOP;
END;
  101. Write a program to print the following series
    5 4 3 2 1
    4 3 2 1
    3 2 1
    2 1
    1
    DECLARE
V VARCHAR2(20);
BEGIN
FOR I IN REVERSE 1..5
LOOP
FOR J IN REVERSE 1..I
LOOP
V:=V||' '||J;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
V:=NULL;
END LOOP;
END;
  102. WAP to accept 2 nos and find the sum and product of the nos and print the output 
    DECLARE
A NUMBER:=&A;
B NUMBER:=&B;
S NUMBER;
M NUMBER;
BEGIN
S:=A+B;
M:=A*B;
DBMS_OUTPUT.PUT_LINE('SUM OF '||'A'||' AND '||'B'||' IS '||S);
DBMS_OUTPUT.PUT_LINE('PRODUCT OF '||'A'||' AND '||'B'||' IS '||M);
END;
  103. WAP to accept 2 nos and find the remainder when the first number is divided by sencond(dont use mod function) 
    DECLARE
A NUMBER:=&A;
B NUMBER:=&B;
C NUMBER;
M NUMBER;
BEGIN
C:=TRUNC(A/B);
M:=A-C*B;
DBMS_OUTPUT.PUT_LINE('REMAINDER IS '||M);
END;
  104. WAP to display all the ASCII characters 0-9--48-57,A-Z--65-90,a-z--97-122 
    BEGIN
FOR I IN 1..255
LOOP
DBMS_OUTPUT.PUT_LINE(I||'-'||CHR(I));
END LOOP;
END;
  105. Print the following format 
    ORACLE
ORACL
ORAC
ORA
OR
O
DECLARE
STR VARCHAR2(10):='&STR';
L VARCHAR2(10);
N NUMBER(15);
BEGIN
N:=LENGTH(STR);
WHILE N>=1
LOOP
L:=SUBSTR(STR,1,N);
N:=N-1;
DBMS_OUTPUT.PUT_LINE(L);
END LOOP;
END;
  106. WAP to display "GOOD MORNING" or "GOOD AFTERNOON" or "GOOD NIGHT" depending upon the current time 
    DECLARE
HH NUMBER;
BEGIN
HH:=TO_CHAR(SYSDATE,'HH24');
IF HH>6 AND HH<12 THEN
DBMS_OUTPUT.PUT_LINE('GOOD MORNING');
ELSIF HH>=12 AND HH<18 THEN
DBMS_OUTPUT.PUT_LINE('GOOD AFTERNOON');
ELSIF HH>=18 AND HH<25 THEN
DBMS_OUTPUT.PUT_LINE('GOOD NIGHT');
END IF;
END;
  107. WAP to accept two strings and concat the two strings 
    DECLARE
STR VARCHAR2(20):='&STR';
STR1 VARCHAR2(20):='&STR1';
V VARCHAR2(40);
BEGIN
V:=STR||''||STR1;
DBMS_OUTPUT.PUT_LINE(V);
END;
  108. WAP to accept a string and count the no of chars,words in that string 
    DECLARE
STR VARCHAR2(20):='&STR';
NOC NUMBER(4):=0;
NOW NUMBER(4):=1;
S CHAR;
BEGIN
FOR I IN 1..LENGTH(STR)
LOOP
S:=SUBSTR(STR,I,1);
NOC:=NOC+1;
IF S=' ' THEN
NOW:=NOW+1;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('THE NO. OF CHARS '||NOC);
DBMS_OUTPUT.PUT_LINE('THE NO. OF WORDS '||NOW);
END;
  109. WAP to accept the octal number and print it in decimal format 
    DECLARE
N VARCHAR2(20):='&N';
A NUMBER;
P NUMBER:=0;
C CHAR;
BEGIN
A:=LENGTH(N);
FOR I IN 1..A
LOOP
C:=SUBSTR(N,I,1);
P:=P+C*POWER(8,A-I);
END LOOP;
DBMS_OUTPUT.PUT_LINE('THE INTEGER OF A GIVEN OCTAL IS '||P);
END;
  110. WAP to accept the mgr and find how many emps are working under that mgr 
    DECLARE
MGRV EMP.MGR%TYPE:=&MGRNO;
N NUMBER:=0;
BEGIN
SELECT COUNT(*) INTO N FROM EMP WHERE MGR=MGRV;
DBMS_OUTPUT.PUT_LINE('NUMBER OF EMPLOYEE UNDER THAT MANAGER ARE '||N);
END;
  111. WAP to accept the empno and update the employee row on the following 
    If sal < 2600 then sal=sal+10% of sal make the changes in the emp table
DECLARE
EMPNOV EMP.EMPNO%TYPE:=&EMPNO;
SALV NUMBER(7,2):=0;
BEGIN
SELECT SAL INTO SALV FROM EMP WHERE EMPNO=EMPNOV;
IF SALV < 2600 THEN
SALV:=SALV+SALV*(10/100);
END IF;
UPDATE EMP SET SAL=SALV WHERE EMPNO=EMPNOV;
DBMS_OUTPUT.PUT_LINE('EMPNO IS '||EMPNOV);
DBMS_OUTPUT.PUT_LINE('SAL IS '||SALV);
END;
  112. Write the floyd's triangle
    1
    2 3
    4 5 6
    7 8 9 10
    11 12 13 14 15
    16 17 18 19 20 21
    ...............
    79..............91 
    DECLARE
N NUMBER:=1;
V VARCHAR2(100);
BEGIN
FOR I IN 1..92
LOOP
FOR J IN 1..I
LOOP
V:=V||' '||N;
N:=N+1;
EXIT WHEN N=92;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
EXIT WHEN N=92;
V:=NULL;
END LOOP;
END;
  113. WAP to accept the real value and print integer value only 
    DECLARE
N NUMBER(7,3):=&N;
A NUMBER(5);
BEGIN
A:=TRUNC(N);
DBMS_OUTPUT.PUT_LINE('REAL VALUE IS '||A);
END;
  114. WAP to calculate the sum of n odd factorials 
    DECLARE
N NUMBER:=&N;
S NUMBER:=0;
F NUMBER:=1;
BEGIN
FOR I IN 1..N
LOOP
IF MOD(I,2)!=0 THEN
FOR J IN 1..I
LOOP
F:=F*J;
END LOOP;
S:=S+F;
F:=1;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('SUM '||S);
END;
  115. WAP to calculate the sum of n even factorials 
    DECLARE
N NUMBER:=&N;
S NUMBER:=0;
F NUMBER:=1;
BEGIN
FOR I IN 1..N
LOOP
IF MOD(I,2)=0 THEN
FOR J IN 1..I
LOOP
F:=F*J;
END LOOP;
S:=S+F;
F:=1;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('SUM '||S);
END;
  116. WAP to generate the nos which are prime and odd between 1 and 100 
    DECLARE
N NUMBER;
CNT NUMBER:=0;
BEGIN
FOR I IN 1..100
LOOP
FOR J IN 1..I
LOOP
IF MOD(I,J)=0 THEN
CNT:=CNT+1;
END IF;
END LOOP;
IF CNT <= 2 THEN
IF MOD(I,2)!=0 THEN
DBMS_OUTPUT.PUT_LINE(I);
END IF;
END IF;
CNT:=0;
END LOOP;
END;
  117. Write a program to generate following series
    12
    12 22
    12 22 32
    12 22 32 42
    12 22 32 42 52 
    DECLARE
V VARCHAR2(20);
BEGIN
FOR I IN 1..5
LOOP
FOR J IN 1..I
LOOP
V:=V||' '||J||CHR(178);
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
V:=NULL;
END LOOP;
END;
  118. Find the roots of a quadratic equation 
    DECLARE
A NUMBER(4):=&A;
B NUMBER(4):=&B;
C NUMBER(4):=&C;
D NUMBER(8,2);
R1 NUMBER(8,2);
R2 NUMBER(8,2);
BEGIN
D:=POWER(B,2)-4*A*C;
IF D = 0 THEN
DBMS_OUTPUT.PUT_LINE('ROOTS ARE EQUAL');
ELSIF D > 0 THEN
R1:=(-B+SQRT(D))/2*A;
R2:=(-B-SQRT(D))/2*A;
DBMS_OUTPUT.PUT_LINE('FIRST ROOT IS '||R1);
DBMS_OUTPUT.PUT_LINE('SECOND ROOT IS '||R2);
ELSE
DBMS_OUTPUT.PUT_LINE('ROOTS ARE IMAGINARY');
END IF;
END;
  119. WAP to accept the 2 diff nos, assume that first one is smaller and second one is highest value then print the all even nos in between them horizontally 
    DECLARE
A NUMBER:=&A;
B NUMBER:=&B;
V VARCHAR2(100);
BEGIN
FOR I IN A..B
LOOP
IF MOD(I,2)=0 THEN
V:=V||' '||I;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
END;
  120. WAP to accept two diff nos assume that first one is smaller and second one is highest value then print the all odd nos in between them horizontally 
    DECLARE
A NUMBER:=&A;
B NUMBER:=&B;
V VARCHAR2(100);
BEGIN
FOR I IN A..B
LOOP
IF MOD(I,2)!=0 THEN
V:=V||' '||I;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
END;
  121. Write a program to accept a year and display the emps belongs to that year? 
    DECLARE
Y NUMBER(4):=&YEAR;
CURSOR YEAR IS
SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,'YYYY')=Y;
B YEAR%ROWTYPE;
BEGIN
OPEN YEAR;
LOOP
FETCH YEAR INTO B;
EXIT WHEN YEAR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('EMP NO IS ' || B.EMPNO);
DBMS_OUTPUT.PUT_LINE('EMP NAME IS ' || B.ENAME);
DBMS_OUTPUT.PUT_LINE('EMP SAL IS ' || B.SAL);
DBMS_OUTPUT.PUT_LINE('HIREDATE IS ' || B.HIREDATE);
DBMS_OUTPUT.PUT_LINE('EMP JOB IS ' || B.JOB);
DBMS_OUTPUT.PUT_LINE('*************************');
END LOOP;
CLOSE YEAR;
END;
  122. Write a program to accept a mgr and display who are working under that mgr? 
    DECLARE
MGRV NUMBER(4):=&MGR;
CURSOR AMGR IS
SELECT * FROM EMP WHERE MGR=MGRV;
B AMGR%ROWTYPE;
BEGIN
OPEN AMGR;
LOOP
FETCH AMGR INTO B;
EXIT WHEN AMGR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('EMP NO IS ' || B.EMPNO);
DBMS_OUTPUT.PUT_LINE('EMP NAME IS ' || B.ENAME);
DBMS_OUTPUT.PUT_LINE('EMP SAL IS ' || B.SAL);
DBMS_OUTPUT.PUT_LINE('HIREDATE IS ' || B.HIREDATE);
DBMS_OUTPUT.PUT_LINE('EMP JOB IS ' || B.JOB);
DBMS_OUTPUT.PUT_LINE('*************************');
END LOOP;
CLOSE AMGR;
END;
  123. Write a program to accept the grade and display emps belongs to that grade? 
    DECLARE
GRADEV SALGRADE.GRADE%TYPE:=&GRADE;
CURSOR A IS
SELECT EMP.*,GRADE FROM EMP,SALGRADE WHERE SAL BETWEEN LOSAL AND HISAL AND GRADE=GRADEV;
B A%ROWTYPE;
BEGIN
OPEN A;
LOOP
FETCH A INTO B;
EXIT WHEN A%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('EMP NO IS ' || B.EMPNO);
DBMS_OUTPUT.PUT_LINE('ENAME IS ' || B.ENAME);
DBMS_OUTPUT.PUT_LINE('SAL IS ' || B.SAL);
DBMS_OUTPUT.PUT_LINE('MGR NO IS ' || B.MGR);
DBMS_OUTPUT.PUT_LINE('COMM IS ' || B.COMM);
DBMS_OUTPUT.PUT_LINE('HIREDATE IS ' || B.HIREDATE);
DBMS_OUTPUT.PUT_LINE('GRADE IS ' || B.GRADE);
DBMS_OUTPUT.PUT_LINE('EMP JOB IS ' || B.JOB);
DBMS_OUTPUT.PUT_LINE('*************************');
END LOOP;
CLOSE A;
END;
  124. Write a program to accept a deptno and display who are working in that dept? 
    DECLARE
DEPTV EMP.DEPTNO%TYPE:=&DEPTNO;
CURSOR A IS
SELECT * FROM EMP WHERE DEPTNO=DEPTV;
B A%ROWTYPE;
BEGIN
OPEN A;
LOOP
FETCH A INTO B;
EXIT WHEN A%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('EMP NO IS ' || B.EMPNO);
DBMS_OUTPUT.PUT_LINE('ENAME IS ' || B.ENAME);
DBMS_OUTPUT.PUT_LINE('SAL IS ' || B.SAL);
DBMS_OUTPUT.PUT_LINE('MGR NO IS ' || B.MGR);
DBMS_OUTPUT.PUT_LINE('COMM IS ' || B.COMM);
DBMS_OUTPUT.PUT_LINE('HIREDATE IS ' || B.HIREDATE);
DBMS_OUTPUT.PUT_LINE('DEPTNO IS ' || B.DEPTNO);
DBMS_OUTPUT.PUT_LINE('EMP JOB IS ' || B.JOB);
DBMS_OUTPUT.PUT_LINE('*************************');
END LOOP;
CLOSE A;
END;
  125. Write a program to display all the information of emp table? 
    DECLARE
CURSOR A IS
SELECT * FROM EMP;
B A%ROWTYPE;
BEGIN
OPEN A;
LOOP
FETCH A INTO B;
EXIT WHEN A%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('EMP NO IS ' || B.EMPNO);
DBMS_OUTPUT.PUT_LINE('ENAME IS ' || B.ENAME);
DBMS_OUTPUT.PUT_LINE('SAL IS ' || B.SAL);
DBMS_OUTPUT.PUT_LINE('MGR NO IS ' || B.MGR);
DBMS_OUTPUT.PUT_LINE('COMM IS ' || B.COMM);
DBMS_OUTPUT.PUT_LINE('HIREDATE IS ' || B.HIREDATE);
DBMS_OUTPUT.PUT_LINE('DEPTNO IS ' || B.DEPTNO);
DBMS_OUTPUT.PUT_LINE('EMP JOB IS ' || B.JOB);
DBMS_OUTPUT.PUT_LINE('*************************');
END LOOP;
CLOSE A;
END;
  126. Write a program to accept the location and display empno, name, sal , date of join and also display the total salary, avg salary and no of emps? 
    DECLARE
LOCV DEPT.LOC%TYPE:='&LOC';
TOT NUMBER(10,2):=0;
ASAL NUMBER(10,2):=0;
NOEMPS NUMBER(5):=0;
CURSOR A IS
SELECT EMP.*,LOC FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND LOC=LOCV;
B A%ROWTYPE;
BEGIN
OPEN A;
LOOP
FETCH A INTO B;
NOEMPS:=NOEMPS+1;
TOT:=TOT+B.SAL;
ASAL:=TOT/NOEMPS;
EXIT WHEN A%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('EMP NO IS ' || B.EMPNO);
DBMS_OUTPUT.PUT_LINE('ENAME IS ' || B.ENAME);
DBMS_OUTPUT.PUT_LINE('SAL IS ' || B.SAL);
DBMS_OUTPUT.PUT_LINE('HIREDATE IS ' || B.HIREDATE);
DBMS_OUTPUT.PUT_LINE('DEPTNO IS ' || B.DEPTNO);
DBMS_OUTPUT.PUT_LINE('EMP JOB IS ' || B.JOB);
DBMS_OUTPUT.PUT_LINE('LOC IS ' || B.LOC);
DBMS_OUTPUT.PUT_LINE('TOT IS ' || TOT);
DBMS_OUTPUT.PUT_LINE('NOEMPS IS ' || NOEMPS);
DBMS_OUTPUT.PUT_LINE('ASAL IS ' || ASAL);
DBMS_OUTPUT.PUT_LINE('*************************');
END LOOP;
CLOSE A;
END;
  127. Write a program to accept a range of salary (that is lower boundary and higher boundary) and print the details of emps along with loc,grade and exp? 
    DECLARE
LOSALV SALGRADE.LOSAL%TYPE:=&LOSAL;
HISALV SALGRADE.HISAL%TYPE:=&HISAL;
EXP NUMBER(5,2);
CURSOR A IS
SELECT EMP.*,LOC,GRADE FROM EMP,DEPT,SALGRADE WHERE EMP.DEPTNO=DEPT.DEPTNO
AND SAL BETWEEN LOSALV AND HISALV
AND SAL BETWEEN LOSAL AND HISAL;
B A%ROWTYPE;
BEGIN
OPEN A;
LOOP
FETCH A INTO B;
EXIT WHEN A%NOTFOUND;
EXP:=MONTHS_BETWEEN(SYSDATE,B.HIREDATE)/12;
DBMS_OUTPUT.PUT_LINE('EMP NO IS ' || B.EMPNO);
DBMS_OUTPUT.PUT_LINE('ENAME IS ' || B.ENAME);
DBMS_OUTPUT.PUT_LINE('EMP JOB IS ' || B.JOB);
DBMS_OUTPUT.PUT_LINE('LOC IS ' || B.LOC);
DBMS_OUTPUT.PUT_LINE('EXP IS ' || EXP);
DBMS_OUTPUT.PUT_LINE('GRADE IS ' || B.GRADE);
DBMS_OUTPUT.PUT_LINE('*************************');
END LOOP;
CLOSE A;
END;
  128. Write a program to print all the details of emps accepting the job? 
    DECLARE
JOBV EMP.JOB%TYPE:='&JOB';
CURSOR A IS
SELECT * FROM EMP WHERE JOB=JOBV;
B A%ROWTYPE;
BEGIN
OPEN A;
LOOP
FETCH A INTO B;
EXIT WHEN A%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('EMP NO IS ' || B.EMPNO);
DBMS_OUTPUT.PUT_LINE('ENAME IS ' || B.ENAME);
DBMS_OUTPUT.PUT_LINE('EMP JOB IS ' || B.JOB);
DBMS_OUTPUT.PUT_LINE('*************************');
END LOOP;
CLOSE A;
END;
  129. Write a program to display the details of emps year wise? 
    DECLARE
CURSOR YEARS IS
SELECT DISTINCT TO_CHAR(HIREDATE,'YYYY') YEARS1 FROM EMP ORDER BY 1;
YEAR YEARS%ROWTYPE;
CURSOR A IS
SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,'YYYY')=YEAR.YEARS1;
B A%ROWTYPE;
BEGIN
DBMS_OUTPUT.ENABLE(10000);
OPEN YEARS;
DBMS_OUTPUT.PUT_LINE('********************');
LOOP
FETCH YEARS INTO YEAR;
EXIT WHEN YEARS%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('YEAR :' || YEAR.YEARS1);
DBMS_OUTPUT.PUT_LINE('**********************');
OPEN A;
LOOP
FETCH A INTO B;
EXIT WHEN A%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('EMPNO IS ' || B.EMPNO);
DBMS_OUTPUT.PUT_LINE('ENAME IS ' || B.ENAME);
DBMS_OUTPUT.PUT_LINE('SALARY IS ' || B.SAL);
DBMS_OUTPUT.PUT_LINE('JOB IS ' || B.JOB);
DBMS_OUTPUT.PUT_LINE('DEPTNO IS ' || B.DEPTNO);
DBMS_OUTPUT.PUT_LINE('*************************');
END LOOP;
CLOSE A;
END LOOP;
CLOSE YEARS;
END;
  130. Write a program to accept empno and print all the details along with loc and grade? 
    DECLARE
EMPNOV EMP.EMPNO%TYPE:=&EMPNO;
CURSOR A IS
SELECT EMP.*,GRADE,LOC FROM EMP,DEPT,SALGRADE
WHERE EMP.DEPTNO=DEPT.DEPTNO
AND SAL BETWEEN LOSAL AND HISAL AND EMPNO=EMPNOV;
B A%ROWTYPE;
BEGIN
OPEN A;
LOOP
FETCH A INTO B;
EXIT WHEN A%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('EMPNO IS ' || B.EMPNO);
DBMS_OUTPUT.PUT_LINE('ENAME IS ' || B.ENAME);
DBMS_OUTPUT.PUT_LINE('SALARY IS ' || B.SAL);
DBMS_OUTPUT.PUT_LINE('JOB IS ' || B.JOB);
DBMS_OUTPUT.PUT_LINE('HIREDATE IS ' || B.HIREDATE);
DBMS_OUTPUT.PUT_LINE('LOC IS ' || B.LOC);
DBMS_OUTPUT.PUT_LINE('GRADE IS ' || B.GRADE);
DBMS_OUTPUT.PUT_LINE('*************************');
END LOOP;
CLOSE A;
END;
  131. Write a procedure to create your own print statement? 
    CREATE OR REPLACE PROCEDURE PRINT(V VARCHAR2)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE(V);
END;
  132. Write a procedure to accept the deptno as parameter and display the details of that dept also display the total salary, no of employees, max sal and avg sal? 
    CREATE OR REPLACE PROCEDURE EMPPRO(DEPTNOV NUMBER)
IS
CURSOR A IS
SELECT * FROM EMP WHERE DEPTNO=DEPTNOV;
B A%ROWTYPE;
NOE NUMBER:=0;
TOT NUMBER:=0;
AVGS NUMBER(7,2):=0;
MAXS NUMBER(7,2):=0;
BEGIN
OPEN A;
LOOP
FETCH A INTO B;
EXIT WHEN A%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('EMPNO :'||B.EMPNO);
DBMS_OUTPUT.PUT_LINE('ENAME :'||B.ENAME);
DBMS_OUTPUT.PUT_LINE('JOB :'||B.JOB);
DBMS_OUTPUT.PUT_LINE('SAL :'||B.SAL);
DBMS_OUTPUT.PUT_LINE('HIREDATE :'||B.HIREDATE);
DBMS_OUTPUT.PUT_LINE('COMM :'||B.COMM);
DBMS_OUTPUT.PUT_LINE('**********************');
TOT:=TOT+B.SAL;
NOE:=NOE+1;
IF B.SAL>MAXS THEN
MAXS:=B.SAL;
END IF;
END LOOP;
AVGS:=TOT/NOE;
DBMS_OUTPUT.PUT_LINE('NO OF EMPLOYEE :'||NOE);
DBMS_OUTPUT.PUT_LINE('TOTAL SALARY :'||TOT);
DBMS_OUTPUT.PUT_LINE('AVG SALARY :'||AVGS);
DBMS_OUTPUT.PUT_LINE('MAX SALARY :'||MAXS);
CLOSE A;
END;
  133. Write a procedure to accept two different numbers and print all odd numbers between the two given numbers? 
    CREATE OR REPLACE PROCEDURE ODDNO(A NUMBER,B NUMBER)
IS
N NUMBER(4);
BEGIN
N:=A;
WHILE N<B
LOOP
IF MOD(N,2)!=0 THEN
DBMS_OUTPUT.PUT_LINE(N);
END IF;
N:=N+1;
END LOOP;
END;
  134. Write a procedure to accept two different numbers and print even numbers between the two given numbers? 
    CREATE OR REPLACE PROCEDURE EVENNO(A NUMBER,B NUMBER)
IS
N NUMBER(4);
BEGIN
N:=A;
WHILE N<B
LOOP
IF MOD(N,2)=0 THEN
DBMS_OUTPUT.PUT_LINE(N);
END IF;
N:=N+1;
END LOOP;
END;
  135. Write a procedure to accept deptno as input and print the details of emps along with grade? 
    CREATE OR REPLACE PROCEDURE EMP_DETAIL(DEPTNOV NUMBER)
IS
CURSOR A IS
SELECT EMP.*,GRADE FROM EMP,SALGRADE
WHERE SAL BETWEEN LOSAL AND HISAL
AND DEPTNO=DEPTNOV;
B A%ROWTYPE;
BEGIN
OPEN A;
LOOP
FETCH A INTO B;
EXIT WHEN A%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('EMPNO IS '||B.EMPNO);
DBMS_OUTPUT.PUT_LINE('ENAME IS '||B.ENAME);
DBMS_OUTPUT.PUT_LINE('JOB IS '||B.JOB);
DBMS_OUTPUT.PUT_LINE('SAL IS '||B.SAL);
DBMS_OUTPUT.PUT_LINE('DEPTNO IS '||B.DEPTNO);
DBMS_OUTPUT.PUT_LINE('GRADE IS '||B.GRADE);
END LOOP;
CLOSE A;
END;
  136. Write a procedure to accept a number as parameter and print its multiplication table? 
    CREATE OR REPLACE PROCEDURE MULT(A NUMBER)
IS
B NUMBER(2) DEFAULT 1;
C NUMBER(3);
BEGIN
WHILE B<=10
LOOP
C:=A*B;
DBMS_OUTPUT.PUT_LINE(A||'*'||B||'='||C);
B:=B+1;
END LOOP;
END;
  137. Write a procedure to accept two different numbers as input and print all even numbers and odd numbers in between them in two different horizontal lines? 
    CREATE OR REPLACE PROCEDURE EVENODD(A NUMBER,B NUMBER)
IS
N NUMBER;
EV VARCHAR2(1000);
OD VARCHAR2(1000);
BEGIN

N:=A;
WHILE N<B
LOOP
IF MOD(N,2)!=0 THEN
OD:=OD||' '||N;
ELSE
EV:=EV||' '||N;
END IF;
N:=N+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('THE ODD NOS ARE '||OD);
DBMS_OUTPUT.PUT_LINE('THE EVEN NOS ARE '||EV);
END;
  138. Write a procedure to accept a string and check whether it is palindrome or not? 
    CREATE OR REPLACE PROCEDURE STRPAL(STR VARCHAR2)
IS
STR1 VARCHAR2(10);
S VARCHAR2(10);
BEGIN
FOR I IN REVERSE 1..LENGTH(STR)
LOOP
S:=SUBSTR(STR,I,1);
STR1:=STR1||S;
END LOOP;
IF STR1=STR THEN
DBMS_OUTPUT.PUT_LINE('IT IS PALINDROME '||STR1);
ELSE
DBMS_OUTPUT.PUT_LINE('IT IS NOT PALINDROME '||STR1);
END IF;
END;
  139. Write a procedure to accept a string and print it in reverse order? 
    CREATE OR REPLACE PROCEDURE STRREV(STR VARCHAR2)
IS
STR1 VARCHAR2(10);
S VARCHAR2(10);
BEGIN
FOR I IN REVERSE 1..LENGTH(STR)
LOOP
S:=SUBSTR(STR,I,1);
STR1:=STR1||S;
END LOOP;
DBMS_OUTPUT.PUT_LINE('ORIGINAL '||STR);
DBMS_OUTPUT.PUT_LINE('REVERSE '||STR1);
END;
  140. Write a procedure to accept the empno and print all the details of emp along with exp, grade and loc? 
    CREATE OR REPLACE PROCEDURE EMP_DET(EMPNOV NUMBER)
IS
EXP NUMBER(6,2);
E EMP%ROWTYPE;
GRADEV SALGRADE.GRADE%TYPE;
LOCV DEPT.LOC%TYPE;
BEGIN
SELECT EMP.* INTO E FROM EMP WHERE EMPNO=EMPNOV;
SELECT LOC INTO LOCV FROM DEPT WHERE DEPT.DEPTNO=E.DEPTNO;
SELECT GRADE INTO GRADEV FROM SALGRADE WHERE E.SAL BETWEEN LOSAL AND HISAL;
EXP:=MONTHS_BETWEEN(SYSDATE,E.HIREDATE)/12;
DBMS_OUTPUT.PUT_LINE('EMPNO IS '||E.EMPNO);
DBMS_OUTPUT.PUT_LINE('ENAME IS '||E.ENAME);
DBMS_OUTPUT.PUT_LINE('SAL IS '||E.SAL);
DBMS_OUTPUT.PUT_LINE('JOB IS '||E.JOB);
DBMS_OUTPUT.PUT_LINE('LOC IS '||LOCV);
DBMS_OUTPUT.PUT_LINE('GRADE IS '||GRADEV);
DBMS_OUTPUT.PUT_LINE('EXP IS '||EXP);
END;
  141. Write a procedure to accept dname irrespective of case and print all the details of emps? 
    CREATE OR REPLACE PROCEDURE DETAILS(DNAMEV VARCHAR2)
IS
CURSOR A IS
SELECT EMP.*,DNAME FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND DNAME=DNAMEV;
B A%ROWTYPE;
BEGIN
OPEN A;
LOOP
FETCH A INTO B;
EXIT WHEN A%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('EMPNO IS '||B.EMPNO);
DBMS_OUTPUT.PUT_LINE('ENAME IS '||B.ENAME);
DBMS_OUTPUT.PUT_LINE('SAL IS '||B.SAL);
DBMS_OUTPUT.PUT_LINE('JOB IS '||B.JOB);
DBMS_OUTPUT.PUT_LINE('DNAME IS '||B.DNAME);
DBMS_OUTPUT.PUT_LINE('HIREDATE IS '||B.HIREDATE);
END LOOP;
END;
  142. Write a procedure to accept a string and print it in reverse case? 
    CREATE OR REPLACE PROCEDURE S_R_CASE(STR VARCHAR2)
IS
S VARCHAR2(10);
V VARCHAR2(10);
N NUMBER(3);
BEGIN
FOR I IN 1..LENGTH(STR)
LOOP
S:=SUBSTR(STR,I,1);
N:=ASCII(S);
IF N BETWEEN 65 AND 90 THEN
V:=V||CHR(N+32);
ELSE
V:=V||CHR(N-32);
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('STRING IN REVERSE CASE IS '||V);
END;
  143. Write a function to accept the empno and return exp with minimum 3 decimal? 
    CREATE OR REPLACE FUNCTION E_DETAILS(EMPNOV NUMBER) RETURN NUMBER
IS
HIREDATEV EMP.HIREDATE%TYPE;
EXP NUMBER(6,3);
BEGIN
SELECT HIREDATE INTO HIREDATEV FROM EMP WHERE EMPNO=EMPNOV;
EXP:=MONTHS_BETWEEN(SYSDATE,HIREDATEV)/12;
RETURN EXP;
END;
  144. Write a function to accept a number and print the factorial of that number? 
    CREATE OR REPLACE FUNCTION FAC(NUM NUMBER) RETURN NUMBER
IS
FACT NUMBER(4):=1;
BEGIN
FOR I IN REVERSE 1..NUM
LOOP
FACT:=FACT*I;
END LOOP;
RETURN FACT;
END;
  145. Write a function to accept a grade and return the number of emps belongs to that grade? 
    CREATE OR REPLACE FUNCTION EMPGRADE(GRADEV NUMBER) RETURN VARCHAR2
IS
N NUMBER(4);
BEGIN
SELECT COUNT(*) INTO N FROM EMP,SALGRADE
WHERE SAL BETWEEN LOSAL AND HISAL AND GRADE=GRADEV;
RETURN 'NO OF EMPS ARE'||N;
END;
  146. Write a program to accept the mgr number and return no of emp working at that mgr? 
    CREATE OR REPLACE FUNCTION N_EMPS(MGRV NUMBER) RETURN VARCHAR2
IS
N NUMBER(4);
BEGIN
SELECT COUNT(*) INTO N FROM EMP WHERE MGR=MGRV;
RETURN 'THE NO OF EMPS ARE WORKING UNDER THIS MGR IS '||N;
END;
  147. Write a function to accept a character string and print it in reverse case? 
    CREATE OR REPLACE FUNCTION REVERSE(STR VARCHAR2) RETURN VARCHAR2
IS
STR1 VARCHAR2(20);
S VARCHAR2(20);
N NUMBER(4);
BEGIN
FOR I IN 1..LENGTH(STR)
LOOP
S:=SUBSTR(STR,I,1);
N:=ASCII(S);
IF N BETWEEN 65 AND 90 THEN
STR1:=STR1||CHR(N+32);
ELSE
STR1:=STR1||CHR(N-32);
END IF;
END LOOP;
RETURN 'THE REVERSE CASE IS '||STR1;
END;
  148. Write a function to accept a string and check whether it is palindrome or not? 
    CREATE OR REPLACE FUNCTION STRPAL1(STR VARCHAR2) RETURN VARCHAR2
IS
STR1 VARCHAR2(10);
S VARCHAR2(10);
BEGIN
FOR I IN REVERSE 1..LENGTH(STR)
LOOP
S:=SUBSTR(STR,I,1);
STR1:=STR1||S;
END LOOP;
IF STR1=STR THEN
RETURN 'IT IS PALINDROME '||STR1;
ELSE
RETURN 'IT IS NOT PALINDROME '||STR1;
END IF;
END;
  149. Write a function to accept the grade and return max, tot, avg salary and number of emps belongs to that grade as script without using any group functions? 
    CREATE OR REPLACE FUNCTION EMP_DETAILS_SCRIPT (GRADEV SALGRADE.GRADE%TYPE) RETURN VARCHAR2
IS
V VARCHAR2(30000);
CURSOR EMP_CUR IS
SELECT EMP.*,GRADE,DNAME FROM DEPT,EMP,SALGRADE
WHERE GRADE=GRADEV AND EMP.DEPTNO=DEPT.DEPTNO AND
SAL BETWEEN LOSAL AND HISAL;
EMP_CUR_V EMP_CUR%ROWTYPE;
MAXSAL EMP.SAL%TYPE:=0;
MINSAL EMP.SAL%TYPE;
AVGSAL NUMBER(6,2);
SUMSAL NUMBER(10,2):=0;
CNT NUMBER:=0;
FLAG CHAR:=0;
EX EXCEPTION;
BEGIN
OPEN EMP_CUR;
LOOP
FETCH EMP_CUR INTO EMP_CUR_V;
EXIT WHEN EMP_CUR%NOTFOUND;
IF MAXSAL < EMP_CUR_V.SAL THEN
MAXSAL:=EMP_CUR_V.SAL;
END IF;
IF FLAG=0 THEN
MINSAL:=EMP_CUR_V.SAL;
FLAG:=1;
ELSIF FLAG=1 AND MINSAL > EMP_CUR_V.SAL THEN
MINSAL:=EMP_CUR_V.SAL;
END IF;
SUMSAL:=SUMSAL+EMP_CUR_V.SAL;
CNT:=CNT+1;
ENDLOOP;
IF CNT=0 THEN
RAISE EX;
END IF;
AVGSAL:=SUMSAL/CNT;
V:='THE MAXIMUM SALARY OF GRADE' ||GRADEV||' IS'||MAXSAL||' MINIMUM SALARY IS'||MINSAL||
'AVERAGE SALARY IS'||AVGSAL||' TOTAL EMPS WORKING FOR THIS GRADE ARE'||CNT;
CLOSE EMP_CUR;
RETURN V;
EXCEPTION
WHEN EX THEN
RETURN 'THERE IS NO EMPLOYEE WORKING FOR THIS GRADE, CHECK AND RE-ENTER THE GRADE....';
END;
  150. Create a package to store the following procedure for multiplication table,even-odd, function for factorial and function for palindrome? 
    CREATE OR REPLACE PACKAGE DATA
IS
PROCEDURE MULT(A NUMBER);
PROCEDURE EVEN_ODD(N NUMBER);
FUNCTION FACT(N NUMBER) RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES(FACT,WNDS);
FUNCTION PALEN(SRT VARCHAR2) RETURN VARCHAR2;
PRAGMA RESTRICT_REFERENCES(PALEN,WNDS);
END;
⁄
CREATE OR REPLACE PACKAGE BODY DATA
IS
PROCEDURE MULT(A NUMBER)
IS
M NUMBER;
BEGIN
FOR I IN 1..10
LOOP
M:=A*I;
DBMS_OUTPUT.PUT_LINE(A||'*'||I||'='||M);
END LOOP;
END;
PROCEDURE EVEN_ODD(N NUMBER)
IS
BEGIN
IF MOD(N,2)=0 THEN
DBMS_OUTPUT.PUT_LINE(N||' IS EVEN NUMBER');
ELSE
DBMS_OUTPUT.PUT_LINE(N||' IS NOT EVEN NUMBER');
END IF;
END;
FUNCTION FACT(N NUMBER) RETURN NUMBER
IS
F NUMBER:=1;
BEGIN
FOR I IN 1..N
LOOP
F:=F*I;
END LOOP;
RETURN F;
END;
FUNCTION PALEN(SRT VARCHAR2) RETURN VARCHAR2
IS
S CHAR;
V VARCHAR2(50);
BEGIN
FOR I IN REVERSE 1..LENGTH(SRT)
LOOP
S:=SUBSTR(SRT,I,1);
V:=V||S;
END LOOP;
IF V=SRT THEN
RETURN 'PALINDROME';
ELSE
RETURN 'NOT PALINDROME';
END IF;
END;
END;
  151. Write a database trigger halt the transaction on Sunday on EMP table 
    CREATE OR REPLACE TRIGGER SUN_TRI
AFTER INSERT OR UPDATE OR DELETE ON EMP
DECLARE
DY VARCHAR2(200);
BEGIN
DY:=TO_CHAR(SYSDATE,'DY');
IF DY='SUN' THEN
RAISE_APPLICATION_ERROR(-20005,'TODAY IS SUNDAY TRANSACTION NOT ALLOWED TODAY');
END IF;
END;
  152. Write a database trigger halt the transaction of USER SCOTT on table EMP 
    CREATE OR REPLACE TRIGGER SCOTT_TRI
BEFORE INSERT OR UPDATE OR DELETE ON EMP
BEGIN
IF USER = 'SCOTT' THEN
RAISE_APPLICATION_ERROR(-20006,'TRANSACTION NOT ALLOWED FOR SCOTT');
END IF;
END;
  153. Write a database trigger halt the transaction between the the time 6pm to 10am on table emp 
    CREATE OR REPLACE TRIGGER OVER_TIME_TRI
BEFORE INSERT OR DELETE OR UPDATE ON EMP
DECLARE
T NUMBER;
BEGIN
T:=TO_CHAR(SYSDATE,'HH24');
IF T NOT BETWEEN 10 AND 18 THEN
RAISE_APPLICATION_ERROR(-20007,'TIME ALREADY OVER.....TRANSACTION NOT ALLOWED NOW');
END IF;
END;
154.Write a database trigger to halt the transaction for the employee SALESMAN and
PRESIDENT
CREATE OR REPLACE TRIGGER SALES_PRI
BEFORE INSERT OR UPDATE OR DELETE ON EMP
FOR EACH ROW
WHEN (OLD.JOB IN ('SALESMAN','PRESIDENT') OR
NEW.JOB IN ('SALESMAN','PRESIDENT'))
BEGIN
RAISE_APPLICATION_ERROR(-20008,'TRANSACTION NOT ALLOWED FOR SALESMAN AND PRESIDENT....');
END;
  154. Write a database trigger stroe the username ,type of transaction ,date of transaction and time of transaction of table emp into the table EMP_LOG 
    CREATE OR REPLACE TRIGGER TRANS_TYPE
AFTER INSERT OR UPDATE OR DELETE ON EMP
DECLARE
V VARCHAR2(50);
BEGIN
IF INSERTING THEN
V:='I';
ELSIF UPDATING THEN
V:='U';
ELSE
V:='D';
END IF;
INSERT INTO EMP_LOG VALUES (USER,V,SYSDATE,TO_CHAR(SYSDATE,'HH:MI:SS'));
END;
  155. Write a database trigger store the deleted data of EMP table in EMPDEL table 
    CREATE OR REPLACE TRIGGER DEL_TRI
BEFORE DELETE ON EMP
FOR EACH ROW
BEGIN
INSERT INTO EMPDEL
VALUES (:OLD.EMPNO,:OLD.ENAME,:OLD.JOB,:OLD.MGR,:OLD.HIREDATE,:OLD.SAL,:OLD.COMM,
:OLD.DEPTNO,SYSDATE,TO_CHAR(SYSDATE,'HH:MI:SS));
END;
  156. Write a database trigger display the message when the inserting hiredate is greater than system date 
    CREATE OR REPLACE TRIGGER HIREDATE_OVER
AFTER INSERT ON EMP
FOR EACH ROW
BEGIN
IF :NEW.HIREDATE > SYSDATE THEN
RAISE_APPLICATION_ERROR(-20009,'INVALID HIREDATE.....');
END IF;
END;
  157. Write a database trigger halt the transaction of EMP table if the deptno is does not exist in the dept table 
    CREATE OR REPLACE TRIGGER DEPT_NO
BEFORE INSERT OR UPDATE OR DELETE ON EMP
FOR EACH ROW
DECLARE
DNO NUMBER:=0;
BEGIN
SELECT COUNT(*) INTO DNO FROM DEPT WHERE DEPTNO=:NEW.DEPTNO;
DBMS_OUTPUT.PUT_LINE(DNO);
IF DNO=0 THEN
RAISE_APPLICATION_ERROR(-20009,'DEPTNO NOT EXIST IN DEPT TABLE....');
END IF;
END;
  158. Write a database trigger add Rs 500 if the inserting salary is less than Rs 1000 
    CREATE OR REPLACE TRIGGER SAL_ADD
BEFORE INSERT ON EMP
FOR EACH ROW
BEGIN
IF :NEW.SAL <= 1000 THEN
:NEW.SAL:=:NEW.SAL+500;
END IF;
END;
  159. Write a database trigger give the appropriate message if the record exceed more than 100 on EMP table 
    CREATE OR REPLACE TRIGGER EMP_OVER_REC
AFTER INSERT ON EMP
DECLARE
R NUMBER;
BEGIN
SELECT COUNT(*) INTO R FROM EMP;
IF R>=100 THEN
RAISE_APPLICATION_ERROR(-20009,'100 RECORD ALLOWED IN EMP TABLE.....');
END IF;
END;
  160. Write a program to month and year and display the Calendar of that month. 
    DECLARE
D NUMBER:=1;
M VARCHAR2(10):='&MONTH';
Y NUMBER:=&YEAR;
C CHAR(20);
V VARCHAR2(500);
N NUMBER;
BEGIN
N:=TO_CHAR(LAST_DAY(D||'-'||M||'-'||Y),'DD');
C:= TO_CHAR(TO_DATE(D||'-'||M||'-'||Y),'DY');
dbms_output.put_line('*********************************');
dbms_output.put_line('* '||M||'-'||Y||' *');
dbms_output.put_line('*SUN MON TUE WED THU FRI SAT *');
dbms_output.put_line('**********************************');
IF C='MON' THEN
V:=' ';
ELSIF C='TUE' THEN
V:=' ';
ELSIF C='WED' THEN
V:=' ';
ELSIF C='THU' THEN
V:=' ';
ELSIF C='FRI' THEN
V:=' ';
ELSIF C='SAT' THEN
V:=' ';
END IF;
FOR I IN 1..N
LOOP
V:=V||LPAD(I,4);
IF LENGTH(V)=28 THEN
dbms_output.put_line(LPAD(V,29,'*')||' *');
V:=NULL;
END IF;
END LOOP;
dbms_output.put_line('*'||RPAD(V,29)||'*');
END;
Posted by at No comments:

PL/SQL


  • It is a programming language which is used to define our own logics.
  • It is used execute block of statements at a time and increase the performance.
  • It supports variables and conditional statements and loops.
  • It supports object oriented programming and supports composite data types.
  • It supports handle the error handling mechanism.
  •  Block
    • It is one of the area which is used to write a programming logic.
    • This block is have 3 sections.
      • Declaration Section
      • Executable Section
      • Exception Section
    1. Declaration Section
      • It is one of the section which is used declare variables, cursors and exceptions and so on.
      • It is optional section.
    2. Executable Section
      • It is one of the section which is used to write a program coding.
      • It is mandatory section.
    3. Exception Section
      • It is one of the section which is used to handle the errors at runtime.
      • It is optional section.
    • There are two types of blocks are supported by pl/sql.
      • Anonymous Block
      • Named Block
      1. Anonymous Block
        • These blocks does not have a name and also not stored in database.
        Example : 
Declare
-------------
Begin
-------------
-------------
End;
Example  1: 
Begin
Dbms_Output.Put_Line(‘welcome to E Business Solutions’ );
End;
      2. Named Block
        • These blocks are having a name and also stored in database.
          Examples :  Procedures , Functions, Packages and Triggers etc..
          • Variable
            • It is one of thememory location which is used to store the data.
            • Generally we aredeclare the variables in declaration section.
            • These are supporteddefault and not null.
              Syntax :Variable_Name Datatype ( Size ); 
              Example : 
Declare
A Number ( 5 );
B Number ( 5 )  not null :=10;
C Number ( 5 ) default  10;
              Example  1: 
Declare
A Varchar2(20);
Begin
A := ‘Hello EBS’;
Dbms_Output.Put_Line( A );
End;
          • Storing a value into variable
            • Using assignment operator ( := ) we storing a value into variable. Syntax : Variable_Name := value;
               
              Example : a :=50;
          • Display Message ( or ) Varaible Value
            • We have one pre defined package which is used display the message or value in a program.
              Syntax : dbms_output.put_line ( ‘message’ );
              dbms_output.put_line ( variable_name );
          • Select ------ Into ------ Clause
            • This clause is used to retrieve the data from table & storing into pl/sql variables.
              Syntax : select col1, col2 into var1, var2;
  •  DataTypes
    • % Type
    • % RowType
    • RecordType ( or ) Pl/sql Record
    • IndexBy Table ( or ) Pl/sql Table
      1. %Type:
        • It is one of thedatatype which is used to assign the column datatype to a variable.
        • It is used to storeone value at a time.
        • It is not possibleto hold more than one column values or row values.
          Syntax : variable_name table_name.column_name%type; 
          Example  1:
Declare
Vno emp.empno%type:=&n;
Vname emp.ename%type;
Begin
Select
ename into vname from emp where empno=vno;
Dbms_output.put_line ( ‘ employee name is : ‘ || ‘ ‘ || vname );
End;
      2. % RowType
        • It is one of thedatatype which is used assign all the column datatypes of table to a variable.
        • It holds entirerecord of the same table.
        • Each of the time it override only one record.
        • It is not possibleto capture the more than one table data.
          Syntax :variable_name table_name%rowtype; 
          Example  1:
Declare 
Vrow emp%rowtype;
Vno emp.empno%type:=&n;
Begin

Select * into vrow from emp where empno=vno;
Dbms_output.put_line ( vrow.ename || ‘ ‘ || vrow.sal );
End;
      3. Record Type ( or ) Pl/Sql Record
        • Is is one of the user defined temporary data type which is used to store more than one table data ( or ) to assign more than one column datatypes.
        • They must at least contain one element.
        • Pinpoint of data is not possible.
          Syntax : Type Typename is Record ( Val-1 Datatype, Val-2 Datatype,…..);
          Var Typename           
          Example :
Declare
Type Rec is record ( vname emp.ename%type, 
     Vsal     emp.sal%type,
                    VLoc    dept.loc%type);
Vrec Rec;
Vno emp.empno%type:=&n;
Begin
Select ename,sal,loc into vrec from emp,dept where emp.deptno=dept.deptno and emp.empno=vno;
Dbms_output.put_line(vrec.vname||’,’||vrec.vsal||’,’||vrec.vloc);
End;
  •  Conditional Statements
    • If Condition
    • If Else Condition
    • Elsif Condition
    • Case Condition
    1. If Condition
      Syntax :If condition then
      Statements;
      End if;       
      Example  1:
Declare
A Number ( 4 ) :=&n;
B Char ( 1 );
Begin
If a<20 then
B:=’Yes’;
End if;
Dbms_output.put_line ( B  );
End;
    2. If Else Condition
      Syntax : If condition then
      Statements ;
      Else
      Statements ;
      End if; 
      Example  1:
Declare
A Number ( 4 ) :=&n;
B Char ( 10 );
Begin
If  a<20 then
B:=’TRUE’;
Else
B:=’FALSE’;
End if;
Dbms_output.put_line ( B );
End;
    3. Elsif Condition
      Syntax : If condition-1 then
      Statements;
      Elsif condition-2 then
      Statements;
      Elsif condition-3 then
      Statements;
      Else
      Statements;
      End if;       
      Example  1:
Declare
A Number ( 4 ) :=&n;
B Char ( 15 );
Begin
If a<20 then
B:=’Low Value’;
Elsif a>20 and a<100 then
B:=’High Value’;
Else
B:=’Invalid Value’;
End if;
Dbms_output.put_line ( B );
End;
    4. Case Condition
      Syntax : Case ( column name )
      When condition then
      Statements;
      When condition then
      Statements;
      Else
      Statements;
      End Case;       
      Example  1:
DECLARE
VSAL NUMBER(10):=&N;
BEGIN
CASE
WHEN VSAL<2000 THEN
DBMS_OUTPUT.PUT_LINE('VSAL IS'||' '||'LOW');
WHEN VSAL>2000 THEN
DBMS_OUTPUT.PUT_LINE('VSAL IS'||' '||'HIGH');
ELSE
DBMS_OUTPUT.PUT_LINE('VSAL IS'||' '||'INVALID');
END CASE;
END;
  •  Loops
    • Simple Loop
    • While Loop
    • For Loop
    1. Simple Loop
      Syntax :
      Loop
      Statements;
      End loop;
      Syntax :
      Loop
      Code;
      Exit when condition;
      End loop; 
      Example  1:
Begin
Loop
Dbms_output.put_line ( ‘Welcome to k-onlines.com' );
End loop;
End;
      Example 2 :
Declare
N number(5):=1;
Begin
Loop
Dbms_output.put_line ( n );
Exit when n>=10;
N:=n+1;
End loop;
End; 
       Example 3 : 
Declare
N  number(5):=1;
Begin
Loop
Dbms_output.put_line ( n );
If n>=10 then
Exit;
End if;
N:=N+1;
End loop;
End;
    2. While Loop
      Syntax : While ( Condition )
      Loop
      Statements;
      End loop; 
      Example  1: 
Declare
N  Number(4):=1;
Begin
While n>=10
Loop 
Dbms_output.put_line ( N );
N:=N+1;
End loop;
End;
    3. For Loop
      Syntax : For variable_name in lowerbound..outerbound
      Loop
      Statements;
      End loop; 
      Example  1:
Declare
N number(5);
Begin
For n     in 1..10
Loop
Dbms_output.put_line ( N );
End loop;
End;
      Example 2 : 
Declare
N  number(5);
Begin
For n     in  reverse  1..10
Loop
Dbms_output.put_line ( N );
End loop;
End;
  •  Bind Variable
    • These variables are session variable.
      Syntax : variable a number; 
      Example  1: 
sql> Variable V Number;
Sql> Declare
 A  number(5):=500;
     Begin
        :v:=a/2;
        End;
Sql> Print V;
  •  CURSORS
    • Cursor is a buffer area which is used to process multiple records and also record by record by process.
    • There are two types
      1. Implicit Cursors
      2. Explicit Cursors
        1. Implicit Cursors
          • Sql statements returns a single record is called implicit cursors
          • Implicit cursor operations done by the system.
          • Open by the system.
          • Fetch the records by the system
          • Close by the system.
          Example :
Declare
X  emp%rowtype;
Begin
Select * into X from emp where empno=7369;
Dbms_output.put_line(x.empno||’,’||x.ename);
End;
        2. Explicit Cursors
          • Sql statements return a multiple records is called explicit cursors
          • Explicit cursor operations done by the user.
          • Declare by the user
          • Open by the user
          • Fetch the records by the user
          • Close by the user
          Example  1:
Declare
Cursor c1 is select ename,sal from emp;
V_Name varchar2(10);
V_Sal  number(10);
Begin
Open C1;
Fetch c1 into v_name,v_sal;
Dbms_output.put_line(v_name||’,’||v_sal);
Close C1;
End;

          Example 2 :
Declare
Cursor c1 is select ename,job from emp;
Vvname varchar2(10);
Job   varchar2(10);
Begin
Open c1;
Fetch c1 into vname,job;
Dbms_output.put_line(vname||','||job);
Fetch c1 into vname,job;
Dbms_output.put_line(vname||','||job);
Close c1;
End;

          Example 3 :
Declare
Ccursor c1 is select ename,job from emp;
Vname varchar2(10);
Vjob      varchar2(10);
Begin
Open c1;
Loop
Fetch c1 into vname,vjob;
Dbms_output.put_line(vname||','||vjob);
End loop;
Close c1;
End;
  •  CURSOR Attributes
    • Every explicit cursor having following four attributes
      1. %NotFound
      2. %Found
      3. %Isopen
      4. %Rowcount
    • All these cursor attributes using along with cursor name only Syntax :cursorname % attributename
      Note : Except %rowcount all other cursor attribute records Boolean value return either true or false where as %rowcount return number datatupe.
    1. %NotFound
      • Returns INVALID_CURSOR if cursor is declared, but not open or if cursor has been closed.
      • Returns NULL if cursor is open, but fetch has not been executed.
      • Returns FALSE if a successful fetch has been executed.
      • Returns TRUE if no row was returned.
      Example  1:
Declare
Ccursor c1 is select ename,job from emp;
Vname varchar2(10);
Vjob      varchar2(10);
Begin
Open c1;
Loop
Fetch c1 into vname,vjob;
Exit when c1%notfound;
Dbms_output.put_line(vname||','||vjob);
End loop;
Close c1;
End;
    2. %Found
      • Returns INVALID_CURSOR if cursor is declared, but not open or if cursor has been closed.
      • Returns NULL if cursor is open, but fetch has not been executed.
      • Returns TRUE if a successful fetch has been executed.
      • Returns FALSE if no row was returned. 
      Example  1:
Declare
Cursor c1 is select * from emp;
I emp%rowtype;
Begin
Open c1;
Loop
Fetch c1 into i;
If c1%found then
Dbms_output.put_line(i.empno||','||i.ename);
Else
Exit;
End if;
End loop;
Close c1;
End;
    3. %IsOpen
      • Returns TRUE if the cursor is open,
      • Retuens FALSE if the cursor is closed. 
      
Example  1:
Declare
Cursor c1 is select * from emp;
I emp%rowtype;
Begin
Open c1;
If c1%isopen then
Dbms_output.put_line('cursor is open');
Loop
Fetch c1 into i;
If c1%found then
Dbms_output.put_line(i.ename);
Else
Exit;
End if;
End loop;
Close c1;
If not c1%isopen then
Dbms_output.put_line('cursor is closed');
End if;
End if;
End;
    4. %Rowcount
      • Returns INVALID_CURSOR if cursor is declared, but not open or if cursor has been closed.
      • Returns the number of rows fetched by the cursor. 
      
Example  1:
Declare
Cursor c1 is select * from emp;
I emp%rowtype;
Begin
Open c1;
Loop
Fetch c1 into i;
Exit when c1%notfound;
Dbms_output.put_line(i.empno||','||i.ename);
End loop;
Dbms_output.put_line('Total no of employee: '|| c1%rowcount);
Close c1; 
End;
  •  PARAMETER CURSOR
    • Passing a parameter in cursor is call it as a parameter cursor.
      • Syntax : Cursor cursor_name ( parameter_name ) is select * from table_name where column_name=parameter_name
      Example  1:
Declare
Cursor c1 (p_deptno number) is select * from emp where deptno=p_deptno;
I emp%rowtype;
Begin
Open c1(10);
Loop
Fetch c1 into i;
Exit when c1%notfound;
Dbms_output.put_line(i.ename);
End loop;
Close c1;
End;

      Example 2 :
Declare
Cursor c1 ( p_job varchar2) is select * from emp where job=p_job;
I emp%rowtype;
Begin
Open c1('MANAGER');
Loop
Fetch c1 into i;
Exit when c1%notfound;
Dbms_output.put_line(i.empno||','||i.ename||','||i.job);
End loop;
Close c1;
Open c1('CLERK');
Loop
Fetch c1 into i;
Exit when c1%notfound;
Dbms_output.put_line(i.empno||','||i.ename||','||i.job);
End loop;
Close c1;
End;
  •  CURSOR WITH FOR Loop
    • In cursor for loop no need to open, fetch, close the cursor. For loop it self automatically will perform these functionalities
    Example  1:
Declare
Cursor c1 is select * from emp;
I emp%rowtype;
Begin
For i in c1 loop
Dbms_output.put_line(i.empno||','||i.ename);
End loop;
End;
  •  NESTED CURSOR WITH FOR Loop 
    Example 2 :
Declare
Cursor c1 is select * from dept;
Cursor c2(p_deptno number) is select * from emp where deptno=p_deptno;
Begin
For i in c1
Loop
Dbms_output.put_line(i.deptno);
For j in c2(i.deptno)
Loop
Dbms_output.put_line(j.empno||','||j.ename||','||j.sal);
End loop;
End loop;
End;
  •  CURSOR WITH DML Operations 
    Example  1:
Declare
Cursor c1 is select * from emp;
Begin
For i in c1
Loop
Insert into t1 values (i.ename,i.sal);
End loop;
End;

    Example 2 :
Declare
Cursor c1 is select * from t1;
Begin
For i in c1
Loop
Delete from t1 where sal=3000;
End loop;
End;

    Example 3 :
Declare
Cursor c1 is select * from kuncham;
Begin
For i in c1
Loop
If i.job='CLERK' then
Update kuncham set sal=i.sal+1111 where empno=i.empno;
Elsif i.job='MANAGER' then
Update kuncham set sal=i.sal+2222 where empno=i.empno;
End if;
End loop;
End;
  •  Ref Cursor
    • Ref Cursors are user define types which is used to process multiple records and also this is record by record process
    • Generally through the static cursors we are using only one select statement at a time for single active set area where as in ref cursors we are executing no of select statements dynamically for single active set area.
    • Thats why these type of cursors are also called as dynamic cursors.
    • By using ref cursors we return large amount of data from oracle database into client applications.
    • There are 2 Types
      • Strong Ref Cursor
      • Weak Ref Cursor
        • Strong Ref Cursor
          • It is one of the ref cursor which is having return type.
        • Weak Ref Cursor
          • It is one of the ref cursor which does not have a return type.
    Note : In ref cursor we are executing select statements using open .... for statement.
    Example 1 : 
Declare
Type t1 is ref cursor;
v_t t1;
i emp%rowtype;
begin
open v_t for select * from emp where sal>2000;
loop
fetch v_t into i;
exit when v_t%notfound;
dbms_output.put_line(i.ename||' '||i.sal);
end loop;
close v_t;
end;

    Example 2 :
Declare
type t1 is ref cursor;
v_t t1;
i emp%rowtype;
j dept%rowtype;
v_no number(5):=&no;
begin
if v_no=1 then
open v_t for select * from emp;
loop
fetch v_t into i;
exit when v_t%notfound;
dbms_output.put_line(i.ename||' '||i.deptno);
end loop;
close v_t;
elsif v_no=2 then
open v_t for select * from dept;
loop
fetch v_t into j;
exit when v_t%notfound;
dbms_output.put_line(j.deptno||' '||j.dname);
end loop;
close v_t;
end if;
end;

    Example 3 :
create or replace package pg1
is
type t1 is ref cursor return emp%rowtype;
type t2 is ref cursor return dept%rowtype;
procedure p1 (p_t1 out t1);
procedure p2 (p_t2 out t2);
end;


create or replace package body pg1 is
procedure p1 (p_t1 out t1)
is
begin
open p_t1 for select * from emp;
end p1;
procedure p2 (p_t2 out t2)
is
begin
open p_t2 for select * from dept;
end p2;
end;


Execution 
variable a refcursor
variable b refcursor
exec pg1.p1(:a);
exec pg1.p2(:b);
print a b;
  •  Where Current of and For Update Clause
    • Generally when we are using update, delete statements automatically locks are generated in the data base.
    • If you want to generate locks before update, delete statements then we are using cursor locking mechanism in all data base systems.
    • In this case we must specify for update clause in cursor definition.
      • Syntax : Cursor Cursor_Name is select * from Table_Name where condition for update
    • If you are specifying for update clause also oracle server does not generate the lock i.e whenever we are opening the cursor then only oracle server internally uses exclusive locks.
    • After processing we must release the locks using commit statement.
    • where current of clause uniquely identifying a record in each process because where current of clause internally uses ROWID.
    • Whenever we are using where current of clause we must use for update clause.
    Example :
Declare
cursor c1 is select * from k for update;
i emp%rowtype;
begin
open c1;
loop
fetch c1 into i;
exit when c1%notfound;
if i.job='CLERK' then
update k set sal=i.sal+1000 where current of c1;
end if;
end loop;
commit;
close c1;
end;
  •  EXCEPTIONS
    • Exception is one of the activity which is used to handle the errors at runtime.
    • There are 3 types of exceptions
      1. Predefined Exception
      2. Userdefined Exception
      3. Unnamed Exception
    1. Predefined Exception
      • It is one of the exception which are defined by oracle.
      • There are 20 exceptions available.
        Syntax : when exception1 then
        statements;
        when exception2 then
        statements;
        when others then
        statements;
        • Predefined Exceptions are
          1. no_data_found
          2. too_many_rows
          3. invalid_cursor
          4. cursor_already_open
          5. invalid_number
          6. value_error
          7. zero_devide
          8. others
            etc.....
          1. No_Data_Found
            • When a pl/sql block contains select ------into clause and also if requested data not available in a table oracle server returns an error.
            • Error is ora-01403 : no data found
            • To handle this error we are using no_data_found exception.
            
Example : 
Declare
v_ename varchar2(20);
v_sal   number(10);
begin
select ename,sal into v_ename,v_sal from k where empno=&no;
dbms_output.put_line(v_ename||' '||v_sal);
        end;

            
Example : 
Declare
v_ename varchar2(20);
v_sal   number(10);
begin
select ename,sal into v_ename,v_sal from k where empno=&no;
dbms_output.put_line(v_ename||' '||v_sal);
 exception
when no_data_found then
dbms_output.put_line('employee does not exit');
end;
          2. Too_Many_Rows
            • When a select ------into clause try to return more than one record or more than one value then oracle server return an error.
            • Error is ora-01422 : exact fetch returns more than requested number of rows.
            • To handle this error we are using too_many_rows exception
            Example :
Declare
v_ename varchar2(20);
v_sal   number(10);
begin
select ename,sal into v_ename,v_sal from k;
dbms_output.put_line(v_ename||' '||v_sal);
        end;

            Example : 
Declare
v_ename varchar2(20);
v_sal   number(10);
begin
select ename,sal into v_ename,v_sal from k;
dbms_output.put_line(v_ename||' '||v_sal);
exception
when too_many_rows then
dbms_output.put_line('program return more than one row');
end;
          3. Invalid_Cursor
            • Whenever we are performing invalid operations on the cursor server returns an error i.e if you are try to close the cursor with out opening cursor then oracle server returns an error.
            • Error is ora-01001 : invalid cursor
            • To handle this error we are using invalid_cursor exception.
            Example : 
Declare
cursor c1 is select * from emp;
i emp%rowtype;
begin
loop
fetch c1 into i;
exit when c1%notfound;
dbms_output.put_line(i.ename||i.sal);
end loop;
close c1;
end;

            Example :
Declare
cursor c1 is select * from emp;
i emp%rowtype;
begin
loop
fetch c1 into i;
exit when c1%notfound;
dbms_output.put_line(i.ename||i.sal);
end loop;
close c1;
exception
when invalid_cursor then
dbms_output.put_line('first you open the cursor');
end;
          4. Cursor_Already_Open
            • When we are try to reopen the cursor without closing the cursor oracle server returns an error.
            • Error is ora-06511 : cursor already open
            • To handle this error we are using cursor_already_open exception
            Example : 
cursor c1 is select * from emp;
i emp%rowtype;
begin
open c1;
loop
open c1;
fetch c1 into i;
exit when c1%notfound;
dbms_output.put_line(i.ename||i.sal);
end loop;
close c1;
end;

            Example :
Declare
cursor c1 is select * from emp;
i emp%rowtype;
begin
open c1;
loop
open c1;
fetch c1 into i;
exit when c1%notfound;
dbms_output.put_line(i.ename||i.sal);
end loop;
close c1;
exception
when cursor_already_open then
dbms_output.put_line('cursor already open');
end;
          5. Invalid_Number
            • Whenever we are try to convert string type to number type oracle server return error.
            • Error is ora-01722 : invalid number
            • To handle this error we are using invalid_error exception
            Example : 
Begin
insert into emp(empno,sal) values (111,'abcd');
end;

            Example :
Begin
insert into emp(empno,sal) values (111,'abcd');
exception
when invalid_number then
dbms_output.put_line('insert proper data only');
end;
          6. Value_Error
            • Whenever we are try to convert string type to number type based on the condition then oracle server returns an error
            • Whenever we are try to store large amount of data than the specified data type size in varaible declaration then oracle server return same error
            • Error is ora-06502 : numeric or value error: character to number conversion error
            • To handle this error we are using value_error exception
            Example : 
Declare
z number(10);
begin
z:='&x'+'&y';
dbms_output.put_line(z);
end;

            Example :
Declare
z number(10);
begin
z:='&x'+'&y';
dbms_output.put_line(z);
exception
when value_error then
dbms_output.put_line('Enter the proper data only');
end;

            Example : 
Declare
z number(3);
begin
z:='abcd';
dbms_output.put_line(z);
end;
          7. Zero_Devide
            • Whenever we are try to divide by zero then oracle server return a error
            • Error is ora-01476 : divisor is equal to zero
            • To handle this error we are using zero_divide exception
              • Example : 
Declare
a number(10);
b number(10):=&b;
c number(10):=&c;
begin
  a:=b/c;
dbms_output.put_line(a);
end;

              Example : 
Declare
a number(10);
b number(10):=&b;
c number(10):=&c;
begin
a:=b/c;
dbms_output.put_line(a);
exception
when zero_divide then
dbms_output.put_line('c does not contain zero');
end;
          • EXCEPTION PROPAGATION
            • Exceptions are also raised in
            • Declaration Section
            • Executable Section
            • Exception Section
          • If the exceptions are raised in executable section those exceptions are handled using either inner block or an outer block.
          • Where as if exception are raised in declaration section or in exception section those exceptions are handled using outer blocks only.
            • Example :
Begin
declare
z varchar2(3);--:='abcd';
begin
z:='abcd';
dbms_output.put_line(z);
exception
when value_error then
dbms_output.put_line('invalid string lenght');
end;
exception 
when value_error then
dbms_output.put_line('the lenght is more');
end;
    2. Userdefined Exception
      • We can also create our own exception names and also raise whenever it is necessary. these types of exceptions are called user defined exceptions.
      • These exceptions are divided into 3 steps
        1. Declare Exception
        2. Raise Exception
        3. Handle Exception
      1. Declare Exception
        • In declare section of the pl/sql program we are defining our own exception name using exception type.
          • Syntax : userdefinedexception_name exception
          Example :
    Declare
    a   exception;
      2. Raise Exception
        • Whenever it is required raise user defined exception either in executable section or in exception section, in this case we are using raise keyword.
          • Syntax : raise userdefinedexception_name
          Example :
Declare
a exception;
begin
raise a;
end;
      3. Handle Exception
        • We can also handle user defined exceptions as same as predefined exception using predefined handler.
          • Syntax :
          when userdefinedexception_name1 then
          statements;
          when userdefinedexception_name2 then
          statements;
          ----------
          ----------
          when others then
          statements;
                                             Example :
Declare
a  exception;
begin
if to_char(sysdate,'dy')='sun' then
raise a;
end if;
exception
when z then
dbms_output.put_line('my exception raised today');
end;

          Ex:
Declare
v_sal number(10);
a exception;
begin
select sal into v_sal from k where empno=7902;
if v_sal>2000 then
raise a;
else
update k set sal=sal+100 where empno=7902;
end if;
exception
when a then
dbms_output.put_line('salary alredy high');
end;
      • RIASING Predefined Exception
        • We can also raise the exception in exception section
          • Example :
Ddeclare
a1 exception;
a2 exception;
begin
begin
raise a1;
exception
when a1 then
dbms_output.put_line('a1 handled');
--raise a2;
end;
exception
when a2 then
dbms_output.put_line('a2 handled');
end;
      • ERROR Trapping Functions
        • There are two error trapping functions supported by oracle.
          1. SQL Code
          2. SQL Errm
          1. SQL Code : It returns numbers
          2. SQL Errm : It returns error number with error message.
          Example :
Declare
v_sal number(10);
begin
select sal into v_sal from emp where empno=7369;
dbms_output.put_line(sqlcode);
dbms_output.put_line(sqlerrm);
end;
      • RAISE APPLICATION ERROR
        • If you want to display your own user defined exception number and exception message then we can use this raise application error
          • Syntax : raise_application_error ( error_number,error_message );Error_Number : It is used to give the error numbers between -20000 to -20999
          Error_Message : It is used to give the message upto 512 characters.
          Example :
Declare
v_sal number(10);
a exception;
begin
select sal into v_sal from k where empno=7369;
if v_sal < 2000 then
raise a;
else
update k set sal=sal+100 where empno=7369;
end if;
exception
when a then
raise_application_error ( -20999,'salary alreday high');
end;
    3. Unnamed Exception
      • If you want to handle other than oracle 20 predefined errors we are using unnamed method.
      • Because oracle define exception names for regularly accured errors other than 20 they are not defining exception names.
      • In this case we are providing exception names and also associate this exception name with appropriate error no using exception_init function.
        • Syntax : pragma exception_init ( userdefined_exception_name, error_number );
      • Here pragma is a compiler directive i.e at the time of compilation only pl/sql runtime engine associate error number with exception name.
      • This function is used in declare section of the pl/sql block.
      Example :
Declare
v_no number(10);
e  exception;
pragma exception_init(e,-2291);
begin
select empno into v_no from emp where empno=&no;
dbms_output.put_line(v_no);
exception
when e then
dbms_output.put_line('pragma error');
end;
  •  SUB PROGRAMS
    • Sub programs are named pl/sql blocks which is used to solve particular task.
    • There are two types of sub programs supported by oracle.
      1. Procedures
      2. Functions
      1. Procedures
        • Procedures may or may not return a value.
        • Procedures return more than one value while using the out parameter.
        • Procedure can execute only 3 ways
          1. Anonymous Block
          2. Exec
          3. Call
        • Procedure can not execute in select statement.
        • Procedure internally having one time compilation process.
        • Procedure are used to improve the performance of business applications
        • Every procedure is having two parts
          1. Procedure Specification
            • In procedure specification we are specifying name of the procedure and types of the parameters.
          2. Procedure Body
            • In procedure body we are solving the actual task.
        Example : 
create or replace procedure p11(p_empno number) is
v_ename varchar2(10);
v_sal   number(10);
begin
select ename,sal into v_ename,v_sal from emp where empno=p_empno;
dbms_output.put_line(v_ename||','||v_sal);
end;
        • Execute The Procedure in 3 ways
          • Method : 1 - Exec P11 ( 7902 )
          • Method : 2 - Begin
            P11 ( 7902 );
            end;
          • Method : 3 - Call P11 ( 7902 )
          • Example :
create or replace procedure p111(p_deptno number) is
cursor c1 is select * from emp where deptno=p_deptno;
i emp%rowtype;
begin
open c1;
loop
fetch c1 into i;
exit when c1%notfound;
dbms_output.put_line(i.ename||','||i.sal||','||i.deptno);
end loop;
close c1;
end;
        • Parameters in Procedures
          • Parameters are used to pass the value into procedures and also return values from the procedure.
          • In this case we must use two types of parameters
            1. Formal Parameters
            2. Actual Parameters
            1. Formal Parameters
              • Formal Parameters are defined in procedure specification
              • In Formal Parameters we are defining parameter name & mode of the parameter
              • There are three types of modes supported by oracle.
                1. IN Mode
                2. OUT Mode
                3. INOUT Mode
                1. IN Mode :
                  • By default procedure parameters having IN mode.
                  • IN Mode is used to pass the values into procedure body.
                  • This mode behaves like a constant in procedure body, through this IN Mode we can also pass default values using default or ":=" operator 
                    Example :  
Create or replace procedure P1 ( p_deptno in number,
p_dname in varchar2,
p_loc in varchar2)
is
begin
insert into dept values (p_deptno,p_dname,p_loc);
dbms_output.put_line('record is inserted through procedure');
end;
                  • There are three types of execution methods supported by in parameter.
                    1. Positional Notations
                    2. Named Notations
                    3. Mixed Notations
                    1. Positional Notations 
                      Example : exec p1( 1, 'a','b');
                    2. Named Notations 
                      Example : exec p1 ( p_dname=>'x', p_loc=>'y', p_deptno=>2 )
                    3. Mixed Notations 
                      Example : exec p1 ( 1, p_dname=>'m', p_loc=>'n' );
                2. OUT Mode :
                  • This mode is used to return values from procedure body.
                  • OUT Mode internally behaves like a uninitialized variable in procedure body
                  Example :
Create or replace procedure p1 (a in number, b out number) is
begin
b:=a*a;
dbms_output.put_line(b);
end;
                  Note : In oracle if a subprogram contains OUT, INOUT Parameters those subprograms are executed using following two methods.
                  1. Method - 1 : Using Bind Variable
                  2. Method - 2 : Using Annonymous Block
                  • Bind Variable:
                    • These variables are session variables.
                    • These variables are created at host environment that's why these variables are also called as host variables.
                    • These variables are not a pl/sql variables, but we can also use these variables in PL/SQL to execute subprograms having OUR Parameters.
                  • Method - 1 : Bind Variable 
                    Example : Variable b number;
exec p1 ( 10, :b);
                  • Method - 2 : Annonymous Block 
                    Example :Declare
b   number(10);
begin
p1( 5, b )
dbms_output.put_line( b );
end;
                                
                    Example :Develop a program for passing employee name as
 in parameter return salary of that employee using out parameter from emp table?
Prog :Create or replace procedure p1 ( p_ename in varchar2, p_sal out number ) is
    begin
    select sal  into p_sal from emp where empno=p_ename;
    end;
                  • Method - 1 :Bind variable
                    • variable a number;
exec p1 ( 'KING', :a);
                  • Method - 2 : Annonymous Block
                    • Declare
a  number(10);
begin
exec p1( ' ALLEN ', a );
dbms_output.put_line( a );
end;

                    Example :
Develop a program for passing deptno as 
a parameter return how many employees are working in a dept from emp table?

                    Prog :
Create or replace procedure pe2 ( p_deptno in number, p_t out number) is
begin
select count(*) into p_t from emp where deptno=p_deptno;
dbms_output.put_line(p_t);
end;
                3. IN OUT Mode
                  • This mode is used to pass the values into sub program & return the values from sub programs.
                    • Example : 
Create or replace procedure p1 ( a in out number ) is 
begin
a := a*a;
dbms_output.put_line ( a );
end;
                    • Method - 1 :Bind Variable
                      • Variable a number;
exec  :a :=10;
exec  p1 ( :a );
                    • Method - 2 : Annonymous Block
                      • Declare
a  number(10) := &n;
begin
p1( a );
dbms_output.put_line ( a );
end;

                      Example :
Create or replace procedure pe4 ( a in out number) is
begin
select sal into a from emp where empno=a;
dbms_output.put_line( a );
end;
          • PRAGMA AUTONOMOUS TRANSACTION
            • Autonomous transactions are independent transactions used in either procedures or in triggers.
            • Generally autonomous transactions are used in child procedures, These procedures are not effected from the main transactions when we are using commit or rollback.
              • Example : Create table test ( name varchar2(10));
Program : Create or replace procedure P1 is
   pragma autonomous_transaction;
   begin
   insert into ptest values ('india');
   commit;
   end;

Execute The Program: Begin
   insert into ptest values ('usa');
   insert into ptest values ('uk');
   P1;
   rollback;
   end;

With out Autonomous Transaction
Program : Create or replace procedure P1 is
   begin
   insert into ptest values ('india');
   commit;
   end;
   Execute The Program: Begin
   insert into ptest values ('usa');
   insert into ptest values ('uk');
   P1;
   rollback;
   end;
      2. Functions
        • Function is a named pl/sql block which is used to solve particular task and by default functions return a single value.
        • Function is allow to write multiple return statements but it execute only first return statement.
        • Function can execute in 4 ways
          1. Annonymous Block
          2. Select Statement
          3. Bind Variable
          4. Exec
        • Function also having two parts
          1. Function Specification
          2. Function Body
        • In Function Specification we are specifying name of the function and type of the parameters where as in function body we are solving the actual task.
          • Example : Create or replace function fun1( a varchar2)
 return varchar2
 is
 begin
 return a;
 end;
    
Method - 1 : Select Clause
  Select fun1('hi') from dual
 
Method - 2 :Annonymous Block
 Declare
 a   varchar2(10);
 begin
 a :=fun1('hi');
 dbms_output.put_line(a);
 end;

Method - 3 : Bind Variable
 Variable  V  Varchar2(20);
 Begin
 :a:=fun1('hi');
 end;
 
Method - 4 :  Exec
   Exec  Dbms_output.put_line(fun1('hi'));
Example : Create or replace function fun2 (a number)
 return varchar2
 is
 begin
 if mod(a,2)=0 then
 return 'even number';
 else
 return 'odd number';
 end if;
 end;
        
Note : We can also use user defined function in insert statement.
 
Example : Create table t1(sno number(10), msg varchar2(10));
 Insert into t1 values ( 1, fun2(5));
 Select * from t1;


Example : Write a pl/sql stored function for passing empno as parameter return 
gross salary from emp table based on following condition?
 Condition => gross:=basic+hra+da+pf;
 hra =>  10% of Sal
 da => 20% of Sal
 pf => 10% of Sal
 
Prog : Create or replace function fun3 (p_empno number)
 return number
 is
 vsal number(10);
 gross number(10);
 hra number(10);
 da number(10);
 pf number(10);
 begin
 select sal into vsal from emp where empno=p_empno;
 hra:=vsal*0.1;
 da:=vsal*0.2;
 pf:=vsal*0.1;
 gross:=vsal+hra+da+pf;
 return gross;
 end;

Note : We can also use predefined functions in user defined functions and also this user defined 
  functions in same table or different table.

Example : Create or replace function fm
  return number
  is
  vsal number(10);
  begin
  select max(sal) into vsal from emp;
  return vsal;
  end;

Note : If we want to return more number of values from function we are using OUT Parameter.

Example : Create or replace function fun4
 (p_deptno in number
 ,p_dname  out varchar2
 ,p_loc    out varchar2)
 return varchar2
 is
 begin
 select dname,loc into p_dname,p_loc from dept where deptno=p_deptno;
 return p_dname;
 end;

 Variable  a  varchar2(10);
 Variable  b  varchar2(10);
 Variable  c  varchar2(10);
  
 Begin
 :a:=fun4 ( 10, :b, :c);
 end;
  
 Print b c;

Example : Write a pl/sql stored function for passing empno,date as 
parameter return number of years that employee is working based on date from emp table?

Prog : Create or replace function fun5(p_empno number,p_date date)
 return number
 is
 a number(10);
 begin
 select months_between(p_date,hiredate)/12 into a from emp where empno=p_empno;
 return (round(a));
 end;
  
Execution :  Select empno,ename,hiredate,
 fun5(empno,sysdate)||'Years' Exp
 from emp where empno=7902

Example : Write a pl/sql stored function for passing empno as parameter,
calculate tax based on following conditions by using emp table.
 Conditions: 
    1) if annual salary >10000 then tax=10%
 2) if annual salary >20000 then tax=20%
 3) if annual salary >50000 then tax=30%

Prog : Create or replace function fun7 (p_empno number)
 return number
 is
 vsal number(10);
 asal number(10);
 itax number(10);
 begin
 select sal into vsal from emp where empno=p_empno;
 asal:=vsal*12;
 if asal>10000 and asal<=15000 then
 itax:=asal*0.1;
 elsif asal>15000 and asal<=2000 then
 itax:=asal*0.2;
 elsif asal>20000 then
 itax:=asal*0.3;
 else
 itax:=0;
 end if;
 return itax;
 end;
  •  Packages
    • Package is a database object which is used encapsulate variables, constants, procedures, cursors, functions, types in to single unit.
    • Packages does not accepts parameters, can not be nested, can not be invoked.
    • Generally packages are used to improve performance of the application because when we are calling packaged sub program first time total package automatically loaded into memory area.
    • Whenever we are calling subsequent sub program calls pl/sql run time engine calling those sub program from memory area.
    • This process automatically reduces disk I/O that's why packages improves performance of the application.
    • Packages have two types.
      1. Package Specification
      2. Package Body
    • In Package Specification we are defining global data and also declare objects, sub programs where as in Package Body we are implementing sub programs and also package body sub program internally behaves like a private sub program.
      • Package Specification Syntax :
      Syntax :
Create or Replace Package Package_Name 
Is/As
Global Variable Declaration;
Constant Declaration;
Cursor  Declaration;
Types  Declaration;
Procedure Declaration;
Function Declaration;
End;
      Package Body Syntax :
      Syntax :
Create or Replace Package Body
Package_Name
Is/As
Procedure Implementations;
Function Implementations;
End;
    Invoking Packaged Subprograms
    1. Exec Package_Name.Procedure_Name ( Actual Parameters );
    2. Select Package_Name.Function_Name ( Actual Parameters ) from dual;
    
Package Specification
    
Example : Create or replace package pack1 is
     procedure pr1;
     procedure pr2;
     end;

    
Package Body
    
Example : Create or replace package body pack1 is
     procedure pr1
     is
     begin
     dbms_output.put_line('first procedure');
     end pr1;
     procedure pr2
     is
     begin
     dbms_output.put_line('second procedure');
     end pr2;
     end;

     Exec Pack1.pr1;
     Exec Pack2.pr2;
  •  Global Variable
    • It is one of the variable which is used to define in package specification and implement in package body that variables are call it as a global variables.
  •  Local Variable
    • It is one of the variable which is used to define in programs ( Procedure, Function ) and implement with in the program only.
                            
    
Package Specification 
    
Example : Create or replace package pck2 is
     g number(5):=500;
     procedure p1;
     function f1 ( a number ) return number;
     end;

    
Package Body
    
Example : create or replace package body pck2 is
     procedure p1
     is
     z number(5);
     begin
     z:=g/2;
     dbms_output.put_line(z);
     end p1;
     function f1( a number ) return number
     is
     begin
     return a*g;
     end f1;
     end;
  •  Procedures Overloading
    • Overloading refers to same name can be used for different purposes i.e we are implementing overloading procedures through packages only, those procedures having same name and also different types of arguments.
      • 
Package Specification
      
Example : Create or replace package pck3 is
  procedure p1(a number, b number);
  procedure p1(x number, y number);
  end;

      
Package Body
      
Example : Create or replace package body pck3 is
  procedure p1 (a number, b number)
  is
  c number(10);
  begin
  c:=a+b;
  dbms_output.put_line(c);
  end p1;
  procedure p1 (x number, y number)
  is
  z number(10);
  begin
  z:=x+y;
  dbms_output.put_line(z);
  end p1;
  end;/
  
  Exec Pack.p1 ( a=>10, b=>20 );
  Exec Pack.p1 ( x=>100, b=>200);
  •  Forward Declaration
    • Whenever we are calling procedures into another procedure then only we are using forword declaration i.e whenever we are calling local procedure into global procedure first we must implement local procedures before calling otherwise use a forward declaration in package body.
      • 
Package Specification
      
Example :  Create or replace package pack14 is
procedure p1;
end;

      
Package Body
      
Example : Create or replace package body pack14 is
 procedure p2;
 procedure p1
 is
 begin
 p2;
 end;
 procedure p2
 is 
 begin
 dbms_output.put_line('local procedure');
 end p2;
 end;
  •  Triggers
    • Trigger is also same as stored procedure & also it will automatically invoked whenever DML Operation performed against table or view.
    • There are two types of triggers supported by PL/SQL.
      • Statement Level Trigger
      • Row Level Trigger
    • In Statement Level Trigger, Trigger body is executed only once for DML Statements.
    • In Row Level Trigger, Trigger body is executed for each and every DML Statements.
    Syntax :
create { or replace } trigger trigger_name 
before / after trigger event
insert / update / delete on table_name
{ for each row }
{ where condition }
{ declare }
variable declarations, cursors
begin
-----
end;
      Execution order in Triggers
    1. Before Statement Level
    2. Before Row Level
    3. After Row Level
    4. After Statement Level
    1. Statement Level Trigger
      • In Statement Level Trigger, Trigger body is executed only once for each DML Statement. Thats why generally statement level triggers used to define type based condition and also used to implement auditing reports. These triggers does not contain new, old qualifiers.
      Q) Write a pl/sql statement level trigger on emp table not to perform DML Operations in saturday and sunday?
      Program :  Create or replace trigger tr1 before insert or update or delete on tt
begin
if to_char(sysdate,'DY') in ('SAT','SUN')
then
raise_application_error(-20123,'we can not perform DMLs on sat and sunday');
end if;
end;
      Q) Write a pl/sql statement level trigger on emp table not to perform DML Operation on last day of the month?
      Program : create or replace trigger tt2 before insert or update or delete on tt
begin
if sysdate=last_day(sysdate) then
raise_application_error (-20111,'we can not perform dml operations on lastday ');
end if;
end;
      Trigger Event ( or ) Trigger Predicate ClausesTrigger Event ( or ) Trigger Predicate Clauses
      • If you want to define multiple conditions on multiple tables then all database systems uses trigger events.
      • These are inserting, updating, deleting clauses
      • These clauses are used in either row level or statement level triggers.
      Syntax : if inserting then
      statements;
      elsif updating then
      statements;
      elsif deleting then
      statements;
      end if;
      Q ) Write a pl/sql statement level trigger on emp table not to perform any dml operation in any days using triggering event?
      Program : create or replace trigger tr3 before insert or update or delete on tt
 begin
 if inserting then
 raise_application_error (-20121,'we can not perform inserting operation');
 elsif updating then
 raise_application_error (-20122,'we can not perfrom update operation');
 elsif deleting then
 raise_application_error (-20123,'we can not perform deleting operation');
 end if;
 end;

      Example : Create table test ( msg varchar2(100));
create or replace trigger tr4 after insert or update or delete on tt
declare
a varchar2(50);
begin
if inserting then
a := 'rows inserted';
elsif updating then
a := 'rows updated';
elsif deleting then
a := 'rows deleted';
end if;
insert into testt values (a);
end;
    2. Row Level Trigger
      • In Row Level Trigger, Trigger body is executed for each row for DML Statement, Thats why we are using for each row clause in trigger specification and also data internally stored in 2 rollback segment qualifiers are OLD & NEW
      • These qualifiers are used in either trigger specification or in trigger body. when we are using these modifiers in trigger body we must use colon prefix in the qualifiers.
        • Syntax : old.column_name ( or ) :new.column_name.
      • When we are using these qualifiers in when clause we are not allow to use colon infront of the qualifiers.
      Qualifier Insert Update Delete
      :new YES YES NO
      :old NO YES YES
      • In Before Triggers, Trigger body is executed before DML Statements are effected into database.
      • In After Triggers, Trigger body is executed after DML Statements are effected into database.
      • Generally if we want to restrict invalid data entry always we are using before triggers, where as if we are performing operation on the one table those operations are effected in another table then we are using after trigger.
      • Whenever we are inserting values into new qualifiers we must use before trigger otherwise oracle server returns an error.
      Q: Write a PL/SQL Row Level Trigger on emp table whenever user inserting data into a emp table sal should be more than 5000?
      Program : Create or replace trigger t90 before insert on tb
for each row
begin
if :new.sal<5000 then
raise_application_error (-20123,'salary should be more than 5000');
end if;
end;
      Q : Write a PL/SQL Row Level Trigger on emp, dept tables while implement on delete cascade concept without using on delete cascade clause?
      Program : Create or replace trigger t1 
after delete on dept
for each row
begin
delete from emp where deptno=:old.deptno;
end;
      Q : Write a PL/SQL Row Level Trigger on dept table whenever updating deptno's in dept table automatically those deptno's modified into emp table?
      Program : Create or replace trigger t19
after update on dept
for each row
begin
update emp set deptno=:new.deptno where deptno=:old.deptno;
end;
      Q : Write a PL/SQL Row Level Trigger whenever user inserting data into ename column after inserting data must be converted into uppercase ?
      Program : create or replace trigger t21
 before insert on emp
 for each row
 begin
 :new.ename:=upper(:new.ename);
 end;
      Q ) Write a PL/SQL Row Level Trigger on emp table by using below conditions?
      1. whenever user inserting data those values stored in another table
      2. whenever user updating data those values stored in another table
      3. whenever user deleting data those values stored in another table
      Program : First we create 3 tables which are having the same structure of emp table.
 Create or replace trigger te1
 after insert or update or delete on t01
 for each row
 begin
 if inserting then
 insert into e1(empno,ename) values (:new.empno,:new.ename);
 elsif updating then
 insert into e2(empno,ename) values (:old.empno,:old.ename);
 elsif deleting then
 insert into e3(empno,ename) values (:old.empno,:old.ename);
 end if;
 end;
      Q : Write a PL/SQL Trigger on emp table whenever user deleting records from emp table automatically display remaining number of existing record number in bottom of the delete statment?
      Program : Create or replace trigger tp1 after delete on emp
 declare
 a number(10);
 begin
 select count(*) into a from emp;
 dbms_output.put_line('remaining records are: '||a);
 end;
    3. Mutating Trigger 
      Example :
 Create or replace trigger tp1 after delete on emp
 for each row
 declare
 a number(10);
 begin
 select count(*) into a from emp;
 dbms_output.put_line('remaining records are: '||a);
 end;
      • Into a Row Level Trigger based on a table trigger body can not read data from same table and also we can not perform DML Operations on same table.
      • If we are trying to this oracle server returns an error is table is mutating.
      • This Error is called Mutating Error
      • This Trigger is called Mutating Trigger
      • This Table is called Mutating Table
      • Mutating Errors are not accured in Statement Level Trigger Because through these Statement Level Trigger when we are performing DML Operations automatically data Committed into database.
      • Where as in Row Level Trigger when we are performing transaction data is not committed and also again we are reading this data from the same table then only mutating error is accured.
      • To avoid this mutating error we are using autonomous transaction in triggers.
      Example : Create or replace trigger tp1 after delete on t01
 for each row
 declare
 pragma autonomous_transaction;
 a number(10);
 begin
 select count(*) into a from t01;
 dbms_output.put_line('remaining records are: '||a);
 commit;
 end;
    4. DDL Triggers
      • We can also create triggers on schema level, database level. These types of triggers are called DDL Triggers or System Triggers.
      • These types of triggers are created by database administrator.
        • Syntax :
        Create or replace trigger trigger_name
        Before / After
        Create / Alter / Drop / Truncate / Rename
        On Username.Schema
        Q : Write a PL/SQL Trigger on scott schema not to drop emp table?
        Program : Create or replace trigger td
 before drop on apps.schema
 begin
 if ora_dict_obj_name = 'T100' and
 ora_dict_obj_type = 'TABLE' then
 raise_application_error(-20121,'we can not drop this table');
 end if;
 end;
        Collections
        • Oracle server supports following types
          1. PL/SQL Record ( or ) Record Type
          2. Index by table ( or ) PL/SQL table ( or ) Associative Arrays.
          3. Nested tables
          4. Varrays
          5. Ref Cursors
        1. Index By Table
          • This is an user defined type which is used to store multiple data items in to a single unit. Basically this is an unconstraint table
          • Generally these tables are used to improve performance of applications because these tables are stored in memory area thats why these tables are also called as memory tables.
          • Basically these table contains key value pairs i.e value field is stored in actual data and key field stored in indexes.
          • Key field values are either integer or character and also these values are either -ve or +ve.
          • These indexes key behaves like a primary key i.e does not accept duplicate and null values. basically this key datatype is binary_integer.
          • Index by table having following collection methods.
          1. exists
          2. first
          3. last
          4. prior
          5. next
          6. count
          7. delete ( range of indexes )
          Example 1 :  
 declare
 type t1 is table of number(10)
 index by binary_integer;
 v_t t1;
 begin
 v_t(1):=10;
 v_t(2):=20;
 v_t(3):=30;
 v_t(4):=40;
 v_t(5):=50;
 dbms_output.put_line(v_t(3));
 dbms_output.put_line(v_t.first);
 dbms_output.put_line(v_t.last);
 dbms_output.put_line(v_t.prior(3));
 dbms_output.put_line(v_t.next(4));
 dbms_output.put_line(v_t.count);
 dbms_output.put_line(v_t(5));
 end;

          Example 2: 
  declare
 type t1 is table of number(10)
 index by binary_integer;
 v_t t1;
 begin
 v_t(1):=10;
 v_t(2):=20;
 v_t(3):=30;
 v_t(4):=40;
 v_t(5):=50;
 dbms_output.put_line(v_t.count);
 v_t.delete(2,3);
 dbms_output.put_line(v_t.count);
 v_t.delete;
 dbms_output.put_line(v_t.count);
 end;
          Q : Write a PLSQL program to get all employee names from emp table and store it into index by table and display data from index by table?
          Program :
 declare
 type t1 is table of varchar2(10) 
 index by binary_integer;
 v_t t1;
 cursor c1 is select ename from emp;
 n number(5):=1;
 begin
 open c1;
 loop
 fetch c1 into v_t(n);
 exit when c1%notfound;
 n:=n+1;
 end loop;
 close c1;
 for i in v_t.first..v_t.last
 loop
 dbms_output.put_line(v_t(i));
 end loop;
 end;

          Program :
 declare
 type t1 is table of varchar2(10) 
 index by binary_integer;
 v_t t1;
 begin
 select ename bulk collect into v_t from emp;
 for i in v_t.first..v_t.last
 loop
 dbms_output.put_line(v_t(i));
 end loop;
 end;

          Program :
 declare
 type t1 is table of date
 index by binary_integer;
 v_t t1;
 begin
 for i in 1..10
 loop
 v_t(i):=sysdate+i;
 end loop;
 for i in v_t.first..v_t.last
 loop
 dbms_output.put_line(v_t(i));
 end loop;
 end;
          Q : Write a PLSQL Program to retrieve all joining dates from emp table and store it into index by table and display content from index by table?
          Program :
 declare
 type t1 is table of date
 index by binary_integer;
 v_t t1;
 begin
 select hiredate bulk collect into v_t from emp;
 for i in v_t.first..v_t.last
 loop
 dbms_output.put_line(v_t(i));
 end loop;
 end;

          Example :
 declare
 type t1 is table of varchar2(10)
 index by varchar2(10);
 v_t t1;
 x varchar2(10);
 begin
 v_t('a'):= 'ARUN';
 v_t('b'):= 'AJAY';
 v_t('c'):= 'ABHI';
 x :='a';
 loop
 dbms_output.put_line(v_t(x));
 x := v_t.next(x);
 exit when x is null;
 end loop;
 end;

          Example :
 declare 
 type t1 is table of emp%rowtype
 index by binary_integer;
 v_t t1;
 x number(5);
 begin
 select * bulk collect into v_t from emp;
 x:=1;
 loop
 dbms_output.put_line(v_t(x).empno||','||v_t(x).ename);
 x:=v_t.next(x);
 exit when x is null;
 end loop;
 end;
 ( OR )

Example :
 declare 
 type t1 is table of emp%rowtype
 index by binary_integer;
 v_t t1;
 begin
 select * bulk collect into v_t from emp;
 for i in v_t.first..v_t.last
 loop
 dbms_output.put_line(v_t(i).empno||','||v_t(i).ename);
 end loop;
 end;
        2. Nested Tables
          • This is also user defined type which is used to store multiple data items in a single unit but before we are storing actual data we must initialize the data while using constructor.
          • Here constructor name is same as type name. Generally we are not allow to store index by tables permanently into database, to overcome this problem they are introduce Nested Tables to extension of the index by tables.
          • These user defined types stored permanently into database using sql.
          • In Index by tables we can not add or remove the indexes. where as in Nested tables we can add or remove the indexes using Extend, Trim collection methods.
          • In Nested tables we can allocate the memory explicitly while using Extend method
          Syntax : Type type_name is Table of datatype( size );
          variable_name type_name( ); ⇒ Constructor_Name           
          Example :
 Declare
 type t1 is table of number(10);
 v t1:=t1();
 begin
 v.extend(100);
 v(100):=10;
 dbms_output.put_line(v(100));
 end;

          Example :
 Declare
 type t1 is table of number(10);
 v1 t1:=t1(10,20,30,40,50);
 begin
 dbms_output.put_line(v1.first);
 dbms_output.put_line(v1.last);
 dbms_output.put_line(v1.prior(3));
 dbms_output.put_line(v1.next(3));
 dbms_output.put_line(v1.count);
 dbms_output.put_line(v1(3));
 for i in v1.first..v1.last
 loop
 dbms_output.put_line(v1(i));
 end loop;
 end;

          Example :
 Declare
 type t1 is table of number(10);
 v1 t1;
 v2 t1:=t1();
 begin
 if v1 is null then
 dbms_output.put_line('v1 is null');
 else
 dbms_output.put_line('v1 is not null');
 end if;
 if v2 is null then
 dbms_output.put_line('v2 is null');
 else
 dbms_output.put_line('v2 is not null');
 end if;
 end;

          Example :
 Declare
 type t1 is table of number(10);
 v t1:=t1();
 begin
 v.extend;
 v(1):=5;
 dbms_output.put_line(v(1));
 end;
          Q : Write a PLSQL program to get all employee names from emp table and store it into Nested Table and display data from Nested Table?
          Program  
 declare
 type t1 is table of varchar2(10);
 v t1:=t1();
 cursor c1 is select ename from emp;
 n number(10):=1;
 begin
 for i in c1
 loop
 v.extend();
 v(n):=i.ename;
 n:=n+1;
 end loop;
 for i in v.first..v.last
 loop
 dbms_output.put_line(v(i));
 end loop;
 end;
 ( OR )
Program :
 declare
 type t1 is table of varchar2(10);
 v t1:=t1();
 begin
 select ename bulk collect into v from emp;
 for i in v.first..v.last
 loop
 dbms_output.put_line(v(i));
 end loop;
 end;

          Program :
 declare
 type t1 is table of emp%rowtype;
 v t1:=t1();
 begin
 select * bulk collect into v from emp;
 for i in v.first..v.last
 loop
 dbms_output.put_line(v(i).empno||','||v(i).ename||','||v(i).job);
 end loop;
 end;
        3. Varrays
          • This is also user defined type which is used to store multiple data items in a single unit but before we are storing actual data we must initialize the data while using constructor.
          • These user defined types stored permanently into database using sql.
          • Basically we are using the Varrays for retrieving the huge data.
            • Syntax : Type type_name is varray( maxsize ) of datatype( size );
            Variable_name Type_name := Type_name( );
            Program :
 Declare
 type t1 is varray(50) of emp%rowtype;
 v t1:=t1();
 begin
 select * bulk collect into v from emp;
 for i in v.first..v.last
 loop
 dbms_output.put_line(v(i).empno||','||v(i).ename||','||v(i).job);
 end loop;
 end;
          • Difference b/w Index by Table, Nested Table, Varrays
        Index by Table Nested Table Varrays
        1. It is not stored permanently in database.
        2. We can not add or remove indexes.
        3. Indexes starting from negative to positive numbers and also having key value pairs.
        1. It is stored permanently in database by using sql.
        2. We can add or remove indexes using extend, trim method.
        3. Indexes starting from 1.
        1. It is stored permanently in database by using sql.
        2. We can add or remove indexes using extend, trim method.
        3. Indexes starting from 1.
        • Bulk Mechanism
          • Bulk is one of the method which is used to improve the performance of the applications.
          • Oracle introduce bulk bind process using collection i.e in this process we are putting all sql statement related values into collection and in this collection we are performing insert, update, delete at a time using for all statement.
          • In this bulk we have two actions
            1. Bulk Collect
            2. Bulk Bind
          1. Bulk Collect
            • In this clause we are used to fetch the data from resource into collection
            • This clauses used in
              1. Select ...........into............clause
              2. Cursor...........Fetch...........Statement
              3. Dml............Returning.........Clauses
            1. Bulk Collect used in select .....into .....clause Syntax : select * bulk collect into collection_name from table_name. 
              Program :
 Declare
 type t1 is table of emp%rowtype
 index by binary_integer;
 v t1;
 begin
 select * bulk collect into v from emp;
 for i in v.first..v.last
 loop
 dbms_output.put_line(v(i).empno||','||v(i).ename||','||v(i).job);
 end loop;
 end;
            2. Bulk Collect used in cursor......fetch.......statement Syntax : fetch cursor_name bulk collect into collection_variable. 
              Program 
 Declare
 type t1 is table of varchar2(10) 
 index by binary_integer;
 v1 t1;
 v2 t1;
 cursor c1 is select ename,job from emp;
 begin
 open c1;
 fetch c1 bulk collect into v1,v2;
 close c1;
 for i in v1.first..v1.last
 loop
 dbms_output.put_line(v1(i)||','||v2(i));
 end loop;
 end;
              Time Program with out BULK 
               Declare
 vrow varchar2(50);
 cursor c1 is select object_name from all_objects;
 z1 number(10);
 z2 number(10);
 begin 
 z1:=dbms_utility.get_time;
 open c1;
 loop
 fetch c1 into vrow;
 exit when c1%notfound;
 end loop;
 close c1;
 z2:=dbms_utility.get_time;
 dbms_output.put_line(z1);
 dbms_output.put_line(z2);
 dbms_output.put_line(z2-z1);
 end;
              Time Program with BULK
                  Declare
 type t1 is table of varchar2(50) index by binary_integer;
 v1 t1;
 cursor c1 is select object_name from all_objects;
 z1 number(10);
 z2 number(10);
 begin
 z1:=dbms_utility.get_time;
 open c1;
 loop
 fetch c1 bulk collect into v1;
 exit when c1%notfound;
 end loop;
 close c1;
 z2:=dbms_utility.get_time;
 dbms_output.put_line(z1);
 dbms_output.put_line(z2);
 dbms_output.put_line(z2-z1);
 end;
            3. Bulk Collect used in DML..........Returning clauses. Syntax : dml statement returning column_name into variable_name; 
              Example : 
 Variable a varchar2(10);
 Update emp set sal=sal+100 where ename ='KING' returning job into :a;
 Print a;
              Q : Write a PLSQL Stored Procedure modify salaries of the clerk from emp table and also these modified value immediately stored into index by table by using dml ...returning clause and also display content from index by table?
              Program :
 Create or replace procedure p1 is
 type t1 is table of emp%rowtype 
 index by binary_integer;
 v1 t1;
 begin
 update emp set sal=sal+100 where job='CLERK' 
 returning empno,ename,job,mgr,hiredate,sal,comm,deptno
 bulk collect into v1;
 dbms_output.put_line('updated no:of clerks are:'||sql%rowcount);
 for i in v1.first..v1.last
 loop
 dbms_output.put_line(v1(i).ename||','||v1(i).job||','||v1(i).sal);
 end loop;
 end;
          2. Bulk Bind
            • In bulk bind process we are performing bulk of operations using collection i.e in this process we are using bulk update, bulk delete, bulk insert using forall statement.
            • Before we are using bulk bind process we are fetching data from database into collections using bulk collect clause.
            Syntax : forall indexvar in collectionvar.frist..collectionvar.last 
            Example :
 Declare
 type t1 is varray(10) of number(10);
 v1 t1:=t1(10,20);
 begin
 forall i in v1.first..v1.last
 update emp set sal=sal+100 where deptno=v1(i); 
 end;
            Bulk Update
            Program :
 Declare
 type t1 is table of number(5) index by binary_integer;
 v1 t1;
 begin
 select empno bulk collect into v1 from emp;
 forall i in v1.first..v1.last
 update emp set sal=sal+111 where empno=v1(i);
 end;
            Bulk Delete
            Program :
 Declare
 type t1 is varray(10) of number(10);
 v1 t1:=t1(20,30,40);
 begin
 forall i in v1.first..v1.last
 delete from emp where empno=v1(i);
 end;
            Bulk Insert
            Program :
 Declare
 type t1 is table of number(10) index by binary_integer;
 v1 t1;
 begin
 for i in 1..100
 loop
 v1(i):=i;
 end loop;
 forall i in v1.first..v1.last
 insert into bt values (v1(i));
 end;
Posted by at No comments:
Subscribe to: Posts (Atom)