Difference Between Star schema and Snowflake Schema

Star schema	Snowflake Schema
1. De-Normalized Data Structure 2. Category wise Single Dimension Table 3. More data dependency and redundancy 4. No need to use complicated join 5. Query Results Faster 6. No Parent Table 7. Simple DB Structure	1. Normalized Data Structure 2. Dimension table split into many pieces 3. less data dependency and No redundancy 4. Complicated Join 5. Some delay in Query Processing 6. It May contain Parent Table 7. Complicated DB Structure

http://www.oraclecity.webs.com/

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;