create the stored procedure, run a query like the following.create procedure testproc(in in1 int, in in2 double, in in3 varchar(20)) language sql
begin
insert into mytable values (in1,in2,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_executeQuery(cur);
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 testproc(int,float,varchar(20)) returns void as ''''
begin
insert into mytable values ($1,$2,$3);
return;
end;'''' language plpgsql
To execute the stored procedure from an SQL Relay program, use code like the following. sqlrcur_prepareQuery(cur,"select testproc(:in1,:in2,:in3)");
sqlrcur_inputBindLong(cur,"in1",1);
sqlrcur_inputBindDouble(cur,"in2",1.1,2,1);
sqlrcur_inputBindString(cur,"in3","hello");
sqlrcur_executeQuery(cur);
To drop the stored procedure, run a query like the following. drop procedure testproc
Single Values
Some stored procedures return single 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. Here is an example where the procedure itself returns a value. Note that Oracle calls these functions. To create the stored procedure, run a query like the following. create function testproc(in1 in number, in2 in number, in3 in varchar2) returns number is
begin
return in1;
end;
To execute the stored procedure from an SQL Relay program, use code like the following. sqlrcur_prepareQuery(cur,"select testproc(:in1,:in2,:in3) from dual");
sqlrcur_inputBindLong(cur,"in1",1);
sqlrcur_inputBindDouble(cur,"in2",1.1,2,1);
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 testproc
Here is an example where the value is returned through an output parameter. To create the stored procedure, run a query like the following. create procedure testproc(in1 in number, in2 in number, in3 in varchar2, out1 out number) as
begin
out1:=in1;
end;
To execute the stored procedure from an SQL Relay program, use code like the following. sqlrcur_prepareQuery(cur,"begin testproc(:in1,:in2,:in3,:out1); end;");
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
Sybase and Microsoft SQL Server
In Sybase and Microsoft SQL Server, 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 @in1 int, @in2 float, @in3 varchar(20), @out1 int output as
select @out1=convert(varchar(20),@in1)
To execute the stored procedure from an SQL Relay program, use code like the following. 上一页 [1] [2] [3] [4] [5] [6] [7] [8] [9] [10] ... 下一页 >> |
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 