Oracle动态游标2种写法

伏2650

两种写法实现相同的功能，取第一条匹配的数据，如果没有返回null

create or replace function origin_to_new (v_code varchar2, v_type varchar2) return varchar2 is   
v_tmp rsb_code_map.new_code%TYPE;
type curtype is ref cursor;
c_cur curtype;
ssql varchar2(3000);
begin  
ssql := 'select new_code from rsb_code_map where origin_code=' || '''' || v_code || '''' || ' and code_type=' || '''' || v_type || '''' ;
open c_cur for ssql;
--dbms_output.put_line(ssql);
FETCH c_cur INTO v_tmp;
CLOSE c_cur;  
return v_tmp;
end;



create or replace function origin_to_new (v_code varchar2, v_type varchar2) return varchar2 is
v_tmp rsb_code_map.new_code%TYPE;
CURSOR c_cur(v_c VARCHAR2 DEFAULT '',v_t varchar2 default '') IS  SELECT new_code FROM rsb_code_map WHERE origin_code = v_c and code_type=v_t ;
begin
open c_cur (v_code,v_type);
FETCH c_cur INTO v_tmp;
CLOSE c_cur;  
return v_tmp;
end;


在我这里open了以后不能用c_cur%FOUND先判断，这个条件总是不成立，即使有数据，看样子还的详细看oracle游标的说明才行