如何以*格式显示行数据仅持续3位数字

Question

Oracle是否提供用于数据掩蔽的包或功能？例如，在开发环境中，为了保护数据，需要屏蔽表客户的信息。

create table customer (last_name varchar2(25), first_name varchar2(25), address varchar2(25));
insert into customer values('Doe', 'John', '10 someroad st');

在生产环境中，如果我们对客户进行选择，结果将是毫不意外的：

select *
from customer;

LAST_NAME FIRST_NAME ADDRESS
-------------- -------------- --------------------
Doe John 10 someroad st

在开发环境中，需要隐藏相同的信息，例如：

LAST_NAME FIRST_NAME ADDRESS
-------------- -------------- --------------------
Doe John 10 somerxxx st

Answer 1

我做了一些类似的事情来掩盖数据。我创造了一个视图。您需要调整regexp_replace以满足您的需要。

CREATE TABLE CARDS_TBL (
  CARD_ID NUMBER
    GENERATED BY DEFAULT AS IDENTITY,
  CARD_STR VARCHAR2(16) NOT NULL,
  PRIMARY KEY (CARD_ID)
);

INSERT INTO CARDS_TBL(CARD_STR) VALUES('4024007187788590');
INSERT INTO CARDS_TBL(CARD_STR) VALUES('5432223398564536');
INSERT INTO CARDS_TBL(CARD_STR) VALUES('5430445512530934');
INSERT INTO CARDS_TBL(CARD_STR) VALUES('4020156755227854');
INSERT INTO CARDS_TBL(CARD_STR) VALUES('5431248766892318');


CREATE OR REPLACE VIEW CARDS AS
  SELECT
    CARD_ID,
    REGEXP_REPLACE(CARD_STR, '(^\d{3})(.*)(\d{4}$)', '\1**********\3') AS CARD_STR
  FROM CARDS_TBL;

CREATE OR REPLACE TRIGGER CARDS_TBL_TRG_INSERT
  INSTEAD OF INSERT ON CARDS 
  FOR EACH ROW
BEGIN
  INSERT INTO CARDS_TBL (CARD_STR) VALUES (:NEW.CARD_STR);
END;
/

INSERT INTO CARDS_TBL (CARD_STR) VALUES ('2222333344445555');

SELECT * FROM CARDS_TBL;

UPDATE CARDS_TBL 
  SET CARD_STR = '2222333344445566'
  WHERE CARD_ID = 6;
/

SELECT * FROM CARDS;

CARD_ID    CARD_STR
1    402**********8590
2    543**********4536
3    543**********0934
4    402**********7854
5    543**********2318
6    222**********5566


SELECT * FROM CARDS_TBL;

CARD_ID    CARD_STR
1    4024007187788590
2    5432223398564536
3    5430445512530934
4    4020156755227854
5    5431248766892318
6    2222333344445566