Oracle_游标示例

 

Oracle_游标示例

 

--函数功能：对单个或组合的代码值进行翻译

CREATE OR REPLACE FUNCTION func_translate(I_XTLB VARCHAR2, I_DMLB VARCHAR2, I_DMZ VARCHAR2)

  RETURN VARCHAR2 DETERMINISTIC AS

  O_MESSAGE VARCHAR2(60) DEFAULT NULL;

 

CURSOR   V_SS IS

select * from codetable where instr(I_DMZ,dmz)>0;

C1  V_SS%ROWTYPE ;

 

BEGIN

open V_SS;

  loop

    FETCH V_SS

      INTO C1;

    Exit when(V_SS%NOTFOUND);

       O_MESSAGE:=O_MESSAGE||C1.DMSM1||'、';

  END LOOP ;

CLOSE V_SS;

return  rtrim(O_MESSAGE,'、') ;

EXCEPTION

  WHEN OTHERS THEN

    RETURN '0' || sqlerrm;

END func_get_MESSAGE;