1.1、获取DDL
--获取包的定义 select DBMS_METADATA.GET_DDL('PACKAGE','PACKAGENAME','USERNAME') from dual --获取包体定义 select DBMS_METADATA.GET_DDL('PACKAGE BODY','PACKAGEBODYNAME','USERNAME') from dual |
1.2、包的使用
--1、定义包规范 create or replace package p_stu as --定义结构体 type re_stu is record( rname student.name%type, rage student.age%type ); --定义游标 type c_stu is ref cursor; --定义函数 function numAdd(num1 number,num2 number)return number; --定义过程 procedure GetStuList(cid in varchar2,c_st out c_stu); end; --2、实现包体，名称一致。 --3、使用 declare c_stu p_stu.c_stu; --定义包中游标变量 r_stu p_stu.re_stu; --定义包中结构体变量 num number; begin --使用及遍历包中过程返回的结果集 p_stu.GetStuList('C001',c_stu); loop fetch c_stu into r_stu; exit when c_stu%notfound; dbms_output.put_line('姓名='||r_stu.rname); end loop; --使用包中的方法 select p_stu.numAdd(5,6) into num from dual; dbms_output.put_line('Num='||num); end; |
1、函数
2.1、函数定义
select dbms_metadata.get_ddl('FUNCTION','FUNCTION_NAME','FUNCTION_OWNER') from dual; |
2.2、动态分页函数
create or replace procedure sp_exec_dynamic_page( /* 功能：实现动态分页 */ var_tablename in VARCHAR2, -- 表名 employees e,departments d var_tablecolumn in VARCHAR2, -- 查询列 a.employee_id,b.department_name var_where in VARCHAR2, -- 查询条件 b.department_name like 'S%' var_ordercolumn in VARCHAR2, -- 排序 b.department_name desc var_pagesize in NUMBER, -- 每页大小 10 var_curpage in NUMBER, -- 当前页 1 var_rowcount OUT NUMBER, -- 返回总条数 --var_pagecount OUT NUMBER, -- 返回总页数 var_cursor OUT sys_refcursor -- 返回分页结果集 ) is v_tablecolumn varchar2(10); v_where varchar2(200); v_ordercolumn varchar2(200); v_sql varchar2(2000); v_count varchar2(2000); v_pagesize integer; v_total_page integer; --v_total_count number(10); v_curpage integer; v_start_record integer; v_end_record integer; v_count_sql varchar2(2000); begin --必须输入表名，否则会报错 if trim(var_tablename) is null then raise_application_error(-20001,'必须输入表明才能进行分页！'); end if; --输入列名,列名必须以‘，’隔开，如果不输入则默认为全部列 if trim(var_tablecolumn) is not null then v_tablecolumn:=var_tablecolumn; else v_tablecolumn:='*'; end if; --判断是否存在where条件 if trim(var_where) is not null then v_where:=' where 1=1 and '||var_where; else v_where:=' where 1=1'; end if; --判断是否有order 不要条件 if trim(var_ordercolumn) is not null then v_ordercolumn:=var_ordercolumn; else v_ordercolumn:=''; end if; --查询总共的记录条数 v_count:='SELECT COUNT(*) FROM '||var_tablename ||v_where; execute immediate v_count into var_rowcount; --判断是否输入了每页显示的条数 if var_pagesize is not null then v_pagesize:=var_pagesize; else v_pagesize:=10; end if; --得到总的页数 if mod(var_rowcount,v_pagesize)=0 then v_total_page:=var_rowcount/v_pagesize; else v_total_page:=floor(var_rowcount/v_pagesize)+1; end if; --如果输入的当前页大于总的页，则取最后一页 if var_curpage>v_total_page then v_curpage:=v_total_page; else v_curpage:=var_curpage; end if; --开始的记录条数 v_start_record:=(v_curpage-1)*v_pagesize+1; --结束的记录条数 v_end_record:=v_curpage*v_pagesize; --拼接分页的sql语句 v_count_sql:='(select '|| v_tablecolumn ||' from '|| var_tablename || v_where || v_ordercolumn||') e'; v_sql:='select * from ('||'select rownum rn, e.* from '|| v_count_sql ||' where rownum<='|| v_end_record ||') p where p.rn>='||v_start_record; --将查询的结果放入游标中 open var_cursor for v_sql; end sp_exec_dynamic_page; |
2.3、行列转换函数
1）pivot函数：行转列函数：
语法：pivot(任一聚合函数 for 需专列的值所在列名 in (需转为列名的值))；
2）unpivot函数：列转行函数：
语法：unpivot(新增值所在列的列名 for 新增列转为行后所在列的列名 in (需转为行的列名))；
执行原理：将pivot函数或unpivot函数接在查询结果集的后面。相当于对结果集进行处理。
--1、环境准备 create table t(t1 varchar2(10),t2 varchar2(10),t3 int); insert into t values('第一行','COL_1',12); insert into t values('第一行','COL_2',24); insert into t values('第一行','COL_3',44); insert into t values('第二行','COL_1',12); insert into t values('第二行','COL_2',24); insert into t values('第二行','COL_3',44); insert into t values('第三行','COL_1',12); insert into t values('第三行','COL_2',24); insert into t values('第三行','COL_3',44); insert into t values('第四行','COL_1',12); insert into t values('第四行','COL_2',24); insert into t values('第四行','COL_3',44); --2、pivot函数行转列 --用聚合函数对数据列进行求值，将t2列中的值’COL_1‘，’COL_2‘，‘COL_3’转化为列名，并为其加上别名。 select * from t pivot (sum(t3) for t2 in ('COL_1' as 第一列,'COL_2'as 第二列 ,'COL_3' as 第三列)); --3、unpivot函数列转行 --在unpivot函数中，aa与bb为新增的列名，分别表示由列转换为行后的数据所在的列名和数据所在的列名。 select * from t pivot (sum(t3) for t2 in ('COL_1' as 第一列,'COL_2'as 第二列 ,'COL_3' as 第三列)) unpivot ( aa for bb in(第一列,第二列,第三列)); |
2.4、身份证校验函数
CREATE OR REPLACE FUNCTION Func_checkidcard (p_idcard IN VARCHAR2) RETURN INT IS v_regstr VARCHAR2 (2000); v_sum NUMBER; v_mod NUMBER; v_checkcode CHAR (11) := '10X98765432'; v_checkbit CHAR (1); v_areacode VARCHAR2 (2000) := '11,12,13,14,15,21,22,23,31,32,33,34,35,36,37,41,42,43,44,45,46,50,51,52,53,54,61,62,63,64,65,71,81,82,91,'; BEGIN CASE LENGTHB (p_idcard) WHEN 15 THEN -- 15位 IF INSTRB (v_areacode, SUBSTR (p_idcard, 1, 2) || ',') = 0 THEN RETURN 0; END IF; IF MOD (TO_NUMBER (SUBSTRB (p_idcard, 7, 2)) + 1900, 400) = 0 OR ( MOD (TO_NUMBER (SUBSTRB (p_idcard, 7, 2)) + 1900, 100) <> 0 AND MOD (TO_NUMBER (SUBSTRB (p_idcard, 7, 2)) + 1900, 4) = 0 ) THEN -- 闰年 v_regstr := '^[1-9][0-9]{5}[0-9]{2}((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1])|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)|02(0[1-9]|[1-2][0-9]))[0-9]{3}$'; ELSE v_regstr := '^[1-9][0-9]{5}[0-9]{2}((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1])|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)|02(0[1-9]|1[0-9]|2[0-8]))[0-9]{3}$'; END IF; IF REGEXP_LIKE (p_idcard, v_regstr) THEN RETURN 1; ELSE RETURN 0; END IF; WHEN 18 THEN -- 18位 IF INSTRB (v_areacode, SUBSTRB (p_idcard, 1, 2) || ',') = 0 THEN RETURN 0; END IF; IF MOD (TO_NUMBER (SUBSTRB (p_idcard, 7, 4)), 400) = 0 OR ( MOD (TO_NUMBER (SUBSTRB (p_idcard, 7, 4)), 100) <> 0 AND MOD (TO_NUMBER (SUBSTRB (p_idcard, 7, 4)), 4) = 0 ) THEN -- 闰年 v_regstr := '^[1-9][0-9]{5}(19|20)[0-9]{2}((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1])|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)|02(0[1-9]|[1-2][0-9]))[0-9]{3}[0-9Xx]$'; ELSE v_regstr := '^[1-9][0-9]{5}(19|20)[0-9]{2}((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1])|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)|02(0[1-9]|1[0-9]|2[0-8]))[0-9]{3}[0-9Xx]$'; END IF; IF REGEXP_LIKE (p_idcard, v_regstr) THEN v_sum := ( TO_NUMBER (SUBSTRB (p_idcard, 1, 1)) + TO_NUMBER (SUBSTRB (p_idcard, 11, 1)) ) * 7 + ( TO_NUMBER (SUBSTRB (p_idcard, 2, 1)) + TO_NUMBER (SUBSTRB (p_idcard, 12, 1)) ) * 9 + ( TO_NUMBER (SUBSTRB (p_idcard, 3, 1)) + TO_NUMBER (SUBSTRB (p_idcard, 13, 1)) ) * 10 + ( TO_NUMBER (SUBSTRB (p_idcard, 4, 1)) + TO_NUMBER (SUBSTRB (p_idcard, 14, 1)) ) * 5 + ( TO_NUMBER (SUBSTRB (p_idcard, 5, 1)) + TO_NUMBER (SUBSTRB (p_idcard, 15, 1)) ) * 8 + ( TO_NUMBER (SUBSTRB (p_idcard, 6, 1)) + TO_NUMBER (SUBSTRB (p_idcard, 16, 1)) ) * 4 + ( TO_NUMBER (SUBSTRB (p_idcard, 7, 1)) + TO_NUMBER (SUBSTRB (p_idcard, 17, 1)) ) * 2 + TO_NUMBER (SUBSTRB (p_idcard, 8, 1)) * 1 + TO_NUMBER (SUBSTRB (p_idcard, 9, 1)) * 6 + TO_NUMBER (SUBSTRB (p_idcard, 10, 1)) * 3; v_mod := MOD (v_sum, 11); v_checkbit := SUBSTRB (v_checkcode, v_mod + 1, 1); IF v_checkbit = upper(substrb(p_idcard,18,1)) THEN RETURN 1; ELSE RETURN 0; END IF; ELSE RETURN 0; END IF; ELSE RETURN 0; -- 身份证号码位数不对 END CASE; EXCEPTION WHEN OTHERS THEN RETURN 0; END fn_checkidcard; / Show Err |
2.5、md5算法实现简单加密脱敏函数
--1、创建函数 create or replace function fn_md5(input_string VARCHAR2) return varchar2 IS raw_input RAW(128) := UTL_RAW.CAST_TO_RAW(input_string); decrypted_raw RAW(2048); error_in_input_buffer_length EXCEPTION; BEGIN sys.dbms_obfuscation_toolkit.MD5(input => raw_input,checksum => decrypted_raw); return rawtohex(decrypted_raw); END; / --2、使用 select fn_md5(job) as string from scott.emp; |
