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 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

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))

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.

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