| Programmer's Guide to the Oracle Precompilers, 1.8 |  Library |
 Product |
 Contents |
 Index |
| Programmer's Guide to the Oracle Precompilers, 1.8 | Library |
Product |
Contents |
Index |
The type column in Table F - 1 is displayed in the format, source/type, where:
source
is either SQL92 standard SQL (S) or an Oracle extension (O)
type
is either an executable (E) statement or a directive (D)
| Purpose | describes the basic uses of the command. |
| Prerequisites | lists privileges you must have and steps that you must take before using the command. Unless otherwise noted, most commands also require that the database be open by your instance. |
| Syntax | shows the keywords and parameters of the command. |
| Keywords and Parameters | describes the purpose of each keyword and parameter. |
| Usage Notes | discusses how and when to use the command. |
| Examples | shows example statements of the command. |
| Related Topics | lists related commands, clauses, and sections of this manual. |
Once you understand the logical flow of a syntax diagram, it becomes a helpful guide. You can verify or construct any embedded SQL statement by tracing through its syntax diagram.
Syntax diagrams use lines and arrows to show how commands, parameters, and other language elements are sequenced to form statements. Trace each diagram from left to right, in the direction shown by the arrows. The following symbols will guide you:
Commands and other keywords appear in UPPER CASE. Parameters appear in lower case. Operators, delimiters, and terminators appear as usual. Following the conventions defined in the Preface, a semicolon terminates statements.
If the syntax diagram has more than one path, you can choose any path to travel.
If you have the choice of more than one keyword, operator, or parameter, your options appear in a vertical list. In the following example, you can travel down the vertical line as far as you like, then continue along any horizontal line:
According to the diagram, all of the following statements are valid:
EXEC SQL WHENEVER NOT FOUND ...
EXEC SQL WHENEVER SQLERROR ...
EXEC SQL WHENEVER SQLWARNING ...
If there is a cursor named emp_cursor, then, according to the diagram, the following statement is valid:
EXEC SQL CLOSE emp_cursor;
If any of the keywords or parameters in a vertical list appears on the main path, one of them is required. That is, you must choose one of the keywords or parameters, but not necessarily the one that appears on the main path. In the following example, you must choose one of the four actions:
If there is a database named oracle2, then, according to the diagram, all of the following statements are valid:
EXEC SQL ROLLBACK; EXEC SQL ROLLBACK WORK; EXEC SQL AT oracle2 ROLLBACK;
If DEBIT, CREDIT, and BALANCE are column names, then, according to the diagram, all of the following statements are valid:
EXEC SQL SELECT DEBIT INTO ...
EXEC SQL SELECT CREDIT, BALANCE INTO ...
EXEC SQL SELECT DEBIT, CREDIT, BALANCE INTO ...
According to the diagram, the following statement is valid:
EXEC SQL PREPARE sql_statement FROM :sql_string;
However, if an Oracle identifier is enclosed by quotation marks ("), it can contain any combination of legal characters, including spaces but excluding quotation marks.
Oracle identifiers are not case-sensitive except when enclosed by quotation marks.
:cursor_variable
is the cursor variable to be allocated.
For more information on this command, see PL/SQL User's Guide and Reference and Oracle7 Server SQL Reference.
Example
This partial example illustrates the use of the ALLOCATE command in a Pro*C/C++ embedded SQL program:
EXEC SQL BEGIN DECLARE SECTION;
SQL_CURSOR emp_cv;
struct{ ... } emp_rec;
EXEC SQL END DECLARE SECTION;
EXEC SQL ALLOCATE emp_cv;
EXEC SQL EXECUTE
BEGIN
OPEN :emp_cv FOR SELECT * FROM emp;
END;
END-EXEC;
for (;;)
{ EXEC SQL FETCH :emp_cv INTO :emp_rec;
}
cursor
is a cursor to be closed.
cursor_variable
is a cursor variable to be closed.
EXEC SQL CLOSE emp_cursor;
To manually commit a distributed in-doubt transaction that you originally committed, you must have FORCE TRANSACTION system privilege. To manually commit a distributed in-doubt transaction that was originally committed by another user, you must have FORCE ANY TRANSACTION system privilege.
If you are using Trusted Oracle7 in DBMS MAC mode, you can only commit an in-doubt transaction if your DBMS label matches the label the transaction's label and the creation label of the user who originally committed the transaction or if you satisfy one of the following criteria:
AT
db_name is a database identifier declared in a previous DECLARE DATABASE statement.
:host_variable is a host variable whose value is a previously declared db_name.
If you omit this clause, Oracle7 issues the statement to your default database.
WORK
COMMENT
specifies a comment to be associated with the current transaction. The 'text' is a quoted literal of up to 50 characters that Oracle7 stores in the data dictionary view DBA_2PC_PENDING along with the transaction ID if the transaction becomes in-doubt.
RELEASE
frees all resources and disconnects the application from the Oracle7 Server.
FORCE
manually commits an in-doubt distributed transaction. The transaction is identified by the 'text' containing its local or global transaction ID. To find the IDs of such transactions, query the data dictionary view DBA_2PC_PENDING. You can also use the optional integer to explicitly assign the transaction a system change number (SCN). If you omit the integer, the transaction is committed using the current SCN.
The COMMIT command has no effect on host variables or on the flow of control in the program. For more information on this command, see Chapter 7.
Example
This example illustrates the use of the embedded SQL COMMIT command:
EXEC SQL AT sales_db COMMIT RELEASE;
If you are using Trusted Oracle7 in DBMS MAC mode, your operating system label must dominate both your creation label and the label at which you were granted CREATE SESSION system privilege. Your operating system label must also fall between the operating system equivalents of DBHIGH and DBLOW, inclusive.
If you are using Trusted Oracle7 in OS MAC mode, your operating system label must match the label of the database to which you are connecting.
:user :password
specifies your username and password separately.
:user_password
is a single host variable containing the Oracle7 username and password separated by a slash (/).
To allow Oracle7 to verify your connection through your operating system, specify "/" as the :user_password value.
AT
identifies the database to which the connection is made. The database can be identified by either:
db_name is a database identifier declared in a previous DECLARE DATABASE statement.
:host_variable is a host variable whose value is a previously declared db_name.
USING
Example
The following example illustrate the use of CONNECT:
EXEC SQL CONNECT :username
IDENTIFIED BY :password
You can also use this statement in which the value of :userid is the value of :username and :password separated by a "/" such as 'SCOTT/TIGER':
EXEC SQL CONNECT :userid
AT
identifies the database on which the cursor is declared. The database can be identified by either:
db_name is a database identifier declared in a previous DECLARE DATABASE statement.
:host_variable is a host variable whose value is a previously declared db_name.
If you omit this clause, Oracle7 declares the cursor on your default database.
cursor
is the name of the cursor to be declared.
SELECT command
is a SELECT statement to be associated with the cursor. The following statement cannot contain an INTO clause.
statement_name block_name
identifies a SQL statement or PL/SQL block to be associated with the cursor. The statement_name or block_name must be previously declared in a DECLARE STATEMENT statement.
You can reference the cursor in the WHERE clause of an UPDATE or DELETE statement using the CURRENT OF syntax, provided that the cursor has been opened with an OPEN statement and positioned on a row with a FETCH statement. For more information on this command, see Chapter 3.
EXEC SQL DECLARE emp_cursor CURSOR
FOR SELECT ename, empno, job, sal
FROM emp
WHERE deptno = :deptno
FOR UPDATE OF sal
db_name
is the identifier established for the non-default database.
For more information on this command, see Chapter 3.
EXEC SQL DECLARE oracle3 DATABASE
AT
db_name is a database identifier declared in a previous DECLARE DATABASE statement.
:host_variable is a host variable whose value is a previously declared db_name.
If you omit this clause, Oracle7 declares the SQL statement or PL/SQL block on your default database.
statement_name block_name
is the declared identifier for the statement.
The scope of a statement declaration is global within its precompilation unit, like a cursor declaration. For more information on this command, see Chapters 3 and 10.
EXEC SQL AT remote_db
DECLARE my_statement STATEMENT
EXEC SQL PREPARE my_statement FROM :my_string
EXEC SQL EXECUTE my_statement
EXEC SQL DECLARE my_statement STATEMENT; EXEC SQL DECLARE emp_cursor CURSOR FOR my_statement; EXEC SQL PREPARE my_statement FROM :my_string; ...
table
is the name of the declared table.
column
is a column of the table.
datatype
is the datatype of a column. For information on Oracle7 datatypes, see Chapter 3.
DEFAULT
specifies the default value of a column.
NULL
specifies that a column can contain nulls.
NOT NULL
specifies that a column cannot contain nulls.
WITH DEFAULT
is supported for compatibility with the IBM DB2 database.
EXEC SQL DECLARE parts TABLE
(partno NUMBER NOT NULL,
bin NUMBER,
qty NUMBER)
For you to delete rows from the base table of a view, the owner of the schema containing the view must have DELETE privilege on the base table. Also, if the view is in a schema other than your own, you must be granted DELETE privilege on the view.
The DELETE ANY TABLE system privilege also allows you to delete rows from any table or any view's base table.
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must dominate the creation label of the table or view or you must meet one of the following criteria:
AT Keywords and Parameters
identifies the database to which the DELETE statement is issued. The database can be identified by either:
db_name is a database identifier declared in a previous DECLARE DATABASE statement.
:host_variable is a host variable whose value is a previously declared db_name.
If you omit this clause, the DELETE statement is issued to your default database.
FOR :host_integer
limits the number of times the statement is executed if the WHERE clause contains array host variables. If you omit this clause, Oracle7 executes the statement once for each component of the smallest array.
schema
is the schema containing the table or view. If you omit schema, Oracle7 assumes the table or view is in your own schema.
table view
is the name of a table from which the rows are to be deleted. If you specify view, Oracle7 deletes rows from the view's base table.
dblink
is the complete or partial name of a database link to a remote database where the table or view is located. For information on referring to database links, see Chapter 2 of the Oracle7 Server SQL Reference. You can only delete rows from a remote table or view if you are using Oracle7 with the distributed option.
If you omit dblink, Oracle7 assumes that the table or view is located on the local database.
alias
is an alias assigned to the table. Aliases are generally used in DELETE statements with correlated queries.
WHERE
specifies which rows are deleted:
condition deletes only rows that satisfy the condition. This condition can contain host variables and optional indicator variables. See the syntax description of condition in Chapter 3 of the Oracle7 Server SQL Reference.
CURRENT OF deletes only the row most recently fetched by the cursor. The cursor cannot be associated with a SELECT statement that performs a join, unless its FOR UPDATE clause specifically locks only one table.
If you omit this clause entirely, Oracle7 deletes all rows from the table or view.
Array host variables in the WHERE clause can have different sizes. In this case, the number of times Oracle7 executes the statement is determined by the smaller of the following values:
The cumulative number of rows deleted is returned through the SQLCA. If the WHERE clause contains array host variables, this value reflects the total number of rows deleted for all components of the array processed by the DELETE statement.
If no rows satisfy the condition, Oracle7 returns an error through the SQLCODE of the SQLCA. If you omit the WHERE clause, Oracle7 raises a warning flag in the fifth component of SQLWARN in the SQLCA. For more information on this command and the SQLCA, see Chapter 8.
You can use comments in a DELETE statement to pass instructions, or hints, to the Oracle7 optimizer. The optimizer uses hints to choose an execution plan for the statement. For more information on hints, see Oracle7 Server Tuning.
EXEC SQL DELETE FROM emp
WHERE deptno = :deptno
AND job = :job; ...
EXEC SQL DECLARE emp_cursor CURSOR
FOR SELECT empno, comm
FROM emp;
EXEC SQL OPEN emp_cursor;
EXEC SQL FETCH c1
INTO :emp_number, :commission;
EXEC SQL DELETE FROM emp
WHERE CURRENT OF emp_cursor;
BIND VARIABLES
initializes the descriptor to hold information about the input variables for the SQL statement or PL/SQL block.
SELECT LIST
initializes the descriptor to hold information about the select list of a SELECT statement.
The default is SELECT LIST FOR.
statement_name block_name
identifies a SQL statement or PL/SQL block previously prepared with a PREPARE statement.
descriptor
is the name of the descriptor to be initialized.
You cannot describe both input variables and output variables into the same descriptor.
The number of variables found by a DESCRIBE statement is the total number of placeholders in the prepare SQL statement or PL/SQL block, rather than the total number of uniquely named placeholders. For more information on this command, see Chapter 10.
EXEC SQL PREPARE my_statement FROM :my_string;
EXEC SQL DECLARE emp_cursor
FOR SELECT empno, ename, sal, comm
FROM emp
WHERE deptno = :dept_number
EXEC SQL DESCRIBE BIND VARIABLES FOR my_statement
INTO bind_descriptor;
EXEC SQL OPEN emp_cursor
USING bind_descriptor;
EXEC SQL DESCRIBE SELECT LIST FOR my_statement
INTO select_descriptor;
EXEC SQL FETCH emp_cursor
INTO select_descriptor;
AT
db_name is a database identifier declared in a previous DECLARE DATABASE statement.
:host_variable is a host variable whose value is a previously declared db_name.
If you omit this clause, the PL/SQL block is executed on your default database.
pl/sql_block
For information on PL/SQL, including how to write PL/SQL blocks, see the PL/SQL User's Guide and Reference.
END-EXEC
must appear after the embedded PL/SQL block, regardless of which programming language your Oracle Precompiler program uses. Of course, the keyword END-EXEC must be followed by the embedded SQL statement terminator for the specific language.
EXEC SQL EXECUTE
BEGIN
SELECT ename, job, sal
INTO :emp_name:ind_name, :job_title, :salary
FROM emp
WHERE empno = :emp_number;
IF :emp_name:ind_name IS NULL
THEN RAISE name_missing;
END IF;
END;
END-EXEC
FOR :host_integer
statement_id
is a precompiler identifier associated with the SQL statement or PL/SQL block to be executed. Use the embedded SQL PREPARE command to associate the precompiler identifier with the statement or PL/SQL block.
USING
specifies a list of host variables with optional indicator variables that Oracle7 substitutes as input variables into the statement to be executed. The host and indicator variables must be either all scalars or all arrays.
EXEC SQL PREPARE my_statement
FROM :my_string;
EXEC SQL EXECUTE my_statement
USING :my_var;
AT
db_name is a database identifier declared in a previous DECLARE DATABASE statement.
:host_variable is a host variable whose value is a previously declared db_name.
If you omit this clause, the statement or block is executed on your default database.
:host_string
is a host variable whose value is the SQL statement or PL/SQL block to be executed.
text
is a quoted text literal containing the SQL statement or PL/SQL block to be executed.
The SQL statement can only be a DELETE, INSERT, or UPDATE statement.
For more information on this command, see Chapter 10.
EXEC SQL EXECUTE IMMEDIATE 'DELETE FROM emp WHERE empno = 9460'
FOR :host_integer
limits the number of rows fetched if you are using array host variables. If you omit this clause, Oracle7 fetches enough rows to fill the smallest array.
cursor
is a cursor that is declared by a DECLARE CURSOR statement. The FETCH statement returns one of the rows selected by the query associated with the cursor.
:cursor_variable
is a cursor variable is allocated an ALLOCATE statement. The FETCH statement returns one of the rows selected by the query associated with the cursor variable.
INTO
USING
The number of rows retrieved is specified by the size of the output host variables and the value specified in the FOR clause. The host variables to receive the data must be either all scalars or all arrays. If they are scalars, Oracle7 fetches only one row. If they are arrays, Oracle7 fetches enough rows to fill the arrays.
Array host variables can have different sizes. In this case, the number of rows Oracle7 fetches is determined by the smaller of the following values:
If a FETCH statement does not retrieve all rows returned by the query, the cursor is positioned on the next returned row. When the last row returned by the query has been retrieved, the next FETCH statement results in an error code returned in the SQLCODE element of the SQLCA.
Note that the FETCH command does not contain an AT clause. You must specify the database accessed by the cursor in the DECLARE CURSOR statement.
You can only move forward through the active set with FETCH statements. If you want to revisit any of the previously fetched rows, you must reopen the cursor and fetch each row in turn. If you want to change the active set, you must assign new values to the input host variables in the cursor's query and reopen the cursor.
EXEC SQL DECLARE emp_cursor CURSOR FOR
SELECT job, sal FROM emp WHERE deptno = 30;
...
EXEC SQL WHENEVER NOT FOUND GOTO ...
LOOP
EXEC SQL FETCH emp_cursor INTO :job_title1, :salary1;
EXEC SQL FETCH emp_cursor INTO :job_title2, :salary2;
...
END LOOP;
...
For you to insert rows into the base table of a view, the owner of the schema containing the view must have INSERT privilege on the base table. Also, if the view is in a schema other than your own, you must have INSERT privilege on the view.
The INSERT ANY TABLE system privilege also allows you to insert rows into any table or any view's base table.
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the creation label of the table or view:
AT
db_name is a database identifier declared in a previous DECLARE DATABASE statement.
:host_variable is a host variable whose value is a previously declared db_name
If you omit this clause, the INSERT statement is executed on your default database.
FOR :host_integer
schema
is the schema containing the table or view. If you omit schema, Oracle7 assumes the table or view is in your own schema.
table view
is the name of the table into which rows are to be inserted. If you specify view, Oracle7 inserts rows into the view's base table.
dblink
is a complete or partial name of a database link to a remote database where the table or view is located. For information on referring to database links, see Chapter 2 of the Oracle7 Server SQL Reference. You can only insert rows into a remote table or view if you are using Oracle7 with the distributed option.
If you omit dblink, Oracle7 assumes that the table or view is on the local database.
column
is a column of the table or view. In the inserted row, each column in this list is assigned a value from the VALUES clause or the query.
If you omit one of the table's columns from this list, the column's value for the inserted row is the column's default value as specified when the table was created. If you omit the column list altogether, the VALUES clause or query must specify values for all columns in the table.
VALUES
specifies a row of values to be inserted into the table or view. See the syntax description of expr in Chapter 3 of the Oracle7 Server SQL Reference. Note that the expressions can be host variables with optional indicator variables. You must specify an expression in the VALUES clause for each column in the column list.
subquery
is a subquery that returns rows that are inserted into the table. The select list of this subquery must have the same number of columns as the column list of the INSERT statement. For the syntax description of a subquery, see "SELECT" in Chapter 4 of the Oracle7 Server SQL Reference.
Array host variables in the WHERE clause can have different sizes. In this case, the number of times Oracle7 executes the statement is determined by the smaller of the following values:
EXEC SQL
INSERT INTO emp (ename, empno, sal)
VALUES (:ename, :empno, :sal);
EXEC SQL
INSERT INTO new_emp (ename, empno, sal)
SELECT ename, empno, sal FROM emp
WHERE deptno = :deptno;
cursor
is the cursor to be opened.
specifies the host variables to be substituted into the WHERE clause of the associated query.
:host_variable specifies a host variable with an optional indicator variable to be substituted into the statement associated with the cursor.
DESCRIPTOR
specifies a descriptor that describes the host variables to be substituted into the WHERE clause of the associated query. The descriptor must be initialized in a previous DESCRIBE statement.
The substitution is based on position. The host variable names specified in this statement can be different from the variable names in the associated query.
Once you have opened a cursor, its input host variables are not reexamined until you reopen the cursor. To change any input host variables and therefore the active set, you must reopen the cursor.
All cursors in a program are in a closed state when the program is initiated or when they have been explicitly closed using the CLOSE command.
You can reopen a cursor without first closing it. For more information on this command, see Chapter 4.
EXEC SQL DECLARE emp_cursor CURSOR FOR
SELECT ename, empno, job, sal
FROM emp
WHERE deptno = :deptno;
EXEC SQL OPEN emp_cursor;
statement_id
is the identifier to be associated with the prepared SQL statement or PL/SQL block. If this identifier was previously assigned to another statement or block, the prior assignment is superseded.
:host_string
is a host variable whose value is the text of a SQL statement or PL/SQL block to be prepared.
text
is a string literal containing a SQL statement or PL/SQL block to be prepared.
A SQL statement is prepared only once, but can be executed any number of times.
EXEC SQL PREPARE my_statement FROM :my_string;
EXEC SQL EXECUTE my_statement;
You can also use this command to manually undo the work done by an in-doubt distributed transaction.
To manually roll back an in-doubt distributed transaction that you originally committed, you must have FORCE TRANSACTION system privilege. To manually roll back an in-doubt distributed transaction originally committed by another user, you must have FORCE ANY TRANSACTION system privilege.
WORK
is optional and is provided for ANSI compatibility.
rolls back the current transaction to the specified savepoint. If you omit this clause, the ROLLBACK statement rolls back the entire transaction.
FORCE
manually rolls back an in-doubt distributed transaction. The transaction is identified by the text containing its local or global transaction ID. To find the IDs of such transactions, query the data dictionary view DBA_2PC_PENDING.
ROLLBACK statements with the FORCE clause are not supported in PL/SQL.
RELEASE
frees all resources and disconnects the application from the Oracle7 Server. The RELEASE clause is not allowed with SAVEPOINT and FORCE clauses.
Using the ROLLBACK command without the TO SAVEPOINT clause performs the following operations:
EXEC SQL ROLLBACK;
EXEC SQL ROLLBACK TO SAVEPOINT sp5;
If there is a network failure during the commit process for a distributed transaction, the state of the transaction may be unknown, or in-doubt. After consultation with the administrators of the other databases involved in the transaction, you may decide to manually commit or roll back the transaction on your local database. You can manually roll back the transaction on your local database by issuing a ROLLBACK statement with the FORCE clause.
For more information on when to roll back in-doubt transactions, see Oracle7 Server Distributed Systems, Volume I.
You cannot manually roll back an in-doubt transaction to a savepoint.
A ROLLBACK statement with a FORCE clause only rolls back the specified transaction. Such a statement does not affect your current transaction.
EXEC SQL
ROLLBACK WORK
FORCE '25.32.87';
AT
identifies the database on which the savepoint is created. The database can be identified by either:
db_name is a database identifier declared in a previous DECLARE DATABASE statement.
:host_variable is a host variable whose value is a previously declared db_name.
If you omit this clause, the savepoint is created on your default database.
savepoint
is the name of the savepoint to be created.
Example
This example illustrates the use of the embedded SQL SAVEPOINT command:
EXEC SQL SAVEPOINT save3;
For you to select rows from the base tables of a view, the owner of the schema containing the view must have SELECT privilege on the base tables. Also, if the view is in a schema other than your own, you must have SELECT privilege on the view.
The SELECT ANY TABLE system privilege also allows you to select data from any table or any snapshot or any view's base table.
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must dominate the creation label of each queried table, view, or snapshot or you must have READUP system privileges.
AT
db_name is a database identifier declared in a previous DECLARE DATABASE statement.
:host_variable is a host variable whose value is a previously declared db_name.
If you omit this clause, the SELECT statement is issued to your default database.
select_list
identical to the non-embedded SELECT command except that a host variables can be used in place of literals.
INTO
specifies output host variables and optional indicator variables to receive the data returned by the SELECT statement. Note that these variables must be either all scalars or all arrays, but arrays need not have the same size.
WHERE
restricts the rows returned to those for which the condition is TRUE. See the syntax description of condition in Chapter 3 of the Oracle7 Server SQL Reference. The condition can contain host variables, but cannot contain indicator variables. These host variables can be either scalars or arrays.
All other keywords and parameters are identical to the non-embedded SQL SELECT command.
You can use comments in a SELECT statement to pass instructions, or hints, to the Oracle7 optimizer. The optimizer uses hints to choose an execution plan for the statement. For more information on hints, see Oracle7 Server Tuning.
EXEC SQL SELECT ename, sal + 100, job
INTO :ename, :sal, :job
FROM emp
WHERE empno = :empno
For you to update values in the base table of a view, the owner of the schema containing the view must have UPDATE privilege on the base table. Also, if the view is in a schema other than your own, you must have UPDATE privilege on the view.
The UPDATE ANY TABLE system privilege also allows you to update values in any table or any view's base table.
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the creation label of the table or view:
AT
db_name is a database identifier declared in a previous DECLARE DATABASE statement.
:host_variable is a host variable whose value is a previously declared db_name.
If you omit this clause, the UPDATE statement is issued to your default database.
FOR :host_integer
limits the number of times the UPDATE statement is executed if the SET and WHERE clauses contain array host variables. If you omit this clause, Oracle7 executes the statement once for each component of the smallest array.
schema
is the schema containing the table or view. If you omit schema, Oracle7 assumes the table or view is in your own schema.
table view
is the name of the table to be updated. If you specify view, Oracle7 updates the view's base table.
dblink
is a complete or partial name of a database link to a remote database where the table or view is located. For information on referring to database links, see Chapter 2 of the Oracle7 Server SQL Reference. You can only use a database link to update a remote table or view if you are using Oracle7 with the distributed option.
alias
is a name used to reference the table, view, or subquery elsewhere in the statement.
column
is the name of a column of the table or view that is to be updated. If you omit a column of the table from the SET clause, that column's value remains unchanged.
expr
is the new value assigned to the corresponding column. This expression can contain host variables and optional indicator variables. See the syntax of expr in Chapter 3 of the Oracle7 Server SQL Reference.
subquery_1
is a subquery that returns new values that are assigned to the corresponding columns. For the syntax of a subquery, see "SELECT" in Chapter 4 of the Oracle7 Server SQL Reference.
subquery_2
is a subquery that return a new value that is assigned to the corresponding column. For the syntax of a subquery, see "SELECT" in Chapter 4 of the Oracle7 Server SQL Reference.
WHERE
specifies which rows of the table or view are updated:
condition updates only rows for which this condition is true. This condition can contain host variables and optional indicator variables. See the syntax of condition in Chapter 3 of the Oracle7 Server SQL Reference.
CURRENT OF updates only the row most recently fetched by the cursor. The cursor cannot be associated with a SELECT statement that performs a join unless its FOR UPDATE clause explicitly locks only one table.
If you omit this clause entirely, Oracle7 updates all rows of the table or view.
Array host variables can have different sizes. In this case, the number of times Oracle7 executes the statement is determined by the smaller of the following values:
You can use comments in an UPDATE statement to pass instructions, or hints, to the Oracle7 optimizer. The optimizer uses hints to choose an execution plan for the statement. For more information on hints, see Oracle7 Server Tuning.
For more information on this command, see Chapters 4 and 7.
EXEC SQL UPDATE emp
SET sal = :sal, comm = :comm INDICATOR :comm_ind
WHERE ename = :ename;
EXEC SQL UPDATE emp
SET (sal, comm) =
(SELECT AVG(sal)*1.1, AVG(comm)*1.1
FROM emp)
WHERE ename = 'JONES';
host_variable Keywords and Parameters
is the host variable to be assigned an Oracle7 external datatype.
is an Oracle7 external datatype recognized by the Oracle Precompilers (not an Oracle7 internal datatype). The datatype may include a length, precision, or scale. This external datatype is assigned to the host_variable. For a list of external datatypes, see Chapter 3.
EXEC SQL BEGIN DECLARE SECTION;
...
dept_name CHARACTER(15); -- default datatype is CHAR
EXEC SQL VAR dept_name IS STRING; -- reset to STRING
...
buffer CHARACTER(200); -- default datatype is CHAR
EXEC SQL VAR buffer IS RAW(200); -- refer to RAW
...
EXEC SQL END DECLARE SECTION;
NOT FOUND
SQLERROR
identifies a condition that results in a negative return code.
SQLWARNING
identifies a non-fatal warning condition.
CONTINUE
indicates that the program should progress to the next statement.
GOTO
indicates that the program should branch to the statement named by label.
STOP
DO
indicates that the program should call a host language routine. The syntax of routine depends on your host language. See your language-specific Supplement to the Oracle Precompilers Guide.
The scope of a WHENEVER statement is positional, rather than logical. A WHENEVER statement applies to all embedded SQL statements that textually follow it in the source file, not in the flow of the program logic. A WHENEVER statement remains in effect until it is superseded by another WHENEVER statement checking for the same condition.
For more information on this command, see Chapter 7. Do not confuse the WHENEVER embedded SQL command with the WHENEVER SQL*Plus command.
EXEC SQL WHENEVER NOT FOUND CONTINUE;
...
EXEC SQL WHENEVER SQLERROR GOTO sql_error:
...
sql_error:
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK RELEASE;
|
Prev
|  Copyright © 1996 Oracle Corporation. All Rights Reserved. |
Library |
Product |
Contents |
Index |