时间戳

首页 > 解决方案 > 在 Oracle 的存储过程中动态运行查询

oracle - 在 Oracle 的存储过程中动态运行查询

问题描述

选择列匹配的所有数据库表，而不是使用循环将表名传递给下一个查询。如果列名和列值匹配，则返回 true 并且使用存储过程存在 for 循环：

CREATE OR REPLACE PROCEDURE TEST 
(
NAME IN VARCHAR2 ,
ID IN NUMBER,
RE OUT SYS_REFCURSOR 
) AS

BEGIN
OPEN  RE FOR SELECT A.TABLE_NAME FROM
user_tables A JOIN user_tab_columns C
ON C.TABLE_NAME = A.TABLE_NAME
WHERE C.COLUMN_NAME = NAME;

FOR RE IN LOOP

v_Sql := 'SELECT COUNT(*) FROM '|| LOOP.TABLE_NAME || 'WHERE COLUMN_NAME = 
ID';
EXECUTE IMMEDIATE v_Sql
IF v_Sql%ROWCOUNT > 0 THEN
return true; 
EXIT

END LOOP;

END TEST;

为了更了解问题

//Get all the tables of database where campus_id is exist in any table of 
 database
 Campus, Class, Section (3 tables found)
 Apply forloop on the records
 Select count(campus_id) as total from (table name using loop) where campus_id = 1(value 
 pass)
 if(total > 0){

 Exist for loop and return true
 }
 else{
 Again iterate the loop to next value
 }

标签： oraclestored-procedures

解决方案

你描述的没有多大意义。如果有多个表包含您正在检查的列，并且您在找到第一个列后立即退出循环，那么其余的呢？

这是我要做的，看看它是否有帮助。我将创建一个返回table的函数（不是过程）。为此，我将首先创建类型：

SQL> create or replace type t_record as object (tn varchar2(30), cnt number);
  2  /

Type created.

SQL> create or replace type t_table as table of t_record;
  2  /

Type created.

SQL>

功能：

  • 在光标FOR循环中，我正在选择包含该列的表
  • L_STR用于组成SELECT语句
  • DBMS_OUTPUT.PUT_LINE用于首先显示它，以便我可以直观地检查它是否设置正确。
  • 如果是，我正在运行它EXECUTE IMMEDIATE
  • 结果存储到表类型中并返回给调用者
SQL> create or replace function f_colname
  2    (par_column_name  in varchar2,
  3     par_column_value in varchar2
  4    )
  5    return t_table
  6  is
  7    retval t_table := t_table();
  8    l_str  varchar2(200);
  9    l_cnt  number;
 10  begin
 11    for cur_r in (select table_name
 12                  from user_tab_columns
 13                  where column_name = par_column_name
 14                 )
 15    loop
 16      l_str := 'select count(*) from ' || cur_r.table_name ||
 17               ' where ' || par_column_name || ' = ' ||
 18               chr(39) || par_column_value || chr(39);
 19      -- Display l_str first, to make sure that it is OK:
 20      -- dbms_output.put_line(l_str);
 21      execute immediate l_str into l_cnt;
 22      retval.extend;
 23      retval(retval.count) := t_record(cur_r.table_name, l_cnt);
 24    end loop;
 25    return retval;
 26  end;
 27  /

Function created.

测试：

SQL> select * from table (f_colname('DEPTNO', '10'));

TN                                    CNT
------------------------------ ----------
TEST_201812                             1
DEPT                                    1
EMP                                     3

SQL> select * from table (f_colname('ENAME', 'KING'));

TN                                    CNT
------------------------------ ----------
EMP                                     1
BONUS                                   1

SQL>

这对于某些数据类型（例如DATE）将无法正常工作，并且必须在必要时进行调整。

[编辑：编辑问题后]

好吧，那就更简单了。它仍然应该是一个函数（返回一个布尔值，正如你所说的那样 - 如果找到了一些东西 - 你想返回TRUE）。代码与前面的函数非常相似。

SQL> create or replace function f_colname
  2    (par_column_name  in varchar2,
  3     par_column_value in varchar2
  4    )
  5    return boolean
  6  is
  7    l_str  varchar2(200);
  8    l_cnt  number;
  9    retval boolean := false;
 10  begin
 11    for cur_r in (select table_name
 12                  from user_tab_columns
 13                  where column_name = par_column_name
 14                 )
 15    loop
 16      l_str := 'select count(*) from ' || cur_r.table_name ||
 17               ' where ' || par_column_name || ' = ' ||
 18               chr(39) || par_column_value || chr(39);
 19      -- Display l_str first, to make sure that it is OK:
 20      -- dbms_output.put_line(l_str);
 21      execute immediate l_str into l_cnt;
 22      if l_cnt > 0 then
 23         retval := true;
 24         exit;
 25      end if;
 26    end loop;
 27    return retval;
 28  end;
 29  /

Function created.

测试：由于不能在 SQL 层返回布尔值，所以必须使用匿名 PL/SQL 块，如下所示：

SQL> declare
  2    l_ret boolean;
  3  begin
  4    if f_colname('DEPTNO', '15') then
  5       dbms_output.put_line('It exists');
  6    else
  7       dbms_output.put_line('It does not exist');
  8    end if;
  9  end;
 10  /
It does not exist

PL/SQL procedure successfully completed.

SQL>

推荐阅读