[oracle存储过程]将给定数据与已有的数据做比较

一、问题描述

数据表student中的数据（表格创建、数据插入语句在文末附上）

给定数据是以逗号形式分隔的，这里是

'张三,
 李四,
 王五,
 赵六,
 钱七,'

现在需要查出，给定数据里有哪些数据是student表里的sname列中不存在的。（这里数据量小，可以马上看出“赵六”、“钱七”没有）

编写一个存储过程，实现上述功能（要求：能够传1.需要比较的数据 2.待比较的表的表名 3.待比较的表的数据列的列名）

二、代码实现

1.先写一个sql实现这个功能

 

select t.field
  from (select trim(replace(field, chr(10), '')) as field
          from (with A as (select '张三,
   李四,
   王五,
   赵六,
   钱七,' field from dual)
   select regexp_substr(field, '[^,]+', 1, rownum) field
     from A
   connect by rownum <= length(regexp_replace(field, '[^,]+')))) t
 where t.field not in (select sname from student)

2.编写存储过程

只要将相关字段提取成变量即可

 

--查询给定数据中是已有表中对应字段里没有的
create or replace procedure compareData(inputData in varchar2, tableName varchar2, fieldName varchar2) is
  v_sql varchar2(2000);--动态拼接的sql
  result_cur sys_refcursor;--存放动态sql的执行结果
  field_name varchar2(500);--返回要比较的字段结果
begin
  dbms_output.enable(buffer_size=>null);
  v_sql:='select t.field from (select trim(replace(field, chr(10), '''')) as field from'||
   '(with A as (select '''||inputData||''' field from dual)'||
  'select regexp_substr(field, ''[^,]+'', 1, rownum) field from A'||
  ' connect by rownum <= length(regexp_replace(field, ''[^,]+'')))) t'||
  ' where t.field not in (select '||fieldName||' from '||tableName||')';
  open result_cur for v_sql;
  fetch result_cur into field_name;
  loop
    exit when not result_cur%found;
    dbms_output.put_line(field_name);
    fetch result_cur into field_name;
  end loop;
  close result_cur;
end;

（去换行符、去空格replace(chr(10))部分的语句参考http://blog.csdn.net/zqpgood/article/details/6316079）
调用：

 

begin
  compareData('张三,
   李四,
   王五,
   赵六,
   钱七,', 'student', 'sname');
end;

补充：

如果只想测试下存储过程功能，并不想创建存储过程（执行一次就没了）。可以这样写

declare
procedure compareData(inputData in varchar2, tableName varchar2, fieldName varchar2) is
  v_sql varchar2(2000);
  result_cur sys_refcursor;
  field_name varchar2(500);
begin
  dbms_output.enable(buffer_size=>null);
  v_sql:='select t.field from (select trim(replace(field, chr(10), '''')) as field from'||
   '(with A as (select '''||inputData||''' field from dual)'||
  'select regexp_substr(field, ''[^,]+'', 1, rownum) field from A'||
  ' connect by rownum <= length(regexp_replace(field, ''[^,]+'')))) t'||
  ' where t.field not in (select '||fieldName||' from '||tableName||')';
  dbms_output.put_line(v_sql);
  open result_cur for v_sql;--将数据存到游标中
  fetch result_cur into field_name;
  loop
    exit when not result_cur%found;
    dbms_output.put_line(field_name);
    fetch result_cur into field_name;
  end loop;
  close result_cur;
end;
begin
  compareData('张三,
   李四,
   王五,
   赵六,
   钱七,', 'student', 'sname');
end;

附注（student表创建、数据插入语句）：

 

-- Create table
create table STUDENT
(
  sid   NUMBER,
  sname VARCHAR2(40),
  sage  NUMBER
)

-- Add comments to the table 
comment on table STUDENT
  is '学生表';
-- Add comments to the columns 
comment on column STUDENT.sid
  is '学生编号';
comment on column STUDENT.sname
  is '学生姓名';
comment on column STUDENT.sage
  is '学生年龄';

insert into STUDENT (SID, SNAME, SAGE)
values (1, '张三', 10);

insert into STUDENT (SID, SNAME, SAGE)
values (2, '李四', 20);

insert into STUDENT (SID, SNAME, SAGE)
values (3, '王五', 30);

insert into STUDENT (SID, SNAME, SAGE)
values (4, '小红', 5);

insert into STUDENT (SID, SNAME, SAGE)
values (5, '小花', 15);

insert into STUDENT (SID, SNAME, SAGE)
values (6, '小凤', 22);

存储过程的一些技巧参考http://www.cnblogs.com/chinafine/archive/2010/07/12/1776102.html
 

其实还有更简单的解决办法：采用excel可以很快看出A中有哪些数据是B没有的

从上图中可以很快看出，给定表数据中“赵六”、“钱七”是数据库student表中sname列中没有的；

student表sname列的数据中“小红”、“小花”、“小凤”是给定表数据中没有的

操作方法：