PL/SQL Using variables in an Explicit Cursor statement
I'm declaring an explicit cursor, however, I need to order the sql differently 开发者_开发百科depending on user interaction. The column to order by is passed to the procedure as a parameter, however, I'm having difficulty incorporating it into the sql for the cursor (as near as I can tell, it is interpreting the variable name as the name of the column and thus ordering by nothing.
Is there a way to use a locally declared variable in an explicit cursor statement?
Here's a basic skeleton of what I'm attempting thus far:
v_order_by varchar2(100) := <function that grabs URL param>
c_cursor is
select...
.
.
.
order by v_order_by;
Any help is greatly appreciated. All the examples of using explicit cursors I've found so far are entirely unhelpful.
If the possible values of v_order_by are static, you can do this:
order by case v_order_by
when 'EMPNO' then empno
when 'DEPTNO then deptno
end
Otherwise you will need to use dynamic SQL and a ref cursor:
declare
v_refcursor sys_refcursor;
...
begin
open v_refcursor for
'select...
order by ' || v_order_by;
If you do that, be sure to learn how to use bind variables rather than literals in your dynamic SQL where clause.
精彩评论