您的位置:首页技术开发数据库教程 → 动态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

文章评论
发表评论

热门文章 oracle10g安装图解(wi

最新文章 数据库流行度排行2019oracle10g安装图解(wi SQL2008全部数据导出导入两种方法SQL2005新建复制“找不到存储过程 错误:28Dos远程登录mysql数据库详细图文教程mysql怎么开启远程登录功能

人气排行 mysql自动定时备份数据库的最佳方法-支持wiVisual Foxpro 6.0安装向导图文教程SQL Server 2008 安装图文教程SQL2008全部数据导出导入两种方法SQL 2000/2005/2008 的收缩日志方法,和清理mysql出 Can't connect to MySQL server onoracle10g安装图解(win7)sql2005安装图解_(sql server2005)安装教程