用于重复检查的动态 SQL - Oracle PL/SQL [英] Dynamic SQL for duplicates check - Oracle PL/SQL

问题描述

由于我的权限有限，以下无法创建为程序.

As my permissions are limited, the following can not be created as procedure.

我需要关于开发动态 SQL 的帮助，该 SQL 检查表中是否存在重复的唯一 ID.另外，是否可以使用同一个查询检查多个表的重复项?

I need help on developing a Dynamic SQL that checks a table for duplicate unique IDs. Also, is it possible to have more than one table to be checked for duplicates with the same query?

declare 
  table_name is table:= table_1
  unique_id varchar2(100):= unique_1
begin
  select unique_id,
         count(unique_id) as count_unique
    from table_name
  having count(unique_id)>1
   group by unique_id 
end;
/

推荐答案

如果您不能创建存储过程(或函数)，您将注定进入匿名 PL/SQL 块.这是一个适用于 SQL*Plus(可能也适用于 SQL Developer)的方法.阅读代码中的注释.

If you can't create a stored procedure (or a function), you're doomed to an anonymous PL/SQL block. Here's one that works in SQL*Plus (probably in SQL Developer as well). Read comments within code.

对于 Scott 的 EMP 表，作业数是

For Scott's EMP table, number of jobs is

SQL> select job, count(*) from emp group by job;

JOB         COUNT(*)
--------- ----------
CLERK              4
SALESMAN           4
PRESIDENT          1
MANAGER            3
ANALYST            2

SQL>

然后你会

SQL> declare
  2    l_table  varchar2(30) := '&PAR_TABLE_NAME';
  3    l_column varchar2(30) := '&PAR_COLUMN_NAME';
  4    l_str    varchar2(500);
  5    l_rc     sys_refcursor;
  6    --
  7    l_ret_column varchar2(30);
  8    l_ret_cnt    number;
  9  begin
 10    -- compose a SELECT statement
 11    l_str := 'select '    || l_column || ', count(*) cnt '   ||
 12             ' from '     || l_table  ||
 13             ' group by ' || l_column ||
 14             ' having count(*) > 1';
 15
 16     -- use L_STR as a "source" for the L_RC (ref)cursor
 17     open l_rc for l_str;
 18
 19     -- loop, fetch data, display what you've found
 20     loop
 21       fetch l_rc into l_ret_column, l_ret_cnt;
 22       exit when l_rc%notfound;
 23
 24       dbms_output.put_line(l_table ||'.'|| l_column ||' = ' ||
 25                              l_ret_column ||', ' || l_ret_cnt || ' row(s)');
 26     end loop;
 27
 28     close l_rc;
 29  end;
 30  /
Enter value for par_table_name: emp
Enter value for par_column_name: job
emp.job = CLERK, 4 row(s)
emp.job = SALESMAN, 4 row(s)
emp.job = MANAGER, 3 row(s)
emp.job = ANALYST, 2 row(s)

PL/SQL procedure successfully completed.

SQL>

这篇关于用于重复检查的动态 SQL - Oracle PL/SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！