动态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;