鑒於論壇上有許多人問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. (測試通過)
