PL/SQL动态SQL

Question

我需要帮助。我在构造PL/SQL块时遇到了一个问题。在游标中，我构造了一个查询，并希望在游标上插入一个过滤器。下面是一个示例：

DECLARE 
code NUMBER;
parameters_amb myOthertable%ROWTYPE;

CURSOR test is SELECT id from mytable
 if parameters_amb.test2 is not null  then
    where mytable.name = 'NAMETABLE'
 else
    where mytable.name = 'NAMETABLE2';

有人能协助我完成这项工程吗？

Answer 1

您可以像这样尝试：

DECLARE  
  code NUMBER; 
  parameters_amb myOthertable%ROWTYPE;  
  CURSOR test is SELECT id 
                 from mytable  
                 WHERE (parameters_amb.test2 is not null AND mytable.name = 'NAMETABLE' )
                   OR (parameters_amb.test2 is null AND mytable.name = 'NAMETABLE2' );

或者像这样：

DECLARE  
  code NUMBER; 
  parameters_amb myOthertable%ROWTYPE;  
  CURSOR test is SELECT id 
                 from mytable  
                 WHERE mytable.name = 
                       CASE WHEN parameters_amb.test2 is not null THEN 'NAMETABLE'
                            WHEN parameters_amb.test2 is null THEN 'NAMETABLE2' END

Answer 2

您可以使用参数化的游标：

DECLARE 
code NUMBER;
parameters_amb myOthertable%ROWTYPE;
param mytable.name%TYPE;

CURSOR test (p_name VARCHAR2) is 
  SELECT id 
    FROM mytable
   WHERE mytable.name = p_name;
BEGIN
  if parameters_amb.test2 is not null  then
    param := 'NAMETABLE'
  else
    param := 'NAMETABLE2';
  end if;

  OPEN test(param);

  -- Add code to fetch and read from cursor
END;

Answer 3

DECLARE
 stmt varchar2(1000);
 code NUMBER;
 parameters_amb myOthertable%ROWTYPE;
BEGIN
 stmt := 'SELECT id from mytable';
 if parameters_amb.test2 is not null  
 then
    stmt := stmt||' where mytable.name = ''NAMETABLE''';
 else
    stmt := stmt||' where mytable.name = ''NAMETABLE2''';
 end if;

 OPEN test FOR stmt;
...
...
END;