exam0.c

VC+ORACLE的开发例子。 PRO*C的例子

/* Result Sets Interface */
#ifndef SQL_CRSR
#  define SQL_CRSR
  struct sql_cursor
  {
    unsigned int curocn;
    void *ptr1;
    void *ptr2;
    unsigned int magic;
  };
  typedef struct sql_cursor sql_cursor;
  typedef struct sql_cursor SQL_CURSOR;
#endif /* SQL_CRSR */

/* Thread Safety */
typedef void * sql_context;
typedef void * SQL_CONTEXT;

/* Object support */
struct sqltvn
{
  unsigned char *tvnvsn; 
  unsigned short tvnvsnl; 
  unsigned char *tvnnm;
  unsigned short tvnnml; 
  unsigned char *tvnsnm;
  unsigned short tvnsnml;
};
typedef struct sqltvn sqltvn;

struct sqladts
{
  unsigned int adtvsn; 
  unsigned short adtmode; 
  unsigned short adtnum;  
  sqltvn adttvn[1];       
};
typedef struct sqladts sqladts;

static struct sqladts sqladt = {
  1,1,0,
};

/* Binding to PL/SQL Records */
struct sqltdss
{
  unsigned int tdsvsn; 
  unsigned short tdsnum; 
  unsigned char *tdsval[1]; 
};
typedef struct sqltdss sqltdss;
static struct sqltdss sqltds =
{
  1,
  0,
};

/* File name & Package Name */
struct sqlcxp
{
  unsigned short fillen;
           char  filnam[21];
};
static const struct sqlcxp sqlfpn =
{
    20,
    "D:\\procdemo\\exam0.pc"
};


static unsigned int sqlctx = 73268875;


static struct sqlexd {
   unsigned int   sqlvsn;
   unsigned int   arrsiz;
   unsigned int   iters;
   unsigned int   offset;
   unsigned short selerr;
   unsigned short sqlety;
   unsigned int   occurs;
      const short *cud;
   unsigned char  *sqlest;
      const char  *stmt;
   sqladts *sqladtp;
   sqltdss *sqltdsp;
            void  **sqphsv;
   unsigned int   *sqphsl;
            int   *sqphss;
            void  **sqpind;
            int   *sqpins;
   unsigned int   *sqparm;
   unsigned int   **sqparc;
   unsigned short  *sqpadto;
   unsigned short  *sqptdso;
   unsigned int   sqlcmax;
   unsigned int   sqlcmin;
   unsigned int   sqlcincr;
   unsigned int   sqlctimeout;
   unsigned int   sqlcnowait;
              int   sqfoff;
   unsigned int   sqcmod;
   unsigned int   sqfmod;
            void  *sqhstv[4];
   unsigned int   sqhstl[4];
            int   sqhsts[4];
            void  *sqindv[4];
            int   sqinds[4];
   unsigned int   sqharm[4];
   unsigned int   *sqharc[4];
   unsigned short  sqadto[4];
   unsigned short  sqtdso[4];
} sqlstm = {12,4};

/* SQLLIB Prototypes */
extern void sqlcxt (void **, unsigned int *,
                    struct sqlexd *, const struct sqlcxp *);
extern void sqlcx2t(void **, unsigned int *,
                    struct sqlexd *, const struct sqlcxp *);
extern void sqlbuft(void **, char *);
extern void sqlgs2t(void **, char *);
extern void sqlorat(void **, unsigned int *, void *);

/* Forms Interface */
static const int IAPSUCC = 0;
static const int IAPFAIL = 1403;
static const int IAPFTL  = 535;
extern void sqliem(char *, int *);

typedef struct { unsigned short len; unsigned char arr[1]; } VARCHAR;
typedef struct { unsigned short len; unsigned char arr[1]; } varchar;

/* cud (compilation unit data) array */
static const short sqlcud0[] =
{12,4130,852,0,0,
5,0,0,1,0,0,27,36,0,0,4,4,0,1,0,1,9,0,0,1,10,0,0,1,10,0,0,1,10,0,0,
36,0,0,2,0,0,32,49,0,0,0,0,0,1,0,
51,0,0,3,0,0,30,55,0,0,0,0,0,1,0,
66,0,0,4,66,0,4,72,0,0,3,1,0,1,0,2,97,0,0,2,4,0,0,1,97,0,0,
93,0,0,5,0,0,32,100,0,0,0,0,0,1,0,
};


/* exam01.pc a·￠Oracleóú3ìDòùày */
/* μ÷￡o±3ìDòééüóPROCa·￠Oracleaóúμ±à3ììμ￡í¨1yòAUTHS
* ±íêè×÷ò′ú￡2éˉ×÷òD°1¤×ê￡DD°ó|¨±í￠2èêyY2￠ìá￡*/
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
/* °üoSQLí¨￡üóóú′|àí′íó￡*/
#include <sqlca.h>
void connect(); /* áóμOracle Server */
void disconnect(); /* aμOracle Serverμáó */
void sql_error(char *); /* ′|àí′íó±ú */
void select(); /* 2éˉ×ó3ìDò */
extern sqlglm(char *,int *,int *);
/* ÷3ìDò */
void main()
{
/* °2×°′íó′|àí±ú */
/* EXEC SQL WHENEVER SQLERROR DO sql_error("Oracle′íó--\n"); */ 

/* áóμêyYa */
connect();
/* ′DD2éˉ */
select();
/* aêyYaáó */
disconnect();
}
/* ×ó3ìDò */
/* áó×ó3ìDò connect() */
void connect()
{
/* EXEC SQL BEGIN DECLARE SECTION; */ 

/* VARCHAR username[20], password[10], server[10]; */ 
struct { unsigned short len; unsigned char arr[20]; } username;

struct { unsigned short len; unsigned char arr[10]; } password;

struct { unsigned short len; unsigned char arr[10]; } server;

/* EXEC SQL END DECLARE SECTION; */ 


strcpy (username.arr,"dbcrm/dbcrmsd@crmdb");
/*EXEC SQL CONNECT :username IDENTIFIED BY :password USING :server;*/
/* EXEC SQL connect :username; */ 

{
struct sqlexd sqlstm;
sqlstm.sqlvsn = 12;
sqlstm.arrsiz = 4;
sqlstm.sqladtp = &sqladt;
sqlstm.sqltdsp = &sqltds;
sqlstm.iters = (unsigned int  )10;
sqlstm.offset = (unsigned int  )5;
sqlstm.cud = sqlcud0;
sqlstm.sqlest = (unsigned char  *)&sqlca;
sqlstm.sqlety = (unsigned short)256;
sqlstm.occurs = (unsigned int  )0;
sqlstm.sqhstv[0] = (         void  *)&username;
sqlstm.sqhstl[0] = (unsigned int  )22;
sqlstm.sqhsts[0] = (         int  )22;
sqlstm.sqindv[0] = (         void  *)0;
sqlstm.sqinds[0] = (         int  )0;
sqlstm.sqharm[0] = (unsigned int  )0;
sqlstm.sqadto[0] = (unsigned short )0;
sqlstm.sqtdso[0] = (unsigned short )0;
sqlstm.sqphsv = sqlstm.sqhstv;
sqlstm.sqphsl = sqlstm.sqhstl;
sqlstm.sqphss = sqlstm.sqhsts;
sqlstm.sqpind = sqlstm.sqindv;
sqlstm.sqpins = sqlstm.sqinds;
sqlstm.sqparm = sqlstm.sqharm;
sqlstm.sqparc = sqlstm.sqharc;
sqlstm.sqpadto = sqlstm.sqadto;
sqlstm.sqptdso = sqlstm.sqtdso;
sqlstm.sqlcmax = (unsigned int )100;
sqlstm.sqlcmin = (unsigned int )2;
sqlstm.sqlcincr = (unsigned int )1;
sqlstm.sqlctimeout = (unsigned int )0;
sqlstm.sqlcnowait = (unsigned int )0;
sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
if (sqlca.sqlcode < 0) sql_error("Oracle′íó--\n");
}


printf("\n连接的数据库是%s\n", username.arr);
}
/* aáó×ó3ìDò disconnect() */
void disconnect()
{
char temp;
printf("\n是否在断开连接前提交所有事务(Y/N)");
scanf("%c", &temp);
fflush(stdin);
if(temp !='Y' && temp != 'y')
{
/* 回退事务，断开连接。 */
/* EXEC SQL ROLLBACK WORK RELEASE; */ 

{
struct sqlexd sqlstm;
sqlstm.sqlvsn = 12;
sqlstm.arrsiz = 4;
sqlstm.sqladtp = &sqladt;
sqlstm.sqltdsp = &sqltds;
sqlstm.iters = (unsigned int  )1;
sqlstm.offset = (unsigned int  )36;
sqlstm.cud = sqlcud0;
sqlstm.sqlest = (unsigned char  *)&sqlca;
sqlstm.sqlety = (unsigned short)256;
sqlstm.occurs = (unsigned int  )0;
sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
if (sqlca.sqlcode < 0) sql_error("Oracle′íó--\n");
}


printf("\n 回退事务，断开连接。\n\n");
}
else
{
/* 提交事务，断开连接。 */
/* EXEC SQL COMMIT WORK RELEASE; */ 

{
struct sqlexd sqlstm;
sqlstm.sqlvsn = 12;
sqlstm.arrsiz = 4;
sqlstm.sqladtp = &sqladt;
sqlstm.sqltdsp = &sqltds;
sqlstm.iters = (unsigned int  )1;
sqlstm.offset = (unsigned int  )51;
sqlstm.cud = sqlcud0;
sqlstm.sqlest = (unsigned char  *)&sqlca;
sqlstm.sqlety = (unsigned short)256;
sqlstm.occurs = (unsigned int  )0;
sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
if (sqlca.sqlcode < 0) sql_error("Oracle′íó--\n");
}


printf("\n提交事务，断开连接。\n\n");
exit(1);
}
}
/* 查询子程序 select()
* 首先输入作家代码，然后查询作家姓名和工资。*/
void select()
{
/* EXEC SQL BEGIN DECLARE SECTION; */ 

char author_code[8], name[10];
float salary;
short salary_ind;
/* EXEC SQL END DECLARE SECTION; */ 

printf("\n输入作家代码: ");
gets(author_code);
/* 查询作家姓名和工资 */
/* EXEC SQL SELECT name, salary INTO :name,:salary 
FROM auths
WHERE author_code = :author_code; */ 

{
struct sqlexd sqlstm;
sqlstm.sqlvsn = 12;
sqlstm.arrsiz = 4;
sqlstm.sqladtp = &sqladt;
sqlstm.sqltdsp = &sqltds;
sqlstm.stmt = "select name ,salary into :b0,:b1  from auths where author_co\
de=:b2";
sqlstm.iters = (unsigned int  )1;
sqlstm.offset = (unsigned int  )66;
sqlstm.selerr = (unsigned short)1;
sqlstm.cud = sqlcud0;
sqlstm.sqlest = (unsigned char  *)&sqlca;
sqlstm.sqlety = (unsigned short)256;
sqlstm.occurs = (unsigned int  )0;
sqlstm.sqhstv[0] = (         void  *)name;
sqlstm.sqhstl[0] = (unsigned int  )10;
sqlstm.sqhsts[0] = (         int  )0;
sqlstm.sqindv[0] = (         void  *)0;
sqlstm.sqinds[0] = (         int  )0;
sqlstm.sqharm[0] = (unsigned int  )0;
sqlstm.sqadto[0] = (unsigned short )0;
sqlstm.sqtdso[0] = (unsigned short )0;
sqlstm.sqhstv[1] = (         void  *)&salary;
sqlstm.sqhstl[1] = (unsigned int  )sizeof(float);
sqlstm.sqhsts[1] = (         int  )0;
sqlstm.sqindv[1] = (         void  *)0;
sqlstm.sqinds[1] = (         int  )0;
sqlstm.sqharm[1] = (unsigned int  )0;
sqlstm.sqadto[1] = (unsigned short )0;
sqlstm.sqtdso[1] = (unsigned short )0;
sqlstm.sqhstv[2] = (         void  *)author_code;
sqlstm.sqhstl[2] = (unsigned int  )8;
sqlstm.sqhsts[2] = (         int  )0;
sqlstm.sqindv[2] = (         void  *)0;
sqlstm.sqinds[2] = (         int  )0;
sqlstm.sqharm[2] = (unsigned int  )0;
sqlstm.sqadto[2] = (unsigned short )0;
sqlstm.sqtdso[2] = (unsigned short )0;
sqlstm.sqphsv = sqlstm.sqhstv;
sqlstm.sqphsl = sqlstm.sqhstl;
sqlstm.sqphss = sqlstm.sqhsts;
sqlstm.sqpind = sqlstm.sqindv;
sqlstm.sqpins = sqlstm.sqinds;
sqlstm.sqparm = sqlstm.sqharm;
sqlstm.sqparc = sqlstm.sqharc;
sqlstm.sqpadto = sqlstm.sqadto;
sqlstm.sqptdso = sqlstm.sqtdso;
sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
if (sqlca.sqlcode < 0) sql_error("Oracle′íó--\n");
}


/*根据指示变量的值来确定该作家的工资是否为空。*/
if (salary >0)
{
printf("\n作家代码\t作家姓名\t作家工资\n");
printf("--------\t--------\t--------\n");
printf("%8s\t%8s\t%8.2f\n", author_code, name, salary);
}
else
{
printf("作家%s的工资未录入，为空值！\n", name);
}
}
/* 错误处理子程序 sql_error() */
void sql_error(char *msg)
{
char err_msg[128];
size_t buf_len, msg_len;
/* 出现SQL错误，继续往下执行。 */
/* EXEC SQL WHENEVER SQLERROR CONTINUE; */ 

printf("\n%s\n", msg);
buf_len=sizeof(err_msg);
/* 调用函数sqlglm()获得错误消息。 */
sqlglm(err_msg, &buf_len, &msg_len);
printf("%.*s\n", msg_len, err_msg);
/* 回退事务，断开连接，退出程序。 */
/* EXEC SQL ROLLBACK RELEASE; */ 

{
struct sqlexd sqlstm;
sqlstm.sqlvsn = 12;
sqlstm.arrsiz = 4;
sqlstm.sqladtp = &sqladt;
sqlstm.sqltdsp = &sqltds;
sqlstm.iters = (unsigned int  )1;
sqlstm.offset = (unsigned int  )93;
sqlstm.cud = sqlcud0;
sqlstm.sqlest = (unsigned char  *)&sqlca;
sqlstm.sqlety = (unsigned short)256;
sqlstm.occurs = (unsigned int  )0;
sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
}


exit(EXIT_FAILURE);
}
/*建表语句
DROP TABLE auths CASCADE CONSTRAINTS
/
CREATE TABLE auths(
AUTHOR_CODE VARCHAR2(8) NOT NULL,
NAME VARCHAR2(10),
BIRTHDATE DATE,
ENTRY_DATE_TIME DATE,
SALARY NUMBER(7,2),
remark VARCHAR2(255))
/
REM 下一行为在PL/SQL环境中运行建表文件的命令
REM @ E: \ PROCW \ Exam01 \ auths.sql
REM 下一行为在PL/SQL环境中向auths表插入数据的命令，插入后应提交(COMMIT)！
REM INSERT INTO auths(author_code,name,salary) VALUES('A00001','王达琳',1200);

*/