一个简单的PL/SQL的例子

给自己做个备份。
这个简单例子使用了记录集，游标等简单功能，正常返回OK，错误返回NG，同时在DB端控制台输出错误信息。

CREATE OR REPLACE PACKAGE EMPLOYEE_IMPORT_SQL AS
    --リテン結果
    RET_NG CONSTANT VARCHAR2(2) := 'NG';
    RET_OK CONSTANT VARCHAR2(2) := 'OK';

    --社員グループ更新機能
    FUNCTION TH_MAIN RETURN VARCHAR2;
END EMPLOYEE_IMPORT_SQL;

/

CREATE OR REPLACE PACKAGE BODY EMPLOYEE_IMPORT_SQL AS

  TYPE T_EMP_GROUP_MEMBER_REC IS RECORD (
      r_empCode EMP_GROUP_MEMBER.EMPLOYEE_CODE%TYPE,
      r_groupCode EMP_GROUP_MEMBER.EMP_GROUP_CODE%TYPE
  );

  FUNCTION TH_MAIN RETURN VARCHAR2 AS
      CURSOR emp_group_member_cursor IS
          SELECT
              emp_detail.employee_code,
              depart_emp_group.emp_group_code
          FROM
              depart_emp_group,
              emp_detail,
              depart
          WHERE
              emp_detail.depart_code = depart_emp_group.depart_code
          AND
              depart.depart_code = depart_emp_group.depart_code
          ORDER BY
              depart_emp_group.emp_group_code,
              emp_detail.employee_code;

      seq_group_code NUMBER;
      nCount NUMBER;
      emp_group_member_rec T_EMP_GROUP_MEMBER_REC;
      v_errorcode number;
      v_errortext varchar2(200);
 
  BEGIN
      nCount := 1;

      DELETE
          EMP_GROUP_MEMBER
      WHERE
          EMP_GROUP_MEMBER.EMP_GROUP_CODE IN
          (SELECT EMP_GROUP_CODE FROM DEPART_EMP_GROUP);

      OPEN emp_group_member_cursor;
      LOOP
          FETCH emp_group_member_cursor INTO emp_group_member_rec;
          EXIT WHEN emp_group_member_cursor%NOTFOUND;
          SELECT seq_emp_group_member_code.nextval INTO seq_group_code FROM dual;
          INSERT INTO emp_group_member
          VALUES(
              seq_group_code,
              emp_group_member_rec.r_empCode,
              emp_group_member_rec.r_groupCode,
              nCount 
          );
          nCount := nCount + 1;
 END LOOP;
    RETURN RET_OK;

    EXCEPTION
      WHEN OTHERS THEN
      v_errorcode:=sqlcode;
      v_errortext:=substr(sqlerrm,1,200);  
      dbms_output.put_line(v_errorcode);  
      dbms_output.put_line(v_errortext);  
      RETURN RET_NG;
  END TH_MAIN;

END EMPLOYEE_IMPORT_SQL;

/