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.核心

DECLARE
 num int;
 linetext varchar2(50);
 CURSOR poem_cursor IS SELECT linenum,line from poem order by linenum FOR UPDATE;
BEGIN
 OPEN poem_cursor;
LOOP
FETCH poem_cursor INTO num,linetext;
EXIT WHEN poem_cursor%NOTFOUND;
IF num mod 2 = 1 THEN
 UPDATE poem SET line = linetext || '，' WHERE CURRENT OF poem_cursor;
ELSE 
 UPDATE 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
)
AS 
line poem.line%TYPE;
CURSOR poem_cursor2 IS
SELECT line FROM poem order by linenum;
BEGIN
OPEN poem_cursor2;
LOOP
FETCH 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 ChangePoem
AS 
line poem.line%TYPE;
CURSOR poem_cursor3 IS
SELECT line FROM poem order by linenum;
BEGIN
OPEN poem_cursor3;
LOOP
FETCH 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;
/