Write a process to dynamically call the queried field and the string value in the where condition, where string

Write a process to dynamically call the queried field and the string value in the where condition, where string

Take the emp table in scott user as an example to compile a stored procedure to query the values of a field in the emp table. However, the fields to be queried are not sure, and the strings passed in the where condition are also not sure.

DECLARE   v_a       VARCHAR2(20)   := 'sal';   v_sql     VARCHAR2(200);   TYPE tab_org_name     IS TABLE OF VARCHAR2(50);   v_org_name_tab        tab_org_name;BEGIN   v_sql := 'SELECT '|| v_a ||' FROM emp';   dbms_output.put_line(v_sql);   EXECUTE IMMEDIATE v_sql BULK COLLECT INTO v_org_name_tab;   FOR i IN 1 .. v_org_name_tab.count LOOP      dbms_output.put_line(v_org_name_tab(i));   END LOOP;END;

If there is a where condition, pay attention to the application of single quotes when the where condition contains strings. The correct code is as follows:

DECLARE   v_a       VARCHAR2(20)   := 'sal';   v_info    VARCHAR2(20)   := '''CLERK''';    v_sql     VARCHAR2(200);   TYPE tab_org_name     IS TABLE OF VARCHAR2(50);   v_org_name_tab        tab_org_name;BEGIN   v_sql := 'SELECT '|| v_a ||' FROM emp WHERE emp.JOB = '||v_info;   dbms_output.put_line(v_sql);   EXECUTE IMMEDIATE v_sql BULK COLLECT INTO v_org_name_tab;   FOR i IN 1 .. v_org_name_tab.count LOOP      dbms_output.put_line(v_org_name_tab(i));   END LOOP;END;

The execution result is as follows:

Zookeeper