20个案例掌握PLSQL 基础Word格式.docx
- 文档编号:22075352
- 上传时间:2023-02-02
- 格式:DOCX
- 页数:25
- 大小:57.79KB
20个案例掌握PLSQL 基础Word格式.docx
《20个案例掌握PLSQL 基础Word格式.docx》由会员分享,可在线阅读,更多相关《20个案例掌握PLSQL 基础Word格式.docx(25页珍藏版)》请在冰豆网上搜索。
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'
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存在,试了试,居然没有这个函数.
9.
搜索出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;
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||'
)+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='
N'
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='
P'
25endif;
26endif;
27v_datecount:
28endloop;
29endSp_UpdateDate;
30
31--执行
32begin
33Sp_UpdateDate('
34end;
效果图:
12.如何快速清空一个大表(不要清空db中现有数据)
1deletefrom表名--可以回滚
快速!
具体的...这个没实践,看书得到的结果.
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
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;
31
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)
as
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
50
100
99
student2
80
60
100
student3
70
20
student4
90
80
student5
67
85
student6
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<
else(casewhenyw>
end)endasyw,
4casewhenhx<
else(casewhenhx>
end)endashx
5fromtest3
19.写一个函数
传送的值是:
{name}flyher{worker_id}S0135{EMAIL}dong3580@
等以{}+value形式的一串有规则的字符
要求根据{}中的內容得到value
如果
输入{name},則得到flyher
输入{worker_id},則得到S0135
2createorreplacefunctionfun_getmystr(v_strinvarchar2,v_inputinvarchar2)--v_str总字符,v_input查找字符
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 20个案例掌握PLSQL 基础 20 案例 掌握 PLSQL