oracle游标根据参数查询,[求助]根据参数对游标数据进行排序

给你一个例子！

SQL> select deptno, ename from emp order by job;

DEPTNO ENAME

------ ----------

20 SCOTT

20 FORD

10 MILLER

30 JAMES

20 SMITH

20 ADAMS

30 BLAKE

20 JONES

10 CLARK

10 KING

30 TURNER

30 MARTIN

30 WARD

30 ALLEN

14 rows selected

SQL>

SQL> Declare

2    cur sys_refcursor;

3    v_deptno emp.deptno%type;

4    v_ename  emp.ename%type;

5    p_order varchar2(20) := 'job'; --参数值， 可以使用procedure

6  Begin

7      open cur for 'select deptno, ename from emp order by ' || p_order;

8      loop

9          fetch cur into v_deptno, v_ename;

10          exit when cur%notfound;

11          dbms_output.put_line(v_deptno || ', ' || v_ename);

12      End Loop;

13  End;

14  /

20, SCOTT

20, FORD

10, MILLER

30, JAMES

20, SMITH

20, ADAMS

30, BLAKE

20, JONES

10, CLARK

10, KING

30, TURNER

30, MARTIN

30, WARD

30, ALLEN

PL/SQL procedure successfully completed

SQL>