oracle的包和存储过程sql调试综合使用

2013-02-26

包和存储过程如下:

包头:

create or replace package PKG_ORDER is
  type CUR_ORDER is ref cursor;
  --订单提取过程
  Procedure PROC_TAKE_OUT_ORDER(P_STAFF_ID  varchar2, --工号
                                P_PRONAME   varchar2, --产品名称
                                P_ORID      varchar2, --订单id
                                P_DEPID     int, --部门id
                                P_GRPIDS    varchar2, --班组id
                                P_BEGINTIME varchar2, --开始时间
                                P_ENDTIME   varchar2, --结束时间
                                P_OUT_ORID  out varchar2 --返回的订单id
                                ); --结果集
  --订单录入ibss过程
  Procedure PROC_IBSS_ORDER(P_STAFF_ID varchar2, --工号
                            P_ORID     varchar2 --订单id
                            );
end PKG_ORDER;

包体:

CREATE OR REPLACE Package Body PKG_ORDER is

--提单处理存储过程,插入lock表和日志表----------------------------------------------------
Procedure PROC_TAKE_OUT_ORDER(P_STAFF_ID varchar2, --工号
P_PRONAME varchar2, --产品名称
P_ORID varchar2, --订单id
P_DEPID int, --部门id
P_GRPIDS varchar2, --班组id
P_BEGINTIME varchar2, --开始时间
P_ENDTIME varchar2, --结束时间
P_OUT_ORID out varchar2 --返回的订单id
) is

type cur_type is ref cursor; --定义游标类型
cur_str cur_type; --策略:第一条可用策略
CUR_ORDER cur_type; --输出结果集

v_sort_rule varchar2(1000); --策略的sqlrule变量
v_sql_text varchar2(1000); --根据参数值动态生成的sql
V_PREORDER int := 0; --订单状态:预处理为0
L_ORID varchar2(20); --游标里的临时订单变量


begin

v_sql_text := ' WHERE O.STATUS =' || V_PREORDER ||
' AND NOT EXISTS (SELECT K.ORID FROM OM_LOCK K WHERE K.ORID=O.ORID)';
open cur_str for 'SELECT SORTRULE FROM PM_STRATEGY WHERE STATUS=1'; --取出策略放入游标
loop
fetch cur_str
into v_sort_rule; --遍历策略值 只有一个
exit when cur_str%notfound;

--如果staffid在指定录单人员的某些产品优先时 连接产品的权重表并排序
IF P_STAFF_ID IS NOT NULL THEN
v_sort_rule := replace(v_sort_rule,
'{join}',
'LEFT JOIN PM_STRATEGY_DETAIL SDPRO ON to_char(S.PM_ID) = SDPRO.FID');
--按照产品优先排序
v_sort_rule := replace(v_sort_rule,
'{order}',
'ORDER BY SDPRO.DVAL DESC,');
else
v_sort_rule := replace(v_sort_rule, '{join}', '');
v_sort_rule := replace(v_sort_rule, '{order}', 'ORDER BY');
END IF;

--产品名称不为空
if P_PRONAME IS NOT NULL then
v_sql_text := v_sql_text || ' AND S.NAME=''' || P_PRONAME || ''' ';
end if;

--订单id不为空
if P_ORID IS NOT NULL then
v_sql_text := v_sql_text || ' AND O.ORID=''' || P_ORID || ''' ';
end if;

--部门id不为空
if P_DEPID > 0 then
v_sql_text := v_sql_text || ' AND O.DEPARTMENT_ID=' || P_DEPID || ' ';
end if;

--业务权限班组id
if P_GRPIDS IS NOT NULL then
v_sql_text := v_sql_text || ' AND O.GROUP_ID IN(' || P_GRPIDS || ') ';
end if;

--开始时间
if P_BEGINTIME IS NOT NULL then
v_sql_text := v_sql_text || ' AND O.BEGIN_TIME>=TO_DATE(''' ||
P_BEGINTIME || ''',''yyyy-mm-dd hh24:mi:ss'') ';
end if;

--结束时间
if P_ENDTIME IS NOT NULL then
v_sql_text := v_sql_text || ' AND O.BEGIN_TIME<=TO_DATE(''' ||
P_ENDTIME || ''',''yyyy-mm-dd hh24:mi:ss'') ';
end if;

--拼接好sql后 v_sort_rule替换 {where}为v_sql_text
v_sort_rule := replace(v_sort_rule, '{where}', v_sql_text);

--将提出来的一条订单id赋给游标或者变量
open CUR_ORDER for v_sort_rule;
--dbms_output.put_line('sql='||v_sort_rule);

end loop;

close cur_str; --关闭策略游标

--插入lock表处理
LOOP
FETCH CUR_ORDER INTO L_ORID ;
EXIT WHEN CUR_ORDER%NOTFOUND;
INSERT INTO OM_LOCK (ORID,STAFF_ID,ACTID) VALUES (L_ORID,P_STAFF_ID,0);--插入lock表

INSERT INTO OM_LOG (ACT_STAFF,ACT_TIME,OLD_MSG,NEW_MSG,ACT_NUM,P_NAME,ORID) -- 插入到订单日志表
VALUES (P_STAFF_ID,sysdate,'锁定单(提单)','锁定单(提单)',10,'',L_ORID);

P_OUT_ORID :=L_ORID; --将订单id返回
END LOOP;
CLOSE CUR_ORDER; --关闭订单记录游标

COMMIT; --提交

end PROC_TAKE_OUT_ORDER;

-----订单录入过程--------------------------------------------------------------------------------
Procedure PROC_IBSS_ORDER(P_STAFF_ID varchar2, --工号
P_ORID varchar2, --订单id
P_OUT_ERR out varchar2 --错误信息
)is
type cur_type is ref cursor; --定义游标类型
--cur_str cur_type; --定义一个游标
--type om_data_type is table of om_data%rowtype;--om_data表类型
tmp_orid om_data.orid%type;--判断订单是否已经录入
tmp_lock_orid om_lock.orid%type;--判断lock表里是否有记录
begin

--该订单是否已录
select orid into tmp_orid from om_data where orid=P_ORID and status =1 and rownum = 1;
if tmp_orid is not null then
P_OUT_ERR :='err-001:该订单已录单';
end if;
--if 锁定表有记录
select orid into tmp_lock_orid from om_lock where orid=P_ORID and rownum = 1;
if tmp_lock_orid is not null then
-- 如果lock插入成功即对om_order表封锁
select orid into tmp_lock_orid from om_order where orid=P_ORID for update nowait;
else
P_OUT_ERR := 'err-002:非法操作,请退回重新进入';
end if;
-- 校验输入值

-- 更新ibss编号
-- if ibss编号更新成功
-- 更新终端(机身码)
-- if 号码存在则激活
-- else 插入号码日志
-- 插入录单操作到om_log
-- 插入礼品日志
-- 更新ibss成功录入时间,记录ibss录入记录
-- 当订单环节,状态,日志信息都完成后才解锁,避免重复提单
-- 录单成功

dbms_output.put_line('测试ibss_order'||P_STAFF_ID||'--'||P_ORID);
end PROC_IBSS_ORDER;

end PKG_ORDER;

测试:

rem PL/SQL Developer Test Script

set feedback off
set autoprint off

rem Execute PL/SQL Block
-- Created on 2013-2-26 by ADMINISTRATOR
declare
P_STAFF_ID varchar2(200) :='admin';--工号
P_PRONAME varchar2(200) :=''; --产品名称
P_ORID varchar2(200) :=''; --订单id
P_DEPID int :=0; --部门id
P_GRPIDS varchar2(200) :=''; --班组id
P_BEGINTIME varchar2(200) :=''; --开始时间
P_ENDTIME varchar2(200) :=''; --结束时间
P_OUT_ORID varchar2(200) :=''; --返回的订单id

begin
--dbms_output.put_line('ddd');
pkg_order.PROC_TAKE_OUT_ORDER(P_STAFF_ID,P_PRONAME,P_ORID,P_DEPID,P_GRPIDS,P_BEGINTIME,P_ENDTIME,P_OUT_ORID);
end;
/

点击Debug里的start然后就可以进入到包里的存储过程单步调试了

作者:robotbird, 分类:关于代码 标签: oracle 数据库 , 浏览(2482), 评论(0)
上一篇: oracle包和存储过程使用
下一篇: 从今天开始坚持不刷技术类文章

相关文章

(0)条评论 订阅

发表评论

电子邮件用于回复通知和avatar全球唯一头像 *

*