动态SQL / PLSQL被认为是Oracle PL / SQL中最高级的主题之一。尽管它根本不是一个新主题，但它仍然是消化和掌握最具挑战性的概念之一。
动态SQL只是SQL代码的任何部分，在编译之前未明确写入。因此，例如，如果您不知道SQL查询中的“WHERE”谓词，则必须使用动态SQL。Oracle为我们提供了两种不同的方法来构建动态SQL / plsql代码，第一种是NativeDynamic SQL（NDS）。使用优雅，易于使用EXECUTE IMMEDIATE和OPEN.. FOR 语句，这种方法是在场景中超过90％绰绰有余。但剩下的10％呢？这是它变得更复杂的地方。
当面对动态SQL方法四，你需要你的代码执行包含不同数量的绑定变量的动态SQL语句时，NDS是不够的，因为使用NDS，你需要知道列数和数据类型你要检索它们（列类型）。Oracle提供了一种替代（实际上更老）的方法来处理代码需要超级动态的最极端情况，那就是DBMS_SQL包。
DBMS_SQL能够处理您可能遇到的所有未知数。使用DBMS_SQL时，使用DBMS_SQL游标时只使用特殊的游标类型（不是我们知道的常规游标，并且总是使用它），并且只获取唯一的游标ID。然后，我们获得完整的表描述，包括列的类型和属性。使用所有这些信息，我们可以将数据提取到正确类型的变量中。该过程通常如下进行：
定义一个游标; 只是一个ID
建立你的查询; 您将要执行的查询
使用您的查询解析光标并检查查询中的任何错误。
绑定变量（如果有）。
获取表格描述; 列数和类型信息。
定义列并将它们链接到正确的变量。
执行游标和查询。
从列中获取实际数据。
欣赏你的代码！
现在，让我们谈谈我们想象中的情景。出于某种原因，我们需要创建一个可用作“数据库”的表来存储来自任何其他表的任何类型的数据，并将其保存为加密形式。我们必须这样做，只有将数据删除到“保险库”的人才能解密并检索它。
为此，我们需要许多部分，例如一些自定义数据类型和对象，但更重要的是，我们必须构建一个能够从任何表中收集数据并加密然后存储在Vault中的函数。
请注意，该函数的编写方式使得可以在不知道任何表的情况下处理任何表，那么我们唯一的工具是什么呢？很简单，这是DBMS_SQL包！
使用Data Vault虚构函数，我将尝试演示和解释依赖于DBMS_SQL的基于动态SQL的函数的最重要，实用的组件。
最后一点，这是一个教程，旨在演示如何使用DBMS_SQL，因此它不是100％生产就绪，许多东西可以增强，甚至可以用其他方式完成。还有一些安全问题，但同样，它是关于DBMS_SQL的。
安装程序
1.我构建了一个原始类型的嵌套表来存储将存储在Vault中的加密数据。
CREAT OR REPLACE TYPE raw_tab AS NESTED TABLE OF RAW(2000)
2.为了存储列信息，我创建了一个模仿DBMS_SQL_DESC类型的对象，我还创建了一个该对象的嵌套表来保存有关所有表列的信息。
艾哈迈德创建或 替换 类型。“COL_INFO_OBJ” 是 对象
（
column_type NUMBER，
column_name VARCHAR2（32），
column_name_len NUMBER，
column_schema_name VARCHAR2（32），
column_precision NUMBER，
column_scale NUMBER
）;
/
CREATE OR REPLACE TYPE COL_INFO_OBJ_T AS TABLE OF col_info_obj;
/我选择创建两张桌子; 一个是将要存储加密数据的保险库本身，另一个是保存有关插入操作的信息的主记录，在构建恢复/解密功能或过程时非常重要。为了演示NDS与DBMS_SQL一起使用，如果其中任何一个表不存在，该函数将创建所需的表。
功能 dynamic_vault_ins（table_name IN VARCHAR2，cond IN VARCHAR2 DEFAULT'1 = 1'，
enc_key IN VARCHAR2 DEFAULT NULL，del_flag VARCHAR2 DEFAULT '否'）
返回 号码 是
encrypt_typ NUMBER：= DBMS_CRYPTO.encrypt_aes256 + DBMS_CRYPTO.chain_cbc + DBMS_CRYPTO.pad_pkcs5;
enc_key_var VARCHAR2（256）;
raw_col_v RAW（2000）;
encrypted_col_v RAW（2000）;
encr_row raw_tab：= raw_tab（）;
sql_query VARCHAR2（2000）;
col_count NUMBER ;
col_desc DBMS_SQL.desc_tab：= DBMS_SQL.desc_tab（）;
cur_id NUMBER ;
row_counter NUMBER：= 0 ;
tab_exist NUMBER ;
num_var NUMBER ;
char_var VARCHAR2（2000）;
date_var 日期 ;
c_var CHAR（4）;
row_id_var NUMBER：= 1 ;
opr_id_var NUMBER：= 1 ;
temp_var NUMBER ;
col_details col_info_obj_t：= col_info_obj_t（）;
bad_cond EXCEPTION ;
开始
IF REGEXP_COUNT（UPPER（cond），'; | DELETE | DROP | CREATE | INSERT | UPDATE | GRANT | TRUNCATE'）> 0 那么
提升 bad_cond;
结束 如果 ;
IF enc_key IS NULL 或 LENGTH（enc_key）！= 32 那么
enc_key_var：= 'ahmed-rony-yousef-29102006-12-11' ;
其他
enc_key_var：= enc_key;
结束 如果 ;
SELECT COUNT（tname）INTO tab_exist FROM tab WHERE tname = 'DATA_VAULT' ;
IF tab_exist = 0 那么
EXECUTE IMMEDIATE'CREATE TABLE data_vault（row_id number，opr_id NUMBER，
ins_date timestamp，table_name VARCHAR2（32），enc_data raw_tab）
NESTED TABLE enc_data STORE AS enc_data' ;
其他
EXECUTE IMMEDIATE'SELECT nvl（MAX（row_id），0）+ 1，nvl（MAX（opr_id），0）+ 1 FROM data_vault'
INTO row_id_var，opr_id_var;
结束 如果 ;
SELECT COUNT（tname）INTO tab_exist FROM tab WHERE tname = 'VAULT_COL_INFO2' ;
IF tab_exist = 0 那么
EXECUTE IMMEDIATE'CREATE TABLE vault_col_info2（
opr_id NUMBER，inst_time TIMESTAMP，no_of_rows NUMBER，org_query varchar（2000），
col_data col_info_obj_t）
嵌套表col_data存储为col_data2' ;
结束 如果 ;
/ *构建查询* /
sql_query：= 'select * from' || DBMS_ASSERT.sql_object_name（table_name）|| 'where' || cond;
/ *打开光标* /
cur_id：= DBMS_SQL.open_cursor;
/ *解析光标* /
DBMS_SQL.PARSE（cur_id，sql_query，DBMS_SQL。天然）;
/ *构建列的描述并将结果存储在特殊的中
记录集合类型'dbms_sql.desc_tab'并获取列和的计数
存储在变量中（在这种情况下，它是col_count * /
DBMS_SQL.describe_columns（cur_id，col_count，col_desc）;
/ *扩展嵌套表，然后存储colums详细信息
objetcs的自定义嵌套表* /
col_details.EXTEND（col_count）;
FOR i IN 1 ..col_count LOOP
col_details（i）：= col_info_obj（
col_desc（ⅰ）.col_type，
col_desc（ⅰ）.col_name，
col_desc（ⅰ）.col_name_len，
col_desc（ⅰ）.col_schema_name，
col_desc（ⅰ）.col_precision，
col_desc（ⅰ）.col_scale）;
结束 循环 ;
/ *定义列类型
如果你要使用FETCH，你必须定义这些colun
你会得到一个错误* /
FOR i IN 1 ..col_count LOOP
case col_desc（i）.col_type
当 2 那么
DBMS_SQL.define_column（cur_id，i，num_var）;
当 12 那么
DBMS_SQL.define_column（cur_id，i，date_var）;
当 1 那么
DBMS_SQL.define_column（cur_id，i，char_var，200）;
当 96 那么
DBMS_SQL.define_column（cur_id，i，c_var，12）;
END 情况 ;
结束 循环 ;
/ *执行光标* /
temp_var：= DBMS_SQL。执行（cur_id）;
encr_row.EXTEND（col_count）;
/ *从行中获取数据并对其进行加密* /
当 DBMS_SQL.fetch_rows（cur_id）> 0 LOOP时
FOR i IN 1 ..col_count LOOP
case col_desc（i）.col_type
当 2 那么
DBMS_SQL.COLUMN_VALUE（cur_id，i，num_var）;
/ *将数据转换为RAW * /
raw_col_v：= UTL_I18N.string_to_raw（TO_CHAR（NVL（num_var，0）），'AL32UTF8'）;
- 加密数据
encrypted_col_v：= DBMS_CRYPTO.encrypt（src => raw_col_v，
typ => encrypt_typ，key => UTL_I18N.string_to_raw（enc_key_var））;
encr_row（i）：= encrypted_col_v;
-
当 12 那么
DBMS_SQL.COLUMN_VALUE（cur_id，i，date_var）;
raw_col_v：= UTL_I18N.string_to_raw（TO_CHAR（NVL（date_var，TO_DATE（'1999/07/15'，'YYYY / MM / DD'））），'AL32UTF8'）;
encrypted_col_v：= DBMS_CRYPTO.encrypt（src => raw_col_v，
typ => encrypt_typ，key => UTL_I18N.string_to_raw（enc_key_var））;
encr_row（i）：= encrypted_col_v;
当 1 那么
DBMS_SQL.COLUMN_VALUE（cur_id，i，char_var）;
raw_col_v：= UTL_I18N.string_to_raw（NVL（char_var，' - '），'AL32UTF8'）;
encrypted_col_v：= DBMS_CRYPTO.encrypt（src => raw_col_v，
typ => encrypt_typ，key => UTL_I18N.string_to_raw（enc_key_var））;
encr_row（i）：= encrypted_col_v;
当 96 那么
DBMS_SQL.COLUMN_VALUE（cur_id，i，c_var）;
raw_col_v：= UTL_I18N.string_to_raw（TO_CHAR（c_var），'AL32UTF8'）;
encrypted_col_v：= DBMS_CRYPTO.encrypt（src => raw_col_v，
typ => encrypt_typ，key => UTL_I18N.string_to_raw（enc_key_var））;
encr_row（i）：= encrypted_col_v;
其他
DBMS_SQL.COLUMN_VALUE（cur_id，i，char_var）;
raw_col_v：= UTL_I18N.string_to_raw（char_var，'AL32UTF8'）;
encrypted_col_v：= DBMS_CRYPTO.encrypt（src => raw_col_v，
typ => encrypt_typ，key => UTL_I18N.string_to_raw（enc_key_var））;
encr_row（i）：= encrypted_col_v;
END 情况 ;
结束 循环 ;
/ *这里你必须使用动态sql，因为如果表仍然不存在
编译器会引发错误'表或视图不存在'* /
EXECUTE IMMEDIATE '插入data_vault值（：1，：2，：3，：4，：5）'
使用 row_id_var，opr_id_var，SYSTIMESTAMP，table_name，encr_row;
row_id_var：= row_id_var + 1 ;
row_counter：= row_counter + 1 ;
结束 循环 ;
/ *这里你必须使用动态sql，因为如果表仍然不存在
编译器会引发错误'表或视图不存在'* /
EXECUTE IMMEDIATE '插入vault_col_info2值（：1，：2，：3，：4，：5）'
使用 opr_id_var，SYSTIMESTAMP，row_counter，sql_query，col_details;
IF del_flag = 'DELETE' 或 del_flag = 'YES' 那么
EXECUTE IMMEDIATE '删除' || table_name || 'where' || cond;
DBMS_OUTPUT.put_line（删除的总oreginal行数为：' || SQL％ROWCOUNT）;
结束 如果 ;
RETURN row_counter;
COMMIT ;
DBMS_SQL.close_cursor（cur_id）;
例外
WHEN bad_cond THEN
raise_application_error（ - 20001，'Bad or dangerous condetions'）;
return row_counter;
WHEN OTHERS THEN
raise_application_error（ - 20002，'发生了错误！==>' || SQLERRM）;
return row_counter;
END dynamic_vault_ins;