存储过程如下:
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