在过程中使用可变参数选择 [英] select with variable parameter in the procedure

问题描述

我正在尝试编写具有以下功能的过程.即，正在从架构中的表中查找记录.特别是，在这些表中是typepkstring列.同时，我在同一架构上有compositedtypes表，该表具有列pk. pk列包含上述typepkstring列中的所有数字标识符.现在的问题是，在typepkstring中，我们还具有不在tabel compositiontypes的pk列中的键.而且我必须搜索该架构，并将其与它们所在的表的名称一起写出来.

I am trying to write a procedure with the following functionality. Namely, is looking for records from the tables in the schema. In particular, it is a typepkstring column in these tables. At the same time, I have the composedtypes table on the same schema, which has the column pk. The pk column contains all number identifiers from the aforementioned typepkstring column. And now the problem is that in typepkstring we have additionally keys that are not in the column pk in tabel composedtypes. And I have to search the schema and write it out together with the name of the table in which they are located.

此时，我的过程如下:

    create or replace PROCEDURE SIEROT
      (i_table_name VARCHAR2)
      is
    CURSOR c is 
      SELECT DISTINCT i_table_name.TYPEPKSTRING
                        FROM  i_table_name
                        LEFT OUTER JOIN COMPOSEDTYPES
                        ON  i_table_name.TYPEPKSTRING=COMPOSEDTYPES.PK
                        WHERE COMPOSEDTYPES.PK IS NULL; 

   TYPE c_list IS TABLE of PRODUCTS.TYPEPKSTRING%type INDEX BY binary_integer; 
   TYPEPK_list c_list; 
   counter integer :=0; 
BEGIN 
   FOR n IN c LOOP 
      counter := counter +1; 
      TYPEPK_list(counter) := n.TYPEPKSTRING; 
      dbms_output.put_line('TABLE: '||i_table_name||'('||counter||'):'||TYPEPK_list(counter)); 
   END LOOP; 

END;

并致电:

set serveroutput on
DECLARE
    ind integer := 0;
BEGIN
FOR ind IN (select table_name from all_tab_columns where column_name='TYPEPKSTRING' AND table_name!='COMPOSEDTYPES')
  LOOP
    BEGIN
        SIEROT(ind.table_name);
    EXCEPTION 
        WHEN NO_DATA_FOUND THEN
        null;
    END; 
  END LOOP;
END;

这是解决问题的第二种方法，对我来说似乎更容易.第二个也是不起作用的，它是基于使用数组类型的游标的. 我的问题是: 调用当然可以正常工作，但是当我编译相同的过程时，出现以下错误:

this is the second approach to the problem I used, it seemed easier to me. The second one, also not functional, was based on cursors using the array type. My problem is: calling certainly works fine, but when I compile the same procedure I get the following error:

Procedure SIEROT compiled

LINE/COL  ERROR
--------- -------------------------------------------------------------
5/7       PL/SQL: SQL Statement ignored
6/31      PL/SQL: ORA-00942: table or view does not exist
17/7      PL/SQL: Statement ignored
17/31     PLS-00364: loop index variable 'N' use is invalid
Errors: check compiler log

对于永久输入的表名进行相同的选择，我自己在其中放置了2条满足任务条件的记录，它们可以正常工作:

Same select for permanently entered table names, where I have put 2 records that meet the conditions of the task myself, works correctly:

SELECT DISTINCT TESTOWY.TYPEPKSTRING
                        FROM TESTOWY
                        LEFT OUTER JOIN COMPOSEDTYPES
                        ON TESTOWY.TYPEPKSTRING=COMPOSEDTYPES.PK
                        WHERE COMPOSEDTYPES.PK IS NULL; 

，对于在过程中键入的select，它可以达到预期的效果，但是，如果要搜索整个模式的全部，而不仅仅是对特定模式的搜索，我需要对源表的名称进行参数化.上述选择中的只有一个就足以满足一个特定条件:

and for select so typed in the procedure, it gets the intended effect but, I need to parameterize the name of the source table if it wants to search all of the whole schema, not just on specific one. Only one of the abovementioned selects would be sufficient for one particular:

TABLE: TESTOWY(1):8790000000098
TABLE: TESTOWY(2):8790000000124


PL/SQL procedure successfully completed.

我真的没有这个程序的实力.给我写信，说说如何改善它的工作效率，同时也能满足您的意见.感谢您的任何提示或更正；)

I really do not have the strength for this procedure. Write me how to improve it to work but also fulfill your opinion. Thanks for any hints or corrections;)

推荐答案

您不能在查询中直接使用参数的值作为表名-您需要动态构建SELECT语句，然后使用循环以获取数据:

You can't use the value of a parameter as a table name in a query directly - you'll need to build your SELECT statement dynamically and then use a loop to fetch the data:

CREATE OR REPLACE PROCEDURE SIEROT(i_table_name VARCHAR2) IS
  strSelect      VARCHAR2(32767);
  c              SYS_REFCURSOR;
  vTYPEPKSTRING  PRODUCTS.TYPEPKSTRING%TYPE;

  TYPE c_list IS TABLE of PRODUCTS.TYPEPKSTRING%type INDEX BY binary_integer; 
  TYPEPK_list c_list; 
  counter integer := 0; 
BEGIN 
  strSelect := 'SELECT DISTINCT i.TYPEPKSTRING ' ||
               '  FROM ' || i_table_name || ' i ' ||
               '  LEFT OUTER JOIN COMPOSEDTYPES c ' ||
               '    ON i.TYPEPKSTRING = c.PK ' ||
               '  WHERE c.PK IS NULL';

  OPEN c FOR strSelect;

  FETCH c INTO vTYPEPKSTRING;

  WHILE c%FOUND LOOP
    counter := counter + 1; 
    TYPEPK_list(counter) := vTYPEPKSTRING; 
    dbms_output.put_line('TABLE: '||i_table_name||'('||counter||'):'||TYPEPK_list(counter)); 

    FETCH c INTO vTYPEPKSTRING;
  END LOOP; 

  CLOSE c;
EXCEPTION
  WHEN OTHERS THEN
    IF c%ISOPEN THEN
      CLOSE c;
    END IF;
END SIEROT;

好运.

这篇关于在过程中使用可变参数选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！