案例：一个应用表中的一个字段是主键，向表中插入数据时，先把数据放在临时表中（没有主键）然后再插入应用表。

这时候如果临时表中有重复数据，无论是主键字段businessid有重复，还是一整行有重复都会报出违反唯一主键约束错误。

方法：group by  XX having count(*)>1,rowid,distinct,temporary table,procedure

a.重复一个字段

b.重复多个字段

c.重复一整行

创建测试表：

create table cfa (businessid number,customer varchar2(50),branchcode varchar2(10),data_date varchar2(10));
insert into cfa values (1,'Albert','SCB','2011-11-11');
insert into cfa values (2,'Andy','DB','2011-11-12');
insert into cfa values (3,'Allen','HSBC','2011-11-13');

---------------以下为重复数据----------------------------------------------
insert into cfa values (1,'Alex','ICBC','2011-11-14');
insert into cfa values (1,'Albert','CTBK','2011-11-15');
insert into cfa values (1,'Albert','SCB','2011-11-11');

对于a的情况,只有businessid重复

select * from cfa where businessid in  (select businessid from cfa group by businessid having count(businessid)>1);

如果是b的情况，businessid 和name同时存在重复

select * from cfa where (businessid,customer) in (select businessid,customer from cfa group by businessid,customer having count(*)>1);

对于c的情况，重复一整行

参考b的方法：select * from cfa where (businessid,customer,branchcode,data_date) in (select * from cfa group by businessid,customer,branchcode,data_date having count(*)>1);

a情况，删除表中多余的重复记录，重复记录是根据单个字段（businessid）来判断，只留有rowid最小的记录

也可以只保留rowid不是最小记录，需要把代码中的min改为max这里不再赘述。

delete from cfa
where businessid in (select businessid
                        from cfa
                       group by businessid
                      having count(businessid) > 1)
   and rowid not in (select min(rowid)
                       from cfa
                      group by businessid
                     having count(businessid) > 1);

或者，使用下面更简单高效的语句

DELETE FROM cfa t
WHERE t.ROWID >
       (SELECT MIN(X.ROWID) FROM cfa X WHERE X.businessid = t.businessid);

b情况，删除表中多余的重复记录（多个字段），只留有rowid最小的记录

delete from cfa
where (businessid,customer) in (select businessid,customer
                        from cfa
                       group by businessid,customer
                      having count(*) > 1)
   and rowid not in (select min(rowid)
                       from cfa
                      group by businessid,customer
                     having count(*) > 1);

或者，使用下面更简单高效的语句

DELETE FROM cfa t
WHERE t.ROWID > (SELECT MIN(X.ROWID)
                    FROM cfa X
                   WHERE X.businessid = t.businessid
                     and x.customer = t.customer);

c情况，这种情况就比较简单，使用临时表方法

create table cfabak as select distinct * from cfa;

truncate table cfa;--如果是生产最好对该表backup

Insert into cfa select * from cfabak;

commit;

本文链接

Instead of 触发器可以在由一个以上的表组成的视图上进行insert,update,delete,merge.也可以使用instead of 来创建分区表等。

创建instead of 触发器语法：

1 CREATE [OR REPLACE] TRIGGER trigger_name 
2 INTEAD OF operation
3 ON view_name
4 FOR EACH ROW
5 BEGIN
6 ...code goes here...
7 END;

下面开始测试 Instead of insert,instead of update,instead of delete触发器以及嵌套表的instead of 触发器

CREATE TABLE employee (
employee_no   VARCHAR2(8),
last_name     VARCHAR2(25) NOT NULL,
first_name    VARCHAR2(10) NOT NULL,
dept_code     VARCHAR2(3) NOT NULL,
active_flag   VARCHAR2(1) DEFAULT 'Y',
mod_user_id   VARCHAR2(30) DEFAULT USER,
mod_user_date DATE DEFAULT SYSDATE);

CREATE TABLE permission_code (
pcode             VARCHAR2(2),
pcode_description VARCHAR2(40) NOT NULL,
mod_user_id       VARCHAR2(30) DEFAULT USER,
mod_user_date     DATE DEFAULT SYSDATE);

CREATE TABLE user_role (
dept_code     VARCHAR2(3),
pcode         VARCHAR2(2),
access_level  VARCHAR2(1) DEFAULT 'R',
mod_user_id   VARCHAR2(30) DEFAULT USER,
mod_user_date DATE DEFAULT SYSDATE);

CREATE TABLE user_permission (
employee_no   VARCHAR2(8),
pcode         VARCHAR2(2),
access_level  VARCHAR2(1) DEFAULT 'R',
mod_user_id   VARCHAR2(30) DEFAULT USER,
mod_user_date DATE DEFAULT SYSDATE);

CREATE TABLE dept_code (
dept_code VARCHAR2(3),
dept_name VARCHAR2(30));

CREATE TABLE test (
test VARCHAR2(20));

-- employee table
INSERT INTO employee
(employee_no, last_name, first_name, dept_code, active_flag)
VALUES
('5001', 'Mark', 'Townsend', 'LCR', 'Y');

INSERT INTO employee
(employee_no, last_name, first_name, dept_code, active_flag)
VALUES
('3996', 'Dacko', 'Carol', 'ESR', 'Y');

INSERT INTO employee
(employee_no, last_name, first_name, dept_code, active_flag)
VALUES
('6842', 'Morgan', 'Daniel', 'ADM', 'Y');

-- permission_code table data
INSERT INTO permission_code
VALUES ('BO', 'BILLING OPTIONS', USER, SYSDATE);

INSERT INTO permission_code
VALUES ('CL', 'CLASS CODES', USER, SYSDATE);

INSERT INTO permission_code
VALUES ('CR', 'CREWS', USER, SYSDATE);

INSERT INTO permission_code
VALUES ('CT', 'CREW TYPES', USER, SYSDATE);

INSERT INTO permission_code
VALUES ('CU', 'CUSTOMER TYPES', USER, SYSDATE);

INSERT INTO permission_code
VALUES ('DH', 'WORKORDER DASH NUMBERS', USER, SYSDATE);

INSERT INTO dept_code (dept_code, dept_name)
VALUES ('ADM', 'ADMINISTRATION');

INSERT INTO dept_code (dept_code, dept_name)
VALUES ('COO', 'COORDINATOR');

INSERT INTO dept_code (dept_code, dept_name)
VALUES ('ESE', 'ELECTRICAL SERVICE');

INSERT INTO dept_code (dept_code, dept_name)
VALUES ('ESR', 'ELECTRICAL SERVICE REP');

INSERT INTO dept_code (dept_code, dept_name)
VALUES ('ENG', 'ENGINEER');

INSERT INTO dept_code (dept_code, dept_name)
VALUES ('LCR', 'LINE CREW');

INSERT INTO dept_code (dept_code, dept_name)
VALUES ('MCR', 'METER CREW');

INSERT INTO dept_code (dept_code, dept_name)
VALUES ('NWE', 'NETWORK ENGINEER');

INSERT INTO dept_code (dept_code, dept_name)
VALUES ('SKA', 'SKETCH ARTIST');

INSERT INTO user_role
(dept_code, pcode, access_level)
SELECT r.dept_code, p.pcode, 'R'
FROM dept_code r, permission_code p;

INSERT INTO user_permission
(employee_no, pcode, access_level)
SELECT e.employee_no, r.pcode, r.access_level
FROM employee e, user_role r
WHERE e.dept_code = r.dept_code;

COMMIT;

CREATE OR REPLACE VIEW role_permission_view AS
SELECT r.dept_code, r.pcode, p.pcode_description, r.access_level
FROM user_role r, permission_code p
WHERE r.pcode = p.pcode;

-- this will fail
INSERT INTO role_permission_view
(dept_code, pcode, pcode_description, access_level)
VALUES
('DAN', 'DM', 'Morgan', 'W');

-- this will fail too
UPDATE role_permission_view
SET access_level = 'W'
WHERE dept_code = 'SKA';

-- another relational view
CREATE OR REPLACE VIEW employee_permission_view AS
SELECT e.employee_no,
e.first_name || ' ' || e.last_name NAME, e.dept_code,
r.pcode, r.access_level DEFACCLVL, u.access_level,
p.pcode_description
FROM employee e, user_role r, user_permission u, permission_code p
WHERE e.dept_code = r.dept_code
AND e.employee_no = u.employee_no
AND r.pcode = u.pcode
AND r.pcode = p.pcode
ORDER BY 1,3;

-- this will fail too
DELETE FROM employee_permission_view
WHERE dept_code = 'LCR';

CREATE OR REPLACE TRIGGER ioft_insert_role_perm
INSTEAD OF INSERT
ON role_permission_view
FOR EACH ROW
DECLARE
x INTEGER;
BEGIN
  SELECT COUNT(*)
  INTO x
  FROM permission_code
  WHERE pcode = :NEW.pcode;

  IF x = 0 THEN
    INSERT INTO permission_code
    (pcode, pcode_description, mod_user_id, mod_user_date)
    VALUES
    (:NEW.pcode, 'New Code', USER, SYSDATE);
  END IF;

  SELECT COUNT(*)
  INTO x
  FROM dept_code
  WHERE dept_code = :NEW.dept_code;

  IF x = 0 THEN
    INSERT INTO dept_code
    (dept_code, dept_name)
    VALUES
    (:NEW.dept_code, 'New Dept');
  END IF;

  INSERT INTO user_role
  (dept_code, pcode, mod_user_id)
  VALUES
  (:NEW.dept_code, :NEW.pcode, 'Morgan');

  INSERT INTO test
  (test)
  VALUES
  ('Z');
END ioft_insert_role_perm;
/

SELECT *
FROM permission_code
WHERE pcode = 'DM';

SELECT *
FROM dept_code
WHERE dept_code = 'DAN';

SELECT *
FROM user_role
WHERE dept_code = 'DAN';

SELECT * FROM test;

-- insert works
INSERT INTO role_permission_view
(dept_code, pcode, pcode_description, access_level)
VALUES
('DAN', 'DM', 'Morgan', 'W');

-- view results
SELECT *
FROM permission_code
WHERE pcode = 'DM';

SELECT *
FROM dept_code
WHERE dept_code = 'DAN';

SELECT *
FROM user_role
WHERE dept_code = 'DAN';

SELECT * FROM test;

CREATE OR REPLACE TRIGGER ioft_role_perm
INSTEAD OF UPDATE
ON role_permission_view
FOR EACH ROW
BEGIN
  UPDATE user_role
  SET access_level = :NEW.access_level,
  mod_user_id = USER,
  mod_user_date = SYSDATE
  WHERE dept_code = :OLD.dept_code
  AND permission_code = :OLD.permission_code;
END ioft_role_perm;
/

SELECT trigger_name, trigger_type, action_type,
description
FROM user_triggers;

SELECT * FROM employee_permission_view;

UPDATE role_permission_view
SET access_level = 'W'
WHERE dept_code = 'SKA';

SELECT * FROM employee_permission_view;

UPDATE employee_permission
SET access_level = 'Z';

SELECT * FROM employee_permission_view;

SELECT * FROM dept_code;

SELECT * FROM employee;

CREATE OR REPLACE TRIGGER ioft_emp_perm
INSTEAD OF DELETE
ON employee_permission_view
FOR EACH ROW
BEGIN
  DELETE FROM dept_code
  WHERE dept_code = :OLD.dept_code;

  UPDATE employee
  SET dept_code = NULL,
      mod_user_id = USER,
      mod_user_date = SYSDATE
  WHERE dept_code = :OLD.dept_code;

  DELETE FROM test
  WHERE test = 'Z';
END ioft_emp_perm;
/

SELECT * FROM employee_permission_view;

DELETE FROM employee_permission_view
WHERE dept_code = 'LCR';

desc employee

DELETE FROM employee_permission_view
WHERE dept_code = 'LCR';

conn scott/tiger

CREATE OR REPLACE TYPE emp_type AS OBJECT (
empno    NUMBER(4),
ename    VARCHAR2(10),
job 

原SQL语句

select t.mail_no,wm_concat(t.notice_no) as notice_no_all from ardba.arbino t group by t.mail_no

错误信息

解决SQL

select a.mail_no,wm_concat(a.notice_no) || max(a.str) as notice_no_all from 
(select k.mail_no,k.notice_no, 
case when k.all_sum > 4000 then '...' else null end as str from
(select t.mail_no, t.notice_no, 
sum(vsize(t.notice_no || ',')) over(partition by t.mail_no) as all_sum, 
sum(vsize(t.notice_no || ',')) over(partition by t.mail_no order by t.notice_no) as up_sum 
from ardba.arbino t) k
where (k.up_sum <= 3998 and k.all_sum > 4000) 
or k.all_sum <= 4001) a
group by a.mail_no

最终效果图

本文链接