Oracle 存储过程返回结果集小例(不积跬步,无以至千里)

原创于 2015-09-23 14:55:25 发布 · 598 阅读

0 ·

CC 4.0 BY-SA版权

Oracle 专栏收录该内容

51 篇文章

订阅专栏

本博客介绍如何使用PL/SQL程序GETPsn_1st来查询指定表中满足条件的第一级审批人信息，包括审批人ID、序列号和角色。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

PACKAGE F25FORM1_PKG AS 

  TYPE type_refcursor IS REF CURSOR;


  PROCEDURE GETPsn_1st(
    P_FORMKIND in VARCHAR2,
    P_FORMNO in NUMBER,
    O_RESULT out type_refcursor
  );  

END F25FORM1_PKG;



PACKAGE BODY F25FORM1_PKG AS

  PROCEDURE GETPsn_1st(
    P_FORMKIND in VARCHAR2,
    P_FORMNO in NUMBER,
    O_RESULT out type_refcursor
  ) AS
  BEGIN
    OPEN O_RESULT FOR
    SELECT P.FLOWER_EMP_ID AS APPROVER_ID,'3' AS SEQUENCE_NO, '1st Line Manager' AS APPROVER_ROLE
    FROM EHR.ORGMULRECORDS@EHR_LINK K 
    INNER JOIN EHR.ORGStdStruct@EHR_LINK O ON K.UNITID = O.UNITID
    INNER JOIN EHR.PSNACCOUNT@EHR_LINK P ON P.PERSONID = K.PERSONID
    WHERE K.EMPTYPE = '56_5' AND O.MANAGEUNIT = '259f1fd7-7103-41ff-9498-17395b75189f'
    AND O.UNITCODE IN (SELECT SUBSTR(UNIT_CODE,1,4) AS UNIT_CODE FROM F25FORM1 WHERE FORM_NO = P_FORMNO);
  END;

END F25FORM1_PKG;