sqldeclaration − RWP*Load Simulator declaration of SQL statements
sqldeclaration ::=
sql identifier
[ sqltext ]
{ sqlspecification ; }
end [ sql | identifier ]
immediatesql ::=
sql execute
sqltext
{ sqlspecification ; }
end [ sql ]
sqltext ::=
embeddedsqltext
| file concatenation ;
| concatenation ;
embeddedsqltext ::=
SQLtext ; newline
| SQLtext newline whitespace ( / | . ) newline
| PLSQLblock newline whitespace ( / | . ) newline
sqlspecification ::=
bind bindspecification { , bindspecification }
| bindout bindspecification { , bindspecification }
| define integer identifier { , integer identifier }
| bind sql
| define sql
| array [ define ] expression
| ignoreerror
| cursorcache
| nocursorcache
bindspecification ::=
| string identifier
| integer identifier
| : identifier
SQLtext ::=
PLSQLblock ::=
See below under SCANNING.
In rwloadsim, you can include SQL statements in three different ways:
The most simple method, called embedded sql can be used if you need to execute single row SQL, typically dml, ddl or single row queries, or if you need to execute some query as a cursor loop. You simply include the SQL text directly in your rwl program. The following constraints apply to embedded sql:
|
1. |
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. | ||
|
2. |
For both single row queries and cursor loop, all select list elements must have simple names or have an alias as a simple name, and these must exist as variables. | ||
|
3. |
No specifications such as array size can be provided; however, see below for directives setting array sizes for embedded sql. | ||
|
4. |
The sql is anonymous and cannot be used with any modify sql statement as these require the name of the sql. |
The second approach is called immediate sql which is still anonymous but without the first three restrictions of embedded sql. In your code, you initiate immediate sql with the keywords sql execute and it is finished with the keyword end.
The third method was the only one available in earlier versions of rwloadsim. Using this method, you first declare the sql statement as a variable, which subsequently can be used in several different places. The typical use of a SQL variable is to execute it as such, which normally is done for DML, DDL, PL/SQL or single row queries, or if it is a query without a known number of rows to execute it in a cursor loop. Using a variable is suggested if you need to execute the same SQL from different places in your rwloadsim program.
The sql keyword followed by an identifier initiates a declaration of a static or dynamic sql statement and the declaration potentially provides a list of variables used for bind, define and bindout in addition to several other attributes. Each such attribute (such as bind) must be terminated by a ;. and the complete declaration must be finished by the keyword end which can be optionally followed by sql or by the name of the sql.
The RWP*Load Simulator recognizes keywords or sequences of multiple keywords that initiate SQL or PL/SQL and if these are seen, SQL or PL/SQL scan will be started. As opposed to anything else in rwloadsim, this scan is not case sensitive.
The following list all keywords or sequences of keywords that initiate scan for PL/SQL, which implies the scan finishes by a line with white space and a / or . at the end of the line.
declare
begin
--
create [ or replace ] procedure
create [ or replace ] function
create [ or replace ] package
create [ or replace ] library
create [ or replace ] trigger
create [ or replace ] java
The following lists all keywords or sequences of keywords that initiate scan for SQL, which implies the scan additionally finishes by a ; at the end of a line.
select
delete
update
insert
create (except those mentioned above)
drop
alter
/*
administer key management
analyze
associate statistics
audit
comment on
disassociate statistics
explain plan
flashback database
flashback table
grant
lock table
noaudit
purge
rename
revoke
savepoint
set transaction
set constraint
set role
with
The two comment keywords /* and -- will initiate scanning for respectively SQL or PL/SQL even if they are not immediately followed by white space.
As opposed to SQL*Plus, rwloadsim does not terminate scanning for SQL or PL/SQL at an empty line.
Rwloadsim does not make any modifications to either, so the complete text including any newline, white space, etc from the initial keyword and until the terminator becomes the text that eventually will be the argument to OCIStmtPrepare2.
If you have a need for comments inside the SQL or PL/SQL, use the appropriate syntax from those languages, i.e. -- or /* */; a # will be part of the SQL or PL/SQL text.
You declare a static sql variable using the keyword sql followed by the name of the variable, and the actual text of the SQL or PL/SQL must follow. There are three ways to provide the SQL text:
|
1. |
Have rwloadsim scan for SQL or PL/SQL similar to how SQL*Plus does it. This is the preferred approach. | ||
|
2. |
Provide the SQL or PL/SQL text in a named file. As an alternative to embed your SQL or PL/SQL text directly in your rwl source file you can use the file keyword followed by a concatenation that resolves to a file name. The file will be opened and the SQL or PL/SQL text will be read from it; the file should not have either of the terminators ; . /. | ||
|
3. |
Have a concatenation (including just a string constant) that is the full sql text. This is primarily useful if the full SQL or PL/SQL text is created from concatenating several text strings or variables containing text strings. Note that local variables cannot be used for this, and that the value is a static, compile time value. If you need sql inside a procedure that contains values from expressions, use the dynamic sql approach of rwloadsim. Also note that if you use this approach, any " needed in your sql text must be prefixed by \ for it to become part of a string in rwloadsim. |
If there is no SQL or PL/SQL provided, neither directly in your rwl source file nor being read from a file, the declared sql statement is dynamic and the text must be provided using the modify sql command. Note that the rwl language (presently) does not have a mechanism to describe the actually provided SQL statement. You can neither get the type of the sql (e.g. query or DML), nor the list of select list elements or bind variables. During declaration of a dynamic sql, you can provide bind, bindout or define variables that are known to be present for all executions regardless of the actual SQL text. During later execution, you can add extra variables as needed using the modify sql command.
In most cases, you will need to provide attributes for your sql statement. These include variables use for bind and/or define, array sizes, use of the cursor cache, etc.
bind placeholder variable
Bind the named variable as input to a placeholder in your sql text. The placeholder is either an integer which results in bind-by-position or is a text string which results in bind-by-name in which case the first character of the text must be a colon. This can e.g. be used in the values clause of an insert statement, or in the where clause of a query.
You can replace the placeholder by a single : in which case it will be bind-by-name where both the name of the placeholder and the name of the variable it is bound to is the identifier following the colon.
bindout placeholder variable
Bind the named variable as output from a placeholder in your sql text. This can e.g. be used as an output variable in a PL/SQL block or in the returning clause of an insert statement. Bindout cannot be used in combination with the array interface. As for normal bind, you can use bind-by-position or bind-by-name and you can replace the placeholder with :.
define position variable
Define the named variable as select-list-element of a query. The integer position is the number in the select list, and the call results in a define-by-position.
bind sql
define sql
These options will turn on implicit bind respectively define for the SQL statement; both can be used as needed. See below under IMPLICIT BIND/DEFINE for details.
array size
Set the number of rows in the array interface. For cursor loops, the default is to use an array sized by memory, and for anything else, the default is 1. For cursor loops, the array interface is handled using the features of Oracle Call Interface, unless you use the array define keywords in which case the array is allocated by rwloadsim. For DML, the array interface is allocated by rwloadsim and is automatically flushed as needed during commit or rollback. You can flush the array for DML explicitly using a modify sql array execute statement.
ignoreerror
Prevent writing any error during execution of the sql statement to stderr. You should code error handling yourself using the oraerror and/or oraerrortext variables. Other error processing such as insert into the oerstats table still takes place.
nocursorcache
By default, rwloadsim will use the cursor cache provided by Oracle Call Interface. It may be beneficial to turn this off for sql that is only executed once.
Typically, you will use bind and/or define to associate variables in your rwl program with bind-variables (a.k.a. placeholders) or select list elements. To ease your rwl program writing, you can have rwloadsim do this implicitly, which it does by matching names of placeholders or select list elements in your SQL statement to declared variables in your rwl program. If a match is found, the mapping will be done and the appropriate bind or define is handled without an explicit bind and/or define.
This implicit matching can be specified as part of your SQL statement declaration or it can be enabled generally via directives. The implicit matching takes place once during the first execution of your SQL statement, and the binds and/or defines done will subsequently be used for any subsequent execution. The name of the select list element or bind variable found in the SQL statement will be converted to lowercase before matching to a variable name. This mechanism can be turned off using the $implicit:keepcase directive.
If your SQL statement has bind variables (e.g. :1, :2, etc) or select list elements (e.g. expressions), that cannot possibly be matched to any identifiers, you need to either use explicit bind or define or to replace a bind variable by a proper name or give an alias to the select list element.
Any bindout required will be done if the $bindoutname:on directive is in effect, otherwise it must be explicitly done. Implicit define cannot be used in combination with array define.
You can combine explicit and implicit bind/define, in which case the implicit behavior will only apply to those not already done explicitly. It is, however, suggested you do not combine the two.
Queries with implicit define and without an explicit array will use OCI prefetch based on memory set to 100kB.
You can also use implicit bind/define with dynamic SQL, in which case any implicitly created binds and/or defines are released when modify sql release is being performed.
In version 3 of rwloadsim, you can use the keyword execute rather than an identifier, causing your SQL to be unnamed and immediately executed. This simplifies your rwloadsim code as you do not need to separate the declaration and the execution of your SQL statements. Additionally, implicit bind and define will be enabled by default for immediate sql.
The immediate sql syntax can be used stand alone to execute single row queries, DDL, DML or PL/SQL, or it can be used as part of a cursor loop.
If bindout is needed, you can use the $bindoutname:on directive, which causes rwloadsim to inspect the first characters of the placeholder, and if these match, the bind will be a bindout. The only specification you typically would need is array to enable the array interface for DML. This cannot be used if bindout is in effect.
The sql text for immediate sql can be a concatenation, which is calculated at runtime, effectively implying dynamically generated sql. However, as rwloadsim does not have a possibility for the programmer to describe the sql to find select list elements and/or bind variables, these must be "known" to the programmer. As an example, using ’select *’ in a query requires the programmer to know what the asterisk expands to and therefore which define variables to declare.
Version 3 of rwloadsim also has the possibility to simply embed static sql directly without the need for the sql and end keywords to wrap the sql and any specifications needed. Due to the missing keywords, you cannot provide any specifications, which also means you cannot set the array size. You can set a default array sizes using either of the directives $embeddedqueryarray or $embeddeddmlarray. The default for queries in cursor loops is to use OCI memory based array and for dml to not use array.
If the $bindoutname:on directive is in effect, placeholder names where the first characters match the letters of the directive will be used as bindout.
Variables of type integer or double can be null in rwloadsim, and that property is being retained when such variables are used for implicit or explicit bind, bindout or define.
Simple query
Declare some variables and a simple sql statement using bind by name for its bind variable, execute the query and print some output:
integer empno := 7900; double sal; string(20) ename; sql getemp select ename, sal from emp where empno=:empno / bind ":empno" empno; define 1 ename, 2 sal; end; getemp; printline "Employee #" empno ", name:" ename ", salary:" sal;
Declaration and use of cursor
sql getemps select e.ename, e.sal , d.loc from emp e join dept d on e.deptno = d.deptno where d.deptno = :1 and e.sal > :2; bind 1 deptno; bind 2 sallimit; define 1 ename, 2 sal; define 3 location; array 5; end sql; for getemps loop printline ename, sal, loc; end loop;
Implicit bind and define
Declare a procedure showing how implicit bind and define can be used. Note that the alias for the select list element e.sal/12 is required as there would otherwise be no simple name that could be matched to a variable.
$implicit:both
procedure printemps(integer deptno)
string ename, dname;
integer empno;
double monthsal;
sql getemps
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
/
end;
for getemps loop
printline empno, ename, monthsal, dname;
end loop;
end printemps;
Immediate execute of simple query
This is similar to the first example using immediate sql execution.
integer empno := 7900; double sal; string(20) ename; sql execute select ename, sal from emp where empno=:empno / end; printline "Employee #" empno ", name:" ename ", salary:" sal;
Cursor loop with embedded sql
Declare a procedure that uses embedded sql to print employees in some department.
procedure printemps(integer deptno)
string ename, dname;
integer empno;
double monthsal;
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
order by e.empno
/
loop
printf "%5d %10s %6.2f %10s0, empno, ename, monthsal, dname;
end loop;
end printemps;
Array for DML
This example shows how to use the array interface for an insert statement.
integer i; # assume more variables are declared sql inslin insert into orderlines ( ordno, linno, product, amount ) values -- it is fine to use bind by position -- also when the actual binds are named ( :a, :b, :c, :d) . # You do not need to have binds in any # particular order and # can mix bind by position and name bind 3 prodcode, 1 ordno; bind ":d" amount; bind 2 i; # Use array of 10 rows array 10; end inslin; ordno := ... ; for i:=1 .. linecount loop # set values prodcode := ... ; amount := ... ; inslin; # fill array and flush each 10 rows end loop; commit; # will flush array as needed
Use of bindout
Directly execute a PL/SQL block having an output bind:
$bindoutname:on:ret # binds beginning with ’ret’ are bindout
string retdate;
sql execute
BEGIN
:retdate := to_char(sysdate,’DD.MM.YYYY HH24:MI’);
END;
.
end;
printline retdate;
Dynamic sql
Declare a dynamic sql statement with an array size and an initial set of define variables assuming these will always be needed during actual execution.
sql myquery define 1 cola, 2 colb; array 42; end;
It is a programming error not to use bindout when needed, and not doing so can lead to unexpected results. In this code
integer val := 10; begin :val := 42; end; / printline val, 0+val;
the output from the printline statement will be
10 42
since the string representation of val is set to the string "10" during the initial assignment, while the implicit bind will be to the integer representation. The latter is then used in the expression 0+val. If the correct bindout had been provided, the string representation ("42") of val would have been generated after execution of the PL/SQL block.
The scan for SQL or PL/SQL starts anywhere one of the keywords listed above are found. If this is used at an inappropriate place, the rwloadsim parser will report an error.
Previous versions of rwloadsim did not recognize all SQL keywords or sets of keywords and you could therefore use these as identifiers. As SQL scan now is initiated by any possible keyword, you will get errors if you have been using these as identifiers and you will need to change your code.
Note that the full syntax for create java is more complex than rwloadsim recognizes and that you therefore will need to initiate create java by -- if it includes any of the keywords and resolve, and compile or noforce.
Copyright © 2023 Oracle Corporation
Licensed under the Universal Permissive License v 1.0 as shown at https://oss.oracle.com/licenses/upl
sqlexecution(1rwl), sqlstatement(1rwl), directive(1rwl)