Pro*C動態SQL使用小例

環境：

WinXPSP2+VC6+Oracle10g

代碼：

// 定義符號常數<br />#define USERNAME "SCOTT"<br />#define PASSWORD "x"<br />#include <stdio.h></p><p>// 說明SQLCA和ORACA<br />EXEC SQL INCLUDE SQLCA;<br />EXEC SQL INCLUDE ORACA;</p><p>// 啟用ORACLE通訊區：ORACA=YES，使它能被使用<br />EXEC ORACLE OPTION (ORACA=YES);</p><p>// 說明SQL變數<br />EXEC SQL BEGIN DECLARE SECTION;<br />char* username=USERNAME;<br />char* password=PASSWORD;<br />VARCHAR sqlstmt[80];<br />int emp_number;<br />VARCHAR emp_name[15];<br />VARCHAR job[50],job1[50],job2[50];<br />float salary;<br />EXEC SQL END DECLARE SECTION;<br />main()<br />{<br />EXEC SQL WHENEVER SQLERROR GOTO sqlerror;</p><p>// 發生錯誤時，儲存SQL語句至ORACA<br />oraca.orastxtf=ORASTFERR;</p><p>// 登入到ORACLE<br />EXEC SQL CONNECT :username IDENTIFIED BY :password;<br />printf("/nConnect to ORACLE./n");</p><p>// 構造動態SQL語句<br />sqlstmt.len=sprintf(sqlstmt.arr,"INSERT INTO EMP(EMPNO,ENAME,JOB,SAL)VALUES(:V1,:V2,:V3,:V4)");</p><p>// 顯示SQL語句<br />puts(sqlstmt.arr);</p><p>// 用PREPARE語句分析當前的動態INSERT語句，語句名是S<br />EXEC SQL PREPARE S FROM :sqlstmt;</p><p>// 迴圈插表<br />for(;;)<br />{<br />printf("/nEnter employee number:");<br />scanf("%d",&emp_number);<br />if(emp_number==0)break;<br />printf("/nEnter employee name:");<br />scanf("%s",&emp_name.arr);<br />emp_name.len=strlen(emp_name.arr);<br />printf("/nEnter employee job:");<br />scanf("%s",&job.arr);<br />job.len=strlen(job.arr);<br />salary = 0; // With VC6, Missing this line will cause C Run-Time Error R6002.<br />printf("/nEnter salary:");<br />scanf("%f",&salary);<br />EXEC SQL EXECUTE S USING :emp_number,:emp_name,:job,:salary;<br />}</p><p>// 提交事務，退出ORACLE<br />EXEC SQL COMMIT RELEASE;<br />printf("/nHave a good day!/n");<br />exit(0);<br />sqlerror:<br />// 列印錯誤資訊<br />printf("/n%.*s/n",sqlca.sqlerrm.sqlerrml,sqlca.sqlerrm.sqlerrmc);</p><p>// 列印出錯SQL語句<br />printf("/n/"%.*s.../"/n",oraca.orastxt.orastxtl,oraca.orastxt.orastxtc);</p><p>// 列印出錯SQL語句所在行號及所在檔案名稱<br />printf("on line %d of %.*s/n/n",oraca.oraslnr,<br />oraca.orasfnm.orasfnml,oraca.orasfnm.orasfnmc);</p><p>// 復原事務，退出ORACLE<br />EXEC SQL WHENEVER SQLERROR CONTINUE;<br />EXEC SQL ROLLBACK RELEASE;<br />exit(1);<br />}