oracle创建包？和存储过程

答案：--创建包头

CREATE OR REPLACE PACKAGE ELS.pkg_proxy
AS
   TYPE cursortype IS REF CURSOR;

   -- 得到交接确认代理人数据
   PROCEDURE get_proxy_info (
      v_emp_no          IN       VARCHAR2,                     --当前登录用户
      v_to_emp_no       IN       VARCHAR2,                       --代理人工号
      v_to_emp_name     IN       VARCHAR2,                       --代理人姓名
      v_proxy_date1     IN       VARCHAR2,         --代理开始时间: yyyy/mm/dd
      v_proxy_date2     IN       VARCHAR2,         --代理结束时间: yyyy/mm/dd
      v_from_emp_no     IN       VARCHAR2,                     --被代理人工号
      v_from_emp_name   IN       VARCHAR2,                     --被代理人姓名
      p_cursor          OUT      cursortype
   );
END pkg_proxy;

--创建包头
CREATE OR REPLACE PACKAGE BODY ELS.pkg_proxy
AS
   -- 得到交接确认代理人数据
   PROCEDURE get_proxy_info (
      v_emp_no          IN       VARCHAR2,                     --当前登录用户
      v_to_emp_no       IN       VARCHAR2,                       --代理人工号
      v_to_emp_name     IN       VARCHAR2,                       --代理人姓名
      v_proxy_date1     IN       VARCHAR2,         --代理开始时间: yyyy/mm/dd
      v_proxy_date2     IN       VARCHAR2,         --代理结束时间: yyyy/mm/dd
      v_from_emp_no     IN       VARCHAR2,                     --被代理人工号
      v_from_emp_name   IN       VARCHAR2,                     --被代理人姓名
      p_cursor          OUT      cursortype
   )
   AS
   BEGIN
      OPEN p_cursor FOR
         WITH v AS
              (
               --查找当前登录人是否为管理员
               SELECT *
                 FROM employee
                WHERE dept_id IN (
                         SELECT     ID
                               FROM department
                         START WITH ID =
                                       (SELECT dept_id
                                          FROM employee
                                         WHERE emp_no = v_emp_no
                                           AND is_admin IN (
                                                   SELECT r_value
                                                     FROM role_conditions
                                                    WHERE r_key =
                                                                 'ProxySearch')
                                        UNION
                                        SELECT dept_id
                                          FROM employee_add
                                         WHERE emp_no = v_emp_no
                                           AND is_admin IN (
                                                   SELECT r_value
                                                     FROM role_conditions
                                                    WHERE r_key =
                                                                 'ProxySearch'))
                         CONNECT BY PRIOR ID = parent_id)
               UNION
               SELECT *
                 FROM employee
                WHERE emp_no = v_emp_no)
         SELECT DISTINCT *
                    FROM (SELECT a.currentuser, a.toagrentuser,
                                 DECODE (a.agent_type,
                                         'A', '当前交接确认代理',
                                         'B', '将来离职签核代理',
                                         'C', '当前离职签核代理',
                                         'D', '将来交接确认代理',
                                         ''
                                        ) agent_type,
                                 a.from_date, a.TO_DATE,
                                 DECODE
                                    (a.to_date1,
                                     '', '无',
                                     TO_CHAR (a.TO_DATE,
                                              'yyyy/mm/dd am hh:mi:ss'
                                             )
                                    ) to_date1,
                                 a.flowinfo, b.emp_cname AS currentusername,
                                 b1.emp_cname AS toagrentusername, workitem
                            FROM (
                                  --当前交接确认代理
                                  SELECT SUBSTR
                                            (x.currentuser,
                                             1,
                                             INSTR (x.currentuser, '(') - 1
                                            ) AS currentuser,
                                         SUBSTR
                                            (x.toagrentuser,
                                             1,
                                             INSTR (x.toagrentuser, '(') - 1
                                            ) AS toagrentuser,
                                         'A' AS agent_type,
                                         x.create_date AS from_date,
                                         x.create_date AS TO_DATE,
                                         NULL AS to_date1, x.workid,
                                         NULL AS formid, y.NAME workitem,
                                         (SELECT    e.emp_no
                                                 || '('
                                                 || e.emp_cname
                                                 || ')'
                                            FROM employee e
                                           WHERE e.emp_no = y.emp_no)
                                                                  AS flowinfo
                                    FROM currentworkagrent x,
                                         worktransferitem_m y
                                   WHERE x.workid = y.ID
                                  UNION ALL
                                  --将来交接确认代理
                                  SELECT SUBSTR
                                            (currentuser,
                                             1,
                                             INSTR (currentuser, '(') - 1
                                            ) AS currentuser,
                                         SUBSTR
                                               (toagrent,
                                                1,
                                                INSTR (toagrent, '(') - 1
                                               ) AS toagrent,
                                         'D' AS agent_type, starttime,
                                         endtime, endtime AS to_date1, NULL,
                                         NULL, NULL, NULL
                                    FROM futureagrent) a,
                                 employee b,
                                 employee b1
                           WHERE a.currentuser = b.emp_no(+)
                                 AND a.toagrentuser = b1.emp_no(+)) a
                   WHERE currentuser IN (SELECT emp_no
                                           FROM v)
                     AND (currentuser = v_from_emp_no OR v_from_emp_no IS NULL
                         )
                     AND (   currentusername = v_from_emp_name
                          OR v_from_emp_name IS NULL
                         )
                     AND (toagrentuser = v_to_emp_no OR v_to_emp_no IS NULL)
                     AND (   toagrentusername = v_to_emp_name
                          OR v_to_emp_name IS NULL
                         )
                     AND from_date <=
                            TO_DATE (NVL (v_proxy_date2, '2099/12/31'),
                                     'yyyy/mm/dd'
                                    )
                     AND TO_DATE >=
                            TO_DATE (NVL (v_proxy_date1, '1000/12/31'),
                                     'yyyy/mm/dd'
                                    )
                ORDER BY a.agent_type, a.from_date DESC;
   E

上一个：Oracle 输入sql命令后 返回值全都是2 怎么回事
下一个：怎么导入oracle的dmp文件