有MS SQL基础,学习了两周多的PL/SQL,做了一些事例,但是很多信息在网上难以找到太多正确的答案,看到一篇又一篇的PL/SQL博文,案例方面的博文一篇又一篇的雷同,一看就是是Ctrl+C的复制.给一些博主留言希望得到解答,但是等到却是"我也是复制来的,具体的没测试".
狠心之下,花了不少时间学习.做了一些例子,搞定之余,留下点供参考的例子.
阅读本篇博文之前,建议你学习一下基础,这是推荐的两个PL/SQL博客,
EricHu 胡勇:Oracle编程详解 =>
liulun: 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 dual PS:如果安装的系统默认语言为EN,那么这种执行结果正确,但是若是CN那就错了.所以,需要设置一下显示的语言才能保证Dec的出现.
正解:
1 select to_char(to_date('2010-12-06','yyyy-mm-dd'),'mon-dd-yyyy','NLS_DATE_LANGUAGE=American') from dual 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.外连接与内连接:
正解:=>
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 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.1
1 select to_char(sysdate,'day','nls_date_language=''simplified chinese''') from dual
5.2
1 select to_char(sysdate,'day','nls_date_language=American') from dual
PS:这个和和第一题类似.注意5.1的simplified chinese后面的3个单引号,因为是字符串.所以在
simplified chinese要加上单引号'simplified chinese',但是因为两个单词中间有空格,再加上一层
''simplified chinese'',再有一个单引号就是转义符,变成了 ''simplified chinese'''.反正有点混乱,看我在论坛问的这个解答:
6.返回当前月的最后一天.
正解:
1 select last_day(add_months(sysdate,0)) from dual
PS:如果把sysydate加法或者减法,结果就是求出加法或者减法之后的月份的最后一天
7.使用Oracle自带函数实现输入5.5,分别得到6和5;
正解:1 select round(5.5,0) from dual--得到62 select trunc(5.5,0) from dual--得到5
PS:参看第2题已有解释.
8.给现有日期加上2年.正解:
1 select add_months(sysdate,24) from dual
PS:天真的以为add_years存在,试了试,居然没有这个函数.
9. 搜索出 users 表中工号以 s 开头的,前 10 条记录.错解:
select * from users where id like 'S*' and rownum<=10
PS:没有*吧,以前在Windows系统中搜索文件常用* ?等,结果现在忘了是在写PL/SQL.惯例,rownum是Oracle特有.
正解:1 select * from users where id like 'S%' and rownum<=10
10.插入全年日期进入mgs_psd_report表F1栏.
正解:
测试表test4,类似mgs_psd_report表.
创建test4表语句(我在这里创建test4以备语句测试,实际上一样,不要拘泥于具体的表名):
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 number:=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;--最后一天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;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_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-v_datestart into v_datelength from dual;--不加1,因为第一天加上364或者365相当于365或者366天14 15 while v_datecount<=v_datelength loop16 select F1 into v_datetemp from mgs_psd_report where F1=to_char(to_date(to_char(v_year||'0101'),'yyyymmdd')+v_datecount,'yyyymmdd');17 select to_char(to_date(trim(v_datetemp),'yyyymmdd'),'day','nls_date_language=American') into v_datetemp2 from dual;18 if trim(v_datetemp2)='saturday' or trim(v_datetemp2)='sunday' then19 update mgs_psd_report set F2='N' where F1=v_datetemp;20 else21 if trim(v_datetemp)=(v_year||'0501') or trim(v_datetemp)=(v_year||'0502') or trim(v_datetemp)=(v_year||'0503') or trim(v_datetemp)=(v_year||'1001')or trim(v_datetemp)=(v_year||'1002')or trim(v_datetemp)=(v_year||'1003') then22 update mgs_psd_report set F2='n2' where F1=v_datetemp;23 else24 update mgs_psd_report set F2='P' where F1=v_datetemp;25 end if;26 end if;27 v_datecount:=v_datecount+1;28 end loop;29 end Sp_UpdateDate; 30 31 --执行32 begin33 Sp_UpdateDate('2012');34 end; 效果图:
12.如何快速清空一个大表(不要清空db中现有数据)
错解:
1 delete from 表名 --可以回滚
PS:快速!具体的...这个没实践,看书得到的结果.
正解:1 truncate table 表名 --不可以回滚，速度更快
13.写一个函数可以进行16进制和10进制的转换.
正解:
10=>16
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_10to16;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 dual
16=>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 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 dual
PS:这个没多大难度,但是不能忘了function的写法步骤.
14.编写一个函数,实现加减乘除,要求有异常处理.
正解:
1 create or replace function fun_getresult(v_num1 in number,v_num2 in number,v_symbol in varchar2) 2 return 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 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 dual PS:开始写的时候没有异常处理,只用了 if 判断被除数是否为0,这个就不算是异常处理了.
15.写一个触发器,操作一个表(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 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
||正常引到引号中，就是字符了。而单引号，需要前边再加一个单引号转义。 ''''四个单引号，前后两个表示字符串两端的单引号，中间部分是字符串。而中间有两个单引号，第一个是转义字符，表示把第二个转成字符串的单引号。第二个，就是外围两个单引号引住的实际的字符串的单引号。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,'英语',grade,null)) 英语from teststugroup by student
PS:decode用法,要注意了.
17.调用sen_email过程把某个数据发送到
正解:
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/plain;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@xxx.com','test1','11111111111111111111111111111111');end;
PS:提示错误:ORA-29278: SMTP transient error: 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位的学生;另写一个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'--因为数据中没有rownum这个列,不能直接写出rownum=5这样的查询,所以为了可以使用rownum,不断查询,把rownum保存入id用来
1 select stu,2 case when sx<60 then '不及格' else(case when sx>80 then '优秀' else '及格' end) end as sx,3 case when yw<60 then '不及格' else(case when yw>80 then '优秀' else '及格' end) end as yw,4 case when hx<60 then '不及格' else(case when hx>80 then '优秀' else '及格' end) end as hx5 from test3
19.写一个函数
传送的值是: 等 以{}+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查找字符 3 return varchar2 4 as 5 --v_strinput varchar2(10);--输入字符 6 v_strlen number;--输入字符串总长度 7 8 v_strinputlen number;--查找字符长度 9 v_strinputpos number;--查找字符串所在位置10 11 v_strtemp varchar2(100);--临时字符串12 v_strend number;--下一个{所在位置13 14 v_stroutput varchar2(20);--查找的结果15 begin16 select length(v_input),length(v_str) into v_strinputlen,v_strlen from dual;--传入字符串总长度和查找字符所在位置17 --find position of v_input18 select instr(v_str,v_input,1) into v_strinputpos from dual;--传入字符串第一次出现所在位置19 20 select substr(v_str,v_strinputpos+v_strinputlen,v_strlen-v_strinputpos-v_strinputlen+1) into v_strtemp from dual;--去掉前面的21 select instr(v_strtemp,'{ ',1) into v_strend from dual;--查询下一个 "{"所在位置,若没有返回022 if v_strend>0 then--后面还有"{"字符串23 select substr(v_strtemp,0,v_strend-1) into v_stroutput from dual;24 return v_stroutput;25 else26 return v_strtemp;27 end if;28 end fun_getmystr;29 --执行30 select fun_getmystr({name}flyher{worker_id}S0135{EMAIL}dong3580@163.com,'{worker_id}') from dual PS:实验一下以下代码,对instr,substr 分割字符串 将会是一个很好的掌握.
select instr('{name}flyher{worker_id}S0135{EMAIL}dong3580@163.com','{worker_id}',1) from dual; select substr('{name}flyher{worker_id}S0135{EMAIL}dong3580@163.com',length('{worker_id}')+12,length('{name}flyher{worker_id}S0135{EMAIL}dong3580@163.com')-12-length('{worker_id}')+1) from dual; select instr('flyher{worker_id}S0135{EMAIL}dong3580@163.com','{ ',1) from dual; select substr('flyher{worker_id}S0135{EMAIL}dong3580@163.com',0,6-1) from dual;
20.写一个函数,将数字人民币金额转为大写
(a)输入参数为数字,如 123456789 输入参数为数字,如 一亿二千三百四十五万六千七百八十九 (b)输入参数为数字,如 123456789.01 输入参数为数字,如 一亿二千三百四十五万六千七百八十九元一角 (c)输入参数为数字,如 10023 输入参数为数字,如 一万零二十三元错误:
1 create or replace function fun_tra(v_num1 in number) 2 return varchar2 3 as 4 v_num2 varchar2(20):=to_char(v_num1);--初始化 5 v_temp varchar2(20);--数字大小写转换 6 --v_tempout varchar(20);-- 7 v_tempdol varchar(10);--人民币判断 8 v_count1 number:=0;--当输入为小数时 9 v_count2 number:=0;--当输入为小数时10 --v_show varchar(40);--11 v_count number:=length(v_num2);--输入数字位数12 13 begin14 select reverse(v_num2) into v_num2 from dual;--反转15 if instr(v_num2,'.')>0 then--带小数部分,?16 while v_count>0 loop17 select substr(v_num2,v_count,1) into v_temp from dual;18 if v_temp='.' then v_count1:=v_count-1 ; v_count2:=length(v_num2)-v_count;end if;--记住小数点之前和之后的长度19 v_count:=v_count-1;20 end loop;21 while v_count1>0 loop22 select substr(v_num2,v_count,1) into v_temp from dual;23 if v_temp='1' then v_temp:='一';end if;24 if v_temp='2' then v_temp:='二';end if;25 if v_temp='3' then v_temp:='三';end if;26 if v_temp='4' then v_temp:='四';end if;27 if v_temp='5' then v_temp:='五';end if;28 if v_temp='6' then v_temp:='六';end if;29 if v_temp='7' then v_temp:='七';end if;30 if v_temp='8' then v_temp:='八';end if;31 if v_temp='9' then v_temp:='九';end if;32 --if v_temp='.' then v_temp:='角';end if;33 --if v_count=length(v_num2)+1 then v_temp:='元';end if;34 if v_count=1+v_count1 then v_tempdol:='元';end if;35 if v_count=2+v_count1 or v_count1=6+v_count1 then v_tempdol:='十';end if;36 if v_count=3+v_count1 or v_count1=7+v_count1 then v_tempdol:='百';end if;37 if v_count=4+v_count1 or v_count1=8+v_count1 then v_tempdol:='千';end if;38 if v_count=5+v_count1 then v_tempdol:='万';end if;39 if v_count=9+v_count1 then v_tempdol:='亿';end if;40 v_count:=v_count-1;41 dbms_output.put(v_temp);42 dbms_output.put_line(v_tempdol);43 end loop;44 dbms_output.put_line('角');45 else--不带小数部分读取46 while v_count>0 loop47 select substr(v_num2,v_count,1) into v_temp from dual;48 if v_temp='1' then v_temp:='一';end if;49 if v_temp='2' then v_temp:='二';end if;50 if v_temp='3' then v_temp:='三';end if;51 if v_temp='4' then v_temp:='四';end if;52 if v_temp='5' then v_temp:='五';end if;53 if v_temp='6' then v_temp:='六';end if;54 if v_temp='7' then v_temp:='七';end if;55 if v_temp='8' then v_temp:='八';end if;56 if v_temp='9' then v_temp:='九';end if;57 if v_temp='.' then v_temp:='角';end if;58 --if v_count=length(v_num2)+1 then v_temp:='元';end if;59 if v_count=1 then v_tempdol:='元';end if;60 if v_count=2 or v_count=6 then v_tempdol:='十';end if;61 if v_count=3 or v_count=7 then v_tempdol:='百';end if;62 if v_count=4 or v_count=8 then v_tempdol:='千';end if;63 if v_count=5 then v_tempdol:='万';end if;64 if v_count=9 then v_tempdol:='亿';end if;65 v_count:=v_count-1;66 dbms_output.put(v_temp);67 dbms_output.put_line(v_tempdol);68 end loop;69 end if;70 return null;71 end;72 73 declare74 v_num number:=213192312.2;75 v_show number;76 begin77 v_show:=fun_tra(v_num);78 --dbms_output.put_line(v_show);79 end; PS:我写的一个,但是不能读取含有小数的,而且还不能一行输出,但是我感觉这样的逻辑还算可以理解,这道题,头大,真希望有个好的讲解,诶.真是晕这个正确的参考了一下网上的,但是还是有点晕.看来我需要加强一下decode函数.
正确:
1 create or replace function fun_tra(v_num1 in number) 2 return varchar2 3 as 4 v_num2 varchar2(20):=to_char(v_num1);--初始化 5 v_temp varchar2(20);--数字大小写转换 6 --v_tempout varchar(20);-- 7 v_tempdol varchar(10);--人民币判断 8 v_count1 number:=0;--当输入为小数时 9 v_count2 number:=0;--当输入为小数时10 --v_show varchar(40);--11 v_count number:=length(v_num2);--输入数字位数12 13 begin14 select reverse(v_num2) into v_num2 from dual;--反转15 if instr(v_num2,'.')>0 then--带小数部分,?16 while v_count>0 loop17 select substr(v_num2,v_count,1) into v_temp from dual;18 if v_temp='.' then v_count1:=v_count-1 ; v_count2:=length(v_num2)-v_count;end if;--记住小数点之前和之后的长度19 v_count:=v_count-1;20 end loop;21 while v_count1>0 loop22 select substr(v_num2,v_count,1) into v_temp from dual;23 if v_temp='1' then v_temp:='一';end if;24 if v_temp='2' then v_temp:='二';end if;25 if v_temp='3' then v_temp:='三';end if;26 if v_temp='4' then v_temp:='四';end if;27 if v_temp='5' then v_temp:='五';end if;28 if v_temp='6' then v_temp:='六';end if;29 if v_temp='7' then v_temp:='七';end if;30 if v_temp='8' then v_temp:='八';end if;31 if v_temp='9' then v_temp:='九';end if;32 --if v_temp='.' then v_temp:='角';end if;33 --if v_count=length(v_num2)+1 then v_temp:='元';end if;34 if v_count=1+v_count1 then v_tempdol:='元';end if;35 if v_count=2+v_count1 or v_count1=6+v_count1 then v_tempdol:='十';end if;36 if v_count=3+v_count1 or v_count1=7+v_count1 then v_tempdol:='百';end if;37 if v_count=4+v_count1 or v_count1=8+v_count1 then v_tempdol:='千';end if;38 if v_count=5+v_count1 then v_tempdol:='万';end if;39 if v_count=9+v_count1 then v_tempdol:='亿';end if;40 v_count:=v_count-1;41 dbms_output.put(v_temp);42 dbms_output.put_line(v_tempdol);43 end loop;44 dbms_output.put_line('角');45 else--不带小数部分读取46 while v_count>0 loop47 select substr(v_num2,v_count,1) into v_temp from dual;48 if v_temp='1' then v_temp:='一';end if;49 if v_temp='2' then v_temp:='二';end if;50 if v_temp='3' then v_temp:='三';end if;51 if v_temp='4' then v_temp:='四';end if;52 if v_temp='5' then v_temp:='五';end if;53 if v_temp='6' then v_temp:='六';end if;54 if v_temp='7' then v_temp:='七';end if;55 if v_temp='8' then v_temp:='八';end if;56 if v_temp='9' then v_temp:='九';end if;57 if v_temp='.' then v_temp:='角';end if;58 --if v_count=length(v_num2)+1 then v_temp:='元';end if;59 if v_count=1 then v_tempdol:='元';end if;60 if v_count=2 or v_count=6 then v_tempdol:='十';end if;61 if v_count=3 or v_count=7 then v_tempdol:='百';end if;62 if v_count=4 or v_count=8 then v_tempdol:='千';end if;63 if v_count=5 then v_tempdol:='万';end if;64 if v_count=9 then v_tempdol:='亿';end if;65 v_count:=v_count-1;66 dbms_output.put(v_temp);67 dbms_output.put_line(v_tempdol);68 end loop;69 end if;70 return null;71 end;72 73 declare74 v_num number:=213192312.2;75 v_show number;76 begin77 v_show:=fun_tra(v_num);78 --dbms_output.put_line(v_show);79 end;