基于oracle数据库存储过程的创建及调用

plsql 是oracle对sql语言的过程化扩展通过sql命令语言中增加了过程处理语句（分支，循环），使sql语言具有过程处理能力 declare 声明变量和游标 begin end；方法体 dbms_output.put_line('');//打印

set serveroutput on 打开打印控制

引用型变量：

　　变量的类型和长度取决于表中字段的类型和长度

　　通过表名.列名%TYPE 指定变脸的类型和长度，例如 v_name emp.ename%TYPE；

　　使用普通变量定义方式，需要知道表中列的类型，而使用引用类型，不需要考虑列的类型，使用%TYPE是非常好的编程风格，因为他使得PLSQL更加灵活，更加适应对数据库定义的更新

记录型变量

　　接收表中的一整行记录

　　语法：变量名称表明%ROWTYPE 例如 V_EMP EMP%ROWTYPE

流程控制

　　条件分支：

IF 条件 THEN 执行1
ELSIF 条件2 THEN 执行2
ELSE 执行3

　　循环：

loop循环
- loop
- exit when v_num > 10;
- dbms_output.put_line(V_NUM);
- v_num:= v_num +1;
- end loop;

　　游标：

用于临时存储一个查询返回的多行数据，通过遍历有表，可以逐行访问处理该结果集的数据；
游标的使用方式：声明-打开-读取-关闭
游标声明：
- CURSOR 游标名[参数列表] IS 查询语句
- OPEN 游标
- 游标的取值
- FETCH 游标名 INTO 变量列表
- 游标的关闭
- CLOSE 游标名　　　
游标的属性
- %ROWCOUNT 整形，获得FETCH语句返回的数据行数
- %FOUNFD 布尔型最近的FETCH语句返回一行数据则为真，否则为假
- %NOTFOUND 布尔型与%FOUND属性返回值相反
- %ISOPEN 布尔型游标已经打开时为真，否则为假

　　存储过程：

psql是将一个个plsql的业务处理过程存储起来进行复用，这些被存储起来的plsql程序称之为存储过程
create or replace procedure 过程名称[(参数列表)] IS
begin
end[过程名称]
可以不带参数，可以带输入参数，带输入输出参数
is和as是可以互用的
过程中没有declare关键字，declare用在语句块中
命令行调用exec + 存储过程名称

CREATE OR REPLACE PROCEDURE PROC_TMS_ORDERDISCOUNT IS
  --批量刷新历史长途订单信息，更新折扣信息
  CURSOR CUR_ORDER IS
    SELECT T.CARRIER_ID, -- 承运商ID,
           T3.CITY, -- 出发城市,
           T4.CITY, --  目的城市
           T.FINISH_TIME, --配送时间
           T.ORDER_NUMBER --订单ID
      FROM BIZ_ORDER T, BIZ_DEMAND T2, BIZ_FACTORY T3, BIZ_FACTORY T4
     WHERE T.ORDER_STATUS = 'DELIVERY_COMPLETED'
       AND T.DEMAND_ID = T2.ID
       AND T2.TRANSPORT_TYPE = 'LONG_DISTANCE'
       AND T3.ID = T2.DEPARTURE_FACTORY_ID
       AND T4.ID = T2.DESTINATION_FACTORY_ID
     ORDER BY T.FINISH_TIME ASC;

  V_CARRIER_ID   BIZ_ORDER.CARRIER_ID%TYPE;
  V_FROM_CITY    BIZ_FACTORY.CITY%TYPE;
  V_TO_CITY      BIZ_FACTORY.CITY%TYPE;
  V_FINISH_TIME  BIZ_ORDER.FINISH_TIME%TYPE;
  V_ORDER_NUMBER BIZ_ORDER.ORDER_NUMBER%TYPE;

  V_SUM         NUMBER(10) := 0;
  V_DISCOUNT_ID BIZ_ORDER_DISCOUNT.ID%TYPE;

BEGIN
  -- 打开游标
  OPEN CUR_ORDER;
  LOOP
    FETCH CUR_ORDER
      INTO V_CARRIER_ID,
           V_FROM_CITY,
           V_TO_CITY,
           V_FINISH_TIME,
           V_ORDER_NUMBER;
    --获取折扣信息，取满足条件的最早一条
    SELECT (SELECT *
              FROM (SELECT T.ID
                      FROM BIZ_ORDER_DISCOUNT T
                     WHERE T.FROM_CITY = V_TO_CITY
                       AND T.TO_CITY = V_FROM_CITY
                       AND T.CARRIER_ID = V_CARRIER_ID
                       AND T.ACTIVE_TIME > V_FINISH_TIME --刷新历史数据以配送完成时间为准
                       AND T.ACT_FLAG = 'Y'
                     ORDER BY T.ACTIVE_TIME ASC)
             WHERE ROWNUM = 1)
      INTO V_DISCOUNT_ID
      FROM DUAL;
    --订单不满足折扣条件，创建折扣信息，更新订单信息
    IF V_DISCOUNT_ID IS NULL THEN
      INSERT INTO BIZ_ORDER_DISCOUNT
        (CREATE_BY,
         CREATE_TIME,
         UPDATE_BY,
         UPDATE_TIME,
         ACTIVE_TIME,
         TO_CITY,
         FROM_CITY,
         CARRIER_ID,
         ORDER_ID_A)
      VALUES
        ('1',
         V_FINISH_TIME,
         '1',
         V_FINISH_TIME,
         V_FINISH_TIME + 1.5, --刷新历史数据以配送完成时间为准
         V_TO_CITY,
         V_FROM_CITY,
         V_CARRIER_ID,
         V_ORDER_NUMBER);
      COMMIT;
      UPDATE BIZ_ORDER OD
         SET OD.SALE = 1
       WHERE OD.ORDER_NUMBER = V_ORDER_NUMBER;
      COMMIT;
      --订单满足折扣条件，更新折扣信息，更新订单信息
    ELSE
      UPDATE BIZ_ORDER_DISCOUNT
         SET ACT_FLAG    = 'N',
             ORDER_ID_B  = V_ORDER_NUMBER,
             UPDATE_TIME = V_FINISH_TIME
       WHERE ID = V_DISCOUNT_ID;
      COMMIT;
      UPDATE BIZ_ORDER OD
         SET OD.SALE = 0
       WHERE OD.ORDER_NUMBER = V_ORDER_NUMBER;
      COMMIT;
    END IF;
    EXIT WHEN CUR_ORDER%NOTFOUND;
  END LOOP;
  CLOSE CUR_ORDER;
END;