Oracle技术汇总手册
1、常用函数
Lpad()函数的用法：
lpad函数将左边的字符串填充一些特定的字符其语法格式如下：
lpad(string,n,[pad_string])
string：可是字符或者参数
n：字符的长度，是返回的字符串的数量，如果这个数量比原字符串的长度要短，lpad函数将会把字符串截取成从左到右的n个字符;
pad_string：是个可选参数，这个字符串是要粘贴到string的左边，如果这个参数未写，lpad函数将会在string的左边粘贴空格。
例如：
lpad('tech', 7); 将返回' tech'
lpad('tech', 2); 将返回'te'
lpad('tech', 8, '0'); 将返回'0000tech'
lpad('tech on the net', 15, 'z'); 将返回 'tech on the net'
lpad('tech on the net', 16, 'z'); 将返回 'ztech on the net'
------------------------------------
Rpad()函数的用法：
rpad函数将右边的字符串填充一些特定的字符其语法格式如下：
rpad(string,n,[pad_string])
string：可是字符或者参数
n：字符的长度，是返回的字符串的数量，如果这个数量比原字符串的长度要短，lpad函数将会把字符串截取成从左到右的n个字符;
pad_string：是个可选参数，这个字符串是要粘贴到string的右边，如果这个参数未写，lpad函数将会在string的右边粘贴空格。
例如：
rpad('tech', 7); 将返回' tech'
rpad('tech', 2); 将返回'te'
rpad('tech', 8, '0'); 将返回'tech0000'
rpad('tech on the net', 15, 'z'); 将返回 'tech on the net'
rpad('tech on the net', 16, 'z'); 将返回 'tech on the netz'
2、定义并使用游标
定义：
CREATE OR REPLACE PACKAGE query_pkg is
-- Author : lijinchang
-- Created : 2006-3-29 17:19:29
-- Purpose : ????
-- Public type declarations
type Query_cur is ref cursor;
end query_pkg;
使用：
create or replace procedure Jiekou_hpcaozuo(
v_wenjianmc in varchar2,--文件名
v_account in varchar2,--操作人
v_I_XIAOZHANGBH in integer,--销账编号
v_date in date,--操作时间
v_ip in varchar2,--计算机ip
V_I_JIEZHANGPH in integer,--结账批号
V_I_SHOUFEIPH in integer,--收费批号
v_bankid in varchar2,--总行编号
semu_cur out query_pkg.Query_cur
)
is
V_ID integer;
c_list query_pkg.Query_cur;
v_count integer:=0;
v_I_FEEID YW_WENJIANSJ_YWMX.i_Feeid%type;--feeid；
v_s_cid YW_WENJIANSJ_YWMX.s_Cid%type;--销根号
v_I_Y YW_WENJIANSJ_YWMX.i_y%type;--账务年
v_i_m YW_WENJIANSJ_YWMX.i_m%type;--账务月
v_i_cc YW_WENJIANSJ_YWMX.i_cc%type;--抄次
v_I_KAIZHANGSL YW_WENJIANSJ_YWMX.i_Kaizhangsl%type;--开账水量
v_n_je YW_WENJIANSJ_YWMX.n_Je%type;--金额
v_N_LINGTOU YW_WENJIANSJ_YWMX.N_LINGTOU%type;--零头；
v_N_KAIZHANGJE YW_WENJIANSJ_YWMX.N_KAIZHANGJE%type;--开账金额；
v_I_KAIHUYH YW_WENJIANSJ_YWMX.I_KAIHUYH%type;--开户银行
v_S_KAIHUHM YW_WENJIANSJ_YWMX.S_KAIHUHM%type;--开户户名
v_S_YINHANGZH YW_WENJIANSJ_YWMX.S_YINHANGZH%type;--银行帐户
v_I_HUAZHANGBH YW_WENJIANSJ.I_HUAZHANGBH%type;--划账编号
v_I_YINHANGBH YW_WENJIANSJ.i_Yinhangbh%type;--银（总）行编号
v_i_RUZHANG integer;--入账类型
v_i_zhangwuny integer;--账务年月
v_s_st kg_biaokaxx.s_st%type;--站点
totoalbs integer;--总笔数
totoalje SF_SHOUFEIHZ.n_Shishouje%type;--总金额
totoalzcbs integer;--正常笔数
totoalzcje SF_SHOUFEIHZ.n_Zhengchangje%type;--正常金额
totoalcbbs integer;--重笔笔数
totoalcbje SF_SHOUFEIHZ.n_Chongbije%type;--重笔金额
totoalynbs integer;--疑难笔数
totoalynje SF_SHOUFEIHZ.n_Yinanje%type;--疑难金额
v_sql varchar2(2000):='';
begin
v_sql:='
select t3.*,
t4.s_st from
(SELECT
T.I_FEEID,
T.S_CID,
T.I_Y,
T.I_M,
T.I_CC,
T.I_KAIZHANGSL,
T.N_JE,
T.N_LINGTOU,
T.N_KAIZHANGJE,
T.I_KAIHUYH,
T.S_KAIHUHM,
T.S_YINHANGZH,
T1.I_HUAZHANGBH,
T1.I_YINHANGBH,
jiekou_getruzhangzt(T.I_Y,T.I_M,T.I_FEEID,T.N_JE) AS I_RUZHANG,
to_number(to_char(T.I_Y)||case length(to_char(T.I_M)) when 1
then ''0''||to_char(T.I_M) else to_char(T.I_M) end) as I_ZHANGWUNY
FROM YW_WENJIANSJ_YWMX T,
YW_WENJIANSJ T1
WHERE T1.I_WENJIANBH=T.I_WENJIANBH
AND T1.S_WENJIANM='''||v_wenjianmc||''') t3
left join
kg_biaokaxx t4 on t3.s_cid=t4.s_cid'
;
open c_list for v_sql;
if c_list%notfound then
v_count:=0;
return;
else
v_count:=1;
loop
fetch c_list into v_I_FEEID,v_s_cid,
v_I_Y,v_i_m,v_i_cc,
v_I_KAIZHANGSL,v_n_je,
v_N_LINGTOU,v_N_KAIZHANGJE,
v_I_KAIHUYH,v_S_KAIHUHM,v_S_YINHANGZH,
v_I_HUAZHANGBH,v_I_YINHANGBH,v_i_RUZHANG,
v_i_zhangwuny
;
V_ID :=GSELNEXTSF_SHOUFEIMXID();
INSERT INTO SF_SHOUFEIMX (
SF_SHOUFEIMX.ID,
SF_SHOUFEIMX.S_PCID,
SF_SHOUFEIMX.I_JIEZHANGPH,
SF_SHOUFEIMX.I_SHOUFEIPH,
SF_SHOUFEIMX.I_ZHANGWUNY,
SF_SHOUFEIMX.S_ST,
SF_SHOUFEIMX.I_FEEID,
SF_SHOUFEIMX.S_CID,
SF_SHOUFEIMX.I_CHAOBIAON,
SF_SHOUFEIMX.I_CHAOBIAOY,
SF_SHOUFEIMX.I_CC,
SF_SHOUFEIMX.N_JE,
SF_SHOUFEIMX.N_YINGSHOUZNJ,
SF_SHOUFEIMX.N_SHISHOUZNJ,
SF_SHOUFEIMX.I_JINCHU,
SF_SHOUFEIMX.I_SHOUFEILX,
SF_SHOUFEIMX.I_RUZHANG,
SF_SHOUFEIMX.I_TUIKUANLX,
SF_SHOUFEIMX.S_SHOUFEIYBH,
SF_SHOUFEIMX.DL_SHOUFEIRQ,
SF_SHOUFEIMX.D_SHOUFEISJ,
SF_SHOUFEIMX.I_XIAOZHANGBH,
SF_SHOUFEIMX.I_DAYINCS,
SF_SHOUFEIMX.S_ZHANGDANTXM,
SF_SHOUFEIMX.S_BEIZHU,
SF_SHOUFEIMX.S_BEIZHU1,
SF_SHOUFEIMX.S_CAOZUOR,
SF_SHOUFEIMX.D_CAOZUOSJ,
SF_SHOUFEIMX.I_JLZT
) VALUES (
V_ID,
V_ip,
V_I_JIEZHANGPH,
V_I_SHOUFEIPH,
V_I_ZHANGWUNY,
V_S_ST,
V_I_FEEID,
V_S_CID,
V_I_Y,
V_I_M,
V_I_CC,
V_N_JE,
0,
0,
1,
0,
V_I_RUZHANG,
0,
v_account,
to_number(to_char(v_date,'yyyyMMdd')),
v_date,
V_I_XIAOZHANGBH,
0,
0,
'无',
'无',
v_account,
v_date,
0
);
totoalbs:=totoalbs+1;--总笔数
totoalje:=totoalje+v_n_je;--总金额
if v_i_RUZHANG=0 then
totoalzcbs:=totoalzcbs+1;--正常笔数
totoalzcje:=totoalzcje+v_n_je;--正常金额
--正常的做销账处理
update zw_yingyez
set zw_yingyez.I_XIAOZHANG = 3,--已销
zw_yingyez.I_JIEZHANGPH = v_I_JIEZHANGPH,--结账批号
zw_yingyez.I_SHOUFEITJ = 4,--收费途径
zw_yingyez.D_XIAOZHANGRQ = v_date,--销账日期
zw_yingyez.DL_XIAOZHANGRQ = to_number(to_char(v_date,'yyyyMMdd')),--销账日期
zw_yingyez.I_XIAOZHANGBH = v_I_XIAOZHANGBH --销账编号
where zw_yingyez.i_feeid=v_I_FEEID
;
end if;
if v_i_RUZHANG=1 then
totoalcbbs:=totoalcbbs+1;--重笔笔数
totoalcbje:=totoalcbje+v_n_je;--重笔金额
--重笔的插入到待处理表
CS_InsZW_DAICHULI
(
0,
0,
0,
0,
v_i_RUZHANG,
0,
v_account,
v_date,
'无',
V_ip,
V_I_JIEZHANGPH,
V_I_SHOUFEIPH,
V_I_ZHANGWUNY,
V_S_ST,
V_I_FEEID,
V_S_CID,
v_I_Y,
v_I_m,
V_I_CC,
V_N_JE,
0,
0,
1,
0,
V_I_RUZHANG,
0,
V_account,
to_number(to_char(v_date,'yyyyMMdd')),
v_date,
V_I_XIAOZHANGBH,
0,
'无',
'五',
'无',
v_account,
v_date,
0,
v_date,
v_account,
V_N_JE,
V_ID
);
end if;
if v_i_RUZHANG=2 then
totoalynbs:=totoalynbs+1;--疑难笔数
totoalynje:=totoalynje+v_n_je;--疑难金额
--疑难的插入到待处理表
CS_InsZW_DAICHULI
(
0,
0,
0,
0,
v_i_RUZHANG,
0,
v_account,
v_date,
'无',
V_ip,
V_I_JIEZHANGPH,
V_I_SHOUFEIPH,
V_I_ZHANGWUNY,
V_S_ST,
V_I_FEEID,
V_S_CID,
v_I_Y,
v_I_m,
V_I_CC,
V_N_JE,
0,
0,
1,
0,
V_I_RUZHANG,
0,
V_account,
to_number(to_char(v_date,'yyyyMMdd')),
v_date,
V_I_XIAOZHANGBH,
0,
'无',
'五',
'无',
v_account,
v_date,
0,
v_date,
v_account,
V_N_JE,
V_ID
);
end if;
end loop;
end if;
close c_list;
V_ID :=GSELNEXTSF_SHOUFEIXJID();
INSERT INTO SF_SHOUFEIXJ (
SF_SHOUFEIXJ.ID,
SF_SHOUFEIXJ.S_PCID,
SF_SHOUFEIXJ.I_JIEZHANGPH,
SF_SHOUFEIXJ.I_SHOUFEIPH,
SF_SHOUFEIXJ.S_ST,
SF_SHOUFEIXJ.N_XIAOJIJE,
SF_SHOUFEIXJ.S_SHUAKALSH,
SF_SHOUFEIXJ.S_HAOMA,
SF_SHOUFEIXJ.I_ZHIFULX,
SF_SHOUFEIXJ.S_SHOUFEIYBH,
SF_SHOUFEIXJ.D_SHOUFEISJ,
SF_SHOUFEIXJ.DL_SHOUFEIRQ,
SF_SHOUFEIXJ.S_BEIZHU,
SF_SHOUFEIXJ.S_BEIZHU1,
SF_SHOUFEIXJ.S_CAOZUOR,
SF_SHOUFEIXJ.D_CAOZUOSJ,
SF_SHOUFEIXJ.I_JLZT
) VALUES (
V_ID,
V_ip,
V_I_JIEZHANGPH,
V_I_SHOUFEIPH,
V_S_ST,
totoalje,
'',
'',
0,
v_account,
v_date,
to_number(to_char(v_date,'yyyyMMdd')),
'无',
'无',
v_account,
v_date,
0
);
V_ID :=GSELNEXTSF_SHOUFEIHZID();
INSERT INTO SF_SHOUFEIHZ (
SF_SHOUFEIHZ.ID,
SF_SHOUFEIHZ.S_ST,
SF_SHOUFEIHZ.S_PCID,
SF_SHOUFEIHZ.S_SHOUFEIDBH,
SF_SHOUFEIHZ.I_SHOUFEITJ,
SF_SHOUFEIHZ.I_JIEZHANGPH,
SF_SHOUFEIHZ.I_SHOUFEIJD,
SF_SHOUFEIHZ.I_YINGSHOUBS,
SF_SHOUFEIHZ.N_YINGSHOUJE,
SF_SHOUFEIHZ.I_SHISHOUBS,
SF_SHOUFEIHZ.N_SHISHOUJE,
SF_SHOUFEIHZ.I_ZHENGCHANGBS,
SF_SHOUFEIHZ.N_ZHENGCHANGJE,
SF_SHOUFEIHZ.I_CHONGBIBS,
SF_SHOUFEIHZ.N_CHONGBIJE,
SF_SHOUFEIHZ.I_YINANBS,
SF_SHOUFEIHZ.N_YINANJE,
SF_SHOUFEIHZ.I_JIADANBS,
SF_SHOUFEIHZ.N_JIADANJE,
SF_SHOUFEIHZ.I_TEZHANGBS,
SF_SHOUFEIHZ.N_TEZHANGJE,
SF_SHOUFEIHZ.I_CHAEBS,
SF_SHOUFEIHZ.N_CHAEJE,
SF_SHOUFEIHZ.I_LINGTOUSBS,
SF_SHOUFEIHZ.N_LINGTOUSJE,
SF_SHOUFEIHZ.I_TUIKUANBS,
SF_SHOUFEIHZ.N_TUIKUANJE,
SF_SHOUFEIHZ.D_JIEZHANGRQ,
SF_SHOUFEIHZ.S_SHOUFEIYBH,
SF_SHOUFEIHZ.D_XIAOZHANGRQ,
SF_SHOUFEIHZ.I_XIAOZHANGBH,
SF_SHOUFEIHZ.S_XIAOZHANGYBH,
SF_SHOUFEIHZ.S_JIEZHANGTXM,
SF_SHOUFEIHZ.S_BEIZHU,
SF_SHOUFEIHZ.S_BEIZHU1,
SF_SHOUFEIHZ.S_CAOZUOR,
SF_SHOUFEIHZ.D_CAOZUOSJ,
SF_SHOUFEIHZ.I_JLZT
) VALUES (
V_ID,
V_S_ST,
V_ip,
v_bankid,
4,
V_I_JIEZHANGPH,
3,
totoalbs,
totoalje,
totoalbs,
totoalje,
totoalzcbs,
totoalzcje,
totoalcbbs,
totoalcbje,
totoalynbs,
totoalynje,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
v_date,
v_account,
v_date,
V_I_XIAOZHANGBH,
v_account,
'',
'',
'',
v_account,
v_date,
0
);
end Jiekou_hpcaozuo;
3、自定义函数的定义
create or replace function jiekou_getruzhangzt
(
-------------------------------------
--作者：李金昌
--时间：2010-10-28
--功能：判断一笔yw_wenjiansj_ywmx记录对应的账务信息的入账状态
-------------------------------------
I_Y IN INTEGER,
I_M IN INTEGER,
I_FEEID IN INTEGER,
N_JE IN NUMBER
)
return integer
is
V_RUZHANG INTEGER;
V_SQL varchar2(4000):= '';
tablename varchar2(150):='';
v_COUNT INTEGER ;
v_recordcount integer;
v_I_M varchar2(2):='';
begin
if length(I_M)=1 then
v_I_M:='0'||I_M;
else
v_I_M:=''||I_M;
end if;
tablename:=upper('xz_yingyez'||I_Y||v_I_M);--销账历史记录表名
select Count(1) into v_count from all_all_tables a where a.owner = 'VEOLIAUSER_HIS' and a.table_name = tablename;
if v_COUNT <>0 then
V_SQL:='
select count(1) from
(
select 1 from '||tablename||'
where i_feeid='||I_FEEID||' and N_JE=
'||N_JE||'
union
select 1 from zw_yingyez t where
t.i_feeid='||I_FEEID||' and t.N_JE=
'||N_JE||' and I_xiaozhang>0
)'
;
else
V_SQL:='
select count(1) from
(
select 1 from zw_yingyez t where
t.i_feeid='||I_FEEID||' and t.N_JE=
'||N_JE||' and I_xiaozhang>0
)'
;
end if;
execute immediate v_sql into v_recordcount;
if v_recordcount>0 then
V_RUZHANG:=1;--重笔状态
else
V_SQL:='
select count(1) from
(
select 1 from zw_yingyez t where
t.i_feeid='||I_FEEID||' and t.N_JE=
'||N_JE||' and I_xiaozhang=0
)'
;
execute immediate v_sql into v_recordcount;
if v_recordcount>0 then
V_RUZHANG:=0;--正常状态
else
V_RUZHANG:=2;--疑难帐状态
end if;
end if;
return V_RUZHANG;
end jiekou_getruzhangzt;
posted on 2010-11-09 11:07 lijinchang 阅读(360) 评论(0) 编辑 收藏 举报
