Dynamic SQL detailed in Oracle

1. static sqlsql and dynamic SQLThe Oracle compiled PL/SQL program block is divided into two types: one for the pre-assembly (early binding), that is, the SQL statement is determined during program compilation, most of the compilation is of this type, and the other is the late-linking (late binding), That is, SQL statements can only be established at run time, for example, when the query condition is user input, then Oracle's SQL engine can not be determined at compile time for the program statement, only after the user entered a certain query criteria to be submitted to the SQL engine for processing. 　　Typically, static SQL takes the previous compilation, whereas dynamic SQL uses the latter one. This paper mainly discusses the development of dynamic SQL, and finally gives some practical development techniques. 2 . Dynamic SQL program DevelopmentUnderstanding the principle of dynamic SQL compilation, but also mastered its basic development ideas. Since dynamic SQL is an "indeterminate" SQL, its execution has its corresponding characteristics. The Execute immediate statement is provided in Oracle to execute dynamic SQL with the following syntax: http://www.cnblogs.com/gaolonglong/archive/2011/05/31/2064790.html Excute immediate Dynamic SQL statement using-bound parameter list returning into output parameter list; This statement is described as follows: 1) Dynamic SQL refers to DDL and indeterminate DML (that is, DML with parameters) 2) binding parameter list as input parameter list 　　, which is the type in type, which is bound at run time with the arguments in the dynamic SQL statement (actually placeholders, which can be understood as formal parameters inside the function). 　　3) The output parameter list is the list of parameters returned after the dynamic SQL statement executes.    4) Since dynamic SQL is determined at runtime, it can lose some system performance in exchange for its flexibility relative to static. More articles: Oracle dynamic SQL Execute immediate notation zt-http://space.itpub.net/14740/viewspace-663092oracle dynamic SQL Assembly Essentials-http:// Mikixiyou.iteye.com/blog/1720563oracle Dynamic sql-http://blog.csdn.net/hncsy403/article/details/7902908

Dynamic SQL detailed in Oracle