oracle ：存储过程和函数的几种写法

关于存储过程和函数的定义网上一搜一大把，这里就不特殊介绍了，这里就只对我自己写的几种格式的存储过程和函数做一些总结，希望对大家有点帮助。
 一：存储过程
  
1：最普通的一种。（传参，查询游标，执行，循环游标做插入动作）。
create or replace procedure zy2040_sirole(rolekey in varchar2) is
  roleid varchar2(30);
  cursor cur_zy2040_department_id(rolekeys in varchar2) is
    select distinct d.department_id
      from si a, si_staff b, sec_staff c, sec_department d
     where a.siid = b.siid
       and b.staffid = c.staff_id
       and c.department_id = d.department_id
       and a.provinceid = '0'
       and a.si_status in ('A', 'W')
       and d.department_id not in
           (select a.department_id
              from sec_department_role a, sec_role b
             where a.role_id = b.role_id
               and b.role_key = rolekeys);
  cursor cur_zy2040_staffid(rolekeys in varchar2) is
    select distinct b.staffid, d.department_id
      from si a, si_staff b, sec_staff c, sec_department d
     where a.siid = b.siid
       and b.staffid = c.staff_id
       and c.department_id = d.department_id
       and a.provinceid = '0'
       and a.si_status in ('A', 'W')
       and b.staffid not in (select a.staff_id
         &nb