Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

EnterpriseDB: Embedded SQL
Oracle Tips by Burleson
 

The power of SPL becomes apparent when you are able to take advantage of SQL in your code.  If you have used most any other 3GL to write database programs, you have seen how convoluted database access can be.

SPL makes SQL access easy.  You can embed your SQL directly in your code as static SQL or you can build your SQL dynamically. 

Static SQL

Static SQL is SQL that has been pre-compiled by the database.  It has been parsed but has not been optimized and does not have an execution plan.  Static SQL may or may not have variables in the statement.

Static DML

Static DML are inserts, updates and deletes or any other type of transactional commands. 

The DML commands in SPL are exactly the same syntax as the DML statement you would use in a SQL tool.  You can see the exact syntax in Chapter 2.

Here is a small example including common DML:

BEGIN
  INSERT INTO emp (empno) VALUES (99);
  UPDATE emp SET ename = 'Jschmoe' WHERE empno = 99;
  DELETE FROM emp WHERE ename = 'Jschmoe';
END; 

INFO:  Inserting employee 99
CONTEXT:  SQL statement "INSERT  INTO emp ( empno ) VALUES ( 99 )"
INFO:
CONTEXT:  SQL statement "INSERT  INTO emp ( empno ) VALUES ( 99 )"
INFO:  User enterprisedb added employee(s) on 2006-11-20
CONTEXT:  SQL statement "INSERT  INTO emp ( empno ) VALUES ( 99 )"
INFO:  Updating employee 99
CONTEXT:  SQL statement "UPDATE  emp SET ename = 'Jschmoe' WHERE empno = 99"
INFO:
CONTEXT:  SQL statement "UPDATE  emp SET ename = 'Jschmoe' WHERE empno = 99"
INFO:
CONTEXT:  SQL statement "UPDATE  emp SET ename = 'Jschmoe' WHERE empno = 99"
INFO:
CONTEXT:  SQL statement "UPDATE  emp SET ename = 'Jschmoe' WHERE empno = 99"
INFO:  User enterprisedb updated employee(s) on 2006-11-20
CONTEXT:  SQL statement "UPDATE  emp SET ename = 'Jschmoe' WHERE empno = 99"
INFO:  Deleting employee 99
CONTEXT:  SQL statement "DELETE FROM emp WHERE ename = 'Jschmoe'"
INFO:
CONTEXT:  SQL statement "DELETE FROM emp WHERE ename = 'Jschmoe'"
INFO:  User enterprisedb deleted employee(s) on 2006-11-20
CONTEXT:  SQL statement "DELETE FROM emp WHERE ename = 'Jschmoe'"

Select Into

While DML works as it does in SQL, a select statement has to be a little bit different.  A select statement returns values.  In a SQL tool, the return values are displayed on your screen.  In your program, you will need to return them into variables.  That is really the only difference between a select statement in SQL and a select statement in SPL.

The syntax for an SPL select statement is:

SELECT <column list>
  INTO <variable list|record>
  FROM <table name>
  <etc>;

All of the pieces of the select are the same with the addition of the INTO keyword.  INTO tells the SQL engine to put the results of the select into either a list of variables (whose number and data types match the select list) or into a record type.

The variables used in the INTO clause can hold only a single record at a time.  If your result set will contain multiple records, you will need to use a CURSOR (which I talk about below).

Below is an example of both kinds of select statements:

DECLARE
  emp_rec emp%ROWTYPE; 

  v_ename emp.ename%TYPE;
  v_dname dept.dname%TYPE; 

BEGIN 

  SELECT *
    INTO emp_rec

  
  FROM emp
    WHERE empno = 7369; 

  DBMS_OUTPUT.PUT_LINE( 'Emp Name: ' || emp_rec.ename ||
        ', Dept No: ' ||  to_char(emp_rec.deptno) ); 

  SELECT emp.ename, dept.dname
    INTO v_ename, v_dname        
    FROM emp,
         dept
    WHERE emp.deptno = dept.deptno

     
AND empno = 7369; 

  DBMS_OUTPUT.PUT_LINE( 'Emp Name: ' || v_ename ||
        ', Dept Nname: ' || v_dname );
 |
END; 

INFO:  Emp Name: SMITH, Dept No: 20
INFO:  Emp Name: SMITH, Dept Nname: RESEARCH 

If you try to run a query that retrieves more than a single row, or you run a query that retrieves no rows, you will get an error.  You can handle the error in an exception handler, which I also talk about below.

DML and SELECT INTO create what is known as an IMPLICIT CURSOR.  An implicit cursor creates a memory area to handle the SQL parts of your code.  If you need to fetch more than a single row, or you do not want to raise an exception if there is no data requires that you use an EXPLICIT CURSOR.

             

This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter