ORACLE數(shù)據(jù)庫(kù)PROC編程經(jīng)驗(yàn)介紹
--------------------------------------------------------------------------------
 
  2002-3-1  　作者：風(fēng)沙  【類別:編程語(yǔ)言及工具】 
 
 
 PROC是ORACLE數(shù)據(jù)庫(kù)提供的編程接口之一，其應(yīng)用十分的廣泛，本文通過(guò)一個(gè)具體的例子,，介紹PROC編程的一些經(jīng)驗(yàn)及應(yīng)注意的地方,。

例子程序：
#include
#include
#include
#include
#include

EXEC SQL INCLUDE sqlca;
/*RELEASE_CURSOR=YES 使PROC 在執(zhí)行完后釋放與嵌入SQL有關(guān)資源*/
EXEC ORACLE OPTION (RELEASE_CURSOR = YES);

EXEC SQL BEGIN DECLARE SECTION;
varchar vc_user[20];
long al_empno=0;
char ac_ename[11]="";
char ac_hiredate[20]="";
double af_sal=0;

EXEC SQL VAR ac_ename IS STRING(11);
EXEC SQL VAR ac_hiredate IS STRING(20);

EXEC SQL END DECLARE SECTION;

/*錯(cuò)誤處理函數(shù)*/
void sql_error(char *msg)
{
printf("\n%s,%ld,%s\n", msg,sqlca.sqlcode,(char *)sqlca.sqlerrm.sqlerrmc);
EXEC SQL ROLLBACK RELEASE;
exit(-1);
}

main()
{
EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE ERROR: ");

/*連接數(shù)據(jù)庫(kù)*/
strcpy(vc_user.arr,"scott/tiger@DEMO");
vc_user.len=16;
exec sql connect :vc_user;

EXEC SQL DECLARE cur_emp CURSOR FOR
SELECT EMPNO, ENAME,to_char(HIREDATE,‘yyyy/mm/dd hh24:mi:ss‘),SAL FROM EMP;

EXEC SQL OPEN cur_emp;
while(1)
{
al_empno=0;
strcpy(ac_ename,"");
strcpy(ac_hiredate,"");
af_sal=0;
EXEC SQL FETCH cur_emp INTO :al_empno, :ac_ename:ename_ind, :ac_hiredate:hiredate_ind, :af_sal:sal_ind;
if( sqlca.sqlcode == 1403)
{
break;
}
printf("empno=%ld,ename=%s,hiredate=%s,sal=%lf\n",al_empno,ac_ename,ac_hiredate,af_sal);
}
EXEC SQL CLOSE cur_emp;
EXEC SQL ROLLBACK WORK RELEASE;
}

1,、宿主變量的聲明
在PROC中,在SQL語(yǔ)句中用到的變量稱為宿主變量,。他們應(yīng)在EXEC SQL BEGIN DECLARE SECTION;與EXEC SQL EDN DECLARE SECTION;
之間聲明,，如上面所示.在聲明宿主變量時(shí)應(yīng)注意以下幾點(diǎn)：
(1) 在數(shù)據(jù)庫(kù)表中定義為VARCHAR2,，VARCHAR,，CHAR的字段，在PROC中可聲明為CHAR,，但長(zhǎng)度應(yīng)為它們?cè)诒碇卸x的長(zhǎng)度加1,，因?yàn)镻ROC中
CHAR型變量用\0做結(jié)尾。

如：ENAME在表中的定義為ename varchar2(10),，在PROC中可定義為：
EXEC SQL BEGIN DECLARE SECTION;
char ename[11];
EXEC SQL END DECLARE SECTION;
常見(jiàn)錯(cuò)誤說(shuō)明:
如果插入的字符串長(zhǎng)度大于10,，如：EXEC SQL INSERT INTO EMP(ENAME) VALUES(‘12345678901‘);會(huì)出現(xiàn)以下錯(cuò)誤:
error:ORA-01480: STR 賦值變量缺少空后綴。

如果定義為:
EXEC SQL BEGIN DECLARE SECTION;
char ename[15];
EXEC SQL END DECLARE SECTION;

當(dāng)插入的字符串長(zhǎng)度大于10,，小于15時(shí),如：EXEC SQL INSERT INTO EMP(ENAME) VALUES(‘12345678901‘);會(huì)出現(xiàn)以下錯(cuò)誤:
error:ORA-01401: 插入的值對(duì)于列過(guò)大,。
當(dāng)插入的字符串長(zhǎng)度大于15，如：EXEC SQL INSERT INTO EMP(ENAME) VALUES(‘12345678901234‘);會(huì)出現(xiàn)以下錯(cuò)誤:
error:ORA-01401:STR 賦值變量缺少空后綴,。

(2) 從SQL語(yǔ)句中取字段的值到宿主變量中時(shí),，PROC不會(huì)自動(dòng)給宿主變量去掉右空格。而是以在DECLARE SECTION 中定義的長(zhǎng)度為準(zhǔn)(與 表中定義的無(wú)關(guān))不足補(bǔ)右空格.如果不注意這一點(diǎn),，在PROC中進(jìn)行字符串操作時(shí)（如比較相等）會(huì)出錯(cuò),。如:
EXEC SQL BEGIN DECLARE SECTION;
char ename[10];
EXEC SQL END DECLARE SECTION;
如果ENAME在表中的值為‘a(chǎn)bc‘,則取出的值為‘a(chǎn)bc ‘;

可用語(yǔ)句EXEC SQL VAR重定義CHAR型變量。這樣宿主變量會(huì)自動(dòng)去掉右空格,。如下:
EXEC SQL BEGIN DECLARE SECTION;
char ename[11];
EXEC SQL VAR ac_ename IS STRING(11);
EXEC SQL END DECLARE SECTION;
如果ENAME在表中的值為‘a(chǎn)bc‘,則取出的值為‘a(chǎn)bc‘;

(3) 對(duì)浮點(diǎn)型的變量,，為保證精度,，最好是聲明成DOUBLE型的.因?yàn)镈OUBLE型的精度比FLOAT型高很多.
(4) 整型可聲明為L(zhǎng)ONG型(對(duì)較長(zhǎng)的整型,而且所用的平臺(tái)支持的話,如在SUN平臺(tái)上,可聲明為L(zhǎng)ONG LONG型).
(5) DATE型的處理：DATE型一般聲明為CHAR(20)。
往表中插入DATE型數(shù)據(jù)時(shí),一般用TO_DATE()函數(shù)進(jìn)行類型轉(zhuǎn)換,取出值時(shí)一般用TO_CHAR()函數(shù)進(jìn)行類型轉(zhuǎn)換.
EXEC SQL select to_char(hiredate,‘yyyy/mm/dd hh24:mi:ss‘) into :ac_hire_date from EMP where empno=1234;
EXEC SQL insert into EMP(EMPNO,HIREDATE) values(123,to_date(:ac_hiredate,‘yyyy/mm/dd hh24:mi:ss‘);

2,、宿主變量的作用范圍
如果宿主變量在所有的函數(shù)之外聲明,，則他們是全局變量。在使用之前要注意把變量的值初始化,，宿主變量也可以在某個(gè)函數(shù)的內(nèi)部定義,。 這時(shí)他們是局部變量。一般都習(xí)慣把宿主變量聲明為全局變量,。

3,、數(shù)據(jù)庫(kù)的連接與斷開(kāi)
數(shù)據(jù)庫(kù)的連接有以下兩種方法：
(1)
strcpy(vc_user.arr,"scott/tiger");
vc_user.len=11;
exec sql connect :vc_user;
(2)
strcpy(user,"scott");
strcpy(pass,"tiger");
exec sql connect :user identified by :pass;
注意：在有些平臺(tái)上兩種都可以,在有些平臺(tái)上只能用第一種方法.
在PROC程序中，要記住用EXEC SQL ROLLBACK WORK RELEASE;斷開(kāi)與數(shù)據(jù)庫(kù)的連接,并釋放相關(guān)的數(shù)據(jù)庫(kù)資源,。

4,、PROC中的NULL值的處理
如果某一字段取出的值是NULL,會(huì)報(bào):sqlcode=-1405, sqlerr=ORA-01405: 讀取的列值為 NULL
并且相應(yīng)的宿主變量的值不會(huì)被改變,為執(zhí)行該SQL語(yǔ)句之前的值. 常用的處理NULL值的方法有：
(1)采用指示器變量,此時(shí)不會(huì)有-1405錯(cuò)誤,當(dāng)必須是所以為NULL的字段都有相應(yīng)的指示器變量,如果某一字段沒(méi)有指示器變量,但取出的值
為NULL值,則仍然會(huì)有-1405錯(cuò)誤.當(dāng)取出的值是NULL時(shí)，相應(yīng)的指示器變量變量為-1,，可根據(jù)指示器變量的值做響應(yīng)的處理,。
(2)如果字段較多,可取字段到一個(gè)結(jié)構(gòu)體中及與該結(jié)構(gòu)體對(duì)應(yīng)的指示器結(jié)構(gòu)體中.如上面的例子中可定義結(jié)構(gòu)體：
struct str_emp{
long al_empno;
char ac_ename;
char ac_hiredate;
double af_sal;
};
struct str_emp_ind{
long al_empno;
char ac_ename;
char ac_hiredate;
double af_sal;
};

struct str_emp str_emp;
strcut str_emp_ind str_emp_ind;
在取之前可用memset(&str_emp,0,sizeof(str_emp)).清空該結(jié)構(gòu)體,這樣如果是字符型的NULL,會(huì)為"",整型的NULL會(huì)為0,
浮點(diǎn)型的會(huì)為0.00。此時(shí)不會(huì)有-1405錯(cuò)誤,。
(3)也可采用NVL()函數(shù):舉例如下：
EXEC SQL DECLARE authors CURSOR FOR
SELECT EMPNO, NVL(ENAME,chr(0)),nvl(to_char(HIREDATE,‘yyyy/mm/dd hh24:mi:ss‘),chr(0)),NVL(SAL,0) FROM EMP;
這樣也不會(huì)有-1405錯(cuò)誤不,當(dāng)取出的值是NULL時(shí),自動(dòng)用NVL()中指定的值代替.
CHR(0)也可直接用‘‘代替,如下:
SELECT EMPNO, NVL(ENAME,‘‘),nvl(to_char(HIREDATE,‘yyyy/mm/dd hh24:mi:ss‘),‘‘),NVL(SAL,0) FROM EMP;

5,、PROC中的錯(cuò)誤的處理
所有的SQL語(yǔ)句都有可能出錯(cuò).所以都要加以判斷,但每個(gè)SQL語(yǔ)句后都加錯(cuò)誤判斷,太麻煩,可用一個(gè)函數(shù)如sql_error()來(lái)進(jìn)行錯(cuò)誤處理,
方法:
(1)定義ql_error()函數(shù)。
(2)在開(kāi)頭加上EXEC SQL WHENEVER SQLERROR DO sql_error();這樣當(dāng)發(fā)生sqlca.sqlcode <0 的錯(cuò)誤時(shí),程序自動(dòng)轉(zhuǎn)到sql_error()中執(zhí)行. 注意:對(duì)sqlca.sqlcode >0的錯(cuò)誤如 sqlca.sqlcode =1403 是不會(huì)轉(zhuǎn)到sql_error()中執(zhí)行的.
另外:在UNIX下,，可以用OERR 來(lái)查找錯(cuò)誤的描述,。如： ora ORA -1405 查找錯(cuò)誤號(hào)為-1405的描述.

6、PROC中調(diào)用存儲(chǔ)過(guò)程的方法
要把存儲(chǔ)過(guò)程放在EXEC SQL EXECUTE 和 END-EXEC;之間,，如下所示：
其中：al_empno,ac_ename 為輸入?yún)?shù),l_return,l_errno,c_errtext 為輸出參數(shù),。
al_empno=8888;
strcpy(ac_ename,"ABCD");
EXEC SQL EXECUTE
BEGIN
up_db_emp(:al_empno,:ac_ename,:l_return,:l_errno,:c_errtext);
END;
END-EXEC;
if (l_return != 0)
{
printf("調(diào)用UP_PB_EMP存儲(chǔ)過(guò)程出錯(cuò),errno=%ld,errtext=%s\n",l_errno,c_errtext);
}

7、PROC的命令行選項(xiàng)：PROC編譯器有很多的命令行選項(xiàng),，在命令行下直接不帶參數(shù)運(yùn)行PROC,，會(huì)列出所有的命令行選項(xiàng)來(lái)，并有說(shuō)明,。
(1)儲(chǔ)存過(guò)程:編譯儲(chǔ)存過(guò)程是要帶上用戶名及密碼
proc USERID=scott/tiger sqlcheck=SEMANTICS ireclen=512 iname=test.cpp
(2)PARSE=NONE 對(duì)非SQL代碼不進(jìn)行語(yǔ)法分析,，默認(rèn)對(duì)非SQL代碼也進(jìn)行語(yǔ)法分析.
在RED HAD6.3上的ORACLE8.1.5中用PROC時(shí),會(huì)提示:/USR/INCLUDE/STDIO.H 及其他的.H文件中有錯(cuò). 可把PARSE=NONE加上,就好了.

8、注意加上：EXEC ORACLE OPTION (RELEASE_CURSOR = YES);
RELEASE_CURSOR=YES 使PROC 在執(zhí)行完后釋放與嵌入SQL有關(guān)資源,，保證在該P(yáng)ROC程序執(zhí)行完后,，ORACLE不會(huì)鎖住數(shù)據(jù)庫(kù)資源，如鎖表等,。
如果在PROC中用到ORACA,，還要在程序頭加上：
EXEC ORACLE OPTION (ORACA=YES);

9、PROC中的類型轉(zhuǎn)換
一,、在C語(yǔ)言中:
(1)字符型到整型可用ATOI() ATOL(),SSCANF()
(2)整型,浮點(diǎn)型到字符型,可用SPRINTF()
(3)字符型到浮點(diǎn)型用ATOF()不行,最好用SSCANF(),舉例如下:

EXEC SQL BEGIN DECLARE SECTION;
double d_demo;
float f_demo;
char ac_text[20]="222";
EXEC SQL END DECLARE SECTION;

(1)sscanf(ac_text, "%f", &d_demo);
printf("ac_text=%s,d_demo=%f\n",ac_text,d_demo);

(2)sscanf(ac_text, "%lf", &d_demo);
printf("ac_text=%s,d_demo=%f\n",ac_text,d_demo);

(3)sscanf(ac_text, "%f", &d_demo);
printf("ac_text=%s,d_demo=%lf\n",ac_text,d_demo);

(4)sscanf(ac_text, "%lf", &d_demo);
printf("ac_text=%s,d_demo=%lf\n",ac_text,d_demo);

printf("*******************\n");
(5)sscanf(ac_text, "%f", &f_demo);
printf("ac_text=%s,f_demo=%f\n",ac_text,f_demo);

(6)sscanf(ac_text, "%lf", &f_demo);
printf("ac_text=%s,f_demo=%f\n",ac_text,f_demo);

(7)sscanf(ac_text, "%f", &f_demo);
printf("ac_text=%s,f_demo=%lf\n",ac_text,f_demo);

(8)sscanf(ac_text, "%lf", &f_demo);
printf("ac_text=%s,f_demo=%lf\n",ac_text,f_demo);

輸出的結(jié)果:
ac_text=222.00,d_demo=0.000000
ac_text=222.00,d_demo=222.000000
ac_text=222.00,d_demo=222.000032
ac_text=222.00,d_demo=222.000000
*******************
ac_text=222.00,f_demo=222.000000
ac_text=222.00,f_demo=0.000000
ac_text=222.00,f_demo=222.000000
ac_text=222.00,f_demo=0.000000
d_demo=atof(ac_text);
printf("ac_text=%s,atof(ac_text)=%f\n",ac_text,d_demo);

d_demo=atof(ac_text);
printf("ac_text=%s,atof(ac_text)=%lf\n",ac_text,d_demo);

f_demo=atof(ac_text);
printf("ac_text=%s,atof(ac_text)=%f\n",ac_text,f_demo);

f_demo=atof(ac_text);
printf("ac_text=%s,atof(ac_text)=%lf\n",ac_text,f_demo);

輸出的結(jié)果:
ac_text=222.00,atof(ac_text)=1243288.000000
ac_text=222.00,atof(ac_text)=1243288.000000
ac_text=222.00,atof(ac_text)=1243288.000000
ac_text=222.00,atof(ac_text)=1243288.000000

從上面的結(jié)果可見(jiàn):
DOUBLE型應(yīng)采用sscanf(ac_app_capcity, "%lf", &d_app); 打印用"%lf","%f" 都可以. (2),(4)正確
FLOAT型應(yīng)采用sscanf(ac_app_capcity, "%f", &d_app); 打印用"%lf","%f" 都可以. (5),(7)正確
采用ATOF()轉(zhuǎn)換的結(jié)果都是錯(cuò)的,所以不要用它,。

二,、寫表或從表中取數(shù)據(jù)時(shí):
(1)字符型與整型之間可不用轉(zhuǎn)換,采用默認(rèn)方式。
(2)字符型與浮點(diǎn)型之間可不用轉(zhuǎn)換,采用默認(rèn)方式,。
(3)日期型與字符型之間可用TO_CHAR(),TO_DATE(),。

10、PROC中的4種動(dòng)態(tài)SQL簡(jiǎn)介
(1)動(dòng)態(tài)SQL1: 不能是查詢(SELECT)語(yǔ)句,并且沒(méi)有宿主變量.
用法:拼一串動(dòng)態(tài)SQL語(yǔ)句,并用EXECUTE IMMEDIATE執(zhí)行,如:
EXEC SQL EXECUTE IMMEDIATE "CREATE TABLE dyn1 (col1 VARCHAR2(4))";

(2)動(dòng)態(tài)SQL2: 不能是查詢(SELECT)語(yǔ)句,并且輸入的宿主變量數(shù)目是知道的,
用法:拼一串動(dòng)態(tài)SQL語(yǔ)句,用PREPARE,EXECUTE語(yǔ)句執(zhí)行.
strcpy(c_sql, "DELETE FROM EMP WHERE EMPNO = :?");
EXEC SQL PREPARE sql_stmt FROM :c_sql;
EXEC SQL EXECUTE sql_stmt USING :emp_number;

(3)動(dòng)態(tài)SQL3: 用于創(chuàng)建動(dòng)態(tài)查詢, 并且要查詢的字段及輸入的宿主變量數(shù)目是知道的
用法: 拼一串動(dòng)態(tài)SQL語(yǔ)句,用PREPARE分析該語(yǔ)句,并要定義一個(gè)CURSOR進(jìn)行取值
如:如要查詢的數(shù)據(jù)按一年12月放到12張表中,。表名為user_fee_1mon, user_fee_2mon,....可采用動(dòng)態(tài)SQL3來(lái)進(jìn)行查詢
strcpy(c_sql,"select c_user_id,c_user_name,to_char(t_date,‘yyyy/mm/dd hh:mi:ss‘),n_fee\n");
strcat(c_sql,"from USER_FEE_");
strcat(c_sql,ac_mon);
strcat(c_sql," \n where c_user_id = :v1");

EXEC SQL PREPARE s FROM :c_sql;

EXEC SQL DECLARE cur_user_fee CURSOR FOR s;

EXEC SQL OPEN cur_user_fee USING :ac_user_id;

while(1)
{

EXEC SQL FETCH cur_user_fee into :c_user_id,:c_user_name,:c_date,:n_fee);

if (sqlca.sqlcode < 0)
{
/*FETCH CURSOR失敗*/
printf("fetch cursor cur_user_fee fail,sqlcode=%ld,sqlserr=%s",sqlca.sqlcode,sqlca.sqlerrm.sqlerrmc);
}
if( sqlca.sqlcode == SQLNOTFOUND)
{
break;
}
}
EXEC SQL CLOSE cur_user_fee;

(4)動(dòng)態(tài)SQL4:要處理的字段及輸入的宿主變量數(shù)目和主變量的類型事先是不知道的,如：
INSERT INTO EMP () VALUES ()
是最復(fù)雜的動(dòng)態(tài)SQL,很少用,在此不做介紹,。

11、SQLCA：SQL是ORACLE的一個(gè)結(jié)構(gòu)體,，它的域用于最近的一條SQL語(yǔ)句執(zhí)行后的一些信息,，如錯(cuò)誤號(hào)，錯(cuò)誤描述,，警告,，狀態(tài)等。常用的
域介紹如下:
SQLCA.sqlcode:錯(cuò)誤號(hào),=0正確,=1403沒(méi)取到數(shù)據(jù)
SQLCA.sqlserrm.sqlerrmc:錯(cuò)誤描述
SQLCA.sqlerrd[3]:最近的一條SQL語(yǔ)句所處理的行數(shù),如果該語(yǔ)句處理失敗,則它的值是不定的,如果錯(cuò)誤在一個(gè)CURSOR操作中發(fā)生,則
它的值指已成功處理的行數(shù).在DELETE,UPDATE中,它不包含因外鍵約束而刪除,更新的那些行,
DELETE FROM EMP WHERE DEPT=‘SALE‘;
在表EMP中刪除20行,但如果表EMP與表ADDRESS有外鍵約束,導(dǎo)致表ADDRESS也被刪除20行,則SQLCA.sqlerrd[3]=20,而不是40,。