mysql注解动态sql语句_MYSQL 动态sql语句

动态sql语句 格式1

直接执行sql声明(sqlstatement)

例程：

stringMysql

Mysql = "CREATE TABLE Employee "&

+"(emp_id integer not null,"&

+"dept_id integer not null, "&

+"emp_fname char(10) not null, "&

+"emp_lname char(20) not null)"

EXECUTE IMMEDIATE :Mysql ;

动态sql语句 格式2

准备sql声明(sqlsa)

执行sql声明(sqlsa)

例程：

INTEmp_id_var = 56

PREPARE SQLSA

FROM "DELETE FROM employee WHERE emp_id=?" ;

EXECUTE SQLSA USING :Emp_id_var ;

动态sql语句 格式3

为动态传输区 声明 游标|进程动态游标|进程

根据sql声明(sqlca)准备动态传输区

打开动态游标

取出游标进程值

关闭游标

例程：

integer Emp_id_var

DECLARE my_cursor DYNAMIC CURSOR FOR SQLSA ;

PREPARE SQLSA FROM "SELECT emp_id FROM employee" ;

OPEN DYNAMIC my_cursor ;

FETCH my_cursor INTO :Emp_id_var ;

CLOSE my_cursor ;

动态sql 格式4

为动态传输区 声明 游标|进程动态游标|进程

根据sql声明(sqlca)准备动态传输区

写入动态描述区

打开动态游标

取出游标进程值

关闭游标

例程：

string Stringvar, Sqlstatement

integer Intvar

Sqlstatement = "SELECT emp_id FROM employee"

PREPARE SQLSA FROM :Sqlstatement ;

DESCRIBE SQLSA INTO SQLDA ;

DECLARE my_cursor DYNAMIC CURSOR FOR SQLSA ;

OPEN DYNAMIC my_cursor USING DESCRIPTOR SQLDA ;

FETCH my_cursor USING DESCRIPTOR SQLDA ;

// If the FETCH is successful, the output

// descriptor array will contain returned

// values from the first row of the result set.

// SQLDA.NumOutputs contains the number of

// output descriptors.

// The SQLDA.OutParmType array will contain

// NumOutput entries and each entry will contain

// an value of the enumerated data type ParmType

// (such as TypeInteger!, or TypeString!).

CHOOSE CASE SQLDA.OutParmType[1]

CASE TypeString!

Stringvar = GetDynamicString(SQLDA, 1)

CASE TypeInteger!

Intvar = GetDynamicNumber(SQLDA, 1)

END CHOOSECLOSE my_cursor ;

动态sql语句 格式1 直接执行sql声明(sqlstatement) 例程： stringMysql Mysql = "CREATE TABLE Employee "& +"(emp_id integer not null,"& +"dept_id integer not null, "& +"emp_fname char(10) not null, "& +"emp_lname char(20) not null)" EXECUTE IMMEDIATE :Mysql ; 动态sql语句 格式2 准备sql声明(sqlsa) 执行sql声明(sqlsa) 例程： INTEmp_id_var = 56 PREPARE SQLSA FROM "DELETE FROM employee WHERE emp_id=?" ; EXECUTE SQLSA USING :Emp_id_var ; 动态sql语句 格式3 为动态传输区 声明 游标|进程动态游标|进程 根据sql声明(sqlca)准备动态传输区 打开动态游标 取出游标进程值 关闭游标 例程： integer Emp_id_var DECLARE my_cursor DYNAMIC CURSOR FOR SQLSA ; PREPARE SQLSA FROM "SELECT emp_id FROM employee" ; OPEN DYNAMIC my_cursor ; FETCH my_cursor INTO :Emp_id_var ; CLOSE my_cursor ; 动态sql 格式4 为动态传输区 声明 游标|进程动态游标|进程 根据sql声明(sqlca)准备动态传输区 写入动态描述区 打开动态游标 取出游标进程值 关闭游标 例程： string Stringvar, Sqlstatement integer Intvar Sqlstatement = "SELECT emp_id FROM employee" PREPARE SQLSA FROM :Sqlstatement ; DESCRIBE SQLSA INTO SQLDA ; DECLARE my_cursor DYNAMIC CURSOR FOR SQLSA ; OPEN DYNAMIC my_cursor USING DESCRIPTOR SQLDA ; FETCH my_cursor USING DESCRIPTOR SQLDA ; // If the FETCH is successful, the output // descriptor array will contain returned // values from the first row of the result set. // SQLDA.NumOutputs contains the number of // output descriptors. // The SQLDA.OutParmType array will contain // NumOutput entries and each entry will contain // an value of the enumerated data type ParmType // (such as TypeInteger!, or TypeString!). CHOOSE CASE SQLDA.OutParmType[1] CASE TypeString! Stringvar = GetDynamicString(SQLDA, 1) CASE TypeInteger! Intvar = GetDynamicNumber(SQLDA, 1) END CHOOSECLOSE my_cursor ;