ProC动态SQL示例(第1,2,3种方法)

原创 2006年05月29日 09:42:00

ProC动态SQL示例(第1,2,3种方法)

草木瓜 2006-2-1

下面是ProC前三种动态SQL的完整示例。

(1)动态SQL1: 不能是查询(SELECT)语句,并且没有宿主变量. 
用法:拼一句动态SQL语句,并用EXECUTE IMMEDIATE执行,如:
 
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)动态SQL2: 不能是查询(SELECT)语句,并且输入的宿主变量数目是知道的,
用法:拼一句动态SQL语句,用PREPARE,EXECUTE语句执行.
strcpy(sqlstring, "DELETE FROM test WHERE test_col = :?"); 
EXEC SQL PREPARE sqlproc FROM :sqlstring;
EXEC SQL EXECUTE sqlproc USING :emp_number; 
 
下文示例中大多数是采用动态SQL2.
 
(3)动态SQL3: 用于创建动态查询, 并且要查询的字段以及输入的宿主变量数目是知道的
用法: 拼一句动态SQL语句,用PREPARE分析该语句,并要定义一个CURSOR进行取值
如:要查询的数据在多张表中,select user_name from,可采用动态SQL3来进行查询
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>

//变量,过程预声明
int i;
char screen[1];
char cmd[1];

//**********************************************************
//CASE对应与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 *);

//主函数
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();
 }
}


//打开数据连接
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; 
}

//选择数据操作
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='请假';
    EXEC SQL SELECT COUNT(DISTINCT FLOW_NAME) INTO :oraCount FROM CLASS_FLOW WHERE FLOW_CLASS='请假';
    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;

    //接受屏幕数据
    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");
    //读取内容
    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';
    //读取数
    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);

}
//显示意外错误
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();
}

版权声明:本文为博主原创文章,未经博主允许不得转载。

相关文章推荐

PROC---动态SQL

动态SQL 在有些情况下, 在编码时 SQL 语句还不能完整地写出来, 而是在程序执行时才能 构造出来(也就是说动态 SQL 语句在程序编译时语句的选择列表和确定, 变量数据 大于 0)。 这...

MySQL procedure详解

1.      存储过程简介   我们常用的操作数据库语言SQL语 句在执行的时候需要要先编译,然后执行,而存储过程(Stored Procedure)是一 组为了完成特定功能的SQL...

SQL Procedure 建立

--********************************************************************************************* -- ...

SQL存储过程(Stored Procedure)

SQL存储过程(Stored Procedure) 什么是存储过程? 存储过程(procedure)类似于C语言中的函数用来执行管理任务或应用复杂的业务规则存储过程可以带参数,也可以返回结...

ORACLE PROC开发

Proc也就是嵌入式C,与informix的ESQ/C有类似之处,本部分主要列出Proc与Esql的区别,相同部分请参见informix部分。 1.数组功能 Proc中支持使用宿主变量数组一次查询...

111、ProC动态SQL示例(第1,2,3种方法)

下面是ProC前三种动态SQL的完整示例。 (1)动态SQL1: 不能是查询(SELECT)语句,并且没有宿主变量. 用法:拼一句动态SQL语句,并用EXECUTE IMMEDIATE执行,如:...

sql server、db2、oracle 存储过程动态sql语句示例

sql server、db2、oracle 存储过程动态sql语句示例

APEX的IR下大量数据检索的提速方法---兼谈动态SQL在IR的实现

系统中有一个表积累了10多年的数据,记录有上百万条,系统的所有部门还不让将旧数据Archive出去。原因是有时候要检索旧数据(1回/月的频度)。 但是,日常检索集中在今年及去年的数据,结果返回要花1...

Pro*C动态SQL总结2

五种典型语句: 1.create table temp(cola int); 2.delete from emp where empno:=7788; 3.delete from emp whe...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)