Embedded SQL, Database APIs,

and PL/SQL

Juliana Freire

Some slides adapted from J. Ullman, L. Delcambre, R. Ramakrishnan, G. Lindstrom and

Silberschatz, Korth and Sudarshan
 SQL: Benefits
• Declarative languages: program is a prescription for
what data is to be retrieved, rather than a procedure
describing how to retrieve the data
• When we write an SQL select query, we do not make
any assumptions about the order of evaluation
• Can be automatically optimized!
– Decision about order and evaluation plan is left to the
optimized
– Optimizer has the resources to make sophisticated
decisions
 SQL: Limitations
• Not flexible enough for some applications
– Some queries cannot be expressed in SQL
– Non-declarative actions can’t be done from SQL,
e.g., printing a report, interacting with user/GUI
– SQL queries may be just one small component of
complex applications
Trade-off: automatic optimization of queries
expressed in powerful languages is hard
 Solution
• SQL commands called from within a powerful host
language program
• Three main integration approaches:
– Embed SQL in the host language (Embedded SQL)
– Create special API to call SQL commands (JDBC,ODBC)
– Allow ‘external’ code to be executed from within SQL
• Query processing performed by the database
– Leverage database capabilities
 Three-Tier Architecture
• A common environment for using a database
has three tiers of processors:
1. Web servers --- talk to the user.
2. Application servers --- execute the business logic.
3. Database servers --- get what the app servers need
from the database.
Three-Tier Architecture
 Example: Amazon
• Database holds the information about products,
customers, etc.
• Business logic includes things like “what do I do
after someone clicks ‘checkout’?”
– Answer: Show the “how will you pay for this?”
screen.
 Environments, Connections, Queries

• The database is, in many DB-access

languages, an environment
• Database servers maintain some number of
connections, so app servers can ask queries or
perform modifications
• The app server issues statements : queries and
modifications, usually
 Client and Servers in SQL Environment
CONNECT TO <server name> AS <connection name>
AUTHORIZATION <name and password>

- A client may maintain several connections to the server,

but only one can be active at any point in time
- Operations performed while a connection is active form
a session

SQL Connection SQL

Client Session Server
 Three Kinds of ‘Modules’
• Module = application program
• Embedded SQL
• Generic SQL Interface
• True modules – stored functions and
procedures
 Embedded SQL
• Embed SQL in the host language.
– A preprocessor converts the SQL statements into special API
calls – optimized access plans that are stored in the database
– Then a regular compiler is used to compile the code.
• SQL statements can refer to host variables (including
special variables used to return status).
– Input: variable given a value by the host program to be used in
a query
– Output: variable used to retrieve value from the database

• Language constructs:
– Connecting to a database:

– Declaring variables:

– Statements:
Declaring Variables
Executing Statements
 Error/Status Reporting
• Each SQL statement executed returns a status
code + additional information
– SQL communication area (SQLCA)
• Two special “error” variables:

• zero indicates normal execution

• negative indicates an error has occurred
• positive indicates warnings or special conditions, e.g., data
not found

• Error class, e.g., syntax error

• Error subclass – implementation dependent
 Impedance Mismatch
• Updates are simple to embed: they execute,
possibly modify the contents of the database,
and return SQLCA structure indicating what
happened
• Queries return data!
• SQL relations are (multi-) sets of records, with
no a priori bound on the number of records. No
such data structure exist traditionally in
procedural programming languages such as C
• SQL supports a mechanism called a cursor to
handle this.
 Cursors
• Can declare a cursor on a relation or query statement (which
generates a relation)
– Name associated with a query
• Can open a cursor, and repeatedly fetch a tuple then move
the cursor, until all tuples have been retrieved
• Open causes the query to be evaluated
EXEC SQL open c END-EXEC
• Fetch causes values of one tuple in the result to be placed on
host variables
EXEC SQL fetch c into :cn, :cc END-EXEC
Repeated calls to fetch get successive tuples in the query
result
 Cursors (cont.)
• SQLSTATE gets set to ‘02000’ to indicate no more data is
available
• The close statement causes the database system to delete
the temporary relation that holds the result of the query
EXEC SQL close c END-EXEC
Note: above details vary with language.
• Can use ORDER BY to control the order in which tuples are
returned
 Cursor: Example

Define SQL query that gets names of sailors

who’ve reserved a boat of color desired_color,
in alphabetical order, and declare a cursor for it

input variable

When are input host variables bound?

When open is executed!
Embedding SQL in C: An Example

input variable

output
variables
 FETCH Statement
• What happens if updates are applied to table(s)
referenced in a cursor after a result is fetched
from a cursor?
• Important note [Chamberlin, A Complete Guide
to DB2]: The result of a query associated with a
cursor may be
– Completely materialized when the 1st row is fetched,
or
– Materialized one row at a time
• Choice depends on the access plan chose by
the optimizer
 Updates Through Cursors
 Can update tuples fetched by cursor by declaring that the cursor
is for update"
declare c cursor for
select *
from account
where branch-name = ʻPerryridgeʼ
for update"
 To update tuple at the current location of cursor"
update account
set balance = balance + 100
where current of c#
 Apart from their method of finding the row to be updated/deleted,
positioned updates behave exactly the same as UPDATE and
DELETE statements that contain a search condition#
 Dynamic SQL
• Sometimes, we can’t predict in advance what SQL
statements will be needed
• Dynamic SQL allows construction of SQL statements
on-the-fly
– Need to PREPARE/EXECUTE
– Example:

• Run-time overhead – use only when essential

– Incurs the cost of access path selection (optimization) at run
time
– Static SQL prepares statements in advance – well-suited for
applications that perform repetitive queries/transactions
 Database APIs: Generic Interface

Rather than modify compiler, add library with database

calls (API)
• Special standardized interface: procedures/objects
• Pass SQL strings from language, presents result sets
in a language-friendly way
• Embedded vs. Generic API: the difference is more a
matter of look and feel than of substance
• Sun’s JDBC: Java API
• Supposedly DBMS-neutral
– a “driver” traps the calls and translates them into DBMS-
specific code
– database can be across a network
Database APIs: Generic Interface
 See
textbook
JDBC: Architecture for details!
• Four architectural components:
– Application (initiates and terminates connections,
submits SQL statements)
– Driver manager (load JDBC driver)
– Driver (connects to data source, transmits requests and
returns/translates results and error codes)
– Data source (processes SQL statements)
Driver Manager

Java Driver Oracle

applet
JDBC

JDBC
API

Java
application Driver DB2
 XSB-Oracle Interface
• Allow calls to Oracle from Prolog
• XSB-specific calls:
db_open(oracle(Name, Pass))
write('% Connected to Oracle as '),
write(Name), writeln(' ...'),nl,
db_sql('drop table DEPT'),
db_create_table('DEPT', 'DEPTNO NUMBER(2),DNAME
VARCHAR2(14)'), db_import(‘DEPT'(‘DEPTNO',‘DNAME'),
deptall),
db_insert(dept_put(A1,A2),(deptall(A1,A2))),
dept_put(1,’computer science’),
dept_put(1,’biology’),…
 Stored Procedures/Functions
• Issues with accessing DB from remote
application:
– JDBC incurs data transfer overheads
– DB resources are tied up, e.g., open cursors
• Advantageous to execute some of the
application logic inside the database
– Minimize data transfer
– Utilize full power of database server
• Stored procedure is a program executed within
the process space of the database
– Can be run by multiple users
 Oracle’s PL/SQL
• Oracle's procedural extension SQL.
– Combines the data manipulating power of SQL with
the data processing power of procedural languages.
• Stored objects
– Procedures, functions packages
• Some uses:
– External procedures: PL/SQL code invoked from
other languages
• E.g., using special tags, you can embed PL/SQL scripts into
HTML source code
– Triggers: procedures invoked by insert, delete,
update
 Procedures In Oracle’s PL/SQL
SQL> create or replace procedure sum_salary
2 is
3 cursor c1 is
4 select * from employee;
5 sal_sum integer;
6 begin
7 sal_sum := 0;
8 for emp_rec in c1 loop
9 sal_sum := sal_sum + emp_rec.salary;
10 end loop;
11 dbms_output.put_line('Salary sum: ' || sal_sum);
12 end;
13 /

Procedure created.

SQL> exec sum_salary

Salary sum: 5805000

PL/SQL procedure successfully completed.

 Functions In PL/SQL
SQL> create or replace function f(i in integer)
2 return integer
3 is
4 begin
5 return i*i;
6 end;
7 /

Function created.

SQL> exec dbms_output.put_line(f(13));

169

PL/SQL procedure successfully completed.

 PL/SQL: Overview
• Block-structured language: procedures,
functions, and anonymous blocks
• Block has three parts: a declarative part, an
executable part, and an exception-handling
part.
[DECLARE]
--- declarations
BEGIN
--- statements May contain
[EXCEPTION HANDLERS] sub-blocks
END;
 PL/SQL: Variables and Constants
%ROWTYPE is
• used to declare
Declared vars and constants can be used in a
record with the
SQL and procedural statements same types as found
• Types: in the specified
database table, view
– SQL datatypes: CHAR, DATE, or NUMBER
or cursor
– PL/SQL datatypes: BOOLEAN or
BINARY_INTEGER
%TYPE is used to
– E.g., part_no NUMBER(4); in_stock BOOLEAN;
dept_rec dept%ROWTYPE; declare a field with
the same type as that
my_title books.title%TYPE
of a specified table's
• Assigning values to vars: column
– tax := price * tax_rate
– SELECT sal * 0.10 INTO bonus FROM emp WHERE
empno = emp_id;
 PL/SQL: Variables and Constants
DECLARE
v_EmpRecord emp%ROWTYPE;
BEGIN
SELECT * INTO v_EmpRecord FROM emp
WHERE ROWNUM = 1;
DBMS_OUTPUT.PUT_LINE('Name = ' ||
v_EmpRecord.ename);
DBMS_OUTPUT.PUT_LINE('Salary = ' ||
v_EmpRecord.sal);
END;
 PL/SQL: Cursors
• PL/SQL implicitly declares a cursor for all SQL data
manipulation statements
• Explicitly declare a cursor for queries that return
multiple rows
DECLARE CURSOR c1 IS
SELECT empno, ename, job
FROM emp WHERE deptno = 20;
• Use OPEN, FETCH, and CLOSE to control cursor.
– OPEN executes the query associated with the cursor, identifies
the result set, and positions the cursor before the first row.
– FETCH retrieves the current row and advances the cursor to
the next row.
– CLOSE disables the cursor after the last row has been
processed
 PL/SQL: Cursors FOR Loops
• Simplify coding – no need to use OPEN, FETCH,
and CLOSE
DECLARE CURSOR c1 IS
SELECT ename, sal, hiredate, deptno
FROM emp; ...
BEGIN
FOR emp_rec IN c1 LOOP ...
salary_total := salary_total + emp_rec.sal;
END LOOP;
END
 PL/SQL: Error Handling
• Exception is raised when an error occurs
• Exceptions
– Internally defined: division by zero and out of memory
– User defined:
DECLARE insufficient_funds EXCEPTION;
…
BEGIN
…
IF … THEN
RAISE insufficient_funds;
END IF; …
• Improved readability
BEGIN
SELECT ...
SELECT ...
SELECT ... ...
EXCEPTION WHEN NO_DATA_FOUND THEN -- catches all 'no data found'
errors
 PL/SQL: Procedures
specification
PROCEDURE debit_account (acct_id INTEGER, amount REAL) IS
old_balance REAL;
new_balance REAL; declarative
overdrawn EXCEPTION;
BEGIN
SELECT bal INTO old_balance execution
FROM accts WHERE acct_no = acct_id;
new_balance := old_balance - amount;
IF new_balance < 0 THEN RAISE overdrawn;
ELSE
UPDATE accts SET bal = new_balance
WHERE acct_no = acct_id;
END IF;
EXCEPTION WHEN overdrawn exception
THEN ...

END debit_account;
body
 PL/SQL: Procedures
Procedure
SQL> create or replace procedure sum_salary is stored in DB
3 cursor c1 is
4 select * from employee;
declaration
5 sal_sum integer;
6 begin
7 sal_sum := 0; execution
8 for emp_rec in c1 loop
9 sal_sum := sal_sum + emp_rec.salary;
10 end loop;
11 dbms_output.put_line('Salary sum: ' || sal_sum);
12 end;
13 /

Procedure created.

SQL> exec sum_salary

Salary sum: 5805000

PL/SQL procedure successfully completed.

 PL/SQL: Procedures
Procedure
SQL> create or replace procedure sum_salary is stored in DB
3 cursor c1 is
4 select * from employee;
declaration
5 Once compiled
sal_sum integer; and stored in the data dictionary,
6 begin
7 it is a schema
sal_sumobject,
:= 0; which can be referenced execution
8 by any number of applications
for emp_rec in c1 loop connected to
9 sal_sum := sal_sum + emp_rec.salary;
10 that database.
end loop;
11 -Call procedure from trigger
dbms_output.put_line('Salary sum: ' || sal_sum);
12 end;
13 -From
/ SQLPlus
-SQL> CALL create_dept('FINANCE', 'NEW YORK');
Procedure created.

SQL> exec sum_salary

Salary sum: 5805000

PL/SQL procedure successfully completed.

 PL/SQL: Functions
• Function = subprogram that computes a value

SQL> create or replace function f(i in integer)

2 return integer
3 is
4 begin
5 return i*i;
6 end;
7 /

Function created.

SQL> exec dbms_output.put_line(f(13));

169

PL/SQL procedure successfully completed.

 What are the advantages of PL/SQL?
• Performance: reduced
communication
overheads
– JDBC and other APIs
need to send one SQL
command at a time – high
overhead
– PL/SQL: sends one block
at a time
• Stored procedures:
– Executed in server – close
to the data
– Re-use application logic by Figure from PL/SQL User's Guide and Reference
multiple users Release 2 (9.2)
 PL/SQL On-line Documentation
• On-line documentation
– http://download.oracle.com/docs/cd/B14117_01/
appdev.101/b10807/toc.htm
• Additional information available at
– http://infolab.stanford.edu/~ullman/fcdb/oracle/or-
plsql.html
• SQL 2003 introduced Persistent, Stored
Modules (SQL/PSM)
– General purpose language for creating stored
procedures
– See textbook, chapter 9.4
 Summary

• Embedded SQL allows execution of

parameterized static queries within a host
language
• Dynamic SQL allows execution of completely ad-
hoc queries within a host language
• Cursor mechanism allows retrieval of one record
at a time and bridges impedance mismatch
between host language and SQL
• APIs such as JDBC introduce a layer of
abstraction between application and DBMS
 Additional References
 Google JDBC, google PL/SQL, …
 JDBC Overview: http://java.sun.com/products/
jdbc/overview.html
 XSB: http://xsb.sourceforge.net
 A Complete Guide to DB2 Universal Database.
Don Chamberlin.
Bonus Material
 JDBC
• Java Database Connectivity (JDBC) is a library
used to access a database server using Java
as the host language

46
 Making a Connection

The JDBC classes

import java.sql.*;
Class.forName(com.mysql.jdbc.Driver);
Connection myCon =
DriverManager.getConnection(…);

The driver
Loaded by URL of the database
for mySql;
forName your name, and password
others exist
go here.

47
 Statements
• JDBC provides two classes:
1. Statement = an object that can accept a string that
is a SQL statement and can execute such a string.
2. PreparedStatement = an object that has an
associated SQL statement ready to execute.

48
 Creating Statements

• The Connection class has methods to create

Statements and PreparedStatements.
Statement stat1 = myCon.createStatement();
PreparedStatement stat2 =
myCon.createStatement(
”SELECT beer, price FROM Sells ” +
”WHERE bar = ’Joe’ ’s Bar’ ”
); createStatement with no argument returns
a Statement; with one argument it returns
a PreparedStatement.
 Executing SQL Statements

• JDBC distinguishes queries from modifications,

which it calls “updates.”
• Statement and PreparedStatement each have
methods executeQuery and executeUpdate.
– For Statements: one argument: the query or
modification to be executed.
– For PreparedStatements: no argument.
 Example: Update

• stat1 is a Statement.
• We can use it to insert a tuple as:
stat1.executeUpdate(
”INSERT INTO Sells ” +
”VALUES(’Brass Rail’,’Bud’,3.00)”
);
 Example: Query
• stat2 is a PreparedStatement holding the query
”SELECT beer, price FROM Sells WHERE bar
= ’Joe’’s Bar’ ”.
• executeQuery returns an object of class
ResultSet – we’ll examine it later.
• The query:
ResultSet menu = stat2.executeQuery();
 Accessing the ResultSet
• An object of type ResultSet is something like a
cursor.
• Method next() advances the “cursor” to the next
tuple.
– The first time next() is applied, it gets the first tuple.
– If there are no more tuples, next() returns the value
false.
 Accessing Components of Tuples

• When a ResultSet is referring to a tuple, we can

get the components of that tuple by applying
certain methods to the ResultSet.
• Method getX (i ), where X is some type, and i
is the component number, returns the value of
that component.
– The value must have type X.
 Example: Accessing Components

• Menu = ResultSet for query “SELECT beer, price

FROM Sells WHERE bar = ’Joe’ ’s Bar’ ”.
• Access beer and price from each tuple by:
while ( menu.next() ) {
theBeer = Menu.getString(1);
thePrice = Menu.getFloat(2);
/*something with theBeer and
thePrice*/
}
 User-Defined Functions (UDF)
• Create additional functions to supplement the
built-in function supplied by DBMS
• Users can utilize UDFs in SQL queries
• Scalar functions are applied to a column or
expression and operate on a single value
• Table functions, when invoked, return an entire
table
 UDF Examples
get_bal returns the balance of the specified account: get_bal can be used in
a SQL statement:
CREATE FUNCTION get_bal(acc_no IN NUMBER)
RETURN NUMBER SELECT get_bal(165)
IS acc_bal NUMBER(11,2); FROM dual
BEGIN
SELECT order_total
INTO acc_bal
FROM orders dual is a table which is
WHERE customer_id = acc_no; created by oracle along with
RETURN(acc_bal); the data dictionary. It consists
END; of exactly one column whose
name is dummy and one
record. The value of that
record is X.
 UDF Examples
CREATE OR REPLACE FUNCTION
new_name(pid IN NUMBER)
RETURN VARCHAR2
IS nname VARCHAR(40);
BEGIN
SELECT 'Pres' ||name
INTO nname
FROM test
WHERE test.id = pid;
RETURN(nname);
END;

select new_name(2) from dual

 UDF Examples
CREATE OR REPLACE FUNCTION mydouble(n IN NUMBER)
RETURN NUMBER
IS dn NUMBER;
BEGIN
dn := n*2;
RETURN(dn);
END;

CREATE OR REPLACE FUNCTION addtitle(name IN VARCHAR)

RETURN VARCHAR
IS tn VARCHAR(40);
BEGIN
tn := 'Pres. ' || name;
RETURN(tn);
END;