C数组与存储过程PL/SQL数组直接的交互

ITPUB里有个网友问到C如何调用返回数组的存储过程，简单做了个例子：

 

一、Pro*C调用返回数组的存储过程

 

SQL> create or replace procedure getidlist(p_id out dbms_sql.number_table,p_num out number)
  2 is
  3 begin
  4 select level bulk collect into p_id from dual connect by level<=10;
  5 p_num:=p_id.count;
  6 end;
  7 /

Procedure created.


SQL> declare
  2 num_table dbms_sql.number_table;
  3 cnt int;
  4 i int;
  5 begin
  6 getidlist(num_table,cnt);
  7 for i in 1..cnt loop
  8 dbms_output.put_line(num_table(i));
  9 end loop;
 10 end;
 11 /
1
2
3
4
5
6
7
8
9
10

PL/SQL procedure successfully completed.

SQL>

首先创建了一个返回数组的存储过程，第二个参数表示返回数组的元素个数。

主要的C代码如下：

 

[oracle@db2server ~]$ vi arraytest.pc

#include <string.h>
#include <ctype.h>
#include <stdlib.h>
exec sql include sqlca;



void sqlerror()
  {
    exec sql whenever sqlerror continue;
    printf("\n% .70s \n", sqlca.sqlerrm.sqlerrmc);
    exec sql rollback work release;
    exit(1);
  }



int main(int argc,char *argv)
{

  exec sql begin declare section;
    int num[100],i,arraysize;
    char * connstr="htyansp/htyansp";
  exec sql end declare section;


  exec sql whenever sqlerror do sqlerror();
  exec sql connect :connstr;
  exec sql execute
     begin
      getidlist(:num,:arraysize);
     end;
  end-exec;
  for(i=0;i<arraysize;i++)
  {
    printf("%d\n",num[i]);
  }
  exec sql commit work release;
  exit(0);
}
"arraytest.pc" 41L, 713C written

编译为可执行文件，执行结果如下：

 

[oracle@db2server ~]$
[oracle@db2server ~]$ proc parse=none arraytest.pc userid=htyansp/htyansp SQLCHECK=SEMANTICS

Pro*C/C++: Release 11.2.0.1.0 - Production on Sun Aug 19 00:50:44 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

System default option values taken from: /u01/app/oracle/product/db11gr2/precomp/admin/pcscfg.cfg

[oracle@db2server ~]$ gcc arraytest.c -o arraytest $ORACLE_HOME/lib/libclntsh.so
[oracle@db2server ~]$ ./arraytest
1
2
3
4
5
6
7
8
9
10

例子中用到了嵌入式PL/SQL块，如果把 PL/SQL块

exec sql execute
     begin
      getidlist(:num,:arraysize);
     end;
  end-exec;
 
改为下面的形式，编译倒是可以能过去，在执行的时候却报错：
 
 exec sql call getidlist(:num,:arraysize);

 

[oracle@db2server ~]$ proc parse=none arraytest.pc userid=htyansp/htyansp SQLCHECK=SEMANTICS

Pro*C/C++: Release 11.2.0.1.0 - Production on Sun Aug 19 01:02:32 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

System default option values taken from: /u01/app/oracle/product/db11gr2/precomp/admin/pcscfg.cfg

Warning at line 30, column 12, file arraytest.pc:
  exec sql call getidlist(:num,:arraysize);
...........1
PCC-W-02344, Host variable array size mismatch. Using minimum: 1
[oracle@db2server ~]$ gcc arraytest.c -o arraytest $ORACLE_HOME/lib/libclntsh.so
[oracle@db2server ~]$ ./arraytest

ORA-06553: PLS-306: ?? 'GETIDLIST' ??????????

二、Pro*C向存储过程传递数组

 

SQL> desc t;
 名称 是否为空? 类型
 ----------------------------------------- -------- ----------------------------

 ID NUMBER(38)
 NAME
                                           VARCHAR2(30)
SQL> select * from t;

未选定行

SQL>


[oracle@db2server ~]$ vi htyansp.pc


#include <string.h>
#include <ctype.h>
#include <stdlib.h>
exec sql include sqlca;
main()
{
  exec sql begin declare section;
     char * connstr="htyansp/htyansp";
     char v_object_name[10][10]={"A","B","C","D","E","F","G","H","I","G"};
     int v_num[10]={0,1,2,3,4,5,6,7,8,9};
  exec sql end declare section;
  exec sql connect :connstr;
  exec sql execute
    declare
      type num_table is table of int index by binary_integer;
      type name_table is table of char(20) index by binary_integer;
      procedure insert_data(p_num num_table,p_name name_table)
      as
      begin
        forall i in p_num.first..p_num.last
          insert into t values(p_num(i),trim(p_name(i)));
        commit;
      end;
    begin
       insert_data(:v_num,:v_object_name);
    end;
  end-exec;
 exec sql commit work release;
}
~
~
~
~
~
~
~
~
~
~
"htyansp.pc" [New] 30L, 829C written

编译为可执行文件

[oracle@db2server ~]$ proc parse=none htyansp.pc userid=htyansp/htyansp SQLCHECK=SEMANTICS

Pro*C/C++: Release 11.2.0.1.0 - Production on Sun Aug 19 01:12:09 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

System default option values taken from: /u01/app/oracle/product/db11gr2/precomp/admin/pcscfg.cfg

[oracle@db2server ~]$ gcc htyansp.c -o htyansp $ORACLE_HOME/lib/libclntsh.so
[oracle@db2server ~]$ ./htyansp
[oracle@db2server ~]$

执行结果如下：

 

SQL> select * from t;

        ID NAME
---------- --------------------

         0 A
         1 B
         2 C
         3 D
         4 E
         5 F
         6 G
         7 H
         8 I
         9 G

10 rows selected.