ANSI动态SQL

ANSI 动态SQL是在Oracle 8i之后开始有的，以前用的时候用的是Oracle动态SQL方法四（下篇文章），ANSI动态 SQL实现了宿主变量和选择

列表变量在未知情况下的处理，也就是等号的左右 ，现在都可以实现动态的指定了。

ANSI动态SQL的步骤如下：

1，分配输入描述区和分配输出描述区

2，准备SQL语句并定义游标

3，处理绑定变量

4，打开游标

5，处理查询结果

6，释放描述区，关闭游标

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlca.h>
void connect();
void sql_err();
void process_input();
void process_output();
EXEC SQL BEGIN DECLARE SECTION;
char sql_stat[100];
EXEC SQL END DECLARE SECTION;
void main()
{
EXEC SQL WHENEVER SQLERROR DO sql_err();
connect();
EXEC SQL ALLOCATE DESCRIPTOR 'input_descriptor';
EXEC SQL ALLOCATE DESCRIPTOR 'output_descriptor';
for(;;)
{
printf("input ANSI dynamic sql_statement(X:to exit): \n");
gets(sql_stat);
if((strncmp(sql_stat,"X",1)==0)||(strncmp(sql_stat,"x",1)==0)) break;
EXEC SQL PREPARE S FROM :sql_stat;
EXEC SQL DECLARE C CURSOR FOR S;
process_input();
EXEC SQL OPEN C USING DESCRIPTOR 'input_descriptor';
if((strncmp(sql_stat,"SELECT",6)==0)||(strncmp(sql_stat,"select",6)==0))
process_output();
EXEC SQL CLOSE C;
}
EXEC SQL DEALLOCATE DESCRIPTOR 'input_descriptor';
EXEC SQL DEALLOCATE DESCRIPTOR 'output_descripotr';
EXEC SQL COMMIT WORK RELEASE;
puts("\n thanks for using ANSI dynamic sql\n");
}

void sql_err()
{
printf ("%.*s\n",sqlca.sqlerrm.sqlerrml,sqlca.sqlerrm.sqlerrmc);
}
void connect()
{
EXEC SQL BEGIN DECLARE SECTION;
char username[20],password[20];
EXEC SQL END DECLARE SECTION;
printf("input username: ");
gets(username);
printf("input password: ");
gets(password);
EXEC SQL CONNECT :username IDENTIFIED BY :password;
}

void process_input()
{
EXEC SQL BEGIN DECLARE SECTION;
int i;
char name[31];
int input_count,input_len,occurs,ANSI_varchar_type;
char input_buf[200];
EXEC SQL END DECLARE SECTION;
EXEC SQL DESCRIBE INPUT S USING DESCRIPTOR 'input_descriptor';
EXEC SQL GET DESCRIPTOR 'input_descriptor' :input_count=COUNT;
ANSI_varchar_type=12;
for(i=0;i<input_count;i++)
{
occurs=i+1;
EXEC SQL GET DESCRIPTOR 'input_descriptor' VALUE :occurs :name = NAME;
printf ("\ninput value for %s: ",name);
gets(input_buf);
input_len=strlen(input_buf);
input_buf[input_len]='\0';
EXEC SQL SET DESCRIPTOR 'input_descriptor'
VALUE :occurs TYPE = :ANSI_varchar_type,
LENGTH = :input_len,DATA = :input_buf;
}
}

void process_output()
{
EXEC SQL BEGIN DECLARE SECTION;
int i;
int output_count,occurs,type,len;
short indi;
char data[200],name[31];
EXEC SQL END DECLARE SECTION;
EXEC SQL DESCRIBE OUTPUT S USING DESCRIPTOR 'output_descriptor';
EXEC SQL GET DESCRIPTOR 'output_descriptor' :output_count = COUNT;
printf ("\n");
type = 12;
len =200;
for (i = 0; i < output_count; i++)
{
occurs = i + 1;
EXEC SQL SET DESCRIPTOR 'output_descriptor' VALUE :occurs TYPE = :type,LENGTH = :len;
EXEC SQL GET DESCRIPTOR 'output_descriptor' VALUE :occurs :name = NAME;
printf ("\t%s ",name);
}
EXEC SQL WHENEVER NOT FOUND DO sql_err();
for(;;)
{
EXEC SQL FETCH C INTO DESCRIPTOR 'output_descriptor';
for(i = 0; i < output_count; i++)
{
occurs = i + 1;
EXEC SQL GET DESCRIPTOR 'output_descriptor' VALUE :occurs :data = DATA, :indi = INDICATOR;
if(indi == -1)
printf ("\t%s "," ");
else
printf ("\t%s ",data);
}
printf ("\n");
}
}