P/L SQL 处理 text 字段时让其自动分行

P/L SQL 处理 text 字段时让其自动分行
最近在做报表时（由于特殊需求，没有用任何报表工具，只用P/L SQL生成），在遇到内容很长的字段时要自动分行成段落，一个英文单词不能拆分，因此写了如下过程来处理

DECLARE
str_length_ NUMBER;
string_ VARCHAR2(2000);

TYPE string_type IS TABLE OF VARCHAR2(300)
INDEX BY BINARY_INTEGER;

sub_string_ string_type;

loop_time_ NUMBER;

start_pos_ NUMBER := 1;
end_pos_ NUMBER := 1;

BEGIN

string_　:= 'ELECT.PRESCHOOL TOYS DESIGNED TO DEVELOP MINDS INCLUDING:1)INTERACTIVE DRAWING SURFACE DISPLAYS CHILDS CREATION ON LCD SCREEN 2)TEACHES KIDS TO WRITE NUMBERS AND LETTERS STEP BY STEP 3)MAGIC SENSOR LETS KIDS SEE THEIR PICTURES ON THE COMPUTER SCREEN 4)FUN GAMES REINFORCE CORE PRESCHOOL CONCEPTS IN ENGAGING FORMAT 5)11 AMAZING MODES OF PLAY. WITH A LCD SCREEN. B/O.' ----- 这里只有一行

str_length_ := length(string_);

loop_time_ := ceil(str_length_/130);　-----分段后每行最多130个字符
dbms_output.put_line('loop_time_: '||loop_time_);
dbms_output.put_line('length_: '||str_length_);

--str_length_ := loop_time_ * 130;

FOR i IN 1..loop_time_ LOOP
IF 130*i < str_length_ THEN
end_pos_ := greatest(instr(string_,' ',130*i - str_length_,1),instr(string_,',',130*i - str_length_,1),instr(string_,'.',130*i - str_length_,1)); ---- 找到离行尾最近的空格，逗号，点等可分行的字符作截断点
ELSE
end_pos_ := str_length_;
END IF;
--dbms_output.put_line(start_pos_);
--dbms_output.put_line(end_pos_);
sub_string_(i) := substr(string_,start_pos_,end_pos_ - start_pos_);
start_pos_ := end_pos_ + 1;

dbms_output.put_line(sub_string_(i));
END LOOP;

END;


运行结果：
ELECT.PRESCHOOL TOYS DESIGNED TO DEVELOP MINDS INCLUDING:1)INTERACTIVE DRAWING SURFACE DISPLAYS CHILD'S CREATION ON LCD SCREEN
2)TEACHES KIDS TO WRITE NUMBERS AND LETTERS STEP BY STEP 3)MAGIC SENSOR LETS KIDS SEE THEIR PICTURES ON THE COMPUTER SCREEN 4)FUN
GAMES REINFORCE CORE PRESCHOOL CONCEPTS IN ENGAGING FORMAT 5)11 AMAZING MODES OF PLAY. WITH A LCD SCREEN. B/O

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8466688/viewspace-45316/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8466688/viewspace-45316/