|Programmer's Guide to the Oracle Precompilers, 1.8||
After covering the key concepts of embedded SQL programming and the steps you take in developing an application, this chapter uses a simple program to illustrate the main points.
Figure 2 - 1 shows all the SQL statements your application program can execute.
Figure 2 - 1. SQL Allowed in a Program
For example, to manipulate and query Oracle data, you use the INSERT, UPDATE, DELETE, and SELECT statements. INSERT adds rows of data to database tables, UPDATE modifies rows, DELETE removes unwanted rows, and SELECT retrieves rows that meet your search criteria.
The Oracle Precompilers support all the Oracle7 SQL statements. For example, the powerful SET ROLE statement lets you dynamically manage database privileges. A role is a named group of related system and/or object privileges granted to users or other roles. Role definitions are stored in the Oracle data dictionary. Your applications can use the SET ROLE statement to enable and disable roles as needed.
Only SQL statements--not SQL*Plus statements--are valid in an application program. (SQL*Plus has additional statements for setting environment parameters, editing, and report formatting.)
Executable statements result in calls to the runtime library SQLLIB. You use them to connect to Oracle, to define, query, and manipulate Oracle data, to control access to Oracle data, and to process transactions. They can be placed wherever any other host-language executable statements can be placed.
Declarative statements, on the other hand, do not result in calls to SQLLIB and do not operate on Oracle data. You use them to declare Oracle objects, communications areas, and SQL variables. They can be placed wherever host-language declarations can be placed.
Table 2 - 1 groups the various embedded SQL statements.
|ARRAYLEN*||To use host arrays with PL/SQL|
|BEGIN DECLARE SECTION* END DECLARE SECTION*||To declare host variables|
|DECLARE*||To name Oracle objects|
|INCLUDE*||To copy in files|
|TYPE*||To equivalence datatypes|
|VAR*||To equivalence variables|
|WHENEVER*||To handle runtime errors|
|ALLOCATE*||To define and control Oracle data|
|DELETE||To query and manipulate Oracle data|
|COMMIT||To process transactions|
|DESCRIBE*||To use dynamic SQL|
|ALTER SESSION||To control sessions|
|*Has no interactive counterpart|
Most embedded SQL statements differ from their interactive counterparts only through the adding of a new clause or the use of program variables. Compare the following interactive and embedded ROLLBACK statements:
ROLLBACK WORK; -- interactive
EXEC SQL ROLLBACK WORK; -- embedded
For a summary of embedded SQL syntax, see the Oracle7 Server SQL Reference.
However, some applications are required to accept and process any valid SQL statement at run time. So, you might not know until then all the SQL commands, database tables, and columns involved.
Dynamic SQL is an advanced programming technique that lets your program accept or build SQL statements at run time and take explicit control over datatype conversion.
From embedded PL/SQL blocks, you can manipulate Oracle data flexibly and safely because PL/SQL supports all SQL data manipulation and transaction processing commands. For more information about PL/SQL, see Chapter 5, "Using Embedded PL/SQL."
Your program uses input host variables to pass data to Oracle. Oracle uses output host variables to pass data and status information to your program. The program assigns values to input host variables; Oracle assigns values to output host variables.
Host variables can be used anywhere an expression can be used. But, in SQL statements, host variables must be prefixed with a colon (:) to set them apart from Oracle objects.
You can associate any host variable with an optional indicator variable. An indicator variable is an integer variable that "indicates" the value or condition of its host variable. You use indicator variables to assign nulls to input host variables and to detect nulls or truncated values in output host variables. A null is a missing, unknown, or inapplicable value.
In SQL statements, an indicator variable must be prefixed with a colon and appended to its associated host variable (unless, to improve readability, you precede the indicator variable with the optional keyword INDICATOR).
Oracle recognizes two kinds of datatypes: internal and external. Internal datatypes specify how Oracle stores data in database columns. Oracle also uses internal datatypes to represent database pseudocolumns, which return specific data items but are not actual columns in a table.
External datatypes specify how data is stored in host variables. When your host program inputs data to Oracle, if necessary, Oracle converts between the external datatype of the input host variable and the internal datatype of the database column. When Oracle outputs data to your host program, if necessary, Oracle converts between the internal datatype of the database column and the external datatype of the output host variable.
On a variable-by-variable basis, you can equivalence supported host language datatypes to Oracle external datatypes. For more information, see "Datatype Equivalencing" .
For static SQL statements, there are two types of cursors: implicit and explicit. Oracle implicitly declares a cursor for all data definition and data manipulation statements, including SELECT statements (queries) that return only one row. However, for queries that return more than one row, to process beyond the first row, you must explicitly declare a cursor (or use host arrays).
The set of rows retrieved is called the active set; its size depends on how many rows meet the query search condition. You use an explicit cursor to identify the row currently being processed, which is called the current row.
Imagine the set of rows being returned to a terminal screen. A screen cursor can point to the first row to be processed, then the next row, and so on. In the same way, an explicit cursor "points" to the current row in the active set, allowing your program to process the rows one at a time.
To help ensure the consistency of your database, the Oracle Precompilers let you define transactions using the COMMIT, ROLLBACK, and SAVEPOINT statements. COMMIT makes permanent any changes made during the current transaction. ROLLBACK ends the current transaction and undoes any changes made since the transaction began. SAVEPOINT marks the current point in a transaction; used with ROLLBACK, it undoes part of a transaction.
With the WHENEVER statement, you can specify actions to be taken automatically when Oracle detects an error or warning condition. These actions include continuing with the next statement, calling a subroutine, branching to a labeled statement, or stopping.
Figure 2 - 2. Application Development Process
As you can see, precompiling results in a source file that can be compiled normally. Although precompiling adds a step to the traditional development process, that step is well worth taking because it lets you write very flexible applications.
Handling errors with the WHENEVER statement, the following program connects to Oracle, prompts the user for an employee number, queries the database for the employee's name, salary, and commission, then displays the information and exits.
-- declare host and indicator variables
EXEC SQL BEGIN DECLARE SECTION;
ind_comm SMALLINT; -- indicator variable
EXEC SQL END DECLARE SECTION;
-- copy in the SQL Communications Area
EXEC SQL INCLUDE SQLCA;
display 'Username? ';
display 'Password? ';
-- handle processing errors
EXEC SQL WHENEVER SQLERROR DO sql_error;
-- log on to Oracle
EXEC SQL CONNECT :username IDENTIFIED BY :password;
display 'Connected to Oracle';
display 'Employee number? ';
-- query database for employee's name, salary, and commission
-- and assign values to host variables
EXEC SQL SELECT ENAME, SAL, COMM
INTO :emp_name, :salary, :commission:ind_comm
WHERE EMPNO = :emp_number;
display 'Employee Salary Commission';
display '-------- ------ ----------';
-- display employee's name, salary, and commission (if not null)
IF ind_comm = -1 THEN -- commission is null
display emp_name, salary, 'Not applicable';
display emp_name, salary, commission;
-- release resources and log off the database
EXEC SQL COMMIT WORK RELEASE;
display 'Have a good day';
-- avoid an infinite loop if the rollback results in an error
EXEC SQL WHENEVER SQLERROR CONTINUE;
-- release resources and log off the database
EXEC SQL ROLLBACK WORK RELEASE;
display 'Processing error';
exit program with an error;
CREATE TABLE DEPT
CREATE TABLE EMP
(EMPNO NUMBER(4) primary key,
DEPTNO DNAME LOC
------- ---------- ---------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ------- --------- ------ --------- ------ ------ -------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
Copyright © 1996 Oracle Corporation.
All Rights Reserved.