[복사되는블로그 꼬마갱이] 동적쿼리를 이용한 Count 함수 '(외따옴표활용)

CREATE OR REPLACE FUNCTION count_rows_where (
   table_name   IN   VARCHAR2,
   v_field1      IN   VARCHAR2 DEFAULT NULL,
   v_value1      IN   VARCHAR2 DEFAULT NULL,
   v_field2      IN   VARCHAR2 DEFAULT NULL,
   v_value2      IN   VARCHAR2 DEFAULT NULL,
   v_field3      IN   VARCHAR2 DEFAULT NULL,
   v_value3      IN   VARCHAR2 DEFAULT NULL
)
   RETURN NUMBER AUTHID CURRENT_USER
IS
   num_rows   NUMBER;
   stmt       VARCHAR2 (2000);
BEGIN

   IF v_field1 IS NULL OR v_value1 IS NULL   THEN
      stmt := 'select count(*) from ' || table_name ;
   ELSIF v_field2 IS NULL OR v_value2 IS NULL   THEN
      stmt := 'select count(*) from ' || table_name
              || '  where ' ||  v_field1 ||  ' = ' || '''' || v_value1 || ''''; 
   ELSIF v_field3 IS NULL OR v_value3 IS NULL   THEN
      stmt := 'select count(*) from ' || table_name
              || '  where ' ||  v_field1 ||  ' = ' || '''' || v_value1 || '''' 
              || '  and   ' ||  v_field2 ||  ' = ' || '''' || v_value2 || '''';
   ELSE
      stmt := 'select count(*) from ' || table_name
              || '  where ' ||  v_field1 ||  ' = ' || '''' || v_value1 || '''' 
              || '  and   ' ||  v_field2 ||  ' = ' || '''' || v_value2 || ''''
              || '  and   ' ||  v_field3 ||  ' = ' || '''' || v_value3 || '''';
   END IF;

    DBMS_OUTPUT.ENABLE;   
    DBMS_OUTPUT.PUT_LINE('stmt : ' || stmt);   

   EXECUTE IMMEDIATE stmt
                INTO num_rows;

   RETURN num_rows;
END;
/