sp_user_no(參數數的oracle_sp)及fn_test(有返回值的oracle參數)

CREATE PROCEDURE sp_user_no
AS
  intWORK_CNT  NUMBER;

BEGIN
   intWORK_CNT:=0;
  DECLARE CURSOR Tbfilecode_cur IS
         SELECT user_id FROM tbcommuser where length(user_id)<=8  ORDER BY user_id;
         percount NUMBER DEFAULT 1;
   BEGIN
        FOR singleTbfilecode IN Tbfilecode_cur
        LOOP

    update tbcommuser set user_no='no'||singleTbfilecode.user_id ;
        END LOOP;
   END;
   COMMIT;
EXCEPTION                              -- cursor 例外處理
   WHEN OTHERS THEN
        ROLLBACK;
     RAISE;
END sp_user_no;
//下面是function 的
CREATE FUNCTION fn_tbsendorg_out_name(strCNO_CODE VARCHAR2) RETURN VARCHAR2 IS
ORGAN_NAME VARCHAR2(200);

ORGAN_NAME_TEMP VARCHAR2(200);
intI integer;
BEGIN

intI := 0;
DECLARE CURSOR ODM0A_cur IS
   SELECT RTRIM(OUT_NAME) ORGAN_NAME_TEMP FROM tbsendorg WHERE CNO_CODE=strCNO_CODE order by NAME_ODR;
   BEGIN
          FOR singleODM0A IN ODM0A_cur
          LOOP
         --IF singleODM0A.ORGAN_NAME<>'' THEN
       IF ORGAN_NAME IS NOT NULL THEN
                  ORGAN_NAME := ORGAN_NAME||' 、 '||singleODM0A.ORGAN_NAME_TEMP;
    ELSE
      ORGAN_NAME := singleODM0A.ORGAN_NAME_TEMP;
    END IF;

      intI := intI + 1;
      IF intI >= 3 THEN
        EXIT;
      END IF;
          END LOOP;
    IF intI > 0 THEN
      ORGAN_NAME := ORGAN_NAME||'等';
    END IF;
   END;

        RETURN ORGAN_NAME;
END;