procsql2.c

采用proc连接oracle数据库,应用proc连接3步曲,供初者参考使用

/*****************************************************************************************/
/*      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;                                                  //主变量声明开始
     VARCHAR uid[20];                                                            //USER ID
     VARCHAR pwd[20];                                                            //USER PASSWORD
     char    sid[9];    
     char    sname[20]; 
EXEC SQL END DECLARE SECTION;                                                    //主变量生命结束
EXEC SQL INCLUDE SQLCA;                                                          //定义SQL通信区域
int main()                                                                       //主函数入口
{
	/******** LOG INTO ORACLE ********/
  strcpy(uid.arr, "yourcli");                                                    //取VARCHAR.arr uid存放USERID
  uid.len=strlen(uid.arr);                                                       //计算USERID串的长度
  strcpy(pwd.arr, "yourpassword");                                               //pwd存放USER PASSWORD
  pwd.len=strlen(pwd.arr);                                                       //计算PASSWORD串的长度
  EXEC SQL WHENEVER SQLERROR GOTO errprt;                                        //出错处理 SQL
  EXEC SQL CONNECT :uid IDENTIFIED BY :pwd;                                      //连接数据库，使用USERID&USERPASSWORD
  if(sqlca.sqlcode == 0)                                                         //连接数据库成功
  {
  	printf("Connected to ORACLE user:%s\n", uid.arr);                            //显示当前用户USERID
  }
  else                                                                           //连接数据库失败
  {
    printf("Error occured. sqlcode = %d \n message = %s\n",sqlca.sqlcode, sqlca.sqlerrm.sqlerrmc);
    exit(0);                                                                     //显示错误的USER信息，并退出
  }
  /******** OPEN CURSOR ASSOCIATED WITH QUERY ********/
  EXEC SQL DECLARE C1 CURSOR for SELECT STU_ID, STU_NAME FROM STUDENT;           //定义查询游标
  EXEC SQL OPEN C1;                                                              //打开游标
  EXEC SQL WHENEVER NOT FOUND GOTO finish;                                       //如果结果集为空，跳转到finish
  for(;;)                                                                        //取结果，每次一条. 等同while(1)
  {
    EXEC SQL FETCH C1 INTO :sid, :sname;                                         //INTO中使用主变量存放当前结果
    printf("%-9s  %-20s\n", sid, sname);                                         //显示当前取得的一条结果信息
  }
  /******** WHEN FINISHED，CLOSE CURSOR，COMMIT AND GET OUT ********/
  finish:                                                                        //结果为空时，执行下列操作
  EXEC SQL CLOSE C1;                                                             //关闭以已经打开的游标
  EXEC SQL WHENEVER SQLERROR CONTINUE;                                           //SQL错误，跳转到ERPRT
  EXEC SQL COMMIT WORK RELEASE;                                                  //提交SQL对数据库做的修改，释放SQL资源
  exit(0);                                                                       //getout，按正常状态，成功退出
  /******** WHEN AN ERROR OCCURS，ROLLBACK AND GET OUT ********/
  errprt:                                                                        //出错时执行的语句
  printf("\n %s \n", sqlca.sqlerrm.sqlerrmc);                                    //显示SQLCA中的错误信息
  EXEC SQL ROLLBACK WORK RELEASE;                                                //回滚SQL数据库到操作之前的正确状态
  exit(1);                                                                       //按错误状态退出
}
/*****************************************************************************************/
/*  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                         */
/*****************************************************************************************/