Oracle题目：奇数行在句末打印逗号，偶数行在句末打印句号

1.建一张表

create table  poem(linenum int,line varchar2(50))TABLESPACE USERS;

2.插入数据

insert into poem values(1,\'久旱逢甘露——几滴\');insert into poem values(2,\'他乡遇故知——仇敌\');insert into poem values(3,\'洞房花烛夜——隔壁\');insert into poem values(4,\'金榜题名时——落第\');

3.核心

DECLAREnum int;linetext varchar2(50);CURSOR poem_cursor IS SELECT linenum,line from poem order by linenum FOR UPDATE;BEGINOPEN poem_cursor;LOOPFETCH poem_cursor INTO num,linetext;EXIT WHEN poem_cursor%NOTFOUND;IF num mod 2 = 1 THENUPDATE poem SET line = linetext || \'，\' WHERE CURRENT OF poem_cursor;ELSEUPDATE poem SET line = linetext || \'。\' WHERE CURRENT OF poem_cursor;END IF;END LOOP;CLOSE poem_cursor;COMMIT;END;/

4.查看数据

select * from poem;

5.效果如图：

第二个题目要求：创建一个存储过程，传入一个参数，能够打印前num个字符

CREATE OR REPLACE PROCEDURE ChangePoem(num int)ASline poem.line%TYPE;CURSOR poem_cursor2 ISSELECT line FROM poem order by linenum;BEGINOPEN poem_cursor2;LOOPFETCH poem_cursor2 INTO line;EXIT WHEN poem_cursor2%NOTFOUND;DBMS_OUTPUT.PUT_LINE(SUBSTR(line,1,num));END LOOP;CLOSE poem_cursor2;END;/EXEC ChangePoem(5);

第三个题目

CREATE OR REPLACE PROCEDURE ChangePoemASline poem.line%TYPE;CURSOR poem_cursor3 ISSELECT line FROM poem order by linenum;BEGINOPEN poem_cursor3;LOOPFETCH poem_cursor3 INTO line;EXIT WHEN poem_cursor3%NOTFOUND;DBMS_OUTPUT.PUT_LINE(\'本想着\'||SUBSTR(line,1,5)||\'，\'||\'没想到结果却是\'||SUBSTR(line,8,9));END LOOP;CLOSE poem_cursor3;END;/