动态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;
标签:DBMS,raw,col,魔术,SQL,var,id
From: https://blog.51cto.com/u_16145034/6486215