oracle存储过程

示例1：获取菜单导航信息

　　应用知识点：while循环

create or replace PROCEDURE XT_CD_DHL(
    cdid IN VARCHAR,
    jb   IN INT,
    res OUT VARCHAR)
AS--声明变量
  num INT :=0;
  mc  VARCHAR(50);
  dj  VARCHAR(10);
  url VARCHAR(50);
  ids VARCHAR(50):=cdid;
  cid VARCHAR(50);
BEGIN -- 执行块
  WHILE num<jb
  LOOP
    num:=num+1;
    SELECT cd.mc,
      cd.dj,
      cd.url,
      cd.dj,
      CD.ID
    INTO mc,
      dj,
      url,
      ids,
      cid
    FROM xt_cd cd
    WHERE CD.ID=ids;
    IF dj      ='0' THEN
      num     :=jb;--跳出循环
    END IF;
    IF res IS NOT NULL THEN
      res  :=res||',';
    END IF;
    res:=res||'{"mc":"'||mc||'","url":"'||url||'","dj":"'||dj||'","id":"'||cid||'"}';
  END LOOP;
  res:='['||res||']';
EXCEPTION
WHEN too_many_rows THEN
  DBMS_OUTPUT.PUT_LINE('返回值多于1行');
WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM, 1, 200));
END;

示例2：根据省、市、区获取坐标值

　　应用知识点：if判断

create or replace PROCEDURE YP_WL_ZB(
    sheng IN VARCHAR,
    shi   IN VARCHAR,
    qu    IN VARCHAR,
    res OUT VARCHAR)
AS --声明变量
  LONGITUDE VARCHAR(50);
  LATITUDE  VARCHAR(50);
  PID       VARCHAR(50);
  WHERESTR  VARCHAR(200);
  sqlStr    VARCHAR(200);
BEGIN -- 执行块
  --省
  IF sheng IS NOT NULL THEN
    sqlstr :='SELECT R.LONGITUDE,R.LATITUDE,R.REGIONID FROM REGION r WHERE R.REGIONLEVEL=1 and (R.REGIONNAME LIKE ''%'||sheng||'%'' OR R.SHORTNAME LIKE ''%'||sheng||'%'') and rownum=1';
    DBMS_OUTPUT.PUT_LINE(sqlStr);
    EXECUTE immediate sqlStr INTO LONGITUDE,LATITUDE,PID;
  END IF;
  --市
  IF shi      IS NOT NULL THEN
    IF pid    IS NOT NULL THEN
      wherestr:=' and R.PID='||pid;
    END IF;
    sqlStr:=' SELECT R.LONGITUDE,R.LATITUDE,R.REGIONID FROM REGION r WHERE R.REGIONLEVEL=2 and (R.REGIONNAME LIKE ''%'||shi||'%'' OR R.SHORTNAME LIKE ''%'||shi||'%'')'||wherestr||' and rownum=1';
    DBMS_OUTPUT.PUT_LINE(sqlStr);
    EXECUTE immediate sqlStr INTO LONGITUDE,LATITUDE,PID;
  END IF;
  --区
  IF qu       IS NOT NULL THEN
    IF pid    IS NOT NULL THEN
      wherestr:=' and R.PID='||pid;
    END IF;
    sqlStr:=' SELECT R.LONGITUDE,R.LATITUDE,R.REGIONID FROM REGION r WHERE R.REGIONLEVEL=3 and (R.REGIONNAME LIKE ''%'||qu||'%'' OR R.SHORTNAME LIKE ''%'||qu||'%'')'||wherestr||' and rownum=1';
    DBMS_OUTPUT.PUT_LINE(sqlStr);
    EXECUTE immediate sqlStr INTO LONGITUDE,LATITUDE,PID;
  END IF;
  res:=LONGITUDE||','||LATITUDE;
EXCEPTION
WHEN too_many_rows THEN
  DBMS_OUTPUT.PUT_LINE('返回值多于1行');
WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM, 1, 200));
END;

示例3：数据备份

　　应用知识点：创建游标、循环游标

create or replace PROCEDURE YP_WL_SJDR(
    dateStr IN VARCHAR,
    res OUT VARCHAR)
AS --声明变量
  CURSOR EMP_CUR ISSELECT w.flag  FROM yp_wl w WHERE TO_CHAR(W.CZSJ,'yyyy-MM-dd') LIKE dateStr||'%'GROUP BY w.flag;
  flag VARCHAR(50);
BEGIN -- 执行块
  FOR str IN EMP_CUR
  LOOP
    DBMS_OUTPUT.PUT_LINE(str.flag);    
  END LOOP;
  res:='导入成功';
EXCEPTION
WHEN too_many_rows THEN
  DBMS_OUTPUT.PUT_LINE('返回值多于1行');
WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM, 1, 200));
END;