| char *out1=sqlrcur_getOutputBind(cur,"1");
char *out2=sqlrcur_getOutputBind(cur,"2");
char *out3=sqlrcur_getOutputBind(cur,"3");
To drop the stored procedure, run a query like the following. drop procedure testproc
DB2
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, out out2 double, out out3 varchar(20)) language sql
begin
set out1 = in1;
set out2 = in2;
set out3 = in3;
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_defineOutputBind(cur,"5",25);
sqlrcur_defineOutputBind(cur,"6",25);
sqlrcur_executeQuery(cur);
char *out1=sqlrcur_getOutputBind(cur,"4");
char *out2=sqlrcur_getOutputBind(cur,"5");
char *out3=sqlrcur_getOutputBind(cur,"6");
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 record as ''''
declare
output record;
begin
select $1,$2,$3 into output;
return output;
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) as (col1 int, col2 float, col3 char(20))");
sqlrcur_inputBindLong(cur,"in1",1);
sqlrcur_inputBindDouble(cur,"in2",1.1,4,2);
sqlrcur_inputBindString(cur,"in3","hello");
sqlrcur_executeQuery(cur);
char *out1=sqlrcur_getFieldByIndex(cur,0,0);
char *out2=sqlrcur_getFieldByIndex(cur,0,1);
char *out3=sqlrcur_getFieldByIndex(cur,0,2);
To drop the stored procedure, run a query like the following. drop function testfunc(int,float,char(20))
Result Sets
Some stored procedures return entire result sets. Below are examples, illustrating how to create, execute and drop this kind of stored procedure for each database that SQL Relay supports. Oracle
To create the stored procedure, run a query like the following. create or replace package types as
type cursorType is ref cursor;
end;
create function testproc return types.cursortype is
l_cursor types.cursorType;
begin
open l_cursor for select * from mytable;
return l_cursor;
end;
To execute the stored procedure from an SQL Relay program, use code like the following. sqlrcur_prepareQuery(cur,"begin :curs:=testproc; end;");
sqlrcur_defineOutputBindCursor(cur,"curs");
sqlrcur_executeQuery(cur);
sqlrcur bindcur=sqlrcur_getOutputBindCursor(cur,"curs");
sqlrcur_fetchFromBindCursor(bindcur);
char *field00=sqlrcur_getFieldByIndex(bindcur,0,0);
char *field01=sqlrcur_getFieldByIndex(bindcur,0,1);
char *field02=sqlrcur_getFieldByIndex(bindcur,0,2);
char *field10=sqlrcur_getFieldByIndex(bindcur,1,0);
char *field11=sqlrcur_getFieldByIndex(bindcur,1,1);
char *field12=sqlrcur_getFieldByIndex(bindcur,1,2);
...
To drop the stored procedure, run a query like the following. drop function上一页 [1] [2] [3] [4] [5] [6] [7] [8] [9] [10] ... 下一页 >>
|
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 