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

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;