两种写法实现相同的功能,取第一条匹配的数据,如果没有返回null
在我这里open了以后不能用c_cur%FOUND先判断,这个条件总是不成立,即使有数据,看样子还的详细看oracle游标的说明才行
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游标的说明才行