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