oracle包

oracle包

--//规范
--//注意 : 如果有游标则必须有返回类型
CREATE OR REPLACE PACKAGE myPkg
AS
PROCEDURE findAvgScore(p_stuName VARCHAR2,p_AvgScore OUT NUMBER);
FUNCTION hello(p_name VARCHAR2)RETURN VARCHAR2;
CURSOR stuInfoCur RETURN stuInfo%ROWTYPE;
END;

--//主体
--//注意 : 游标的实现放在开始部分
CREATE OR REPLACE PACKAGE BODY myPkg
AS
  --//实现游标
  CURSOR stuInfoCur RETURN stuInfo%ROWTYPE IS SELECT * FROM stuInfo;
  --//实现PROCEDURE
  PROCEDURE findAvgScore
   (p_stuName VARCHAR2,p_AvgScore OUT NUMBER)
  AS
  BEGIN
    SELECT AVG(b.score) INTO p_AvgScore FROM stuInfo a, stuScore b
      WHERE a.stuId = b.stuID
      AND a.stuName = p_stuName;
  EXCEPTION
    WHEN no_data_found THEN
      DBMS_OUTPUT.PUT_LINE('no_data_found');
      p_AvgScore := 0;
    WHEN others THEN
      DBMS_OUTPUT.PUT_LINE('others');
      p_AvgScore := 0;
  END;
  --//实现FUNCTION
  FUNCTION hello
  (p_name VARCHAR2)
  RETURN VARCHAR2
  AS
  BEGIN
   RETURN p_name || '你好!';
  END;
END;


SET SERVEROUTPUT ON;
--//调用包中的FUNCTION
SELECT myPkg.hello('ACCP') FROM dual;
--//调用包中的PROCEDURE
DECLARE
  num NUMBER;
BEGIN
  myPkg.findAvgScore('AAA',num);
  DBMS_OUTPUT.PUT_LINE('平均成绩 ' || num);
END;
--//调用包中的CURSOR
BEGIN
  FOR stuRec IN myPkg.stuInfoCur
  LOOP
    DBMS_OUTPUT.PUT_LINE('姓名 : '||stuRec.stuName);
    DBMS_OUTPUT.PUT_LINE('年龄 : '||stuRec.stuAge);
    DBMS_OUTPUT.PUT_LINE('=========================');
  END LOOP;
END;


--//查看当前用户过程
 select object_name FROM user_objects WHERE object_type = 'PROCEDURE';
--//查看当前用户的过程代码,名称需要大写
 select text from user_source where name='HELLO';
 
--//业务处理例子。包里边返回游标
--//规范
CREATE OR REPLACE  PACKAGE RET_CURSOR
AUTHID CURRENT_USER
AS 
TYPE RefCursor IS REF CURSOR;
FUNCTION GET_LBTRACK
 (
  fromDate IN varchar2,
  endDate IN varchar2,
  cusType IN varchar2,
  cusName IN varchar2,
  transType IN varchar2,
  toCity IN varchar2,
  deptPurview IN varchar2,
  carrierPurview IN varchar2,
  deptNo IN varchar2
 )
 RETURN RefCursor;
FUNCTION GET_LBASSURANCE
 (
  fromDate IN varchar2,
  endDate IN varchar2,
  cusType IN varchar2,
  cusName IN varchar2,
  transType IN varchar2,
  printBillNo IN varchar2,
  deptPurview IN varchar2,
  carrierPurview IN varchar2
 )
 RETURN RefCursor;
end;

--//主体
CREATE OR REPLACE  PACKAGE BODY RET_CURSOR as 
FUNCTION GET_LBTRACK
  (
  fromDate IN varchar2,
  endDate IN varchar2,
  cusType IN varchar2,
  cusName IN varchar2,
  transType IN varchar2,
  toCity IN varchar2,
  deptPurview IN varchar2,
  carrierPurview IN varchar2,
  deptNo IN varchar2
  )
RETURN RefCursor
AS
  C_LBNO RefCursor;
  C_OUTBILLNO RefCursor;
  C_OUTBILLINFO RefCursor;
  C_LBDETAIL RefCursor;
  C_LBTRACK RefCursor;
  R_LBNO OT_LBHEAD%ROWTYPE;
  fDate date;
  tDate date;
  sqlSTR varchar2(1000) := '';
  sqlCreate varchar2(1000) := '';
  isEXIST integer;
 
  numItem   number(10,0);
  numWeight number(10,0);
  numVolume number(10,0);
 
  numIncome number(10,0);
  charAgentTel  varchar2(50) := '';
  charAgentName  varchar2(100) := '';
  charCarrier  varchar2(100) := '';
  charOutBillNo varchar2(50);
 
  charItemNo    varchar2(100);
  numItemNum    number(10,0);
  tempItemNo    varchar2(100);
  tempItemNum    number(10,0);
  charTransType varchar2(10);
  charAccountType varchar2(10);
  numItemCount  number(10,0) := 0;
  recCount  number(10,0) := 0;
 
  sqlInsOne    varchar2(500);
  sqlInsTwo    varchar2(500);
 
  charItemName   mt_item.v_itemname%type;
  charContact    mt_receiver.v_contact%type;

BEGIN
  fDate := TO_DATE(fromDate,'yyyy-mm-dd');
  tDate := TO_DATE(endDate,'yyyy-mm-dd')+1;
  sqlSTR := 'SELECT * FROM OT_LBHEAD WHERE D_DESIGNATIONDATE IS NOT NULL ';
  IF TRIM(deptNo) = '' OR deptNo IS NULL THEN
    sqlSTR := sqlSTR || 'AND V_DEPTNO IN (' || deptPurview ||') ';
  ELSE
    sqlSTR := sqlSTR || 'AND V_DEPTNO =''' || deptNo ||''' ';
  END IF; 
  IF TRIM(cusType) = '-1' THEN
    sqlSTR := sqlSTR || 'AND V_BRANCHCODE = ''-1'' ';
  ELSE
    sqlSTR := sqlSTR || 'AND V_BRANCHCODE IN (' || carrierPurview ||') ';
  END IF;
  sqlSTR := sqlSTR || 'AND D_OPERATEDATE >= ''' || fDate ||''' ';
  sqlSTR := sqlSTR || 'AND D_OPERATEDATE <= ''' || tDate ||''' ';
  IF transType IS NOT NULL OR TRIM(transType) <>'' THEN
    sqlSTR := sqlSTR || 'AND V_TRANSTYPE = ''' || transType ||''' ';
  END IF;
  IF toCity IS NOT NULL OR TRIM(toCity) <> '' THEN
    sqlSTR := sqlSTR || 'AND V_PORTTO = ''' || toCity ||''' ';
  END IF;
  IF cusName IS NOT NULL OR TRIM(cusName) <> '' THEN
    sqlSTR := sqlSTR || 'AND V_BRANCHCODE = ''' || cusName ||''' ';
  END IF;
  sqlSTR := sqlSTR || 'ORDER BY D_OPERATEDATE,V_LBNO';
  --build temp. table (do not use create directly)
  sqlCreate := 'CREATE GLOBAL TEMPORARY TABLE TEMPTRACK ' 
    ||'(V_ORDERDATE VARCHAR2(10) DEFAULT '' '',V_PRINTNO   VARCHAR2(20)  DEFAULT '' '',V_RECEIVER  VARCHAR2(100) DEFAULT '' '',V_CONTACT  VARCHAR2(100) DEFAULT '' '','
 ||'V_TOCITY     VARCHAR2(50) DEFAULT '' '',V_TRANSTYPE VARCHAR2(10) DEFAULT '' '',N_ITEMNUM   NUMBER(10,0)  DEFAULT     0,'
 ||'N_WEIGHT     NUMBER(10,0) DEFAULT      0,V_SEND    VARCHAR2(100) DEFAULT '' '',V_PAY       VARCHAR2(10)  DEFAULT '' '','
 ||'V_ITEM      VARCHAR2(100) DEFAULT '' '',V_ITEMNAME VARCHAR2(100) DEFAULT '' '',N_PCS       NUMBER(10,0) DEFAULT     0,V_VOLUME    VARCHAR2(20)  DEFAULT '' '','
 ||'V_AGENTLBNO  VARCHAR2(20) DEFAULT '' '',V_AGENTTEL VARCHAR2(150) DEFAULT '' '',V_FLIGHT    VARCHAR2(50)  DEFAULT '' '','
 ||'V_DUE        VARCHAR2(20) DEFAULT '' '',V_SIGN      VARCHAR2(20) DEFAULT '' '',V_DUEREMARK VARCHAR2(100) DEFAULT '' '','
 ||'V_CARRIER   VARCHAR2(100) DEFAULT '' '',N_INCOME    NUMBER(10,2)  )ON COMMIT PRESERVE ROWS';
  SELECT COUNT(*) INTO isEXIST FROM ALL_TABLES WHERE TABLE_NAME='TEMPTRACK';
  --sqlCreate:='drop table TEMPTRACK';
 
 
  IF isEXIST = 0 THEN
    EXECUTE IMMEDIATE sqlCreate;
  ELSE
    EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMPTRACK';
  END IF;
  --process lbbill loop
  OPEN C_LBNO FOR sqlSTR;
     LOOP
       FETCH C_LBNO INTO R_LBNO;
     EXIT WHEN C_LBNO%NOTFOUND;
--sp_ExceptionParaLog('RET_CURSOR-11','R_LBNO='||R_LBNO.V_LBNO);
     --query detail
    SELECT SUM(DECODE(V_CALTYPE,'件数',N_QTY,0))+SUM(DECODE(V_CALTYPE,'重量',N_QTY,0)) f_item,
      SUM(DECODE(V_CALTYPE,'重量',N_CHARGEWEIGHT,0)) f_weight
   INTO numItem,numWeight
   FROM OT_LBDETAIL WHERE V_LBNO=R_LBNO.V_LBNO;
    --query income
    SELECT SUM(N_COST) INTO numIncome FROM OT_LBCOST
      WHERE V_COSTCODE NOT IN ('TOTAL','LBIN')
   AND V_TYPE= '1'
   AND V_LBNO=R_LBNO.V_LBNO;
    --query agent tel.
    SELECT COUNT(*) INTO recCount FROM OT_COMPANY WHERE V_COMPANYCODE=R_LBNO.V_COMPANYCODE;
    IF recCount > 0 THEN
     SELECT V_COMPANYNAME,V_TELEPHONE INTO charAgentName,charAgentTel
      FROM OT_COMPANY
      WHERE V_COMPANYCODE=R_LBNO.V_COMPANYCODE;
    END IF;
    recCount := 0;
    --query carrier tel.
    SELECT COUNT(*) INTO recCount FROM MT_CARRIER WHERE V_CARRIERCODE=R_LBNO.V_BRANCHCODE;
    IF recCount > 0 THEN
     SELECT V_CARRIERNAME INTO charCarrier
      FROM MT_CARRIER
      WHERE V_CARRIERCODE=R_LBNO.V_BRANCHCODE;
    END IF;
   
    ---------------------------------
    recCount := 0;
    --query carrier tel.
    SELECT COUNT(*) INTO recCount FROM MT_RECEIVER WHERE V_RECEIVERCODE=R_LBNO.V_RECEIVERCODE;
    --sp_ExceptionLog('R_LBNO.V_RECEIVERCODE======'||R_LBNO.V_RECEIVERCODE);
    IF recCount > 0 THEN
     SELECT nvl(V_CONTACT,'') INTO charContact
      FROM MT_RECEIVER
      WHERE V_RECEIVERCODE=R_LBNO.V_RECEIVERCODE;
    ELSE
      charContact:=R_LBNO.V_RECEIVERNAME; 
    END IF;   
    ---------------------------------
    recCount := 0;
    --query transtype
    SELECT V_TRANSNAME INTO charTransType FROM D_TRANSTYPE
      WHERE V_TRANSTYPE=R_LBNO.V_TRANSTYPE;
    --query paytype
    SELECT COUNT(*) INTO recCount FROM D_ACCOUNTTYPE WHERE V_ACCOUNTTYPE=R_LBNO.V_ACCOUNTTYPE;
    IF recCount > 0 THEN
     SELECT V_ACCOUNTDESC INTO charAccountType FROM D_ACCOUNTTYPE
      WHERE V_ACCOUNTTYPE=R_LBNO.V_ACCOUNTTYPE;
    END IF;
    recCount := 0;
    --insert temp. table
   IF R_LBNO.V_BRANCHCODE <> '-1' THEN
    OPEN C_OUTBILLNO FOR
      SELECT V_BILLNO FROM OT_LBBILL WHERE V_LBNO=R_LBNO.V_LBNO;  
    LOOP
      FETCH C_OUTBILLNO INTO charOutBillNo;
   EXIT WHEN C_OUTBILLNO%NOTFOUND;
   OPEN C_OUTBILLINFO FOR
     SELECT a.V_ITEMNO,nvl(b.v_Itemname,''),a.N_TRANSQTY
       FROM MT_TRANSACTION a,MT_ITEM b WHERE a.v_itemno=b.v_itemno(+) and a.V_BILLNO=charOutBillNo;
     LOOP
     numItemCount := numItemCount + 1;
     FETCH C_OUTBILLINFO INTO charItemNo,charItemname,numItemNum;
    
      
    
     EXIT WHEN C_OUTBILLINFO%NOTFOUND;
      IF numItemCount = 1 THEN
          sqlInsOne := 'INSERT INTO TEMPTRACK VALUES ('
                 ||''''||TO_CHAR(R_LBNO.D_ORDERDATE,'yyyy-mm-dd')||''','
     ||''''||R_LBNO.V_BILLNO||''','
     ||''''||R_LBNO.V_RECEIVERNAME||''','
     ||''''||charContact||''','
     ||''''||R_LBNO.V_PORTNAMETO||''','
     ||''''||charTransType||''','
     ||''''||numItem||''','
     ||''''||numWeight||''','
     ||''''||R_LBNO.V_HANDLEINFO||R_LBNO.V_ACCOUNTINFO||''','
     ||''''||charAccountType||''','
     ||''''||charItemNo||''','
     ||''''||charItemName||''','
     ||''''||numItemNum||''','
     ||'''0'','
     ||''''||R_LBNO.V_AGENTBILLNO||''','
     ||''''||charAgentName||'/'||charAgentTel||''','
     ||''''||R_LBNO.V_FREIGHTNO||'/'||TO_CHAR(R_LBNO.D_FREIGHTDATE,'yyyy-mm-dd')||''','
     ||''''||TO_CHAR(R_LBNO.D_DUEDATE,'yyyy-mm-dd')||''','
     ||''''||R_LBNO.V_CONTACT||''','
     ||''''||R_LBNO.V_DUEDESC||''','
     ||''''||charCarrier||''','
     ||''''||numIncome||''')';
--sp_ExceptionParaLog('RET_CURSOR-2','sqlInsOne='||sqlInsOne);
             EXECUTE IMMEDIATE sqlInsOne;
     ELSE
          sqlInsTwo := 'INSERT INTO TEMPTRACK (V_ITEM,N_PCS) VALUES ('
     ||''''||charItemNo||''','
     ||''''||numItemNum||''')';
--sp_ExceptionParaLog('RET_CURSOR-3','sqlInsTwo='||sqlInsTwo);
             EXECUTE IMMEDIATE sqlInsTwo;
     END IF;
     END LOOP;
   CLOSE C_OUTBILLINFO;
    END LOOP;
    CLOSE C_OUTBILLNO;
   ELSE
    OPEN C_LBDETAIL FOR
      SELECT V_ITEMNAME,N_QTY INTO charItemNo,numItemNum FROM OT_LBDETAIL
        WHERE V_LBNO=R_LBNO.V_LBNO; 
     charItemname:=charItemNo;
--sp_ExceptionParaLog('RET_CURSOR-8','charItemNo='||charItemNo);
      LOOP
   FETCH C_LBDETAIL INTO tempItemNo,tempItemNum;
   EXIT WHEN C_LBDETAIL%NOTFOUND;
   charItemNo := charItemNo ||tempItemNo||'/';
   numItemNum := numItemNum +  tempItemNum;
   END LOOP;  
    CLOSE C_LBDETAIL;
    sqlInsOne := 'INSERT INTO TEMPTRACK VALUES ('
                 ||''''||TO_CHAR(R_LBNO.D_OPERATEDATE,'yyyy-mm-dd')||''','
     ||''''||R_LBNO.V_BILLNO||''','
     ||''''||R_LBNO.V_RECEIVERNAME||''','
     ||''''||charContact||''','
     ||''''||R_LBNO.V_PORTNAMETO||''','
     ||''''||charTransType||''','
     ||''''||numItem||''','
     ||''''||numWeight||''','
     ||''''||R_LBNO.V_HANDLEINFO||R_LBNO.V_ACCOUNTINFO||''','
     ||''''||charAccountType||''','
     ||''''||charItemNo||''','
     ||''''||charItemName||''','
     ||''''||numItemNum||''','
     ||'''0'','
     ||''''||R_LBNO.V_AGENTBILLNO||''','
     ||''''||charAgentName||'/'||charAgentTel||''','
     ||''''||R_LBNO.V_FREIGHTNO||'/'||TO_CHAR(R_LBNO.D_FREIGHTDATE,'yyyy-mm-dd')||''','
     ||''''||TO_CHAR(R_LBNO.D_DUEDATE,'yyyy-mm-dd')||''','
     ||''''||R_LBNO.V_CONTACT||''','
     ||''''||R_LBNO.V_DUEDESC||''','
     ||''''||R_LBNO.V_FULLNAME||''','
     ||''''||numIncome||''')';
--sp_ExceptionParaLog('RET_CURSOR-4','sqlInsOne='||sqlInsOne);
    EXECUTE IMMEDIATE sqlInsOne;
    charItemNo := ''; 
    numItemNum := 0;
   END IF;
   numItemCount := 0;
     END LOOP;
  CLOSE C_LBNO;
  OPEN C_LBTRACK FOR 'SELECT * FROM TEMPTRACK';
  RETURN C_LBTRACK;
  EXCEPTION
    WHEN OTHERS THEN
   ROLLBACK;
   sp_ExceptionLog('RET_CURSOR-1');
   RETURN C_LBTRACK;
END;
FUNCTION GET_LBASSURANCE
 (
  fromDate IN varchar2,
  endDate IN varchar2,
  cusType IN varchar2,
  cusName IN varchar2,
  transType IN varchar2,
  printBillNo IN varchar2,
  deptPurview IN varchar2,
  carrierPurview IN varchar2
 )
 RETURN RefCursor
 AS
   C_LBASSURANCE RefCursor;
 BEGIN
 RETURN C_LBASSURANCE;
 END;
END;

阅读更多
个人分类: Database---Oracle
想对作者说点什么? 我来说一句

Oracle驱动包

2017年09月06日 1.81MB 下载

oracle_driver.rar

2008年09月30日 2.91MB 下载

用于oracle 的驱动包

2009年04月12日 52KB 下载

plsql oracle登录包

2017年11月28日 70.13MB 下载

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭