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

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

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.

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

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]  ...  下一页 >>