PL/SQL Dynamic SQL (de Oracle)

 Enlace al capitulo de SQL dinámico:

https://docs.oracle.com/database/121/LNPLS/dynamic.htm#LNPLS011

Dynamic SQL is a programming methodology for generating and running SQL statements at run time.

It is useful when writing general-purpose and flexible programs like ad hoc query systems, when writing programs that must run database definition language (DDL) statements, or when you do not know at compile time the full text of a SQL statement or the number or data types of its input and output variables.

PL/SQL provides two ways to write dynamic SQL:

Native dynamic SQL, a PL/SQL language (that is, native) feature for building and running dynamic SQL statements

DBMS_SQL package, an API for building, running, and describing dynamic SQL statements

Native dynamic SQL code is easier to read and write than equivalent code that uses the DBMS_SQL package, and runs noticeably faster (especially when it can be optimized by the compiler). However, to write native dynamic SQL code, you must know at compile time the number and data types of the input and output variables of the dynamic SQL statement. If you do not know this information at compile time, you must use the DBMS_SQL package. You must also use the DBMS_SQL package if you want a stored subprogram to return a query result implicitly (not through an OUT REF CURSOR parameter).

When you need both the DBMS_SQL package and native dynamic SQL, you can switch between them, using the "DBMS_SQL.TO_REFCURSOR Function" and "DBMS_SQL.TO_CURSOR_NUMBER Function".

Topics

When You Need Dynamic SQL

Native Dynamic SQL

DBMS_SQL Package

SQL Injection

.....

Fuentes.

Artículo:   "7 . PL/SQL Dynamic SQL" Publicado en https://docs.oracle.com/. Consultado el 06/11/2022.

URL: https://docs.oracle.com/database/121/LNPLS/dynamic.htm#LNPLS011