您的位置：首页 → 技术开发 → 数据库教程 → 动态SQL四种类型的语句格式

动态SQL四种类型的语句格式

时间：2004/11/7 4:14:00来源：本站整理作者：蓝点我要评论(0)

1.Dynamic SQL Format 1



EXECUTE IMMEDIATE SQLStatement     {USING TransactionObject} ;



eg:

string            Mysql

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 ;



2.Dynamic SQL Format 2



PREPARE DynamicStagingArea FROM SQLStatement     {USING TransactionObject} ;

EXECUTE DynamicStagingArea USING {ParameterList} ;



eg:

INT        Emp_id_var = 56

PREPARE SQLSA

    FROM "DELETE FROM employee WHERE emp_id=?" ;

EXECUTE SQLSA USING :Emp_id_var ;





3.Dynamic SQL Format 3



DECLARE Cursor | Procedure     DYNAMIC CURSOR | PROCEDURE      FOR DynamicStagingArea ;

PREPARE DynamicStagingArea FROM SQLStatement     {USING TransactionObject} ;

OPEN DYNAMIC Cursor     {USING ParameterList} ;

EXECUTE DYNAMIC Procedure    {USING ParameterList} ;

FETCH Cursor | Procedure     INTO HostVariableList ;

CLOSE Cursor | Procedure ;

eg:

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 ;





4.Dynamic SQL Format 4



DECLARE Cursor | Procedure     DYNAMIC CURSOR | PROCEDURE      FOR DynamicStagingArea ;

PREPARE DynamicStagingArea FROM SQLStatement    {USING TransactionObject} ;

DESCRIBE DynamicStagingArea    INTO DynamicDescriptionArea ;

OPEN DYNAMIC Cursor | Procedure    USING DESCRIPTOR DynamicDescriptionArea ;

EXECUTE DYNAMIC Cursor | Procedure    USING DESCRIPTOR DynamicDescriptionArea ;

FETCH Cursor | Procedure     USING DESCRIPTOR DynamicDescriptionArea ;

CLOSE Cursor | Procedure ;



eg:



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 CHOOSE

CLOSE my_cursor ;

相关阅读 Mac访问Windows共享文件夹Windows 7正版系统验证方法windows 8.1系统版本号查看方法Windows 8.1系统电话激活时无法输入微软返回代码解决方法Windows 8如何调整屏幕分辨率windows8.1磁盘占用100%解决方法Mac双系统如何删除Boot Camp安装的Windows分区Apple教你如何在Mac 上运行 Windows

文章评论

查看所有0条评论>>

发表评论

我来说两句...

提交评论