サンプル・プログラム: SQL99構文を使用する単純な問合せ

このプログラムは前述のサンプルと似ていますが、SELECT、INSERT、DELETEおよびUPDATE文のSQL99構文を使用し、DECLARE CURSOR文のカーソル本体がサポートされています。

MODE=ORACLEプリコンパイラ・オプションを使用して、サンプル・プログラムをプリコンパイルします。

/*
 *  sql99.pc
 *
 *  Prompts the user for an employee number,
 *  then queries the emp table for the employee's
 *  name, salary and department. 
 *
 */
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlda.h>
#include <sqlcpr.h>
.
/* Define constants for VARCHAR lengths. */
#define     UNAME_LEN     30
#define     PWD_LEN        40
/* Declare variables.  No declare section is needed if MODE=ORACLE. */
 
VARCHAR     username[UNAME_LEN]; 
 /* VARCHAR is an Oracle-supplied struct */
 varchar     password[PWD_LEN];  
 /* varchar can be in lower case also. */
/* Define a host structure for the output values of a SELECT statement.  */
 
struct{ 
   VARCHAR   emp_name[UNAME_LEN];   
   float     salary;  
   VARCHAR  dept_name[UNAME_LEN] ;
  } emprec;
/* Define an indicator struct to correspond to the host output struct. */
struct{ 
   short     emp_name_ind;  
   short     sal_ind;   
   short     dept_name;
  } emprec_ind;
 
/*  Input host variable. */
int         emp_number;
int         total_queried;
/* Include the SQL Communications Area. You can use #include or EXEC SQL 
INCLUDE. */
 
#include <sqlca.h>
/* Declare error handling function. */
void sql_error(msg)   
   char *msg;
   {   
    char err_msg[128];    
    size_t buf_len, msg_len;    
    EXEC SQL WHENEVER SQLERROR CONTINUE;    
    printf("\n%s\n", msg); 
    buf_len = sizeof (err_msg);
    sqlglm(err_msg, &buf_len, &msg_len);  
    printf("%.*s\n", msg_len, err_msg); 
    EXEC SQL ROLLBACK RELEASE;  
  exit(EXIT_FAILURE);
  }
 
void main(){  
   char temp_char[32];
  /* Connect to ORACLE-- * Copy the username into the VARCHAR. */
    strncpy((char *) username.arr, "scott", UNAME_LEN);
  /* Set the length component of the VARCHAR. */   
    username.len = (unsigned short) strlen((char *) username.arr);
   /* Copy the password. */   
     strncpy((char *) password.arr, "tiger", PWD_LEN);   
     password.len = (unsigned short) strlen((char *) password.arr);
  /* Register sql_error() as the error handler. */    
    EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--\n");
  /* Connect to ORACLE.  Program will call sql_error() * if an error occurs 
when connecting to the default database. */    
     EXEC SQL CONNECT :username IDENTIFIED BY :password; 
    printf("\nConnected to ORACLE as user: %s\n", username.arr);
  /* Loop, selecting individual employee's results */   
     total_queried = 0;   
     for (;;)    {
                  emp_number = 0;       
                  printf("\nEnter employee number (0 to quit): "); 
                  gets(temp_char);    
                  emp_number = atoi(temp_char);      
                  if (emp_number == 0)            
                        break;
  /* Branch to the notfound label when the * 1403 ("No data found") condition 
occurs. */     
   EXEC SQL WHENEVER NOT FOUND GOTO notfound;
 
  /* The following query uses SQL99 syntax - RIGHT OUTER JOIN */
    EXEC SQL SELECT e.ename, e.sal, d.dname        
    INTO :emprec INDICATOR :emprec_ind       
    FROM EMP e RIGHT OUTER JOIN dept d   
    ON e.deptno = d.deptno  
    WHERE e.EMPNO = :emp_number;
  /* Print data. */      
  printf("\n\nEmployee   Salary    Department Name\n");    
  printf("--------   -------   ------------------\n");
  /* Null-terminate the output string data. */    
    emprec.emp_name.arr[emprec.emp_name.len] = '\0';  
    emprec.dept_name.arr[emprec.dept_name.len]='\0';   
    printf("%s      %7.2f          %s ",            emprec.emp_name.arr, 
  emprec.salary, emprec.dept_name.arr);
    total_queried++;   
     continue;
  
  notfound:        
     printf("\nNot a valid employee number - try again.\n");  
  }
  
 printf("\n\nTotal rows returned was %d.\n", total_queried); 
 printf("\nG'day.\n\n\n");
/* Disconnect from ORACLE. */ 
   EXEC SQL ROLLBACK WORK RELEASE;   
 exit(EXIT_SUCCESS);
}