Oracle小记 PLSQL块 游标 存储过程 函数方法

Oracle小记 PLSQL块 游标 存储过程 方法

1) PLSQL块,包含变量赋值,游标循环,异常块处理

DECLARE

  STR VARCHAR2(500);
  --声明变量STR为VARCHAR2类型, 长度为500
  NUM NUMBER;
  --声明变量NUM为NUMBER类型, 长度 | 精度为默认值
  CURSOR MYCUR IS--声明游标
    SELECT ID, USERNAME FROM S_USER;
  --IS 后面跟上SQL查询, 这里查询ID, USERNAME列

BEGIN

  FOR CUR IN MYCUR LOOP
    NUM := CUR.ID;
    --进行NUM变量赋值操作
    IF NUM = 111 THEN
      --如果用户的ID为111那么, 打印用户名
      STR := CUR.USERNAME;
      --进行STR变量赋值操作
      DBMS_OUTPUT.PUT_LINE(STR);
    END IF;
  END LOOP;

EXCEPTION--异常块处理
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-12321, 'YOUR EXCEPTION');
  
END;

直接拿去用,不用谢;

2) Oracle存储过程,函数方法

下面是procedure示例:

--存储过程PROC_STK003_GENERATESERIALCODE,生成STK003模块流水号
CREATE OR REPLACE PROCEDURE PROC_STK003_GENERATESERIALCODE
(
       P_RECEIPT_DATE            IN VARCHAR2,--入库日期
       P_STORE_HOUSE_NO          IN VARCHAR2,
       SERIALCODE                OUT VARCHAR2
)
IS
RECEIPTDATE                 VARCHAR2(10);
HOUSECODE                   VARCHAR2(100);                --仓库简码
SPLITCHAR                   CHAR(1);                     --连接符
PICKSERIALNUM               NUMBER;                      --流水号
NUMBERLENGTH                NUMBER;                      --流水号长度
FIRSTDAY                    DATE;                        --当月第一天
LASTDAY                     VARCHAR2(10);                --当月最后一天
BEGIN
  SPLITCHAR :='-';
  NUMBERLENGTH :=4;
  --获取仓库简码
  SELECT SH.STORE_HOUSE_CODE INTO HOUSECODE FROM STORE_HOUSE SH WHERE SH.STORE_HOUSE_NO = P_STORE_HOUSE_NO;
  --获取传递参数日期的第一天和最后一天
  SELECT TRUNC(TO_DATE(P_RECEIPT_DATE,'YYYY-MM-DD'),'MM') INTO FIRSTDAY FROM DUAL;
  SELECT TO_CHAR(LAST_DAY(TO_DATE(P_RECEIPT_DATE,'YYYY-MM-DD')),'YYYY-MM-DD') INTO LASTDAY FROM DUAL;
 --查询采购入库单在当前日期下是否存在记录
  SELECT NVL(MAX(SUBSTR(PRH.CUSTOMER_COL_1, -4)), 0) + 1 INTO PICKSERIALNUM FROM PO_RECEIPT_HDR PRH
         WHERE to_char(PRH.RECEIPT_DATE,'yyyy-mm-dd') >= to_char(FIRSTDAY,'yyyy-mm-dd')
         AND to_char(PRH.RECEIPT_DATE,'yyyy-mm-dd') <= LASTDAY
         AND PRH.CUSTOMER_COL_1 IS NOT NULL;
  SELECT TO_CHAR(TO_DATE(P_RECEIPT_DATE,'YYYY-MM-DD'),'YYMMDD') INTO RECEIPTDATE FROM DUAL;
  --4位随机数
  SERIALCODE :=LPAD(PICKSERIALNUM,NUMBERLENGTH,0);
  SERIALCODE :=HOUSECODE || RECEIPTDATE || SPLITCHAR || SERIALCODE;
  DBMS_OUTPUT.PUT_LINE('STRRESULT ==>' || SERIALCODE);
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('调用存储过程发生异常');
  END PROC_STK003_GENERATESERIALCODE;

调用方法很简单:

DECLARE

CODESTR VARCHAR2(500);--过程OUT参数返回值

BEGIN
  -- CALL THE PROCEDURE
  PROC_STK003_GENERATESERIALCODE(P_RECEIPT_DATE   => :P_RECEIPT_DATE,--使用字符串替换:P_RECEIPT_DATE
                                 P_STORE_HOUSE_NO => :P_STORE_HOUSE_NO,--使用字符串替换:P_STORE_HOUSE_NO
                                 SERIALCODE       => CODESTR);
END;

下面是function示例:

--函数依据工程内码获取工程号
CREATE OR REPLACE FUNCTION GETPROJIDS(PROJNOS IN VARCHAR2) RETURN VARCHAR2 IS
  FUNCTIONRESULT VARCHAR2(4000);
BEGIN
  SELECT TO_CHAR(WM_CONCAT(PROJ.PROJ_ID)) INTO FUNCTIONRESULT
  FROM PROJ
 WHERE PROJ_NO IN
       (SELECT TO_NUMBER(TRIM(COLUMN_VALUE))
          FROM TABLE (SELECT SPLIT(PROJNOS) FROM DUAL));--这里SPLIT也是一个函数,并且使用Table强制转化为表对象
  RETURN(FUNCTIONRESULT);
END GETPROJIDS;

调用方法:

SELECT GETPROJIDS('2,4') FROM DUAL;

返回结果:XY023,XY024

最后补充说明一下:当使用DBMS_OUTPUT.PUT_LINE的时候,可能会报超出缓冲区的异常,这个时候,需要切换到output标签,放大Buffer Size就可以了,前提是使用SQLPLUS三方工具;

给出示例图:

希望对大家有帮助!