sample.pc

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

/*
NAME
  sample
FUNCTION
  Simple 32-bit Pro*C sample program from Pro*C User's Guide
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.

           The program queries the user for data as follows:

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

           The program terminates 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


OWNER
  Clare
DATE
  05/01/84
MODIFIED
  kakiyama    05/05/97 - replaced sqlproto.h with sqlcpr.h 
  rahmed      08/10/95 - No need yo define WIN_NT 
  rahmed      07/05/95 - Removed all the warnings for NT.
  syau        03/07/95 - WIN_NT: add prototype files
  Hartenstine 04/27/93 - WIN_NT: Port to Windows NT
  dcriswel    04/11/92 - IBMC: Add .H to EXEC SQL INCLUDE
  bfotedar    01/07/92 - Included stdlib.h, modified compiling instructions
  bfotedar    11/22/91 - OS2_2: Port to OS/2 2.0
  Criswell    11/08/90 - Declared main() void
  Criswell    11/01/90 - Microsoft C 6.0 changes
  Okamura     07/05/89 - Update documentation for V6 on OS/2
  RPatterson  03/26/87 - Updated error handling.
  RPatterson  02/20/86 - Port: support Microsoft 'C' compiler and sqlmsc.lib.
  Clare       09/19/84 - Port: remove VMSisms.
  Clare       12/26/84 - Port IBM: fflush() prompts.
*/

#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                     */
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA.H;

void main()
{

/* --------------------------------------------------------------------------
 logon to ORACLE, and open the cursors. The program exits if any errors occur.
-------------------------------------------------------------------------- */

   strcpy((char *)uid.arr,"SCOTT");
   uid.len = (short) strlen((char *)uid.arr);
   strcpy((char *)pwd.arr,"TIGER");
   pwd.len = (short) strlen((char *)pwd.arr);

   EXEC SQL WHENEVER SQLERROR GOTO errexit;
   EXEC SQL CONNECT :uid IDENTIFIED BY :pwd;

/* --------------------------------------------------------------------------
   Retrieve the 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
-------------------------------------------------------------------------- */

   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.
     */
     l = asks("Enter employee name  : ", (char *)ename.arr);

     if ( l <= 0 )
       break;

     ename.len = (short) l;

     job.len = (short) asks("Enter employee job   : ", (char *)job.arr);
     askn("Enter employee salary: ",&sal);
     for ( ; ; )
       {
       if ( askn("Enter employee dept  :   ",&deptno) < 0 )
         break;

       EXEC SQL WHENEVER NOT FOUND GOTO nodept;
       EXEC SQL SELECT DNAME
                  INTO :dname
                  FROM DEPT
                  WHERE DEPTNO = :deptno;

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

       EXEC SQL WHENEVER NOT FOUND STOP;

       /* Here if deptno was found in dbs. Insert new employee into dbs. */

       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);
       break;

       /* Here if deptno NOT found in dbs */
       nodept:
         printf("\nNo such department\n");
         continue;
       }
     }

/* --------------------------------------------------------------------------
   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 points to the null terminated string to be printed
   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(text,variable)
   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);
   }