定义总是很抽象。存储过程其实就是能完成一定操作的一组SQL语句，只不过这组语句是放在数据库中的(这里我们只谈SQL Server)。如果我们通过创建存储过程以及在ASP中调用存储过程，就可以避免将SQL语句同ASP代码混杂在一起。这样做的好处至少有三个：
- 第一、大大提高效率。存储过程本身的执行速度非常快，而且，调用存储过程可以大大减少同数据库的交互次数。
- 第二、提高安全性。假如将SQL语句混合在ASP代码中，一旦代码失密，同时也就意味着库结构失密。
- 第三、有利于SQL语句的重用。
oracle 存储过程的基本语法
1.基本结构
CREATE OR REPLACE PROCEDURE 存储过程名字
(
参数1 IN NUMBER,
参数2 IN NUMBER
) IS
变量1 INTEGER :=0;
变量2 DATE;
BEGIN
END 存储过程名字
2.SELECT INTO STATEMENT
将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条
记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND)
例子：
BEGIN
SELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx;
EXCEPTION
WHEN NO_DATA_FOUND THEN
xxxx;
END;
...
3.IF 判断
IF V_TEST=1 THEN
BEGIN
do something
END;
END IF;
4.while 循环
WHILE V_TEST=1 LOOP
BEGIN
XXXX
END;
END LOOP;
5.变量赋值
V_TEST := 123;
6.用for in 使用cursor
...
IS
CURSOR cur IS SELECT * FROM xxx;
BEGIN
FOR cur_result in cur LOOP
BEGIN
V_SUM :=cur_result.列名1+cur_result.列名2
END;
END LOOP;
END;
7.带参数的cursor
CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;
OPEN C_USER(变量值);
LOOP
FETCH C_USER INTO V_NAME;
EXIT FETCH C_USER%NOTFOUND;
do something
END LOOP;
CLOSE C_USER;
8.用pl/sql developer debug
连接数据库后建立一个Test WINDOW
在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试
关于oracle存储过程的若干问题备忘
1.在oracle中，数据表别名不能加as，如：select a.appname from appinfo a;-- 正确
select a.appname from appinfo as a;-- 错误
也许，是怕和oracle中的存储过程中的关键字as冲突的问题吧
2.在存储过程中，select某一字段时，后面必须紧跟into，如果select整个记录，利用游标的话就另当别论了。
select af.keynode into kn from APPFOUNDATION af where af.appid=aid and af.foundationid=fid;-- 有into，正确编译
select af.keynode from APPFOUNDATION af where af.appid=aid and af.foundationid=fid;-- 没有into，编译报错，提示：Compilation
Error: PLS-00428: an INTO clause is expected in this SELECT statement
3.在利用select...into...语法时，必须先确保数据库中有该条记录，否则会报出"no data found"异常。
可以在该语法之前，先利用select count(*) from 查看数据库中是否存在该记录，如果存在，再利用select...into...
4.在存储过程中，别名不能和字段名称相同，否则虽然编译可以通过，但在运行阶段会报错
select keynode into kn from APPFOUNDATION where appid=aid and foundationid=fid;-- 正确运行
select af.keynode into kn from APPFOUNDATION af where af.appid=appid and af.foundationid=foundationid;-- 运行阶段报错，提示
ORA-01422:exact fetchreturns more than requested numberof rows
假设有一个表A，定义如下：
createtable A(
id varchar2(50) primarykeynotnull ,
vcount number(8) notnull ,
bid varchar2(50) notnull-- 外键
); 如果在存储过程中，使用如下语句：
select sum(vcount) into fcount from A where bid='xxxxxx'; 如果A表中不存在bid="xxxxxx"的记录，则fcount=null(即使fcount定义时设置了默认值，如：fcount number(8):=0依然无效，fcount还是会变成null)，这样以后使用fcount时就可能有问题，所以在这里最好先判断一下：
if fcount isnull then
fcount:=0 ;
end if; 这样就一切ok了。
6.Hibernate调用oracle存储过程
this .pnumberManager.getHibernateTemplate().execute(
new HibernateCallback() ...
{public Object doInHibernate(Session session)
throws HibernateException, SQLException ...{CallableStatement cs = session
.connection()
.prepareCall("{call modifyapppnumber_remain(?)}");
cs.setString(1, foundationid);
cs.execute();
returnnull;
}
} );
create or replace procedure ET(p1 in number,p2 in number , m out number) is
begin
--sql_str := 'insert into TABLE1(LKEY,TKBN) values(111,"758")' ;
-- EXECUTE IMMEDIATE sql_str;
DECLARE
v_title varchar(100);
l_count number ;
--定义游标
l_table1 WL.TABLE1%rowtype;
CURSOR cur_table1(vID number) is
SELECT * FROM WL.TABLE1 where table1.lkey = vID;
BEGIN
--打开游标方法一
/*
OPEN cur_table1(1) ;
--fetch cur_table1 into l_table1 ;
while cur_table1% found loop
l_count := l_count +1 ;
--比较
INSERT INTO TABLE2(ID,TXT) VALUES (l_table1.LKEY, l_table1.TKNB);
commit;
fetch cur_table1 into l_table1 ;
end loop;
*/
-- vs_ym_sn_beg :=
TO_CHAR(ADD_MONTHS(TO_DATE(vs_ym_beg,'yyyymm'), -12),'yyyymm');
FOR rec IN cur_table1(1) LOOP
INSERT INTO TABLE2(ID,TXT,DT)
VALUES (rec.LKEY, rec.TKNB,to_char(SYSDATE,'yymmdd'));
commit;
l_count := l_count +1 ;
dbms_output.put_line('插入记录数:'||l_count);
END LOOP;
CLOSE cur_table1;
--dbms_sql.close_cursor(cur_table1); --关闭游标;
END;
EXCEPTION
WHEN OTHERS THEN
BEGIN
dbms_output.put_line(SQLERRM);
rollback;
END;
end ET;
/
| 字符函数： 转换函数： 分组函数： 日期函数： > |
4、asp调用orcle存储过程（过程中含有输出参数）
建立表：
SQL> create table CD_BILL_LOG
2 (
3 BILL_ID NUMBER,
4 DEAL_EMP_ID NUMBER,
5 REACH_DATE DATE,
6 DONE_DATE DATE,
7 BILL_STATUS VARCHAR2(10),
8 DEAL_DEPT_ID NUMBER,
9 SIGN NUMBER,
10 REMARK VARCHAR2(50)
11 )
12 ;
建立过程：
SQL> create or replace procedure
2 cd_update_log(inputremark varchar2,msg out float)
3 as
4 begin
5
6 update cd_bill_log a
7 set a.remark =inputremark --to_char(sysdate,'mi:ss')
8 where a.bill_id=45;
9 msg:=1;
10 commit;
11 end;
调用过程：
<%@ LANGUAGE="Javascript"%>
<%
var conn = Server.CreateObject("ADODB.Connection");
conn.Open("sczbo","sczb","sczb");
var sp_common= Server.CreateObject("ADODB.Command");
var rs = Server.CreateObject("ADODB.Recordset");
conn.CursorLocation =3;
sp_common.CommandText = "cd_update_log";
sp_common.CommandType = 4;
var para1=sp_common.CreateParameter("inputremark", 129,1,50,"联通");
sp_common.Parameters.Append(para1);
var para2=sp_common.CreateParameter("msg", 3, 2);
sp_common.Parameters.Append(para2);
sp_common.activeConnection=conn;
sp_common.execute;
var msg=sp_common("msg");
Response.write( msg);
Response.end;
%>
5、ORACLE 存储过程实战
--定义获取部门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 NAME = in_unit_name;
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
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 ,
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,
'领_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 id = 23231
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 || '''';
--获取同类单据最大编号
execute immediate 'select t.bill_no from mater_in_stock_base t where t.in_stock_id = '|| to_char(in_SubStorageId) ||' and t.bill_no like '||v_Query_No ||'and t.bill_no is not null and rownum = 1 order by t.create_time desc ' into v_CurBillNo;
if v_CurBillNo = null then
--首张单据
v_returnBillNo := in_SubStorageBillNoPre || v_buyTypeChar || to_char(in_yearCode) || '-' || to_char(in_monthCode) || '1';
else
dbms_output.put_line('the Sql values is:'||'select max(cast(replace(t.bill_no,'''||v_billNo||''','''')as number) ) from mater_in_stock_base t where t.in_stock_id = '|| to_char(in_SubStorageId) ||' and t.bill_no like '||v_Query_No ||'');
execute immediate 'select max(cast(replace(t.bill_no,'''||v_billNo||''','''')as number) ) from mater_in_stock_base t where t.in_stock_id = '|| to_char(in_SubStorageId) ||' and t.bill_no like '||v_Query_No ||'' into v_CurBillNo;
dbms_output.put_line('the max v_CurBillNo values is:'||v_CurBillNo);
--钢-自2006-071
--重新组合最终单据编号
v_returnBillNo := v_billNo || to_char(cast( v_CurBillNo as number) + 1);
dbms_output.put_line('after rebuild v_returnBillNo values is:'||v_returnBillNo);
end if;
dbms_output.put_line('v_returnBillNo values is:'||v_returnBillNo);
return v_returnBillNo;
Exception
when no_data_found then
--首张单据
v_returnBillNo := in_SubStorageBillNoPre || v_buyTypeChar || to_char(in_yearCode) || '-' || to_char(in_monthCode) || '1';
--dbms_output.put_line('v_returnBillNo values is:'||v_returnBillNo);
dbms_output.put_line('错误内容:' || SQLERRM);
return v_returnBillNo;
end GetSubStorageBillNo;
--存储过程主体
/*
自动根据大库出库单形成二级库入库单,适用于某部门一直在大库中领料但最近才开启二级仓库业务
适用时请注意修改OPEN v_Base_CursorVar FOR部分
*/
create or replace procedure AutoTransSubStorage
(
in_MainStorageId in integer, --大库ID号
in_SubStorageId in integer, --二级库ID号
in_SubDeptId in integer,--二级库部门ID号
in_MainStorageMgrDeptId in integer ,--大库管理员所在部门ID号
in_MainStorageMgrUserId in varchar2, --大库管理员用户ID
in_SubStorageBillNoPre in varchar2--二级入库单编号前缀,如'工矿-','电-','办公-'等
)
is
type t_target_Data IS REF CURSOR; --目标数据类型
v_Base_CursorVar t_target_Data; --定义类型游标
v_Detail_CursorVar t_target_Data; --定义类型游标
--可以从目标表中获取的变量
v_m_o_s_b_id mater_out_stock_base.id%TYPE; --序列号
v_m_o_s_b_bill_no mater_out_stock_base.bill_no%TYPE; --单据编码
v_m_o_s_b_pact_code mater_out_stock_base.pact_code%TYPE; --合同编码
v_m_o_s_b_buy_type mater_out_stock_base.buy_type%TYPE; --物资购买类型(1：统供；2：自购)
v_m_o_s_b_out_stock_id mater_out_stock_base.out_stock_id%TYPE;--出库仓库编号
v_m_o_s_b_receive_dept_id mater_out_stock_base.receive_dept_id%TYPE;--领料部门编号
v_m_o_s_b_bill_type mater_out_stock_base.bill_type%TYPE;--单据类型(1：调拨单；2：领料单；3：价拨单)
v_m_o_s_b_action_type mater_out_stock_base.action_type%TYPE;--单据操作类型(1：一般单据；2：冲红单据；3 ：库存损耗冲帐单据)
v_m_o_s_b_produce_state mater_out_stock_base.produce_state%TYPE;--生成出库记录状态(0：未生成；1：已经生成)
v_m_o_s_b_month_code mater_out_stock_base.MONTH_CODE%TYPE;
v_m_o_s_b_month_start_date mater_out_stock_base.month_start_date%TYPE;
v_m_o_s_b_month_end_date mater_out_stock_base.month_end_date%TYPE;
v_m_o_s_b_out_date mater_out_stock_base.out_date%TYPE;--出库日期
v_m_o_s_b_bill_date mater_out_stock_base.bill_date%TYPE;--制单日期
v_m_o_s_b_audit_state mater_out_stock_base.audit_state%TYPE;--单据审核状态(0：编辑状态；1：审核通过；2 ：审核中)
v_m_o_s_b_audit_time mater_out_stock_base.audit_time%TYPE;--单据审核时间
v_m_o_s_b_wbs_parent_id mater_out_stock_base.wbs_parent_id%TYPE;
v_m_o_s_b_wbs_child_id mater_out_stock_base.wbs_child_id%TYPE;
v_m_o_s_b_out_man mater_out_stock_base.out_man%TYPE;
v_m_o_s_b_record_man mater_out_stock_base.record_man%TYPE;--制单人员姓名
v_m_o_s_b_create_userid mater_out_stock_base.create_userid%TYPE;--创建用户ID
v_m_o_s_b_create_deptid mater_out_stock_base.create_deptid%TYPE;--创建用户所属部门编号
v_m_o_s_b_create_time mater_out_stock_base.create_time%TYPE;--创建时间
v_m_o_s_b_is_modify_dept_stock mater_out_stock_base.is_modify_dept_stock%TYPE;--是否更新领料部门库存数据(针对作业队领料到旗下仓库)
v_m_o_s_b_below_pact_code mater_out_stock_base.below_pact_code%TYPE;--领料部门对应的分包合同编码
v_m_o_s_b_sign_state mater_out_stock_base.sign_state%TYPE;--单据签字状态。
v_m_o_s_b_is_work_dept_bill mater_out_stock_base.is_work_dept_bill%TYPE;--是否是作业队单据
v_m_o_s_d_id mater_out_stock_detail.id%TYPE ;
v_m_o_s_d_base_id mater_out_stock_detail.base_id%TYPE; --出库单编号
v_m_o_s_d_batch_number mater_out_stock_detail.batch_number%TYPE;--物资系统批号
v_m_o_s_d_mater_id mater_out_stock_detail.mater_id%TYPE;--物资编号
v_m_o_s_d_out_amount mater_out_stock_detail.out_amount%TYPE;--出库数量
v_m_o_s_d_out_price mater_out_stock_detail.out_price%TYPE;--出库价格
v_m_o_s_d_out_money mater_out_stock_detail.out_money%TYPE;--出库金额
v_m_o_s_d_plan_price mater_out_stock_detail.plan_price%TYPE;--计划价格
v_m_o_s_d_plan_money mater_out_stock_detail.plan_money%TYPE;--计划金额
v_m_o_s_d_out_stock_price mater_out_stock_detail.out_stock_price%TYPE;--库存价格
v_m_o_s_d_out_stock_money mater_out_stock_detail.out_stock_money%TYPE;--库存金额
v_m_o_s_d_before_amount mater_out_stock_detail.BEFORE_AUDIT_AMOUNT%TYPE;--审核前库存数量
v_m_o_s_d_befoue_audit_money mater_out_stock_detail.BEFORE_AUDIT_MONEY%TYPE;--审核前库存金额
v_m_o_s_d_after_audit_amount mater_out_stock_detail.after_audit_amount%TYPE;--审核后库存数量
v_m_o_s_d_after_audit_money mater_out_stock_detail.after_audit_money%TYPE;--审核后库存金额
v_m_o_s_d_commments mater_out_stock_detail.COMMENTS%TYPE;
v_m_o_s_d_approve_amount mater_out_stock_detail.approve_amount%TYPE;--审批数量
v_m_o_s_d_request_amount mater_out_stock_detail.request_amount%TYPE;--请领数量
v_m_o_s_d_receive_price mater_out_stock_detail.receive_price%TYPE;--领料价格
v_m_o_s_d_receive_money mater_out_stock_detail.receive_money%TYPE;--领料金额
--需要计算或定义的变量
v_Record_cur_index integer; --当前记录Id
--系统调试用变量
S_Process_Counter integer := 0;
--系统异常对象
SYS_DEBUG_EXC EXCEPTION;
begin
--打开 mater_out_stock_base
OPEN v_Base_CursorVar FOR
select id,bill_no,pact_code,buy_type,out_stock_id,receive_dept_id,bill_type,
action_type,produce_state,month_code,month_start_date,month_end_date,out_date,
bill_date,audit_state,audit_time,wbs_parent_id,wbs_child_id,out_man,record_man,
create_userid,create_deptid,create_time,is_modify_dept_stock,below_pact_code,
sign_state,is_work_dept_bill
from mater_out_stock_base where receive_dept_id = in_SubDeptId and out_stock_id = in_MainStorageId
and id not in(23231,23665,24074); --排除部分已自动生成的记录;
--遍历数据
LOOP
--从游标中提取记录值赋予指定变量
FETCH v_Base_CursorVar INTO v_m_o_s_b_id,v_m_o_s_b_bill_no,v_m_o_s_b_pact_code,
v_m_o_s_b_buy_type,v_m_o_s_b_out_stock_id,v_m_o_s_b_receive_dept_id,
v_m_o_s_b_bill_type,v_m_o_s_b_action_type,v_m_o_s_b_produce_state,
v_m_o_s_b_month_code,v_m_o_s_b_month_start_date,v_m_o_s_b_month_end_date,
v_m_o_s_b_out_date,v_m_o_s_b_bill_date,v_m_o_s_b_audit_state,
v_m_o_s_b_audit_time,v_m_o_s_b_wbs_parent_id,v_m_o_s_b_wbs_child_id,
v_m_o_s_b_out_man,v_m_o_s_b_record_man,v_m_o_s_b_create_userid,v_m_o_s_b_create_deptid,
v_m_o_s_b_create_time,v_m_o_s_b_is_modify_dept_stock,v_m_o_s_b_below_pact_code,
v_m_o_s_b_sign_state,v_m_o_s_b_is_work_dept_bill;
--退出条件
EXIT WHEN v_Base_CursorVar %NOTFOUND;
--插入mater_in_stock_base
insert into mater_in_stock_base
(
id,
bill_no, --单据编码
pact_code,--合同编码
buy_type,--物资购买类型(1：统供；2：自购)
out_stock_id,--调拨出库仓库编号
in_stock_id,--验收入库仓库编号
bill_type,--单据类型(1：统供物资入库单；2：自购物资入库单；3：调拨验收单)
produce_state,--生成进库记录状态(0：未生成；1：已生成)
month_code,
month_start_date,
month_end_date,
bill_date,--制单日期
check_date,--验收日期
AUDIT_STATE,--单据入库审核状态(0：编辑状态，未审核；1：审核通过)
comments,
create_userid,--创建用户ID
create_deptid,--创建用户所属部门编号
create_time,--创建时间
is_work_dept_bill,--是否是作业队单据(1：是，0：否)
out_bill_id --如果是自动生成的,存储出库单ID
)
values
(
seq_mater_in_stock_base.nextval,
GetSubStorageBillNo(-- 调用自动生成单据编码函数
in_SubStorageBillNoPre,
v_m_o_s_b_buy_type,
in_SubStorageId,
SUBSTRB(v_m_o_s_b_month_code,1,4),
SUBSTRB(v_m_o_s_b_month_code,5,2)
),
v_m_o_s_b_pact_code,
v_m_o_s_b_buy_type,
in_MainStorageId,
in_SubStorageId,
v_m_o_s_b_bill_type,
1,
v_m_o_s_b_month_code,
v_m_o_s_b_month_start_date,
v_m_o_s_b_month_end_date,
v_m_o_s_b_out_date, --制单日期与大库出库日期同步
v_m_o_s_b_out_date, --验收日期与大库出库日期同步
0,--默认未审核入库
'本单据由出库单' || '‘' || v_m_o_s_b_bill_no || '’' || '自动生成',
in_MainStorageMgrUserId,
in_MainStorageMgrDeptId ,
sysdate,--程序处理时刻
1,--厂队单据
v_m_o_s_b_id --对应入库单编号
);
--记录当前记录id
select max(id) into v_Record_cur_index from mater_in_stock_base;
--嵌套游标
OPEN v_Detail_CursorVar FOR
select id,base_id,batch_number,mater_id,out_amount,out_price,out_money,plan_price,plan_money,
out_stock_price,out_stock_money,BEFORE_AUDIT_AMOUNT,BEFORE_AUDIT_MONEY,after_audit_amount,after_audit_money,
COMMENTS,approve_amount,request_amount,receive_price,receive_money from mater_out_stock_detail
where base_id = v_m_o_s_b_id ;
LOOP
--遍历数据
FETCH v_Detail_CursorVar INTO v_m_o_s_d_id ,v_m_o_s_d_base_id,
v_m_o_s_d_batch_number ,v_m_o_s_d_mater_id ,v_m_o_s_d_out_amount,
v_m_o_s_d_out_price,v_m_o_s_d_out_money,v_m_o_s_d_plan_price,
v_m_o_s_d_plan_money,v_m_o_s_d_out_stock_price,
v_m_o_s_d_out_stock_money,v_m_o_s_d_before_amount ,
v_m_o_s_d_befoue_audit_money,v_m_o_s_d_after_audit_amount,
v_m_o_s_d_after_audit_money ,v_m_o_s_d_commments,
v_m_o_s_d_approve_amount,v_m_o_s_d_request_amount,
v_m_o_s_d_receive_price ,v_m_o_s_d_receive_money;
--退出条件
EXIT WHEN v_Detail_CursorVar %NOTFOUND;
--插入mater_in_stock_detail
insert into mater_in_stock_detail
(
id,
base_id,--入库单编号
batch_number,--系统批号,由系统自动生成
mater_id,--物资编号
purchase_amount,--进货数量
purchase_price,--进货价格
purchase_money,--进货金额
check_amount,--验收数量
check_price,--验收价格
check_money,--验收金额
plan_price,--计划价格
plan_money,--计划金额
in_stock_price,--入库库存价格
in_stock_money,--入库库存金额
should_amount--应收数量
)
values
(
seq_mater_in_stock_detail.nextval,
v_Record_cur_index,
v_m_o_s_d_batch_number,
v_m_o_s_d_mater_id,
v_m_o_s_d_approve_amount,
v_m_o_s_d_receive_price,
v_m_o_s_d_receive_money,
v_m_o_s_d_approve_amount,
v_m_o_s_d_receive_price,
v_m_o_s_d_receive_money,
v_m_o_s_d_plan_price,
v_m_o_s_d_plan_money,
v_m_o_s_d_receive_price,
v_m_o_s_d_receive_money,
v_m_o_s_d_approve_amount
);
END LOOP;
--关闭游标
CLOSE v_Detail_CursorVar ;
--调试信息
dbms_output.put_line('当前完成:'|| v_m_o_s_b_bill_no );
S_Process_Counter := S_Process_Counter + 1;
dbms_output.put_line('累计完成:' || to_char(S_Process_Counter));
END LOOP;
--关闭游标
CLOSE v_Base_CursorVar ;
--提交事务
commit;
--异常处理
exception
--自定义异常
when SYS_DEBUG_EXC then
--关闭游标
CLOSE v_Base_CursorVar ;
dbms_output.put_line('异常调试,自动会滚');
--回滚事务
rollback;
--其他异常
when others then
--关闭游标
CLOSE v_Base_CursorVar ;
dbms_output.put_line('执行错误,自动会滚');
dbms_output.put_line('错误内容:' || SQLERRM);
--回滚事务
rollback;
end AutoTransSubStorage;
--检查处理之前
select count(* ) from mater_in_stock_base where in_stock_id = 1226
exec AutoTransSubStorage(885,1226,2418,9,'licuiping','钢-');
--检查处理之后
select count(* ) from mater_in_stock_base where in_stock_id = 1226
--检查数据
select * from mater_in_stock_base where in_stock_id = 1226
delete from mater_in_stock_base where in_stock_id = 1226
--利用临时表获取自增序列函数
create or replace function GetSeqNumber return number
is
v_num number;
begin
--动态创建临时表
select count(*) into v_num from user_tables where table_name= 'T_TEMP';
if v_num < 1 then
--创建临时表
execute immediate 'CREATE GLOBAL TEMPORARY TABLE T_TEMP (COL1 number(4)) ON COMMIT delete ROWS';
---初始记录
execute immediate 'insert into t_temp(COL1) values(1)';
end if;
--记录获取
execute immediate 'select col1 from t_temp' into v_num;
--记录修正
execute immediate 'update t_temp set col1 = col1 + 1 ';
return v_num;
end GetSeqNumber;
3168
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
