存储过程篇3--参数

本是古典何须时尚 2022-05-19 02:49 187阅读 0赞

前两天改了一个小的无参存储过程，以至于差点忽略掉参数的重要性，今天改了一个大约700行的存储过程，本身有参数传入并计算出返回值，感受到参数的重要性，这次分享下含参存储过程的学习总结；

含参存储过程结构：

create or replace procedure A(B in varchar,C out integer,D out SYS\_REFCURSOR)

as V\_B

begin

end;

这里我定义了存储过程A的三个参数B C D，由之前经验分享得知，B是只能传出，不会传出，C D 是传出参数，不能传入；

此次我遇到的问题是线上传一个字符串，要通过存储过程解析求得值并反馈回字段；

所以存储过程写了一个B的in类型接字符串值，B拿到值后，通过变量的赋值的形式附到变量上去计算，最后同步变量计算赋值到返回类型的C 和D上进行返回值处理，原存储过程如下：

CREATE OR REPLACE PACKAGE BODY COMMON\_QUERY\_3 IS

PROCEDURE GET\_MERCHANT\_ORDER\_LIST(INDATA  IN VARCHAR2  
                                   ,TOTAL   OUT INTEGER  
                                   ,OUTDATA OUT SYS\_REFCURSOR) AS  
    I           INTEGER;  
    V\_MYVALS    MYARRAY; --查询条件  
    V\_ONEVALS   MYARRAY;  
    V\_MYSQL     VARCHAR2(30000); --返回用  
    V\_SQLCOUNT  VARCHAR2(30000); --统计用  
    V\_PAGEINDEX INTEGER DEFAULT 1; --页码  
    V\_PAGESIZE  INTEGER DEFAULT 10; --每页条数  
    V\_CONDITION VARCHAR2(5000); --查询列表集用  
    V\_CONDITION\_SPLIT VARCHAR2(5000); --查询拆分订单用  
    V\_START\_RN  INTEGER; --起始数据rownumber  
    V\_END\_RN    INTEGER; --结束数据rownumber  
    V\_ISEXP     INTEGER; --是否为导出需求  
      
    V\_MERCHANTID INTEGER;  
  BEGINa  
    TOTAL       := 0;  
    V\_MYVALS    := COMMON\_FUNC.SPLITSTRING(INDATA, ',');  
    V\_CONDITION := ' WHERE 1=1   
    ';  
    V\_CONDITION\_SPLIT := ' WHERE 1=1   
    ';  
    
    --FOR 查询分类  
    FOR I IN 1 .. V\_MYVALS.COUNT LOOP  
      V\_ONEVALS := COMMON\_FUNC.SPLITSTRING(V\_MYVALS(I), '=');  
      IF UPPER(V\_ONEVALS(1)) = 'MERCHANTID' AND V\_ONEVALS(2) IS NOT NULL  
      THEN  
        --商户编号  
        V\_MERCHANTID := TO\_NUMBER(V\_ONEVALS(2));  
        V\_CONDITION := V\_CONDITION || ' AND (TCI.BUSINESS\_ID = ' || TO\_NUMBER(V\_ONEVALS(2))   
                                   || ' OR TMI.ID = ' || TO\_NUMBER(V\_ONEVALS(2)) || ') ';  
        V\_CONDITION\_SPLIT := V\_CONDITION\_SPLIT || ' AND (TCI.BUSINESS\_ID = ' || TO\_NUMBER(V\_ONEVALS(2))   
                                   || ' OR TMI.ID = ' || TO\_NUMBER(V\_ONEVALS(2)) || ') ';                     
      ELSIF UPPER(V\_ONEVALS(1)) = 'COMMODITYID' AND V\_ONEVALS(2) IS NOT NULL  
      THEN  
        --商品编号  
        V\_CONDITION := V\_CONDITION || ' AND TCIO.COMMODITY\_ID = ' || TO\_NUMBER(V\_ONEVALS(2));  
        V\_CONDITION\_SPLIT := V\_CONDITION\_SPLIT || ' AND TCSIO.COMMODITY\_ID = ' || TO\_NUMBER(V\_ONEVALS(2));  
      ELSIF UPPER(V\_ONEVALS(1)) = 'SPAY\_TIME' AND V\_ONEVALS(2) IS NOT NULL  
      THEN  
        --订单支付开始时间  
        V\_CONDITION := V\_CONDITION || ' AND TCO.PAY\_TIME >= date''' || V\_ONEVALS(2) || '''';  
        V\_CONDITION\_SPLIT := V\_CONDITION\_SPLIT || ' AND TCSO.PAY\_TIME >= date''' || V\_ONEVALS(2) || '''';  
      ELSIF UPPER(V\_ONEVALS(1)) = 'EPAY\_TIME' AND V\_ONEVALS(2) IS NOT NULL  
      THEN  
        --订单支付结束时间  
        V\_CONDITION := V\_CONDITION || ' AND TCO.PAY\_TIME < date''' || V\_ONEVALS(2) || ''' + 1 ';  
        V\_CONDITION\_SPLIT := V\_CONDITION\_SPLIT || ' AND TCSO.PAY\_TIME < date''' || V\_ONEVALS(2) || ''' + 1 ';  
      ELSIF UPPER(V\_ONEVALS(1)) = 'SEND\_TIME' AND V\_ONEVALS(2) IS NOT NULL  
      THEN  
        --订单签收开始时间  
        V\_CONDITION := V\_CONDITION || ' AND TCIO.END\_TIME >= date''' || V\_ONEVALS(2) || '''';  
        V\_CONDITION\_SPLIT := V\_CONDITION\_SPLIT || ' AND TCIO.END\_TIME >= date''' || V\_ONEVALS(2) || '''';  
      ELSIF UPPER(V\_ONEVALS(1)) = 'EEND\_TIME' AND V\_ONEVALS(2) IS NOT NULL  
      THEN  
        --订单签收结束时间  
        V\_CONDITION := V\_CONDITION || ' AND TCIO.END\_TIME < date''' || V\_ONEVALS(2) || ''' + 1';  
        V\_CONDITION\_SPLIT := V\_CONDITION\_SPLIT || ' AND TCIO.END\_TIME < date''' || V\_ONEVALS(2) || ''' + 1';  
      ELSIF UPPER(V\_ONEVALS(1)) = 'ORDER\_STATUS' AND V\_ONEVALS(2) IS NOT NULL  
      THEN  
        --订单状态  
        V\_CONDITION := V\_CONDITION || ' AND TCO.STATUS = ' || TO\_NUMBER(V\_ONEVALS(2));  
        V\_CONDITION\_SPLIT := V\_CONDITION\_SPLIT || ' AND TCO.STATUS = ' || TO\_NUMBER(V\_ONEVALS(2));  
      ELSIF UPPER(V\_ONEVALS(1)) = 'CARDNO' AND V\_ONEVALS(2) IS NOT NULL  
      THEN  
        --商品卡号  
        V\_CONDITION := V\_CONDITION || ' AND TCD.CARDNO = ''' || V\_ONEVALS(2) || '''';  
        V\_CONDITION\_SPLIT := V\_CONDITION\_SPLIT || ' AND TCD.CARDNO = ''' || V\_ONEVALS(2) || '''';  
      ELSIF UPPER(V\_ONEVALS(1)) = 'ORDER\_ID' AND V\_ONEVALS(2) IS NOT NULL  
      THEN  
        --订单编号  
        V\_CONDITION := V\_CONDITION || ' AND TCO.ORDER\_ID = ' || TO\_NUMBER(V\_ONEVALS(2));  
        V\_CONDITION\_SPLIT := V\_CONDITION\_SPLIT || ' AND TCSO.ORDER\_ID = ' || TO\_NUMBER(V\_ONEVALS(2));  
      ELSIF UPPER(V\_ONEVALS(1)) = 'CUST\_ID' AND V\_ONEVALS(2) IS NOT NULL  
      THEN  
        --客户编号  
        V\_CONDITION := V\_CONDITION || ' AND TCO.CUST\_ID = ''' || V\_ONEVALS(2) || '''';  
        V\_CONDITION\_SPLIT := V\_CONDITION\_SPLIT || ' AND TCO.CUST\_ID = ''' || V\_ONEVALS(2) || '''';  
      ELSIF UPPER(V\_ONEVALS(1)) = 'STATUS' AND V\_ONEVALS(2) IS NOT NULL  
      THEN  
        --发货状态  
        V\_CONDITION := V\_CONDITION || ' AND TCIO.STATUS = ' || TO\_NUMBER(V\_ONEVALS(2));  
        V\_CONDITION\_SPLIT := V\_CONDITION\_SPLIT || ' AND TCIO.STATUS = ' || TO\_NUMBER(V\_ONEVALS(2));  
      ELSIF UPPER(V\_ONEVALS(1)) = 'LEVEL\_ID' AND V\_ONEVALS(2) IS NOT NULL  
      THEN  
        --仓库编号  
        V\_CONDITION := V\_CONDITION || ' AND TCI.LEVEL\_ID = ' || TO\_NUMBER(V\_ONEVALS(2));  
        V\_CONDITION\_SPLIT := V\_CONDITION\_SPLIT || ' AND TCI.LEVEL\_ID = ' || TO\_NUMBER(V\_ONEVALS(2));  
      ELSIF UPPER(V\_ONEVALS(1)) = 'MOBILE\_NO' AND V\_ONEVALS(2) IS NOT NULL  
      THEN  
        --下单手机号  
        V\_CONDITION := V\_CONDITION || ' and tco.username\_tx = ''' || V\_ONEVALS(2) || '''';  
        V\_CONDITION\_SPLIT := V\_CONDITION\_SPLIT || ' and tco.username\_tx = ''' || V\_ONEVALS(2) || '''';  
      ELSIF UPPER(V\_ONEVALS(1)) = 'DELAYED\_STATUS' AND V\_ONEVALS(2) IS NOT NULL  
      THEN  
        --延迟发货  
        V\_CONDITION := V\_CONDITION || ' and tco.DELAYED\_STATUS = ' || V\_ONEVALS(2) ;  
        V\_CONDITION\_SPLIT := V\_CONDITION\_SPLIT || ' and tco.DELAYED\_STATUS = ' || V\_ONEVALS(2) ;  
      ELSIF UPPER(V\_ONEVALS(1)) = 'PAGEINDEX' AND V\_ONEVALS(2) IS NOT NULL  
      THEN  
        --页码  
        V\_PAGEINDEX := TO\_NUMBER(V\_ONEVALS(2));  
      ELSIF UPPER(V\_ONEVALS(1)) = 'PAGESIZE' AND V\_ONEVALS(2) IS NOT NULL  
      THEN  
        --每页条数  
        V\_PAGESIZE := TO\_NUMBER(V\_ONEVALS(2));  
      ELSIF UPPER(V\_ONEVALS(1)) = 'ISEXP' AND V\_ONEVALS(2) IS NOT NULL  
      THEN  
        --是否为导出  
        V\_ISEXP := TO\_NUMBER(V\_ONEVALS(2));  
      ELSIF UPPER(V\_ONEVALS(1)) = 'TIME\_OUT' AND V\_ONEVALS(2) IS NOT NULL  
      THEN  
        --超时发货  
        IF V\_ONEVALS(2) = 1 THEN   
        V\_CONDITION := V\_CONDITION || ' and NVL(TCIO.OUT\_TIME,SYSDATE)-TCO.PAY\_TIME > 2 AND TCI.TYPE\_ID = 1' ;  
        V\_CONDITION\_SPLIT := V\_CONDITION\_SPLIT || ' and NVL(TCIO.OUT\_TIME,SYSDATE)-TCO.PAY\_TIME > 2 AND TCI.TYPE\_ID = 1' ;  
        ELSIF V\_ONEVALS(2) = 0 THEN  
        V\_CONDITION := V\_CONDITION || ' and ((NVL(TCIO.OUT\_TIME,SYSDATE)-TCO.PAY\_TIME <= 2 AND TCI.TYPE\_ID = 1) OR TCI.TYPE\_ID <>1) ' ;  
        V\_CONDITION\_SPLIT := V\_CONDITION\_SPLIT || ' and ((NVL(TCIO.OUT\_TIME,SYSDATE)-TCO.PAY\_TIME <= 2 AND TCI.TYPE\_ID = 1) OR TCI.TYPE\_ID <>1)' ;  
        END IF;  
      END IF;  
    END LOOP;  
    
    V\_START\_RN := (V\_PAGEINDEX - 1) \* V\_PAGESIZE + 1;  
    V\_END\_RN   := (V\_PAGEINDEX) \* V\_PAGESIZE;  
    
    V\_MYSQL := '  
 tableA  
    ' || V\_CONDITION\_SPLIT || ') A )';  
    
    V\_SQLCOUNT := '  
      WITH RESULTTAB AS (  
      SELECT /\*+index(TCO,IDX\_COMMODRDER\_PAYDATE) use\_nl(TCO,TCIO) use\_nl(TCIO,TCI)\*/  
        DISTINCT TCIO.ID 序号  
      FROM T\_COMMODITY\_ORDER TCO  
      LEFT JOIN T\_COMMODITY\_INORDER TCIO ON TCIO.ORDER\_ID = TCO.ORDER\_ID  
      LEFT JOIN T\_COMMODITY\_INFO TCI ON TCIO.COMMODITY\_ID = TCI.COMMODITY\_ID  
      LEFT JOIN T\_COMMODITY\_DUMMY TCD ON TCIO.ID = TCD.BUYID  
      LEFT JOIN T\_MERCHANT\_INFO TMI ON TCO.OPERATOR = TMI.BWAP\_CHANNEL  
      ' || V\_CONDITION || ' AND TCO.IS\_SPLIT = 0  
      UNION ALL  
      SELECT /\*+index(TCO,IDX\_COMMOSPLITDRDER\_PAYDATE) use\_nl(TCO,TCIO) use\_nl(TCIO,TCI)\*/  
        DISTINCT TCSIO.ID 序号  
      FROM T\_COMMODITY\_SPLIT\_ORDER TCSO  
      JOIN T\_COMMODITY\_SPLIT\_INORDER TCSIO ON TCSIO.ORDER\_ID = TCSO.ORDER\_ID  
      JOIN T\_COMMODITY\_ORDER TCO ON TCSO.MAIN\_ORDERID = TCO.ORDER\_ID  
      JOIN T\_COMMODITY\_INORDER TCIO ON TCO.ORDER\_ID = TCIO.ORDER\_ID  
      LEFT JOIN T\_COMMODITY\_INFO TCI ON TCIO.COMMODITY\_ID = TCI.COMMODITY\_ID  
      LEFT JOIN T\_COMMODITY\_SPLIT\_DUMMY TCSD ON TCSIO.ID = TCSD.ORDER\_DETAIL\_ID  
      LEFT JOIN T\_COMMODITY\_DUMMY TCD ON TCSD.ID = TCD.ID  
      LEFT JOIN T\_MERCHANT\_INFO TMI ON TCO.OPERATOR = TMI.BWAP\_CHANNEL  
      ' || V\_CONDITION\_SPLIT || ')  
      SELECT COUNT(\*) FROM RESULTTAB';  
    
    V\_MYSQL := V\_MYSQL || '  
      SELECT   下单时间, 支付时间, 发货时间, 配送物流公司, 运单号,是否超时发货  
      FROM RESULTTAB RT WHERE RN >= :A AND RN <= :B   
      ORDER BY RN';  
    
    
    dbms\_output.put\_line(V\_MYSQL);  
    EXECUTE IMMEDIATE V\_SQLCOUNT  
      INTO TOTAL;  
    --如果为导出则全量  
    IF V\_ISEXP = 1  
    THEN  
      V\_START\_RN := 1;  
      V\_END\_RN   := TOTAL;  
    END IF;  
    
    OPEN OUTDATA FOR(V\_MYSQL)  
      USING V\_START\_RN, V\_END\_RN;  
  END GET\_MERCHANT\_ORDER\_LIST;