PRO*C中使用動(dòng)態(tài)游標(biāo)的四種方法

2011.01.13

PRO*C中使用動(dòng)態(tài)游標(biāo)的四種方法

動(dòng)態(tài)方法1
動(dòng)態(tài)方法1的處理過程是先構(gòu)造一個(gè)動(dòng)態(tài)SQL語句然后用EXECUTE IMMEDIATE來執(zhí)行,EXECUTE IMMEDIATE的功能是分析動(dòng)態(tài)的語句的文本,檢查是否有錯(cuò)誤,
如果SQL 語句僅執(zhí)行一次動(dòng)態(tài)方法1的效率很高,動(dòng)態(tài)方法1一定不是SELECT 語句
            1 EXEC SQL EXECUTE IMMEDIATE CREATE TABLE .............;

            2 sprintf(host_string,"");
               EXEC SQL EXECUTE IMMEDIATE :host_string ;
動(dòng)態(tài)方法2
方法2與方法1 類似也是能含有SELECT語句,方法2中含義虛擬輸入宿主變量,比方法1 多了一步SQL語句的語法分析,
處理方式分為3步:
                構(gòu)造一個(gè)動(dòng)態(tài)SQL
                用PREPARE分析和命名該SQL
                用EXECUTE來執(zhí)行它
    用法: sprintf(host_string,"DELETE FROM table_name WHERE no=:v1 AND name=:name");(拼帶有輸入宿主變量的SQL)
          EXEC SQL PREPARE sql_name FROM :host_string;(分析語法)
          EXEC SQL EXECUTE IMMEDIATE sql_name USING :v1,:name;(傳遞實(shí)際變量)

動(dòng)態(tài)方法3
方法就是使用游標(biāo),查詢并返回多行,如果在方法1和方法2中的SELECT 語句查詢返回一行的話,也可以使用SELECT 語句,
方法為 SELECT column1,column2...INTO:variale1,variable2;
方法3是專門解決一次返回多行的,使用方法:
                                      1 拼成一個(gè) SQL sprintf(host_string,"")
                                      2 用EXEC SQL PREPARE name FROM :host_string 來分析其語法
                                      3 用EXEC SQL DECLARE c_name CURSOR FOR : name 來聲明游標(biāo)
                                      4 用EXEC SQL OPEN c_name ;
                                      5 用EXEC SQL FETCH c_name INTO:variable1,:variable2;取出游標(biāo)中的數(shù)據(jù)
                                      6 EXEC SQL CLOSE c_name;關(guān)閉游標(biāo)

三種動(dòng)態(tài)SQL方法的特點(diǎn)都是先在C語言中拼成所需要的SQL 文本串,然后用EXECUTE IMMEDIATE 來執(zhí)行

下面是我從網(wǎng)上收集的可以和我的對(duì)照,ProC前三種動(dòng)態(tài)SQL的完整示例。

ProC動(dòng)態(tài)SQL示例(第1,2,3種方法)

草木瓜 2006-2-1

下面是ProC前三種動(dòng)態(tài)SQL的完整示例。

(1)動(dòng)態(tài)SQL1: 不能是查詢(SELECT)語句,并且沒有宿主變量.
用法:拼一句動(dòng)態(tài)SQL語句,并用EXECUTE IMMEDIATE執(zhí)行,如:

EXEC SQL EXECUTE IMMEDIATE CREATE TABLE test (test_col VARCHAR2(4));
EXEC SQL EXECUTE IMMEDIATE INSERT INTO TABLE test ('AAAA');
EXEC SQL EXECUTE IMMEDIATE DELETE test WHERE test_col='AAAA';

(2)動(dòng)態(tài)SQL2: 不能是查詢(SELECT)語句,并且輸入的宿主變量數(shù)目是知道的,
用法:拼一句動(dòng)態(tài)SQL語句,用PREPARE,EXECUTE語句執(zhí)行.
strcpy(sqlstring, "DELETE FROM test WHERE test_col = :?");
EXEC SQL PREPARE sqlproc FROM :sqlstring;
EXEC SQL EXECUTE sqlproc USING :emp_number;

下文示例中大多數(shù)是采用動(dòng)態(tài)SQL2.

(3)動(dòng)態(tài)SQL3: 用于創(chuàng)建動(dòng)態(tài)查詢, 并且要查詢的字段以及輸入的宿主變量數(shù)目是知道的
用法: 拼一句動(dòng)態(tài)SQL語句,用PREPARE分析該語句,并要定義一個(gè)CURSOR進(jìn)行取值
如:要查詢的數(shù)據(jù)在多張表中，select user_name from，可采用動(dòng)態(tài)SQL3來進(jìn)行查詢
strcpy(sql,"select user_name from ");
strcat(sql,"table1");//table2,table3,table4
EXEC SQL PREPARE sqlproc FROM :sql;
EXEC SQL DECLARE cur_user_name CURSOR FOR sqlproc;
EXEC SQL OPEN cur_user_name;
while(1)
{
EXEC SQL FETCH cur_user_name into :ora_id;
if (sqlca.sqlcode < 0)
{
/*FETCH CURSOR失敗*/
printf("fetch cursor fail,sqlcode=%ld,sqlserr=%s",sqlca.sqlcode,sqlca.sqlerrm.sqlerrmc);
}
if( sqlca.sqlcode == SQLNOTFOUND)
{
break;
}
}
EXEC SQL CLOSE cur_user_name;

下文示例中Case5也是采用這種方法.

//Proc 示例

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

//變量，過程預(yù)聲明
int i;
char screen[1];
char cmd[1];

//**********************************************************
//CASE對(duì)應(yīng)與db_selectop的switch
EXEC SQL BEGIN DECLARE SECTION;

VARCHAR oraName[30]; //CASE 1,2,3

VARCHAR oraValue[20]; //CASE 1,2,3,5
int oraCount; //CASE 1,2,3,4,5
VARCHAR oraSql[30],oraTable[20]; //CASE 4,5

VARCHAR oraField[10]; //CASE 5
VARCHAR oraCountSql[30]; //CASE 5

VARCHAR oraCode[10]; //CASE 6
VARCHAR oraContent[10]; //CASE 6

EXEC SQL END DECLARE SECTION;
//**********************************************************

int db_connect();
int db_selectop();

//void dy_tablecount();
//void dy_tablefield();
void view_tabledata();

void pause();
void sql_error(char *);

//主函數(shù)
void main()
{
EXEC SQL INCLUDE sqlca;
EXEC ORACLE OPTION (RELEASE_CURSOR = YES);
EXEC SQL WHENEVER SQLERROR DO sql_error(" <ERROR> ");

if(db_connect()==0)
{
db_selectop();
}
}

//打開數(shù)據(jù)連接
int db_connect()
{

EXEC SQL BEGIN DECLARE SECTION;
VARCHAR oraCN[30];
EXEC SQL END DECLARE SECTION;

printf("----------------------------------");
printf("\n [ Examples With Oracle DB ]\n");
printf("----------------------------------");
printf("\n Designed by Liwei 2005\n");
cmd[0]='A';
while(cmd[0]!='0' && cmd[0]!='1')
{

  printf("\n Confirm DB Source:");
  printf("\n 1:workflow/workflow@if");
  printf("\n 0:Exit;");
  printf("\n Choose:");

  gets(cmd);
  switch(cmd[0])
  {
   case '1':
    strcpy(oraCN.arr,"workflow/workflow@if");
    oraCN.len = strlen(oraCN.arr);
    oraCN.arr[oraCN.len]='\0';

    //EXEC SQL WHENEVER SQLERROR GOTO cnError;
    EXEC SQL CONNECT :oraCN;

    printf("\n [OK Connected!] ");
    return 0;

    break;
   case '0':
    break;
   default:
    printf("\n [Error Input!] \n");
    break;
  }

}

exit(0);

//cnError:
// printf("\n [Error Oracle Connected!]");
// return 1;
}

//選擇數(shù)據(jù)操作
int db_selectop()
{
char order[1];

cmd[0]='A';
//order[0]='A';

while(cmd[0]!='0')
{
  printf("\n ");
  printf("\n Select DB Method:");
  printf("\n -------------------------------------------");
  printf("\n 1: GetTableCount      STATIC [CLASS_FLOW]");
  printf("\n 2: GetTableField One STATIC [CLASS_FLOW]");
  printf("\n 3: GetTableField Muti STATIC [USE_POWER]");
  printf("\n");
  printf("\n 4: GetTableCount      DYNAMIC      ");
  printf("\n 5: GetTableField One DYNAMIC      ");
  printf("\n");
  printf("\n 6: EditTable USE_DEPT");
  printf("\n -------------------------------------------");
  printf("\n 0: Exit");
  printf("\n\n Enter:");

gets(cmd);

  switch(cmd[0])
  {
   case '1':

    EXEC SQL SELECT NVL(COUNT(*),0) INTO :oraCount FROM CLASS_FLOW;

    printf("\n <The Table Count> ");
    printf("%d",oraCount);
    pause();
    break;

   case '2':

    EXEC SQL DECLARE curOne CURSOR FOR SELECT DISTINCT FLOW_NAME FROM CLASS_FLOW WHERE FLOW_CLASS='請(qǐng)假';
    EXEC SQL SELECT COUNT(DISTINCT FLOW_NAME) INTO :oraCount FROM CLASS_FLOW WHERE FLOW_CLASS='請(qǐng)假';
    EXEC SQL OPEN curOne;

    for(i=1;i<=oraCount;i++)
    {
     EXEC SQL FETCH curOne INTO :oraName;
     oraName.arr[oraName.len]='\0';
     printf("\n <Field List> ");
     printf("%s",oraName.arr);
    }
    EXEC SQL CLOSE curOne;
    pause();
    break;

case '3':

    EXEC SQL DECLARE curMuti CURSOR FOR SELECT POWER_ID,POWER_NAME FROM USE_POWER ORDER BY POWER_ID ASC;
    EXEC SQL SELECT COUNT(*) INTO :oraCount FROM USE_POWER;
    EXEC SQL OPEN curMuti;

    for(i=1;i<=oraCount;i++)
    {
     EXEC SQL FETCH curMuti INTO :oraValue,:oraName;
     oraValue.arr[oraValue.len]='\0';
     oraName.arr[oraName.len]='\0';
     printf("\n <Fields List> ");
     printf("%-8s",oraValue.arr);
     printf("%-20s",oraName.arr);
    }

EXEC SQL CLOSE curMuti;

pause();
break;

case '4':

    //EXEC SQL BEGIN DECLARE SECTION;
    //VARCHAR oraSql[30],oraTable[20];
    //int oraCount;
    //EXEC SQL END DECLARE SECTION;

    printf("\n Custom Table ");
    printf("\n ----------------------- ");
    printf("\n Input Table Name:");
    gets(oraTable.arr);

oraTable.len=strlen(oraTable.arr);
oraTable.arr[oraTable.len]='\0';

    strcpy(oraSql.arr,"SELECT COUNT(*) FROM ");
    strcat(oraSql.arr,oraTable.arr);
    oraSql.len=strlen(oraSql.arr);
    oraSql.arr[oraSql.len]='\0';

    printf("\n <SQL STATE> ");
    printf(oraSql.arr);
    printf("\n ");

    EXEC SQL PREPARE sqlDyCount FROM :oraSql;
    EXEC SQL DECLARE curDyCount CURSOR FOR sqlDyCount;
    EXEC SQL OPEN curDyCount;
    EXEC SQL FETCH curDyCount INTO :oraCount;
    EXEC SQL CLOSE curDyCount;

    printf("\n <Table Count> ");
    printf("%d",oraCount);
    //dy_tablecount();
    pause();
    break;

case '5':

    //EXEC SQL BEGIN DECLARE SECTION;
    //VARCHAR oraSql[30],oraTable[10],oraField[10],oraValue[20];
    //VARCHAR oraCountSql[30];
    //int oraCount;
    //EXEC SQL END DECLARE SECTION;

    //接受屏幕數(shù)據(jù)
    printf("\n Custom Table And Field ");
    printf("\n ----------------------- ");
    printf("\n Input Table Name:");
    gets(oraTable.arr);
    oraTable.len=strlen(oraTable.arr);
    oraTable.arr[oraTable.len]='\0';
    printf(" Input Field Name:");
    gets(oraField.arr);
    oraField.len=strlen(oraField.arr);
    oraField.arr[oraField.len]='\0';

    //組合SELECT語句
    strcpy(oraSql.arr,"SELECT ");
    strcat(oraSql.arr,oraField.arr);
    strcat(oraSql.arr," FROM ");
    strcat(oraSql.arr,oraTable.arr);
    oraSql.len=strlen(oraSql.arr);
    oraSql.arr[oraSql.len]='\0';
    printf("\n <SQL STATE> ");
    printf(oraSql.arr);
    printf("\n");
    //讀取內(nèi)容
    EXEC SQL PREPARE sqlDy FROM :oraSql;
    EXEC SQL DECLARE curDyField CURSOR FOR sqlDy;
    EXEC SQL OPEN curDyField;

    //組合SELECT COUNT語句
    strcpy(oraCountSql.arr,"SELECT COUNT(*) FROM ");
    strcat(oraCountSql.arr,oraTable.arr);
    oraCountSql.len=strlen(oraCountSql.arr);
    oraCountSql.arr[oraCountSql.len]='\0';
    //讀取數(shù)
    EXEC SQL PREPARE sqlDyCount FROM :oraCountSql;
    EXEC SQL DECLARE curDyFieldCount CURSOR FOR sqlDyCount;
    EXEC SQL OPEN curDyFieldCount;
    EXEC SQL FETCH curDyFieldCount INTO :oraCount;

    for(i=1;i<=oraCount;i++)
    {
    EXEC SQL FETCH curDyField INTO :oraValue;
    oraValue.arr[oraValue.len]='\0';
    printf("\n <Field List> ");
    printf("%s",oraValue.arr);
    }
    EXEC SQL CLOSE curDyFieldCount;
    EXEC SQL CLOSE curDyField;
    //dy_tablefield();
    pause();
    break;

case '6':

    order[0]='A';
    while(order[0]!='0')
    {
     printf("\n ");
     printf("\n Edit Table ");
     printf("\n -------------");
     printf("\n 1: VIEW");
     printf("\n 2: INSERT");
     printf("\n 3: DELETE");
     printf("\n 4: UPDATE");
     printf("\n -------------");
     printf("\n 0: EXIT");
     printf("\n\n Enter:");
     gets(order);

     switch(order[0])
     {
     case '1':
      view_tabledata();
      pause();
      break;
     case '2':
      //INSERT
      printf("\n INSERT ");
      printf("\n ----------------------- ");
      printf("\n ENTER CODE:");
      gets(oraCode.arr);
      oraCode.len=strlen(oraCode.arr);
      oraCode.arr[oraCode.len]='\0';
      printf(" ENTER CONTENT:");
      gets(oraContent.arr);
      oraContent.len=strlen(oraContent.arr);
      oraContent.arr[oraContent.len]='\0';

      EXEC SQL INSERT INTO USE_DEPT VALUES(:oraCode,:oraContent);
      EXEC SQL COMMIT;
      pause();
      break;
     case '3':
      view_tabledata();
      //DELETE
      printf("\n DELETE ");
      printf("\n ----------------------- ");
      printf("\n ENTER CODE:");
      gets(oraCode.arr);
      oraCode.len=strlen(oraCode.arr);
      oraCode.arr[oraCode.len]='\0';
      EXEC SQL DELETE USE_DEPT WHERE DEPT_ID=:oraCode;
      EXEC SQL COMMIT;
      //strcpy(c_sql, "DELETE FROM EMP WHERE EMPNO = :?");
      //EXEC SQL PREPARE sql_stmt FROM :c_sql;
      //EXEC SQL EXECUTE sql_stmt USING :emp_number;
      pause();
      break;
     case '4':
      view_tabledata();
      //UPDATE
      printf("\n UPDATE ");
      printf("\n ----------------------- ");
      printf("\n ENTER CODE:");
      gets(oraCode.arr);
      oraCode.len=strlen(oraCode.arr);
      oraCode.arr[oraCode.len]='\0';
      printf(" ENTER CONTENT:");
      gets(oraContent.arr);
      oraContent.len=strlen(oraContent.arr);
      oraContent.arr[oraContent.len]='\0';

EXEC SQL UPDATE USE_DEPT SET DEPT_NAME=:oraContent WHERE DEPT_ID=:oraCode;
EXEC SQL COMMIT;

      pause();
      break;
     default:
      break;
     }
    }
    cmd[0]='6';
    break;

   default:
    break;
  }

}
return 0;

}

void view_tabledata()
{
//VIEW
EXEC SQL DECLARE curTable CURSOR FOR SELECT DEPT_ID,DEPT_NAME FROM USE_DEPT ORDER BY DEPT_ID ASC;
EXEC SQL SELECT COUNT(*) INTO :oraCount FROM USE_DEPT;
EXEC SQL OPEN curTable;

printf("\n ");
printf("%-8s","CODE");
printf("%-20s","CONTENT");
printf("\n--------------------");

for(i=1;i<=oraCount;i++)
{
  EXEC SQL FETCH curTable INTO :oraValue,:oraName;
  oraValue.arr[oraValue.len]='\0';
  oraName.arr[oraName.len]='\0';
  printf("\n ");
  printf("%-8s",oraValue.arr);
  printf("%-20s",oraName.arr);
}

printf("\n--------------------");

EXEC SQL CLOSE curTable;
}
//暫停屏幕
void pause()
{
printf("\n\n--Press Enter To Continue--");
gets(screen);

}
//顯示意外錯(cuò)誤
void sql_error(char *msg)
{
//printf("\n%s %ld %s\n", msg,sqlca.sqlcode,(char *)sqlca.sqlerrm.sqlerrmc);
printf("\n%s %s\n", msg,(char *)sqlca.sqlerrm.sqlerrmc);
//EXEC SQL ROLLBACK RELEASE;
db_selectop();
}

本站僅提供存儲(chǔ)服務(wù)，所有內(nèi)容均由用戶發(fā)布，如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請(qǐng)點(diǎn)擊舉報(bào)。

打開APP，閱讀全文并永久保存查看更多類似文章

Pro*c的簡(jiǎn)單例子 - NinGoo的數(shù)據(jù)庫(kù)專欄

[精華] 在Oracle運(yùn)行操作系統(tǒng)命令（轉(zhuǎn)載）

Linux下的Oracle編程技術(shù)

學(xué)習(xí)Linux下Oracle數(shù)據(jù)庫(kù)編程

C連接SQLite

C/C++面試題大匯總6

更多類似文章 >>

国产一级a片免费看高清,亚洲熟女中文字幕在线视频,黄三级高清在线播放,免费黄色视频在线看

PRO*C中使用動(dòng)態(tài)游標(biāo)的四種方法