20个案例掌握PL SQL 基础.docx
《20个案例掌握PL SQL 基础.docx》由会员分享，可在线阅读，更多相关《20个案例掌握PL SQL 基础.docx（21页珍藏版）》请在冰豆网上搜索。
20个案例掌握PLSQL基础
20个案例掌握PL/SQL基础
有MSSQL基础,学习了两周多的PL/SQL,做了一些事例,但是很多信息在网上难以找到太多正确的答案,看到一篇又一篇的PL/SQL博文,案例方面的博文一篇又一篇的雷同,一看就是是Ctrl+C的复制.给一些博主留言希望得到解答,但是等到却是"我也是复制来的,具体的没测试".
狠心之下,花了不少时间学习.做了一些例子,搞定之余,留下点供参考的例子.
阅读本篇博文之前,建议你学习一下基础,这是推荐的两个PL/SQL博客,
EricHu胡勇:
Oracle编程详解 =>传送门
liulun:
PL/SQL学习笔记（索引帖）=>传送门
测试:
SQLNavigator3+Oracle;远程连接
用以下案例来总结PL/SQL相关知识:
1.将2010-12-06转换成Dec-06-2010的格式.
错解:
1selectto_char（to_date（'2010-12-06','yyyy-mm-dd'）,'mon-dd-yyyy'）fromdual
PS:
如果安装的系统默认语言为EN,那么这种执行结果正确,但是若是CN那就错了.所以,需要设置一下显示的语言才能保证Dec的出现.
正解:
1selectto_char（to_date（'2010-12-06','yyyy-mm-dd'）,'mon-dd-yyyy','NLS_DATE_LANGUAGE=American'）fromdual
2.PL/SQL中常用round函数,trunc函数,instr函数,substr函数的区别.
正解:
（1）round（x,y）以小数点右边第y位四舍五入x;
（2）trunc（x,y）舍去小数点右边第y位以后的数字;
（不要去看网上的所谓负数出现的解释,没什么用,以后出现的机会也不会存在）
（3）instr（str1,str2,num）
Str1:
原字符串,str2要查找的字符串,num第几次出现
返回要查找的str2在原字符串str1中第num次出现的位置,若不存在,则返回0;
（4）substr（str1,num1,num2）截取字符串str中从num1位开始之后的num2个,num1为负数时反向
3.外连接与内连接:
正解:
oracle外连接中左.右.全连接的区别=> 传送门
4.如何删除重复的记录.
错解:
1select*from表whereIdin（selectIdfrom表groupbyIdhavingcount（Id）>1）
PS:
仔细看了一下代码,自己居然粗心地把所有重复记录数据的都按id删除了,但是是不是该保留一行呢?
!
正解:
1deletefromtable
2whereidin
3（
4selectidfromtable
5groupbyid
6havingcount（id）>1
7androwidnotin
8（
9selectmin（rowid）fromtable
10--记住oracle中独有的标识列字段rowid,
11--查询所有重复id但是不包括最小（min）的id,删除之;最大的效果同理.
12--但是是不是还有个问题,id必须是递增而且是设为主键的,要不然,这道例子......
13）
14）
5.返回今天星期几（5.1返回星期五;5,2返回Friday）
正解:
5.1
1selectto_char（sysdate,'day','nls_date_language=''simplifiedchinese'''）fromdual
5.2
1selectto_char（sysdate,'day','nls_date_language=American'）fromdual
PS:
这个和和第一题类似.注意5.1的simplifiedchinese后面的3个单引号,因为是字符串.所以在
simplifiedchinese要加上单引号'simplifiedchinese',但是因为两个单词中间有空格,再加上一层
''simplifiedchinese'',再有一个单引号就是转义符,变成了''simplifiedchinese'''.反正有点混乱,看我在论坛问的这个解答:
6.返回当前月的最后一天.
正解:
1selectlast_day（add_months（sysdate,0））fromdual
PS:
如果把sysydate加法或者减法,结果就是求出加法或者减法之后的月份的最后一天
7.使用Oracle自带函数实现输入5.5,分别得到6和5;
正解:
1selectround（5.5,0）fromdual--得到6
2selecttrunc（5.5,0）fromdual--得到5
PS:
参看第2题已有解释.
8.给现有日期加上2年.
正解:
1selectadd_months（sysdate,24）fromdual
PS:
天真的以为add_years存在,试了试,居然没有这个函数.
9. 搜索出users表中工号以s开头的,前10条记录.
错解:
select*fromuserswhereidlike'S*'andrownum<=10
PS:
没有*吧,以前在Windows系统中搜索文件常用*?
等,结果现在忘了是在写PL/SQL.惯例,rownum是Oracle特有.
正解:
1select*fromuserswhereidlike'S%'andrownum<=10
10.插入全年日期进入mgs_psd_report表F1栏.
正解:
测试表test4,类似mgs_psd_report表.
创建test4表语句（我在这里创建test4以备语句测试,实际上一样,不要拘泥于具体的表名）:
1--（实验表test4
2select*fromtest4
3droptabletest4
4createtabletest4
5（
6F1number,
7F2varchar（20）,
8F3number
9）
10--）
插入语句（mgs_psd_report）:
1--存储过程.以前存储过程真是没怎么写过,现在补习虽然说有点不习惯.不过还好,基本格式对了,居然写出来了.
2createorreplaceprocedureSp_ShowDate（v_yearinvarchar2）
3as
4v_datecountnumber:
=0;--从0开始,因为要算上第一天
5v_datelengthnumber;
6v_datestartdate;--第一天
7v_dateenddate;--最后一天
8begin
9--selectto_char（sysdate,'yyyy'）||'0101'intov_datestartfromdual
10selectto_date（（v_year||'0101'）,'yyyymmdd'）intov_datestartfromdual;--第一天
11selectto_date（（v_year||'1231'）,'yyyymmdd'）intov_dateendfromdual;--最后一天
12
13selectv_dateend-v_datestartintov_datelengthfromdual;--不加1,因为第一天加上364或者365相当于365或者366天
14
15whilev_datecount<=v_datelengthloop
16insertintomgs_psd_report（F1）values（to_char（to_date（to_char（v_year||'0101'）,'yyyymmdd'）+v_datecount,'yyyymmdd'））;
17v_datecount:
=v_datecount+1;
18endloop;
19end;
20--执行.以'2012'年为例
21begin
22Sp_ShowDate（'2012'）;
23end;
PS:
居然还真对了,蛮高兴的.
11.写一个存储过程,更新上一题中的F2栏位（可以见我上题创建的test4表语句）,更新所有的.
要求:
若当天星期六.星期日为N;
5月1日到5月3日,10月1日到10月3日为N2;
其他日期为P.
错解就不贴了,有点长.
正解:
1--创建
2createorreplaceprocedureSp_UpdateDate（v_yearinvarchar2）--年份
3as
4v_datecountnumber:
=0;--从0开始,因为要算上第一天
5v_datelengthnumber;--总天数
6v_datestartdate;--第一天
7v_dateenddate;--最后一天
8v_datetempvarchar2（20）;--日期
9v_datetemp2varchar（20）;--星期几
10begin
11selectto_date（（v_year||'0101'）,'yyyymmdd'）intov_datestartfromdual;--第一天
12selectto_date（（v_year||'1231'）,'yyyymmdd'）intov_dateendfromdual;--最后一天
13selectv_dateend-v_datestartintov_datelengthfromdual;--不加1,因为第一天加上364或者365相当于365或者366天
14
15whilev_datecount<=v_datelengthloop
16selectF1intov_datetempfrommgs_psd_reportwhereF1=to_char（to_date（to_char（v_year||'0101'）,'yyyymmdd'）+v_datecount,'yyyymmdd'）;
17selectto_char（to_date（trim（v_datetemp）,'yyyymmdd'）,'day','nls_date_language=American'）intov_datetemp2fromdual;
18iftrim（v_datetemp2）='saturday'ortrim（v_datetemp2）='sunday'then
19updatemgs_psd_reportsetF2='N'whereF1=v_datetemp;
20else
21iftrim（v_datetemp）=（v_year||'0501'）ortrim（v_datetemp）=（v_year||'0502'）ortrim（v_datetemp）=（v_year||'0503'）ortrim（v_datetemp）=（v_year||'1001'）ortrim（v_datetemp）=（v_year||'1002'）ortrim（v_datetemp）=（v_year||'1003'）then
22updatemgs_psd_reportsetF2='n2'whereF1=v_datetemp;
23else
24updatemgs_psd_reportsetF2='P'whereF1=v_datetemp;
25endif;
26endif;
27v_datecount:
=v_datecount+1;
28endloop;
29endSp_UpdateDate;
30
31--执行
32begin
33Sp_UpdateDate（'2012'）;
34end;
效果图:
12.如何快速清空一个大表（不要清空db中现有数据）
错解:
1deletefrom表名--可以回滚
PS:
快速!
具体的...这个没实践,看书得到的结果.
正解:
1truncatetable表名--不可以回滚，速度更快
13.写一个函数可以进行16进制和10进制的转换.
正解:
10=>16
1--函数
2createorreplacefunctionfun_10to16（v_numinnumber）
3returnvarchar2
4as
5v_tempvarchar2（20）;
6begin
7selectto_char（v_num,'xxxxx'）intov_tempfromdual;
8returnv_temp;
9endfun_10to16;
10--执行
11declare
12v_testnumber:
=16;
13v_tempvarchar（20）:
='';
14begin
15v_temp:
=fun_10to16（v_test）;
16dbms_output.put_line（v_temp）;
17end;
18selectfun_10to16（16）fromdual
16=>10
1--函数
2createorreplacefunctionfun_16to10（v_numinvarchar2）
3returnvarchar2
4as
5v_tempvarchar2（20）;
6begin
7selectto_number（v_num,'xxxxx'）intov_tempfromdual;
8returnv_temp;
9endfun_16to10;
10--执行
11selectfun_16to10（‘1E’）fromdual
PS:
这个没多大难度,但是不能忘了function的写法步骤.
14.编写一个函数,实现加减乘除,要求有异常处理.
正解:
1createorreplacefunctionfun_getresult（v_num1innumber,v_num2innumber,v_symbolinvarchar2）
2returnnumber
3as
4ex_errorexception;
5v_tempnumber;
6begin
7ifv_symbol='+'then
8v_temp:
=v_num1+v_num2;
9returnv_temp;
10endif;
11ifv_symbol='-'then
12v_temp:
=v_num1-v_num2;
13returnv_temp;
14endif;
15ifv_symbol='*'then
16v_temp:
=v_num1*v_num2;
17returnv_temp;
18endif;
19ifv_symbol='/'then
20ifv_num2=0then
21raiseex_error;
22else
23v_temp:
=v_num1/v_num2;
24returnv_temp;
25endif;
26endif;
27exception
28whenex_errorthen
29dbms_output.put_line（'ocannotbeusedhere!
'）;
30end;
31
32--执行
33selectfun_getresult（12,3,'/'）fromdual
PS:
开始写的时候没有异常处理,只用了if判断被除数是否为0,这个就不算是异常处理了.
15.写一个触发器,操作一个表（emp_info）时,向另一个表（emp_info_bk）插入操作的内容.测试向其插入 "'" ,"|"字符。
正解:
1--创建
2createorreplacetriggertr_replace
3beforeinsertorupdateordelete
4onemp_info
5foreachrow
6begin
7insertintoemp_info_bkvalues（:
new.creator,:
new.creation_date,:
new.id,:
new.name,:
new.address）;
8endtr_replace;
9--执行
10insertintoemp_infovalues（23,'test22','test22',to_date（'20130426','yyyymmdd'）,'dong2'）
11insertintoemp_infovalues（24,'''','|',to_date（'20130426','yyyymmdd'）,'dong3'）
PS:
触发器,写的更少了,诶.
--select'''||'fromdual
||正常引到引号中，就是字符了。
而单引号，需要前边再加一个单引号转义。
''''四个单引号，前后两个表示字符串两端的单引号，中间部分是字符串。
而中间有两个单引号，第一个是转义字符，表示把第二个转成字符串的单引号。
第二个，就是外围两个单引号引住的实际的字符串的单引号。
16.用一条sql实现以下转换
如
studentsubjectgrade
---------------------------
student1语文80
student1数学70
student1英语60
student2语文90
student2数学80
student2英语100
......
转换为:
语文数学英语
student1807060
student29080100
正解:
selectstudent姓名,sum（decode（subject,'语文',grade,null））语文,sum（decode（subject,'数学',grade,null））数学,sum（decode（subject,'英语',grade,null））英语
fromteststu
groupbystudent
PS:
decode用法,要注意了.
17.调用sen_email过程把某个数据发送到xx@
正解:
createorreplaceprocedureSp_SendMyEmail（v_Frominvarchar2,v_Toinvarchar2,v_Subjectinvarchar2,v_Bodyinvarchar2）
as
v_CcVARCHAR2（20）:
=NULL;
v_BccVARCHAR2（20）:
=NULL;
v_ContentTypeVARCHAR2（40）:
='text/plain;charset=gb2312';
v_MailIpVARCHAR2（20）:
=这里是服务器的IP地址xx.xxx.xx.x';
v_PortNUMBER:
=25;
begin
send_email（v_From,v_To,v_Subject,v_Body,v_Cc,v_Bcc,v_ContentType,v_MailIp,v_Port）;
endSp_SendMyEmail;
--执行
begin
Sp_SendMyEmail（2,'xx@','test1','111111*********11111111111111111'）;
end;
PS:
提示错误:
ORA-29278:
SMTPtransienterror:
421Servicenotavailable.说明这个写的正确,另外想问一点:
如果
v_CcVARCHAR2（20）:
=NULL;
v_BccVARCHAR2（20）:
=NULL;
v_ContentTypeVARCHAR2（40）:
='text/plain;charset=gb2312';
v_MailIpVARCHAR2（20）:
='xx.xxx.x.x';
v_PortNUMBER:
=25;
在存储过程中已经初始化了,但是存储过程传递的参数中还有这些变量,难道必须得我这么做,在外面调用时候还得初始化一下?
不然怎么传参?
求解.
18.列出总分成绩处于第5位的学生;另写一个sql语句得到大于或者等于80的为优秀,大于或者等于60的为及格,小于60分显示不及格
Stu 数学 语文 化学
student1 50 100 99
student2 80 60 100
student3 60 70 20
student4 90 80 80
student5 100 67 85
student6 100 77 81
...
正解:
select*from（
selectrownumid,stufrom（
selectstu,sum（yw+sx+hx）result
fromtest3
groupbystu
orderbyresultasc
）
）
whereid='5'
--因为数据中没有rownum这个列,不能直接写出rownum=5这样的查询,所以为了可以使用rownum,不断查询,把rownum保存入id用来
1selectstu,
2casewhensx<60then'不及格'else（casewhensx>80then'优秀'else'及格'end）endassx,
3casewhenyw<60then'不及格'else（casewhenyw>80then'优秀'else'及格'end）endasyw,
4casewhenhx<60then'不及格'else（casewhenhx>80then'优秀'else'及格'end）end
升级会员 