PROGRAM THAT FETCHES THE DEPT NAME AND LOCATION OF A GIVEN DEPTNO AND RAISES THE USER DEFINED EXCEPTION IF DEPTNO DOES NOT EXIST IN ORACLE?



SQL> DECLARE
  2   LOCATION DEPT.LOC%TYPE;
  3   NAME DEPT.DNAME%TYPE;
  4   DNO DEPT.DEPTNO%TYPE;
  5   DEPTNO_ERR EXCEPTION;
  6   BEGIN
  7   DNO:=&DNO;
  8   IF DNO NOT IN (10, 20, 30, 40) THEN
  9   RAISE DEPTNO_ERR;
 10   END IF;
 11   SELECT DNAME, LOC INTO NAME, LOCATION FROM
        DEPT WHERE DEPTNO=DNO;
 12   DBMS_OUTPUT.PUT_LINE('LOCATION IS '||LOCATION);
 13   DBMS_OUTPUT.PUT_LINE(' DEPT NAME IS '||NAME);
 14   EXCEPTION
 15   WHEN DEPTNO_ERR THEN
 16   DBMS_OUTPUT.PUT_LINE('DEPARTMENT NUMBER
        DOES NOT EXIST');
 17   END;
 18 /
Enter value for dno: 20
old   7:  DNO:=&DNO;
new   7:  DNO:=20;
LOCATION IS DALLAS
DEPT NAME IS RESEARCH

PL/SQL procedure successfully completed.

Comments

Post a Comment

Popular posts from this blog

Write a program to add two number using inline function in C++?

#include<iostream.h> #include<conio.h> class bca {   public:   inline int sum(int x, int y)   {   return(x+y);   } }; int main() { clrscr(); int r,a,b; bca obj; cout<<"enter two integers :"<<endl; cin>>a>>b; r=obj.sum(a,b); cout<<" the sum of two integers is :"<<r<<endl; getch(); return 0; }   OUTPUT:- enter two integers :    3    6 the sum of two integer is :   9  
Read more

Traversing of elements program with algorithm and Flowchart

Image
ALGORITHM VARIABLES: §   i:      loop counter of array. §   A:    array name. §   LB: lower bound. §   UB: upper bound. ALGORITHM: STEP 1:         set i =LB STEP 2:         repeat steps 3 & 4                            While i<=UB STEP 3:         apply process to A[i] STEP 4:         i = i+1            STEP 5:         exit EXPLANATION: §   To traverse the elements of the array, firstly set the loop counter ‘i’ equal to last element of the array called the LOWER BOUND. ...
Read more