oracle 存储过程复习

CREATE OR REPLACE PROCEDURE "GETCODE" (EN in varchar2,EC OUT INTEGER) is
  t_count integer;
FID INTEGER;
a integer;
begin
  select count(*) into t_count from APP_GETCODE where FENTITYNAME=EN;
  if t_count>0 then
    begin

      Select FENTITYCODE into EC From APP_GETCODE Where FENTITYNAME=EN;
      Update APP_GETCODE Set FENTITYCODE = FENTITYCODE+1 Where FENTITYNAME=EN;

    end;
  else
    begin
      EC :=1;
        select count(*) into FID from app_getcode;
        if FID=0  then
              begin
              Insert into APP_GETCODE(FPRIMARYKEY,FENTITYNAME,FENTITYCODE) Values(1,EN,2);
              end;
        else
              begin
              select max(FPRIMARYKEY) INTO a FROM APP_GETCODE;
              Insert into APP_GETCODE(FPRIMARYKEY,FENTITYNAME,FENTITYCODE) Values(a+1,EN,2);
              end;
        end if;

    end;
  end if;

  commit;

  exception
    when others then
      rollback;
end GETCODE;

--初始化时删除表数据
create or replace procedure InitTable(tablename in varchar2) is
row_count integer;
tname varchar2(50);
strsql varchar2(250);
begin
tname:=tablename;
strsql := 'select count(*) from ' || tname;
execute immediate strsql into row_count;
if(row_count>0) then
 strsql := 'delete from ' || tname;
 execute immediate strsql;
end if;
end InitTable;

begin
inittable('SYSPERSON');
end;
--输出select count(*) from SYSPERSON
-- 00

--初始化系统管理员信息
create or replace procedure InitAdmin
is
tempid number;
did number;--部门当前id
pid number;--人员当前id
rid number;--角色当前id
urid number;--用户角色关联id
mid number;--模块当前id
rmid number;--角色模块关联id
begin
--清空相关表
inittable('rolelegalpower');
inittable('sysmodules');
inittable('app_userandrole');
inittable('sysrole');
inittable('sysperson');
inittable('app_department');
--获取自增主键
getcode('APP_DEPARTMENT',did);
getcode('SYSPERSON',pid);
getcode('APP_USERANDROLE',urid);
getcode('sysrole',rid);
getcode('sysmodules',mid);
getcode('rolelegalpower',rmid);
--插入系统管理员角色
insert into sysrole(fid,fname,fremark,ftype) values(rid,'系统管理员','','其他');
--插入系统模块和系统管理员权限
insert into sysmodules(fid,fname,parent,sequ,descp,moduletype,assemblyname,classname,range,param) values(mid,'系统维护',0,0,'',0,'','','','');
insert into rolelegalpower(fid,froleid,fmoduleid) values(rmid,rid,mid);
tempid:=mid;
getcode('sysmodules',mid);
getcode('rolelegalpower',rmid);
insert into sysmodules(fid,fname,parent,sequ,descp,moduletype,assemblyname,classname,range,param) values(mid,'组织管理',tempid,0,'',3,'Com.Jointown.BasisSet.dll','Com.Jointown.BasisSet.Win.DepartmentForm','','');
insert into rolelegalpower(fid,froleid,fmoduleid) values(rmid,rid,mid);
getcode('sysmodules',mid);
getcode('rolelegalpower',rmid);
insert into sysmodules(fid,fname,parent,sequ,descp,moduletype,assemblyname,classname,range,param) values(mid,'用户管理',tempid,0,'',3,'Com.Jointown.MainGui.exe','Com.Jointown.MainGui.Modules.CzyManageForm','公司','');
insert into rolelegalpower(fid,froleid,fmoduleid) values(rmid,rid,mid);
getcode('sysmodules',mid);
getcode('rolelegalpower',rmid);
insert into sysmodules(fid,fname,parent,sequ,descp,moduletype,assemblyname,classname,range,param) values(mid,'角色管理',tempid,0,'',3,'Com.Jointown.MainGui.exe','Com.Jointown.MainGui.Modules.RoleForm','','');
insert into rolelegalpower(fid,froleid,fmoduleid) values(rmid,rid,mid);
getcode('sysmodules',mid);
getcode('rolelegalpower',rmid);
insert into sysmodules(fid,fname,parent,sequ,descp,moduletype,assemblyname,classname,range,param) values(mid,'计算机MAC信息管理',tempid,0,'',3,'Com.Jointown.MainGui.exe','Com.Jointown.MainGui.Modules.NetAddress','部门','');
insert into rolelegalpower(fid,froleid,fmoduleid) values(rmid,rid,mid);
--插入组织信息
insert into app_department(fpkid,ffkid,departmentname,departmentcode,zjm,state,parentdepartment,parentdepartmentcode,type,departdescription,operator,operatortime,departmenttype,searchcode,indexs)
values(did,0,'九州通医药集团股份有限公司','DBM00000031','JZTJT','启用','九州通集团','0','公司','九州通医药集团股份有限公司','admin',sysdate,'非业务','01.01','');
tempid := did;
getcode('APP_DEPARTMENT',did);
insert into app_department(fpkid,ffkid,departmentname,departmentcode,zjm,state,parentdepartment,parentdepartmentcode,type,departdescription,operator,operatortime,departmenttype,searchcode,indexs)
values(did,0,'湖北九州通达科技开发有限公司','DBM00001034','kjgs','启用','',tempid,'公司','','admin',sysdate,'非业务','01.01','');
tempid := did;
getcode('APP_DEPARTMENT',did);
insert into app_department(fpkid,ffkid,departmentname,departmentcode,zjm,state,parentdepartment,parentdepartmentcode,type,departdescription,operator,operatortime,departmenttype,searchcode,indexs)
values(did,0,'供应链事业部','DBM00001036','gylsyb','启用','湖北九州通达科技开发公司',tempid,'部门','','admin',sysdate,'非业务','01.01','');
--插入人员用户信息
insert into sysperson(fid,fname,floginname,forganizeid,fpassword,fdesc,fisactive,post,personcode,departmentcode,companycode,zjm,state,headdepassess)
values(pid,'系统管理员','admin',did,'111111','',1,'研发工程师','ZIY00022668','DBM00001036','DBM00001034','xtgly',0,'');
--插入用户角色关联信息
insert into app_userandrole(userid,roleid,fid)
values(pid,rid,urid);
commit;
  exception
    when others then
      rollback;
end InitAdmin;

 

 

select * from sysrole
select * from sysperson
select * from app_department
select * from app_userandrole
select * from sysmodules
select * from rolelegalpower