Oracle中加密解密的数据库函数，创建（包含游标）和使用存储过程

1、加密函数  gen_encr：

create or replace function gen_encr(v_input varchar2) return raw

is

v_result raw(256);

c_key char(32) := '1q2wa3es4rz5tx6ycD*UF8tif9ogjen2';

begin

v_result := DBMS_CRYPTO.ENCRYPT(

UTL_I18N.STRING_TO_RAW(v_input, 'AL32UTF8'),

DBMS_CRYPTO.ENCRYPT_AES256+DBMS_CRYPTO.CHAIN_CBC+DBMS_CRYPTO.PAD_PKCS5,

UTL_I18N.STRING_TO_RAW(c_key, 'AL32UTF8'));

return v_result;

end;

2、解密函数 gen_decr：

create or replace function gen_decr(v_input raw) return varchar2

is

v_result raw(256);

c_key char(32) := '1q2wa3es4rz5tx6ycD*UF8tif9ogjen2';

begin

v_result := DBMS_CRYPTO.DECRYPT(

v_input,

DBMS_CRYPTO.ENCRYPT_AES256+DBMS_CRYPTO.CHAIN_CBC+DBMS_CRYPTO.PAD_PKCS5,

UTL_I18N.STRING_TO_RAW(c_key, 'AL32UTF8'));

return UTL_I18N.RAW_TO_CHAR(v_result, 'AL32UTF8');

end;

3、创建一个不带输入输出参数的且里面包含加密函数的存储过程 lpdev_encr：

CREATE OR REPLACE PROCEDURE lpdev_encr
AS
  CURSOR group_buying
  IS
    SELECT gen_encr(name),
      gen_encr(mobile),
      gen_encr(email),
      gen_encr(address),
      id
    FROM GROUP_BUYING_REG;
  --  截止到上面是定义了一个名叫group_buying的游标
  pname GROUP_BUYING_REG.name%type ;
  pmobile GROUP_BUYING_REG.mobile%type ;
  pemail GROUP_BUYING_REG.email%type ;
  paddress GROUP_BUYING_REG.address%type ;
  pid GROUP_BUYING_REG.id%type ;
  --  上面这些定义了待会用来存储游标赋值的变量
BEGIN
  OPEN group_buying;
  LOOP
    --  开启游标把grouo_buing中的值赋给上面定义好的变量
    FETCH group_buying
    INTO pname,
      pmobile,
      pemail,
      paddress,
      pid;
    UPDATE GROUP_BUYING_REG
      --  set语句里面写的是变化的内容，把加密后的字段内容重新赋值给原来的字段
    SET name  =pname,
      mobile  =pmobile,
      email   =pemail,
      address =paddress
    WHERE id  =pid;
    EXIT
  WHEN group_buying%notfound;
  END LOOP;
  CLOSE group_buying;
END;

4、创建一个不带输入输出参数的且里面包含解密函数的存储过程 lpdev_decr：

CREATE OR REPLACE PROCEDURE lpdev_decr
AS
  CURSOR group_buying
  IS
    SELECT gen_decr(name),
      gen_decr(mobile),
      gen_decr(email),
      gen_decr(address),
      id
    FROM GROUP_BUYING_REG;
  --  截止到上面是定义了一个名叫group_buying的游标
  pname GROUP_BUYING_REG.name%type ;
  pmobile GROUP_BUYING_REG.mobile%type ;
  pemail GROUP_BUYING_REG.email%type ;
  paddress GROUP_BUYING_REG.address%type ;
  pid GROUP_BUYING_REG.id%type ;
  --  上面这些定义了待会用来存储游标赋值的变量
BEGIN
  OPEN group_buying;
  LOOP
    --  开启游标把grouo_buing中的值赋给上面定义好的变量
    FETCH group_buying
    INTO pname,
      pmobile,
      pemail,
      paddress,
      pid;
    UPDATE GROUP_BUYING_REG
      --  set语句里面写的是变化的内容，把解加密后的字段内容重新赋值给原来的字段
    SET name  =pname,
      mobile  =pmobile,
      email   =pemail,
      address =paddress
    WHERE id  =pid;
    EXIT
  WHEN group_buying%notfound;
  END LOOP;
  CLOSE group_buying;
END;

5、调用不带输入输出参数的存储过程

EXECUTE lpdev_encr;
EXECUTE lpdev_decr;

6、一个带有输入输出参数的存储过程

 

  PROCEDURE CHECK_STATUS(
    P_RANDOM_ID IN VARCHAR2,
    P_MOBILE_NO IN VARCHAR2,
    RETURN_CODE OUT NUMBER )
IS
  s_status VARCHAR2(20);
BEGIN
  SELECT STATUS
  INTO s_status
  FROM MYTV_SUPER_OFFER
  WHERE RANDOM_ID = P_RANDOM_ID
  ............
END CHECK_STATUS;

7、调用输入输出参数的存储过程

--先打开输入缓存

SET SERVEROUTPUT ON
DECLARE
  id     VARCHAR2(20);
  phone  VARCHAR2(20);
  result NUMBER;
BEGIN
  id   :=18334189586908172016;
  phone:=93373132;
  MYTV_SUPER_PKG.CHECK_STATUS(id,phone,result);
  DBMS_OUTPUT.PUT_LINE(result);
END;