20个案例掌握PL SQL 基础文档格式.docx

资源描述

20个案例掌握PL SQL 基础文档格式.docx

《20个案例掌握PL SQL 基础文档格式.docx》由会员分享，可在线阅读，更多相关《20个案例掌握PL SQL 基础文档格式.docx（21页珍藏版）》请在冰豆网上搜索。

20个案例掌握PL SQL 基础文档格式.docx

oracle外连接中左.右.全连接的区别=>

4.如何删除重复的记录.

1select*from表whereIdin（selectIdfrom表groupbyIdhavingcount（Id）>

1）

仔细看了一下代码,自己居然粗心地把所有重复记录数据的都按id删除了,但是是不是该保留一行呢?

1deletefromtable

2whereidin

3（

4selectidfromtable

5groupbyid

6havingcount（id）>

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'

5.2

nls_date_language=American'

这个和和第一题类似.注意5.1的simplifiedchinese后面的3个单引号,因为是字符串.所以在

simplifiedchinese要加上单引号'

但是因为两个单词中间有空格,再加上一层

再有一个单引号就是转义符,变成了'

.反正有点混乱,看我在论坛问的这个解答:

6.返回当前月的最后一天.

1selectlast_day（add_months（sysdate,0））fromdual

如果把sysydate加法或者减法,结果就是求出加法或者减法之后的月份的最后一天

7.使用Oracle自带函数实现输入5.5,分别得到6和5;

1selectround（5.5,0）fromdual--得到6

2selecttrunc（5.5,0）fromdual--得到5

参看第2题已有解释.

8.给现有日期加上2年.

1selectadd_months（sysdate,24）fromdual

天真的以为add_years存在,试了试,居然没有这个函数.

搜索出users表中工号以s开头的,前10条记录.

select*fromuserswhereidlike'

S*'

andrownum<

=10

没有*吧,以前在Windows系统中搜索文件常用*?

等,结果现在忘了是在写PL/SQL.惯例,rownum是Oracle特有.

1select*fromuserswhereidlike'

S%'

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||'

yyyymmdd'

）intov_datestartfromdual;

11selectto_date（（v_year||'

1231'

）intov_dateendfromdual;

13selectv_dateend-v_datestartintov_datelengthfromdual;

--不加1,因为第一天加上364或者365相当于365或者366天

15whilev_datecount<

=v_datelengthloop

16insertintomgs_psd_report（F1）values（to_char（to_date（to_char（v_year||'

）+v_datecount,'

））;

17v_datecount:

=v_datecount+1;

18endloop;

19end;

20--执行.以'

2012'

年为例

21begin

22Sp_ShowDate（'

）;

23end;

居然还真对了,蛮高兴的.

11.写一个存储过程,更新上一题中的F2栏位（可以见我上题创建的test4表语句）,更新所有的.

要求:

若当天星期六.星期日为N;

5月1日到5月3日,10月1日到10月3日为N2;

其他日期为P.

错解就不贴了,有点长.

1--创建

2createorreplaceprocedureSp_UpdateDate（v_yearinvarchar2）--年份

--总天数

8v_datetempvarchar2（20）;

--日期

9v_datetemp2varchar（20）;

--星期几

10begin

12selectto_date（（v_year||'

16selectF1intov_datetempfrommgs_psd_reportwhereF1=to_char（to_date（to_char（v_year||'

17selectto_char（to_date（trim（v_datetemp）,'

）intov_datetemp2fromdual;

18iftrim（v_datetemp2）='

saturday'

ortrim（v_datetemp2）='

sunday'

then

19updatemgs_psd_reportsetF2='

whereF1=v_datetemp;

20else

21iftrim（v_datetemp）=（v_year||'

0501'

）ortrim（v_datetemp）=（v_year||'

0502'

0503'

1001'

）ortrim（v_datetemp）=（v_year||'

1002'

1003'

）then

22updatemgs_psd_reportsetF2='

n2'

23else

24updatemgs_psd_reportsetF2='

25endif;

26endif;

27v_datecount:

28endloop;

29endSp_UpdateDate;

31--执行

32begin

33Sp_UpdateDate（'

34end;

效果图:

12.如何快速清空一个大表（不要清空db中现有数据）

1deletefrom表名--可以回滚

快速!

具体的...这个没实践,看书得到的结果.

1truncatetable表名--不可以回滚，速度更快

13.写一个函数可以进行16进制和10进制的转换.

10=>

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=>

2createorreplacefunctionfun_16to10（v_numinvarchar2）

7selectto_number（v_num,'

9endfun_16to10;

10--执行

11selectfun_16to10（‘1E’）fromdual

这个没多大难度,但是不能忘了function的写法步骤.

14.编写一个函数,实现加减乘除,要求有异常处理.

1createorreplacefunctionfun_getresult（v_num1innumber,v_num2innumber,v_symbolinvarchar2）

2returnnumber

3as

4ex_errorexception;

5v_tempnumber;

7ifv_symbol='

8v_temp:

=v_num1+v_num2;

9returnv_temp;

10endif;

11ifv_symbol='

12v_temp:

=v_num1-v_num2;

13returnv_temp;

14endif;

15ifv_symbol='

16v_temp:

=v_num1*v_num2;

17returnv_temp;

18endif;

19ifv_symbol='

20ifv_num2=0then

21raiseex_error;

22else

23v_temp:

=v_num1/v_num2;

24returnv_temp;

27exception

28whenex_errorthen

29dbms_output.put_line（'

ocannotbeusedhere!

30end;

32--执行

33selectfun_getresult（12,3,'

开始写的时候没有异常处理,只用了if判断被除数是否为0,这个就不算是异常处理了.

15.写一个触发器,操作一个表（emp_info）时,向另一个表（emp_info_bk）插入操作的内容.测试向其插入

字符。

2createorreplacetriggertr_replace

3beforeinsertorupdateordelete

4onemp_info

5foreachrow

7insertintoemp_info_bkvalues（:

new.creator,:

new.creation_date,:

new.id,:

new.name,:

new.address）;

8endtr_replace;

9--执行

10insertintoemp_infovalues（23,'

test22'

to_date（'

20130426'

dong2'

）

11insertintoemp_infovalues（24,'

dong3'

触发器,写的更少了,诶.

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

decode用法,要注意了.

17.调用sen_email过程把某个数据发送到xx@

createorreplaceprocedureSp_SendMyEmail（v_Frominvarchar2,v_Toinvarchar2,v_Subjectinvarchar2,v_Bodyinvarchar2）

v_CcVARCHAR2（20）:

=NULL;

v_BccVARCHAR2（20）:

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;

--执行

Sp_SendMyEmail（2,'

xx@'

test1'

111111*********11111111111111111'

end;

提示错误:

ORA-29278:

SMTPtransienterror:

421Servicenotavailable.说明这个写的正确,另外想问一点:

如果

xx.xxx.x.x'

在存储过程中已经初始化了,但是存储过程传递的参数中还有这些变量,难道必须得我这么做,在外面调用时候还得初始化一下?

不然怎么传参?

求解.

18.列出总分成绩处于第5位的学生;

另写一个sql语句得到大于或者等于80的为优秀,大于或者等于60的为及格,小于60分显示不及格

Stu

数学

语文

化学

student1

100

student2

100

student3

student4

student5

student6

...

select*from（

selectrownumid,stufrom（

selectstu,sum（yw+sx+hx）result

fromtest3

groupbystu

orderbyresultasc

）

whereid='

--因为数据中没有rownum这个列,不能直接写出rownum=5这样的查询,所以为了可以使用rownum,不断查询,把rownum保存入id用来

1selectstu,

2casewhensx<

60then'

不及格'

else（casewhensx>

80then'

优秀'

else'

及格'

end）endassx,

3casewhenyw<

else（casewhenyw>

end）endasyw,

4casewhenhx<

else（casewhenhx>

end）end

展开阅读全文