PROC---动态SQL

最新推荐文章于 2021-10-25 16:06:47 发布

tccee

最新推荐文章于 2021-10-25 16:06:47 发布

阅读量2.7k

点赞数 2

分类专栏： PROC oracle

本文链接：https://blog.csdn.net/u012960623/article/details/23831581

版权

oracle 同时被 2 个专栏收录

9 篇文章 0 订阅

订阅专栏

PROC

2 篇文章 0 订阅

订阅专栏

动态SQL
        在有些情况下，在编码时 SQL 语句还不能完整地写出来，而是在程序执行时才能构造出来(也就是说动态 SQL 语句在程序编译时语句的选择列表和确定，变量数据大于 0)。这种在程序执行临时生成的 SQL 语句叫动态 SQL 语句. 利用动态 SQL 来编写 Pro*C 程序的方法叫动态 SQL 技术.
    在下列情况之一不知道时，使用动态 SQL 技术：
    a. SQL 语句的文本.
    b. 宿主变量的个数。
    c. 宿主变量的数据类型。
    d. 引用的数据库对象，如列，索引，序列，表，用户名和视图.
    实际上，动态 SQL 在一般应用中用的相当普遍！
    Oracle 中动态 SQL 可用以下两种方法实现：
    一个是 Oracle 自己的方法，一个是 ANSI 的方法. 一般建议使用 Oracle 的方法，
    但对于较复杂的应用，可以使用 ANSI 的方法，因为这样可以使自己的程序简化。
    下面先说 Oracle 自己的方法.
1 Oracle方法
    一般来说，应该使用一个字符串变量来表示动态 SQL 语句的文本。但该文本不包含“EXEC SQL”和分号“；”以及下列嵌入式 SQL 命令：
    ALLOCATE CLOSE DECLARE DESCRIBE
   EXECUTE   FETCH FREE   GET
   INCLUDE   OPEN PREPARE SET

WHENEVER

1.1 动态SQL语句的处理过程
    动态 SQL 语句的处理过程如下：
    a. 构造动态 SQL 语句，提示用户输入和构造一个动态 SQL 语句文本。
    b. 分析该语句： Oracle 对动态 SQL 语句文本进行语法检查，并检查动态 SQL 语句所参照的数据库对象是否存在，以及用户是否具有访问它所参照的对象的权限。
    c. 为 SQL 语句结合宿主变量：在执行语句过程中， Oracle 读取宿主变量地址，并从这些变量中读取数据，式将数据写入这此变量中。
    d. 执行 SQL 语句，实现数据库操作。
1.2 Oracle使用动态SQL的四种方法
    Oracle 提供了四种处理动态 SQL 的方法，这四种方法从一到四依次变得复杂，从功能上说也是依次包含关系，即前一种方法的功能可以完全由后一种方法实现。
    用户可以根据每个任务的难易程度选择一种适合于自己的方法。
    a. 方法一
    这种方法只能执行非查询语句，且在非查询动态 SQL 语句内不能包含输入宿主变量占位符。
    这种方法使用 EXECUTE IMMEDIATE 命令立即执行动态 SQL 语句，所以在每次执行时， Oracle 需要对语句进行重新解释。
如:
…
<连接数据库>
strcpy(str_sql,“DELETE FROM test001 WHERE name=’username’”)；
EXEC SQL EXECUTE IMMEDIATE :str_sql;
…
    b. 方法二
    这种方法也只能用于执行非查询语句，并且允许非查询动态 SQL 语句内包含输入宿主变量占位符, 但它要求在预编译时刻动态 SQL 语句内的占位符数量及输入宿主变量的数据类型必须确定。
    ①. 准备阶段：调用 PREPARE 语句准备需要执行的动态 SQL 语句，这时Oracle 对动态 SQL 语句进行语法分析，检查它所参照的数据库对象等；
    ②. 执行阶段：调用 EXECUTE 命令执行准备好的动态 SQL 语句。采用这种方式时，动态 SQL被准备好后可以被多次执行，而不需要 Oracle 在每次执行时重新解释 SQL 语句。
如:
…
<连接数据库>
strcpy(str_sql,“INSERT INTO test001 values (:K1,:K2,:K3)”)；
EXEC SQL PREPARE test FROM :str_sql;
EXEC SQL EXECUTE test USING :uname, :uaddress,:usex;
…
占位符可以是随便给的一个字符，单词等.它只起占位作用而没有实际意义.
    c. 方法三
    这种方法可以执行包括查询语句在内的动态 SQL 语句，但它要求在预编译时刻动态 SQL 语句内选择列表项数，输入宿主变量占位符数据以及输入宿主变量的数据类型必须确定。方法三以游标方式执行动态 SQL 语句，它对 SQL 语句的处理过程为：
    ①. 准执行 PREPARE 命令准备 SQL 语句； #实际就是把要执行的动态语句调入内存, 以便以后对它进行操作。
    ②. 执行 DECLARE 命令声明游标；
    ③. 执行 OPEN 打开游标；
    ④. 调用 FETCH 语句提取游标；
    ⑤. 调用 CLOSE 语句关闭游标。
动态 SQL 语句中的数据库对象名称和列名可以在运行时刻指定，便此时不能以
宿主变量形式指定对象名称或列名.
其实就是多了一个游标的使用。
如下面程序:
<连接数据库>;
strcpy(sql_stmt, "SELECT name, address, sex FROM test001");
EXEC SQL PREPARE select_stmt FROM :sql_stmt;
EXEC SQL DECLARE c1 CURSOR FOR select_stmt;
EXEC SQL OPEN c1;
EXEC SQL WHENEVER NOT FOUND DO BREAK;
while(1)
{
    EXEC SQL FETCH c1 INTO :Name, :address, :sex;
    printf("The name is: %s, The Address is: %s, The sex is :%s\n", Name, address, sex);
}
EXEC SQL CLOSE c1;
    d. 方法四
    使用描述符方式处理动态 SQL 语句，它可以执行包括查询语句在内的所有动态SQL 语句。这种方式对动态 SQL 语句的限制最小，它不要求在预编译时确定动动态 SQL 语句内的选择列表项数，库占位符数量以及宿主变量数据类型，这些元素只要在运行时刻能够确定即可。也就是说这种方法适合于在程序编译时对程序中的 SQL 语句还一无所知的情况。
    方法四通过使用 SQLDA 结构代替宿主变量，并通过调用 DESCRIBE SELECT LIST或 DESCRIBE BIND VARIABLES 语句来检索选择列表项和结合变量（即宿主变量）描述信息，从而使应用程序能够在运行时刻处理这种类型的动态 SQL 语句。当 SQLDA 结构用于存列表信息项信息时，它们被称做查询描述符；当 SQLDA
结构用于存储结合变量信息时，它们被称作结合描述符。
这三种方法中前三种比较简单，这里就不多说了，方法四最为灵活，功能最全，但是也最为复杂. 使用方法共可分为以下 18 个步骤（请参考以下 18 个步骤去看例程）：
    ⑴．在宿主变量声明区域声明字符串型宿主变量，用它存储动态语句文本。
    ⑵．声明查询和结合描述符。
    ⑶．为查询和结合描述符分配内存空间；
    ⑷．设置描述符能够最多描述的选择列表项和占位符数量；
    ⑸．将动态语句文本拷贝到前面声明的宿主变量字符串中；
    ⑹．调用 PREPARE 语句准备宿主变量字符串中的动态语句；
    ⑺．为查询语句声明游标；
    ⑻．描述结合变量，填充结合描述符；
    ⑼．用 DESCRIBE 语句所检索到的占位符实际数据（F）设置结合描述符中的占位符数据量，即 N 元素；
    ⑽．为 DESCRIBE 语句所检索到的结合变量分配内存，并为各个结合变量提供数值；
    ⑾．使用结合描述符打开游标；
    ⑿．描述选择列表，填充查询描述符；
    ⒀．将查询描述符中的选择列表项数值设为 DESCRIBE 语句所检索到的动态SQL 语句中的实际选择列表项数;
    ⒁．重新设置选择列有项的长度和数据类型；
    ⒂．执行 FETCH 语句，将数据库表中的数据提取到查询描述符所指向的数据缓冲区中；
    ⒃．处理 FETCH 语句返回的选择列值；
    ⒄．释放应用程序为选择列，占位符，指示符变量和描述符所申请的内存空间；
    ⒅．关闭游标.
2 ANSI方法

2.1 使用ANSI方法的一些注意事项
   Pro*C 应用程序的使用 ANSI 处理方法时，必须将 DYNAMIC 预编译选项值设置为 ANSI。此外，还需将 TYPE_CODE 选项设为 ANSI。（这一点要十分注意，因为这两个选项不加会出现意想不到的错误，比如说：如果不加 TYPE_CODE, 程序一样可以编译通过，但是执行的结果却和你原来的的想法大相径庭）
2.2 ANSI方法和Oracle方法的不同
    ANSI 方法和 Oracle 方法相比有以下不同:
    ①． Oracle 处理方法不支持对象类型，游标变量，结构数组， DML 语句的RETURNING 子句，多字节字符变量
和 LOB 等，而 ANSI 处理方法支持所有的 Oracle 数据类型。
    ②． ANSI 处理方法所使用的描述符为 C/C++结构变量，它们由应用程序申请，释放和维护，而 ANSI 处理方法所使用的描述符则为 SQL 描述符，它们由Oracle 内部维护。
    正是由于 ANSI 方法所使用的描述符是由 Oracle 内部维护，所以 ANSI 方法比以上方法四少了一些对于描述符的操作，比如分配内存空间等。
2.3 ANSI处理方法对动态SQL语句的处理步骤
    ANSI 处理方法对动态 SQL 语句的处理步骤为：
    ⑴．声明宿主变量，包括动态 SQL 语句存储缓冲区；
    ⑵．为输入，输出变量分配描述符；
    ⑶．将动态语句文本拷贝到前面（1）中声明的宿主变量字符串中；
    ⑷．调用 PREPARE 语句准备宿主变量字符串中的动态语句； #这一步实际上是对 SQL 语句进行语法分析，检查
它所参照的数据库对象。
   ⑸．用 DESCRIBE 语句描述输入操作； #目的是读取动态 SQL 语句中的输入变量和输出变量等描述信息，将它们存储到指定的输入描述符和输出描述符中。
   ⑹．声明动态游标；
   ⑺．用 SET DESCRIPTOR 语句设置输出描述符；
   ⑻．打开游标；
   ⑼．提取游标数据；
   ⑽．处理所提取的游标数据；
   ⑾．关闭游标；
   ⑿．释放输入，输出描述符；
2.4 程序举例

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlca.h>
#include <setjmp.h>
#include <sqlcpr.h>
#include <sqlda.h>
#define MAX_ITEMS 40
#define BUF_LEN 255
char username[10];
char password[10];
int connect();
int error_proc();
int get_stmt(void);
void query_proc();
char sql_stmt[1024];
jmp_buf jmp_continue;
void main( )
{
   int i;
   char buf[BUF_LEN];
   int bind_vars;
   int data_len;
   short bind_ind;
   static int col_type=12;
   int loop_flat=1;
   memset(sql_stmt,sizeof(sql_stmt),0);
/*connect database*/
   if (connect()!=0)
       exit(-1);
   EXEC SQL WHENEVER SQLERROR DO error_proc();
   EXEC SQL ALLOCATE DESCRIPTOR 'InDesc';
   EXEC SQL ALLOCATE DESCRIPTOR 'OutDesc';
   for(;;)
   {
        (void) setjmp(jmp_continue);
        if(get_stmt()!=0)
             break;
        EXEC SQL PREPARE mystmt FROM :sql_stmt;
        EXEC SQL DECLARE C CURSOR FOR mystmt;
        EXEC SQL DESCRIBE INPUT mystmt USING DESCRIPTOR 'InDesc';
        printf("\nStart of open cursor\n");
        EXEC SQL OPEN C USING DESCRIPTOR 'InDesc';
        printf("\nEnd of open cursor\n");
        if((strncmp(sql_stmt,"SELECT",6)==0)||(strncmp(sql_stmt,"select",6)==0))
             query_proc();
        EXEC SQL CLOSE C;
   }
   puts("\nGood bye!\n");
   EXEC SQL DEALLOCATE DESCRIPTOR 'InDesc';
   EXEC SQL DEALLOCATE DESCRIPTOR 'OutDesc';
   EXEC SQL WHENEVER SQLERROR CONTINUE;
   EXEC SQL COMMIT WORK RELEASE;
   EXEC SQL WHENEVER SQLERROR DO error_proc();
   exit(0);
}

以上主函数中，error_proc()函数不太重要，可根据自己的意思去做!
query_proc()函数如下:

void query_proc()
{
    int i;
    int j=0;
    int col_num;
    static int col_type=12;
    int col_length=BUF_LEN;
    int col_len[MAX_ITEMS];
    short col_ind;
    char data[BUF_LEN], name[31];
    printf("\nHAHAHA\n");
    EXEC SQL DESCRIBE OUTPUT mystmt USING DESCRIPTOR 'OutDesc';
    EXEC SQL GET DESCRIPTOR 'OutDesc' :col_num=COUNT;
    if(col_num>MAX_ITEMS)
    {
        printf("\nSorry, The max colum is lager than %d\n",(int)MAX_ITEMS);
        return;
    }
    for(i=1;i<=col_num;i++)
    {
        EXEC SQL GET DESCRIPTOR 'OutDesc'
            VALUE :i 
            :name=NAME,
            :col_len[i-1]=OCTET_LENGTH;
        printf("%-*s ",col_len[i-1],name);
        if(col_len[i-1]<strlen(name)+1)
              col_len[i-1]=strlen(name)+1;
        EXEC SQL SET DESCRIPTOR 'OutDesc'
           VALUE :i
           TYPE=:col_type,
           LENGTH=:col_length;
    }
    EXEC SQL WHENEVER NOT FOUND DO BREAK;
    for(;;)
    {
        j++;
        printf("\n");
        EXEC SQL FETCH C INTO DESCRIPTOR 'OutDesc';
        for(i=1;i<=col_num;i++)
        {
             EXEC SQL GET DESCRIPTOR 'OutDesc'
                 VALUE :i
                 :data=DATA,
                 :col_ind=INDICATOR;
             if(col_ind!=-1)
                 printf("%-*s ",col_len[i-1],data);
             else
                 printf("%-*c ",col_len[i-1],' ');
        }
    }
    printf("\n");
    printf("%d lines you have selected!\n",j-1);
    error_msg:
    return;
}

注意：如果用 ANSI 方法处理非查询语句时，就没必要申请输出描述符！以上所列的步骤可根据各自的应用进行增删！
比如说，像没有参数的 delete, insert 等语句的处理就可以简化为以下四步：
a. 准备语句；
b. 声明游标；
c. 打开游标。
d. 关闭游标；