What are the types of statement formats for dynamic SQL
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!
