Saturday 31 March 2012
PL/SQL PROGRAM
/*****************************************************************
**** Amstrong Number ****
** if a number =Addition of it's individual element's cube *
*** EX: 153=1*1*1 + 5*5*5 + 3*3*3 ****
*****************************************************************/
DECLARE
V_Num NUMBER(3) := &Num;
V_Rem NUMBER ;
V_Save NUMBER ;
V_Result NUMBER := 0;
V_Mesage VARCHAR2(200) := ' ';
BEGIN
IF(V_Num IS NOT NULL ) THEN
V_Mesage := 'Inserted Number ' || V_Num ||' and Sum of digits = ';
V_Save := V_Num;
<<OuterLoop>>
LOOP
EXIT WHEN V_Num <= 0
V_Rem := MOD(V_Num,10); -- Action State
V_Result := V_Result + POWER(V_Rem,3); -- Action State
V_Num := TRUNC(V_Num/10); -- Updation State
END LOOP OuterLoop;
IF( V_Save = V_Result ) THEN
V_Mesage := ' is an AMSTRONG Number ';
ELSE
V_Mesage := ' is NOT an AMSTRONG Number ';
END IF;
DBMS_OUTPUT.PUT_LINE(V_Mesage || V_Result );
ELSE
DBMS_OUTPUT.PUT_LINE('Fatal Error ! NULL Encounted Can''t Proceed Forther Try Again ');
END IF;
END;
/************************************
*** REVERSE Number ***
*** EX: 153=351 ***
**************************************/
DECLARE
V_Num NUMBER(3) := &Num;
V_Rem NUMBER ;
V_Result NUMBER := 0;
V_Mesage VARCHAR2(200) := ' ';
BEGIN
IF(V_Num IS NOT NULL ) THEN
V_Mesage := 'Inserted Number ' || V_Num ||' and It'' Reverse is ';
<<OuterLoop>>
LOOP
EXIT WHEN V_Num <= 0 ;
V_Rem := MOD(V_Num,10); -- Action State
V_Result := V_Result*10 + V_Rem; -- Action State
V_Num := TRUNC(V_Num/10); -- Updation State
END LOOP OuterLoop;
DBMS_OUTPUT.PUT_LINE(V_Mesage || V_Result );
ELSE
DBMS_OUTPUT.PUT_LINE('Fatal Error ! NULL Encounted Can''t Proceed Forther Try Again ');
END IF;
END;
/************************************************
***** Palindron Number ***
************************************************/
DECLARE
V_Num NUMBER(3) := &Num;
V_Rem NUMBER ;
V_Result NUMBER := 0;
V_Save NUMBER ;
V_Mesage VARCHAR2(200) := ' ';
BEGIN
IF(V_Num IS NOT NULL ) THEN
V_Mesage := 'Inserted Number ' || V_Num ||' and It'' Reverse is ';
V_Save := V_Num;
<<OuterLoop>>
LOOP
EXIT WHEN V_Num <= 0
V_Rem := MOD(V_Num,10); -- Action State
V_Result := V_Result*10 + V_Rem; -- Action State
V_Num := TRUNC(V_Num/10); -- Updation State
END LOOP OuterLoop;
V_Mesage := V_Mesage || V_Result ;
IF( V_Save = V_Result ) THEN
V_Mesage := V_Mesage || CHR(10) || V_Save || ' = ' || V_Result || CHR(10) || ' SO it''s PALINDRON ';
ELSE
V_Mesage := V_Mesage || CHR(10) || V_Save || ' != ' || V_Result || CHR(10) || ' SO it''s not PALINDRON ';
END IF;
DBMS_OUTPUT.PUT_LINE(V_Mesage);
ELSE
DBMS_OUTPUT.PUT_LINE('Fatal Error ! NULL Encounted Can''t Proceed Forther Try Again ');
END IF;
END;
/* *************************************************
**** Print Stars in a Step wise *****
*** * *****
*** * * *****
*** * * * *****
**************************************************/
DECLARE
V_Num NUMBER(3) := &Num;
V_Mesage VARCHAR2(200) := ' ';
BEGIN
IF(V_Num IS NOT NULL ) THEN
DBMS_OUTPUT.PUT_LINE( 'Number of line * will be printed = ' || V_Num);
<<OuterLoop>>
LOOP
EXIT WHEN V_Num <= 0 ;
V_Mesage := V_Mesage || ' * '; -- Action State
V_Num := V_Num -1 ; -- Updation State
DBMS_OUTPUT.PUT_LINE(V_Mesage);
END LOOP OuterLoop;
ELSE
DBMS_OUTPUT.PUT_LINE('Fatal Error ! NULL Encounted Can''t Proceed Forther Try Again ');
END IF;
END;
/**************************************************
**** 1 *****
**** 2 3 4 *****
**** 5 6 7 8 9 *****
**************************************************/
DECLARE
V_Num NUMBER(3) := &Num;
V_InNum NUMBER;
V_OutNum NUMBER(3) := 1;
V_InCont NUMBER(3) := 1;
V_Counter NUMBER(3) := 1;
V_Mesage VARCHAR2(200) := ' ';
BEGIN
IF(V_Num IS NOT NULL ) THEN
DBMS_OUTPUT.PUT_LINE( 'Number of line * will be printed = ' || V_Num);
<<OuterLoop>>
LOOP
EXIT WHEN V_Num < V_OutNum ;
V_InNum := 2 * (V_OutNum -1) + 1;
V_Mesage := ' ';
V_InCont := 1;
<<InnrLoop>>
LOOP
EXIT WHEN V_InNum < V_InCont ;
V_Mesage := V_Mesage || ' ' ||V_Counter ;
V_Counter := V_Counter + 1;
V_InCont := V_InCont + 1;
END LOOP InnrLoop;
DBMS_OUTPUT.PUT_LINE(V_Mesage);
V_OutNum := V_OutNum + 1 ;
END LOOP OuterLoop;
ELSE
DBMS_OUTPUT.PUT_LINE('Fatal Error ! NULL Encounted Can''t Proceed Forther Try Again ');
END IF;
END;
/*****************************************************************
***** Sin X =x - (x^3) / 3! + (x^5 / 5!) -( x^7 / 7! )............ ***
*****************************************************************/
DECLARE
V_X NUMBER(3) := &X;
V_InNum NUMBER;
V_OutNum NUMBER(3) := 1;
V_InCont NUMBER(3) := 1;
V_Fact NUMBER ;
V_Ratio NUMBER ;
V_Result NUMBER := 0 ;
V_Message VARCHAR2(200) := ' ';
BEGIN
IF(V_X IS NOT NULL ) THEN
<<OuterLoop>>
LOOP
EXIT WHEN 20 < V_OutNum ;
V_InNum := 2 * (V_OutNum -1) + 1;
V_InCont := 1;
V_Fact := 1;
V_Ratio := 1;
<<InnrLoop>>
LOOP
EXIT WHEN V_InNum < V_InCont ;
V_Fact := V_Fact *V_InCont;
V_InCont := V_InCont + 1;
END LOOP InnrLoop;
V_Ratio := POWER(V_X,V_InNum)/ V_Fact;
IF ( MOD(V_OutNum,2) = 0 ) THEN
V_Result := V_Result - V_Ratio;
ELSE
V_Result := V_Result + V_Ratio;
END IF;
V_OutNum := V_OutNum + 1 ;
END LOOP OuterLoop;
DBMS_OUTPUT.PUT_LINE(V_Result);
ELSE
DBMS_OUTPUT.PUT_LINE('Fatal Error ! NULL Encounted Can''t Proceed Forther Try Again ');
END IF;
END;
/*****************************************************************
***** Cos X =1 - (x^2 / 2! )+ (x^4 / 4!) -( x^6 / 6! )........... ***
*****************************************************************/
DECLARE
V_X NUMBER(3) := &X;
V_InNum NUMBER;
V_OutNum NUMBER(3) := 1;
V_InCont NUMBER(3) := 1;
V_Fact NUMBER ;
V_Ratio NUMBER ;
V_Result NUMBER := 1 ;
V_Message VARCHAR2(200) := ' ';
BEGIN
IF(V_X IS NOT NULL ) THEN
<<OuterLoop>>
LOOP
EXIT WHEN 50 < V_OutNum ;
V_InNum := 2 * V_OutNum ;
V_InCont := 1;
V_Fact := 1;
V_Ratio := 1;
<<InnrLoop>>
LOOP
EXIT WHEN V_InNum < V_InCont ;
V_Fact := V_Fact *V_InCont;
V_InCont := V_InCont + 1;
END LOOP InnrLoop;
V_Ratio := POWER(V_X,V_InNum) / V_Fact;
IF ( MOD(V_OutNum,2) != 0 ) THEN
V_Result := V_Result - V_Ratio;
ELSE
V_Result := V_Result + V_Ratio;
END IF;
V_OutNum := V_OutNum + 1 ;
END LOOP OuterLoop;
DBMS_OUTPUT.PUT_LINE(V_Result);
ELSE
DBMS_OUTPUT.PUT_LINE('Fatal Error ! NULL Encounted Can''t Proceed Forther Try Again ');
END IF;
END; ========================================================
While Loop ========================================================
/* ************************************************
*********** Factorial of a Number *************
**************************************************/
DECLARE
V_Num NUMBER := &Num ;
V_Result NUMBER := 1 ;
V_Message VARCHAR2(100) := ' ';
BEGIN
IF ( V_Num IS NOT NULL ) THEN /* Check For NULL Encounted */
V_Message := 'Factorial of ' || V_Num || ' = ' ;
WHILE V_Num > 0
LOOP
V_Result := V_Result * V_Num;
V_Num := V_Num - 1;
END LOOP ;
DBMS_OUTPUT.PUT_LINE(V_Message || V_Result);
ELSE
DBMS_OUTPUT.PUT_LINE('Fatal Error ! NULL Encounted Can''t Proceed Forther Try Again ');
END IF;
END ;
/* ************************************************
*********** Reverse of a Number ***********
**************************************************/
DECLARE
V_Num NUMBER := &Num ;
V_Result NUMBER := 0 ;
V_Rem NUMBER ;
V_Message VARCHAR2(100) := ' ';
BEGIN
IF ( V_Num IS NOT NULL ) THEN /* Check For NULL Encounted */
V_Message := 'Reverse of ' || V_Num || ' = ' ;
WHILE V_Num > 0
LOOP
V_Rem := MOD(V_Num,10);
V_Result := V_Result *10 + V_Rem;
V_Num := TRUNC(V_Num / 10) ;
END LOOP ;
DBMS_OUTPUT.PUT_LINE(V_Message || V_Result);
ELSE
DBMS_OUTPUT.PUT_LINE('Fatal Error ! NULL Encounted Can''t Proceed Forther Try Again ');
END IF;
END ;
/* ************************************************
**** Sum of the digits of a Number *****
**************************************************/
DECLARE
V_Num NUMBER := &Num ;
V_Result NUMBER := 0 ;
V_Rem NUMBER ;
V_Message VARCHAR2(100) := ' ';
BEGIN
IF ( V_Num IS NOT NULL ) THEN /* Check For NULL Encounted */
V_Message := 'Sum of the digits of Number ' || V_Num || ' = ' ;
WHILE V_Num > 0
LOOP
V_Rem := MOD(V_Num,10);
V_Result := V_Result + V_Rem;
V_Num := TRUNC(V_Num / 10) ;
END LOOP ;
DBMS_OUTPUT.PUT_LINE(V_Message || V_Result);
ELSE
DBMS_OUTPUT.PUT_LINE('Fatal Error ! NULL Encounted Can''t Proceed Forther Try Again ');
END IF;
END ;
/* ************************************************
******* Sum of number upto n *******
**************************************************/
DECLARE
V_Num NUMBER := &Num ;
V_Result NUMBER := 0 ;
V_Message VARCHAR2(100) := ' ';
BEGIN
IF ( V_Num IS NOT NULL ) THEN /* Check For NULL Encounted */
IF( V_Num > 0) THEN
V_Message := 'Sum of number from 1 to ' || V_Num || ' = ' ;
WHILE V_Num > 0
LOOP
V_Result := V_Result + V_Num;
V_Num := V_Num - 1 ;
END LOOP ;
DBMS_OUTPUT.PUT_LINE(V_Message || V_Result);
ELSE
DBMS_OUTPUT.PUT_LINE('Sorry _ve Value Inserted .. Try again with +ve Value ');
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('Fatal Error ! NULL Encounted Can''t Proceed Forther Try Again ');
END IF;
END ;
/*****************************************************************
**** Amstrong Number ****
*****************************************************************/
DECLARE
V_Num NUMBER := &Num;
V_Rem NUMBER ;
V_Save NUMBER ;
V_Result NUMBER := 0;
V_Mesage VARCHAR2(200) := ' ';
BEGIN
IF(V_Num IS NOT NULL ) THEN
V_Mesage := ' Inserted Number ' || V_Num ||' and Cube /Sum of digits = ';
V_Save := V_Num;
WHILE V_Num > 0
LOOP
V_Rem := MOD(V_Num,10);
V_Result := V_Result + POWER(V_Rem,3);
V_Num := TRUNC(V_Num/10);
END LOOP ;
V_Mesage := V_Mesage || V_Result || CHR(10) ;
IF( V_Save = V_Result ) THEN
V_Mesage := V_Mesage || ' It is an AMSTRONG Number ';
ELSE
V_Mesage := V_Mesage || 'It is NOT an AMSTRONG Number ';
END IF;
DBMS_OUTPUT.PUT_LINE(V_Mesage );
ELSE
DBMS_OUTPUT.PUT_LINE('Fatal Error ! NULL Encounted Can''t Proceed Forther Try Again ');
END IF;
END;
/************************************************
***** Palindron Number ***
************************************************/
DECLARE
V_Num NUMBER := &Num;
V_Rem NUMBER ;
V_Result NUMBER := 0;
V_Save NUMBER ;
V_Mesage VARCHAR2(200) := ' ';
BEGIN
IF(V_Num IS NOT NULL ) THEN
V_Mesage := 'Inserted Number ' || V_Num ||' and It'' Reverse is ';
V_Save := V_Num;
WHILE V_Num > 0
LOOP
V_Rem := MOD(V_Num,10);
V_Result := V_Result*10 + V_Rem;
V_Num := TRUNC(V_Num/10);
END LOOP OuterLoop;
V_Mesage := V_Mesage || V_Result ;
IF( V_Save = V_Result ) THEN
V_Mesage := V_Mesage || CHR(10) || V_Save || ' = ' || V_Result || CHR(10) || ' SO it''s PALINDRON ';
ELSE
V_Mesage := V_Mesage || CHR(10) || V_Save || ' != ' || V_Result || CHR(10) || ' SO it''s not PALINDRON ';
END IF;
DBMS_OUTPUT.PUT_LINE(V_Mesage);
ELSE
DBMS_OUTPUT.PUT_LINE('Fatal Error ! NULL Encounted Can''t Proceed Forther Try Again ');
END IF;
END; =======================================================
FOR LOOP =======================================================
/* ************************************************
******* Factorial of a Number ********
**************************************************/
DECLARE
V_Num NUMBER := &Num ;
V_Result NUMBER := 1 ;
V_Message VARCHAR2(100) := ' ';
BEGIN
IF ( V_Num IS NOT NULL ) THEN /* Check For NULL Encounted */
V_Message := 'Factorial of ' || V_Num || ' = ' ;
FOR IndexI IN 1 .. V_Num
LOOP
V_Result := V_Result * IndexI;
END LOOP ;
DBMS_OUTPUT.PUT_LINE(V_Message || V_Result);
ELSE
DBMS_OUTPUT.PUT_LINE('Fatal Error ! NULL Encounted Can''t Proceed Forther Try Again ');
END IF;
END ;
/* ************************************************
******* Reverse of a Number *******
**************************************************/
DECLARE
V_Num NUMBER := &Num ;
V_Result NUMBER := 0 ;
V_Rem NUMBER ;
V_Message VARCHAR2(100) := ' ';
BEGIN
IF ( V_Num IS NOT NULL ) THEN /* Check For NULL Encounted */
V_Message := 'Reverse of ' || V_Num || ' = ' ;
FOR IndexI IN 1 .. LENGTH(V_Num)
LOOP
V_Rem := MOD(V_Num,10);
V_Result := V_Result *10 + V_Rem;
V_Num := TRUNC(V_Num / 10) ;
END LOOP ;
DBMS_OUTPUT.PUT_LINE(V_Message || V_Result);
ELSE
DBMS_OUTPUT.PUT_LINE('Fatal Error ! NULL Encounted Can''t Proceed Forther Try Again ');
END IF;
END ;
/* ************************************************
**** Sum of the digits of a Number *****
**************************************************/
DECLARE
V_Num NUMBER := &Num ;
V_Result NUMBER := 0 ;
V_Rem NUMBER ;
V_Message VARCHAR2(100) := ' ';
BEGIN
IF ( V_Num IS NOT NULL ) THEN /* Check For NULL Encounted */
V_Message := 'Sum of the digits of Number ' || V_Num || ' = ' ;
FOR IndexI IN 1 .. LENGTH(V_Num)
LOOP
V_Rem := MOD(V_Num,10);
V_Result := V_Result + V_Rem;
V_Num := TRUNC(V_Num / 10) ;
END LOOP ;
DBMS_OUTPUT.PUT_LINE(V_Message || V_Result);
ELSE
DBMS_OUTPUT.PUT_LINE('Fatal Error ! NULL Encounted Can''t Proceed Forther Try Again ');
END IF;
END ;
/* ************************************************
***** Sum of number upto n s********
**************************************************/
DECLARE
V_Num NUMBER := &Num ;
V_Result NUMBER := 0 ;
V_Message VARCHAR2(100) := ' ';
BEGIN
IF ( V_Num IS NOT NULL ) THEN /* Check For NULL Encounted */
IF( V_Num > 0) THEN
V_Message := 'Sum of number from 1 to ' || V_Num || ' = ' ;
FOR IndexI IN 1 .. V_Num
LOOP
V_Result := V_Result + IndexI;
END LOOP ;
DBMS_OUTPUT.PUT_LINE(V_Message || V_Result);
ELSE
DBMS_OUTPUT.PUT_LINE('Sorry _ve Value Inserted .. Try again with +ve Value ');
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('Fatal Error ! NULL Encounted Can''t Proceed Forther Try Again ');
END IF;
END ;
/*****************************************************************
**** Amstrong Number ****
*****************************************************************/
DECLARE
V_Num NUMBER := &Num;
V_Rem NUMBER ;
V_Save NUMBER ;
V_Result NUMBER := 0;
V_Mesage VARCHAR2(200) := ' ';
BEGIN
IF(V_Num IS NOT NULL ) THEN
V_Mesage := ' Inserted Number ' || V_Num ||' and Cube Sum of digits = ';
V_Save := V_Num;
FOR IndexI IN 1 .. LENGTH(V_Num)
LOOP
V_Rem := MOD(V_Num,10);
V_Result := V_Result + POWER(V_Rem,3);
V_Num := TRUNC(V_Num/10);
END LOOP ;
V_Mesage := V_Mesage || V_Result || CHR(10) ;
IF( V_Save = V_Result ) THEN
V_Mesage := V_Mesage || ' It is an AMSTRONG Number ';
ELSE
V_Mesage := V_Mesage || 'It is NOT an AMSTRONG Number ';
END IF;
DBMS_OUTPUT.PUT_LINE(V_Mesage );
ELSE
DBMS_OUTPUT.PUT_LINE('Fatal Error ! NULL Encounted Can''t Proceed Forther Try Again ');
END IF;
END;
/************************************************
***** Palindron Number ***
************************************************/
DECLARE
V_Num NUMBER := &Num;
V_Rem NUMBER ;
V_Result NUMBER := 0;
V_Save NUMBER ;
V_Mesage VARCHAR2(200) := ' ';
BEGIN
IF(V_Num IS NOT NULL ) THEN
V_Mesage := 'Inserted Number ' || V_Num ||' and It'' Reverse is ';
V_Save := V_Num;
FOR IndexI IN 1 .. LENGTH(V_Num)
LOOP
V_Rem := MOD(V_Num,10);
V_Result := V_Result*10 + V_Rem;
V_Num := TRUNC(V_Num/10);
END LOOP OuterLoop;
V_Mesage := V_Mesage || V_Result ;
IF( V_Save = V_Result ) THEN
V_Mesage := V_Mesage || CHR(10) || V_Save || ' = ' || V_Result || CHR(10) || ' SO it''s PALINDRON ';
ELSE
V_Mesage := V_Mesage || CHR(10) || V_Save || ' != ' || V_Result || CHR(10) || ' SO it''s not PALINDRON ';
END IF;
DBMS_OUTPUT.PUT_LINE(V_Mesage);
ELSE
DBMS_OUTPUT.PUT_LINE('Fatal Error ! NULL Encounted Can''t Proceed Forther Try Again ');
END IF;
END;
/****************************************************************
*** Print 1 to N *******
****************************************************************
DECLARE
V_Num NUMBER := &Num;
V_Mesage VARCHAR2(200) := ' ';
BEGIN
IF(V_Num IS NOT NULL ) THEN
V_Mesage := ' Numbers from 1 to ' || V_Num ||' are ';
FOR IndexI IN 1 .. V_Num
LOOP
V_Mesage := V_Mesage ||' ' || IndexI ;
END LOOP ;
DBMS_OUTPUT.PUT_LINE(V_Mesage);
ELSE
DBMS_OUTPUT.PUT_LINE('Fatal Error ! NULL Encounted Can''t Proceed Forther Try Again ');
END IF;
END;
/****************************************************************
*** Print 1 to N Rverse Order *******
****************************************************************
DECLARE
V_Num NUMBER := &Num;
V_Mesage VARCHAR2(200) := ' ';
BEGIN
IF(V_Num IS NOT NULL ) THEN
V_Mesage := ' Numbers from 1 to ' || V_Num ||' are ';
FOR IndexI IN REVERSE 1 .. V_Num
LOOP
V_Mesage := V_Mesage ||' ' || IndexI ;
END LOOP ;
DBMS_OUTPUT.PUT_LINE(V_Mesage);
ELSE
DBMS_OUTPUT.PUT_LINE('Fatal Error ! NULL Encounted Can''t Proceed Forther Try Again ');
END IF;
END;
/* *************************************************
**** Print Stars in a Step wise *****
*** * *****
*** * * *****
*** * * * *****
*** * * * * *****
*** * * * *****
*** * * *****
*** * *****
**************************************************/
DECLARE
V_Num NUMBER := &Num;
V_Mesage VARCHAR2(200) := ' ';
V_Inner NUMBER;
BEGIN
IF(V_Num IS NOT NULL ) THEN
DBMS_OUTPUT.PUT_LINE( 'Number of line * will be printed = ' || V_Num);
<<OuterLoop1>>
FOR IndexI IN 1 .. V_Num
LOOP
V_Inner := IndexI;
<<InnerLoop1>>
FOR IndexI IN 1 .. V_Inner
LOOP
V_Mesage := V_Mesage || ' * ';
END LOOP InnerLoop1;
DBMS_OUTPUT.PUT_LINE(V_Mesage);
V_Mesage := ' ';
END LOOP OuterLoop1;
<<OuterLoop2>>
FOR IndexI IN REVERSE 1 .. V_Num+1
LOOP
V_Inner := IndexI;
<<InnerLoop2>>
FOR IndexI IN 1 .. V_Inner
LOOP
V_Mesage := V_Mesage || ' * ';
END LOOP InnerLoop2;
DBMS_OUTPUT.PUT_LINE(V_Mesage);
V_Mesage := ' ';
END LOOP OuterLoop2;
ELSE
DBMS_OUTPUT.PUT_LINE('Fatal Error ! NULL Encounted Can''t Proceed Forther Try Again ');
END IF;
END;
/**********************************************
**** 1 *****
**** 2 3 4 *****
**** 5 6 7 8 9 *****
**********************************************/
DECLARE
V_Num NUMBER(3) := &Num;
V_InNum NUMBER;
V_Counter NUMBER := 1;
V_Mesage VARCHAR2(200) := ' ';
BEGIN
IF(V_Num IS NOT NULL ) THEN
DBMS_OUTPUT.PUT_LINE( 'Number of line * will be printed = ' || V_Num);
<<OuterLoop>>
FOR IndexI IN 1 .. V_Num
LOOP
V_InNum := 2 * (IndexI -1) + 1;
<<InnrLoop>>
FOR IndexI IN 1 .. V_InNum
LOOP
V_Mesage := V_Mesage || ' ' || V_Counter ;
V_Counter := V_Counter + 1;
END LOOP InnrLoop;
DBMS_OUTPUT.PUT_LINE(V_Mesage);
V_Mesage := ' ';
END LOOP OuterLoop;
ELSE
DBMS_OUTPUT.PUT_LINE('Fatal Error ! NULL Encounted Can''t Proceed Forther Try Again ');
END IF;
END;
/**************************************************
**** 1 2 3 *****
**** 2 3 4 *****
**** 3 4 5 *****
**************************************************/
DECLARE
V_Num NUMBER(3) := &Num;
V_InNum NUMBER;
V_Mesage VARCHAR2(200) := ' ';
BEGIN
IF(V_Num IS NOT NULL ) THEN
DBMS_OUTPUT.PUT_LINE( 'Number of line * will be printed = ' || V_Num);
<<OuterLoop>>
FOR IndexI IN 1 .. V_Num
LOOP
<<InnrLoop>>
FOR IndexJ IN 1 .. V_Num
LOOP
V_Mesage := V_Mesage || ' ' || ( IndexI + IndexJ - 1) ;
END LOOP InnrLoop;
DBMS_OUTPUT.PUT_LINE(V_Mesage);
V_Mesage := ' ';
END LOOP OuterLoop;
ELSE
DBMS_OUTPUT.PUT_LINE('Fatal Error ! NULL Encounted Can''t Proceed Forther Try Again ');
END IF;
END;
/*************************************************************
**** Co-Ordinate Poition of a matrix *****
*************************************************************/
DECLARE
V_Num NUMBER(3) := &Num;
V_InNum NUMBER;
V_Mesage VARCHAR2(200) := ' ';
BEGINS
IF(V_Num IS NOT NULL ) THEN
DBMS_OUTPUT.PUT_LINE( 'Number of line * will be printed = ' || V_Num);
<<OuterLoop>>
FOR IndexI IN 1 .. V_Num
LOOP
<<InnrLoop>>
FOR IndexJ IN 1 .. V_Num
LOOP
V_Mesage := V_Mesage || ' ( ' || ( IndexI -1 ) || ',' || (IndexJ - 1) || ' )' ;
END LOOP InnrLoop;
DBMS_OUTPUT.PUT_LINE(V_Mesage);
V_Mesage := ' ';
END LOOP OuterLoop;
ELSE
DBMS_OUTPUT.PUT_LINE('Fatal Error ! NULL Encounted Can''t Proceed Forther Try Again ');
END IF;
END;
/*************************************************************
***** String Reverse ****
**************************************************************
DECLARE
V_String VARCHAR2 (50):='&String';
V_Mesage VARCHAR2(200) := ' ';
BEGIN
IF(V_String IS NOT NULL ) THEN
DBMS_OUTPUT.PUT_LINE( 'Entered String is ' || V_String );
<<OuterLoop>>
FOR IndexI IN REVERSE 1 .. LENGTH(V_String)
LOOP
V_Mesage := V_Mesage || SUBSTR(V_String, IndexI,1) ;
END LOOP OuterLoop;
DBMS_OUTPUT.PUT_LINE('Reverse of the String is ' || V_Mesage );
ELSE
DBMS_OUTPUT.PUT_LINE('Fatal Error ! NULL Encounted Can''t Proceed Forther Try Again ');
END IF;
END;
DEGIN
V_Empno NUMBER(4) :=&Empid;
V_Name VARCHAR2(20) ;
V_Deptno NUMBER(2) ;
V_Job VARCHAR2(10);
V_Sal NUMBER(4);
V_Comm NUMBER(4);
V_HireDate DATE;
V_Mgr NUMBER(4);
BEGIN
DBMS_OUTPUT.PUT_LINE('CONNECT TO DATABASE...........');
SELECT EName,Deptno,Job,Sal,Comm,HireDate,Mgr
INTO
V_Name,V_Deptno,V_Job,V_Sal,V_Comm,V_HireDate,V_Mgr
FROM Emp
WHERE Empno=V_Empno;
DBMS_OUTPUT.PUT_LINE('Employee having Employe ID '|| V_Empno || ' has following details....');
DBMS_OUTPUT.PUT_LINE('Name is :'|| INITCAP(V_Name));
DBMS_OUTPUT.PUT_LINE('Department no is :' || V_Deptno);
DBMS_OUTPUT.PUT_LINE('Designation : '|| V_Job);
DBMS_OUTPUT.PUT_LINE('Salary as for Indian Rupees : '|| TO_CHAR(V_Sal,'L99G999D99','NLS_CURRENCY=INR'));
DBMS_OUTPUT.PUT_LINE('Commission : ' || NVL(TO_CHAR(V_Comm),'Sorry...No Commissino'));
DBMS_OUTPUT.PUT_LINE('Hired on : ' || TO_CHAR(V_HireDate,'DDTh Month yyyySP '));
DBMS_OUTPUT.PUT_LINE('Working under manager ID ' || NVL(TO_CHAR(V_Mgr),'Can''t Controled ' ));
END;
/* Manager information with out selef join */
DECLARE
V_Empno NUMBER(4) :=&Empid;
V_Name VARCHAR2(20) ;
V_Deptno NUMBER(2) ;
V_Job VARCHAR2(10);
V_Sal NUMBER(4);
V_Comm NUMBER(4);
V_HireDate DATE;
V_Mgr NUMBER(4);
V_MgrName VARCHAR2(20);
BEGIN
DBMS_OUTPUT.PUT_LINE('CONNECT TO DATABASE...........');
SELECT
EName,Deptno,Job,Sal,Comm,HireDate,Mgr
INTO
V_Name,V_Deptno,V_Job,V_Sal,V_Comm,V_HireDate,V_Mgr
FROM Emp
WHERE Empno=V_Empno;
IF(V_Mgr IS NOT NULL) THEN
SELECT EName
INTO
V_MgrName
FROM Emp
WHERE Empno=V_Mgr;
ELSE
NULL;
END IF;
DBMS_OUTPUT.PUT_LINE(' DATABASE.Connection Exit.');
DBMS_OUTPUT.PUT_LINE('Employee having Employe ID '|| V_Empno || ' has following details....');
DBMS_OUTPUT.PUT_LINE('Name is :'|| INITCAP(V_Name));
DBMS_OUTPUT.PUT_LINE('Department no is :' || V_Deptno);
DBMS_OUTPUT.PUT_LINE('Designation : '|| V_Job);
DBMS_OUTPUT.PUT_LINE('Salary as for Indian Rupees : '|| TO_CHAR(V_Sal,'L99G999D99','NLS_CURRENCY=INR'));
DBMS_OUTPUT.PUT_LINE('Commission : ' || NVL(TO_CHAR(V_Comm),'Sorry...No Commissino'));
DBMS_OUTPUT.PUT_LINE('Hired on : ' || TO_CHAR(V_HireDate,'DDTh Month yyyySP '));
DBMS_OUTPUT.PUT_LINE('Working under manager ID ' || NVL(TO_CHAR(V_Mgr),'Can''t Controled ' )|| ' and manager name is ' || NVL(V_MgrName,V_Name)) ;
END;
/* Department Info with out join */
DECLARE
V_Empno NUMBER(4) :=&Empid;
V_Name VARCHAR2(20) ;
V_Deptno NUMBER(2) ;
V_Job VARCHAR2(10);
V_Sal NUMBER(4);
V_Comm NUMBER(4);
V_HireDate DATE;
V_Mgr NUMBER(4);
V_MgrName VARCHAR2(20);
V_Dname VARCHAR2(20);
V_LOC VARCHAR2(20);
BEGIN
DBMS_OUTPUT.PUT_LINE('CONNECT TO DATABASE...........');
SELECT
EName,Deptno,Job,Sal,Comm,HireDate,Mgr
INTO
V_Name,V_Deptno,V_Job,V_Sal,V_Comm,V_HireDate,V_Mgr
FROM Emp
WHERE Empno=V_Empno;
IF(V_Mgr IS NOT NULL) THEN
SELECT
EName
INTO
V_MgrName
FROM Emp
WHERE Empno=V_Mgr;
ELSE
NULL;
END IF;
IF(V_Deptno IS NOT NULL) THEN
SELECT
DName,LOC
INTO
V_Dname,V_Loc
FROM Dept
WHERE Deptno=V_Deptno;
ELSE
NULL;
END IF;
DBMS_OUTPUT.PUT_LINE(' DATABASE.Connection Exit.');
DBMS_OUTPUT.PUT_LINE('Employee having Employe ID '|| V_Empno || ' has following details....');
DBMS_OUTPUT.PUT_LINE('Name is :'|| INITCAP(V_Name));
DBMS_OUTPUT.PUT_LINE('Department no is :' || V_Deptno || ' Department name is ' || V_DName || ' is Situated at ' || V_Loc);
DBMS_OUTPUT.PUT_LINE('Designation : '|| V_Job);
DBMS_OUTPUT.PUT_LINE('Salary as for Indian Rupees : '|| TO_CHAR(V_Sal,'L99G999D99','NLS_CURRENCY=INR'));
DBMS_OUTPUT.PUT_LINE('Commission : ' || NVL(TO_CHAR(V_Comm),'Sorry...No Commissino'));
DBMS_OUTPUT.PUT_LINE('Hired on : ' || TO_CHAR(V_HireDate,'DDTh Month yyyySP '));
DBMS_OUTPUT.PUT_LINE('Working under manager ID ' || NVL(TO_CHAR(V_Mgr),'Can''t Controled ' )|| ' and manager name is ' || NVL(V_MgrName,V_Name)) ;
END;
/ Dpartment info salGrade info, manager info witho out nay join */
DECLARE
V_Empno NUMBER(4) :=&Empid;
V_Name VARCHAR2(20) ;
V_Deptno NUMBER(2) ;
V_Job VARCHAR2(10);
V_Sal NUMBER(4);
V_Comm NUMBER(4);
V_HireDate DATE;
V_Mgr NUMBER(4);
V_MgrName VARCHAR2(20);
V_Dname VARCHAR2(20);
V_LOC VARCHAR2(20);
V_Grade NUMBER(2);
BEGIN
DBMS_OUTPUT.PUT_LINE('CONNECT TO DATABASE...........');
SELECT EName,Deptno,Job,Sal,Comm,HireDate,Mgr
INTO
V_Name,V_Deptno,V_Job,V_Sal,V_Comm,V_HireDate,V_Mgr
FROM Emp
WHERE Empno=V_Empno;
IF(V_Mgr IS NOT NULL) THEN
SELECT
EName
INTO
V_MgrName
FROM Emp
WHERE Empno=V_Mgr;
ELSE
NULL;
END IF;
IF(V_Deptno IS NOT NULL) THEN
SELECT
DName,LOC
INTO
V_Dname,V_Loc
FROM Dept
WHERE Deptno=V_Deptno;
ELSE
NULL;
END IF;
IF(V_Sal IS NOT NULL) THEN
SELECT
Grade
INTO
V_Grade
FROM SalGrade
WHERE V_Sal BETWEEN LoSal AND HiSal;
ELSE
NULL;
END IF;
DBMS_OUTPUT.PUT_LINE(' DATABASE.Connection Exit.');
DBMS_OUTPUT.PUT_LINE('Employee having Employe ID '|| V_Empno || ' has following details....');
DBMS_OUTPUT.PUT_LINE('Name is :'|| INITCAP(V_Name));
DBMS_OUTPUT.PUT_LINE('Department no is :' || V_Deptno || ' Department name is ' || V_DName || ' is Situated at ' || V_Loc);
DBMS_OUTPUT.PUT_LINE('Designation : '|| V_Job);
DBMS_OUTPUT.PUT_LINE('Salary as for Indian Rupees : '|| TO_CHAR(V_Sal,'L99G999D99','NLS_CURRENCY=INR') || 'and pay grade is ' || V_Grade);
DBMS_OUTPUT.PUT_LINE('Commission : ' || NVL(TO_CHAR(V_Comm),'Sorry...No Commissino'));
DBMS_OUTPUT.PUT_LINE('Hired on : ' || TO_CHAR(V_HireDate,'DDTh Month yyyySP '));
DBMS_OUTPUT.PUT_LINE('Working under manager ID ' || NVL(TO_CHAR(V_Mgr),'Can''t Controled ' )|| ' and manager name is ' || NVL(V_MgrName,V_Name)) ;
END;
===========================================================
Anchor Data type format
===========================================================
DECLARE
V_Empno Emp.Empno%TYPE :=&Empid;
V_Name Emp.Ename%TYPE;
V_Deptno Emp.Deptno%TYPE;
V_Job Emp.Job%TYPE;
V_Sal Emp.Sal%TYPE;
V_Comm Emp.Comm%TYPE;
V_HireDate Emp.HireDate%TYPE;
V_Mgr Emp.Mgr%TYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('CONNECT TO DATABASE...........');
SELECT EName,Deptno,Job,Sal,Comm,HireDate,Mgr
INTO
V_Name,V_Deptno,V_Job,V_Sal,V_Comm,V_HireDate,V_Mgr
FROM Emp
WHERE Empno=V_Empno;
DBMS_OUTPUT.PUT_LINE('Employee having Employe ID '|| V_Empno || ' has following details....');
DBMS_OUTPUT.PUT_LINE('Name is :'|| INITCAP(V_Name));
DBMS_OUTPUT.PUT_LINE('Department no is :' || V_Deptno);
DBMS_OUTPUT.PUT_LINE('Designation : '|| V_Job);
DBMS_OUTPUT.PUT_LINE('Salary as for Indian Rupees : '|| TO_CHAR(V_Sal,'L99G999D99','NLS_CURRENCY=INR'));
DBMS_OUTPUT.PUT_LINE('Commission : ' || NVL(TO_CHAR(V_Comm),'Sorry...No Commissino'));
DBMS_OUTPUT.PUT_LINE('Hired on : ' || TO_CHAR(V_HireDate,'DDTh Month yyyySP '));
DBMS_OUTPUT.PUT_LINE('Working under manager ID ' || NVL(TO_CHAR(V_Mgr),'Can''t Controled ' ));
END;
/* Manager information with out selef join */
DECLARE
V_Empno Emp.Empno%TYPE :=&Empid;
V_Name Emp.EName%TYPE;
V_Deptno Emp.Deptno%TYPE;
V_Job Emp.Job%TYPE;
V_Sal Emp.Sal%TYPE;
V_Comm Emp.Comm%TYPE;
V_HireDate Emp.HireDate%TYPE;
V_Mgr Emp.Mgr%TYPE;
V_MgrName Emp.EName%TYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('CONNECT TO DATABASE...........');
SELECT
EName,Deptno,Job,Sal,Comm,HireDate,Mgr
INTO
V_Name,V_Deptno,V_Job,V_Sal,V_Comm,V_HireDate,V_Mgr
FROM Emp
WHERE Empno=V_Empno;
IF(V_Mgr IS NOT NULL) THEN
SELECT EName
INTO
V_MgrName
FROM Emp
WHERE Empno=V_Mgr;
ELSE
NULL;
END IF;
DBMS_OUTPUT.PUT_LINE(' DATABASE.Connection Exit.');
DBMS_OUTPUT.PUT_LINE('Employee having Employe ID '|| V_Empno || ' has following details....');
DBMS_OUTPUT.PUT_LINE('Name is :'|| INITCAP(V_Name));
DBMS_OUTPUT.PUT_LINE('Department no is :' || V_Deptno);
DBMS_OUTPUT.PUT_LINE('Designation : '|| V_Job);
DBMS_OUTPUT.PUT_LINE('Salary as for Indian Rupees : '|| TO_CHAR(V_Sal,'L99G999D99','NLS_CURRENCY=INR'));
DBMS_OUTPUT.PUT_LINE('Commission : ' || NVL(TO_CHAR(V_Comm),'Sorry...No Commissino'));
DBMS_OUTPUT.PUT_LINE('Hired on : ' || TO_CHAR(V_HireDate,'DDTh Month yyyySP '));
DBMS_OUTPUT.PUT_LINE('Working under manager ID ' || NVL(TO_CHAR(V_Mgr),'Can''t Controled ' )|| ' and manager name is ' || NVL(V_MgrName,V_Name)) ;
END;
/* Department Info with out join */
DECLARE
V_Empno Emp.Empno%TYPE :=&Empid;
V_Name Emp.EName%TYPE ;
V_Deptno Emp.Deptno%TYPE;
V_Job Emp.Job%TYPE;
V_Sal Emp.Sal%TYPE;
V_Comm Emp.Comm%TYPE;
V_HireDate Emp.HireDate%TYPE;
V_Mgr Emp.Mgr%TYPE;
V_MgrName Emp.EName%TYPE;
V_Dname Dept.DName%TYPE;
V_LOC Dept.LOC%TYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('CONNECT TO DATABASE...........');
SELECT
EName,Deptno,Job,Sal,Comm,HireDate,Mgr
INTO
V_Name,V_Deptno,V_Job,V_Sal,V_Comm,V_HireDate,V_Mgr
FROM Emp
WHERE Empno=V_Empno;
IF(V_Mgr IS NOT NULL) THEN
SELECT
EName
INTO
V_MgrName
FROM Emp
WHERE Empno=V_Mgr;
ELSE
NULL;
END IF;
IF(V_Deptno IS NOT NULL) THEN
SELECT
DName,LOC
INTO
V_Dname,V_Loc
FROM Dept
WHERE Deptno=V_Deptno;
ELSE
NULL;
END IF;
DBMS_OUTPUT.PUT_LINE(' DATABASE.Connection Exit.');
DBMS_OUTPUT.PUT_LINE('Employee having Employe ID '|| V_Empno || ' has following details....');
DBMS_OUTPUT.PUT_LINE('Name is :'|| INITCAP(V_Name));
DBMS_OUTPUT.PUT_LINE('Department no is :' || V_Deptno || ' Department name is ' || V_DName || ' is Situated at ' || V_Loc);
DBMS_OUTPUT.PUT_LINE('Designation : '|| V_Job);
DBMS_OUTPUT.PUT_LINE('Salary as for Indian Rupees : '|| TO_CHAR(V_Sal,'L99G999D99','NLS_CURRENCY=INR'));
DBMS_OUTPUT.PUT_LINE('Commission : ' || NVL(TO_CHAR(V_Comm),'Sorry...No Commission'));
DBMS_OUTPUT.PUT_LINE('Hired on : ' || TO_CHAR(V_HireDate,'DDTh Month yyyySP '));
DBMS_OUTPUT.PUT_LINE('Working under manager ID ' || NVL(TO_CHAR(V_Mgr),'Can''t Controled ' )|| ' and manager name is ' || NVL(V_MgrName,V_Name)) ;
END;
DECLARE
s
V_Name Emp.EName%TYPE ;
V_Deptno Emp.Deptno%TYPE;
V_Job Emp.Job%TYPE;
V_Sal Emp.Sal%TYPE;
V_Comm Emp.Comm%TYPE;
V_HireDate Emp.HireDate%TYPE;
V_Mgr Emp.Mgr%TYPE;
V_MgrName Emp.EName%TYPE;
V_Dname Dept.Dname%TYPE;
V_LOC Dept.LOC%TYPE;
V_Grade SalGrade.Grade%TYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('CONNECT TO DATABASE...........');
SELECT EName,Deptno,Job,Sal,Comm,HireDate,Mgr
INTO
V_Name,V_Deptno,V_Job,V_Sal,V_Comm,V_HireDate,V_Mgr
FROM Emp
WHERE Empno=V_Empno;
IF(V_Mgr IS NOT NULL) THEN
SELECT
EName
INTO
V_MgrName
FROM Emp
WHERE Empno=V_Mgr;
ELSE
NULL;
END IF;
IF(V_Deptno IS NOT NULL) THEN
SELECT
DName,LOC
INTO
V_Dname,V_Loc
FROM Dept
WHERE Deptno=V_Deptno;
ELSE
NULL;
END IF;
IF(V_Sal IS NOT NULL) THEN
SELECT
Grade
INTO
V_Grade
FROM SalGrade
WHERE V_Sal BETWEEN LoSal AND HiSal;
ELSE
NULL;
END IF;
DBMS_OUTPUT.PUT_LINE(' DATABASE.Connection Exit.');
DBMS_OUTPUT.PUT_LINE('Employee having Employe ID '|| V_Empno || ' has following details....');
DBMS_OUTPUT.PUT_LINE('Name is :'|| INITCAP(V_Name));
DBMS_OUTPUT.PUT_LINE('Department no is :' || V_Deptno || ' Department name is ' || V_DName || ' is Situated at ' || V_Loc);
DBMS_OUTPUT.PUT_LINE('Designation : '|| V_Job);
DBMS_OUTPUT.PUT_LINE('Salary as for Indian Rupees : '|| TO_CHAR(V_Sal,'L99G999D99','NLS_CURRENCY=INR') || 'and pay grade is ' || V_Grade);
DBMS_OUTPUT.PUT_LINE('Commission : ' || NVL(TO_CHAR(V_Comm),'Sorry...No Commissino'));
DBMS_OUTPUT.PUT_LINE('Hired on : ' || TO_CHAR(V_HireDate,'DDTh Month yyyySP '));
DBMS_OUTPUT.PUT_LINE('Working under manager ID ' || NVL(TO_CHAR(V_Mgr),'Can''t Controled ' )|| ' and manager name is ' || NVL(V_MgrName,V_Name)) ;
END;
=================================================================
Row Type Data
=================================================================
DECLARE
EmpRecode Emp%ROWTYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('CONNECT TO DATABASE...........');
SELECT
*
INTO
EmpRecode
FROM Emp
WHERE Empno=&Empno;
DBMS_OUTPUT.PUT_LINE('Employee having Employe ID '|| EmpRecode.Empno || ' has following details....');
DBMS_OUTPUT.PUT_LINE('Name is :'|| INITCAP(EmpRecode.EName));
DBMS_OUTPUT.PUT_LINE('Department no is :' || EmpRecode.Deptno);
DBMS_OUTPUT.PUT_LINE('Designation : '||EmpRecode.Job);
DBMS_OUTPUT.PUT_LINE('Salary as for Indian Rupees : '|| TO_CHAR(EmpRecode.Sal,'L99G999D99','NLS_CURRENCY=INR'));
DBMS_OUTPUT.PUT_LINE('Commission : ' || NVL(TO_CHAR(EmpRecode.Comm),'Sorry...No Commissino'));
DBMS_OUTPUT.PUT_LINE('Hired on : ' || TO_CHAR(EmpRecode.HireDate,'DDTh Month yyyySP '));
DBMS_OUTPUT.PUT_LINE('Working under manager ID ' || NVL(TO_CHAR(EmpRecode.Mgr),'Can''t Controled ' ));
END;
DECLARE
EmpRecode Emp%ROWTYPE;
EmpRecodes EmpRecode%TYPE;
V_MgrName Emp.EName%TYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('CONNECT TO DATABASE...........');
SELECT
*
INTO
EmpRecodes
FROM Emp
WHERE Empno=&EmployeeID;
IF(EmpRecodes.Mgr IS NOT NULL) THEN
SELECT
EName
INTO
V_MgrName
FROM Emp
WHERE Empno=EmpRecodes.Mgr;
ELSE
NULL;
END IF;
DBMS_OUTPUT.PUT_LINE(' DATABASE.Connection Exit.');
DBMS_OUTPUT.PUT_LINE('Employee having Employe ID '|| EmpRecodes.Empno || ' has following details....');
DBMS_OUTPUT.PUT_LINE('Name is :'|| INITCAP(EmpRecodes.EName));
DBMS_OUTPUT.PUT_LINE('Department no is :' || EmpRecodes.Deptno);
DBMS_OUTPUT.PUT_LINE('Designation : '|| EmpRecodes.Job);
DBMS_OUTPUT.PUT_LINE('Salary as for Indian Rupees : '|| TO_CHAR(EmpRecodes.Sal,'L99G999D99','NLS_CURRENCY=INR'));
DBMS_OUTPUT.PUT_LINE('Commission : ' || NVL(TO_CHAR(EmpRecodes.Comm),'Sorry...No Commissino'));
DBMS_OUTPUT.PUT_LINE('Hired on : ' || TO_CHAR(EmpRecodes.HireDate,'DDTh Month yyyySP '));
DBMS_OUTPUT.PUT_LINE('Working under manager ID ' || NVL(TO_CHAR(EmpRecodes.Mgr),'Can''t Controled ' )|| ' and manager name is ' || NVL(V_MgrName,EmpRecodes.EName)) ;
END;
/* Department Info with out join */
DECLARE
EmpRecode Emp%ROWTYPE;
EmpRecodes EmpRecode%TYPE;
V_MgrName EmpRecodes.EName%TYPE;
Department Dept%ROWTYPE;
V_LOC VARCHAR2(20);
BEGIN
DBMS_OUTPUT.PUT_LINE('CONNECT TO DATABASE...........');
SELECT
*
INTO
EmpRecodes
FROM Emp
WHERE Empno=&Empno;
IF(EmpRecodes.Mgr IS NOT NULL) THEN
SELECT
EName
INTO
V_MgrName
FROM Emp
WHERE Empno=EmpRecodes.Mgr;
ELSE
NULL;
END IF;
IF(EmpRecodes.Deptno IS NOT NULL) THEN
SELECT
*
INTO
Department
FROM Dept
WHERE Deptno=EmpRecodes.Deptno;
ELSE
NULL;
END IF;
DBMS_OUTPUT.PUT_LINE(' DATABASE.Connection Exit.');
DBMS_OUTPUT.PUT_LINE('Employee having Employe ID '|| EmpRecodes.Empno || ' has following details....');
DBMS_OUTPUT.PUT_LINE('Name is :'|| INITCAP(EmpRecodes.EName));
DBMS_OUTPUT.PUT_LINE('Department no is :' || EmpRecodes.Deptno || ' Department name is ' || Department.DName || ' is Situated at ' || Department.Loc);
DBMS_OUTPUT.PUT_LINE('Designation : '|| EmpRecodes.Job);
DBMS_OUTPUT.PUT_LINE('Salary as for Indian Rupees : '|| TO_CHAR(EmpRecodes.Sal,'L99G999D99','NLS_CURRENCY=INR'));
DBMS_OUTPUT.PUT_LINE('Commission : ' || NVL(TO_CHAR(EmpRecodes.Comm),'Sorry...No Commissino'));
DBMS_OUTPUT.PUT_LINE('Hired on : ' || TO_CHAR(EmpRecodes.HireDate,'DDTh Month yyyySP '));
DBMS_OUTPUT.PUT_LINE('Working under manager ID ' || NVL(TO_CHAR(EmpRecodes.Mgr),'Can''t Controled ' )|| ' and manager name is ' || NVL(V_MgrName,EmpRecodes.EName)) ;
END;
DECLARE
EmpRecode Emp%ROWTYPE;
Employee EmpRecode%TYPE;
V_MgrName Employee.EName%TYPE;
Department Dept%ROWTYPE;
SalaryGrade SalGrade%ROWTYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('CONNECT TO DATABASE...........');
SELECT
*
INTO
Employee
FROM Emp
WHERE Empno=&Empno;
IF(Employee.Mgr IS NOT NULL) THEN
SELECT
EName
INTO
V_MgrName
FROM Emp
WHERE Empno=Employee.Mgr;
ELSE
NULL;
END IF;
IF(Employee.Deptno IS NOT NULL) THEN
SELECT
*
INTO
Department
FROM Dept
WHERE Deptno=Employee.Deptno;
ELSE
NULL;
END IF;
IF(Employee.Sal IS NOT NULL) THEN
SELECT
Grade
INTO
SalaryGrade.Grade
FROM SalGrade
WHERE Employee.Sal BETWEEN LoSal AND HiSal;
ELSE
NULL;
END IF;
DBMS_OUTPUT.PUT_LINE(' DATABASE.Connection Exit.');
DBMS_OUTPUT.PUT_LINE('Employee having Employe ID '||Employee.Empno || ' has following details....');
DBMS_OUTPUT.PUT_LINE('Name is :'|| INITCAP(Employee.EName));
DBMS_OUTPUT.PUT_LINE('Department no is :' ||Employee.Deptno || ' Department name is ' || Department.DName || ' is Situated at ' || Department.Loc);
DBMS_OUTPUT.PUT_LINE('Designation : '|| Employee.Job);
DBMS_OUTPUT.PUT_LINE('Salary as for Indian Rupees : '|| TO_CHAR(Employee.Sal,'L99G999D99','NLS_CURRENCY=INR') || 'and pay grade is ' || SalaryGrade.Grade);
DBMS_OUTPUT.PUT_LINE('Commission : ' || NVL(TO_CHAR(Employee.Comm),'Sorry...No Commissino'));
DBMS_OUTPUT.PUT_LINE('Hired on : ' || TO_CHAR(Employee.HireDate,'DDTh Month yyyySP '));
DBMS_OUTPUT.PUT_LINE('Working under manager ID ' || NVL(TO_CHAR(Employee.Mgr),'Can''t Controled ' )|| ' and manager name is ' || NVL(V_MgrName,Employee.EName)) ;
END;
============================================================
User Type
===============================================================
DECLARE
V_Empno Emp.Empno%TYPE := &Empno;
TYPE EmpRecodeType IS RECORD
(
EName Emp.EName%TYPE,
Deptno Emp.Deptno%TYPE,
Sal Emp.Sal%TYPE
);
EmpRecode EmpRecodeType;
BEGIN
SELECT EName,Deptno,Sal
INTO
EmpRecode.EName,EmpRecode.Deptno,EmpRecode.Sal
FROM Emp
WHERE Empno=V_Empno;
DBMS_OUTPUT.PUT_LINE('The Details of the Employee having Employee number '|| V_Empno || 'are shown below ‘);
DBMS_OUTPUT.PUT_LINE('Name : '|| EmpRecode.EName );
DBMS_OUTPUT.PUT_LINE('Department Number is : '||EmpRecode.Deptno);
DBMS_OUTPUT.PUT_LINE('Salary as for indian rupee is : ' || TO_CHAR(EmpRecode.Sal,'L99G999D99',
END;
DECLARE
V_Empno Emp.Empno%TYPE := &Empno;
TYPE EmpType IS RECORD
(
Employee Emp%ROWTYPE
);
TYPE EmpDeptType IS RECORD
(
Depatment Dept%ROWTYPE,
Employees EmpType
);
TYPE EmpDeptGradeType IS RECORD
(
SalGrades SalGrade%ROWTYPE,
Depatments EmpDeptType
);
EmpDptGradeRecodes EmpDeptGradeType;
BEGIN
SELECT *
INTO
EmpDptGradeRecodes.Depatments.Employees.Employee
FROM Emp
WHERE Empno=V_Empno;
IF EmpDptGradeRecodes.Depatments.Employees.Employee.Deptno IS NOT NULL THEN
SELECT *
INTO
EmpDptGradeRecodes.Depatments.Depatment
FROM Dept
WHERE Deptno=EmpDptGradeRecodes.Depatments.Employees.Employee.Deptno;
ELSE
DBMS_OUTPUT.PUT_LINE('Sorry Employee is not assign into any Depatment ');
END IF;
IF EmpDptGradeRecodes.Depatments.Employees.Employee.Sal IS NOT NULL THEN
SELECT Grade
INTO
EmpDptGradeRecodes.SalGrades.Grade
FROM SalGrade
WHERE EmpDptGradeRecodes.Depatments.Employees.Employee.Sal
BETWEEN LoSal AND HiSal;
ELSE
DBMS_OUTPUT.PUT_LINE('Sorry ..Salary is not given to the Employee ');
END IF;
DBMS_OUTPUT.PUT_LINE('The Details of the Employee having Employee number '|| V_Empno || ' are shown below...........');
DBMS_OUTPUT.PUT_LINE('Name : '|| EmpDptGradeRecodes.Depatments.Employees.Employee.EName );
DBMS_OUTPUT.PUT_LINE('Department Number is : '||EmpDptGradeRecodes.Depatments.Employees.Employee.Deptno || ' Departname is '|| EmpDptGradeRecodes.Depatments.Depatment.DName || ' anr it''s situated at ' || EmpDptGradeRecodes.Depatments.Depatment.LOC);
DBMS_OUTPUT.PUT_LINE('Designation :'|| EmpDptGradeRecodes.Depatments.Employees.Employee.Job);
DBMS_OUTPUT.PUT_LINE('Hired on ' || TO_CHAR(EmpDptGradeRecodes.Depatments.Employees.Employee.HireDate,'DDTH MONTH DAY YYYYSP'));
DBMS_OUTPUT.PUT_LINE('Commison : '|| NVL(TO_CHAR(EmpDptGradeRecodes.Depatments.Employees.Employee.Comm,'9G999D99'),'Sorry.. No Commision'));
DBMS_OUTPUT.PUT_LINE('Mamager ID :' || NVL(TO_CHAR(EmpDptGradeRecodes.Depatments.Employees.Employee.Mgr),'Cant''t Controled'));
DBMS_OUTPUT.PUT_LINE('Salary as for indian rupee is : ' || TO_CHAR(EmpDptGradeRecodes.Depatments.Employees.Employee.Sal,'L99G999D99','NLS_CURRENCY= INR') || ' and his grade as por pay grade is ' || EmpDptGradeRecodes.SalGrades.Grade );
END;
Subscribe to:
Posts (Atom)