sqlexecution

NAME
SYNTAX
DESCRIPTION
USAGE
AMPERSAND REPLACEMENT
EXAMPLES
NOTES
COPYRIGHT
SEE ALSO

NAME

sqlexecution − RWP*Load Simulator execute sql, PL/SQL or cursor loops

SYNTAX

terminatedstatement ::=
  embeddedsqltext
| embeddedsqltext atclause ;
| identifier [ atclause ] ;
| immediatesql [ atclause ]  ;

cursorloop ::=
  for { embeddedsqltext | immediatesql | identifier }
    [ atclause ]
    [ and expression ]
  loop
    statementlist
  end [ loop ]


embeddedsqltext ::=
  SQLtext ; newline
| SQLtext newline whitespace ( / | . ) newline
| PLSQLblock newline whitespace ( / | . ) newline

DESCRIPTION

In rwloadsim, you can execute SQL statement in three different ways:

	1.		You can simply embed your SQL or PL/SQL directly in your code via embeddedsqltext. This approach is useful in many cases, but it does have some limitations.
	2.		You can execute immediate SQL or PL/SQL, which relaxes some of the limitations.
	3.		You can separate the declaration and execution into two rwloadsim statements, which gives you full control. This was the only method available in previous versions.

For details, see sqldeclaration(1rwl).

USAGE

embeddedsqltext
for embeddedsqltext loop s;s;s; end

The most simple way to execute sql is to embed the sql text directly in your program, which can be done if only implicit bind and define is needed, and if any dynamic parts of your sql can be handled using simple ampersand replacement as described below. Hence, all bind variables must exist as variables of the same name, and bindout will be done only if the $bindoutname:on directive is in effect. For queries all select list elements must have simple names or have an alias as a simple name, and these must exist as variables.

The names for both placeholders and select list elements are by default converted to lowercase before matching to variables declared in your rwloadsim program. If the $implicit:keepcase directive is in effect, this conversion to lowercase does not take place.

If an at clause is provided, the execution will be against the specified database.

It is highly recommended that if the sql statement is a query, it is expected to return (at most) one row. For queries returning an unknown number of rows, use the cursor loop syntax, which can include an at clause and/or an and clause to terminate the fetch early if needed.

You can provide array size for query respectively dml using the directive $embeddedqueryarray or $embeddeddmlarray. By default, queries use OCI prefetch based on memory, and DML does not use array.

You can make directly embedded sql dynamic using ampersand replacement as described below.

identifier

An identifier that is not followed by an open parenthesis must be a SQL statement which is executed once. If an at clause is provided, the execution will be against the specified database. It is highly recommended that if the sql statement is a query, it is expected to return (at most) one row.

If the SQL statement is a dml that has an array size set, rwloadsim will buffer executions until the array is filled or a commit or rollback is executed. If needed, the array can be flushed using modify sql array execute.

for c at d and e loop s;s;s; end

The identifier c must refer to a SQL statement that is a query, and the statement list between loop and end will be executed once for each row fetched from the query. If no rows are returned, no execution will take place. The optional at clause can be used to choose a particular database, d, otherwise the currently chosen one will be used.

If array fetch is active (which is the case by default), rwloadsim will use the Oracle Call Interface array fetch capabilities; if define array is used, rwloadsim will create a client side array. In either case, actual fetch of rows from the database will take place initially and subsequently only when the array has been exhausted in the loop.

The optional and followed by an expression e before the loop keyword will cause the cursor to be canceled when the expression is zero. Note that due to array fetching, some rows may have been fetched from the database but not yet processed when the condition after and becomes false. The cursor will also be canceled if you perform a break or a return from inside the loop.

sql execute ... end
for sql execute ... end at d and e loop s;s;s; end

In stead of an identifier that refers to a declared SQL statement you can immediately execute a SQL statement or a cursor loop by using the syntax for immediate sql beginning with sql execute and terminating with end as documented in sqldeclaration(1rwl). This has advantage over embedded sql as it allows sql specifications such as array. With this syntax, the at clause and the and keyword followed by an expression have the same effects as they do when executing named SQL statements.

AMPERSAND REPLACEMENT

Inspired by SQL*Plus, you can make directly embedded sql dynamic by including ampersand replacement in the sql text. To enable this, use the $ampersand:on directive.

An ampersand replacement consist of the ampersand character & followed by the name of a string, integer or double variable and by a dot (decimal point) character. As an example, if your embedded sql contains &myvar., it will be replaced by the contents of the variable myvar when executed. The dot character is not optional as in SQL*Plus even when followed by white space. If you need to include an actual & character in your sql statement when ampersand replacement is on, it must be written && i.e. two & characters immediately after each other. Note that the use of && in SQL*Plus is irrelevant in rwloadsim.

If the variable is of type integer or double, the text that will be put in your sql statement is the text representation of your variable according to your current $iformat or $dformat setting. Note that this may imply different rounding if compared to using a placeholder.

If the variable is of type string, the text will simply be the contents of your variable, this means you should have single quote around it if it is to be taken as an actual text string by Oracle. If the string variable actually contains e.g. the name of a table, you may consider putting double quotes around it unless you want Oracle’s standard automatic conversion to upper case.

Ampersand replacement has similar functionality as immediate sql, when used with a dynamically generated sql text. The benefit of ampersand replacement is the simpler syntax similar to SQL*Plus.

When ampersand replacement is in use, the $embeddedqueryarray is in effect for cursor loop queries, but $embeddeddmlarray is not as the actual sql text potentially is different for each execution. The same constraint applies for dynamically generated immediate sql.

Ampersand replacement is only available when the sql is embedded directly in your code. A warning will be shown if you attempt using it in any other place such as when declaring a sql variable.

Ampersand replace can only be used to replace parts of your sql with the contents of variable; your sql statement must still explicitly start with a relevant keyword such as select, create or insert, as rwloadsim needs such a keyword to identify the beginning of a sql statement.

EXAMPLES

Cursor loop with embedded sql

This example shows how to declare some variables that match select list elements and bind variables in your SQL statement. The sql statement is not declared, but is embedded as a cursor loop against some specified database.

string ename, dname;
integer empno;
double monthsal;
integer deptno := 10; $useroption:deptno
for
  select e.empno, e.ename, d.dname
  , e.sal/12 monthsal
  from emp e join dept d
  on e.deptno = d.deptno
  where d.deptno=:deptno
  /
at mydatabase
loop
  printf "%5d %10s %6.2f %10s0, empno, ename, monthsal, dname;
end loop;

Ampersand replacement

This example shows how you can use ampersand replacement to create a number of identically shaped tables.

$ampersand:on
string tablename;
integer t;
for t := 1 .. 10 loop
  # generate the tablename
  sprintf tablename, "tab%02d", t;
  # execute the ddl using ampersand replacement for the name of the table
  create table &tablename. (a number primary key, b varchar2(10));
end loop;

For more examples, see sqldeclaration(1rwl).

NOTES

Embedded sql text does itself include a terminator (for both SQL and PL/SQL a line with only white space terminated by / or . or for SQL a ; at the end of a line) and that it therefore should not be followed by another terminating ; when used as a simple statement. In other cases, such as when an at clause is present, the complete statement must be terminated with an ; as any other statement in rwloadsim.

After executing DML, you should always have either a commit or rollback, and rwloadsim will display a warning and force a rollback if this is missing. In your main program, such statements must be wrapped in an executionblock unless you have a default database declared as dedicated.