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;/