rhel7靜態(tài)安裝oracle11g真的很麻煩Ｗ榈住Ｂ舭ァ！

oracle啟動(dòng)

$sqlplus /nolog
SQL> connect /as sysdba               
Connected.
SQL> startup 

“/nolog”是不登陸到數(shù)據(jù)庫服務(wù)器的意思录淡，如果沒有/nolog參數(shù)捌木，sqlplus會(huì)提示你輸入用戶名和密碼 如果在sql*plus環(huán)境中啟動(dòng)數(shù)據(jù)庫的話，必須先使用不登陸到數(shù)據(jù)庫服務(wù)器的方式進(jìn)入sqlplus環(huán)境嫉戚，再用startup命令啟動(dòng)數(shù)據(jù)庫刨裆。因?yàn)閿?shù)據(jù)庫沒有啟動(dòng)的話澈圈，不能登陸數(shù)據(jù)庫，也無法驗(yàn)證用戶名和密碼帆啃。
啟動(dòng)和關(guān)閉監(jiān)聽服務(wù)： Oracle監(jiān)聽用于相應(yīng)客戶端連接oracle服務(wù)器的請求瞬女，如果監(jiān)聽沒有啟動(dòng)，則不能通過網(wǎng)絡(luò)方式訪問Oracle數(shù)據(jù)庫服務(wù)努潘，只能在Oracle服務(wù)器本機(jī)以IPC通信的方式接入

$ lsnrctl start/status/stop

登錄Oracle服務(wù)器

管理員登陸诽偷，是典型的操作系統(tǒng)認(rèn)證，不需要listener進(jìn)程慈俯。

$ sqlplus / as sysdba

普通用戶登錄渤刃。

$ sqlplus scott/11

切換用戶：

SQL> connect / as sysdba    將當(dāng)前登錄用戶切換為管理員登錄
SQL> connect scott/tiger(原始密碼)  將當(dāng)前登錄用戶切換為scott用戶登錄

查看用戶：

SQL> select username from dba_users;

修改用戶密碼：
如在登錄時(shí)出現(xiàn)如下錯(cuò)誤提示，說明用戶密碼即將到期贴膘，需進(jìn)行重設(shè)或者修改卖子。

ERROR:
ORA-28002: the password will expire within 7 days

可以采用如下方法修改或重設(shè)用戶密碼：

$sqlplus / as sysdba
SQL> alter user scott identified by password; 
User altered.       出現(xiàn)此提示說明修改用戶密碼成功。

linux下sqlplus使用ed命令

1. 在sqlplus中 鍵入 ed
2. 在另外一個(gè)應(yīng)用程序中刑峡，打開afiedt.buf文件洋闽。進(jìn)行修改 保存 退出
3. 回到sqlplus環(huán)境中，鍵入q
4. 在sqlplus中 /

可以將相關(guān)命令設(shè)置到配置文件中突梦，其配置方法如下：

打開該文件：

# vi $ORACLE_HOME/sqlplus/admin/glogin.sql

在配置文件末尾寫入如下內(nèi)容：

define _editor=vim
set linesize 140
set pagesize 140

Pro*C/C++簡介

官方文檔查詢:E11882_01诫舅、e10825_oracleproc聯(lián)機(jī)文件.pdf

proc編譯工具的初步使用，宿主變量宫患、連接數(shù)據(jù)庫刊懈。

proc“程序結(jié)構(gòu)”
proc“開發(fā)流程”：對比.c 和 .pc文件編譯流程。

ANSI（美國國家標(biāo)準(zhǔn)學(xué)會(huì)）定義標(biāo)準(zhǔn)：Oracle → proc編譯器

Linux: proc 編譯器位置：cdORACLE_HOME → cd bin → ls -l p*

Windows: C:> proc 編譯器位置: C:\app\Administrator\product\11.2.0\client_1\BIN

1.proc編程初步

1.1編寫hello world

proc編譯.pc文件→.c程序娃闲。
proc hello.pc → hello.c

編譯出錯(cuò)：需要修改 proc 配置文件 pcscfg.cfg

上述問題是因?yàn)閔ello.pc中stdio.h在proc編譯器中沒有被找到所致
打開設(shè)置文件
$ cd $ORACLE_HOME           
$ cd ./precomp/admin/
$ vi pcscfg.cfg 

1

將兩個(gè)版本對應(yīng)上

2

1.2連接Oracle數(shù)據(jù)庫

宿主變量：定義在緩沖區(qū)中虚汛，是連接C語言群和嵌入式SQL語言群的紐帶。

EXEC SQL BEGIN DECLARE SECTION皇帮；//分號(hào)結(jié)束標(biāo)記卷哩。
char *serversid = "scott/scott@orc1";   宿主變量可以被c直接引用；@后面是數(shù)據(jù)庫名稱

EXEC SQL END DECLARE SECTION属拾；

EXEC SQL CONNECT :serversid;

C直接使用将谊，嵌入式SQL使用“:”

if (sqlca.sqlcode != 0) {   連接數(shù)據(jù)庫失敗
判斷sqlca.sqlcode的值是否為0。 

sqlcode：數(shù)據(jù)庫操作的返回碼渐白，成功→0尊浓；失敗→-1

前提保證Oracle數(shù)據(jù)庫已經(jīng)啟動(dòng)，并且監(jiān)聽服務(wù)也啟動(dòng)成功纯衍。

#include "sqlca.h"  所在目錄：$ORACLE_HOME/precomp/public    

動(dòng)態(tài)庫libclntsh.so  所在目錄：$ORACLE_HOME/lib

gcc test.c -o test -L $ORACLE_HOME/lib 
-I $ORACLE_HOME/precomp/public -l clntsh

連接數(shù)據(jù)庫出錯(cuò)：如眠砾，將登陸密碼錯(cuò)誤輸入，./test 執(zhí)行文件 得到錯(cuò)誤提示：connect err : -1017

使用：“oerr ora 錯(cuò)誤碼” 查看錯(cuò)誤描述。如：oerr ora 1017(負(fù)號(hào)省略)

斷開連接  EXEC SQL COMMIT RELEASE;  release 是斷開連接褒颈，斷開之前必須commit或者rollback

1.2.1示例程序：【hello.pc】

#include <stdio.h>
#include "sqlca.h"

EXEC SQL BEGIN DECLARE SECTION;

    char *serversid = "scott/121@orcl";

EXEC SQL END DECLARE SECTION;

int main(void)
{
    int ret = 0;
    printf("serversid: %s\n", serversid);
    
    EXEC SQL CONNECT :serversid;
    if (sqlca.sqlcode != 0) {
        ret = sqlca.sqlcode;
        printf("connect err: %d\n", ret);   
        return ret;
    }
    printf("connect ok...\n");
    
    EXEC SQL COMMIT RELEASE;
    
    printf("hello proc...\n");
    
    return 0;
}

1.2.2Makefile文件的編寫

C_FILES = $(wildcard *.pc)
C_MIDDLE1 = $(patsubst %.pc, %.c, $(C_FILES))
C_MIDDLE2 = $(patsubst %.pc, %.lis, $(C_FILES))
C_TARGET = $(patsubst %.pc, %, $(C_FILES))

lib_path = ${ORACLE_HOME}/lib
inc_path = ${ORACLE_HOME}/precomp/public

all:$(C_TARGET) 

$(C_TARGET):%:%.c
    gcc $< -o $@ -L$(lib_path) -I$(inc_path) -lclntsh
%.c:%.pc
    proc $<

.PHONY:clean all
clean: 
    -rm -rf $(C_TARGET) $(C_MIDDLE1) $(C_MIDDLE2) *~

${ ORACLE_HOME }可以在Makefile中使用環(huán)境變量使用自定義變量是 $(abc)

1.3proc編譯C++程序

將test.pc修改成test2.pc励堡，使用c++的語法谷丸。 同樣使用proc編譯 出錯(cuò)。

原因是proc編譯器默認(rèn)會(huì)將.pc的文件按C語法進(jìn)行編譯应结。如果想編譯c++程序刨疼，需要在proc編譯時(shí)指定編譯選項(xiàng)

parse=none 告訴proc編譯器 按照c++規(guī)范解析文件

code=cpp 告訴proc編譯器 按照c++規(guī)范生成文件

oname 指定輸出文件名

$ proc test2.pc parse=none code=cpp oname=test2.cpp
$ g++ test2.cpp -o test2 -I $ORACLE_HOME/precomp/public -L $ORACLE_HOME/lib -l clntsh

1.4靜態(tài)SQL insert/update/delete

1.4.1在proc程序中insert

EXEC SQL insert into dept(deptno, dname, loc) values(:deptno, :dname, :loc);

由于事物的隔離性，所以任何一個(gè)操作結(jié)束后應(yīng)及時(shí)使用 EXEC SQL COMMIT

注意：proc中做insert鹅龄、update揩慕、delete操作的時(shí)候，sqlplus不要做除select之外其他操作扮休。否則會(huì)發(fā)生死鎖迎卤。

1.4.2在proc程序中update

EXEC SQL update dept set loc = :loc, dname = :dname where deptno=50;

1.4.3在proc程序中delete

EXEC SQL delete from dept where deptno = :deptno;

示例程序：【01_insert_up_del.pc】

#include <stdio.h>
#include <sqlca.h>

EXEC SQL BEGIN DECLARE SECTION;

char *serversid = "scott/scott@orc1";

EXEC SQL END DECLARE SECTION;

int main(void)
{
    int ret;
    printf("serversid = %s\n",serversid);//C直接使用
    
    EXEC SQL connect :serversid;//PROC連接
    if(sqlca.sqlcode != 0){//非0表示連接失敗,錯(cuò)誤提示保存在sqlca結(jié)構(gòu)體的sqlcode中
        ret = sqlca.sqlcode;
        printf("connect error:%d\n",sqlca.sqlcode);//u can find error by "oerr ora ret"
        return ret;
    }
    printf("connect success!\n");
    
    
    //insert into dept(deptno,dname,loc) values(51,'Engineer','BEIJING');
    EXEC SQL insert into dept(deptno,dname,loc) values(55,'Engineer','BEIJING');
    if(sqlca.sqlcode != 0){
        ret = sqlca.sqlcode;
        printf("insert error:%d\n",sqlca.sqlcode);
        return ret;
    }
    EXEC SQL commit;//插入一個(gè)事務(wù)，必須要commit代表完成玷坠，不然要到完成整個(gè)事務(wù)蜗搔，這個(gè)insert才算成功
    printf("insert success!\n");
    
    //update dept set dname='50dname',loc='50loc' where deptno = 50;
    EXEC SQL update dept set dname='50dname',loc='50loc' where deptno = 55;
    if(sqlca.sqlcode != 0){
        ret = sqlca.sqlcode;
        printf("update error:%d\n",sqlca.sqlcode);
        return ret;
    }
    printf("update success!\n");
    
    getchar();
    
    //delete from dept where deptno > 40;
    EXEC SQL delete from dept where deptno > 40;
    if(sqlca.sqlcode != 0){
        ret = sqlca.sqlcode;
        printf("delete error:%d\n",sqlca.sqlcode);
        return ret;
    }
    printf("delete success!\n");
    
        
    EXEC SQL COMMIT RELEASE;//斷開連接
    if(sqlca.sqlcode != 0){
        ret = sqlca.sqlcode;
        printf("release error:%d\n",sqlca.sqlcode);
        return ret;
    }
    printf("release success!\n");

    return 0;
}

1.4.4select

將數(shù)據(jù)庫中的數(shù)據(jù)查詢出來，保存在 宿主變量 中八堡，并回顯到終端上樟凄。

EXEC SQL select deptno, dname, loc into :deptno, :dname, :loc from dept where deptno = 10;

由于數(shù)據(jù)類型的原因，所有不足20字節(jié)的字符串填充了“空格”兄渺！——> 宿主變量的數(shù)據(jù)類型缝龄。
【注意】：當(dāng)搜索結(jié)果為多行的時(shí)候，一個(gè)變量是無法保存的挂谍，編譯會(huì)報(bào)錯(cuò)：SELECT..INTO returns too many rows

例如叔壤，使用where deptno = 20作為限定條件，查詢emp表凳兵。但是百新，如果是where empno = 7788則沒有問題。

1.4.5變長字符串類型

使用 EXEC SQL select deptno, dname, loc into :deptno, :dname, :loc from dept where deptno = 10;

輸出到屏幕庐扫，由于數(shù)據(jù)類型的原因饭望，所有不足20字節(jié)的字符串填充了“空格”！在此處應(yīng)該使用變長字符串varchar類型形庭。但這種數(shù)據(jù)類型存儲(chǔ)的數(shù)據(jù)铅辞，如何讓gcc編譯器認(rèn)得呢？

分析程序結(jié)構(gòu)萨醒，應(yīng)發(fā)現(xiàn)：

EXEC SQL BEGIN DECLARE SECTION;
    ...
EXEC SQL END DECLARE SECTION;   

之間的變量應(yīng)是Proc編譯器斟珊，借助C定義變量的語法，定義的變量富纸。
原因：.pc → proc → .c → gcc → 可執(zhí)行文件囤踩。   

在 proc_xxx.pc 中 定義varchar dname[20] 宿主變量旨椒，varchar 類型只有proc識(shí)得
但gcc編譯器也沒有報(bào)錯(cuò)。查看生成的.c文件堵漱，發(fā)現(xiàn)：proc編譯器對varchar進(jìn)行了轉(zhuǎn)化：

varchar dname[20] → struct { unsigned short len; unsigned char arr[20]; } dname2; 

C程序使用dname時(shí)综慎，實(shí)際用的是dname.arr，——如：printf("dname:%s, loc:%s, deptno:%d\n", dname2.arr, loc2.arr, deptno2);

而 EXEC SQL 使用danme的時(shí)候勤庐，就直接使用danme示惊，因?yàn)閜roc會(huì)在編譯的時(shí)候?qū)anme → struct ....結(jié)構(gòu)體。

——程序比較愉镰，使用char dname[20]; printf("%s", dname) 和 varchar dname[20] printf("%s",danme.arr);區(qū)別米罚。

示例程序：

#include <stdio.h>
#include <sqlca.h>

EXEC SQL BEGIN DECLARE SECTION;//宿主變量部分
        
        char *serverid = "scott/scott@orc1";
        int deptno;
        varchar dname[20];
        varchar loc[20];

EXEC SQL END DECLARE SECTION;
int main(void)
{
     int ret;
     EXEC SQL CONNECT :serverid;
     if(sqlca.sqlcode != 0){
        ret = sqlca.sqlcode;
        printf("connect error:%d\n",sqlca.sqlcode);
        return ret;
    }
     printf("connect success!\n");
     
     //select deptno,dname,loc from dept where deptno = 10;
     EXEC SQL select deptno,dname,loc into :deptno, :dname, :loc from dept where deptno = 10;//結(jié)果需要變量接收
     if(sqlca.sqlcode != 0){
        ret = sqlca.sqlcode;
        printf("select error:%d\n",sqlca.sqlcode);
        return ret;
    }
     printf("deptno = %d |dname = %s|loc = %s\n",deptno,dname.arr,loc.arr);
   /*查看編譯出來的.c文件，varchar類型被自動(dòng)轉(zhuǎn)化為一個(gè)結(jié)構(gòu)體丈探！*/
   
   EXEC SQL COMMIT RELEASE;
   if(sqlca.sqlcode != 0){
        ret = sqlca.sqlcode;
        printf("release error:%d\n",sqlca.sqlcode);
        return ret;
     }
     printf("release success!\n");
   
   return 0;
}

1.5Oracle Proc數(shù)據(jù)類型

在借助Proc程序訪問Oracle數(shù)據(jù)庫的整個(gè)過程中录择，主要涉及到的數(shù)據(jù)類型有三種，分別是：宿主變量數(shù)據(jù)類型类嗤、外部數(shù)據(jù)類型糊肠、內(nèi)部數(shù)據(jù)類型。

3

1.5.1內(nèi)部數(shù)據(jù)類型

數(shù)據(jù)庫中的表和偽列使用的數(shù)據(jù)類型遗锣，如：varchar2货裹、number、date ……Oracle將數(shù)據(jù)存儲(chǔ)到表的列中所使用的數(shù)據(jù)格式精偿。

1.5.2宿主變量數(shù)據(jù)類型

在 .pc 文件中定義變量所使用的數(shù)據(jù)類型弧圆，如：int、char笔咽、char[N]搔预、short、varchar[N]叶组、……
用于在 .pc 應(yīng)用程序中和oracle數(shù)據(jù)庫之間傳遞數(shù)據(jù)拯田。

1.5.3外部數(shù)據(jù)類型

外部數(shù)據(jù)類型包括全部的 內(nèi)部數(shù)據(jù)類型 和 宿主變量數(shù)據(jù)類型。

.pc生成的可執(zhí)行程序甩十，在運(yùn)行時(shí)會(huì)將宿主變量數(shù)據(jù)類型船庇，映射成Oracle外部數(shù)據(jù)類型。

重點(diǎn)：在編寫Pro*C/C++程序的時(shí)候不能直接使用Oracle外部數(shù)據(jù)類型來定義宿主變量侣监！

1.5.4數(shù)據(jù)類型轉(zhuǎn)換

只有“宿主變量數(shù)據(jù)類型”和我們的.pc程序直接產(chǎn)生關(guān)系鸭轮。proc編譯時(shí)，需完成轉(zhuǎn)化成外部數(shù)據(jù)類型工作：

1. 自動(dòng)轉(zhuǎn)換：varchar dname[20] → struct { unsigned short len; unsigned char arr[20]; } dname2;
2. 手動(dòng)轉(zhuǎn)換：其主要轉(zhuǎn)換語法如下：

typedef char dnameType[20];         使用typedef定義數(shù)組數(shù)據(jù)類型   

EXEC SQL BEGIN DECLARE SECITON;
EXEC SQL TYPE dnameType is string(20);      告訴proc編譯器將dnameType類型轉(zhuǎn)為string類型 ('\0'結(jié)束標(biāo)記)橄霉。
dnameType dname;            使用新定義的數(shù)據(jù)類型定義變量窃爷。相當(dāng)于 char danme[20];
EXEC SQL END DELARE SECTION;    手動(dòng)轉(zhuǎn)化、變量定義都應(yīng)該放置在聲明段中。

注意：手動(dòng)轉(zhuǎn)換按厘，這里沒有使用到varchar數(shù)據(jù)類型医吊，只是char[N] 和 string 之間進(jìn)行轉(zhuǎn)換。

無論是自動(dòng)轉(zhuǎn)換還是手動(dòng)轉(zhuǎn)換刻剥，都必須滿足“可以轉(zhuǎn)換”的前提條件遮咖。

——程序比較，使用typedef造虏、EXEC SQL TYPE 與之前兩種類型，printf輸出的區(qū)別麦箍。

示例程序：

#include <stdio.h>
#include <sqlca.h>
#include <string.h>

typedef char dnameType[20];

EXEC SQL BEGIN DECLARE SECTION;//宿主變量部分
        
        char *serverid = "scott/scott@orc1";
        
        EXEC SQL TYPE dnameType is string(20);//向proc編譯器注冊
        
        int deptno;
        dnameType dname;//char dname[20];
        dnameType loc;

EXEC SQL END DECLARE SECTION;
int main(void)
{
     int ret;
     EXEC SQL CONNECT :serverid;
     if(sqlca.sqlcode != 0){
        ret = sqlca.sqlcode;
        printf("connect error:%d\n",sqlca.sqlcode);
        return ret;
    }
     printf("connect success!\n");
     
     
     deptno = 80;
     strcpy(dname,"80dname");//雙引號(hào)
     strcpy(loc,"80loc");
     EXEC SQL insert into dept(deptno,dname,loc) values(:deptno,:dname,:loc);
     if(sqlca.sqlcode != 0){
        ret = sqlca.sqlcode;
        printf("insert error:%d\n",sqlca.sqlcode);
        return ret;
     }
      EXEC SQL COMMIT;
      printf("insert success!\n");
     
     //select deptno,dname,loc from dept where deptno = 10;
     EXEC SQL select deptno,dname,loc into :deptno, :dname, :loc from dept where deptno = 80;//結(jié)果需要變量接收
     if(sqlca.sqlcode != 0){
        ret = sqlca.sqlcode;
        printf("select error:%d\n",sqlca.sqlcode);
        return ret;
    }
     printf("deptno = %d |dname = %s|loc = %s\n",deptno,dname,loc);
   /*查看編譯出來的.c文件漓藕，varchar類型被自動(dòng)轉(zhuǎn)化為一個(gè)結(jié)構(gòu)體！*/
   
   EXEC SQL COMMIT RELEASE;
   if(sqlca.sqlcode != 0){
        ret = sqlca.sqlcode;
        printf("release error:%d\n",sqlca.sqlcode);
        return ret;
     }
     printf("release success!\n");
   
   return 0;
}

1.6宿主變量和指示變量的作用

宿主變量：
通過 EXEC SQL 從程序?qū)憯?shù)據(jù)到數(shù)據(jù)庫中 ——輸入
通過 EXEC SQL 從數(shù)據(jù)庫讀數(shù)據(jù)到程序中 ——輸出

指示變量：寫入挟裂、讀出數(shù)據(jù)庫數(shù)據(jù)是一個(gè)“空值”的時(shí)候享钞。

在 DECLARE SECTION (聲明段) 中，對應(yīng)宿主變量定義指示變量：只能使用short類型诀蓉。如：short loc_ind;

loc_ind = -1;當(dāng)指示變量被賦值為-1時(shí)栗竖，執(zhí)行插入語句時(shí)，無論對應(yīng)宿主變量為何值渠啤，都直接寫入NULL狐肢，

EXEC SQL insert into dept(deptno, dname, loc) values(:deptno, :dname, :loc:loc_ind);
EXEC SQL COMMIT;    
EXEC SQL select deptno, dname, loc into :deptno, :dname, :loc:loc_ind  from dept where deptno = :deptno;
if (loc_ind == -1) {        
    strcpy(loc, "NULL");            
}

同樣，從數(shù)據(jù)庫讀取數(shù)據(jù)時(shí)沥曹， 若原表中數(shù)據(jù)為空份名，loc_ind將被置成-1。

可以根據(jù)需要妓美，將為NULL的數(shù)據(jù)設(shè)置為指定值僵腺。如：設(shè)置成字符串“NULL”

#include <stdio.h>
#include <sqlca.h>
#include <string.h>

typedef char dnameType[20];

EXEC SQL BEGIN DECLARE SECTION;//宿主變量部分
        
        char *serverid = "scott/scott@orc1";
        
        EXEC SQL TYPE dnameType is string(20);//向proc編譯器注冊
        
        int deptno;
        dnameType dname;//char dname[20];
        dnameType loc;
        short loc_ind;//定義指示變量

EXEC SQL END DECLARE SECTION;
int main(void)
{
     int ret;
     EXEC SQL CONNECT :serverid;
     if(sqlca.sqlcode != 0){
        ret = sqlca.sqlcode;
        printf("connect error:%d\n",sqlca.sqlcode);
        return ret;
    }
     printf("connect success!\n");
     
     
     deptno = 81;
     strcpy(dname,"80dname");//雙引號(hào)
     strcpy(loc,"80loc");
     loc_ind = -1;//指示變量賦值
     
     EXEC SQL insert into dept(deptno,dname,loc) values(:deptno,:dname,:loc:loc_ind);//指示變量用法
     if(sqlca.sqlcode != 0){
        ret = sqlca.sqlcode;
        printf("insert error:%d\n",sqlca.sqlcode);
        return ret;
     }
      EXEC SQL COMMIT;
      printf("insert success!\n");
      
      getchar();
     
     //select deptno,dname,loc from dept where deptno = 10;
     EXEC SQL select deptno,dname,loc into :deptno, :dname, :loc:loc_ind from dept where deptno = 81;//結(jié)果需要變量接收
     if(sqlca.sqlcode != 0){
        ret = sqlca.sqlcode;
        printf("select error:%d\n",sqlca.sqlcode);
        return ret;
     }
     if(loc_ind == -1){
        strcpy(loc,"NULL");
     }
     printf("deptno = %d |dname = %s|loc = %s\n",deptno,dname,loc);
   /*查看編譯出來的.c文件，varchar類型被自動(dòng)轉(zhuǎn)化為一個(gè)結(jié)構(gòu)體壶栋！*/
   
   EXEC SQL COMMIT RELEASE;
   if(sqlca.sqlcode != 0){
        ret = sqlca.sqlcode;
        printf("release error:%d\n",sqlca.sqlcode);
        return ret;
     }
     printf("release success!\n");
   
   return 0;
}

1.6通訊區(qū)和錯(cuò)誤處理

當(dāng) .pc → proc → .c 文件辰如，proc編譯器在編譯期間向源文件加入了一個(gè)重要區(qū)域： —— 通訊區(qū)。

ORACLE提供的兩個(gè)通信區(qū)贵试，sqlca 和 oraca琉兜。當(dāng)需要更進(jìn)一步的信息時(shí)，ORACA將幫助我們達(dá)成愿望锡移，所以O(shè)RACA也可以看作時(shí)SQLCA的補(bǔ)充和輔助呕童。

通訊區(qū)用來緩存程序信息，如程序編譯出錯(cuò)淆珊，錯(cuò)誤信息會(huì)被對應(yīng)到 sqlca.h 中的結(jié)構(gòu)體中夺饲。可以從該頭文件中找到該通訊區(qū)的結(jié)構(gòu)。

查看sqlca.h頭文件所在位置：

Linux: $ORACLE_HOME/precomp/public/sqlca.h

該頭文件實(shí)際上只包含兩個(gè)部分往声，struct sqlca 定義和初始化擂找。定義中嵌套了結(jié)構(gòu)體變量 sqlerrm：

表示 sql error message ，兩個(gè)成員變量分別表示 sql error message length 和sql error message contents

#ifndef SQLCA
#define SQLCA 1
 
struct   sqlca
         {
         /* ub1 */ char   sqlcaid[8];   //被初始化為sqlca,標(biāo)識(shí)SQL通訊區(qū)
         /* b4  */ int     sqlabc;  //SQL通訊區(qū)的長度
         /* b4  */ int     sqlcode; //最近執(zhí)行的SQL語句的狀態(tài)碼    0:正確執(zhí)行  
    //>0:執(zhí)行了語句,但沒有記錄行返回  
    //<0:數(shù)據(jù)庫,系統(tǒng),網(wǎng)絡(luò)故障,SQL語句沒有執(zhí)行
         struct
           {
           /* ub2 */ unsigned short sqlerrml;       // sqlerrmc數(shù)組的實(shí)際文本長度
           /* ub1 */ char          sqlerrmc[70];    //與sqlcode一致的對應(yīng)的錯(cuò)誤信息文本
                    //只有當(dāng)sqlcode<0才能訪問,否則是上次錯(cuò)誤信息
           } sqlerrm;

         /* ub1 */ char    sqlerrp[8];  //沒有使用
         /* b4  */ int     sqlerrd[6];  //sqlerrd[0],sqlerrd[1] , sqlerrd[3], sqlerrd[5]沒有使用
                //sqlerrd[2]SQL語句處理的行數(shù),如果SQL執(zhí)行失敗,則沒有定義
                //sqlerrd[4]出現(xiàn)語法分析錯(cuò)誤的字符開始位置,第一個(gè)位置是0
         /* ub1 */ char    sqlwarn[8];  //警告標(biāo)記

//sqlwarn[0]其他警告標(biāo)記設(shè)置,該標(biāo)記就被設(shè)置
//sqlwarn[1]字段值被截?cái)噍敵龅剿拗髯兞康臅r(shí)候被設(shè)置
//sqlwarn[2],sqlwarn[6],sqlwarn[7]沒有被使用
//sqlwarn[3]查詢字段個(gè)數(shù)不等于宿主變量個(gè)數(shù)的時(shí)候被設(shè)置
//sqlwarn[4]表中記錄被沒有where子句的delete,update處理的時(shí)候被設(shè)置
//sqlwarn[5]當(dāng)EXEC SQL CREATE{PROCDURE|FUNCTION|PACKAGE}語句編譯錯(cuò)誤的時(shí)候被設(shè)置

         /* ub1 */ char    sqlext[8]; //沒有被使用
         };

#ifndef SQLCA_NONE 
#ifdef   SQLCA_STORAGE_CLASS
SQLCA_STORAGE_CLASS struct sqlca sqlca
#else
         struct sqlca sqlca
#endif
 
#ifdef  SQLCA_INIT
         = {
         {'S', 'Q', 'L', 'C', 'A', ' ', ' ', ' '},
         sizeof(struct sqlca),
         0,
         { 0, {0}},
         {'N', 'O', 'T', ' ', 'S', 'E', 'T', ' '},
         {0, 0, 0, 0, 0, 0},
         {0, 0, 0, 0, 0, 0, 0, 0},
         {0, 0, 0, 0, 0, 0, 0, 0}
         }
#endif
         ;
#endif
 
#endif

1.6.1示例程序：

//sqlerr函數(shù)的實(shí)現(xiàn)浩销。
//EXEC SQL WHENEVER SQLERROR DO sqlerr();

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include "sqlca.h"

typedef char dnameType[20];

EXEC SQL BEGIN DECLARE SECTION;
    char *serversid = "scott/121@orcl";
    
    EXEC SQL TYPE dnameType is string(20);
    int deptno;
    dnameType dname;
    dnameType loc;
    
    short loc_ind;
    
EXEC SQL END DECLARE SECTION;

void sqlerr(void)
{
    EXEC SQL WHENEVER SQLERROR CONTINUE;
    printf("Error Reason: %.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
    EXEC SQL ROLLBACK RELEASE;
    exit(1);
}

int main(void)
{
    int ret = 0;
        
    EXEC SQL WHENEVER SQLERROR DO sqlerr();  //注冊錯(cuò)誤處理

    EXEC SQL CONNECT :serversid;
            
    printf("-----------exec here?------------\n");
    if (sqlca.sqlcode != 0) {
        ret = sqlca.sqlcode;
        printf("connect error : %d\n", ret);    
        return ret;
    }
    
    EXEC SQL select deptno, dname, loc into :deptno, :dname, :loc:loc_ind from dept where deptno = 10;
    if (loc_ind == -1) {
        strcpy(loc, "NULL");    
    }
    printf("dname:%s, loc:%s, deptno:%d\n", dname, loc, deptno);
    
    EXEC SQL COMMIT RELEASE;
    
    return 0;
}

EXEC SQL WHENEVER SQLERROR DO sqlerr();可以代替之前 EXEC SQL 語句后的if (sqlca.sqlcode != 0)

printf("Error Reason: %.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);語句中贯涎，“*”用來指定寬度，對應(yīng)一個(gè)整數(shù)慢洋√瘤ǎ“.”與后面的數(shù)合起來，指定必須輸出這個(gè)寬度普筹，如果輸出的字符串長度大于該數(shù)败明，則按此寬度輸出，如果小于太防，則輸出實(shí)際長度妻顶。

EXEC SQL WHENEVER SQLERROR CONTINUE;中“continue”的作用：“下一步”，防止遞歸蜒车。 區(qū)別于for讳嘱、while中的continue。

在CONTINUE之后的 EXEC SQL ROLLBACK RELEASE;如果出錯(cuò)酿愧，那么不會(huì)再次調(diào)用sqlerr()沥潭，而是執(zhí)行“下一步”exit(1)。

1.7宿主數(shù)組

當(dāng)查詢表中多行數(shù)據(jù)的時(shí)候寓娩，一個(gè)宿主變量不能勝任叛氨，此時(shí)應(yīng)該使用“宿主數(shù)組”道川。

char dname2[10][20];表示可以存10行dname部翘，每個(gè)dname的字符數(shù)不超過20字節(jié)

EXEC SQL select deptno, dname, loc into :deptno2, :dname2, :loc2:loc_ind from dept;

注意跟之前的宿主變量select比較材义， 這里沒有使用where限制條件抓督，可以一次查詢多行數(shù)據(jù)孩等。

如果宿主變量沒有跟隨指示變量loc_ind幕屹，查詢結(jié)果有“空值”時(shí)满葛，會(huì)出現(xiàn)SQL錯(cuò)誤：ORA-01405: fetched column value is NULL

——復(fù)習(xí)創(chuàng)建表昌罩、刪除表：
create table 表名 as select * from 數(shù)據(jù)源表名 where 1=2;
drop table 表名 purge;
——向表dept2中插入多行數(shù)據(jù)：   
EXEC SQL For :count insert into dept2(deptno, dname, loc) values(:deptno2, :dname2, :loc2:loc_ind);

count = sqlca.sqlerrd[2];           通訊區(qū)的sqlerrd[2]保存了SQL語句處理的行數(shù)阱穗。
for (i = 0; i < count; i++)
    printf("%d\t%s\t%s\n", deptno2[i], dname2[i].arr, loc2[i].arr);

注意：如果是從一張表中讀出數(shù)據(jù)饭冬，插入另外一張表，那么字符數(shù)組 dname2揪阶、loc2 應(yīng)該是varchar類型昌抠，而非char類型。

——?jiǎng)?chuàng)建表： 使用SQL創(chuàng)建一張表dept2鲁僚，格式同dept

EXEC SQL create table dept2 as select * from dept where 1=2;

示例程序：

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include "sqlca.h"

typedef char dnameType[20];     //dnameType類型

EXEC SQL BEGIN DECLARE SECTION;
    char *serversid = "scott/11@orcl";
    EXEC SQL TYPE dnameType is string(20);
    
    int count;                  //記錄rows
    
    int deptno2[10];
    varchar dname2[10][20];     //10個(gè)名字炊苫，每個(gè)名字最長20字符
    varchar loc2[10][20];
    short loc_ind[10];
    
EXEC SQL END DECLARE SECTION;

void sqlerr(void)
{
    EXEC SQL WHENEVER SQLERROR CONTINUE;
    printf("Error Reason: %.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
    EXEC SQL ROLLBACK RELEASE;
    exit(1);
}

int main(void)
{
    int i;
    
    EXEC SQL WHENEVER SQLERROR DO sqlerr();     //錯(cuò)誤處理裁厅，代替if(sqlca.sqlcode != 0)
    EXEC SQL CONNECT :serversid;                            //鏈接數(shù)據(jù)庫
    
    EXEC SQL select deptno, dname, loc into :deptno2, :dname2, :loc2:loc_ind from dept; 
        
    count = sqlca.sqlerrd[2];                   //通訊區(qū)的sqlerrd[2]保存了SQL語句處理的行數(shù)。
    printf("------SQL rows = %d\n", count);
    
    for (i = 0; i < count; i++) {
        printf("%d\t%s\t%s\n", deptno2[i], dname2[i], loc2[i]); //dname2[i]取二維數(shù)組的行
    }
    
    printf("Enter any key to create dept2 \n");
    getchar();
    getchar();
    
    //使用SQL創(chuàng)建一張表dept2侨艾，格式同dept
    EXEC SQL create table dept2 as select * from dept where 7=3;
    
    printf("Enter any key to insert into dept2 ----lots of rows\n");
    getchar();
    
    //向表中插入多行數(shù)據(jù)：
    EXEC SQL For :count insert into dept2(deptno, dname, loc) 
                        values(:deptno2, :dname2, :loc2:loc_ind);
                                                                        
    printf("----insert into dept2 finish...\n");

    EXEC SQL COMMIT RELEASE;        //提交事物并斷開連接执虹。
    
    return 0;
}

1.8普通游標(biāo)

使用宿主數(shù)組操作表：
優(yōu)點(diǎn)：整體數(shù)據(jù)操作，便捷唠梨；
缺點(diǎn)：不靈活袋励。如果表中有100000行數(shù)據(jù)，varchar dname[100000][20]不合適当叭。

游標(biāo)(cursor)：單行單行獲取數(shù)據(jù)茬故。
——所以: 不需要定義宿主數(shù)組(宿主變量即可)，也不使用 count 和 sqlca.cqlerrd[2]

使用一般步驟：

1. 定義游標(biāo)： ——理解：游標(biāo)是為某一次查詢（select）而生的蚁鳖。

EXEC SQL DECLARE dept_cursor CURSOR For select deptno, dname, loc from dept;

2. 打開游標(biāo)：

EXEC SQL OPEN dept_cursor;

打開游標(biāo)成功均牢，將做兩件事：

• 將select查詢的結(jié)果放置到內(nèi)存區(qū)。
• 將游標(biāo)指針指向第一行數(shù)據(jù)才睹。

3. 提取數(shù)據(jù)：fetch into

每取完一條數(shù)據(jù)，游標(biāo)指針自動(dòng)向下移動(dòng)一行甘邀。所以琅攘，多行數(shù)據(jù)時(shí)，應(yīng)該使用循環(huán)松邪。

while (1) {
    EXEC SQL FETCH dept_cursor INTO :deptno, :dname, :loc:loc_ind;
if (sqlca.sqlcode == 100 || sqlca.sqlcode == 1403) {  
        break;
}
    printf("%d, %s, %s\n", deptno, dname.arr, loc.arr);
//效果等同于使用宿主數(shù)組時(shí)的 EXEC SQL select ... into ... from dept;
}

100 → ANSI標(biāo)準(zhǔn) 1403 → Oracle標(biāo)準(zhǔn) 判斷是否到達(dá)數(shù)據(jù)結(jié)尾坞琴。

4. 關(guān)閉游標(biāo)：

EXEC SQL CLOSE dept_cursor;

當(dāng)使用varchar變量的arr成員時(shí)，會(huì)發(fā)生短數(shù)據(jù)名稱中包含長數(shù)據(jù)結(jié)尾的現(xiàn)象逗抑。原因是結(jié)構(gòu)體中的arr保存完數(shù)據(jù)后不會(huì)自動(dòng)清空剧辐。

varchar dname2[20]; →  proc預(yù)編譯器 → struct { unsigned short len; unsigned char arr[22]; } dname2[20];

varchar loc2[20];   →  proc預(yù)編譯器 → struct { unsigned short len; unsigned char arr[22]; } loc2[20];

可以使用memset()來手動(dòng)置空。memset(dname.arr, 0, 20);     memset(loc.arr, 0, 20);

也可以使用typedef 和 TYPE ... is string(20) 轉(zhuǎn)化為string外部數(shù)據(jù)類型邮府。

typedef char dnameType[20];
typedef char locType[20];
EXEC SQL TYPE dnameType is string(20);
EXEC SQL TYPE locType is string(20);
dnameType dname;
locType loc;
while (1) {
EXEC SQL FETCH dept_cursor INTO :deptno, :dname, :loc:loc_ind;
if (sqlca.sqlcode == 100 || sqlca.sqlcode == 1403) {  
break;
}
printf("%d\t%s\t%s\n", deptno, dname, loc);
}

示例程序

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include "sqlca.h"

typedef char dnameType[20];
typedef char locType[20];

EXEC SQL BEGIN DECLARE SECTION;
    char *serversid = "scott/scott@orc1";
    
    EXEC SQL TYPE dnameType is string(20);
    EXEC SQL TYPE locType is string(20);
    
    int deptno;
    dnameType dname;
    locType loc;
    short loc_ind;      
    
EXEC SQL END DECLARE SECTION;

void sqlerr(void)
{
    EXEC SQL WHENEVER SQLERROR CONTINUE;
    printf("Error Reason: %.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
    EXEC SQL ROLLBACK RELEASE;
    exit(1);
}


int main(void)
{
    int ret = 0;
    
    deptno = 50;
    strcpy(dname,"Engineer");
    strcpy(loc,"BeiJing");
    
    EXEC SQL WHENEVER SQLERROR DO sqlerr();     //錯(cuò)誤處理荧关，代替if(sqlca.sqlcode != 0)
    EXEC SQL CONNECT :serversid;                            //鏈接數(shù)據(jù)庫 
        
    EXEC SQL insert into dept(deptno, dname, loc) values(:deptno, :dname, :loc);

    printf("insert ok...\n");
    
        
    //1. 定義游標(biāo)   
    EXEC SQL DECLARE dept_cursor CURSOR for select * from dept; 
    
    //2. 打開游標(biāo)
    EXEC SQL OPEN dept_cursor;          
    
    //3. 提取數(shù)據(jù)
    while (1) {
        EXEC SQL FETCH dept_cursor INTO :deptno, :dname, :loc:loc_ind;      
            /*
            沒有commit為什么能查到新插入的數(shù)據(jù)？
            我的所有操作都在同一個(gè)事務(wù)當(dāng)中褂傀，所以我可以查詢得到
            
            如果是另外一個(gè)事務(wù)要查詢忍啤，這里必須是commit，它才可以查詢得到
            
            */
        if (sqlca.sqlcode == 100 || sqlca.sqlcode == 1403) {
            break;
        }   
        printf("%d\t%s\t%s\n", deptno, dname, loc);
    }   
    //4. 關(guān)閉游標(biāo)
    EXEC SQL CLOSE dept_cursor;     
    EXEC SQL COMMIT RELEASE;                                    //提交事物并斷開連接仙辟。
    return 0;
}

2.動(dòng)態(tài)SQL

• 靜態(tài)SQL：
  如果將SQL語句直接寫死到程序中同波，那么proc、gcc編譯完成生成可執(zhí)行程序叠国，不能再進(jìn)行修改未檩。
• 動(dòng)態(tài)SQL：
  程序運(yùn)行起來以后，再進(jìn)行輸入SQL語句（SQL語句生成的時(shí)間晚于應(yīng)用程序）粟焊。 sqlplus使用大量的動(dòng)態(tài)SQL 所以冤狡，含有動(dòng)態(tài)SQL的程序要解析所有的用戶輸入/輸出：

select  ....
form tab1, tab2 ...
where ...
order by...
group by...
having

動(dòng)態(tài)SQL分為4種孙蒙，前面三種作為第4種的基礎(chǔ)。

第4種動(dòng)態(tài)SQL有兩種實(shí)現(xiàn)方式：

1. ANSI標(biāo)準(zhǔn)
2. Oracle自定義

應(yīng)用場景：公司內(nèi)部proc項(xiàng)目組筒溃，為公司提供企業(yè)的马篮、通用的proc API接口。(自定義API函數(shù))

2.1動(dòng)態(tài)SQL1

非select語言
可以執(zhí)行insert怜奖、update浑测、delete這樣的非select語句，這些語句的特點(diǎn)是歪玲，沒有select結(jié)果集迁央。
執(zhí)行用戶輸入的SQL語句(保存在pSql中)：

EXEC SQL EXECUTE IMMEDIATE :pSql;

注：測試 動(dòng)態(tài)sql_1 時(shí)，insert into dept values(68, '68name', '68loc')之后不要加 “ 滥崩；”程序中未添加結(jié)束標(biāo)記判定岖圈。

---------------------------顯示錯(cuò)誤SQL語句函數(shù)sqlgls----------------------------
sqlerr02 sqlgls(char *, size_t *， size_t *) → .../public/sqlcpr.h
-------------------------------------------------------------------------------------
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include "sqlca.h"
#include "oraca.h"
                
extern sqlgls(char * , size_t *, size_t * );
void connet();

void sqlerr02()
{
    char    stm[120];
    size_t  sqlfc, stmlen=120;
    unsigned int ret = 0;
    
    //出錯(cuò)時(shí),可以把錯(cuò)誤SQL語言給打印出來
    EXEC SQL WHENEVER SQLERROR CONTINUE;
    
    ret = sqlgls(stm, &stmlen, &sqlfc); 
    printf("出錯(cuò)的SQL:%.*s\n", stmlen, stm);
    printf("出錯(cuò)原因:%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
    EXEC SQL ROLLBACK WORK RELEASE;
    exit(1);
}

void nodata(void)
{
    int ret = 0;
    printf("沒有發(fā)現(xiàn)數(shù)據(jù)\n");
    if (sqlca.sqlcode != 0) {
        ret = sqlca.sqlcode;
        printf("sqlca.sqlcode: err:%d \n", sqlca.sqlcode);
        return ;
    }
}

typedef char dnameType[20];
typedef char locType[20];

EXEC SQL BEGIN DECLARE SECTION;
    EXEC SQL TYPE dnameType is string(20);
    EXEC SQL TYPE locType is string(20);
    char    *usrname = "scott";
    char    *passwd = "scott";
    char    *serverid = "orc1";
    
    int             deptno;
    dnameType       dname; 
    short           dname_ind;
    locType         loc;
    short           loc_ind;
        
    char            mySql[1024];
    char            *pSql;
EXEC SQL END DECLARE SECTION;

/*動(dòng)態(tài)sql_1
  insert into dept values(69, '69name', '69loc')  沒有結(jié)果集
  非select語言,無占位符變量
*/
void connet(void)
{
    int ret = 0;
    //連接數(shù)據(jù)庫
    EXEC SQL CONNECT:usrname IDENTIFIED BY:passwd USING:serverid ;
    if (sqlca.sqlcode != 0)
    {
        ret = sqlca.sqlcode;
        printf("sqlca.sqlcode: err:%d \n", sqlca.sqlcode);
        return ;
    } else {
      printf("connect ok...\n");
    }
}

int main(void)
{
    int ret = 0;    
    int i = 0;
    char choosechar;

    memset(mySql, 0, sizeof(mySql));        //緩沖區(qū)清空
    pSql = NULL;
    
    EXEC SQL WHENEVER SQLERROR DO sqlerr02();
    connet();
    EXEC SQL WHENEVER NOT FOUND DO nodata();
    
    pSql = mySql;               //指針指向緩沖區(qū)
    //循環(huán)處理sql語言
    for(;;)
    {
        printf("\nPlease enter sql(not select and no ';'): ");
        gets(mySql);
        //scanf("%s", mySql);        --不宜使用钙皮，因?yàn)榭崭窠財(cái)?        printf("mysql:%s\n", mySql);

        EXEC SQL EXECUTE IMMEDIATE :pSql;//pSql正指向mySql
            //> update dept set loc= 'locloc' where deptno=62    
            
        EXEC SQL COMMIT;
        printf("繼續(xù)執(zhí)行嗎?\n");
        scanf("%c", &choosechar);
        fflush(stdin);
        
        if (choosechar=='n' || choosechar=='N')
        {
        break;
        }
    }

    EXEC SQL COMMIT WORK RELEASE;//WORK可以省略
    printf("return ok...\n");

    return ret ;
}

2.2動(dòng)態(tài)SQL2

非select語言蜂科，可綁定變量(占位符)
相較于動(dòng)態(tài)SQL1，可以使用戶在輸入SQL語句時(shí)有機(jī)會(huì)指定a短条、b兩處的值导匣，這兩個(gè)占位符得值將綁定到宿主變量。

• 準(zhǔn)備動(dòng)態(tài)SQL:
  EXEC SQL PREPARE my_pre_sql FROM 'update dept set loc = :a where deptno = :b';

• 執(zhí)行動(dòng)態(tài)SQL：
  EXEC SQL EXECUTE my_pre_sql USING :loc, :deptno;//關(guān)鍵是using

/*初始化：同 1例*/

/*動(dòng)態(tài)sql2 

  非查詢語言, 可以帶固定數(shù)量的宿主變量
  使用內(nèi)嵌PREPARE命令準(zhǔn)備SQL語言
  有2步驟:
1. PREPARE語法
    EXEC SQL PREPARE statement_name FROM {:host_string | string_literal};
    意為：準(zhǔn)備一個(gè)SQL語句statement_name, 該語句包含占位符茸时。但尚未執(zhí)行贡定。 
    PREPARE是一個(gè)預(yù)編譯器標(biāo)識(shí)符,而不是宿主變量。

2. USING子句
    EXEC SQL EXECUTE statement_name [USING :host_variable1[:indicator1] [, :host_variable2[:indicator2] ...  ] ;
    意為：使用準(zhǔn)備好的SQL語句statement_name, 同時(shí)使用宿主變量
*/
int main(void)
{
    int ret = 0;    
    int i = 0;
    char choosechar;

    memset(mySql, 0, sizeof(mySql));
    pSql = NULL;
    
    EXEC SQL WHENEVER SQLERROR DO sqlerr02();
    connet();
    EXEC SQL WHENEVER NOT FOUND DO nodata();
    
    pSql = mySql;
    //循環(huán)處理sql語句
    for(;;)
    {
    printf("\update ur new deptno ");
    scanf("%d", &deptno);
        
    printf("\nupdate ur new loc ");
    scanf("%s", loc);
        
//準(zhǔn)備動(dòng)態(tài)sql
    EXEC SQL PREPARE my_pre_sql FROM 'update dept set loc = :a where deptno = :b';
    //執(zhí)行動(dòng)態(tài)sql可都，并使用宿主變量   :a :b相當(dāng)于留坑缓待！等著下面填
    EXEC SQL EXECUTE my_pre_sql USING :loc, :deptno;  //:a = loc  :b = deptno
    
    EXEC SQL COMMIT;
        
    printf("\n press any key to continue ");
    getchar();
    printf("\npress n to log out ");
    scanf("%c", &choosechar);
    fflush(stdin);
        
    if (choosechar=='n' || choosechar=='N') {
    break;
    }
    }
    EXEC SQL COMMIT WORK RELEASE;
    printf("return ok...\n");

    return ret ;
}

2.3

可以處理select返回回來的結(jié)果集 (有限個(gè)數(shù))。

可以 select 語句渠牲。但：
輸入宿主變量個(gè)數(shù)固定旋炒，查詢條件固定。
輸出宿主變量個(gè)數(shù)固定嘱兼，返回結(jié)果固定国葬。

在sqlplus中查詢dept表可以返回3列，查詢emp表可以返回多列芹壕，而在動(dòng)態(tài)SQL3中只能查詢一種固定列數(shù)的結(jié)果集汇四。
EXEC SQL PREPARE my_pre_sql3 FROM 'select deptno, dname, loc from dept where deptno > :a';

相當(dāng)于給 select deptno, dname, loc from dept where deptno > :a起了一個(gè)別名 my_pre_sql3，帶有占位符踢涌，用來給用戶輸入條件通孽。無論指定幾個(gè)占位符，編譯完成睁壁，用戶一但輸入背苦，查詢條件即固定互捌。同樣，輸出的列數(shù)也是固定的行剂。deptno秕噪，dname，loc

動(dòng)態(tài)sql_3是將 PREPARE 與 游標(biāo) 結(jié)合在一起使用.

/*動(dòng)態(tài)sql_3 

  查詢部門號(hào)大于10的所有部門信息
  處理選擇列表項(xiàng)(select查詢出來的結(jié)果列數(shù)固定) 和 輸入宿主變量個(gè)數(shù)一定

  
本質(zhì):     輸入宿主變量個(gè)數(shù)固定  查詢條件固定  
            輸出宿主變量個(gè)數(shù)固定  返回結(jié)果固定
  語法特征：結(jié)合游標(biāo)一起使用
*/

int main(void)
{
    int  ret = 0;   
    char choosechar;

    memset(mySql, 0, sizeof(mySql));
    pSql = NULL;
    
    EXEC SQL WHENEVER SQLERROR DO sqlerr02();
    connet();
    EXEC SQL WHENEVER NOT FOUND DO nodata();

    //循環(huán)處理sql語言
    for(;;)
    {
    printf("\n請輸入部門編號(hào) ");
    scanf("%d", &deptno);
    
    //準(zhǔn)備動(dòng)態(tài)sql厚宰，在SQL2中用到
    EXEC SQL PREPARE my_pre_sql3 FROM 'select deptno, dname, loc from dept where deptno > :a';
        
    //定義游標(biāo)c1腌巾，為某一次查詢
  EXEC SQL DECLARE c1 CURSOR FOR my_pre_sql3;
        
    //打開游標(biāo)c1，使用占位符指定的宿主變量
    EXEC SQL OPEN c1 USING :deptno;//USING相當(dāng)于填坑操作铲觉，位置不固定澈蝙，只要在proc語句中
            
    //相當(dāng)于下面循環(huán)中加入了： if (sqlca.sqlcode == 100 || sqlca.sqlcode == 1403)
    EXEC SQL WHENEVER NOT FOUND DO break;
        
    //提取數(shù)據(jù)
    for (;;) {
    //當(dāng)游標(biāo)查詢到結(jié)果時(shí)，將數(shù)據(jù)fetch into到宿主變量中撵幽。處理完一行灯荧，游標(biāo)下移處理下一行
    EXEC SQL FETCH c1 INTO :deptno, :dname, :loc:loc_ind;
    printf("%d\t %s\t %s \n", deptno, dname, loc); 
    }
    //關(guān)閉游標(biāo)
    EXEC SQL CLOSE c1;
        
    EXEC SQL COMMIT;
        
    printf("\n 按任意鍵繼續(xù)? ");
    getchar();
    printf("\n鍵入 n 退出, 其他繼續(xù)? ");
    scanf("%c", &choosechar);
    fflush(stdin);
        
    if (choosechar == 'n' || choosechar == 'N') {
break;
    }
    }

    EXEC SQL COMMIT WORK RELEASE;
    printf("return ok...\n");

    return ret ;
}   

2.4動(dòng)態(tài)SQL4

2.4.1ANSI標(biāo)準(zhǔn)方式

按照ANSI組織定義的標(biāo)準(zhǔn)實(shí)現(xiàn)的動(dòng)態(tài)SQL。掌握程度：理解程序架構(gòu)即可盐杂。

讀程序思考：

1. 如何處理select語句 和 非select語句逗载。→ 二者最大區(qū)別在于：應(yīng)用程序是否能處理select的結(jié)果集链烈。
   找到程序中處理結(jié)果集的代碼撕贞。區(qū)分非處理結(jié)果集代碼。
2. 輸入描述區(qū) 和 輸出描述區(qū)测垛。
3. 如何處理輸入描述區(qū)
4. 如何處理輸出描述區(qū)（如何一行一行的解析數(shù)據(jù)庫返回的數(shù)據(jù)集）

#include <stdio.h>  
#include <stdlib.h> 
#include <string.h>  
#include <sqlca.h>  

/* 定義綁定變量值 和 選擇列表項(xiàng)值的最大長度 
 * 綁定變量：類似于在SQL語句中輸入的“&”占位符。
 */ 
#define MAX_VAR_LEN     30 

/* 定義選擇列表項(xiàng)名的最大長度 */  
#define MAX_NAME_LEN    31  

/* 定義宿主變量 */  
exec sql begin declare section; 
    char    *usrname = "scott";
    char    *passwd = "11";
    char    *serverid = "orcl"; 
    char    sql_stat[100];  
    char    current_date[20];  
exec sql end declare section;  
  
void sql_error(void);  
void connet(void);
void process_input(void);  
void process_output(void);  
  
int main(void)  
{  
    /* 安裝錯(cuò)誤處理句柄 */  
exec sql whenever sqlerror do sql_error();  
  
    /* 連接到數(shù)據(jù)庫 */  
    connet();  
  
    /* 
     * 分配輸入描述區(qū)和輸出描述區(qū) 
     * Ansi定義了該套標(biāo)準(zhǔn)秧均，proc程序開發(fā)者按標(biāo)準(zhǔn)實(shí)現(xiàn)了它
     * 這兩條語句在proc編譯的時(shí)候會(huì)開辟對應(yīng)大小的存儲(chǔ)空間
     */  
    exec sql allocate descriptor 'input_descriptor';            //用來存儲(chǔ)輸入的宿主變量
    exec sql allocate descriptor 'output_descriptor';       //用來緩存數(shù)據(jù)庫端返回的結(jié)果集 
  
    for ( ; ; ) {  
        printf("\n請輸入動(dòng)態(tài)SQL語句(EXIT:退出):\n");  
        gets(sql_stat);  
  
        /* EXIT(exit)->退出 */  
        if(0 == strncmp(sql_stat , "EXIT" , 4) || 0 == strncmp(sql_stat , "exit" , 4))  
            break;  
  
        /* 準(zhǔn)備動(dòng)態(tài)SQL語句 */  
        exec sql prepare s from :sql_stat;  
  
        /* 定義游標(biāo) */  
        exec sql declare c cursor for s;  
  
        /* 處理綁定變量 食侮， 即處理占位符 “&” */  
        process_input();  
  
        /* 
         * 打開游標(biāo)成功 意寓著結(jié)果已經(jīng)被保存到輸出描述區(qū)了。
         * select語句：處理查詢結(jié)果 
         * 其他SQL語句：執(zhí)行 
         */  
        exec sql open c using descriptor 'input_descriptor';  
        if(0 == strncmp(sql_stat , "SELECT" , 6) , 0 == strncmp(sql_stat , "select" , 6)) {  
    process_output();
        }  
        /* 關(guān)閉游標(biāo) */  
        exec sql close c;  
    }  
  
    /* 釋放輸入描述區(qū)和輸出描述區(qū) */  
    exec sql deallocate descriptor 'input_descriptor';  
    exec sql deallocate descriptor 'output_descriptor';  
  
    /* 提交事務(wù)目胡，斷開連接 */  
    exec sql commit work release;  
    puts("謝謝使用ANSI動(dòng)態(tài)SQL!\n");  
  
    return 0;  
}  
  
void sql_error(void)  
{  
    /* 顯示SQL錯(cuò)誤號(hào)锯七、錯(cuò)誤描述 */  
    printf("%.*s\n" , sqlca.sqlerrm.sqlerrml , sqlca.sqlerrm.sqlerrmc);  
    exit(1);
}  

void process_input(void)  
{  
    int i;  
  
    /* 定義宿主變量 */  
    exec sql begin declare section;  
        int input_count;  
        int input_type ;  
        int input_len;  
        char input_buffer[MAX_VAR_LEN];  
        char name[MAX_NAME_LEN];  
        int occurs;  
    exec sql end declare section;  
  
    /* 綁定變量->輸入描述區(qū) */  
    exec sql describe input s using descriptor 'input_descriptor';  
  
    /* 取得綁定變量個(gè)數(shù) */  
    exec sql get descriptor 'input_descriptor' :input_count = count;  
  
    /* 循環(huán)處理綁定變量名 */  
    for(i = 0 ; i != input_count ; ++i) {  
        occurs = i + 1;  
  
        /* 取得綁定變量名 */  
        exec sql get descriptor 'input_descriptor' value :occurs :name = name;  
        printf("請輸入%s的值：" , name);  
        gets(input_buffer);  
  
        /* 以NULL結(jié)尾 */  
        input_len = strlen(input_buffer);  
        input_buffer[input_len] = '\0';  
  
        /* 設(shè)置綁定變量類型、長度和值 */  
        input_type = 1;  
        exec sql set descriptor 'input_descriptor' value :occurs   
            type = :input_type , length = :input_len , data = :input_buffer;  
    }  
}  
  
void process_output(void)  
{  
    int i;  
  
    // 定義宿主變量  
    EXEC SQL BEGIN DECLARE SECTION ;  
        int output_count;  
        int output_type;  
        int output_len;  
        char output_buffer[MAX_VAR_LEN];
        short  output_indicator;  
        char name[MAX_NAME_LEN];  
        int occurs;  
    EXEC SQL END DECLARE SECTION ;  
  
    // 選擇列表項(xiàng)->輸出描述區(qū) 
    exec sql describe output s using descriptor 'output_descriptor';  
  
    //取得選擇列表項(xiàng)個(gè)數(shù)  
    exec sql get descriptor 'output_descriptor' :output_count = count;  
  
    //循環(huán)處理選擇列表項(xiàng)(即列名誉己，或者叫表頭) 
    
    output_type = 12;  //設(shè)置類型為變長字符串varchar
     
    for(i = 0 ; i != output_count ; ++i) {  
        occurs = i + 1;  
        output_len = MAX_VAR_LEN;  
      
        // 設(shè)置選擇列表項(xiàng)的類型和長度(設(shè)置每一列眉尸，按照varchar類型進(jìn)行顯示) 
        exec sql set descriptor 'output_descriptor' value :occurs   
                        type = :output_type , length = :output_len;  
        //取得選擇列表項(xiàng)的名稱并輸出 
        exec sql get descriptor 'output_descriptor' value :occurs :name = name;  
            
        //顯示選擇列表項(xiàng)名稱  
        printf("\t%s" , name);  
    }  
    printf("\n");  
  
    // 提取數(shù)據(jù)完畢->退出循環(huán)  
    exec sql whenever not found do break;  
  
    // 循環(huán)處理選擇列表項(xiàng)數(shù)據(jù)  
    for ( ; ; ) {  
        // 行數(shù)據(jù)-> fetch into 利用游標(biāo)從輸出描述區(qū)讀取數(shù)據(jù)。 
        exec sql fetch c into descriptor 'output_descriptor';  
           
        // 循環(huán)處理每列數(shù)據(jù)  
        for (i = 0 ; i < output_count ; ++i) {  
            occurs = i + 1;  
           
            // 取得列數(shù)據(jù)和指示變量值  
            exec sql get descriptor 'output_descriptor' VALUE :occurs  
                :output_buffer = DATA , :output_indicator = INDICATOR;  
  
            //輸出列數(shù)據(jù)   
            if (-1 == output_indicator)  
               printf("\t%s", "   ");         
            else  
               printf("\t%s" , output_buffer);        
        }   
        printf("\n"); 
    }  
}

void connet(void)
{
    int ret = 0;
    //連接數(shù)據(jù)庫
    EXEC SQL CONNECT:usrname IDENTIFIED BY:passwd USING:serverid ;
    if (sqlca.sqlcode != 0) {
    ret = sqlca.sqlcode;
    printf("sqlca.sqlcode: err:%d \n", sqlca.sqlcode);
    return ;
    } else {
    printf("connect ok...\n");
    }
}

2.4.2Oracle實(shí)現(xiàn)方式

/*******************************************************************
Sample Program 10:  Dynamic SQL Method 4

This program connects you to ORACLE using your username and
password, then prompts you for a SQL statement.  You can enter
any legal SQL statement.  Use regular SQL syntax, not embedded SQL.
Your statement will be processed.  If it is a query, the rows
fetched are displayed.
You can enter multiline statements.  The limit is 1023 characters.
This sample program only processes up to MAX_ITEMS bind variables and
MAX_ITEMS select-list items.  MAX_ITEMS is #defined to be 40.
*******************************************************************/

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

#define SQL_SINGLE_RCTX  ((void *)0) 

/* Maximum number of select-list items or bind variables. */
#define MAX_ITEMS         40

/* Maximum lengths of the _names_ of the
   select-list items or indicator variables. */
#define MAX_VNAME_LEN     30
#define MAX_INAME_LEN     30

#ifndef NULL
#define NULL  0
#endif

/* Prototypes */
#if defined(__STDC__)
  void sql_error(void);
  int oracle_connect(void);
  int alloc_descriptors(int, int, int);
  int get_dyn_statement(void);
  void set_bind_variables(void);
  void process_select_list(void);
  void help(void);
#else
  void sql_error(/*_ void _*/);
  int oracle_connect(/*_ void _*/);
  int alloc_descriptors(/*_ int, int, int _*/);
  int get_dyn_statement(/* void _*/);
  void set_bind_variables(/*_ void -*/);
  void process_select_list(/*_ void _*/);
  void help(/*_ void _*/);
#endif

char *dml_commands[] = {"SELECT", "select", "INSERT", "insert",
                        "UPDATE", "update", "DELETE", "delete"};

EXEC SQL INCLUDE sqlda;
EXEC SQL INCLUDE sqlca;

EXEC SQL BEGIN DECLARE SECTION;
    char    dyn_statement[1024];
    EXEC SQL VAR dyn_statement IS STRING(1024);
EXEC SQL END DECLARE SECTION;
 
SQLDA *bind_dp;
SQLDA *select_dp;

/* Define a buffer to hold longjmp state info. */
jmp_buf jmp_continue;
/* A global flag for the error routine. */
int parse_flag = 0;

void main()
{
    int i;

    /* Connect to the database. */
    if (oracle_connect() != 0)
        exit(1);

    /* Allocate memory for the select and bind descriptors. */
    if (alloc_descriptors(MAX_ITEMS, MAX_VNAME_LEN, MAX_INAME_LEN) != 0)
        exit(1);

    /* Process SQL statements. */
    for (;;) 
    {
        (void) setjmp(jmp_continue);

        /* Get the statement.  Break on "exit". */
        if (get_dyn_statement() != 0)
            break;

        /* Prepare the statement and declare a cursor. */
        EXEC SQL WHENEVER SQLERROR DO sql_error();

        parse_flag = 1;     /* Set a flag for sql_error(). */
        EXEC SQL PREPARE S FROM :dyn_statement;
        parse_flag = 0;     /* Unset the flag. */

        EXEC SQL DECLARE C CURSOR FOR S;

        /* Set the bind variables for any placeholders in the
           SQL statement. */
        set_bind_variables();

        /* Open the cursor and execute the statement.
         * If the statement is not a query (SELECT), the
         * statement processing is completed after the
         * OPEN.
         */

        EXEC SQL OPEN C USING DESCRIPTOR bind_dp;

        /* Call the function that processes the select-list.
         * If the statement is not a query, this function
         * just returns, doing nothing.
         */
        process_select_list();

        /* Tell user how many rows processed. */
        for (i = 0; i < 8; i++)
        {
           if (strncmp(dyn_statement, dml_commands[i], 6) == 0)
           {
               printf("\n\n%d row%c processed.\n", sqlca.sqlerrd[2],
                       sqlca.sqlerrd[2] == 1 ? '\0' : 's');
               break;
           }
        }
    }       /* end of for(;;) statement-processing loop */

    /* When done, free the memory allocated for
       pointers in the bind and select descriptors. */
    for (i = 0; i < MAX_ITEMS; i++)
    {    
        if (bind_dp->V[i] != (char *) 0)
            free(bind_dp->V[i]);
        free(bind_dp->I[i]);   /* MAX_ITEMS were allocated. */
        if (select_dp->V[i] != (char *) 0)
            free(select_dp->V[i]);
        free(select_dp->I[i]); /* MAX_ITEMS were allocated. */
    }

    /* Free space used by the descriptors themselves. */
    SQLSQLDAFree(SQL_SINGLE_RCTX, bind_dp);
    SQLSQLDAFree(SQL_SINGLE_RCTX, select_dp);

    EXEC SQL WHENEVER SQLERROR CONTINUE;
    /* Close the cursor. */
    EXEC SQL CLOSE C;

    EXEC SQL COMMIT WORK RELEASE;
    puts("\nHave a good day!\n");

    EXEC SQL WHENEVER SQLERROR DO sql_error();
    return;
}

int oracle_connect()
{
    EXEC SQL BEGIN DECLARE SECTION;
        VARCHAR  username[128];
        VARCHAR  password[32];
    EXEC SQL END DECLARE SECTION;

    printf("\nusername: ");
    fgets((char *) username.arr, sizeof username.arr, stdin);
    username.arr[strlen((char *) username.arr)-1] = '\0';
    username.len = (unsigned short)strlen((char *) username.arr);

    printf("password: ");
    fgets((char *) password.arr, sizeof password.arr, stdin);
    password.arr[strlen((char *) password.arr) - 1] = '\0';
    password.len = (unsigned short)strlen((char *) password.arr);


    EXEC SQL WHENEVER SQLERROR GOTO connect_error;

    EXEC SQL CONNECT :username IDENTIFIED BY :password;

    printf("\nConnected to ORACLE as user %s.\n", username.arr);
    return 0;

connect_error:
    fprintf(stderr, "Cannot connect to ORACLE as user %s\n", username.arr);
    return -1;
}


/*
 *  Allocate the BIND and SELECT descriptors using SQLSQLDAAlloc().
 *  Also allocate the pointers to indicator variables
 *  in each descriptor.  The pointers to the actual bind
 *  variables and the select-list items are realloc'ed in
 *  the set_bind_variables() or process_select_list()
 *  routines.  This routine allocates 1 byte for select_dp->V[i]
 *  and bind_dp->V[i], so the realloc will work correctly.
 */

alloc_descriptors(size, max_vname_len, max_iname_len)
int size;
int max_vname_len;
int max_iname_len;
{
    int i;

    /*
     * The first SQLSQLDAAlloc parameter is the runtime context.

     * The second parameter determines the maximum number of
     * array elements in each variable in the descriptor. In
     * other words, it determines the maximum number of bind
     * variables or select-list items in the SQL statement.
     *
     * The third parameter determines the maximum length of
     * strings used to hold the names of select-list items
     * or placeholders.  The maximum length of column 
     * names in ORACLE is 30, but you can allocate more or less
     * as needed.
     *
     * The fourth parameter determines the maximum length of
     * strings used to hold the names of any indicator
     * variables.  To follow ORACLE standards, the maximum
     * length of these should be 30.  But, you can allocate
     * more or less as needed.
     */

    if ((bind_dp =
       SQLSQLDAAlloc(SQL_SINGLE_RCTX, size, max_vname_len, max_iname_len)) == 
         (SQLDA *) 0)
    {
        fprintf(stderr,
            "Cannot allocate memory for bind descriptor.");
        return -1;  /* Have to exit in this case. */
    }

    if ((select_dp =
        SQLSQLDAAlloc (SQL_SINGLE_RCTX, size, max_vname_len, max_iname_len)) == 
           (SQLDA *) 0)
    {
        fprintf(stderr,
            "Cannot allocate memory for select descriptor.");
        return -1;
    }
    select_dp->N = MAX_ITEMS;

    /* Allocate the pointers to the indicator variables, and the
       actual data. */
    for (i = 0; i < MAX_ITEMS; i++) {
        bind_dp->I[i] = (short *) malloc(sizeof (short));
        select_dp->I[i] = (short *) malloc(sizeof(short));
        bind_dp->V[i] = (char *) malloc(1);
        select_dp->V[i] = (char *) malloc(1);
    }
       
    return 0;
}

int get_dyn_statement()
{
    char *cp, linebuf[256];
    int iter, plsql;


    for (plsql = 0, iter = 1; ;)
    {
        if (iter == 1)
        {
            printf("\nSQL> ");
            dyn_statement[0] = '\0';
        }
        
        fgets(linebuf, sizeof linebuf, stdin);

        cp = strrchr(linebuf, '\n');
        if (cp && cp != linebuf)
            *cp = ' ';
        else if (cp == linebuf)
            continue;

        if ((strncmp(linebuf, "EXIT", 4) == 0) ||
            (strncmp(linebuf, "exit", 4) == 0))
        {
            return -1;
        }

        else if (linebuf[0] == '?' ||
            (strncmp(linebuf, "HELP", 4) == 0) ||
            (strncmp(linebuf, "help", 4) == 0))
        {
            help();
            iter = 1;
            continue;
        }

        if (strstr(linebuf, "BEGIN") ||
            (strstr(linebuf, "begin")))
        {
            plsql = 1;
        }

        strcat(dyn_statement, linebuf);

        if ((plsql && (cp = strrchr(dyn_statement, '/'))) ||
            (!plsql && (cp = strrchr(dyn_statement, ';'))))
        {
            *cp = '\0';
            break;
        }
        else
        {
            iter++;
            printf("%3d  ", iter);
        }
    }
    return 0;
}

void set_bind_variables()
{
    int i, n;
    char bind_var[64];

    /* Describe any bind variables (input host variables) */
    EXEC SQL WHENEVER SQLERROR DO sql_error();

    bind_dp->N = MAX_ITEMS;  /* Initialize count of array elements. */
    EXEC SQL DESCRIBE BIND VARIABLES FOR S INTO bind_dp;

    /* If F is negative, there were more bind variables
       than originally allocated by SQLSQLDAAlloc(). */
    if (bind_dp->F < 0) {
        printf ("\nToo many bind variables (%d), maximum is %d\n.",
                    -bind_dp->F, MAX_ITEMS);
        return;
    }

    /* Set the maximum number of array elements in the
       descriptor to the number found. */
    bind_dp->N = bind_dp->F;
 
    /* Get the value of each bind variable as a
     * character string.
     *   
     * C[i] contains the length of the bind variable
     *      name used in the SQL statement.
     * S[i] contains the actual name of the bind variable
     *      used in the SQL statement.
     *
     * L[i] will contain the length of the data value
     *      entered.
     *
     * V[i] will contain the address of the data value
     *      entered.
     *
     * T[i] is always set to 1 because in this sample program
     *      data values for all bind variables are entered
     *      as character strings.
     *      ORACLE converts to the table value from CHAR.
     *
     * I[i] will point to the indicator value, which is
     *      set to -1 when the bind variable value is "null".
     */
    for (i = 0; i < bind_dp->F; i++) {
        printf ("\nEnter value for bind variable %.*s:  ",
               (int)bind_dp->C[i], bind_dp->S[i]);
        fgets(bind_var, sizeof bind_var, stdin);

        /* Get length and remove the new line character. */
        n = strlen(bind_var) - 1;

        /* Set it in the descriptor. */
        bind_dp->L[i] = n;

        /* (re-)allocate the buffer for the value.
           SQLSQLDAAlloc() reserves a pointer location for
           V[i] but does not allocate the full space for
           the pointer. */

         bind_dp->V[i] = (char *) realloc(bind_dp->V[i],
                         (bind_dp->L[i] + 1));            

        /* And copy it in. */
        strncpy(bind_dp->V[i], bind_var, n);

        /* Set the indicator variable's value. */
        if ((strncmp(bind_dp->V[i], "NULL", 4) == 0) ||
                (strncmp(bind_dp->V[i], "null", 4) == 0))
            *bind_dp->I[i] = -1;
        else
            *bind_dp->I[i] = 0;
    
        /* Set the bind datatype to 1 for CHAR. */
        bind_dp->T[i] = 1;
    }
  return;
}

void process_select_list()
{
int i, null_ok, precision, scale;

    if ((strncmp(dyn_statement, "SELECT", 6) != 0) &&
        (strncmp(dyn_statement, "select", 6) != 0))
    {
        select_dp->F = 0;
        return;
    }

    /* If the SQL statement is a SELECT, describe the
        select-list items.  The DESCRIBE function returns
        their names, datatypes, lengths (including precision
        and scale), and NULL/NOT NULL statuses. */

    select_dp->N = MAX_ITEMS;
    
    EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;

    /* If F is negative, there were more select-list
       items than originally allocated by SQLSQLDAAlloc(). */
    if (select_dp->F < 0)
    {
        printf ("\nToo many select-list items (%d), maximum is %d\n",
                -(select_dp->F), MAX_ITEMS);
        return;
    }

    /* Set the maximum number of array elements in the
       descriptor to the number found. */
    select_dp->N = select_dp->F;

    /* Allocate storage for each select-list item.
  
       SQLNumberPrecV6() is used to extract precision and scale
       from the length (select_dp->L[i]).

       sqlcolumnNullCheck() is used to reset the high-order bit of
       the datatype and to check whether the column
       is NOT NULL.

       CHAR    datatypes have length, but zero precision and
               scale.  The length is defined at CREATE time.

       NUMBER  datatypes have precision and scale only if
               defined at CREATE time.  If the column
               definition was just NUMBER, the precision
               and scale are zero, and you must allocate
               the required maximum length.

       DATE    datatypes return a length of 7 if the default
               format is used.  This should be increased to
               9 to store the actual date character string.
               If you use the TO_CHAR function, the maximum
               length could be 75, but will probably be less
               (you can see the effects of this in SQL*Plus).

       ROWID   datatype always returns a fixed length of 18 if
               coerced to CHAR.

       LONG and
       LONG RAW datatypes return a length of 0 (zero),
               so you need to set a maximum.  In this example,
               it is 240 characters.

       */
    
    printf ("\n");
    for (i = 0; i < select_dp->F; i++)
    {
        char title[MAX_VNAME_LEN]; 
        /* Turn off high-order bit of datatype (in this example,
           it does not matter if the column is NOT NULL). */
        SQLColumnNullCheck (0, (unsigned short *)&(select_dp->T[i]), 
             (unsigned short *)&(select_dp->T[i]), &null_ok);

        switch (select_dp->T[i])
        {
            case  1 : /* CHAR datatype: no change in length
                         needed, except possibly for TO_CHAR
                         conversions (not handled here). */
                break;
            case  2 : /* NUMBER datatype: use SQLNumberPrecV6() to
                         extract precision and scale. */
                SQLNumberPrecV6( SQL_SINGLE_RCTX, 
                      (unsigned long *)&(select_dp->L[i]), &precision, &scale);
                      /* Allow for maximum size of NUMBER. */
                if (precision == 0) precision = 40;
                      /* Also allow for decimal point and
                         possible sign. */
                /* convert NUMBER datatype to FLOAT if scale > 0,
                   INT otherwise. */
                if (scale > 0)
                    select_dp->L[i] = sizeof(float);
                else
                    select_dp->L[i] = sizeof(int);
                break;

            case  8 : /* LONG datatype */
                select_dp->L[i] = 240;
                break;

            case 11 : /* ROWID datatype */
                select_dp->L[i] = 18;
                break;

            case 12 : /* DATE datatype */
                select_dp->L[i] = 9;
                break;
 
            case 23 : /* RAW datatype */
                break;

            case 24 : /* LONG RAW datatype */
                select_dp->L[i] = 240;
                break;
        }
        /* Allocate space for the select-list data values.
           SQLSQLDAAlloc() reserves a pointer location for
           V[i] but does not allocate the full space for
           the pointer.  */

         if (select_dp->T[i] != 2)
           select_dp->V[i] = (char *) realloc(select_dp->V[i],
                                    select_dp->L[i] + 1);  
         else
           select_dp->V[i] = (char *) realloc(select_dp->V[i],
                                    select_dp->L[i]);  

        /* Print column headings, right-justifying number
            column headings. */
        
        /* Copy to temporary buffer in case name is null-terminated */
        memset(title, ' ', MAX_VNAME_LEN);
        strncpy(title, select_dp->S[i], select_dp->C[i]);
        if (select_dp->T[i] == 2)
           if (scale > 0)
             printf ("%.*s ", select_dp->L[i]+3, title);
           else
             printf ("%.*s ", select_dp->L[i], title);
        else
          printf("%-.*s ", select_dp->L[i], title);

        /* Coerce ALL datatypes except for LONG RAW and NUMBER to
           character. */
        if (select_dp->T[i] != 24 && select_dp->T[i] != 2)
            select_dp->T[i] = 1;

        /* Coerce the datatypes of NUMBERs to float or int depending on
           the scale. */
        if (select_dp->T[i] == 2)
          if (scale > 0)
             select_dp->T[i] = 4;  /* float */
          else
             select_dp->T[i] = 3;  /* int */
    }
    printf ("\n\n");

    /* FETCH each row selected and print the column values. */
    EXEC SQL WHENEVER NOT FOUND GOTO end_select_loop;

    for (;;)
    {
        EXEC SQL FETCH C USING DESCRIPTOR select_dp;

        /* Since each variable returned has been coerced to a
           character string, int, or float very little processing 
           is required here.  This routine just prints out the 
           values on the terminal. */
        for (i = 0; i < select_dp->F; i++)
        {
            if (*select_dp->I[i] < 0)
                if (select_dp->T[i] == 4) 
                  printf ("%-*c ",(int)select_dp->L[i]+3, ' ');
                else
                  printf ("%-*c ",(int)select_dp->L[i], ' ');
            else
                if (select_dp->T[i] == 3)     /* int datatype */
                  printf ("%*d ", (int)select_dp->L[i], 
                                 *(int *)select_dp->V[i]);
                else if (select_dp->T[i] == 4)     /* float datatype */
                  printf ("%*.2f ", (int)select_dp->L[i], 
                                 *(float *)select_dp->V[i]);
                else                          /* character string */
                  printf ("%-*.*s ", (int)select_dp->L[i],
                            (int)select_dp->L[i], select_dp->V[i]);
        }
        printf ("\n");
    }
    end_select_loop:
    return;
}

void help()
{
    puts("\n\nEnter a SQL statement or a PL/SQL block at the SQL> prompt.");
    puts("Statements can be continued over several lines, except");
    puts("within string literals.");
    puts("Terminate a SQL statement with a semicolon.");
    puts("Terminate a PL/SQL block (which can contain embedded semicolons)");
    puts("with a slash (/).");
    puts("Typing \"exit\" (no semicolon needed) exits the program.");
    puts("You typed \"?\" or \"help\" to get this message.\n\n");
}

void sql_error()
{
    /* ORACLE error handler */
    printf ("\n\n%.70s\n",sqlca.sqlerrm.sqlerrmc);
    if (parse_flag)
        printf
        ("Parse error at character offset %d in SQL statement.\n",
           sqlca.sqlerrd[4]);

    EXEC SQL WHENEVER SQLERROR CONTINUE;
    EXEC SQL ROLLBACK WORK;
    longjmp(jmp_continue, 1);
}