代码之家 › 专栏 › 技术社区 › Coding Duchess

PL/SQL和条件FROM子句

conditional plsql oracle

Coding Duchess · 技术社区 · 7 年前

Table2 但是其他的桌子 FROM 条款不同。

例子:

SELECT t2.Column1, t2.Column2, t2.Column3
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID=t2.ID
LEFT JOIN Table3 t3 ON t3.ID=t1.Second_ID
WHERE t1.Third_ID= p_first;

SELECT t2.Column1, t2.Column2, t2.Column3
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID=t2.ID
LEFT JOIN Table4 t4 ON t2.ID=t4.ID
WHERE t2.Second_ID= p_second;

否则,如果:

SELECT t2.Column1, t2.Column2, t2.Column3
FROM Table2 t2
WHERE t2.Second_ID= p_second;

如何将这三个语句与条件 从 WHERE 条款?

2 回复 | 直到 7 年前

TJo 7 年前

您可以创建动态查询,并使用execute immediate进行单行提取,使用cursor进行多行提取。示例代码如下:

    declare
     str varchar2(3200);
     str1 varchar2(10):=1;
     v1 VARCHAR2(20);
     v2 VARCHAR2(20);
     v3 VARCHAR2(20);
     str2 varchar2(1000);

    begin

     str2:='SELECT t2.c1,t2.c2,t2.c3 FROM ';

     if str1=3 then
        str:= 't1,t2,t3 where t1.c1=t2.c1 and t2.c1=t3.c1 and t1.c1=p_first ;' ;
     elsif str1=2 then
        str:=' t1,t2,t4 where t1.c1=t2.c1 and t2.c1=t4.c1 and t2.c1=p_second ;' ;
     else
        str:='t2 where t2.c1=p_second ;';
     end if;

      str2:=str2 || str;

     dbms_output.put_line(str2);
     EXECUTE IMMEDIATE  str2 into v1,v2,v3;

     dbms_output.put_line(v1||','||v2||','||v3);

    end;

Steven Feuerstein 7 年前

CREATE OR REPLACE PACKAGE refcursor_pkg
IS
   -- Use this REF CURSOR to declare cursor variables whose
   -- queries return data from the ALL_OBJECTS table.

   TYPE all_objects_t IS REF CURSOR
      RETURN all_objects%ROWTYPE;

   -- Use this REF CURSOR to declare cursor variables whose
   -- queries return any number of columns.

   TYPE weak_t IS REF CURSOR;

   -- Return rows in ALL_OBJECTS for any objects
   -- in the specified schema
   FUNCTION objects_in_schema_cv (
      schema_in        IN   all_objects.owner%TYPE
    , name_filter_in   IN   VARCHAR2
   )
      RETURN all_objects_t;

   -- Return data from whatever query is passed as an argument.
   FUNCTION data_from_any_query_cv (query_in IN VARCHAR2)
      RETURN weak_t;

   -- Return data from whatever query is passed as an argument.
   -- But this time, use the predefined weak type,
   -- available in Oracle9i Database Release 2 and above.
   FUNCTION data_from_any_query_cv2 (query_in IN VARCHAR2)
      RETURN sys_refcursor;
END refcursor_pkg;
/

CREATE OR REPLACE PACKAGE BODY refcursor_pkg
IS
   /* Static SQL - note different where clause */
   FUNCTION objects_in_schema_cv (
      schema_in        IN   all_objects.owner%TYPE
    , name_filter_in   IN   VARCHAR2
   )
      RETURN all_objects_t
   IS
      l_cursor_variable   all_objects_t;
   BEGIN
      IF name_filter_in IS NULL
      THEN
         OPEN l_cursor_variable FOR
            SELECT *
              FROM all_objects
             WHERE owner = schema_in;
      ELSE
         OPEN l_cursor_variable FOR
            SELECT *
              FROM all_objects
             WHERE owner = schema_in AND object_name LIKE name_filter_in;
      END IF;

      RETURN l_cursor_variable;
   END objects_in_schema_cv;

   FUNCTION data_from_any_query_cv (query_in IN VARCHAR2)
      RETURN weak_t
   IS
      l_cursor_variable   weak_t;
   BEGIN
      OPEN l_cursor_variable FOR query_in;

      RETURN l_cursor_variable;
   END data_from_any_query_cv;

   FUNCTION data_from_any_query_cv2 (query_in IN VARCHAR2)
      RETURN sys_refcursor
   IS
      l_cursor_variable   sys_refcursor;
   BEGIN
      OPEN l_cursor_variable FOR query_in;

      RETURN l_cursor_variable;
   END data_from_any_query_cv2;
END refcursor_pkg;
/

/* Demonstrate strong ref cursor type. */

DECLARE
   l_objects   refcursor_pkg.all_objects_t;
   l_object    all_objects%ROWTYPE;
BEGIN
   l_objects := refcursor_pkg.objects_in_schema_cv (USER, '%EMP%');

   LOOP
      FETCH l_objects
       INTO l_object;

      EXIT WHEN l_objects%NOTFOUND;
      DBMS_OUTPUT.put_line (l_object.object_name);
   END LOOP;

   CLOSE l_objects;
END;
/

/* Demonstrate weak ref cursor type. */

DECLARE
   l_objects   sys_refcursor;
   l_object    all_objects%ROWTYPE;
BEGIN
   l_objects :=
      refcursor_pkg.data_from_any_query_cv2
                ('SELECT * FROM all_objects WHERE object_name LIKE ''%EMP%''');

   LOOP
      FETCH l_objects
       INTO l_object;

      EXIT WHEN l_objects%NOTFOUND;
      DBMS_OUTPUT.put_line (l_object.object_name);
   END LOOP;

   CLOSE l_objects;
END;
/