存储过程如下:
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;
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');
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;
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
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;
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
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
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
MAIN02
DEFINE n INTEGER03
DEFINE d DECIMAL(6,2)04
DEFINE c VARCHAR(200)05
DATABASE test106
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 = 11113
EXECUTE stmt USING n IN, d INOUT, c INOUT14
DISPLAY d15
DISPLAY c16
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.
原来是引的我的。。。。。。。
回覆刪除