107、proc/动态sql技术

1、非select情形，固定语句

a、exec sql execcute immediate后跟sql语句

char    sqlstmt[100];

Gets(sqlstmt)

/** user input from keybord: “CREATE TABLE student( sno INT, sname CHAR(15) )” ***/

EXEC SQL EXECUTE IMMEDIATE :sqlstmt;

2、非select情形，可加入变量

       a、构造一个动态sql语句

       b、用prepare语句来分析和命名该动态sql语句

       c、用execute语句来执行它

int      emp_number;

char   sqlstmt1[100];

char   emp_name[20];

char   job[20];

float   salary;

…

strcpy( sqlstmt1, “INSERT INTO  emp(empno, ename, job, sal) \

VALUES(:v1, :v2, :v3, :v4) ” );

EXEC SQL PREPARE S FROM :sqlstmt1;

EXEC SQL EXECUTE S USING :emp_number, :emp_name, :job, :salary;

…

3、select情形

      a、prepare

b、declare

c、open

d、fetch

e、close

f、declare statement

…

int      dept_number；

char   sqlstmt1[100]， emp_name[20]，job[20];

float   salary;

…

strcpy( sqlstmt1,

                    “SELECT ename, job, sal FROM  emp WHERE deptno = :v1” );

EXEC SQL PREPARE S FROM :sqlstmt1;

Cout<<“Enter Department Number: \n”;

Cin>>dept_number ;

EXEC SQL DECLARE C CURSOR FOR S;

EXEC SQL OPEN C USING :dept_number;

while() {

EXEC SQL FETCH C INTO  :emp_name, :job, :salary;

…}

EXEC SQL CLOSE C;

…

4、select情形和非select情形、

        a、prepare

b、declare cursor

c、describe

d、open

e、fetch

f、close