动态魔术使用DBMS_SQL

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