Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Wednesday, 6 April 2016

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
Share this article :

0 comments:

Post a Comment