4gl中如何调用存储过程

引用來源:  http://blog.csdn.net/bason/archive/2010/03/17/5388592.aspx

存储过程如下：

drop table test1;
create table test1
(
    tid decimal(10),
    t1 varchar2(50),
    t2 varchar2(100)
);
CREATE UNIQUE INDEX test_pk ON test1 (tid);
GRANT DELETE, INDEX, INSERT, SELECT, UPDATE ON test1 TO PUBLIC;

insert into test1(tid,t1) values(1,'hello');
insert into test1(tid,t1) values(2,'world');

create or replace procedure sp_test1(name1  varchar2, name2  varchar2)
is
begin
    update test1 set t2=name1||name2;
end;

GRANT EXECUTE, DEBUG ON sp_test1 TO PUBLIC;

select * from test1；

在4gl中调用方法如下：

database ds
main
  BEGIN WORK
  PREPARE id1 FROM "CALL .sp_test1(?,?)"
  EXECUTE id1 USING "good"," prog"
  COMMIT WORK
end main

如果存储过程有输入输出参数：

create or replace procedure sp_test2(name1 in varchar2 , name2 in varchar2 ,  name3 out varchar2)
is
begin
    update test1 set t2=name1||name2;
    name3 := upper(name1);
end;

4gl中调用方法如下：

database ds
main
  define l_a3   char(100)
  define l_a1    char(100)
  define l_a2    char(100)
  BEGIN WORK
  PREPARE id1 from "call SP_TEST2(?,?,?)"
  LET l_a1 = "Hello"
  LET l_a2 = "Kitty"
  EXECUTE id1 USING l_a1 IN ,l_a2 IN,l_a3 out
  display "l_a3=",l_a3
  COMMIT WORK
end main

=========================================================================

4Js 相關文件
http://webcache.googleusercontent.com/search?q=cache:bApA3vIoLeEJ:www.4js.com/techdocs/genero/fgl/devel/DocRoot/User/SqlProgramming.html+genero+long+transaction&cd=1&hl=zh-TW&ct=clnk

Stored procedure call with Oracle

Oracle supports stored procedures and stored functions as a group of PL/SQL statements that you can call by name. Oracle stored functions are very similar to stored procedures, except that a function returns a value to the environment in which it is called. Functions can be used in SQL expressions.  

Stored procedures with output parameters

Oracle stored procedures or stored functions must be called with the input and output parameters specification in the USING clause of the EXECUTE, OPEN or FOREACH instruction. As in normal dynamic SQL, parameters must correspond by position, and the IN/OUT/INOUT options must match the parameter definition of the stored procedure.

To execute the stored procedure, you must include the procedure in an anonymous PL/SQL block with BEGIN and END keywords:

11   PREPARE stmt FROM "begin proc1(?,?,?); end;"

Remark: Oracle stored procedures do not specify the size of number and character parameters. The size of output values (especially character strings) are defined by the calling context (i.e. the data type of the variable used when calling the procedure). When you pass a CHAR(10) to the procedure, the returning value will be filled with blanks to reach a size of 10 bytes.

Warning: For technical reasons, the Oracle driver uses dynamic binding with OCIBindDynamic(). The Oracle Call Interface does not support stored procedures parameters with the CHAR data type when using dynamic binding. You must use VARCHAR2 instead of CHAR to define character string parameters for stored procedures.

Here is a complete example creating and calling a stored procedure with output parameters:

01 MAIN
02    DEFINE n INTEGER
03    DEFINE d DECIMAL(6,2)
04    DEFINE c VARCHAR(200)
05    DATABASE test1
06    EXECUTE IMMEDIATE "create procedure proc1( p1 in int, p2 in out number, p3 in out varchar2 )"
07                 || " is begin"
08                 || "  p2 := p1 + 0.23;"
09                 || "  p3 := 'Value = ' || to_char(p1);"
10                 || "end;"
11    PREPARE stmt FROM "begin proc1(?,?,?); end;"
12    LET n = 111
13    EXECUTE stmt USING n IN, d INOUT, c INOUT
14    DISPLAY d
15    DISPLAY c
16 END MAIN

Stored functions with a return value

To execute the stored function returning a value, you must include the function in an anonymous PL/SQL block with BEGIN and END keywords, and use an assignment expression to specify the place holder for the returning value:

11    PREPARE stmt FROM "begin ? := func1(?,?,?); end;"

Stored procedures producing a result set

Oracle supports result set generation from stored procedures with the concept of cursor variables (REF CURSOR).

Warning: Genero does not support cursor references produced by Oracle stored procedures or functions.