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 :

4 comments:

  1. Hi Dayakar,


    Muchas Gracias Mi Amigo! You make learning so effortless. Anyone can follow you and I would not mind following you to the moon coz I know you are like my north star.

    I know this is a REAL general question...but just looking for some suggestions and perhaps best practices.
    We need to set-up some General SQL Server Service Accounts...to run SSRS Reports...to access SQL Servers using SSIS...things of that nature...
    Are there any common, best practices for naming and identifying SQL Server Service Accounts? I'd LOVE to hear feedback on what best works and why. I know it's more of a site specific kind of matter...but hoping for a hit of someone that has seen SQL Server Service Accounts and what works best.

    Once again thanks for your tutorial.


    Thanks a heaps,
    Morgan

    ReplyDelete
  2. Hello Dayakar,

    The sense of praise that I have found for you after reading SQL Statements in PL/SQL is overwhelming! Such a tremendous read!

    i need help

    I have 3 tables, the first 2 tables already have values, this is the output

    SQL> select * from tblhorario
    2 ;

    IDHORARIO DESCRIPCIONHORARIO
    ---------- -----------------------
    1 L-V Oficina
    2 L-S Oficina
    3 L-S Extendido
    4 L-S Completo
    5 247

    SQL> select * from tblciudad;

    IDCIUDAD NOMBRECIUDAD
    ---------- -----------------------
    1 Bogota
    2 Cali
    3 Medellin
    4 Bucaramanga
    5 Cartagena
    6 Barranquilla
    7 Cucuta

    7 rows selected.

    For the thir table i need to insert data using a mix of sources:

    First from the normal keyboard input, and second a select query that brings me the fileds DESCRIPCIONHORARIO and NOMBRECIUDAD using the respective ID´S from the other 2 tables. I'm an student and i need help with this.

    This is the original query

    INSERT INTO tblLocal(nombreLocal, direccionLocal, telefono1Local, telefono2Local, celularLocal, emailLocal, renta, idHorario, idCiudad)
    VALUES('UTadeoLozano', 'Cra 5 calle 22', '2756456', 'N/A', '3107770900', 'lutadeo@printing.com', 675000, 1, 1);

    The last numerical values at the and of the clause are meant to come from the two original tables.

    If the solution is a trigger or a stored procedure, please help me.

    By the way do you have any YouTube videos, would love to watch it. I would like to connect you on LinkedIn, great to have experts like you in my connection (In case, if you don’t have any issues).

    Best Regards,
    Preethi

    ReplyDelete
  3. Hello Mate,


    The sense of praise that I have found for you after reading SQL Statements in PL/SQL is overwhelming! Such a tremendous read!

    i need help

    I have 3 tables, the first 2 tables already have values, this is the output

    SQL> select * from tblhorario
    2 ;

    IDHORARIO DESCRIPCIONHORARIO
    ---------- -----------------------
    1 L-V Oficina
    2 L-S Oficina
    3 L-S Extendido
    4 L-S Completo
    5 247

    SQL> select * from tblciudad;

    IDCIUDAD NOMBRECIUDAD
    ---------- -----------------------
    1 Bogota
    2 Cali
    3 Medellin
    4 Bucaramanga
    5 Cartagena
    6 Barranquilla
    7 Cucuta

    7 rows selected.

    For the thir table i need to insert data using a mix of sources:

    First from the normal keyboard input, and second a select query that brings me the fileds DESCRIPCIONHORARIO and NOMBRECIUDAD using the respective ID´S from the other 2 tables. I'm an student and i need help with this.

    This is the original query

    INSERT INTO tblLocal(nombreLocal, direccionLocal, telefono1Local, telefono2Local, celularLocal, emailLocal, renta, idHorario, idCiudad)
    VALUES('UTadeoLozano', 'Cra 5 calle 22', '2756456', 'N/A', '3107770900', 'lutadeo@printing.com', 675000, 1, 1);

    The last numerical values at the and of the clause are meant to come from the two original tables.

    If the solution is a trigger or a stored procedure, please help me.


    By the way do you have any YouTube videos, would love to watch it. I would like to connect you on LinkedIn, great to have experts like you in my connection (In case, if you don’t have any issues).

    Best Regards,
    Radhey

    ReplyDelete
  4. Hi There,

    Seems like I won the lottery here….This is a treasure box of blogs and your folks are like leprechauns! Phenomenal read on SQL Statements in PL/SQL
    I'd like to be able to log all constraint errors when users are running an application having Oracle as the back-end. That means inserting a record into an Errors Log table in Oracle.

    Is that even possible? In an exception clause, I could do that; however, when a constraint fails, I don't know where I'd be able to run such code to do the insert.

    Is this even possible?

    Thanks for any help you can give.

    Wayne

    Appreciate your effort for making such useful blogs and helping the community.

    Merci,
    Preethi.

    ReplyDelete