Support For Stored Procedures

Silk Performer supports stored procedures in your Silk Performer programs. You can use in, out and in/out parameters in your stored procedures. Likewise you can use fetchable procedures that use fetch statements to get multiple-row results of the procedure (similar to select commands returning multiple rows using into variables and fetch statements). The syntax of calling a stored procedure in a transaction is the same as the syntax of calling a SQL command. The syntax of defining a stored procedure in the SQL section is very similar to defining a SQL command. You define stored procedures (more precisely, calls to stored procedures) in the SQL section of your Silk Performer program. You do not write the stored procedure directly in your Silk Performer program; instead you write stored procedures in the environment of your DBMS and only call the stored procedure inside your Silk Performer script using ODBC syntax.

Syntax

StoredProc = ident "(" "proc" ")" ":" SqlProc.

SqlProc = "{" [ bindvar "=" ] "call" ProcName
          [ BindVar [ "in" | "out" | "inout" ]
          { "," BindVar [ "in" | "out" | "inout"] } ] "}"
          [ "into" BindVar { "," BindVar } ].

BindVar = ":" ident.

ProcName = [ ident "." ] ident.

Example

var
  v1, v2, v3, v4, v5, v6: number;

dcltrans
  transaction TestProc
  begin
    v1 := 1; v2 := 2;
    c1: SpTest();
    while not eos(c1) do
      fetch c1 next 1;
    end;
  end TestProc;

dclsql
  SpTest(proc):
    { :v3=CALL MyProc(:v1 IN, :v2 INOUT, :v4 OUT) }
    INTO :v5, :v6;