首先到DBA里面分配用户在目录操作的权限
Codecreate or replace DIRECTORY dir as 'c:\目录名称';
grant read,write on DIRECTORY dir to conman;
CodeCREATE OR REPLACE PROCEDURE WRITE_TXT(
--公司名称
COMPANY_NAME IN VARCHAR2,
--附件名称
FILE_PATH_NAME OUT VARCHAR2,
--统计日报日期
day_date_time IN VARCHAR2 default to_char(sysdate,'yyyy-MM-dd')
)
is
--查询符合条件的数据到文本文件，文件名称是当前的年月日
--进场日报 箱经营人
CURSOR carrier1 IS
select distinct(do.carrier) as carrier from daily_one do
where substr(do.get_in_time ,1,10)=day_date_time;
carrier1_var carrier1%ROWTYPE;
--进场日报 按照进场方式进行分组
CURSOR out_way1 IS
/*
select distinct(do.out_way) as out_way from daily_one do
where substr(do.get_in_time ,1,10)=to_char(sysdate,'yyyy-MM-dd');
*/
select carrier ,out_way,
conta_size||' '||do.CONTA_TYPE||' '||do.conta_Gbad as a111
from daily_one do
WHERE substr(do.get_in_time ,1,10)=day_date_time
group by carrier, out_way,do.conta_size , do.CONTA_TYPE,do.conta_Gbad;
out_way1_var out_way1%ROWTYPE;
--进场日报详细信息
CURSOR CURSOR_daily_one IS
/*
select rownum, CONTA_NO , substr (get_in_time ,1,10) as get_in_time,conta_Gbad ,
FS_name,LAIYUAN,out_way,picking_no,carrier,conta_size,CONTA_TYPE
from daily_one do
where substr(do.get_in_time ,1,10)=to_char(sysdate,'yyyy-MM-dd')
order by do.carrier,do.out_way;
*/
select rownum,
rpad (CONTA_NO ,16 ,' ') as CONTA_NO,
rpad (conta_Gbad,2,' ') as conta_Gbad ,
rpad (FS_name , 30,' ') as FS_name,
(
case when LAIYUAN is null then ' '
else
rpad(LAIYUAN ,10,' ') end ) as LAIYUAN,
rpad (out_way,10,' ') as out_way,
rpad (picking_no , 12 ,' ') as picking_no,
substr (get_in_time ,1,10) as get_in_time,
carrier,
conta_size,
CONTA_TYPE,
conta_size||' '||CONTA_TYPE||' '||conta_Gbad as a111
from daily_one do
where substr(do.get_in_time ,1,10)=day_date_time
order by do.carrier,do.out_way;
CURSOR_daily1_var CURSOR_daily_one%ROWTYPE;
--出场日报 箱经营人
CURSOR carrier2 IS
select distinct(do.carrier) as carrier from daily_two do
where substr(do.out_time ,1,10)=day_date_time;
carrier2_var carrier2%ROWTYPE;
--按照出场方式，出场尺寸，好坏 进行分组
CURSOR out_way2 IS
select carrier,out_way,
conta_size||' '||conta_type||' '||conta_Gbad as a111
from daily_two
where substr(out_time ,1,10)=day_date_time
group by carrier,out_way, conta_size, conta_type,conta_Gbad;
out_way2_var out_way2%ROWTYPE;
--出场日报详细信息
CURSOR CURSOR_daily_two IS
select rownum,
rpad (CONTA_NO,16 ,' ') as CONTA_NO,
rpad (conta_status,4 ,' ') as conta_status ,--箱框
substr(get_in_time,1,10) as get_in_time,
rpad (send_Place,20,' ') as send_Place,
rpad (FS_name ,30,' ') as FS_name,
rpad (picking_no,12,' ') as picking_no,
rpad (purpose_port ,20,' ') as purpose_port,
carrier ,
conta_Gbad,
out_way,
conta_size,
CONTA_TYPE
from daily_two do
where substr(do.out_time ,1,10)=day_date_time;
CURSOR_daily2_var CURSOR_daily_two%ROWTYPE;
--日报3
CURSOR CURSOR_daily_three IS
select rpad(do.inorout,3,' ') as inorout ,
rpad(do.conta_size,10,' ') as conta_size,
rpad(do.conta_type_normal,10,' ') as conta_type_normal,
rpad(do.amount,10,' ') as amount
from daily_three do
where substr(do.get_in_time ,1,10)=day_date_time;
CURSOR_daily3_var CURSOR_daily_three%ROWTYPE;
--日报４
CURSOR CURSOR_daily_four IS
select
rpad (base_daily_para,10,' ') as base_daily_para,
rpad (c20gp,6,' ') as c20gp,
rpad(c40gp,6,' ') as c40gp,
rpad(c20ot,6,' ') as c20ot,
rpad(c40ot,6,' ') as c40ot,
rpad(c40hc,6,' ') as c40hc,
rpad(c45hc,6,' ') as c45hc,
rpad(c20rf,6,' ') as c20rf,
rpad(c40rf,6,' ') as c40rf,
rpad(c40rh,6,' ') as c40rh,
rpad(c20fr,6,' ') as c20fr,
rpad(c40fr,6,' ') as c40fr,
rpad(c40ht,6,' ') as c40ht,
rpad(c45ht,6,' ') as c45ht,
rpad(c20gh,6,' ') as c20gh,
rpad(c40gh,6,' ') as c40gh,
rpad(c20hc,6,' ') as c20hc
from store_info ;
CURSOR_daily4_var CURSOR_daily_four%ROWTYPE;
--日报5
CURSOR CURSOR_daily_five IS
select
rpad(conta_no,20,' ') as conta_no
,rpad (conta_size_conta_type_normal,6,' ') as conta_size_conta_type_normal,
row_countNew
from
(
select
conta_no ,conta_size_conta_type_normal , count(conta_no) as row_countNew
from
(
select
/*
substr (conta_no,1,4)||'('||
case when
about_no is null then
'OTHER '||conta_size||conta_type_normal else about_no end
||')'
as conta_no, --箱号（LR号码）
*/
case when
about_no is null then
'OTHER ' else substr(conta_no,1,4)||'('|| about_no||')' end
as conta_no, --箱号（LR号码）
conta_size||conta_type_normal as conta_size_conta_type_normal,--箱型
/*
case when
about_no is null then
'OTHER '||conta_size||conta_type_normal else about_no end about_no,
conta_size,
conta_type_normal,
*/
get_in_time
from
(select h.conta_no,b.about_no,h.conta_size,h.conta_type_normal,h.get_in_time
from dump_conta_head h left join dump_conta_body b
on h.conta_head_seq = b.conta_head_seq and b.op_type = 'D')
WHERE substr(get_in_time ,1,10)=day_date_time
)
group by conta_no ,conta_size_conta_type_normal
);
CURSOR_daily5_var CURSOR_daily_five%ROWTYPE;
--文本文件操作
handle utl_file.file_type;
--行数
row_count_data NUMBER;
begin
--Txt 文档设置
BEGIN
FILE_PATH_NAME:='d:\Txt\'||day_date_time||'.txt';
/*
A Append Text
AB Append Byte Mode
R Read Text
RB Read Byte Mode
W Write Text
WB Write Byte Mode
*/
handle:=utl_file.fopen('DIR',FILE_PATH_NAME,'W');
END;
--进场日报
BEGIN
row_count_data:=1;
utl_file.put_line(handle,'---------------------------------------------------------------------');
utl_file.put_line(handle,COMPANY_NAME||'集装箱进场日报');
OPEN carrier1;
LOOP
FETCH carrier1 INTO carrier1_var;
EXIT WHEN carrier1%NOTFOUND;
BEGIN
--经营人
--dbms_output.put_line(carrier_var.carrier);
--utl_file.put_line(handle,CHR(10));
utl_file.put_line(handle,'箱经营人 '||carrier1_var.carrier);
utl_file.put_line(handle,'日期 '||day_date_time);
--进场日报详细信息
--进场日报表头
utl_file.put_line(handle,' 箱号 进场日期 箱况 船名航次 来源 进场方式 提单号 ');
--按照进场方式进行分组
OPEN out_way1;
LOOP
FETCH out_way1 INTO out_way1_var;
EXIT WHEN out_way1%NOTFOUND;
BEGIN
IF (carrier1_var.carrier=out_way1_var.carrier)
THEN
OPEN CURSOR_daily_one;
LOOP
FETCH CURSOR_daily_one INTO CURSOR_daily1_var;
EXIT WHEN CURSOR_daily_one%NOTFOUND;
BEGIN
--首先按照经营人进行分组 (out_way1_var.out_way=CURSOR_daily1_var.Out_Way) AND
IF
(carrier1_var.carrier=CURSOR_daily1_var.Carrier) AND
(trim(out_way1_var.out_way)=trim(CURSOR_daily1_var.Out_Way)) AND
--conta_size||' '||do.CONTA_TYPE||' '||do.conta_Gbad
--(out_way1_var.a111=(CURSOR_daily1_var.conta_size||' '||CURSOR_daily1_var.CONTA_TYPE||' '||CURSOR_daily1_var.conta_Gbad))
(out_way1_var.a111=CURSOR_daily1_var.a111)
THEN
--然后按照进场方式进行分组
--IF THEN
if (CURSOR_daily1_var.LAIYUAN is null ) then CURSOR_daily1_var.LAIYUAN:=' '; END IF;
utl_file.put_line
(handle,--to_char(CURSOR_daily1_var.Rownum)||','||
row_count_data||' '||
CURSOR_daily1_var.CONTA_NO||' '||
CURSOR_daily1_var.get_in_time||' '||
CURSOR_daily1_var.conta_Gbad||' '||
CURSOR_daily1_var.FS_name||' '||
CURSOR_daily1_var.LAIYUAN||' '||
CURSOR_daily1_var.out_way||' '||
CURSOR_daily1_var.picking_no);
row_count_data:=row_count_data+1;
--ELSE
--row_count_data:=1;
--utl_file.put_line(handle,carrier1_var.carrier||','||row_count_data||','||'*');
--EXIT;
--END IF;
--
--utl_file.put_line(handle,carrier1_var.carrier||','||row_count_data||','||'*');
--);
END IF;
END;
END LOOP;
CLOSE CURSOR_daily_one;
END IF;
--进场明细
IF row_count_data<>1 THEN
row_count_data:=row_count_data-1;
utl_file.put_line(handle,carrier1_var.carrier||' '||row_count_data||' '||'* '||out_way1_var.a111);
--utl_file.put_line(handle,CHR(10));
END IF;
row_count_data:=1;
END;
END LOOP;
CLOSE out_way1;
utl_file.put_line(handle,CHR(10));
END;
END LOOP;
CLOSE carrier1;
utl_file.put_line(handle,CHR(10));
END;
--出场日报
BEGIN
--row_count_data:=0;
row_count_data:=1;
utl_file.put_line(handle,'---------------------------------------------------------------------');
utl_file.put_line(handle,COMPANY_NAME||'集装箱出场日报');
OPEN carrier2;
LOOP
FETCH carrier2 INTO carrier2_var;
EXIT WHEN carrier2%NOTFOUND;
BEGIN
--经营人
--utl_file.put_line(handle,CHR(10));
utl_file.put_line(handle,'箱经营人 '||carrier2_var.carrier);
utl_file.put_line(handle,'日期 '||day_date_time);
utl_file.put_line(handle,' 箱号 状态 箱况 出场日期 发往地点 船名航次 提单号 目的港 出场方式');
--出场日报详细信息
--出场日报表头
--按照出场方式 ，出场尺寸，好坏 进行分组
/*
OPEN out_way2;
LOOP
FETCH out_way2 INTO out_way2_var;
EXIT WHEN out_way2%NOTFOUND;
END LOOP;
CLOSE out_way2;
*/
OPEN out_way2;
LOOP
FETCH out_way2 INTO out_way2_var;
EXIT WHEN out_way2%NOTFOUND;
IF (carrier2_var.carrier=out_way2_var.carrier)
THEN
BEGIN
--出场明细
OPEN CURSOR_daily_two;
LOOP
FETCH CURSOR_daily_two INTO CURSOR_daily2_var;
EXIT WHEN CURSOR_daily_two%NOTFOUND;
BEGIN
--人员筛选 --按照出场方式 ，出场尺寸，好坏 进行分组 (out_way2_var.out_way=CURSOR_daily2_var.Out_Way) AND
IF (carrier2_var.carrier=CURSOR_daily2_var.Carrier) AND
(trim(out_way2_var.out_way)=trim(CURSOR_daily2_var.Out_Way)) AND
(out_way2_var.a111=(CURSOR_daily2_var.Conta_Size||' '||CURSOR_daily2_var.Conta_Type||' '||CURSOR_daily2_var.conta_Gbad))
THEN
if (CURSOR_daily2_var.send_Place is null) THEN
CURSOR_daily2_var.send_Place:=' ';
END IF;
if (CURSOR_daily2_var.FS_name is null) THEN
CURSOR_daily2_var.FS_name:=' ';
END IF;
utl_file.put_line
(handle,
--to_char(CURSOR_daily2_var.Rownum)||','||
row_count_data||' '||
CURSOR_daily2_var.CONTA_NO||' '||
CURSOR_daily2_var.conta_status||','||
rpad(CURSOR_daily2_var.conta_Gbad,10,' ')||' '||
rpad(CURSOR_daily2_var.get_in_time,10,' ')||' '||
rpad(CURSOR_daily2_var.send_Place,20,' ')||' '||
rpad( CURSOR_daily2_var.FS_name,12,' ')||' '||
CURSOR_daily2_var.picking_no||' '||
rpad(CURSOR_daily2_var.purpose_port,20)||' '||
CURSOR_daily2_var.Out_Way);
row_count_data:=row_count_data+1;
END IF;
END;
END LOOP;
CLOSE CURSOR_daily_two;
IF row_count_data<>1 THEN
row_count_data:=row_count_data-1;
utl_file.put_line(handle,carrier2_var.carrier||','||row_count_data||','||'* '||out_way2_var.a111);
--utl_file.put_line(handle,CHR(10));
END IF;
row_count_data:=1;
END;
END IF;
END LOOP;
CLOSE out_way2;
utl_file.put_line(handle,CHR(10));
END;
END LOOP;
CLOSE carrier2;
utl_file.put_line(handle,CHR(10));
END;
--日报3
BEGIN
utl_file.put_line(handle,'---------------------------------------------------------------------');
OPEN CURSOR_daily_three;
LOOP
FETCH CURSOR_daily_three INTO CURSOR_daily3_var;
EXIT WHEN CURSOR_daily_three%NOTFOUND;
BEGIN
utl_file.put_line(handle,CURSOR_daily3_var.inorout||' '||
CURSOR_daily3_var.Conta_Size||' '||
CURSOR_daily3_var.Conta_Type_Normal||' '||
CURSOR_daily3_var.Amount);
END;
END LOOP;
CLOSE CURSOR_daily_three;
END;
--日报4
BEGIN
utl_file.put_line(handle,'---------------------------------------------------------------------');
utl_file.put_line(handle,' 20gp 40gp 20ot 40ot 40hc 45hc 20rf 40rf 40rh 20fr 40fr 40ht 45ht 20gh 40gh 20hc');
OPEN CURSOR_daily_four;
LOOP
FETCH CURSOR_daily_four INTO CURSOR_daily4_var;
EXIT WHEN CURSOR_daily_four%NOTFOUND;
BEGIN
--utl_file.put_line(handle,CURSOR_daily4_var)
utl_file.put_line(handle,CURSOR_daily4_var.Base_Daily_Para ||' '||
CURSOR_daily4_var.C20gp||' '||
CURSOR_daily4_var.C40gp||' '||
CURSOR_daily4_var.c20ot||' '||
CURSOR_daily4_var.c40ot||' '||
CURSOR_daily4_var.c40hc||' '||
CURSOR_daily4_var.c45hc||' '||
CURSOR_daily4_var.c20rf||' '||
CURSOR_daily4_var.c40rf||' '||
CURSOR_daily4_var.c40rh||' '||
CURSOR_daily4_var.c20fr||' '||
CURSOR_daily4_var.c40fr||' '||
CURSOR_daily4_var.c40ht||' '||
CURSOR_daily4_var.c45ht||' '||
CURSOR_daily4_var.c20gh||' '||
CURSOR_daily4_var.c40gh||' '||
CURSOR_daily4_var.c20hc
);
END;
END LOOP;
CLOSE CURSOR_daily_four;
END;
--日报5
BEGIN
utl_file.put_line(handle,'---------------------------------------------------------------------');
utl_file.put_line(handle,'箱号（LR号码） 箱型 数量');
OPEN CURSOR_daily_five;
LOOP
FETCH CURSOR_daily_five INTO CURSOR_daily5_var;
EXIT WHEN CURSOR_daily_five%NOTFOUND;
BEGIN
utl_file.put_line(handle,CURSOR_daily5_var.conta_no||' '||
CURSOR_daily5_var.Conta_Size_Conta_Type_Normal||' '||
CURSOR_daily5_var.Row_Countnew);
END;
END LOOP;
CLOSE CURSOR_daily_five;
END;
utl_file.fclose(handle);
end;
存储过程代码如下<把数据库查询到的符合条件的记录,写到文件里面,文件名是年+月+日.txt>:
在DBA下面给用户增加创建目录，删除目录的权限
