oracle 存储过程的几个例子

一：生成部门用户拥有的查看权限信息

create or replace procedure refreshUseridOfCity
as
cursor c_job is select id,dept_code,dept_name from ram_department order by dept_code;--定义游标
c_row c_job%rowtype; --定义一个游标变量c_row ，该类型为游标c_job中的一行数据类型
v_result varchar2(200);
v_deptlevel varchar2(2);--部门级别，1 地市级别；2普通级别
v_levelname varchar2(60);--级别名称
v_prov number;--级别标识 省直单位
v_city number;--级别标识 市级单位
v_levelcode varchar2(20); --地市所属级别编码

begin
delete from tb_infostat_useridofcity;
for c_row in c_job loop
select count(*) into v_prov from dual where c_row.dept_code like ‘3700%‘;
select count(*) into v_city from dual where c_row.dept_code like ‘37%00‘ and c_row.dept_code != ‘370000‘;
if v_prov > 0 then--省直
select wm_concat(id) ids into v_result from
(select b.id,a.real_name,b.dept_code,b.id deptid,b.parent_id
from ram_user a inner join ram_department b on a.dept_id = b.id)t
where t.dept_code like ‘3700%‘
start with dept_code = c_row.dept_code connect by prior deptid = parent_id;
v_deptlevel := case when c_row.dept_code = ‘370000‘ then ‘1‘ else ‘2‘ end;
v_levelcode := c_row.id;
v_levelname := ‘省直属单位‘;
insert into tb_infostat_useridofcity(id,userid,deptname,deptlevel,levelname,levelcode)values(c_row.dept_code,v_result,c_row.dept_name,v_deptlevel,v_levelname,v_levelcode);
dbms_output.put_line( c_row.dept_code || ‘:‘ || v_result || ‘:‘ || c_row.dept_name || ‘:‘ || v_deptlevel || ‘:‘ || v_levelname || ‘:‘ || v_levelcode);
elsif v_city > 0 then --市级
select ids into v_result from
(select wm_concat(id) ids from
(select b.id,a.real_name,b.dept_code,b.id deptid,b.parent_id
from ram_user a inner join ram_department b on a.dept_id = b.id)
start with dept_code = c_row.dept_code connect by prior deptid = parent_id) tt;
v_deptlevel := ‘1‘;
v_levelcode := c_row.id;
v_levelname := c_row.dept_name;
insert into tb_infostat_useridofcity(id,userid,deptname,deptlevel,levelname,levelcode)values(c_row.dept_code,v_result,c_row.dept_name,v_deptlevel,v_levelname,v_levelcode);
dbms_output.put_line( c_row.dept_code || ‘:‘ || v_result || ‘:‘ || c_row.dept_name || ‘:‘ || v_deptlevel || ‘:‘ || v_levelname || ‘:‘ || v_levelcode);
else
select ids into v_result from
(select wm_concat(id) ids from
(select b.id,a.real_name,b.dept_code,b.id deptid,b.parent_id
from ram_user a inner join ram_department b on a.dept_id = b.id)
start with dept_code = c_row.dept_code connect by prior deptid = parent_id) tt;
--过滤掉部门下未创建用户的信息
if v_result is not null then
v_deptlevel := ‘2‘;
v_levelcode := c_row.id;
select b.dept_name into v_levelname from ram_department a inner join ram_department b on a.parent_id = b.id where a.dept_code = c_row.dept_code;
insert into tb_infostat_useridofcity(id,userid,deptname,deptlevel,levelname,levelcode)values(c_row.dept_code,v_result,c_row.dept_name,v_deptlevel,v_levelname,v_levelcode);
dbms_output.put_line( c_row.dept_code || ‘:‘ || v_result || ‘:‘ || c_row.dept_name || ‘:‘ || v_deptlevel || ‘:‘ || v_levelname || ‘:‘ || v_levelcode);
else
dbms_output.put_line(c_row.dept_name);
end if;
end if;
end loop;
commit;
exception
when others then
dbms_output.put_line(sqlerrm);
rollback;
end;