Learn Oracle In Easy Way

WORKING WITH EXCEPTIONS IN PL/SQL

noreply@blogger.com (Phani Kumar) — Sun, 27 Jun 2010 14:52:00 +0000

WORKING WITH EXCEPTIONS IN PL/SQL:
=================================

EXCEPTIONS:

    a) It is an IDENTIFIER in PL/SQL, raised during the execution of a block that termination its main body of ACTIONS.
    b) A block terminates when PL/SQL raises an EXCEPTION, By Specifying an Exception HANDLER we can perform final action.

      METHODS TO RAISE AN EXCEPTION:
      ==============================
                a) An ORACLE ERROR OCCURS and the associated exception is raised automatically.
            b) Raise the EXCEPTION explicitly by issuing the RAISE statement within the Block.
                c) The RAISED EXCEPTION can be either USER DEFINED or PREDEFINED.

      EXCEPTION HANDLING:
      ==================
                a) Exception Handling can happier in two ways
                               * Exception trapping
                               * Exception propagation

                * EXCEPTION TRAPPING:
                  -------------------
                         -> If an Exception is raised in the Executable section of the Block then process the Exception to
                             the corresponding Exception Handling in the Exception Section of the same Block.
                         -> If the Exception is successfully handled then the exception should not propagate to the enclosing
                              block of environment.
                         -> If exception is trapped and Handled successfully then the PL/SQL Block terminated successfully.

                 * EXCEPTION PROPAGATION:
                   ----------------------
                         -> If an EXCEPTION is raised in the executable section of the block and there is no corresponding
                              Exception Handler then the PL/SQL Block terminates with failure and the exception is
                              propagated to the calling encampment.

                          EXCEPTION TRAPPING                            EXCEPTION PROPAGATION
                          DECLARE                                      DECLARE
                          BEGIN                    BEGIN
                          Exception Raised;                  Exception Raised;
              EXCEPTION                       EXCEPTION
            Exception Trapped;                 END
           END
                                    Exception Not Trapped
                                       |
                                       V
                                    Exception Propagation to calling Environment.

TYPES OF EXCEPTIONS:
====================

        a) As per PL/SQL Exception are considered to be of three types
                     * Predefined Oracle Server Errors
                     * Non-Predefined Oracle Server Errors
             * User Defined Errors.
        b) The PL/SQL programmer can program for exceptions to avoid disruption at Runtime.

SPECIFICATIONS OF EXCEPTIONS:
=============================

        1) EXCEPTION TYPE : Predefined Oracle Server Errors
           DESCRIPTION    : These are one of appropriately 20 errors that occurs most often in PL/SQL code.
           HANDLING TIP   : Do not declare and allow the ORACLE SERVER to RAISE then Implicitly.

        2) EXCEPTION TYPE : Non-Predefined Oracle Server Errors
           DESCRIPTION    : It is any other standard Oracle Server Error.
           HANDLING TIP   : Declare with in the Declarative Section and allow the Oracle Server to Raise them implicitly.

        3) EXCEPTION TYPE : User Defined Errors
           DESCRIPTION    : It is any condition that the developer determines, which is abnormal.
           HANDLING TIP   : Declare with in the Declarative Section and Raise Explicitly.

TRAPPING EXCEPTIONS:
====================

        a) Including a corresponding routine within the exception handling section of the PL/SQL block can trap any errors
           raised while running a PL/SQL Block.
    b) Each Exception Handler consists of WHEN clause which specifies an Exception.
    c) Each Exception is followed by a sequence of statements to be executed when that exception is raised.

              Syntax:
                         EXCEPTION
                         WHEN Exception 1[OR Exception 2.............]THEN
                                 Statement 1;
                                 Statement 2;
                                 Statement 3;
                                       .
                                       .
                                       .
                                       .
                         WHEN OTHERS THEN
                                 Statement 1;
                                 Statement 2;
                                       .
                                       .
                                       .

    d) OTHERS is an optional EXCEPTION handling clause that traps unspecified Exceptions.

WHEN OTHERS EXCEPTION HANDLER:
==============================

    a) The EXCEPTION Handling Section taps only those Exceptions that are specified any other Exceptions are not trapped
           unless the OTHERS EXCEPTION handles is used.
    b) The OTHERS should be the last exceptions handles in the defination.

TRAPPING EXCEPTIONS GUIDELINES:
===============================
    a) Begin the exception handling section of the Block with the keyword EXCEPTION.
    b) Define several Exception handlers, each with its own set of actions for the block.
    c) When an Exception occurs PL/SQL processes only one handles before leaving the block.
    d) Place the OTHERS clause after all other Exception Handling Clauses.
    e) We can have at most one OTHERS clause.
    f) EXCEPTIONS cannot appear in argument statements or SQL statements.

HANDLING PREDEFINED EXCEPTIONS:
===============================
    a) A PREDEFINED Oracle Server Error Tapped by referencing its standard Name within the corresponding EXCEPTION
        HANDLING Runtime.
    b) All predefined Exceptions are declared by PL/SQL in the STANDARD Package.

   SOME VERY COMMON PREDEFINED EXCEPTIONS:

         EXCEPTION NAME        NUMBER        DESCRIPTION

        ACCESS_INTO_NULL    ORA_06530    Attempted to assign values to
                            the attributes of an instantiated object.

        COLLECTION_IS_NULL    ORA_06531    Attempt to apply collection methods other
                            than Exists to an uninitiated nested table
                            or VARY.

CURSOR WITH SUBQURIES

noreply@blogger.com (Phani Kumar) — Sun, 27 Jun 2010 14:51:00 +0000

CURSOR WITH SUBQURIES:

     a) A CURSOR can be constructed upon the result provided through a SUBQUERY.
     b) The SUBQUERY can be an ordinary on of a correlated SUBQUERY.
     c) The SUBQUERY when evaluated can provide a value on a set of values to the statement.

        Example:
        -------
                 Sql > DECLARE
                               V_DeptNo Dept.DeptNo%TYPE;
                               V_DName Dept.DName%TYPE;
                               V_Staff NUMBER(4);
                               CURSOR StraffCountCursor IS
                                                  SELECT T1.DeptNo,T1.Dname,T2.Staff FROM Dept T1,
                                                         (SELECT DeptNo,COUNT(*) Staff FROm Emp GROUP BY DeptNo)T2
                                                                  WHERE T1.DeptNo=T2.DeptNo AND T2.Staff>=5;
                         BEGIN
                               OPEN StaffCountCursor;
                               LOOP
                                   FETCH StaffCountCursor INTO V_DeptNo,V-Dname,V-Staff;
                                   EXIT WHEN StaffCountCursor%NOTFOUND;
                                   DBMS_OUTPUT.PUT_LINE(V_DeptNo||''||V_Dname||''||V_Staff);
                               END LOOP;
                               CLOSE StaffCountCursor;
                        END;

DATA MANIPULATING THROUGH PL/SQL

noreply@blogger.com (Phani Kumar) — Sun, 27 Jun 2010 14:50:00 +0000

DATA MANIPULATING THROUGH PL/SQL:

        a) The data in the database is manipulated using the DML Commands.
        b) The DML Commands INSERT, UPDATE and DELETE can be used in PL/SQL without any restrictions.
        c) The Row Locks and Table Locks in the database are released using the COMMIT and ROLLBACK.

INSERTING DATA:

        a) The SQL INSERT Statement is used as it exists, in the PL/SQL Block.
        b) We can use SQL functions, like USER and SYSDATE as per requirements.
        c) The PRIMARY KEY values can be generated using the database sequences.
        d) Derive values within the PL/SQL Block as per necessity.
        e) Add Column default values if any.
        f) Any Identifiers in INSERT Clause must be an Database Column Name only, avoiding ambiguity.

      Example:
               SQL> BEGIN
                          INSERT INTO Emp(Empno, EName,Job,Deptno,MGR,Sal)
                          VALUES (Empsequence.NEXTVAL,'SATISH','MANAGER',10,7654,5000);
                    END;

UPDATING DATA:

        a) The Data Updation is an similar to the UPDATE statement used in SQL.
        b) A small amount of Ambiguity can arise in the SET clause of the UPDATE statement, as the identifier on the right side of the assignment operates can be a PL/SQL variable, or another database column.
        c) In the SET clause, the identifier on the left is always a database column.
        d) It is always better to implement a WHERE clause to that the required rows only are UPDATED.
        e) If any rows are not updated, then no errors is returned.
        f) A point to note this state is
                       * PL/SQL Variable Assignement use:=.
                       * SQL Column Assignement uses=.
                 Example:
                 Sql > DECLARE
                     V_EmpNo%TYPE:=&EmpNo;
                     V_Asterisk Emp.States%TYPE:=NULL;
                     CURSOR EmpCursor IS
                                   SELECT EmpNo, Nvl(ROUND(Sal/100),0) Sal FROM EmpNo=V_EmpNo FOR UPDATE;
              BEGIN
                     FOR EmpRecord IN EmpCursor
                     LOOP
                         FOR MyIndex IN 1...EmpRecord.Sal
                         LOOP
                             V_Asterisk:=V_Asterisk||'*';
                         END LOOP;
                         UPDATE Emp Set States= V_Asterisk
                                                     WHERE CURRENT OF EmpCursor;
                          V_Asterisk:=NULL;
                     END LOOP;
                     COMMIT;
              END;

PL/SQL TABLES

noreply@blogger.com (Phani Kumar) — Sun, 27 Jun 2010 14:48:00 +0000

PL/SQL TABLES:

        a) Objects of type TABLE are called PL/SQL tables.
        b) They are modeled as database tables, but are not same.
        c) PL/SQL tables use a Primary key to give array like access to Rows.

     Things To Note:
     --------------
              i) It is similar to an Array.
             ii) It shouls contains two components.
                        * A PRIMARY KEY of datatype BINARY_INTEGER, that Indexes the PL/SQL Table.
                        * A Column of a scalar or Record datatyoe which stores the PL/SQL TABLE elements.
            iii) It can increase dynamically as it is unconstrained.

CREATING A PL/SQL TABLE:
=========================

         a) There are two steps involved in creating a PL/SQL Table.
                   -> Declare a TABLE Datatype.
                   -> Declare a Variable of that Datatype.

                         Syntax:
                               TYPE TypeName IS TABLE OF
                               { ColumnType | Variable%TYPE
                | Table.Column%TYPE} [NOT NULL]
                [INDEX BY BINARY_INTEGER];

                               Identifier TypeName;

                         Example:
                               TYPE Ename_Table_TYOE IS TABLE OF Emp.Ename%TYPE INDEX BY BINARY INTEGER;
                               EnameTable Ename_Table_Type;

PL/SQL TABLE STRUCTURE:

            PRIMARY KEY              COLUMN

                      1                           SATISH
                      2                      RAJ
                      3                          KRISHNA

               BINARY_INTEGER             SCALAR

         a) The number of rows in PL/SQL table can increase dynamically, Hence a PL/SQL table can grow as new rows are added.
         b) PL/SQL tables can have one column and a PRIMARY KEY, neither of which can be named.
         c) The column can belong to any scalar or record datatype, but the PRIMARY KEY must belong to Typr BINARY_INTEGER.
         d) We cannot initialize a PL/SQL table in its declaration.

REFERENCING A PL/SQL TABLE:

         Syntax:
                PL/SQL_Table_Name(Primary_Key_Value);
         a) Primary_Key_Value belongs to type BINARY_INTEGER.
         b) The Primary Key value can be negative Indexing need not start with 1.
         c) The Methods that make PL/SQL Tables easier to use are

                * EXISTS(n)   -> Returns TRUE if the nth element in a PL/SQL table exists.
                * COUNT       -> Returns the number of elements that PL/SQL TABLE currently contains.
                * FIRST and
            LAST -> Returns the FIRST and Last index numbers in PL/SQL Table. Returns NULL if the PL/SQL table is empty.
                * Prior(n)    -> Returns the Index number that preceeds index n in a PL/SQL Table.
                * NEXT(n)     -> Returns the Index number taht succeeds Index n in a PL/SQL Table.
                * EXTEND(n.i) -> It increases the size of a PL/SQL Table.

                                   i) It appends one NULL element to a PL/SQL Table.
                                  ii) EXTEND(n) appends n NULL elements into a PL/SQL table.
                                 iii) EXTEND(n.i) appends n copies of the ith element to a PL/SQL Table.
                * TRIM       -> It removes one element from the end of a PL/SQL Table.
                * DELETE     -> It removes all elements from a PL/SQL Table.

                        DELETE(n), DELET(m,n)
         d) To Reference PL/SQL Table of Records we use
                        -> Table(index).Field
            Example:
                      SQL > DECLARE
                                   TYPE Dept_Table_Type IS TABLE OF Dept.DName%TYPE INDEX BY BINARY_INTEGER;
                                   My_Dept_Table Dept_Table_Type;
                                   V_Count NUMBER(2);
                            BEGIN
                                   SELECT COUNT(*) INTO V_Count FROM DEPT;
                                   FOR MyIndex IN 1...V_Count
                                   LOOP
                                       SELECT Dname INTO My_Dept_Table(MyIndex) FROM DEPT WHERE Deptno=MyIndex*10;
                                   END LOOP;
                                   FOR MyIndex IN 1....V_Count
                                   LOOP
                                       DBMS_OUTPUT_PUTLINE(My_Dept_Table(MyIndex));
                                   ENDLOOP;
                            END;

                      SQL > DECLARE
                                   TYPE Dept_Table_Type IS TABLE OF Dept%ROWTYPE INDEX BY BINARY_INTEGER;
                                   My_Dept_Table Dept_Table_Type;
                                   V_Count NUMBER(2);
                            BEGIN
                                   SELECT COUNT(*) INTO V_Count FROM DEPT;
                                   FOR MyIndex IN 1...V_Count
                                   LOOP
                                       SELECT *INTO My_Dept_Table(MyIndex) FROM DEPT WHERE Deptno=MyIndex*10;
                                   END LOOP;
                                   FOR MyIndex IN 1....V_Count
                                   LOOP
                                       DBMS_OUTPUT_PUTLINE('Department NUmber'||My_Dept_Table(MyIndex).DeptNO||'Department Name'||My_Dept_Table(MyIndex).DeptName ||'Department Location'||My_Dept_Table(MyIndex).LOC);
                                   ENDLOOP;
                            END;

%ROWTYPE ATTRIBUTE

noreply@blogger.com (Phani Kumar) — Sun, 27 Jun 2010 14:46:00 +0000

%ROWTYPE ATTRIBUTE:

        a) To declare a record based on a collection of columns in a database table or view, we can use the %ROWTYPE attribute.
        b) The fields in the Record take their Names and datatypes from the columns of the table or view.
        c) The Record can also store an entre row of the data fetched from cursor or cursor variable.
        d) Prefix %ROWTYPE with the database table.

                Syntax:
                        VariableName TableName%ROWTYPE;
                 Example:
                        V_EmpRecord Emp%ROWTYPE;

ADVANTAGES:
        a) The number and datatypes of the underlying database columns may not be known.
        b) The number and datatypes of the underlying database column may change at runtime.
        c) It is mostly when retrieving a row with the SELECT statement.
                  Example:
                          SQL > DECLARE
                                       V_Empno Emp.Empno%ROWTYPE:=&EnterEmpno;
                                       V_EmpRecord Emp%ROWTYPE;
                                  BEGIN
                                       SELECT * INTO V_EmpRecord FROM WHERE Empno=V_Empno;
                                              DBMS_OUTPUT.PUT_LINE('Employee Name:' || EmpRecord.V_EmpRecord.EName);
                                              DBMS_OUTPUT.PUT_LINE('Designation:'|| EmpRecord.Job);
                                  END;

DEFINING and DECLARING A PL/SQL RECORD

noreply@blogger.com (Phani Kumar) — Sun, 27 Jun 2010 14:45:00 +0000

DEFINING and DECLARING A PL/SQL RECORD:

       a) To create a Recoed, we define a RECORD type and then declare Records of that Type.
                * TypeName -> is the name of the RECORD Type.
                * FieldName -> it is the Name of the field within the Record.
                * FieldType -> is the datatype of the Field.
                * Expr      -> it is the FieldType or an initial value.

       b) The NOTNULL Constraint prevents the assigning of NULL's to those fields.
       c) Field declaration are like variable declarations each field has a Unique Name aand a specific datatype.
       d) The most important point to note is we must create the datatype first and then declare an identifier using the datatype.
                Example:
                         SQL > DECLARE
                                     TYPE EmpRecordType IS RECORD
                                       (
                                         EMpno NUMBER(4) NOT NULL:=500,
                                         EName Emp.EName%TYPE;
                                         Job Emp.Job%TYPE;
                                        );
                                     EmpRecord EmpRecordType;
       e) Fields in a record are accessed by Name.
       f) To reference of initialize an individual field, we must use dot notation.

                           RecordName.FieldName
                    Example:
                            SQL > DECLARE
                                         V_Empno NUMBER(4):=&EnterEmpmno;
                                         TYPE EmpRecordType IS RECORD
                                          (
                                            V_EName VARCHAR2(30),
                                            V_Deptno NUMBER(2),
                                            V_Job VARCHAR2(20),
                                          );
                                         EmpRecord EmpRecordType;
                                  BEGIN
                                       SELECT EName, Deptno, Job INTO
                                              EmpRecord.V_Ename,
                                              EmpRecord.V_Deptno,
                                              EmpRecord.V_Job
                                                             FROM EMP WHERE Empno=V_Empno;
                                              DBMS_OUTPUT.PUT_LINE('Employee Name' || EmpRecord.V_EName);
                                              DBMS_OUTPUT.PUT_LINE('Employee Department' || EmpRecord.V_Deptno);
                                              DBMS_OUTPUT.PUT_LINE('Employee Designation'|| EmpRecord.V_Job);
                                  END;

APPLYING COMPOSITE DATATYPES IN PL/SQL

noreply@blogger.com (Phani Kumar) — Sun, 27 Jun 2010 14:43:00 +0000

APPLYING COMPOSITE DATATYPES IN PL/SQL:

        a) The Composite datatypes in PL/SQL are also called as COLLECTIONS.
        b) The types of composite datatypes in PL/SQL are
                       * RECORD *TABLE *NESTED TABLE *VARRAY

PL/SQL RECORDS:


        a) A RECORD is a group of related data items stored in Fields, each with its own Name and Datatype.
        b) When a RECORD TYPE of fields in declared then they can be manipulated as a Unit.
        c) The Points to Note are:
                -> Each record defined can have as many fields as necessarily.
                -> Records can be assigned initial values and can be defined as NOT NULL.
                -> Fields without initial values are initialized to NULL.
                -> The DEFAULT keyword can be used when defining fields.
                -> RECORD Types can be declared as user define records in the declaritive part of any block.
                -> We can declare and reference nested records. A recoed can be the component of another record.

              Syntax:
                       TYPE Type_Name IS RECORD
                                      (Field_declaration[,Field_declaration]....);
                       Identifier Type_Name;

              * WHERE Field_Declaration is:

                       Field_Name{Field_Type| Variable%TYPE | Table.Column%TYPE| Table%ROWTYPE}
                                 [[ NOT NULL]
                                   { :=|DEFAULT}Expr]

USING SQL WITH PL/SQL:

noreply@blogger.com (Phani Kumar) — Sun, 27 Jun 2010 14:41:00 +0000

USING SQL WITH PL/SQL:

Data Retrieval Standards:

         a) Data Retrieval options range from basic SELECT statements to pattern matching with Regular Expressions.

                Sql> SELECT Statement

                    Syntax:
                            SELECT SelectList
                            [INTO VariableList]
                            FROM TableList
                            [WHERE WhereClause]
                            [ORDER BY ColumnList]
        b) The SelectList can be Columns, Strings, Built in Functions or * to retrieve data.
        c) Arithmetic operations are allowed in theSelect List.
        d) Variables can be declared as a single data type, or anchored datatype or entire record types.
        e) The table list can be one or more tables, views or inline views.
        f) The WHERE clause restricts the result set.
        g) When selecting a value into a variable, be sure to return one and only one variable.
        h) Only one record can be returned into a SELECT Statement in PL/SQL.
        i) The Common Errors Encountered are:

                 -> ORA-01403-Nodata Found.
                 -> ORA-01422-Exact fetch returns more than requested number of rows.
             Example:

                     Sql > DECLARE
                                  V_Empno NUMBER:=&EnterEmpno;
                                  V_EName VARCHAR2(30);
                                  V_Job VARCHAR2(30);
                                  V_Sal NUMBER(7,2);
                           BEGIN
                                  SELECT Ename, Job, Sal INTO V_Ename, V_Job, V_Sal From EMP WHERE Empno=V_Empno;
                DBMS_OUTPUT.PUT_LINE('The Name:' || V_Ename);
                DBMS_OUTPUT.PUT_LINE('The Job: ' || V_Job);
                DBMS_OUTPUT.PUT_LINE('The Sal: ' || V_Sal);
                           END;

        j) SELECT INTO ASSOCIATED WIYH CONDITION CONSTRUCTS and SQL BUILT IN FUNCTION.


                     Sql > DECLARE
                                  V_Empno NUMBER(4):=&EnterEmpno;
                                  V_EName VARCHAR2(30);
                                  V_Sal NUMBER(7,2);
                                  v_comm NUMBER(7,2);
                           BEGIN
                                  SELECT Ename,Sal, Comm INTO V_Ename, V_Sal, V_Comm From EMP WHERE Empno=V_Empno;
                DBMS_OUTPUT.PUT_LINE('The Name:' || INITCAP(V_Ename));
                DBMS_OUTPUT.PUT_LINE('The Salary: ' || TO_CHAR(V_Sal,'99,999.999'));
                DBMS_OUTPUT.PUT_LINE('The Commission: ' || NVL(TO_CGAR(V_CHAR(V_Comm),'NO Commission'));
                                  DBMS_OUTPUT.PUT_LINE('The Total sal is:'|| TO_CHAR(V_Sal+NVL(V_Comm,0)));
                           END;

% TYPE VARIABLE DECLARATION:
===========================
        a) The % Type variable is used to anchor PL/SQL variable to the datatype Type Columns.
        b) This methodology is more suitable where the variable that is declared in the program is mapping directly to a column in the database table.

                   Syntax:
                           VariableName TableName.ColumnName%Type;

        c) This methos keeps the PL/SQL program to be unaffected even when the datatypes within the database are changing.
        d) The metedology provides structural independency for the PL/SQL program.
                  Example:
                         SQL > DECLARE
                                      V_Empno Emp.Empno%TYPE;
                                      V_Empno Emp.Empno%TYPE;
                                      V_Sal Emp.Sal%TYPE;
                               BEGIN

ITERATIONS IN PL/SQL

noreply@blogger.com (Phani Kumar) — Sun, 27 Jun 2010 14:41:00 +0000

    3) ITERATIONS IN PL/SQL:


           a) LOOPs facilitates to repeat a statement or sequence of statements multiple times.
           b) The different types of PL/SQL Loops are

                         i) Basic or SImple Loop
                        ii) For Loops
                       iii) WHILE Loops

          i) BASIC OR SIMPLE LOOP:
              --------------------
                 a) It is the simplest form of the LOOP construct in PL/SQL.
                 b) It encloses a sequence o statements between the keywords LOOP and END LOOP.
                 c) It allows execution of its statements at least once.
                 d) If we keep the LOOP in finite state the EXIT statement is used.

                           Syntax:
                                   LOOP
                                        Statement1;
                                             :
                                             :
                                        EXIT[WHEN Condition];
                                   END LOOP;
                     -> EXIT Statement:
                             i) EXIT statement is used to terminate a LOOP.
                            ii) Once the LOOP is terminated, the Control passes to the next statement after the END LOOP.
                           iii) EXIT can be issued either as an action within an IF statement or as stand alone statement with LOOP.
                            iv) The EXIT statement should always be placed inside a LOOP.
                             v) EXIT ca be associated with in a WHEN clause to allow conditional termination of the LOOP.
                            vi) A Basic Loop can contain multiple EXIT statements.
                           vii) The EXIT condition can be at the top of the LOOP or at the end of the LOOP as per Logical Convenience.

                                  Example: \SimpleLoop\ SimpleExit1.sql

                    Sql> DECLARE
                            V_Num NUMBER:=1;
                          BEGIN
                         LOOP
                            DBMS_OUTPUT.PUT_LINE('The Line'||V_Num||'Output is'||V_Num);
                            V_Num:=V_Num+1;
                            IF V_Num>5 THEN
                                   EXIT;
                            END IF;
                        END LOOP;
                             DBMS_OUTPUT.PUT_LINE('The Total Lines of Outputs are'|| TO_CHAR(V_Num-1));
                              END;

                                  Example: \SimpleLoop\ SimpleLoopExitWhen2.sql

                    Sql> DECLARE
                            V_Num NUMBER:=1;
                          BEGIN
                         LOOP
                            DBMS_OUTPUT.PUT_LINE('The Line'||V_Num||'Output is'||V_Num);
                            V_Num:=V_Num+1;
                            EXIT WHEN V_Num>5;
                        END LOOP;
                             DBMS_OUTPUT.PUT_LINE('The Total Lines of Outputs are'|| TO_CHAR(V_Num-1));
                              END;

                  ->NESTED LOOPS:
                            i) It is a situation where one Loop is embedded into the other.
               ii) The Outer Loop and the Inner Loop get associated with one another and executed simultaniously.
                          iii) The overall Loop terminates in declared by the Outer Loop EXIT WHEN condition or EXIT cindition.

                                  Example: \SimpleLoop\NestedLoops3.sql
                                                        NestedLoops4.sql

                    Sql> DECLARE
                            V_Num NUMBER:=1;
                          BEGIN
                         LOOP
                            LOOP
                                EXIT WHEN V-Num>5;
                                DBMS_OUTPUT.PUT_LINE('Inner Loop:'||V_Num);
                                V_Num:=V_Num+1;
                            END LOOP;
                                                      DBMS_OUTPUT.PUT_LINE('Outer Loop:'||V_Num);
                                                      V_Num:=V_Num+1;
                                                      EXIT WHEN V_Num>10;
                        END LOOP;
                               END;

                ->WHILE LOOP:
                            i) It can be used to repeat a sequence of statements untill the controlling condition is no longer TRUE.
                           ii) The condition is evaluated at the start of each iteration.
                          iii) The Loop terminates when the condition is FALSE.

                               Syntax:
                                        WHILE Condition LOOP
                                                Statement1;
                                                Statement2;
                                                     :
                                         END LOOP;
                           iv) If the condition yields NULL, the LOOP is bypassed and control passed to the next statement.

                 -> NESTED LOOPS AND LABLES:
                             i) LOOPs can be nested to multiple levels.
                            ii) All the LOOPs can be Nested into one another.
                           iii) LOOPs can be Labeled as per the requirements.
                            iv) A Label is placed before the statement, eithe on the same line or on a separate line.
                             v) Label Loops by placing the label before the word Loop within the Label delimiters.
                            vi) When the LOOP is labled, the Label Name can be optionally included after the END LOOP statement for clarity.

                                   Example:
                                        SQL> DECLARE
                                                  V_Number NUMBER(2):=1;
                        V_Output VARCHAR2(100);
                                              BEGIN
                                                  WHILE V_Number<=10
                                                    LOOP
                             V_Output||''||V_Number;
                            V_Number:=V-Number+1;
                            END LOOP;
                        DBMS_OUTPUT.PUT_LINE(V_Output);
                          END;

                                        SQL> DECLARE
                                                  V_Number NUMBER;
                                                  V_Position NUMBER;
                                                  V_Result NUMBER:=0;
                         BEGIN
                                                  V_Num:=&NumbertoReverse;
                                                  WHILE V_Num>0
                                                    LOOP
                             V_Position:=MOD(V_Num,10);
                             V_Result:=(V_Result*10)+V_Position;
                                                        V_Num:=TRUNC(V_Num/10);
                            END LOOP;
                        DBMS_OUTPUT.PUT_LINE(V_Result);
                          END;

                 ->FOR LOOP:

                             i) It has the same general structure as the basic loop.
                            ii) FOR LOOP contains a control statement at the front of the LOOP Keyword, to determine the number of iterations that PL/SQL performs.

                                     Syntax:
                                                  FOR Counter IN[REVERSE]
                                                           LowerBound..UpperBound LOOP
                                                     Statement1;
                                                     Statement2;
                            :
                            :
                           END LOOP;
                                 Counter:
                                      It is an implicitly declared integer whose value automatically increased or decreased by 1 on each iteration of the LOOP untill the Upper Bound or Lower Bound is reached.
                                 REVERSE:
                                      It is a keyword, and causes the counter to decreament with each iteration from the UpperBound to the Lower Bound.
                           iii) The Counter need not be declared, as it implicitly declared as an Integer.
                            iv) The Lower Bound and the Upper Bound of the loop can be Literals, Variables or Expressions, but they should be evaluated to Integer.
                             v) The Lower and Upper bounds of a LOOP statement need not be numeric literals, they can be expressions that convert to numeric values.

                                     Example:
                                              Sql> DECLARE
                                                        V_StartRange NUMBER:=&StartRange;
                                                        V_EndRange NUMBER:=&EndRange;
                                                        V_Result VARCHAR2(500):=NULL;
                                                   BEGIN
                                                        DBMS_OUTPUT.PUT_LINE('Illustration of FOR LOOP......!');
                                                        FOR MyIndex IN V_StartRange.V-EndRange
                                                           LOOP
                                                             V_Result"=V_Result||''||V_StartRange;
                                                             V_StartRange:=V_StartRange+1;
                                                           END LOOP;
                                                         DBMS_OUTPUT.PUT_LINE(V_Result);
                                                   END;

                                              Sql> DECLARE
                                                        V_NumFact NUMBER:=&GiveNumber;
                                                        V_Factorial NUMBER:=1;
                                                   BEGIN
                                                        FOR Index1 IN V_NumFact
                                                           LOOP
                                                             V_Factorial:=V-Factorial*Index1;
                                                           END LOOP;
                                                         DBMS_OUTPUT.PUT_LINE(V_Factorial);
                                                   END;

                                              Sql> DECLARE
                                                        V_NumFact NUMBER:=&GiveNumber;
                                                        V_Factorial NUMBER:=1;
                                                   BEGIN
                                                        FOR Index1 IN REVERSE 1..V_NumFact
                                                           LOOP
                                                             V_Factorial:=V-Factorial*Index1;
                                 DBMS_OUTPUT.PUT_LINE(Index1);
                                                           END LOOP;
                                                         DBMS_OUTPUT.PUT_LINE(V_Factorial);
                                                   END;

                                              Sql> DECLARE
                                                        V_Name VARCHAR2(30);
                                                        V_Position VARCHAR2(200):=NULL;
                                                   BEGIN
                            V_Name:='&EnterName';
                                                        FOR 1 IN 1..LENGTH(V_Name)
                                                           LOOP
                                                             V_Position:=V_Position||''||SUBSTR(V_Nmae,1,1);
                               END LOOP;
                                                         DBMS_OUTPUT.PUT_LINE(V_Position);
                                                   END;

                                              Sql> DECLARE
                                                        V_MyChar Varchar2(20);
                                                   BEGIN
                            FOR MyIndex1 IN 1..15
                                                           LOOP
                                                               FOR MyIndex2 IN 1...MyIndex1
                                     LOOP
                                     V_MyChar:=V_MyChar||"*";
                                                              END LOOP;
                                                              DBMS_OUTPUT.PUT_LINE(V_MyChar);
                                                              V_MyChar:=NULL;
                                                           END LOOP;
                                                   END;

                                              Sql> DECLARE
                                                        V_OuterLoopRange NUMBER:=5;
                                                        V_InnerLoopRange NUMBER:=3;
                                                   BEGIN
                            <>
                            FOR MyIndex IN 1..V_OuterLoopRange
                                                           LOOP
                                   DBMS_OUTPUT.PUT_LINE('OuterLoop:'|| MyIndex);
                                   <>
                                                               FOR MyIndex2 IN 1..V_InnerLoopRange
                                     LOOP
                                     DBMS_OUTPUT.PUT_LINE('InnerLoop:'|| MyIndex2);
                                                              END LOOP;
                                                           END LOOP;
                                                   END;

CONTROL STRUCTURES IN PL/SQL:

noreply@blogger.com (Phani Kumar) — Sun, 27 Jun 2010 14:36:00 +0000

CONTROL STRUCTURES IN PL/SQL:

a) To write programs that reflect the real time requirement, we need:
         1) BRANCHING
         2) SELECTION
         3) LOOPING
1) BRANCHING STATEMENTS:

              i) In PL/SQL branching is implemented using the IF statements.
              ii) PL/SQL provides three types of conditional control.
                        * Simple IF * ELSEIF * ELSE IF
              iii) The branching standards can be implemented either as
                        * Simple IF
                        * Nested IF
                        * ELSE IF ladders

                    GENERAL SYNTAX:
                           IF Condition1 THEN
                                    Statement1;
                                    Statement2;
                                         :
                                         :
                           END IF;
              iv) IF is a reversed word and marks the beggning of the IF statements.
              v) The END IF is a reversed phrase that indicates the end of the IF..THEN construct.
              vi) When IF..THEN is executed, a condition is evaluated to either TRUE or FALSE.

                Example:
                  SQL > DECLARE
                              V_Number1 NUMBER:=5;
                              V_Number2 NUMBER:=3;
                              V_Temp NUMBER;
                        BEGIN
                              DBMS_OUTPUT.PUT_LINE('Oriinal V_Number1='||V_Number1);
                              DBMS_OUTPUT.PUT_LINE('Oriinal V_Number2='||V_Number2);
                          IF V_Number1>V-Number2 THEN
                                     V_TEMP:=V_Number1;
                      V_Number1:=V_Number2;
                         V_Number2:=V_TEMP;
                              END IF;
                   /* Print the values from variable which are swapped */
                        END;

         ->IF-THEN-ELSE STATEMENT:
                i) IF-THEN-ELSE statements enables to specify two groups of statements.
                ii) One group is evaluated when the condition evaluates to TRUE, the next group is evaluated when the condition evaluates to FALSE.

                      SYNTAX:
                              IF Condition THEN
                                      Statement 1;
                              ELSE
                                      STATEMENT 2;
                              END IF;
               iii) This concept should be used when trying to choose between two mutually exclusive actions.
                      Example:
                                DECLARE
                                       V_Num NUMBER:= &ENTERNumber;
                                BEGIN
                                       IF MOD(V_Num,2)=0 THEN
                                           DBMS_OUTPUT.PUT_LINE(V_Num||'Is Even');
                                       ELSE
                                           DBMS_OUTPUT.PUT_LINE(V_Num||'Is Odd');
                                       END IF;
                                END;

         ->BEHAVIOR OF NULL'S:
                i) If a condition is evaluated to NULL, then the statements will not be executed, instead the control will be passed to the first executable statement after the END IF,
                ii) In IF...THEN...ELSE construct the FALSE block is executed whenever the condition evaluates to NULL.
                          QL > DECLARE
                                      V_Num1 NUMBER:=&EnterNumber1;
                                      V_Num2 NUMBER:=&EnterNumber2;
                               BEGIN
                                      IF V_Num1=V_Num2 THEN
                                           DBMS_OUTPUT.PUT_LINE('Given Number are Equal');
                                      END IF;
                                           DBMS_OUTPUT.PUT_LINE('Did You Watch the NULL Effect.......');
                               END;
                               DISCUSS: EVENODD2.SQL
         ->ELSEIF STATEMENTS:

                   Syntax:
                           IF Condition1 THEN
                                 Statement1;
                           ELSEIF Condition2 THEN
                                 Statement2;
                           ELSEIF Condition3 THEN
                                 Statement3;
                                     :
                                     :
                           ELSE
                                 Statement n;
                           END IF;
                  i) In this construct for every false statement of an IF condition another IF Follows.
                  ii) This construct is also specified as ELSEIF ladders.
                  iii) The application syntax can also be produced with ELSE if condition structure.
                  iv) For clarity purpose rather than ELSEIF, ELSE IF construct is suggested.

                         Example:            \Ifstatements\simpleIF2.sql

                                  SQL > DECLARE
                                              V_Day VARCHAR2(3):=TO_CHAR(TO_DATE('& YourDate', 'DD-MON-YY'), 'DY');
                                        BEGIN
                                           IF V_Day='SAT' then
                                              DBMS_OUTPUT.PUT_LINE('Enjoy Weekend');
                                           ELSEIF V_Day='SUN' then
                                              DBMS_OUTPUT.PUT_LINE('Enjoy Weekend');
                                           ELSE
                                              DBMS_OUTPUT.PUT_LINE('Have a nice day');
                                           END IF
                                           END;
          -> NESTED IF STATEMENTS:

                   i) The IF statements can be nested into one another as per requirements.
                   ii) NESTED IF is a situation in which an IF follows another IF immediately for every true statement of an IF.
                   iii) Each IF is considered as an individual Block of IF and needs proper nesting.

                            Syntax:
                                    IF Condition THEN
                                         IF Condition THEN
                                                Statement1;
                                         ELSE
                                             IF Condition THEN
                                                    Statement2;
                                             ELSE
                                                    Statement2;
                                             END IF;
                                        END IF;
                                    ELSE
                                        Statement3;
                                    END IF;

                              Example: \IFStatements\Nestedif5.sql

                                     SQL > DECLARE
                                                  V_Year NUMBER:=&YEAR;
                                           BEGIN
                                                  IF MOD(V_Year,4)=0 THEN
                                                       IF MOD(V_Year,100)=0 THEN
                                                            DBMS_OUTPUT.PUT_LINE(V_Year||'is a Leap Year');
                                                       ELSE
                                                          IF MOD(V_Year,400)=0 THEN
                                                               DBMS_OUTPUT.PUT_LINE(V_Year||'is a Leap Year');
                                                          ELSE
                                                               DBMS_OUTPUT.PUT_LINE(V_Year||'is Not a Leap Year');
                                                          END IF;
                                                       END IF;
                                                  ELSE
                                                     DBMS_OUTPUT.PUT_LINE(V_Year||'is Not a Leap Year');
                                                  END IF;
                                           END;

            -> ELSE IF LADDER:

                         i) This is a situation where a condition is followed for every FALSE statement of an IF Condition.
                         ii) Each FALSE statement is immediately associated with a condition of its own.

                              Syntax:
                                     IF Condition THEN
                                                Statement1;
                                     ELSEIF Condition THEN
                              Statement2;
                        ELSEIF Condition THEN
                        Statement3;
                        ELSE
                         Statement4;
                    END IF;

                       Note: Do not mistake this syntax with construt of : IF Cond THEN
                                        :
                                         ELSE
                                             IF Condition THEN
                                                 :
                  Example: \IF Statements\ELSEIFLadder.sql

                               DECLARE
                                     V_Operator VARCHAR2(2):='&Operator';
                                     V_Number1 NUMBER :=&Operator1;
                                     V_Number2 NUMBER :=&Operator1;
                               BEGIN
                                     IF V_Operator ='+' THEN
                                        DBMS_OUTPUT.PUT_LINE(TO_NUMBER(V_Number1+V_Number(2));
                                     ELSEIF V_Operator ='-' THEN
                                        DBMS_OUTPUT.PUT_LINE(TO_NUMBER(V_Number1-V_Number(2));
                                     ELSEIF V_Operator ='*' THEN
                                        DBMS_OUTPUT.PUT_LINE(TO_NUMBER(V_Number1*V_Number(2));
                                     ELSEIF V_Operator ='/' THEN
                                        DBMS_OUTPUT.PUT_LINE(TO_NUMBER(V_Number1/V_Number(2));
                                     ELSE
                                        DBMS_OUTPUT.PUT_LINE('Check Opreator');
                                     ENDIF;
                               END;
                             NOTE: The same program can be written using IF...THEN
                                                                          ELSE
                                                                          IF...THEN
   2)SELECTIONS IN PL/SQL:

             a) The SELECTION stratergy is implemented in PL/SQL   using CASE STATEMENTS.
             b) The CASE Statements in PL/SQL has two forms
                   i) Simple CASE
                   ii) Searched CASE
             c) In Simple CASE we have to specify a SELECTOR, which detemines which group of actions to get executed.
             d) In Searched Case, the SELECTOR is not present, it has search conditions that are evaluated in order to determine which group of actions to take place.

                     Simple CASE Syntax:

                                      CASE SELECTOR
                                                WHEN Expr1 THEN Statement1;
                                                WHEN Expr2 THEN Statement2;
                                                             :
                                                ELSE
                                                       Statement n;
                                       END CASE;
             e) The Reserved word CASE marks the begning of the CASE statement.
             f) The SELECTOR is a value that determines, which WHEN clause should be executed.
             g) Each WHEN clause contains are EXPRESSION and One or more executable statements associated with it.
             h) The ELSE clause is optional.
             i) Each CASE statement is marked with END CASE.
             j) The SELECTOR is evaluated only once.
                    Example: \Case statements\Simple CASE user3.sql

                          SQL > DECLARE
                                       V_Grade CHAR:= UPPER('&EnterGrade');
                                 BEGIN
                                    CASE V_Grade
                                        WHEN 'A' THEN
                                             DBMS_OUTPUT.PUT_LINE('Grade A');
                                        WHEN 'B' THEN
                                             DBMS_OUTPUT.PUT_LINE('Grade B');
                                        WHEN 'C' THEN
                                             DBMS_OUTPUT.PUT_LINE('Grade C');
                                        ELSE
                                             DBMS_OUTPUT.PUT_LINE(' No Grade');
                                    END CASE;
                                 END;
         ->SEARCHED CASE:
                  a) A SEARCHED CASE statement has searched conditions that yield Boolean values TRUE, FALSE or NULL.
                  b) When a particular search condition evaluates to TRUE, the group of statements associated with this condition are executed.

                           Syntax:
                                   CASE
                                   WHEN SearchCondition THEN Statement1;
                                   WHEN SearchCondition THEN Statement2;
                                                        :
                                   END StatementN;
                                   END CASE;
                  c) When a SEARCH CONDITION evaluates to TRUE, Control is passed to the statement associated with it.
                  d) If no SEARCH CONDITION yields to TRUE, then statements associated with ELSE caluse are executed.

                           Example:
                                 SQL > DECLARE
                                       V_Grade CHAR:= UPPER('&EnterGrade');
                                 BEGIN
                                    CASE
                                        WHEN V_Grade='A' THEN
                                             DBMS_OUTPUT.PUT_LINE('Grade A');
                                        WHEN V_Grade='B' THEN
                                             DBMS_OUTPUT.PUT_LINE('Grade B');
                                        WHEN V_Grade='C' THEN
                                             DBMS_OUTPUT.PUT_LINE('Grade C');
                                        ELSE
                                             DBMS_OUTPUT.PUT_LINE(' No Grade');
                                    END CASE;
                                 END;

          -> CASE EXPRESSION:
                  a) The result of an expression yields a single value that is assigned to a variable.
                  b) A CASE Expression is an expression where the CASE evaluated an expression to a single value and then it si assigned to a variable.
                  c) A CASE expression has a structure similar to a CASE statement.


                     DIFFERENCE TO NOTE......

                           CASE STATEMENT                      CASE EXPRESSION

                           CASE V_Flag                   CASE V_Flag
                             WHEN 0 THEN                       WHEN 0 THEN
                               DBMS_OUTPUT                             V_Num ||'is even'
                               PUT_LINE('PRINT')
                             ELSE                               ELSE
                               DMBS_OUTPUT                           V_Num ||'is odd'
                               PUT_LINE('PRINT')
                           END CASE;                END;
                  d) In CASE expression the END CASE is not implemented, instead only END, is used.
                  e) If any value is returned through CASE expression, then ensure that it always return the same datatype as executed.

                          Example: \Casestatements\CaseExpr.Sql

                              Sql>DECLARE
                                      V_NumberNUMBER:=&NUMBER;
                                      V_Result VARCHAR2(30);
                                  BEGIN
                                      V_RESULT:= CASE WHEN MOD(V_Number,2)=0
                                                 THEN
                                                    V_Number||'is an Even Number'
                                                 ELSE
                                                    V_Number||'is an Odd Number'
                                                 END;
                                      DBMS_OUTPUT.PUT_LINE(V_Result);
                                  END;

                           Discuss:   \CaseStatements\CaseExpr6.sql

           -> UNCONDITIONAL BRANCHING USING GOTO:

                   a) GOTO provides the ability to jump through a program from one place to another.
                   b) GOTO can be used with complex exception handlers in nested blocks, to make the execution section more readable.
                   c) It is better to have a limited usage of GOTO in programming block.
                   d) When a jump is proposed using GOTO it is assocoated with an appropriate label.

                           Syntax:    GOTO LabelName;

                           RULES TO CROSS CHECK:
                                   a) GOTO cannot reference a label in a Nested Block.
                                   b) GOTO cannot be executed outside an IF clause to label inside the IF clause.
                                   c) GOTO cannot be executed from inside an IF clause to a label inside another IF clause.
                                   d) GOTO cannot navigate from the EXCEPTION section to any other section of the block.

                              Example:
                                     SQL > DECLARE
                                                V_MyNumber NUMBER:=&GiveNumber;
                                           BEGIN
                                                IF MOD(V_MyNumber,2)=0 THEN
                                                       GOTO L_EVEN;
                                                ELSE
                                                       GOTO L_ODD;
                                                END IF;

                                                <>
                                                       DBMS_OUTPUT.PUT_LINE('Even Number');
                                                       RETURN;

                                                <>
                                                       DBMS_OUTPUT.PUT_LINE('Odd Number');
                                                       RETURN;
                                          END;

                  e) GOTO always needs a proper label to jump.
                  f) A Label does not need any GOTO for exception.
                  g) Hence to keep proper meaning within the sequence RETURN should be used .

PROGRAMMING STRUCTURES IN PL/SQL

noreply@blogger.com (Phani Kumar) — Sun, 27 Jun 2010 14:30:00 +0000

PROGRAMMING STRUCTURES IN PL/SQL:

a) Code maintenance can be made easier by
          1) Documenting code with comments.
          2) Developing a case convention for the code
          3) Developing naming conventions for identifiers and other objects.
          4) Enhancing readability by indenting.
b) SQL statements, PL/SQL keywords and datatypes are kept in uppercase.
c) Identifiers and parameters, database tables and columns are kept in lower casing and initcap casing.
d) The names of local variables and formal parameters take precedence over the names of database tables.
e) The names of columns take precedence over the names of local variables.

VARIABLE TYPES IN PL/SQL PROGRAMMING

noreply@blogger.com (Phani Kumar) — Sun, 27 Jun 2010 14:29:00 +0000

VARIABLE TYPES IN PL/SQL PROGRAMMING:

a) While passing the values to arguments at Runtime, the PL/SQL variables can be treated as the types.\
       1) Bind Variables
       2) PL/SQL Variables

    1) Bind Variables:
           i) In PL/SQL to reference Bind variables, it must be prefix with a colon(:).
           ii) In SQL*PLUS the Bind variables has to be displayed using the PRINT command.
                   Ex:
                       Sql>    VARIABLE G_SALARY NUMBER;

                       Sql>    DECLARE
                                   V_Sal NUMBER(7,2);
                               BEGIN
                                   SELECT Sal INTO V_Sal FROM Emp where Empno=7369;
                                   :G_Salary :=V-Sal;
                               END;
                      SQL>   PRINT G_Salary;
                      SQL>   SET ECHO OFF;
                      SQL>   SET VERIFY OFF;
                      SQL>   SET SERVEROUTPUT ON;
                      SQL>   ACCEPT B_NUMBER1 PROMPT 'Please Enter the first number:';
                      SQL>   ACCEPT B_NUMBER2 PROMPT 'Please Enter the secong number:';

                      SQL> DECLARE
                               V_Number1 NUMBER(9,2):=&B_Number1;
                               V_Number2 Number(9,2):=&B_Number2;
                           BEGIN
                               :Bind_Variable:=(V_Number1/V_Number2)+V_Number2;
                           END;
                      SQL> PRINT Bind_Variable;

        DISCUSS: \Introduction\Vmethods\Bvari\Example1.sql
   \Introduction\Vmethods\Bvari\Example2.sql

               SQL> SET ECHO OFF
               SQL> SET VERIFY OFF
               SQL> VARIABLE B_Total NUMBER
               SQL> ACCEPT B_Salary PROMOT 'Please Enter the Salary amount:'
               SQL> ACCEPT B_Bonus PROMOT 'Please Enter the Bonus Percentage:'
               SQL> DECLARE
                         V_Salary NUMBER(9,2):=&B_Salary;
                         V_Bonus NUMBER(9,2):=&B_Bonus;
                    BEGIN
                        :B_Total :=NVL(V_Salary,0)*(1+NVL(V_Bonus,0)/100
                    END;

               SQL> PRINT B_Total;

    2) PL/SQL Variables:
           i) These are substitution variables that are directly declared within the program.
           ii) These variables can be associated to the variables declared in ACCEPT command.
           iii) The PL/SQL variables can be projected using the DBMS_OTUPUT.PUT_LINE method within PL/SQL program.


                      SQL>   SET ECHO OFF;
                      SQL>   SET VERIFY OFF;
                      SQL>   SET SERVEROUTPUT ON;
                      SQL>   ACCEPT P_NUMBER1 PROMPT 'Please Enter the first number:';
                      SQL>   ACCEPT P_NUMBER2 PROMPT 'Please Enter the secong number:';

                      SQL> DECLARE
                               V_Number1 NUMBER(9,2):=&P_Number1;
                               V_Number2 Number(9,2):=&P_Number2;
                               V_RESULT(9,2);
                           BEGIN
                               V_RESULT:=(V_Number1/V_Number2)+V_Number2;
                               DBMS_OUTPUT.PUT_LINE(V_Result);
                           END;

        DISCUSS: \Introduction\Vmethods\Pvari\Example1.sql
          \Introduction\Vmethods\Pvari\Example2.sql

               SQL> SET ECHO OFF
               SQL> SET VERIFY OFF
               SQL> SET SERVEROUTPUT ON;
               SQL> ACCEPT P_Salary PROMOT 'Please Enter the Salary amount:'
               SQL> ACCEPT P_Bonus PROMOT 'Please Enter the Bonus Percentage:'
               SQL> DECLARE
                         V_Salary NUMBER(9,2):=&P_Salary;
                         V_Bonus NUMBER(9,2):=&P_Bonus;
                         V_RESULT NUMBER(9,2);
                    BEGIN
                        DBMS_OUTPUT.PUT_LINE('The salary is the Bonus is Rs'||TO_CHAR(NVL(V_Salary,0)*(1+NVL(V_Bonus,0)/100)));
                    END;

USING NAMED BLOCKS TO INCREASE VISIBILITY:

noreply@blogger.com (Phani Kumar) — Sun, 27 Jun 2010 14:27:00 +0000

USING NAMED BLOCKS TO INCREASE VISIBILITY:

       Sql>    <>
               DECLARE
                     A VARCHAR2(30):='Hello Guys';
                     B REAL:=300;
               BEGIN
                     DBMS_OUTPUT.PUTLINE('The Value of Outer String A is available Here:='||A);
                     DBMS_OUTPUT.PUTLINE('The Value of Outer Real B is available Here:='||B);

                     DECLARE
                           A INTEGER :=2000;
                           B REAL:=400;
                     BEGIN
                           DBMS_OUTPUT.PUTLINE('The Value of Inner Integer A is available Here:='||A);
                           DBMS_OUTPUT.PUTLINE('The Value of Inner Real B is available Here:='||B);
                           DBMS_OUTPUT.PUTLINE('The Value of Outer String A is available Here:='||FirstBlock.A);
                     END;
               END;

SCOPE AND VISIBILITY DIAGRAM

noreply@blogger.com (Phani Kumar) — Sun, 27 Jun 2010 14:26:00 +0000

SCOPE AND VISIBILITY DIAGRAM:

     Sql>    DECLARE
                    x BINARY_INTEGER;
             BEGIN
                    .......
                    ..........
                    DELARE
                          y NUMBER;
                    BEGIN
                          .....
                          ..........
                                    End of 'y';
                    END;
             END;

NESTED BLOCKS AND VARIABLE SCOPE:

a) The variable 'y' can reference the variable named 'x'.
b) The variable 'x' cannot reference variable 'y'.
c) If the variable named y in the nested block is given the same name as the variable name x in the outer block its value is valid only for the duration of the nested block.


       Sql> DECLARE
                   X REAL:=205;
            BEGIN
                   DBMS_OUTPUT.PUTLINE('The Value of Outer X:='||x);
                        DECLARE
                              X REAL:=405;
                        BEGIN
                              DBMS_OUTPUT.PUTLINE('The Value of Inner X:='||x);
                        END;
                   DBMS_OUTPUT.PUTLINE('The Value of Outer X:='||x);
            END;
      Sql> DECLARE
                   A VARCHAR2(30):='Hello Guys';
                   B REAL:=0;
            BEGIN
                  DBMS_OUTPUT.PUTLINE('The Value of Outer String A is available Here:='||A);
                  DBMS_OUTPUT.PUTLINE('The Value of Outer Real B is available Here:='||B);

                  DECLARE
                        A INTEGER :=2000;
                        B REAL:=400;
                  BEGIN
                      DBMS_OUTPUT.PUTLINE('The Value of Inner Integer A is available Here:='||A);
                      DBMS_OUTPUT.PUTLINE('The Value of Inner Real B is available Here:='||B);
                  END;
                       DECLARE
                              D REAL:=500;
                              A VARCHAR2(30):='Hellow Gals';
                       BEGIN
                            DBMS_OUTPUT.PUTLINE('The Value of Last B is available Here:='||D);
                            DBMS_OUTPUT.PUTLINE('The Value of Last String is available Here:='||A);
                       END;
            DBMS_OUTPUT.PUTLINE('The Value of Outer String A is available:='||A);
            DBMS_OUTPUT.PUTLINE('The Value of Outer Real B is available:='||B);
         END;

About Scope and Naming Resolution Standards

noreply@blogger.com (Phani Kumar) — Sun, 27 Jun 2010 14:25:00 +0000

CONCEPTS OF SCOPING:

a) With in the same scope, all identifiers must be unique.
b) Even when the datatypes are differing, variables and parameters cannot share the same name.

       Examlpe:
               DECLARE
                      V_FBoolean    BOOLEAN;
                      V_FBoolean    VARCHAR2(5):='SAMPLE';
               BEGIN
                      DBMS_OUTPUT.PUT_LINE('The Vlaue is:'||V_FBoolean);
                      V_FBoolean:=FALSE;
               END;

NAME RESOLUTION STANDARDS:

a) The Names of database columns take precedence over the names of local variables and formal parameters.
b) To avoid the ambiguity of name resolution, add a prefix to the names of the local variables and formal parameters, or a BLOCK LABEL can be used to qualify reference.

SCOPE AND VISIBILITY OF PL/SQL IDENTIFIERS

noreply@blogger.com (Phani Kumar) — Sun, 27 Jun 2010 14:21:00 +0000

SCOPE AND VISIBILITY OF PL/SQL IDENTIFIERS:

a) References to an identifiers are resolved according to the scope and visibility.
b) SCOPE: Scope of an identifier is that region of a program unit, from which we can unqualified the identifier.
c) VISIBLE: It is the region from which we can reference the identifier using an qualified name.
d) Identifiers declared in a PL/SQL block are considered LOCAL to that block and GLOBAL to all its sub-blocks.
e) If a GLOBAL IDENTIFIER is re-declared in a sub Block, Both identifiers remain in scope.
f) With in the sub block, only the local identifiers is visible.
g) A qualified name should be used to reference the Global Identifiers.
h) The Same Identifier can be declared in two different blocks, the change in one does not affect the other.

OPERATOES IN PL/SQL

noreply@blogger.com (Phani Kumar) — Sun, 27 Jun 2010 14:20:00 +0000

OPERATOES IN PL/SQL:

a) Logical        : AND,OR,NOT.
b) Arithmetic        : +,-,*,/.
c) Concatenation    : ||

Parenthesis To Control Order of Operations

d) Exponentiation    : **
e) Comparison        : =,!=,<,>, <=,>=.
f) SQL*Plus         : IS NULL, LIKE, BETWEEN, IN.

PRECEDENCE:
==========

1) **,not
2) +,-(identity, Negation)
3) *,/
4) +,-
5) =,!=,<,>,<=,>=,IS NULL, LIKE, BETWEEN, IN.
6) AND
7) OR

OPERATORS IN PL/SQL, THINGS TO NOTE:

a) Comparison involving NULL's always yield NULL.
b) Applying logical operator NOT to a NULL yields NULL.
c) In Conditional Control Statements, if the condition yields NULL, its associates sequence of statements are not executed.

SIMPLE WAY TO STRAT

noreply@blogger.com (Phani Kumar) — Sun, 27 Jun 2010 14:18:00 +0000

Sql>DECLARE
          String1 VARCHAR2(30):='Oracle';
          String2 VARCHAR2(30):='Corporation';
    BEGIN
          DBMS_OUTPUT.PUT_LINR('Your First String is :'||String1);
          DBMS_OUTPUT.PUT_LINR('Your Second String is:'||String2);
          DBMS_OUTPUT.PUT_LINR('Your Final String is :'||String1||String2);
    END;

STEPS TO COMPILE and PRODUCE OUTPUT:

a) At SQL PROMPT TYPE
     Sql> SET SERVEROUTPUT ON;
b) Place forward slash and press Enter Key.
     Sql>/
     Sql> DECLARE
               MyNumber1NUMBER:=10;
               MyNumber1NUMBER:=20;
          BEGIN
               DBMS_OUTPUT.PUTLINE('Your First Number is :'||MyNumber1);
               DBMS_OUTPUT.PUTLINE('Your First Number is :'||MyNumber2);
               DBMS_OUTPUT.PUTLINE('Your First Number is :'||MyNumber1'and'||MyNumber2||'is:'||To_Char(MyNumber1+MyNumber2,'999.99'));
          END;

    Sql>DECLARE
              V_FirstName              VARCHAR2(30):='&FName';
              V_MiddleName             VARCHAR2(30):='&MName';
              V_LastName               VARCHAR2(30):='&LName';
              V_DOB                    DATE:='&DateOfBirth';
        BEGIN
              DBMS_OUTPUT.PUTLINE('Your First Name is :'||V_FirstName);
              DBMS_OUTPUT.PUTLINE('Your Middle Name is:'||V_MiddleName);
              DBMS_OUTPUT.PUTLINE('Your Last Name is :'||V_LastName);
              DBMS_OUTPUT.PUTLINE('******Concatenating*********');
              DBMS_OUTPUT.PUTLINE('Your Full Name is :'||V_FirstName||' '||V_MiddleName||' '||V_LastName);
              DBMS_OUTPUT.PUTLINE('Your Date Of Birth is :'||V_DOB);
              DBMS_OUTPUT.PUTLINE('Your Age is :'||Trunc((SYSDATE-V_DOB)/365));
        END;
    Sql>DECLARE
              V_FirstNum NUMBER:=&FNumber;
              V_SecondNum NUMBER:=&SNumber;
              V_Result NUMBER:=0;
        BEGIN
              DBMS_OUTPUT.PUTLINE('You Gave me'||V_FirstNum||'and'||V_SecondNum);
              DBMS_OUTPUT.PUTLINE('I Excepted their Sum and The Result is:'||To_Char(V_FirstNum+V_SecondNum,'999.99'));
              DBMS_OUTPUT.PUTLINE('As You did not Relinitialize the Result variable it is having:'||V_Result||'only');
        END;

APPLYING SUBSTITUTION VARIABLES:

noreply@blogger.com (Phani Kumar) — Sun, 27 Jun 2010 14:16:00 +0000

APPLYING SUBSTITUTION VARIABLES:

a) PL/SQL is not a stand alone programming language, it only exists as a tool within the Oracle programming environment.
b) Because of the alone mentioned standard it does not really have capabilities to accept input from user.
c) For accepting the inputs the substitutions variables can be used, which can be either '&' or'&&'.
d) Substitutions variables cannot be used to output the values.
e) The substitutions of the value is actually done by the SQL*PLUS environment, before the PL/SQL block is sent to the database.

PRESENTING DATA ONTO THE SCREEN:

noreply@blogger.com (Phani Kumar) — Sun, 27 Jun 2010 14:15:00 +0000

a) For producing outputs on the video device we need the assistance of DBMS_OUTPUT package.
b) The package enables to display output from PL/SQL blocks and subprogram.
c) The procedure PUT_LINE outputs information to a buffer in the SGA.
d) The information can be displayed by calling the procedure GET_LINE or by setting SERVEROUTPUT ON in SQL*PLUS.
      Syntax:

            DBMS_OUTPUT.PUT_LINE('Manage');

e) Has limitation for maximum off 255 character per line.
f) The DBMS_OUTPUT package is owned by the Oracle USER SYS.
g) It writes information to the BUFFER for STORAGE.
h) The size of the BUFFER can be set between 2000 to 1,000,000 Bytes.
i) The specifications to SET Buffers are

            a) SET SERVEROUTPUT ON;
            b) SET SERVEROUTPUT ON SIZE 5000;
            c) SET SERVEROUTPUT OFF;

ASSIGNING VALUES TO A VARIABLE:

noreply@blogger.com (Phani Kumar) — Sun, 27 Jun 2010 14:14:00 +0000

In PL/SQL a variable can be assigned in three ways.
         i) BY using Assignment Operator(:=).
         ii) By selecting or fetching database values.
         iii) By passing it as an OUT or INOUT parameters to a subprogram.

DECLARATIONS IN PL/SQL:

noreply@blogger.com (Phani Kumar) — Sun, 27 Jun 2010 14:13:00 +0000

DECLARATIONS IN PL/SQL:

a) The program stores values in variable.
b) As the program executes, the values of variables can change but the values of Constants cannot change.
c) Declarations allocate storage space for a value, hence should be specified by a datatype, and name for further reference.
d) Any variable can be declared only in the declarative part of any PL/SQL block, subprogram or package.

   EX:
        DECLARE
               AdmnDate            DATE;
               PatientName        VARCHAR2(30);
               BPCount            SMALLINT:=0;

DETAILED SPECIFICATION OF PL/SQL DATATYPES

noreply@blogger.com (Phani Kumar) — Sun, 27 Jun 2010 14:12:00 +0000

DETAILED SPECIFICATION OF PL/SQL DATA TYPES:

a) They let the programmer to store Numeric data, represent quantities and apply calculations.

BINARY_INTEGER:

a) It is used to store signed integers.
b) Its magnitude range is -2power31.....2power31.
c) They occupy less storage space than Number values.
d) Arithmetic operations on Binary_Integer values are more faster than Number Arithmetic.

BINARY_INTEGER SUBTYPES:

a) A base type is the data type from which a subtype is derived.
b) A subtype associates a base type with a constraint and hence defines a subset of values.
c) The subtypes available are
     *NATURAL *NATURAIN *POSITIVE *POSITIVEN *SIGNTYPE
    Natural and Positive: They restrict an integer variable to Non-Negative or Positive values.
    Naturain and Positiven: Prevent the assigning of NULLs' to an integer variables.
    Signtype: Lets you restrict an integer variable to the values -1, 0, 1, used in programming to state logic.

BINARY_FLOAT and BINARY_DOUBLE:

a) These datatypes are used primarily for high-speed Scientific computation.
b) Literals of these types will end with f(for Binary_Float) or ( for Binary_Double).
c) Computations involving these datatypes produce special values, rather than raising exceptions.
d) Need special functions to deal with overflow, underflow and other conditions.

NUMBER:

a) This datatype is used to store Fixed_Point of Floating_Point Numbers.
b) The magnitude range is 1*10power-130...... 10*10power125 , above this range numeric overflow or underflow error is generated.
c)Precision can be specified, for total number of digits and scale.

     Syntax: NUMBER(P,S);

NUMBER SUBTYPES:

a) These subtypes are used for more descriptive name.
b) The NUMBER subtypes are used for compatibility with ANSI/ISO and IBM types.
c) The subtypes available are

     *DEC *DECIMAL *DOUBLE PRECISION *FLOAT *INTEGER

     *INT *NUMBER *REAL *SMALL INT

d) The subtypes DEC, DECIMAL and NUMBER are used to declare fixed point numbers with a maximum precision of 38 decimal digits.
e) The subtypes DOUBLE PRECISION and FLOAT are used to declare floating point number with a maximum precision of 126 binary digits.
f) The REAL subtypes is used to declare floating point number with a maximum precision of 63 binary digits, roughly equivalent to 18 decimal digits.
g) The subtypes INTEGER,INT and SMALL INT are used to declare integers with a maximum precision of 38 Decimal Digits.

PLS_INTEGER:

a) PLS_INTEGER datatypes is used to store signed integers.
b) The magnitude range is -2power31.....2power31.
c)PLS_INTEGER value requires less storage than NUMBER values.
d) They use Machine Arithmetic, hence result in faster calculation.

PL/SQL Character and String Types:

a) Character types let you store alphanumeric data, represent words and test, and also manipulate character strings.

CHAR:

a) It is used to store fixed length character data.
b) The maximum size is 32767 Bytes.

       Syntax: CHAR[Max_Size[CHAR/BYTE])]

c) If the maximum size is not specified then it defaults to 1.

CHAR SUBTYPE(Character):

a) It has the same range of values as its base type.
b) CHARACTER is just another name for CHAR, having compatibility with ANSI/ISO and IBM types

LONG and LONG RAW:

a)It is used to store variable length character strings.
b) The maximum size of LONG is 32760 Bytes.
c) LONG RAW is used to store Binary Data or Binary Strings, it is not interpreted by PL/SQL.

RAW:

a) It is used to store binary data or byte strings.
b) It can store a sequence of graphics characters or a digitized picture.
c) It cannot be interpreted by PL/SQL.
d) The maximum size is 32767 Bytes.

VARCHAR2:

a) It is used to store variable length character data.
b) The maximum size is up to 32767 bytes.

     Syntax: VARCHAR2(MaxSize[CHAR/BYTE]);

VARCHAR2 SUBTYPES:

a) The subtypes available are
        * STRING    * VARCHAR
b) Used for compatibility with ANSI/ISO and IBM types.

BOOLEAN TYPES:

a) It is used to represent Boolean values TRUE and FALSE.
b) It is used to store high logical values TRUE, FALSE and NULL.
c) It takes no parameters, INSERTS & SELECTS not accepted.

DATE:

a) It is used to store fixed length date times.
b) The data portion defaults to the first day of the current month, and time portion defaults to mid night.
c) Valid Range January 1 4712 BC to December 31 9999 A.D.

Character Set

noreply@blogger.com (Phani Kumar) — Sun, 27 Jun 2010 14:07:00 +0000

Character Set:

i) PL/SQL program are written as lines of text using a specific set of characters.
ii) The characters that can be used are
   -> A-Z or a-z
   -> 0-9
   -> () + - * / < > = ! ~ ^ : . ' @ % . " # $ & - ! { } ? [ ]
   -> Tabs, Spaces and Carriage Returns
iii) PL/SQL key words are not case sensitive, hence lower case letters are equivalent to corresponding upper case letters.

Lexical Units:

i) A group of characters that are contained within a line of PL/SQL statement are called as Lexical Units.
ii) The types of Lexical Units are
    -> DELIMITERS
    -> IDENTIFIERS
    -> LITERALS
    -> COMMENTS

Delimiters:

i) It is a simple or compound syntax that has special meaning to PL/SQL.
ii) The Delimiters are used to represent arithmetic operations etc.

Identifiers:

i) They are used to name PL/SQL program items and units.
ii) Identifiers can include

        * Constants * Variable * Exception * Cursor Variables * Subprograms * Packages.

iii) An identifiers consists of a letter followed by a set of letters, numerals, dollars signs etc.,.

            *& *- */ *''

iv)      Valid Identifiers:                 Invalid Identifiers:
         =================                 ====================

                X                                This & That
                P2                               You-me
                V_phone#                         Show/off
                V-phone$Number                   Emp Name

v) Identifiers are not case sensitive, until they are not declared within single quotes.
vi) An identifier length cannot exceed 30 characters.

RESERVED WORDS:

i) Reserved Words have special syntactic meaning to PL/SQL.
ii) Reserved Words can be used in combination with other words for declaration.
iii) It is Better to represent reserved words in upper casing.

PREDEFINED IDENTIFIERS:

i) The identifiers globally declared in packages STANDARD can be redeclared.
ii) Declaring predefined identifiers again is error prone as the local declaration overrides the global declaration.

QUOTED IDENTIFIERS:

i) Identifiers declared in double quotes are called Quoted Identifiers.
ii) The Maximum size of a Quoted identifier cannot exceed 30 characters.
iii) Using PL/SQL reserved words as Quoted Identifiers is not advisable.
       Example: "Employees(s)"
                 "Status On/Off"

LITERALS:

i) A Literals is an explicit Numeric, Character, String or Boolean value not represented by an Identifier.

NUMERIC LITERALS:

a) They are of two types: Integers & Real's.
b) An Integer literal is an optionally signed whole number without a decimal point.
       Example: 030, 8, -180, +56798.
c) A Real literal is optionally signed whole or Fractional number with a decimal point.
       Example : 6.6667, 0.0, -12.0, 3.14, .5, 25.
d) Numeric literals cannot contain dollar signs or columns, but can be written using scientific notation.
e) The double asterisk operator(**) is the Exponentiation operator.
f) If the value of a Numeric literals fails outside the range, it generates compilation error.
    Example:
          DECLARE
              MyNumber NUMBER;
          BEGIN
          MyNumber:=10E127;
          END; /* The Range is 1E-130 .. 10E125*/
g) Real literals can use the trailing letters f and d to specify the BINARY_FLOAT and BINARY_DOUBLE.
       Example:
          DECLARE
              MyFloat BINARY_FLOAT:= SQRT(2.of);
              MyDouble BINARY_DOUBLE:=SQRT(2.od);
          BEGIN
              NULL;
          END;

CHARACTER LITERALS:

a) It is an individual character enclosed by single quotes.
b) In include all printable character in the PL/SQL character set.
         Example: 'A', 'C', 'a', '7'.
c) Character literals are case sensitive.
d) Character literals in the form of number is not equivalent to integer.
e) Even though pure character literals declared only with numbers are implicitly converted, it is better to use explicit conversion.

STRING LITERALS:

a) A String Literal is a sequence of zero or more characters enclosed by single quotes.
b) All string literals except the NULL string ' ' have datatype CHAR.
c) Top represent an apostrophe within a string declare two single quotes.
          Example:      'I am in, Oracle!'
                        '02-10-83'
                        'It is "So Sunny Outside."'
                        'Your's "Association President."'
d) To implement single quotation marks with easiness, select any character that is not a part of the string and declare the style.
          Example:    Q '!.........!'
                      Q '! We 're a group, in Your's group !"

BOOLEAN LITERALS:

a) They are predefined values TRUE,FALSE and NULL.
b) BOOLEAN LITERALS are values, but not strings.

DATATIME LITERALS:

a) A DATATIME Literals can have various formats depending on the datatype.
Example:   DATE '1998-12-25';
             TIMESTAMP '1997-10-22 01:05:21';

COMMENTS IN PL/SQL:

a) PL/SQL compiler ignores comments.
b) Comments promote readability and aids understanding.
c) PL/SQL supports two styles.
       -> SINGLE LINE Comments(--)
       -> MULTI- LINE Comments(/*.....*/)
d) Comments should appear within a statement at the end of a line.
e) Comments cannot be nested.
f) It is must preferable to use multi-line comments rather than single line comments.

DECLARING PL/SQL VARIABLES

noreply@blogger.com (Phani Kumar) — Sun, 27 Jun 2010 13:59:00 +0000

Syntax:

Identifier[CONSTANT]Data type[NOT NULL][:= DEFAULT Expression];

=> Identifier- Specifies the name of the variables.
=> Constant - Constraints the variables such that its value cannot change. Constants must be Initialized.
=> Data type - It is a Scalar,composite,reference or LOB Datatype.
=> NOT NULL - Constraints a variable such that it must contain a value. Should be initialized.
=> Expression- It is any PL/SQL Expression that can be a literal, another variable or an expression involving operations and functions.