zjhiphop2006的专栏

Where amazing happens,Where differences happens!!!

proc 编程杂项(推荐)

原文出处
http://www.oradb.net/proc/proc_003.htm
鉴于论坛上有许多人问PRO*C问题
所以把PRO*C编程方法帖出来


  1. 环境变量
  Ø 系统运行需要配置以下内容
  ORACLE_SID=ORA8
  NLS_LANG=American_America.zhs16cgb231280
  ORA_NLS33=/home/oracle/ocommon/nls/admin/data
  ORACLE_TERM=vt100
  SHLIB_PATH=/home/oracle/lib
  LD_LIBRARY_PATH=/home/oracle/lib:/usr/lib:.
  ORACLE_HOME=/home/oracle
  ORACLE_OWNER=oracle
  NLS_DATE_FORMAT='YYYY/MM/DD'
  Ø Oracle Shared Library: SHLIB_PATH $ORACLE_HOME/lib
  说明:Oracle Shared Library在安装时自动建立,如有必要重建此库,可以Oracle 用户身份执行一下命令
   cd $ORACLE_HOME/rdbms/lib
  make -f ins_rdbms.mk client_sharedlib
  Ø 日期格式的设置:有两种方法
  (1).通过在给定的SQL启动中设置 alter session set NLS_DATE_FORMAT
  (2).设置 INIT.ORA 中的 NLS_DATE_FORMAT 参数可设置缺省日期的格式
  Ø 预编译选项:设置 PROCFLAGS='mode=ansi,def_sqlcode=true,parse=full,lines=true'
  Ø 头文件:sqlca.h , oraca.h , sqlda.h


  2. 移植注意事项:
  Ø 对于 char 型数据的处理:
  Informix 中用双引号括字符串,而。。。Oracle 中字符串用单引号 ’ 括起来,如用双引号 ” 则会出错(在Proc 中编译时不报错,运行时出错)
  如果插入的字符串比字段定义的长度长,在 Informix 中会自动截掉多余的字符,而在 Oracle中会报错
  在Oracel 中要使用 string 类型要采用如下的声明方式
   char name[41];
   EXEC SQL VAR name IS STRING(41);
  Ø 头文件的使用:
  由于 Oracle 不使用 EXEC SQL DEFINE 定义宏,故要用到这些东西的地方,只有改成用常量或变量。

       也可以用 EXEC ORACLE DEFINE ...


  3. 相关命令
  Ø 查错用 oerr:命令格式 oerr ora err_num
  Ø 查找标示符相关的库文件: symfind symname
  
  
  4. proc 预编译设置
  预编译选项 设置方式 缺省值 值域 说明
  代码一致性 Mode=string Oracle ansi, iso, oracle
  允许使用SQLCODE Def_sqlcode=boolean No yes, no,true, false
  指定预编译源文件 Iname=string *none* *none*
  指定预编译头文件路径 Include=string OR include=(string, …) ( *none*
  指定系统头文件的路径 Sys_include=stringOR Sys_include=(string, …) ( *none*
  预编译宏定义 define=string
  预编译后的代码类型 Code=string Kr_c ansi_c, cpp,kr_c
  指定与字符串数组,字符串对应的类型 Char_map=string Charz charz,varchar2,charf,string
  指定连接数据库的字符串(username/password [@dbname]) Userid=string *none* *none*
  指定 C 编译器支持的字符集 Comp_charset=string Multi_byte multi_byte,single_byte
  数据库兼容模式(Oracle) Dbms=string Native v6, v6_char,v7, native, v8
  事物相关参数 Duration=string Transaction session, transaction (设置object 在cache中的时间)
  错误处理 Errors=boolean Yes yes, no,true, false (是否将错误信息发送到终端)
  信息处理标准 Fips=string None none, sql89, sql2, yes, no
  游标控制 Hold_cursor=boolean No yes, no,true, false (control holding of cursors in the cursor cache)
  控制游标cache中的游标释放 Release_cursor=boolean No yes, no,true, false (control release of cursors from cursor cache)
  Allow a NULL fetch without indicator variable Unsafe_null=boolean No yes, no,true, false
  是否在产生的code 中加入行号(#line) Lines=boolean No yes, no,true, false
  设置字符串长度 Maxliteral=number 1024 10-1024
  可以被cache的打开的最大游标数目 Maxopencursors=number 10 *none*
  语言支持 nls_char, nls_local
  是否使用 ORACA Oraca=boolean No yes, no,true, false
  控制是否解析 non-SQL CODE Parse=string Full full, partial,none
  Control flagging of select errors Select_error=boolean Yes yes, no, true, false
  预编译时对SQL代码的检查方式 Sqlcheck=string Syntax(语法) none, syntax,semantics(语义), full, limited(有限的)
  是否支持多线程 Threads=boolean No yes, no, true, false
  允许使用varchar 结构 Varchar=boolean No yes, no,true, false
  
  
  5. 数据类型

  Ø Oracle ProC 原始数据类型
  C Datatype or Pseudotype Description
  Char single character
  char[n] n-character array (string)
  Int Integer
  Short small integer
  long large integer
  float floating-point number (usually single precision)
  Double floating-point number (always double precision)
  VARCHAR[n] variable-length string
  
  Ø Oracle Internal Type ßà C Type ( ProC External Type
  ORACLE Internal Type C Type(External Type)
  VARCHAR2(Y)(Note 1) char
  CHAR(X)(Note 1) char[n]VARCHAR[n]IntShortLongFloatDouble
  NUMBER int
  NUMBER(P,S)(Note 2) ShortLongFloatDoubleCharChar[n]VARCHAR[n]
  DATE Char[n]VARCHAR[n]
  LONG Char[n]VARCHAR[n]
  RAW(X)(Note 1) Unsigned char[n]VARCHAR[n]
  LONG RAW Unsigned char[n]VARCHAR[n]
  ROWID Unsigned char[n]VARCHAR[n]
  MLSLABEL Unsigned char[n]VARCHAR[n]
  Notes:
  1. X ranges from 1 to 255. 1 is the default value. Y ranges from 1 to 4000.
  2. P ranges from 2 to 38. S ranges from -84 to 127.
  
  Ø Informix Data Type ßàESQL/C Data Type ßà C Type
  SQL Data Type ESQL/C Predefined Data Type C Language Type
  BYTE loc_t
  CHAR(n)CHARACTER(n) Fixchar array[n] orString array[n+1] char array[n + 1] or char *
  DATE Date long int
  DATETIME Datetime or dtime_t
  DECIMAL(m,n)DECNUMERICMONEY(m,n) Decimal or dec_t
  FLOATDOUBLE PRECISION Double
  INTEGERINT 4-byte integer
  INTERVAL Interval or intrvl_t
  MULTISET(e) Collection
  NCHAR(n) Fixchar array[n] orString array[n+1] char array[n + 1] or char *
  NVARCHAR(m) Varchar[m+1] orString array[m+1] char array[m+1]
  SERIAL 4-byte integer
  SMALLFLOATREAL Float
  SMALLINT 2-byte integer
  TEXT loc_t
  VARCHAR(m,x) Varchar[m+1] orString array[m+1] char array[m+1]
  BLOB ifx_lo_t
  BOOLEAN Boolean
  CLOB ifx_lo_t
  INT8 int8 or ifx_int8_t 8-byte integer
  LIST(e) Collection
  LVARCHAR Lvarchar Char
  Opaque data type Lvarchar, fixed binary, orvar binary
  ROW(...) Row
  SERIAL8 int8 or ifx_int8_t 8-byte integer
  SET(e) Collection
  
  
  Ø Informix internal Type ßà C Type(External type Informixßà Oracle)ßà Oracle Intal Type
  Informix Internal Type C Type(External Type) ORACLE Internal Type
  Char(x) Char(x) Char(x), varchar2(x)
  Integer Long NUMBER(9)
  Smallint Short Int NUMBER(6)
  Decimal Int, float, double NUMBER
  Float Float NUMBER(18,4)
  Serial Long NUMBER(11)
  Date Char(11)àDATE DATE (/ NUMBER(10)
  Money
  DateTime Char(20) DATE
  Interval
  Varchar(1..255) Char(x) Char(x)
  Text
  Byte
  
  
  参考资料:Oracle 8.0.4 完全电子文档(可从 站点technet.oracle.com.cn得到)
  Pro C/C++ Precompiler Programmer’s Guide
   Page 354:Transaction
   Page 362:Commit work in Fetch
   Page 218: you cannot FETCH from a FORUPDATE cursor after a COMMIT. If you try to do this, Oracle returns a 1002 error code.
   Page 357:When MODE=ORACLE, explicit cursors not referenced in a CURRENT OF clause remain open across ROLLBACKs.
   Page 601:when MODE=ANSI, you must CLOSE a cursor before reOPENing it.
   Page 356:Rolling back to a savepoint erases any savepoints marked after that savepoint. The savepoint to which you roll back, however, is not erased. For example, if you mark five savepoints, then roll back to the third, only the fourth and fifth are erased.
   If you give two savepoints the same name, the earlier savepoint is erased. A COMMIT or ROLLBACK statement erases all savepoints.
  
   Page 356: sqlca.sqlerrd[ 2 ]
   Page 436: sqlca.sqlerrd[ 2 ]
  For INSERT, UPDATE, DELETE, and SELECT INTO statements, sqlca.sqlerrd[2] records the number of rows processed. For FETCH statements, it records the cumulative sum of rows processed. (测试通过)

阅读更多
个人分类: 数据库技术
想对作者说点什么? 我来说一句

ProC编程入门

2011年11月14日 2.76MB 下载

OracleProC编程系列-2

2010年05月09日 7.95MB 下载

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭