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

去去7

去去7

2016-01-29 16:31

动态SQL四种类型的语句格式,动态SQL四种类型的语句格式
  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 ;
 
展开更多 50%)
分享

猜你喜欢

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

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

动态SQL语句

SQLServer
动态SQL语句

s8lol主宰符文怎么配

英雄联盟 网络游戏
s8lol主宰符文怎么配

腹泻宝宝:四种类型食疗别错过

孕妇食谱
腹泻宝宝:四种类型食疗别错过

C#中方法参数的四种类型

电脑网络
C#中方法参数的四种类型

lol偷钱流符文搭配推荐

英雄联盟 网络游戏
lol偷钱流符文搭配推荐

女人气血虚的四种类型

女性健康
女人气血虚的四种类型

现代坐月子 四种类型全面PK

月子
现代坐月子 四种类型全面PK

lolAD刺客新符文搭配推荐

英雄联盟
lolAD刺客新符文搭配推荐

全面接触SQL语法(4)

全面接触SQL语法(4)

《刀塔传奇》辅助肉盾奶妈介绍

《刀塔传奇》辅助肉盾奶妈介绍
下拉加载更多内容 ↓