简单的存储过程例子

create or replace procedure P_GET_STRUCT_PRICE_NEW(V_CATEGORY_STYLE IN VARCHAR2,
                                                   V_BRIDGE_CODE    IN VARCHAR2,
                                                   V_STRUCTURE      IN VARCHAR2,
                                                   V_COST_TYPE     IN VARCHAR2,
                                                   V_COST_TYPE_SUPPLIER  IN VARCHAR2,
                                                  V_ANALYSIS_ID   IN VARCHAR2,
                                                   V_START_DATE     DATE,
                                                   V_END_DATE       DATE,
                                                   V_PRICE     OUT NUMBER) IS

    --零件图号
  V_PART_CODE VARCHAR2(500);
  --零件名称
  V_PART_NAME VARCHAR2(500);
  --物料编码对照表和物料表物料编码
  --v_material_code varchar2(500);
  --整桥物料表物料编码
  V_SUPPLIER_MATCODE VARCHAR2(500);
  --BOM价格和供应商
  V_BOM_PRICE_AND_SUPPLIER VARCHAR2(500);
  --高价单价
  V_HIGH_PRICE NUMBER:=0;
  --高价金额
  V_HIGH_MONEY NUMBER:=0;
  --结构高价金额总计
  V_HIGH_TOTAL_PRICE NUMBER:=0;
  --低价单价
  V_LOW_PRICE NUMBER:=0;
  --低价金额
  V_LOW_MONEY NUMBER:=0;
  --结构低价金额总计
  V_LOW_TOTAL_PRICE NUMBER:=0;
  --平均单价
  V_AVERAGE_PRICE NUMBER:=0;
  --平均金额
  V_AVERAGE_MONEY NUMBER:=0;
    --结构低价金额总计
  V_AVERAGE_TOTAL_PRICE NUMBER:=0;
  --指定供应商价格
  V_SPECIFY_SUPPLIER_PRICE NUMBER:=0;
  --指定供应商金额
  V_SPECIFY_SUPPLIER_MONEY NUMBER:=0;
  --物料编码总和
  V_MATERIAL_CODE_COUNT INT;
  CURSOR BRIDGE_BOM_CURSOR IS
  -- SELECT * FROM (
    SELECT S.BOM_LEVEL,
           B.PART_CODE,
           B.PART_NAME,
           S.STATUS,
           S.PART_STRUCTURE,
           S.PART_COUNT,
           S.PART_TOTAL_COUNT,
           S.PART_CATEGORY_STYLE,
           S.IS_USE_BRIDGE,
           B.ID,
           S.ORIGINAL_STRUCT
      FROM PRICE_NEW_PRODUCT_BOM_DETAIL B
      LEFT JOIN PRICE_NEW_BOM_RELATIONSHIP S
        ON B.ID = S.BOM_ID
     WHERE S.PART_CATEGORY_STYLE LIKE V_CATEGORY_STYLE || '%'
       AND S.SECOND_STRUCT LIKE '%' || V_STRUCTURE || '%'
       AND S.ANALYSIS_ID = V_ANALYSIS_ID;
      -- AND B.ANALYSIS_ID = V_ANALYSIS_ID
  --   ORDER BY S.ID DESC ) WHERE  Rownum=1;
  --声明游标变量
  V_BRIDGE_BOM_CURSOR BRIDGE_BOM_CURSOR%ROWTYPE;
BEGIN
  --打开BOM游标
  OPEN BRIDGE_BOM_CURSOR;
  LOOP
    FETCH BRIDGE_BOM_CURSOR
      INTO V_BRIDGE_BOM_CURSOR;
    IF BRIDGE_BOM_CURSOR%FOUND THEN
     --只计算用于整桥成本核算的
     IF V_BRIDGE_BOM_CURSOR.IS_USE_BRIDGE='0' THEN
      --零件图号
      V_PART_CODE := LTRIM(RTRIM(V_BRIDGE_BOM_CURSOR.PART_CODE));
      --零件名称
      V_PART_NAME := LTRIM(RTRIM(V_BRIDGE_BOM_CURSOR.PART_NAME));
      V_SUPPLIER_MATCODE := NULL;
      --取高价
      V_BOM_PRICE_AND_SUPPLIER := NULL;
      V_HIGH_PRICE             := 0;
      V_HIGH_MONEY             := 0;
      --取低价
      V_LOW_PRICE         := 0;
      V_LOW_MONEY         := 0;
      --取平均价
      V_AVERAGE_PRICE := 0;
      V_AVERAGE_MONEY := 0;
      --从整桥物料表取物料编码
      SELECT COUNT(S.PART_CODE)
        INTO V_MATERIAL_CODE_COUNT
        FROM PRICE_NEW_PRODUCT_SUPPLIER S
       WHERE S.PART_NUMBER = V_PART_CODE
         AND S.BRIDGE_ID = V_BRIDGE_CODE
         AND S.ANALYSIS_ID = V_ANALYSIS_ID;
      IF V_MATERIAL_CODE_COUNT > 0 THEN
        SELECT S.PART_CODE
          INTO V_SUPPLIER_MATCODE
          FROM PRICE_NEW_PRODUCT_SUPPLIER S
         WHERE S.PART_NUMBER = V_PART_CODE
           AND S.BRIDGE_ID = V_BRIDGE_CODE
           AND S.ANALYSIS_ID = V_ANALYSIS_ID;
        --取高价
        V_BOM_PRICE_AND_SUPPLIER := F_GET_BOM_PRICE(V_BRIDGE_CODE,
                                                    V_SUPPLIER_MATCODE,
                                                    V_START_DATE,
                                                    V_END_DATE,
                                                    1);
        V_HIGH_PRICE             := SUBSTR(V_BOM_PRICE_AND_SUPPLIER,
                                           1,
                                           INSTR(V_BOM_PRICE_AND_SUPPLIER,
                                                 '|') - 1);
        V_HIGH_MONEY             := V_BRIDGE_BOM_CURSOR.PART_TOTAL_COUNT *
                                    V_HIGH_PRICE;
        --取低价
        V_BOM_PRICE_AND_SUPPLIER := F_GET_BOM_PRICE(V_BRIDGE_CODE,
                                                    V_SUPPLIER_MATCODE,
                                                    V_START_DATE,
                                                    V_END_DATE,
                                                    2);
        V_LOW_PRICE              := SUBSTR(V_BOM_PRICE_AND_SUPPLIER,
                                           1,
                                           INSTR(V_BOM_PRICE_AND_SUPPLIER,
                                                 '|') - 1);
        V_LOW_MONEY              := V_BRIDGE_BOM_CURSOR.PART_TOTAL_COUNT *
                                    V_LOW_PRICE;
        --取平均价
        V_BOM_PRICE_AND_SUPPLIER := F_GET_BOM_PRICE(V_BRIDGE_CODE,
                                                    V_SUPPLIER_MATCODE,
                                                    V_START_DATE,
                                                    V_END_DATE,
                                                    3);
        V_AVERAGE_PRICE          := SUBSTR(V_BOM_PRICE_AND_SUPPLIER,
                                           1,
                                           INSTR(V_BOM_PRICE_AND_SUPPLIER,
                                                 '|') - 1);
        V_AVERAGE_MONEY          := V_BRIDGE_BOM_CURSOR.PART_TOTAL_COUNT *
                                    V_AVERAGE_PRICE;
      END IF;
          --新产品物料价格表中查找指定供应商相关信息
          IF(V_COST_TYPE_SUPPLIER=1) THEN
              BEGIN
        SELECT  NVL(T.SUPPLYER_PRICE, 0)
          INTO V_SPECIFY_SUPPLIER_PRICE
          FROM PRICE_NEW_PRODUCT_SUPPLIER T
          JOIN PRICE_SUPPLIER_MANAGEMENT PSM
            ON T.SUPPLYER_ID = PSM.ID
         WHERE T.BRIDGE_ID = V_BRIDGE_CODE
           AND T.PART_NUMBER = V_PART_CODE
           AND T.ANALYSIS_ID = V_ANALYSIS_ID;
        V_SPECIFY_SUPPLIER_MONEY := V_BRIDGE_BOM_CURSOR.PART_TOTAL_COUNT *
                                    V_SPECIFY_SUPPLIER_PRICE;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          V_SPECIFY_SUPPLIER_PRICE := 0;
      END;
          END IF;
      IF(V_SPECIFY_SUPPLIER_PRICE=0) THEN
      V_HIGH_TOTAL_PRICE:=V_HIGH_TOTAL_PRICE+V_HIGH_MONEY;
      V_LOW_TOTAL_PRICE:=V_LOW_TOTAL_PRICE+V_LOW_MONEY;
      V_AVERAGE_TOTAL_PRICE:=V_AVERAGE_TOTAL_PRICE+V_AVERAGE_MONEY;

      ELSE
        V_HIGH_TOTAL_PRICE:=V_HIGH_TOTAL_PRICE+V_SPECIFY_SUPPLIER_MONEY;
      V_LOW_TOTAL_PRICE:=V_LOW_TOTAL_PRICE+V_SPECIFY_SUPPLIER_MONEY;
      V_AVERAGE_TOTAL_PRICE:=V_AVERAGE_TOTAL_PRICE+V_SPECIFY_SUPPLIER_MONEY;

      END IF;
    END IF;
     END IF;
    EXIT WHEN BRIDGE_BOM_CURSOR%NOTFOUND;
  END LOOP;
  CLOSE BRIDGE_BOM_CURSOR;
  COMMIT;
   IF(V_COST_TYPE=1) THEN
  V_PRICE:=V_HIGH_TOTAL_PRICE;
  ELSIF(V_COST_TYPE=2) THEN
  V_PRICE:=V_LOW_TOTAL_PRICE;
  ELSE
  V_PRICE:=V_AVERAGE_TOTAL_PRICE;
  END IF;
END P_GET_STRUCT_PRICE_NEW;
/