登录  
 加关注
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

飞哥的技术博客

世上无难事,只怕有心人!

 
 
 

日志

 
 
 
 

oracle存储过程实战-2  

2009-05-11 12:32:20|  分类: Oracle |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

--定义获取部门ID的函数
create or replace function Get_Dept_Id(in_Dept_name in varchar2) return integer
as
v_dept_id integer := 1;
begin
     sELECT dept_id into v_dept_id  FROM dict_depts WHERE DEPT_NAME = in_Dept_name;
     return v_dept_id;
     exception
         when no_data_found then
         v_dept_id := 1;
         return v_dept_id;
         when others then
         v_dept_id := 1200; --糯扎渡项目部将返回2条记录,在此处捕获并重新赋值
          return v_dept_id;
end Get_Dept_Id;
--定义获取单位ID的函数
create or replace function Get_Unit_Id(in_unit_name in varchar2) return integer
as
v_unit_id integer := 1;
begin
     SELECT id into v_unit_id FROM ins_unit_dict WHERE >     return v_unit_id;
     exception
         when no_data_found then
         v_unit_id := 1;
         return v_unit_id;
        
end Get_Unit_Id;

--存储过程定义代码
/*调用注意事项:
目标表的数据列的数据类型必须符合下述规定:
设备名称 varchar2;
规格型号 varchar2;
原值 number(22,4)/ varchar2;
静值 number(22,4)/ varchar2;
使用单位 varchar2/ varchar2;
分类 varchar2;
单位 varchar2;
*/
/*
参数说明:
in_math_type:机具类型
in_OWNER_NAME :产权单位
in_Self_Code_pre:自编号前缀
*/
create or replace procedure sub_mach_trans(in_math_type in integer,in_OWNER_NAME in varchar2,in_Self_Code_pre in varchar2) is
type t_target_Data IS REF CURSOR; --目标数据类型
v_CursorVar t_target_Data; --定义类型游标
--可以从目标表中获取的变量
v_MACH_NAME 项目部机具.设备名称%TYPE;           --机具名称;
v_MACH_SPEC 项目部机具.规格型号%TYPE;           --规格型号;
v_ORIGINAL_VALUE  项目部机具.原值%TYPE;         --机具原值
v_CUR_VALUE 项目部机具.净值%TYPE;               --机具净值
v_CUR_USED_DEPTID_name 项目部机具.使用单位%TYPE;--使用单位名称
v_VALUE_TYPE 项目部机具.分类%TYPE;              --机具价值分类
v_MACH_UNIT_NAME 项目部机具.单位%TYPE;          --计量单位名称
v_ID 项目部机具.序号%TYPE;                      --序号
v_COMMENTS 项目部机具.备注%TYPE;                --备注信息

v_AMORTISE_PRICE 项目部机具.十月摊销%TYPE;      --摊销单价
--需要计算或定义的变量
v_CUR_USED_DEPTID integer;      --使用部门编号
v_MACH_UNIT integer ;           --计量单位编号
v_MACH_BASE_TYPE integer := 2;  --基础设备类型
v_SELF_CODE varchar2(50) := ''; --自编号
v_CUR_STATE integer;            --当前状态

v_Value_Type_num integer := 0;  --价值分类数值表述变量
v_Record_cur_index integer;     --当前记录Id
v_HANDOVER_Counter integer := 1;--领用记录计数器
--系统调试用变量
S_Process_Counter integer := 0;
--系统异常对象
SYS_DEBUG_EXC EXCEPTION;
v_ErrorCode NUMBER;          -- 出错的代码
v_ErrorMsg  VARCHAR2(200);   -- 错误的消息显示
v_CurrentUser VARCHAR2(8);   -- 当前数据库用户
v_Information VARCHAR2(100); -- 关于错误的信息
  begin
      --读取数据
      OPEN v_CursorVar  FOR
           SELECT 设备名称,规格型号,原值,净值,使用单位,分类,单位,序号,备注,十月摊销 FROM 项目部机具 order by 序号;

      --遍历数据

      LOOP
           --从游标中提取记录值赋予制定变量
           FETCH v_CursorVar  INTO v_MACH_NAME, v_MACH_SPEC,v_ORIGINAL_VALUE,v_CUR_VALUE,v_CUR_USED_DEPTID_name,v_VALUE_TYPE,v_MACH_UNIT_NAME,v_Id,v_COMMENTS,v_AMORTISE_PRICE;
           --退出条件
           EXIT WHEN v_CursorVar %NOTFOUND;
              --工作代码
              --使用部门编号
              v_CUR_USED_DEPTID := Get_Dept_Id(v_CUR_USED_DEPTID_name);
              --计量单位编号
              v_MACH_UNIT := Get_Unit_Id(v_MACH_UNIT_NAME);
                --自编号
                v_SELF_CODE := in_Self_Code_pre||to_char(v_Id);
                --当前状态
                if v_CUR_USED_DEPTID = 1 then
                   v_CUR_STATE := 0;
                else
   &nb

sp;               v_CUR_STATE := 1;
                end if;
                -- 价值分类
                if v_VALUE_TYPE = 'A' then
                   v_Value_Type_num := 1;
                ELSIF v_VALUE_TYPE = 'B' then
                   v_Value_Type_num:= 2;
                ELSIF v_VALUE_TYPE = 'C' then
                   v_Value_Type_num := 3;
                ELSIF v_VALUE_TYPE = 'D' then
                   v_Value_Type_num := 4;
                else
                   v_Value_Type_num := 0;
                end if;               
                --执行复制到mach_basic_info表
              insert into mach_basic_info
                (
                     id,
                     MACH_NAME ,
                     MACH_TYPE ,
                     MACH_SPEC ,
                     OWNER_NAME,
                     ORIGINAL_VALUE  ,
                     CUR_VALUE,CUR_STATE ,
                     CUR_USED_DEPTID ,
                     VALUE_TYPE ,
                   MACH_UNIT ,
                     MACH_UNIT_NAME ,
                     MACH_BASE_TYPE,
                     comments,
                     self_code
                 )
              values
                (
                     seq_mach_basic_info.nextval,
                     v_MACH_NAME ,
                     in_math_type ,
                     v_MACH_SPEC ,
                     in_OWNER_NAME ,
                     CAST(v_ORIGINAL_VALUE AS number(22,4))  ,
                     CAST(v_CUR_VALUE AS number(22,4)),
                     v_CUR_STATE ,v_CUR_USED_DEPTID ,
                     v_Value_Type_num ,
                   v_MACH_UNIT ,
                     v_MACH_UNIT_NAME ,
                 &

nbsp;   v_MACH_BASE_TYPE,
                     v_COMMENTS,
                     v_SELF_CODE
                );
                --记录当前记录id
                select max(id) into v_Record_cur_index from mach_basic_info;
                --插入记录到MACH_AMOR_ATTACH_INFO表:摊销价格表
                insert into MACH_AMOR_ATTACH_INFO
                (
                    id,
                    MACH_ID,
                    AMORTISE_PRICE,
                    COMMENTS
                )
                values
                (
                     seq_MACH_AMOR_ATTACH_INFO.Nextval,
                     v_Record_cur_index,
                     cast( v_AMORTISE_PRICE AS number(22,6)),
                     '2006-11-18,程序自动导入,凭据:10月摊销金额'
                 );
                --插入记录到MACH_HANDOVER_RECORD表:领用记录表
                 if v_CUR_STATE = 1 then
                     insert into MACH_HANDOVER_RECORD
                     (
                         id,
                         MACH_ID,             --设备序号
                         ORDER_CODE,          --交接单号
                         HANDOVER_DATE,       --交接日期
                         HANDOVER_SPAN_CODE,  --交接日期所在财务月
                         FROM_DEPTID,         --移交单位
                         TO_DEPTID,           --接收单位
                         HANDOVER_STATE,      --交接单状态(枚举 0:未交接 1:已交接 2:已退库)
                         AMORTISE_MONEY,      --摊销单价(若为空自动从台帐中读取)
                         COMMENTS
                      )
                      values
                      (
                         seq_MACH_HANDOVER_RECORD.Nextval,
                         v_Record_cur_index,
                    &nbs

p;    '领_2006_10_'||to_char(v_HANDOVER_Counter),
                         to_date('2006-10-01','yyyy-mm-dd'),
                         '200610C',
                         1,
                         v_CUR_USED_DEPTID,
                         1,
                         cast( v_AMORTISE_PRICE AS number(22,6)),
                         '2006-11-18,程序自动导入,凭据:10月摊销金额'
                      );
                      --计数器加1
                      v_HANDOVER_Counter := v_HANDOVER_Counter + 1;
                end if;
                --输出信息
                dbms_output.put_line('当前完成:'|| v_MACH_NAME  );
                S_Process_Counter := S_Process_Counter + 1;
                dbms_output.put_line('累计完成:' || to_char(S_Process_Counter));
                --调试异常
                /*
                if S_Process_Counter = 2 then
                   Raise SYS_DEBUG_EXC;
                end if;
                */
      END LOOP;
      --关闭游标
      CLOSE v_CursorVar ;
      --提交事务
      commit;
             --异常处理
             exception
               --自定义异常
               when SYS_DEBUG_EXC then
                   --关闭游标
                   CLOSE v_CursorVar ;
                   dbms_output.put_line('异常调试,自动回滚');
                   --回滚事务
                   rollback;
               --其他异常
               when others then
                    --关闭游标
                   CLOSE v_CursorVar ;
                   v_ErrorCode := SQLCODE;
                   v_ErrorMsg := SQLERRM;
                   v_CurrentUser := USER;
                   v_Information := '遇到了错误 ' || TO_CHAR(SYSDATE) || ' 数据库用户 ' || v_CurrentUser;
                   dbms_output.put_line('执行错误,自动回滚');
                   dbms_output.put_line('详细信息:'||'错误代码:'||v_ErrorCode||',错误消息:'||v_ErrorMsg||',日志信息:'||v_Information);
                   --回滚事务
                   rollback;
end sub_mach_trans;

--存储过程调用代码
set serverout on  --开启系统输出
execute sub_mach_trans(334 ,'自购' ,'项目部-');

 
--历史镜像
select max(id) from mach_basic_info t
--检查结果
select * from mach_basic_info where id >3217

select * from dict_depts where dept_name  like '%物资部%'
--id is 9
select * from dict_depts where dept_name  like '%水工厂%'
--id is 2418
select * from mater_stock_dict where name like '%钢材%'
--id is 885
select * from mater_stock_dict where name like '%水工厂%'
--id is 1226

select * from mater_out_stock_base where receive_dept_id = 2418 and out_stock_id = 885 and >select * from mater_out_stock_detail where base_id =  23231
select * from mater_in_stock_base where in_stock_id = 1226
select * from mater_in_stock_detail where base_id = 14142

--辅助功能函数
create or replace function GetSubStorageBillNo
(
 in_SubStorageBillNoPre in varchar2,--二级入库单编号前缀,如'工矿-','电-','办公-'等
 in_BuyType in integer,----物资购买类型(1:统供;2:自购)
 in_SubStorageId in integer,--二级库ID号
 in_yearCode in varchar,--当前记录所在财务年
 in_monthCode in varchar--当前记录所在财务月
)
return varchar
as
v_billNo varchar2(50);
v_CurBillNo varchar2(50);
v_returnBillNo varchar2(50);
v_buyTypeChar varchar2(2);
v_Incre


--定义获取部门ID的函数
create or replace function Get_Dept_Id(in_Dept_name in varchar2) return integer
as
v_dept_id integer := 1;
begin
     sELECT dept_id into v_dept_id  FROM dict_depts WHERE DEPT_NAME = in_Dept_name;
     return v_dept_id;
     exception
         when no_data_found then
         v_dept_id := 1;
         return v_dept_id;
         when others then
         v_dept_id := 1200; --糯扎渡项目部将返回2条记录,在此处捕获并重新赋值
          return v_dept_id;
end Get_Dept_Id;
--定义获取单位ID的函数
create or replace function Get_Unit_Id(in_unit_name in varchar2) return integer
as
v_unit_id integer := 1;
begin
     SELECT id into v_unit_id FROM ins_unit_dict WHERE >     return v_unit_id;
     exception
         when no_data_found then
         v_unit_id := 1;
         return v_unit_id;
        
end Get_Unit_Id;

--存储过程定义代码
/*调用注意事项:
目标表的数据列的数据类型必须符合下述规定:
设备名称 varchar2;
规格型号 varchar2;
原值 number(22,4)/ varchar2;
静值 number(22,4)/ varchar2;
使用单位 varchar2/ varchar2;
分类 varchar2;
单位 varchar2;
*/
/*
参数说明:
in_math_type:机具类型
in_OWNER_NAME :产权单位
in_Self_Code_pre:自编号前缀
*/
create or replace procedure sub_mach_trans(in_math_type in integer,in_OWNER_NAME in varchar2,in_Self_Code_pre in varchar2) is
type t_target_Data IS REF CURSOR; --目标数据类型
v_CursorVar t_target_Data; --定义类型游标
--可以从目标表中获取的变量
v_MACH_NAME 项目部机具.设备名称%TYPE;           --机具名称;
v_MACH_SPEC 项目部机具.规格型号%TYPE;           --规格型号;
v_ORIGINAL_VALUE  项目部机具.原值%TYPE;         --机具原值
v_CUR_VALUE 项目部机具.净值%TYPE;               --机具净值
v_CUR_USED_DEPTID_name 项目部机具.使用单位%TYPE;--使用单位名称
v_VALUE_TYPE 项目部机具.分类%TYPE;              --机具价值分类
v_MACH_UNIT_NAME 项目部机具.单位%TYPE;          --计量单位名称
v_ID 项目部机具.序号%TYPE;                      --序号
v_COMMENTS 项目部机具.备注%TYPE;                --备注信息

v_AMORTISE_PRICE 项目部机具.十月摊销%TYPE;      --摊销单价
--需要计算或定义的变量
v_CUR_USED_DEPTID integer;      --使用部门编号
v_MACH_UNIT integer ;           --计量单位编号
v_MACH_BASE_TYPE integer := 2;  --基础设备类型
v_SELF_CODE varchar2(50) := ''; --自编号
v_CUR_STATE integer;            --当前状态

v_Value_Type_num integer := 0;  --价值分类数值表述变量
v_Record_cur_index integer;     --当前记录Id
v_HANDOVER_Counter integer := 1;--领用记录计数器
--系统调试用变量
S_Process_Counter integer := 0;
--系统异常对象
SYS_DEBUG_EXC EXCEPTION;
v_ErrorCode NUMBER;          -- 出错的代码
v_ErrorMsg  VARCHAR2(200);   -- 错误的消息显示
v_CurrentUser VARCHAR2(8);   -- 当前数据库用户
v_Information VARCHAR2(100); -- 关于错误的信息
  begin
      --读取数据
      OPEN v_CursorVar  FOR
           SELECT 设备名称,规格型号,原值,净值,使用单位,分类,单位,序号,备注,十月摊销 FROM 项目部机具 order by 序号;

      --遍历数据

      LOOP
           --从游标中提取记录值赋予制定变量
           FETCH v_CursorVar  INTO v_MACH_NAME, v_MACH_SPEC,v_ORIGINAL_VALUE,v_CUR_VALUE,v_CUR_USED_DEPTID_name,v_VALUE_TYPE,v_MACH_UNIT_NAME,v_Id,v_COMMENTS,v_AMORTISE_PRICE;
           --退出条件
           EXIT WHEN v_CursorVar %NOTFOUND;
              --工作代码
              --使用部门编号
              v_CUR_USED_DEPTID := Get_Dept_Id(v_CUR_USED_DEPTID_name);
              --计量单位编号
              v_MACH_UNIT := Get_Unit_Id(v_MACH_UNIT_NAME);
                --自编号
                v_SELF_CODE := in_Self_Code_pre||to_char(v_Id);
                --当前状态
                if v_CUR_USED_DEPTID = 1 then
                   v_CUR_STATE := 0;
                else
   &nb

sp;               v_CUR_STATE := 1;
                end if;
                -- 价值分类
                if v_VALUE_TYPE = 'A' then
                   v_Value_Type_num := 1;
                ELSIF v_VALUE_TYPE = 'B' then
                   v_Value_Type_num:= 2;
                ELSIF v_VALUE_TYPE = 'C' then
                   v_Value_Type_num := 3;
                ELSIF v_VALUE_TYPE = 'D' then
                   v_Value_Type_num := 4;
                else
                   v_Value_Type_num := 0;
                end if;               
                --执行复制到mach_basic_info表
              insert into mach_basic_info
                (
                     id,
                     MACH_NAME ,
                     MACH_TYPE ,
                     MACH_SPEC ,
                     OWNER_NAME,
                     ORIGINAL_VALUE  ,
                     CUR_VALUE,CUR_STATE ,
                     CUR_USED_DEPTID ,
                     VALUE_TYPE ,
                   MACH_UNIT ,
                     MACH_UNIT_NAME ,
                     MACH_BASE_TYPE,
                     comments,
                     self_code
                 )
              values
                (
                     seq_mach_basic_info.nextval,
                     v_MACH_NAME ,
                     in_math_type ,
                     v_MACH_SPEC ,
                     in_OWNER_NAME ,
                     CAST(v_ORIGINAL_VALUE AS number(22,4))  ,
                     CAST(v_CUR_VALUE AS number(22,4)),
                     v_CUR_STATE ,v_CUR_USED_DEPTID ,
                     v_Value_Type_num ,
                   v_MACH_UNIT ,
                     v_MACH_UNIT_NAME ,
                 &

nbsp;   v_MACH_BASE_TYPE,
                     v_COMMENTS,
                     v_SELF_CODE
                );
                --记录当前记录id
                select max(id) into v_Record_cur_index from mach_basic_info;
                --插入记录到MACH_AMOR_ATTACH_INFO表:摊销价格表
                insert into MACH_AMOR_ATTACH_INFO
                (
                    id,
                    MACH_ID,
                    AMORTISE_PRICE,
                    COMMENTS
                )
                values
                (
                     seq_MACH_AMOR_ATTACH_INFO.Nextval,
                     v_Record_cur_index,
                     cast( v_AMORTISE_PRICE AS number(22,6)),
                     '2006-11-18,程序自动导入,凭据:10月摊销金额'
                 );
                --插入记录到MACH_HANDOVER_RECORD表:领用记录表
                 if v_CUR_STATE = 1 then
                     insert into MACH_HANDOVER_RECORD
                     (
                         id,
                         MACH_ID,             --设备序号
                         ORDER_CODE,          --交接单号
                         HANDOVER_DATE,       --交接日期
                         HANDOVER_SPAN_CODE,  --交接日期所在财务月
                         FROM_DEPTID,         --移交单位
                         TO_DEPTID,           --接收单位
                         HANDOVER_STATE,      --交接单状态(枚举 0:未交接 1:已交接 2:已退库)
                         AMORTISE_MONEY,      --摊销单价(若为空自动从台帐中读取)
                         COMMENTS
                      )
                      values
                      (
                         seq_MACH_HANDOVER_RECORD.Nextval,
                         v_Record_cur_index,
                    &nbs

p;    '领_2006_10_'||to_char(v_HANDOVER_Counter),
                         to_date('2006-10-01','yyyy-mm-dd'),
                         '200610C',
                         1,
                         v_CUR_USED_DEPTID,
                         1,
                         cast( v_AMORTISE_PRICE AS number(22,6)),
                         '2006-11-18,程序自动导入,凭据:10月摊销金额'
                      );
                      --计数器加1
                      v_HANDOVER_Counter := v_HANDOVER_Counter + 1;
                end if;
                --输出信息
                dbms_output.put_line('当前完成:'|| v_MACH_NAME  );
                S_Process_Counter := S_Process_Counter + 1;
                dbms_output.put_line('累计完成:' || to_char(S_Process_Counter));
                --调试异常
                /*
                if S_Process_Counter = 2 then
                   Raise SYS_DEBUG_EXC;
                end if;
                */
      END LOOP;
      --关闭游标
      CLOSE v_CursorVar ;
      --提交事务
      commit;
             --异常处理
             exception
               --自定义异常
               when SYS_DEBUG_EXC then
                   --关闭游标
                   CLOSE v_CursorVar ;
                   dbms_output.put_line('异常调试,自动回滚');
                   --回滚事务
                   rollback;
               --其他异常
               when others then
                    --关闭游标
                   CLOSE v_CursorVar ;
                   v_ErrorCode := SQLCODE;
                   v_ErrorMsg := SQLERRM;
                   v_CurrentUser := USER;
                   v_Information := '遇到了错误 ' || TO_CHAR(SYSDATE) || ' 数据库用户 ' || v_CurrentUser;
                   dbms_output.put_line('执行错误,自动回滚');
                   dbms_output.put_line('详细信息:'||'错误代码:'||v_ErrorCode||',错误消息:'||v_ErrorMsg||',日志信息:'||v_Information);
                   --回滚事务
                   rollback;
end sub_mach_trans;

--存储过程调用代码
set serverout on  --开启系统输出
execute sub_mach_trans(334 ,'自购' ,'项目部-');

 
--历史镜像
select max(id) from mach_basic_info t
--检查结果
select * from mach_basic_info where id >3217

select * from dict_depts where dept_name  like '%物资部%'
--id is 9
select * from dict_depts where dept_name  like '%水工厂%'
--id is 2418
select * from mater_stock_dict where name like '%钢材%'
--id is 885
select * from mater_stock_dict where name like '%水工厂%'
--id is 1226

select * from mater_out_stock_base where receive_dept_id = 2418 and out_stock_id = 885 and >select * from mater_out_stock_detail where base_id =  23231
select * from mater_in_stock_base where in_stock_id = 1226
select * from mater_in_stock_detail where base_id = 14142

--辅助功能函数
create or replace function GetSubStorageBillNo
(
 in_SubStorageBillNoPre in varchar2,--二级入库单编号前缀,如'工矿-','电-','办公-'等
 in_BuyType in integer,----物资购买类型(1:统供;2:自购)
 in_SubStorageId in integer,--二级库ID号
 in_yearCode in varchar,--当前记录所在财务年
 in_monthCode in varchar--当前记录所在财务月
)
return varchar
as
v_billNo varchar2(50);
v_CurBillNo varchar2(50);
v_returnBillNo varchar2(50);
v_buyTypeChar varchar2(2);
v_Increment_id integer;
v_Query_No varchar2(50);
v_RecordCounter integer;
begin
         if in_BuyType = 1 then
            v_buyTypeChar := '统';
         else
            v_buyTypeChar := '自';
         end if;
         --Demo: 水暖-自2006-11105
         v_billNo := in_SubStorageBillNoPre || v_buyTypeChar || to_char(in_yearCode) || '-' || to_char(in_monthCode );        
         v_Query_No := v_billNo || '%';
         v_Query_No := '''' || v_Query_No || '''';
        

ment_id integer;
v_Query_No varchar2(50);
v_RecordCounter integer;
begin
         if in_BuyType = 1 then
            v_buyTypeChar := '统';
         else
            v_buyTypeChar := '自';
         end if;
         --Demo: 水暖-自2006-11105
         v_billNo := in_SubStorageBillNoPre || v_buyTypeChar || to_char(in_yearCode) || '-' || to_char(in_monthCode );        
         v_Query_No := v_billNo || '%';
         v_Query_No := '''' || v_Query_No || '''';
        

  评论这张
 
阅读(224)| 评论(0)

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018