/*****************************************************************************************/
/*      To compile a C program using the Pro*C precompiler                               */
/*1、	  Get the demo_proc.mk from the Oracle proc/demo directory.                        */
/*	    /pkgs2/oracle8.0.4/app/oracle/product/8.0.4/precomp/demo/proc                    */
/*2、	  Write a Pro*C program and save it with the extension "pc".                       */
/*3、	  Run the demo_proc.mk makefile to precompile and compile the program.             */ 
/*		  make -f demo_proc.mk OBJS=fn.o EXE=fn build                                      */
/*		  (fn is the file name of your Pro*C program)                                      */
/*4、	  Execute the program.                                                             */
/*****************************************************************************************/
/* NOTE：There should be one like this for C++ programs using cppbuild instead of build. */
/*****************************************************************************************/
/****************** TIHS PROGRAME SHOWS A SELECT IMBEDED IN A C PROGRAM ******************/
/*****************************************************************************************/
#include <stdio.h>
EXEC SQL BEGIN DECLARE SECTION;                                                  //主變量聲明開(kāi)始
     VARCHAR uid[20];                                                            //USER ID
     VARCHAR pwd[20];                                                            //USER PASSWORD
     char    sid[9];    
     char    sname[20]; 
EXEC SQL END DECLARE SECTION;                                                    //主變量生命結(jié)束
EXEC SQL INCLUDE SQLCA;                                                          //定義SQL通信區(qū)域
int main()                                                                       //主函數(shù)入口
{
	/******** LOG INTO ORACLE ********/
  strcpy(uid.arr, "yourcli");                                                    //取VARCHAR.arr uid存放USERID
  uid.len=strlen(uid.arr);                                                       //計(jì)算USERID串的長(zhǎng)度
  strcpy(pwd.arr, "yourpassword");                                               //pwd存放USER PASSWORD
  pwd.len=strlen(pwd.arr);                                                       //計(jì)算PASSWORD串的長(zhǎng)度
  EXEC SQL WHENEVER SQLERROR GOTO errprt;                                        //出錯(cuò)處理 SQL
  EXEC SQL CONNECT :uid IDENTIFIED BY :pwd;                                      //連接數(shù)據(jù)庫(kù)，使用USERID&USERPASSWORD
  if(sqlca.sqlcode == 0)                                                         //連接數(shù)據(jù)庫(kù)成功
  {
  	printf("Connected to ORACLE user:%s\n", uid.arr);                            //顯示當(dāng)前用戶USERID
  }
  else                                                                           //連接數(shù)據(jù)庫(kù)失敗
  {
    printf("Error occured. sqlcode = %d \n message = %s\n",sqlca.sqlcode, sqlca.sqlerrm.sqlerrmc);
    exit(0);                                                                     //顯示錯(cuò)誤的USER信息，并退出
  }
  /******** OPEN CURSOR ASSOCIATED WITH QUERY ********/
  EXEC SQL DECLARE C1 CURSOR for SELECT STU_ID, STU_NAME FROM STUDENT;           //定義查詢游標(biāo)
  EXEC SQL OPEN C1;                                                              //打開(kāi)游標(biāo)
  EXEC SQL WHENEVER NOT FOUND GOTO finish;                                       //如果結(jié)果集為空，跳轉(zhuǎn)到finish
  for(;;)                                                                        //取結(jié)果，每次一條. 等同while(1)
  {
    EXEC SQL FETCH C1 INTO :sid, :sname;                                         //INTO中使用主變量存放當(dāng)前結(jié)果
    printf("%-9s  %-20s\n", sid, sname);                                         //顯示當(dāng)前取得的一條結(jié)果信息
  }
  /******** WHEN FINISHED，CLOSE CURSOR，COMMIT AND GET OUT ********/
  finish:                                                                        //結(jié)果為空時(shí)，執(zhí)行下列操作
  EXEC SQL CLOSE C1;                                                             //關(guān)閉以已經(jīng)打開(kāi)的游標(biāo)
  EXEC SQL WHENEVER SQLERROR CONTINUE;                                           //SQL錯(cuò)誤，跳轉(zhuǎn)到ERPRT
  EXEC SQL COMMIT WORK RELEASE;                                                  //提交SQL對(duì)數(shù)據(jù)庫(kù)做的修改，釋放SQL資源
  exit(0);                                                                       //getout，按正常狀態(tài)，成功退出
  /******** WHEN AN ERROR OCCURS，ROLLBACK AND GET OUT ********/
  errprt:                                                                        //出錯(cuò)時(shí)執(zhí)行的語(yǔ)句
  printf("\n %s \n", sqlca.sqlerrm.sqlerrmc);                                    //顯示SQLCA中的錯(cuò)誤信息
  EXEC SQL ROLLBACK WORK RELEASE;                                                //回滾SQL數(shù)據(jù)庫(kù)到操作之前的正確狀態(tài)
  exit(1);                                                                       //按錯(cuò)誤狀態(tài)退出
}
/*****************************************************************************************/
/*  EXEC SQL declare <cursor-name> for <select-stmt>;                                    */
/*  Declares a cursor associated with an SQL query                                       */
/*****************************************************************************************/
/*  EXEC SQL open <cursor-name>;                                                         */
/*****************************************************************************************/
/*  EXEC SQL fetch <cursor-name> into <host-variable-list>;                              */
/*  Retrieves the next row into the host variables                                       */
/*****************************************************************************************/
/*  EXEC SQL close <cursor-name>;                                                        */
/*****************************************************************************************/
/*  EXEC SQL begin declare section;                                                      */
/*  Declaration of host variables.  Host variables are used normally                     */  
/*  in C statements.  In SQL statements they are preceded by a ‘:’.                      */
/*****************************************************************************************/
/*  EXEC SQL end declare section;                                                        */
/*****************************************************************************************/
/*  EXEC SQL commit [work] [release];                                                    */
/*  EXEC SQL rollback [work] [release];                                                  */
/*  commit – makes database changes permanent.                                           */
/*  rollback – reverses any changes made to the database.                                */
/*  release – disconnects from the database.                                             */
/*****************************************************************************************/
/*  EXEC SQL whenever <condition>       <action>;                                        */
/*                    conditions				actions                                          */
/*                    sqlerror				  continue                                         */
/*                    sqlwarning				goto <label>                                     */
/*                    not found				  stop                                             */
/*                    do function | break | return                                       */
/*  NOTE： Embeds an ‘if’ after each ‘EXEC SQL’ statement to check for                   */
/*         the condition and take the action in effect.                                  */
/*         <condition> <action> stays in effect until overridden                         */
/*****************************************************************************************/