/*****************************************************************
**** 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;
