 |
|
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. |
