4gl中如何调用存储过程

存储过程如下：

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