Shulou(Shulou.com)05/31 Report--

This article mainly explains "what types of sentence formats are there in dynamic SQL". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn what types of sentence formats are available in dynamic 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 typeIntegerical, or TypeString!).

CHOOSE CASE SQLDA.OutParmType [1]

CASE TypeString!

Stringvar = GetDynamicString (SQLDA, 1)

CASE TypeInteger!

Intvar = GetDynamicNumber (SQLDA, 1)

END CHOOSE

CLOSE my_cursor

Thank you for your reading, the above is the content of "what types of sentence formats are there in dynamic SQL". After the study of this article, I believe you have a deeper understanding of what types of sentence formats there are in dynamic SQL, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.