第一种方法:调用函数和只有out参数的存储过程
1.语法说明
(1)
DECLARE ProcedureName PROCEDURE FOR StoredProcedureName
@Param1=:Value1, @Param2=:Value2,... {USING TransactionObject} ;
注意:ProcedureName为PB中的变量,StoredProcedureName为数据库中的存储过程名,Param
为数据库存储过程参数名,Value为PB中的变量。
(2)
EXECUTE ProcedureName ;
(3)
FETCH Procedure INTO :HostVariableList ;
(4)
CLOSE ProcedureName ;
2.eg:
create or replace procedure he_test1(out1 out varchar2, out2 out varchar2) as
l_in varchar2(10);
begin
l_in := 'bb';
out1 := l_in;
out2 := l_in;
end he_test1;
----------------------------------------------------
在pb中
1.调用只带OUT参数的存储过程
string ta,tb
DECLARE get_trriff PROCEDURE FOR
he_test1 using sqlca; --可带参数,也可不带
//he_test1 @out1=:ta,@out2=tb using sqlca;
EXECUTE get_trriff;
if sqlca.SqlCode <> 0 then
MessaGeBox("a","a" + sqlca.sqlerrtext)
CLOSE get_trriff;
RETURN -1
end if
FETCH get_trriff INTO :ta,:tb;
if sqlca.SqlCode <> 0 then
MessaGeBox("b","b" + sqlca.sqlerrtext)
CLOSE get_trriff;
RETURN -1
end if
MessaGeBox("输出", ta)
MessaGeBox("输出", tb)
----------------------------------------------------------------
2.调函数
----------------------------------------------------------------
create or replace function fun1_test
return varchar2
as
out1 varchar2(10);
begin
out1 :='aa';
return out1;
end;
----------------------------------------------------------------
在pb中
string lin,ta
lin = 'aa'
DECLARE get_trriff PROCEDURE FOR
fun1_test using sqlca;
EXECUTE get_trriff;
if sqlca.SqlCode <> 0 then
MessaGeBox("a","a" + sqlca.sqlerrtext)
CLOSE get_trriff;
RETURN -1
end if
FETCH get_trriff INTO :ta;
if sqlca.SqlCode <> 0 then
MessaGeBox("b","b" + sqlca.sqlerrtext)
CLOSE get_trriff;
RETURN -1
end if
MessaGeBox("输出", ta)
第二种方法:调用带in参数的存储过程
1.语法说明
(1)
DECLARE ProcedureName PROCEDURE FOR StoredProcedureName
(:Value1,:Value2,...) {USING TransactionObject} ;
注意:ProcedureName为PB中的变量,StoredProcedureName为数据库中的存储过程名,
Value为PB中的变量。
(2)
EXECUTE ProcedureName ;
(3)
FETCH Procedure INTO :HostVariableList ;
(4)
CLOSE ProcedureName ;
2.eg:
-----------------------------------------------------------
在oracle创建存储过程
create or replace procedure he_test(tin in varchar2,
out1 out varchar2,
out2 out varchar2) as
l_in varchar2(10);
begin
l_in := 'bb';
out1 := tin;
out2 := l_in;
end he_test;
------------------------------------------------------------
在pb中
string lin,ta,tb
lin = 'ss'
DECLARE get_trriff PROCEDURE FOR
he_test (:lin) using sqlca;
EXECUTE get_trriff;
if sqlca.SqlCode <> 0 then
MessaGeBox("a","a" + sqlca.sqlerrtext)
CLOSE get_trriff;
RETURN -1
end if
FETCH get_trriff INTO :ta,:tb;
if sqlca.SqlCode <> 0 then
MessaGeBox("b","b" + sqlca.sqlerrtext)
CLOSE get_trriff;
RETURN -1
end if
MessaGeBox("输出", ta)
MessaGeBox("输出", tb)
1.语法说明
(1)
DECLARE ProcedureName PROCEDURE FOR StoredProcedureName
@Param1=:Value1, @Param2=:Value2,... {USING TransactionObject} ;
注意:ProcedureName为PB中的变量,StoredProcedureName为数据库中的存储过程名,Param
为数据库存储过程参数名,Value为PB中的变量。
(2)
EXECUTE ProcedureName ;
(3)
FETCH Procedure INTO :HostVariableList ;
(4)
CLOSE ProcedureName ;
2.eg:
create or replace procedure he_test1(out1 out varchar2, out2 out varchar2) as
l_in varchar2(10);
begin
l_in := 'bb';
out1 := l_in;
out2 := l_in;
end he_test1;
----------------------------------------------------
在pb中
1.调用只带OUT参数的存储过程
string ta,tb
DECLARE get_trriff PROCEDURE FOR
he_test1 using sqlca; --可带参数,也可不带
//he_test1 @out1=:ta,@out2=tb using sqlca;
EXECUTE get_trriff;
if sqlca.SqlCode <> 0 then
MessaGeBox("a","a" + sqlca.sqlerrtext)
CLOSE get_trriff;
RETURN -1
end if
FETCH get_trriff INTO :ta,:tb;
if sqlca.SqlCode <> 0 then
MessaGeBox("b","b" + sqlca.sqlerrtext)
CLOSE get_trriff;
RETURN -1
end if
MessaGeBox("输出", ta)
MessaGeBox("输出", tb)
----------------------------------------------------------------
2.调函数
----------------------------------------------------------------
create or replace function fun1_test
return varchar2
as
out1 varchar2(10);
begin
out1 :='aa';
return out1;
end;
----------------------------------------------------------------
在pb中
string lin,ta
lin = 'aa'
DECLARE get_trriff PROCEDURE FOR
fun1_test using sqlca;
EXECUTE get_trriff;
if sqlca.SqlCode <> 0 then
MessaGeBox("a","a" + sqlca.sqlerrtext)
CLOSE get_trriff;
RETURN -1
end if
FETCH get_trriff INTO :ta;
if sqlca.SqlCode <> 0 then
MessaGeBox("b","b" + sqlca.sqlerrtext)
CLOSE get_trriff;
RETURN -1
end if
MessaGeBox("输出", ta)
第二种方法:调用带in参数的存储过程
1.语法说明
(1)
DECLARE ProcedureName PROCEDURE FOR StoredProcedureName
(:Value1,:Value2,...) {USING TransactionObject} ;
注意:ProcedureName为PB中的变量,StoredProcedureName为数据库中的存储过程名,
Value为PB中的变量。
(2)
EXECUTE ProcedureName ;
(3)
FETCH Procedure INTO :HostVariableList ;
(4)
CLOSE ProcedureName ;
2.eg:
-----------------------------------------------------------
在oracle创建存储过程
create or replace procedure he_test(tin in varchar2,
out1 out varchar2,
out2 out varchar2) as
l_in varchar2(10);
begin
l_in := 'bb';
out1 := tin;
out2 := l_in;
end he_test;
------------------------------------------------------------
在pb中
string lin,ta,tb
lin = 'ss'
DECLARE get_trriff PROCEDURE FOR
he_test (:lin) using sqlca;
EXECUTE get_trriff;
if sqlca.SqlCode <> 0 then
MessaGeBox("a","a" + sqlca.sqlerrtext)
CLOSE get_trriff;
RETURN -1
end if
FETCH get_trriff INTO :ta,:tb;
if sqlca.SqlCode <> 0 then
MessaGeBox("b","b" + sqlca.sqlerrtext)
CLOSE get_trriff;
RETURN -1
end if
MessaGeBox("输出", ta)
MessaGeBox("输出", tb)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10678339/viewspace-531798/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10678339/viewspace-531798/