什么是动态PL/SQL？

[jettli]

我是初学者，现在不明白什么是动态PL/SQL？为什么要采用动态的呢？它有什么优缺点？里面类似“:name,:dept”之类在前面加冒号的变量用不用声明，它是什么时候被赋值的？

[老年程序员]

Most PL/SQL programs do a specific, predictable job. For example, a stored procedure might accept an employee number and salary increase, then update the sal column in the emp table. In this case, the full text of the UPDATE statement is known at compile time. Such statements do not change from execution to execution. So, they are called static SQL statements.

However, some programs must build and process a variety of SQL statements at run time. For example, a general-purpose report writer must build different SELECT statements for the various reports it generates. In this case, the full text of the statement is unknown until run time. Such statements can, and probably will, change from execution to execution. So, they are called dynamic SQL statements.

[老年程序员]

Dynamic SQL statements are stored in character strings built by your program at run time. Such strings must contain the text of a valid SQL statement or PL/SQL block. They can also contain placeholders for bind arguments. A placeholder is an undeclared identifier, so its name, to which you must prefix a colon, does not matter. For example, PL/SQL makes no distinction between the following strings:

'DELETE FROM emp WHERE sal > :my_sal AND comm < :my_comm'
'DELETE FROM emp WHERE sal > :s AND comm < :c'


To process most dynamic SQL statements, you use the EXECUTE IMMEDIATE statement. However, to process a multi-row query (SELECT statement), you must use the OPEN-FOR, FETCH, and CLOSE statements.

[3yugui]

i agree

[jettli]

:name之类叫做“占位符”对吗？它是在什么时候被赋值的呢？是不是经常用一个过程把变量的值赋给它？

[jettli]

??

[catwg77]

这这个动态SQL被执行之前赋值就可以了；应该可以直接赋值，既的不太清楚了，变量是可以的。