sample_r.pc

orale培训教材包括了所有的sql说明和实例

/*
 * name:pro*c c程序，使用service名完成远程连接数据库，调用存储过程
 * file name :sample_r.pc
 *
NOTES

   sample  is a simple example program which adds new employee
           records to the personnel data base.  Checking
           is done to insure the integrity of the data base.
           The employee numbers are automatically selected using
           the current maximum employee number + 10.

           提示画面为:

           Enter employee name:
           Enter employee job:
           Enter employee salary:
           Enter employee dept:

           程序将结束： if control Z (end of file) or null
           string (<return> key) is entered when the employee name
           is requested.

           If the record is successfully inserted, the following
           is printed:

           ename added to department dname as employee # nnnnnn



*/

#include <stdlib.h>
#include <stdio.h>
#include <ctype.h>
#include <string.h>
#include <sqlda.h>
#include <sqlcpr.h>

/*函数声明*/
int errrpt(void);
int asks(char *,char *);
int askn(char *,int *);

/*宿主变量声明区*/
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR uid[80];
                                   /* username                            */
VARCHAR pwd[20];
                                   /* password                            */

int     empno;                     /* employee number                     */
VARCHAR ename[15];
                                   /* employee name                       */
int     deptno;                    /* department number                   */
VARCHAR dname[15];
                                   /* department name                     */

VARCHAR job[15];
                                   /* employee job                        */
int     sal;                       /* employee salary                     */

/*remote server connect pointer*/
/*字符指针存储字符串，用户名口令*/
char* userid;
/*字符指针存储字符串，service名*/
char* server;

/*用于调用存储过程取得总雇员数, EXEC SQL CALL count_emp(:emp_no)*/
int emp_no;

EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA.H;

void main()
{

/* --------------------------------------------------------------------------
 logon to ORACLE,The program exits if any errors occur.
-------------------------------------------------------------------------- */

   /*用VARCHAR宿主变量连接数据库
   strcpy((char *)uid.arr,"SCOTT");
   uid.len = (short) strlen((char *)uid.arr);
   strcpy((char *)pwd.arr,"TIGER");
   pwd.len = (short) strlen((char *)pwd.arr);
   */

   /*用字符串指针初始化用户，口令*/
   /*使用两个宿主指针变量,在宿主变量定义区定义*/
   userid = "scott/tiger";
   server = "ora8";


   EXEC SQL WHENEVER SQLERROR GOTO errexit;


   /*使用service名连接远端服务器*/
   EXEC SQL CONNECT :userid USING :server;
   /*使用用户名，口令连接本地服务器*/
   /*EXEC SQL CONNECT :uid IDENTIFIED BY :pwd;*/

/* --------------------------------------------------------------------------
   使用宿主变量取得 current maximum employee number
-------------------------------------------------------------------------- */

   EXEC SQL SELECT NVL(MAX(EMPNO),0) + 10
              INTO :empno
              FROM EMP;

/* --------------------------------------------------------------------------
   read the user's input from STDIN.  If the employee name is
   not entered, exit.
   Verify that the entered department number is valid and echo the
   department's name：检查输入的department number是否符合数据完整性的要求
-------------------------------------------------------------------------- */
   /*每次雇员编号+10*/
   for( ; ; empno+=10 )
     {
     int l;

     /* Get employee name to be inserted.

        IMPORTANT NOTE: beware of coding as follows (I got burnt, 1st time):

          ename.len = (short) asks("Enter employee name  : ", (char *)ename.arr);
          if ( ename.len <= 0 )
            ..etc..

        In the above, asks() returns an int, but ename.len is an unsigned
        short (see SQLCA). Therefore, the "if" fails for <EOF> (which returns
        -1) because, by definition, the unsigned short can't be negative.
     */
     /*asks（）是用户自定义的函数，见文件尾.读入字符串到ename.arr中*/
     l = asks("Enter employee name  : ", (char *)ename.arr);
     
     /*如果击入回车，结束输入*/
     if ( l <= 0 )
     {
       /*调用存储过程count_emp()取得总雇员数*/
       EXEC SQL CALL count_emp(:emp_no);
       /*如果printf选用输出format不正确，会出现系统故障*/
       printf("\n total employeers is ");
       printf("%d",emp_no);
       /*跳出最外层for循环，终止输入*/
       break;
      }
     

     /*取得各VARCHAR变量的长度(.len)*/
     ename.len = (short) l;

     job.len = (short) asks("Enter employee job   : ", (char *)job.arr);
     askn("Enter employee salary: ",&sal);

/* --------------------------------------------------------------------------
   此一重for循环，检查检查输入的department number是否符合数据完整性的要求，
   不符合要求，强制再次输入，直到符合要求为止
-------------------------------------------------------------------------- */
     for ( ; ; )
       {
       if ( askn("Enter employee dept  :   ",&deptno) < 0 )
         break;
       /*当deptno不满足数据完整性，跳转到nodept标号*/
       EXEC SQL WHENEVER NOT FOUND GOTO nodept;
       EXEC SQL SELECT DNAME
                  INTO :dname
                  FROM DEPT
                  WHERE DEPTNO = :deptno;

       dname.arr[dname.len] = '\0';

       /*终止WHENEVER NOT FOUND GOTO nodept的设定*/
       EXEC SQL WHENEVER NOT FOUND STOP;

       /* 若在dept表中找到对应deptno :if deptno was found in dbs. 使用宿主变量插入 emp表 */

       EXEC SQL INSERT INTO EMP(EMPNO,ENAME,JOB,SAL,DEPTNO)
                  VALUES (:empno,:ename,:job,:sal,:deptno);

       printf("\n%s added to the %s department as employee number %d\n",
         ename.arr,dname.arr,empno);
       /*跳出内层for循环*/
       break;

       /* nodept标号 :Here if deptno NOT found in dbs */
       nodept:
         printf("\nNo such department\n");
         continue;
       }
/* --------------------------------------------------------------------------
   department number检查循环尾
-------------------------------------------------------------------------- */
     }

/* --------------------------------------------------------------------------
   close the cursors and log off from ORACLE
-------------------------------------------------------------------------- */

   EXEC SQL COMMIT WORK RELEASE;
   printf ("\nEnd of the Pro*C Sample example program.\n");
   return;

errexit:
   errrpt();
   EXEC SQL WHENEVER SQLERROR CONTINUE;
   EXEC SQL ROLLBACK WORK RELEASE;
   return;
}

/*自定义函数*/
/*---------------------------------------------------------------------------
COUNT askn(text,variable)

   print the 'text' on STDOUT and read an integer variable from
   SDTIN.在屏幕上打出text参数对应的字符串，读入一个int型变量:variable

   text[] 将要打印输出的以 null 结尾的字符串
   variable points to an integer variable

   askn returns a 1 if the variable was read successfully or a
       -1 if -eof- was encountered
-------------------------------------------------------------------------- */

int askn(char text[],int* variable)
   {
   char s[20];
   printf(text);
   fflush(stdout);
   if ( gets(s) == (char *)0 )
     return(EOF);


   *variable = atoi(s);
   return(1);
   }

/* --------------------------------------------------------------------------
COUNT asks(text,variable)
   
   读入字符串
   print the 'text' on STDOUT and read up to 'len' characters into
   the buffer pointed to by variable from STDIN.

   text points to the null terminated string to be printed
   variable points to a buffer of at least 'len'+1 characters

   asks returns the number of character read into the string, or a
       -1 if -eof- was encountered
----------------------------------------------------------------------- */

int asks(text,variable)
   char text[],variable[];
   {
   printf(text);
   fflush(stdout);
   return( gets(variable) == (char *)0 ? EOF : strlen(variable) );
   }

/* --------------------------------------------------------------------------
int errrpt()

   errrpt prints the ORACLE error msg and number.
-------------------------------------------------------------------------- */

errrpt()
   {
   printf("%.70s (%d)\n", sqlca.sqlerrm.sqlerrmc, -sqlca.sqlcode);
   return(0);
   }