StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The alternative to a cut'n'pasted CASE() statement is to shape the query in-flight with dynamic SQL. </p> <p>Here is the simplest demo possible. </p> <pre><code>create or replace function get_emps ( p_dno in emp.deptno%type , p_sort_col in varchar2 := 'EMPNO' , p_asc_desc in varchar2 := 'ASC') return sys_refcursor is stmt varchar2(32767); return_value sys_refcursor; begin stmt := 'select * from emp where deptno = :1'; if p_sort_col is not null then stmt := stmt ||' order by '||p_sort_col||' '||p_asc_desc; end if; open return_value for stmt using p_dno; return return_value; end get_emps; / </code></pre> <p>And this is it in action ...</p> <pre><code>SQL> var rc refcursor SQL> exec :rc := get_emps(20, 'ENAME') PL/SQL procedure successfully completed. SQL> print rc EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 CLARKE CLERK 7902 17-DEC-80 800 20 7902 GASPAROTTO ANALYST 7566 03-DEC-81 3000 20 7876 KULASH CLERK 7788 23-MAY-87 1100 20 7788 RIGBY ANALYST 7566 19-APR-87 3000 20 7566 ROBERTSON MANAGER 7839 02-APR-81 2975 20 SQL> exec :rc := get_emps(20, 'SAL', 'DESC') PL/SQL procedure successfully completed. SQL> print rc EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7788 RIGBY ANALYST 7566 19-APR-87 3000 20 7902 GASPAROTTO ANALYST 7566 03-DEC-81 3000 20 7566 ROBERTSON MANAGER 7839 02-APR-81 2975 20 7876 KULASH CLERK 7788 23-MAY-87 1100 20 7369 CLARKE CLERK 7902 17-DEC-80 800 20 SQL> </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload