sqlrcur_prepareQuery(cur,"exec testproc");
sqlrcur_inputBindLong(cur,"in1",1);
sqlrcur_inputBindDouble(cur,"in2",1.1,2,1);
sqlrcur_inputBindString(cur,"in3","hello");
sqlrcur_defineOutputBind(cur,"out1",20);
sqlrcur_executeQuery(cur);
char *result=sqlrcur_getOutputBind(cur,"out1");
To drop the stored procedure, run a query like the following. drop procedure testproc
Interbase and Firebird
To create the stored procedure, run a query like the following. create procedure testproc(in1 integer, in2 float, in3 varchar(20)) returns (out1 integer) as
begin
out1=in1;
suspend;
end;
To execute the stored procedure from an SQL Relay program, use code like the following. sqlrcur_prepareQuery(cur,"select * from testproc(?,?,?)");
sqlrcur_inputBindLong(cur,"1",1);
sqlrcur_inputBindDouble(cur,"2",1.1,2,1);
sqlrcur_inputBindString(cur,"3","hello");
sqlrcur_executeQuery(cur);
char *result=sqlrcur_getFieldByIndex(cur,0,0);
Alternatively, you can run a query like the following and receive the result using an output bind variable. Note that in Interbase/Firebird, input and output bind variable indices are distict from one another. The index of the output bind variable is 1 rather than 4, even though there were 3 input bind variables. sqlrcur_prepareQuery(cur,"execute procedure testproc ?, ?, ?");
sqlrcur_inputBindLong(cur,"1",1);
sqlrcur_inputBindDouble(cur,"2",1.1,2,1);
sqlrcur_inputBindString(cur,"3","hello");
sqlrcur_defineOutputBind(cur,"1",20);
sqlrcur_executeQuery(cur);
char *result=sqlrcur_getOutputBind(cur,"1");
To drop the stored procedure, run a query like the following. drop procedure testproc
DB2
In DB2, stored procedures return values through output parameters rather than as return values of the procedure itself. To create the stored procedure, run a query like the following. create procedure testproc(in in1 int, in in2 double, in in3 varchar(20), out out1 int) language sql
begin
set out1 = in1;
end
To execute the stored procedure from an SQL Relay program, use code like the following. sqlrcur_prepareQuery(cur,"call testproc(?,?,?,?)");
sqlrcur_inputBindLong(cur,"1",1);
sqlrcur_inputBindDouble(cur,"2",1.1,2,1);
sqlrcur_inputBindString(cur,"3","hello");
sqlrcur_defineOutputBind(cur,"4",25);
sqlrcur_executeQuery(cur);
char *result=sqlrcur_getOutputBind(cur,"4");
To drop the stored procedure, run a query like the following. drop procedure testproc
Postgresql
To create the stored procedure, run a query like the following. create function testfunc(int,float,char(20)) returns int as ''''
declare
in1 int;
in2 float;
in3 char(20);
begin
in1:=$1;
return;
end;
'''' language plpgsql
To execute the stored procedure from an SQL Relay program, use code like the following. sqlrcur_prepareQuery(cur,"select * from testfunc(:in1,:in2,:in3)");
sqlrcur_inputBindLong(cur,"in1",1);
sqlrcur_inputBindDouble(cur,"in2",1.1,4,2);
sqlrcur_inputBindString(cur,"in3","hello");
sqlrcur_executeQuery(cur);
char *result=sqlrcur_getFieldByIndex(cur,0,0);
To drop the stored procedure, run a query like the following. drop function testfunc(int,float,char(20))
Multiple Values
Some stored procedures return multiple values. Below are examples, illustrating how to create, execute and drop this kind of stored procedure for each database that SQL Relay supports. Oracle
In Oracle, stored procedures can return values through output parameters or as return values of the procedure itself. If a procedure needs to return multiple values, it can return one of them as the return value of the procedure itself, but the rest must be 上一页 [1] [2] [3] [4] [5] [6] [7] [8] [9] [10] ... 下一页 >> |
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 