我正在嘗試撰寫一個使用動態查詢來獲取不同表中的記錄的程序。現在我想在這個子句中添加一個 where 條件。該程序需要一個輸入引數，我想使用此引數來比較表中存在的相同引數的值。以下代碼不起作用，我不知道如何讓它作業。
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 /
uj5u.com熱心網友回復：
您可以使用：
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;
/
其中，對于樣本資料：
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)
);
然后：
BEGIN
p_count('aaa');
END;
/
tb6包含：
TABLE_NAME 碳納米管 tb1 1 tb2 0 tb3 2
db<>在這里擺弄
uj5u.com熱心網友回復：
根據您發布的評論，這將是這樣的：
示例表（tb1只有一列：category；您的可能還有更多列；不想創建其他表，所以我在回圈中使用了例外處理部分，它不會引發錯誤并讓您回圈到最后）。
SQL> SET SERVEROUTPUT ON
SQL>
SQL> SELECT * FROM tb1;
CATEGORY
--------
A
B
B
SQL>
程式：
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.
測驗：
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>
結果：
SQL> SELECT * FROM tb6;
NAME CNT
---- ----------
tb1 1
SQL>
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/461922.html
上一篇：具有動態值的Apex主區域標頭