|
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
#1
|
|||
|
|||
|
PL\SQL Loop question: Beginner.
PL\SQL loop question.
1.) I need to write to a file with the following output: Date/Time Stamp: Fri Jun 24 02:32:26 2005 Segment: 06000220 U.S. & World News Pg 00001 LPN 07003A84 PgMbr 001 PgMbrID 080112F3 PgMbr 002 PgMbrID 0800424D PgMbr 003 PgMbrID 0800424E Segment: 06000220 U.S. & World News Pg 00002 LPN 070035C4 PgMbr 001 PgMbrID 080078DB PgMbr 002 PgMbrID 080078DC PgMbr 003 PgMbrID 080078DD <repeats...> 2.) However with the following PL\SQL, I am writing this output instead: Date/Time Stamp: Fri Jun 24 02:32:26 2005 Segment: 06000220 U.S. & World News Pg 00001 LPN 07003A84 PgMbr 001 PgMbrID 080112F3 Segment: 06000220 U.S. & World News Pg 00001 LPN 07003A84 PgMbr 002 PgMbrID 0800424D Segment: 06000220 U.S. & World News Pg 00001 LPN 07003A84 PgMbr 003 PgMbrID 0800424E Segment: 06000220 U.S. & World News Pg 00002 LPN 070035C4 PgMbr 001 PgMbrID 080078DB Segment: 06000220 U.S. & World News Pg 00002 LPN 070035C4 PgMbr 002 PgMbrID 080078DC Segment: 06000220 U.S. & World News Pg 00002 LPN 070035C4 PgMbr 003 PgMbrID 080078DD <repeats...> Details: Line 1: Is the Date/Time Stamp Line 2: Starts with 'Segment: 06...' Line 3: Starts with 'PgMbr 00...' and needs to be repeated. PL\SQL: Code:
RPT_DATE := TO_CHAR(SYSDATE, 'Dy Mon dd HH:MI:SS YYYY');
rptfile := UTL_FILE.FOPEN('/data/oracle/rpts',
SRC_SEGMENT || '.LNG',
'w');
FOR toprec in top LOOP
--1. Outout Date/Timestamp line
IF RUN_ONCE = 0 THEN
UTL_FILE.put_line(rptfile, 'Date/Time Stamp: ' || RPT_DATE);
RUN_ONCE := 1;
END IF;
REPEAT_SEGMENT := toprec.SEGMENT;
--2. Show line once
IF REPEAT_SEGMENT = toprec.SEGMENT THEN
REPEAT_SEGMENT := toprec.SEGMENT;
UTL_FILE.put_line(rptfile,
'Segment: ' || toprec.SEGMENT || ' ' ||
toprec.SEGMENT_DEF || ' Pg ' ||
LPAD(toprec.LPN_ORDER, 5, '0') || ' LPN ' ||
toprec.LPN);
END IF;
--3. Repeat lines
UTL_FILE.put_line(rptfile,
' PgMbr ' || LPAD(toprec.PGMBR_ORDER, 3, '0') ||
' PgMbrID ' || toprec.PAGEMBR);
END LOOP;
UTL_FILE.FCLOSE(rptfile);
I have attached the entire procedure for reference as well... I believe Number 2 or 3 is my issue, therefore any suggestions are greatly appreciate. I am new and learning PL\SQL. Thank you!
__________________
--Todd |
|
#2
|
|||
|
|||
|
Right before 2, you are setting REPEAT_SEGMENT := toprec.SEGMENT, then the very next line, you check to see if they are equal (assuming no nulls are involved, this will always be true). So you are running that section every time instead of just once.
There are easier ways to do this than your repeat logic though.... For instance, you can move the Date/Time code to before the loop starts, and then you don't have to check RUN_ONCE every time through the loop. Also, for the Segment and PgMbr sections, think about using 2 cursors... The first would get the segment information. As you loop through that, you would open the second cursor which would be the PgMbr information for the current segment. i.e. Code:
PROCEDURE proc
IS
v_curr_segment NUMBER;
CURSOR cur1
IS
select segment_id, segment_field1, segment_field2 etc.
from segments;
CURSOR cur2
IS
select PgMbr_id, PgMbr_field1, PgMbr_field2 etc.
from pgMbrs
where segment_id = v_curr_segment;
BEGIN
/* Write Date */
UTL_FILE.put_line(rptfile, 'Date/Time Stamp: ' || RPT_DATE);
/* Open Segment Cursor */
FOR rec1 IN cur1
LOOP
/* Write Segment */
UTL_FILE.put_line(rptfile, 'Segment Info');
/* Set variable to current segment */
v_curr_segment := rec1.segment_id;
/* Open PgMbr Cursor */
FOR rec2 IN cur2
LOOP
/* Write PgMbr */
UTL_FILE.put_line(rptfile, 'PgMbr Info');
END LOOP;
END LOOP;
END;
|
|
#3
|
|||
|
|||
|
The previous reply was good.
To add to that, you could try to improve the cursor statement by grouping the segment number. ie select segment, page from table group by segment, page This would simplify your utl_file output. Let the query do most of the work ! |
![]() |
| Viewing: Dev Shed Forums > Databases > Oracle Development > PL\SQL Loop question: Beginner. |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|