20个案例掌握PLSQL 基础.docx

1、20个案例掌握PLSQL 基础20个案例掌握PL/SQL 基础测试:SQL Navigator 3+Oracle;远程连接用以下案例来总结 PL/SQL相关知识:1.将2010-12-06转换成Dec-06-2010的格式.错解:1 select to_char(to_date(2010-12-06,yyyy-mm-dd),mon-dd-yyyy) from dualPS:如果安装的系统默认语言为EN,那么这种执行结果正确,但是若是CN那就错了.所以,需要设置一下显示的语言才能保证Dec的出现.正解:1 select to_char(to_date(2010-12-06,yyyy-mm-dd)

2、,mon-dd-yyyy,NLS_DATE_LANGUAGE=American) from dual2.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(s

3、tr1,num1,num2)截取字符串str中 从num1位开始之后的num2个,num1为负数时反向3.外连接与内连接:正解:oracle外连接中左.右.全连接的区别= 传送门4.如何删除重复的记录.错解:1 select * from 表 where Id in (select Id from 表 group by Id having count(Id) 1)PS:仔细看了一下代码,自己居然粗心地把所有重复记录数据的都按 id 删除了,但是是不是该保留一行呢?!正解: 1 delete from table 2 where id in 3 ( 4 select id from table

4、5 group by id 6 having count(id)1 7 and rowid not in 8 ( 9 select min(rowid) from table10 -记住oracle中独有的标识列字段rowid,11 -查询所有重复id但是不包括最小(min)的id,删除之;最大的效果同理.12 -但是是不是还有个问题,id必须是递增而且是设为主键的,要不然,这道例子.13 )14 )5.返回今天星期几(5.1返回 星期五;5,2返回Friday)正解:5.11 select to_char(sysdate,day,nls_date_language=simplified ch

5、inese) from dual5.21 select to_char(sysdate,day,nls_date_language=American) from dualPS:这个和和第一题类似.注意5.1的simplified chinese后面的3个单引号,因为是字符串.所以在simplified chinese要加上单引号simplified chinese,但是因为两个单词中间有空格,再加上一层simplified chinese,再有一个单引号就是转义符,变成了 simplified chinese.反正有点混乱,看我在论坛问的这个解答: 6.返回当前月的最后一天.正解:1 sele

6、ct last_day(add_months(sysdate,0) from dualPS:如果把sysydate加法或者减法,结果就是求出加法或者减法之后的月份的最后一天7.使用Oracle自带函数实现输入5.5,分别得到6和5;正解:1 select round(5.5,0) from dual-得到62 select trunc(5.5,0) from dual-得到5PS:参看第2题已有解释.8.给现有日期加上2年.正解:1 select add_months(sysdate,24) from dualPS:天真的以为add_years存在,试了试,居然没有这个函数.9.搜索出 use

7、rs 表中工号以 s 开头的,前 10 条记录.错解:select * from users where id like S* and rownum=10PS:没有*吧,以前在Windows系统中搜索文件常用* ?等,结果现在忘了是在写PL/SQL.惯例,rownum是Oracle特有.正解:1 select * from users where id like S% and rownum=1010.插入全年日期进入mgs_psd_report表F1栏.正解:测试表test4,类似mgs_psd_report表.创建test4表语句(我在这里创建test4以备语句测试,实际上一样,不要拘泥于具

8、体的表名): 1 -(实验表test4 2 select * from test4 3 drop table test4 4 create table test4 5 ( 6 F1 number, 7 F2 varchar(20), 8 F3 number 9 ) 10 -)插入语句(mgs_psd_report): 1 -存储过程.以前存储过程真是没怎么写过,现在补习虽然说有点不习惯.不过还好,基本格式对了,居然写出来了. 2 create or replace procedure Sp_ShowDate(v_year in varchar2) 3 as 4 v_datecount numb

9、er:=0;-从0开始,因为要算上第一天 5 v_datelength number; 6 v_datestart date;-第一天 7 v_dateend date;-最后一天 8 begin 9 -select to_char(sysdate,yyyy)|0101 into v_datestart from dual10 select to_date(v_year|0101),yyyymmdd) into v_datestart from dual;-第一天11 select to_date(v_year|1231),yyyymmdd) into v_dateend from dual;

10、-最后一天12 13 select v_dateend-v_datestart into v_datelength from dual;-不加1,因为第一天加上364或者365相当于365或者366天14 15 while v_datecount=v_datelength loop16 insert into mgs_psd_report(F1) values(to_char(to_date(to_char(v_year|0101),yyyymmdd)+v_datecount,yyyymmdd);17 v_datecount:=v_datecount+1;18 end loop;19 end;

11、20 -执行.以2012年为例21 begin22 Sp_ShowDate(2012);23 end;PS:居然还真对了,蛮高兴的.11.写一个存储过程,更新上一题中的F2栏位(可以见我上题创建的test4表语句),更新所有的.要求:若当天星期六.星期日为N; 5月1日到5月3日,10月1日到10月3日为N2; 其他日期为P.错解就不贴了,有点长.正解: 1 -创建 2 create or replace procedure Sp_UpdateDate(v_year in varchar2)-年份 3 as 4 v_datecount number:=0;-从0开始,因为要算上第一天 5 v_

12、datelength number;-总天数 6 v_datestart date;-第一天 7 v_dateend date;-最后一天 8 v_datetemp varchar2(20);-日期 9 v_datetemp2 varchar(20);-星期几10 begin11 select to_date(v_year|0101),yyyymmdd) into v_datestart from dual;-第一天12 select to_date(v_year|1231),yyyymmdd) into v_dateend from dual;-最后一天13 select v_dateend

13、-v_datestart into v_datelength from dual;-不加1,因为第一天加上364或者365相当于365或者366天14 15 while v_datecount16 1 -函数 2 create or replace function fun_10to16(v_num in number) 3 return varchar2 4 as 5 v_temp varchar2(20); 6 begin 7 select to_char(v_num,xxxxx) into v_temp from dual; 8 return v_temp; 9 end fun_10to

14、16;10 -执行11 declare12 v_test number:=16;13 v_temp varchar(20):=;14 begin15 v_temp:=fun_10to16(v_test);16 dbms_output.put_line(v_temp);17 end; 18 select fun_10to16(16) from dual16=10 1 -函数 2 create or replace function fun_16to10(v_num in varchar2) 3 return varchar2 4 as 5 v_temp varchar2(20); 6 begin

15、 7 select to_number (v_num,xxxxx) into v_temp from dual; 8 return v_temp; 9 end fun_16to10;10 -执行 11 select fun_16to10(1E) from dualPS:这个没多大难度,但是不能忘了function的写法步骤.14.编写一个函数,实现加减乘除,要求有异常处理.正解: 1 create or replace function fun_getresult(v_num1 in number,v_num2 in number,v_symbol in varchar2) 2 return

16、number 3 as 4 ex_error exception; 5 v_temp number; 6 begin 7 if v_symbol=+ then 8 v_temp:=v_num1+v_num2; 9 return v_temp;10 end if;11 if v_symbol=- then12 v_temp:=v_num1-v_num2;13 return v_temp;14 end if;15 if v_symbol=* then16 v_temp:=v_num1*v_num2;17 return v_temp;18 end if;19 if v_symbol=/ then20

17、 if v_num2=0 then21 raise ex_error;22 else23 v_temp:=v_num1/v_num2;24 return v_temp;25 end if;26 end if;27 exception28 when ex_error then29 dbms_output.put_line(o cannot be used here!);30 end;31 32 -执行33 select fun_getresult(12,3,/) from dualPS:开始写的时候没有异常处理,只用了 if 判断被除数是否为0,这个就不算是异常处理了.15.写一个触发器,操作一

18、个表(emp_info)时,向另一个表(emp_info_bk)插入操作的内容.测试向其插入 , | 字符。正解: 1 -创建 2 create or replace trigger tr_replace 3 before insert or update or delete 4 on emp_info 5 for each row 6 begin 7 insert into emp_info_bk values(:new.creator,:new.creation_date,:new.id,:new.name,:new.address); 8 end tr_replace; 9 -执行10

19、insert into emp_info values(23,test22,test22,to_date(20130426,yyyymmdd),dong2)11 insert into emp_info values(24,|,to_date(20130426,yyyymmdd),dong3)PS:触发器,写的更少了,诶.-select | from dual|正常引到引号中，就是字符了。而单引号，需要前边再加一个单引号转义。 四个单引号，前后两个表示字符串两端的单引号，中间部分是字符串。而中间有两个单引号，第一个是转义字符，表示把第二个转成字符串的单引号。第二个，就是外围两个单引号引住的实际

20、的字符串的单引号。16.用一条sql实现以下转换如student subject grade-student1 语文 80student1 数学 70student1 英语 60student2 语文 90student2 数学 80student2 英语 100.转换为: 语文 数学 英语student1 80 70 60student2 90 80 100正解:select student 姓名,sum(decode(subject,语文,grade,null) 语文,sum(decode(subject,数学,grade,null) 数学,sum(decode(subject,英语,gr

21、ade,null) 英语from teststugroup by studentPS:decode用法,要注意了.17.调用sen_email过程把某个数据发送到xx正解:create or replace procedure Sp_SendMyEmail(v_From in varchar2,v_To in varchar2,v_Subject in varchar2,v_Body in varchar2)as v_Cc VARCHAR2(20) := NULL; v_Bcc VARCHAR2(20):= NULL; v_ContentType VARCHAR2(40) := text/pl

22、ain;charset=gb2312; v_MailIp VARCHAR2(20) := 这里是服务器的IP地址xx.xxx.xx.x; v_Port NUMBER := 25;begin send_email(v_From,v_To,v_Subject,v_Body,v_Cc,v_Bcc,v_ContentType,v_MailIp,v_Port);end Sp_SendMyEmail;-执行begin Sp_SendMyEmail(2,xx,test1,111111*11111111111111111);end;PS:提示错误:ORA-29278: SMTP transient error

23、: 421 Service not available.说明这个写的正确,另外想问一点:如果 v_Cc VARCHAR2(20) := NULL; v_Bcc VARCHAR2(20):= NULL; v_ContentType VARCHAR2(40) := text/plain;charset=gb2312; v_MailIp VARCHAR2(20) := xx.xxx.x.x; v_Port NUMBER := 25;在存储过程中已经初始化了,但是存储过程传递的参数中还有这些变量,难道必须得我这么做,在外面调用时候还得初始化一下?不然怎么传参?求解.18.列出总分成绩处于第5位的学生;

24、另写一个sql语句得到大于或者等于80的为优秀,大于或者等于60的为及格,小于60分显示不及格Stu 数学 语文 化学student1 50 100 99student2 80 60 100student3 60 70 20student4 90 80 80student5 100 67 85student6 100 77 81.正解:select * from( select rownum id,stu from( select stu,sum(yw+sx+hx) result from test3 group by stu order by result asc )where id=5-因

25、为数据中没有rownum这个列,不能直接写出rownum=5这样的查询,所以为了可以使用rownum,不断查询,把rownum保存入id用来1 select stu,2 case when sx80 then 优秀 else 及格 end) end as sx,3 case when yw80 then 优秀 else 及格 end) end as yw,4 case when hx80 then 优秀 else 及格 end) end as hx5 from test319.写一个函数传送的值是: nameflyherworker_idS0135EMAILdong3580 等 以+value形式的一串有规则的字符要求根据中的內容得到value如果 输入name,則得到flyher 输入worker_id,則得到S0135正解: 1 -函数 2 create or replace function fun_getmystr(v_str in varchar2,v_input in varchar2)-v_str总字符,v_input查找字符