How to add where condition in a dynamic sql query?-CodePudding

I am trying to write a procedure that uses a dynamic query to fetch records in different tables.Now I want to add a where condition to this clause. The procedure takes an input parameter and I want to use this parameter to compare the value of the same parameter present within the table. The following code doesn't work and I am not sure how to get it to work.

SQL> create or replace procedure p_count(x IN varchar2) as
  2    type   arr is varray(5) of varchar2(30);
  3    tables arr := arr('tb1', 'tb2', 'tb3');
  4    cnt    number;
  5  begin
  6     for i in 1 .. tables.count loop
  7       execute immediate 'select count(*) from ' || tables(i) ||' where tables(i).column_name ='||x into cnt;
  8       insert into tb6 values(tables(i), cnt);
  9     end loop;
 10  end;
 11  /

CodePudding user response：

You can use:

create or replace procedure p_count(x IN varchar2)
as
  type   arr is varray(5) of varchar2(30);
  tables arr := arr('tb1', 'tb2', 'tb3');
  cnt    number;
begin
  for i in 1 .. tables.count loop
    execute immediate 'select count(*)
                       from   ' || DBMS_ASSERT.SIMPLE_SQL_NAME(tables(i)) || '
                       where  column_name = :1'
                 INTO  cnt
                 USING x;
    insert into tb6 values(tables(i), cnt);
  end loop;
end;
/

Which, for the sample data:

CREATE TABLE tb1 (column_name) AS
SELECT 'aaa' FROM DUAL;

CREATE TABLE tb2 (column_name) AS
SELECT 'bbb' FROM DUAL;

CREATE TABLE tb3 (column_name) AS
SELECT 'aaa' FROM DUAL UNION ALL
SELECT 'aaa' FROM DUAL UNION ALL
SELECT 'bbb' FROM DUAL;

CREATE TABLE tb6 (
  table_name VARCHAR2(30),
  cnt        NUMBER(10,0)
);

Then after:

BEGIN
  p_count('aaa');
END;
/

tb6 contains:

TABLE_NAME CNT

tb1 1

tb2 0

tb3 2

TABLE_NAME	CNT
tb1	1
tb2	0
tb3	2

db<>fiddle here

CodePudding user response：

Based on comments you posted, that would be like this:

Sample table (tb1 has only one column: category; yours probably has many more columns; didn't feel like creating other tables so I used exception handling section within the loop which doesn't raise an error and lets you loop until the end).

SQL> SET SERVEROUTPUT ON
SQL>
SQL> SELECT * FROM tb1;

CATEGORY
--------
A
B
B

SQL>

Procedure:

SQL> CREATE OR REPLACE PROCEDURE p_count (x IN VARCHAR2)
  2  AS
  3     TYPE arr IS VARRAY (5) OF VARCHAR2 (30);
  4
  5     tables  arr := arr ('tb1', 'tb2', 'tb3');
  6     cnt     NUMBER;
  7     l_str   VARCHAR2 (500);
  8  BEGIN
  9     FOR i IN 1 .. tables.COUNT
 10     LOOP
 11        BEGIN
 12           l_str :=
 13                 'select count(*) from '
 14              || tables (i)
 15              || ' where category ='
 16              || DBMS_ASSERT.enquote_literal (x);
 17
 18           EXECUTE IMMEDIATE l_str
 19              INTO cnt;
 20
 21           INSERT INTO tb6
 22                VALUES (tables (i), cnt);
 23        EXCEPTION
 24           WHEN OTHERS
 25           THEN
 26              DBMS_OUTPUT.put_line (tables (i) || ': ' || SQLERRM);
 27        END;
 28     END LOOP;
 29  END;
 30  /

Procedure created.

Testing:

SQL> EXEC p_count('A');
tb2: ORA-00942: table or view does not exist
tb3: ORA-00942: table or view does not exist

PL/SQL procedure successfully completed.

SQL>

Result:

SQL> SELECT * FROM tb6;

NAME        CNT
---- ----------
tb1           1

SQL>