数据库函数和存储过程例子

create or replace procedure ADD_CYBH(ghbh IN VARCHAR2,num IN INTEGER,flag OUT INTEGER) AS
--增加采样编号界面(CZY目录下AddCybh.mxml) 输入：ghbh过衡编号,num需要产生的采样编号数目；输出flag 1/0
i INTEGER;
ls_lj CHAR(2);
ls_qybh VARCHAR2(11);
ls_fxbh VARCHAR2(11);

w_ghbh CHAR(11);
ls_ghbh CHAR(11);
ls_gmdw VARCHAR2(30);
ls_gmkb VARCHAR2(30);
ls_cs INTEGER;
ls_fz VARCHAR2(30);
ls_lmrq DATE;
ls_ycybh VARCHAR2(11);
BEGIN
  i:=num;
IF i=0 THEN--产生0个编号直接返回1
       BEGIN
          flag:=1;
END;
END IF;
while i>0 LOOP
BEGIN
      i:=i-1;
--ghbh=20080314150
      
--得到采样编号
      ls_qybh:=get_code(ghbh,3);
--bmjmjb编码加密级别，LJ=2或3，得到分析编号
      select LJ into ls_lj from hc_wjljsz where LX='bmjmjb';  
if ls_lj='2' or ls_lj='3' then
BEGIN
IF ls_lj='2' THEN
BEGIN
              ls_fxbh:=ls_qybh;
END;
END IF;
IF ls_lj='3' THEN
BEGIN
              ls_fxbh:=get_code(ghbh,3);
END;
END IF;
END;
end if;
--从HC_GHJLD_T获取插入HC_QYJL时需要的部分数据
      w_ghbh:=ghbh;--必须
      select GHBH into ls_ghbh from HC_GHJLD_T where GHBH=w_ghbh;
select GMDW into ls_gmdw from HC_GHJLD_T where GHBH=w_ghbh;
select GMKB into ls_gmkb from HC_GHJLD_T where GHBH=w_ghbh;
select CS   into ls_cs from HC_GHJLD_T where GHBH=w_ghbh;
select FZ   into ls_fz from HC_GHJLD_T where GHBH=w_ghbh;
select DCRQ into ls_lmrq from HC_GHJLD_T where GHBH=w_ghbh;
select GHBH1 into ls_ycybh  from HC_GHJLD_T where GHBH=w_ghbh;
--插入到HC_QYJL
      insert into HC_QYJL (GHBH,GMDW,GMKB,CS,FZ,LMRQ,YCYBH,QYBH,FXBH,QYRQ) values(ls_ghbh,ls_gmdw,ls_gmkb,ls_cs,ls_fz,ls_lmrq,ls_ycybh,ls_qybh,ls_fxbh,to_date('1990-01-01','YYYY-MM-DD'));--
      if sqlcode=0 then
        flag:=1;
commit;
else
rollback;
        flag:=0;
end if;
END;
end LOOP;
END;

create or replace function get_code(ghbh IN VARCHAR2,jmws

IN integer)
return VARCHAR2
AS
  v_ghbh1 VARCHAR2(11);
  i integer(3);
  k integer(3);
  m VARCHAR2(11);
BEGIN

if jmws = 0  then    --如果加密位数为0 表示不加密
    return ghbh;
end if;

--根据加密的位数生成随机加密码
i:=1;
while i=1 loop

    v_ghbh1 := substr(ghbh,1,11 - jmws);

select dbms_random.string('u',jmws) into m from dual;
  v_ghbh1 := v_ghbh1||m;

--确定生成的编号是否可以使用

    k:=0;
select count(*) into k from hc_ghjld where

ghbh1=v_ghbh1 or ghbh2=v_ghbh1;
if k>=1 then
     i := 1;
else
     i:=0;
end if;

    k:=0;
select count(*) into k from hc_ghjld_t where

ghbh1=v_ghbh1 or ghbh2=v_ghbh1;
if k>=1 then
     i := 1;
else
     i:=0;
end if;

    k:=0;
select count(*) into k from HC_QYCHD where

qybh=v_ghbh1;
if k>=1 then
     i := 1;
else
     i:=0;
end if;
end loop;

return v_ghbh1;
END;

转载于:https://www.cnblogs.com/zjhnl/archive/2012/03/26/2418098.html