SQL Statements in PL/SQL

Valid SQL statements inside PL/SQL Block

SELECT statement

All DML statements

Transaction statements like COMMIT & ROLLBACK

SELECT statement inside PL/SQL Block

To use the SELECT statement, the INTO clause is mandatory

Select statements must return a single row

Returning no row or multiple rows, both, generate an error

Syntax:

      SELECT column_list INTO variable/s FROM table_name WHERE condition;

SELECT Statement inside PL/SQL Block

DECLARE

   v_empno   emp.empno%TYPE   := &empno;

   v_ename   emp.ename%TYPE;

BEGIN

   SELECT ename

     INTO v_ename

     FROM emp

    WHERE empno = v_empno;

   DBMS_OUTPUT.put_line (' The name is :  ' || v_ename);

END;

/

%TYPE

Suppose instead of using %TYPE, we declared a variable "v_ename" as of type “varchar2(10)” then

What if we don't know the data type (or size) of the field "ename"?

What if the DBA increases the width of "ename" column without informing you

Your program may not work correctly.

By using %TYPE both these problems can be solved as when we use tablename.columnname%TYPE, PL/SQL will find out the data type and size of the mentioned column of the table and will allocate the same to the variable at run time

Notes:

          For the example, if the input value of empno is not found in the emp table, then PLSQL  displays an error message

           (The error message also shows the line number from code, which caused the error ).

DECLARE

   v_empno   emp.empno%TYPE   := &empno;

   v_ename   emp.ename%TYPE;

BEGIN

   SELECT ename

     INTO v_ename

     FROM emp

    WHERE empno = v_empno;

   DBMS_OUTPUT.put_line (' The name is :  ' || v_ename);

END;

/

Enter value for empno: 89

old   2:      v_empno emp.empno%TYPE := &empno;

new   2:      v_empno emp.empno%TYPE := 89;

DECLARE

*

ERROR at line 1:

ORA-01403: no data found

ORA-06512: at line 5

%ROWTYPE Variable Example

DECLARE

   v_dno     dept.deptno%TYPE   := &dno;

   deptrec   dept%ROWTYPE;

BEGIN

   SELECT deptno,

          dname,

          loc

     INTO deptrec

     FROM dept

    WHERE deptno = v_dno;

   DBMS_OUTPUT.put_line (deptrec.deptno);

   DBMS_OUTPUT.put_line (deptrec.dname);

   DBMS_OUTPUT.put_line (deptrec.loc);

END;

/

Bind Variable Example

VARIABLE result NUMBER

BEGIN

   SELECT (sal * 12) + NVL (comm, 0)

     INTO :RESULT

     FROM emp

    WHERE empno = 7839;

   DBMS_OUTPUT.put_line ('The total amount payable : ' || :RESULT);

END;

/

-- To see the value set for the variable on SQL prompt use the SQL * plus command PRINT

PRINT result