有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的格式.
错解:
select to_char(to_date('2010-12-06','yyyy-mm-dd'),'mon-dd-yyyy') from dual
如果安装的系统默认语言为EN,那么这种执行结果正确,但是若是CN那就错了.所以,需要设置一下显示的语言才能保证Dec的出现.
正解:
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.外连接与内连接:
正解:oracle外连接中左.右.全连接的区别
4.如何删除重复的记录.
错解:
select * from 表 where Id in (select Id from 表 group by Id having count(Id) > 1)
仔细看了一下代码,自己居然粗心地把所有重复记录数据的都按 id 删除了,但是是不是该保留一行呢?!
正解:
delete from table
where id in
(
select id from table
group by id
having count(id)>1
and rowid not in
(
select min(rowid) from table
--记住oracle中独有的标识列字段rowid,
--查询所有重复id但是不包括最小(min)的id,删除之;最大的效果同理.
--但是是不是还有个问题,id必须是递增而且是设为主键的,要不然,这道例子......
)
)
5.返回今天星期几(5.1返回 星期五;5.2返回Friday)
正解:
5.1
select to_char(sysdate,'day','nls_date_language=''simplified chinese''') from dual
5.2
select to_char(sysdate,'day','nls_date_language=American') from dual
这个和和第一题类似.注意5.1的simplified chinese后面的3个单引号,因为是字符串.所以在
simplified chinese要加上单引号’simplified chinese’,但是因为两个单词中间有空格,再加上一层
”simplified chinese”,再有一个单引号就是转义符,变成了 ”simplified chinese”’.反正有点混乱,看我在论坛问的这个解答:
6.返回当前月的最后一天.
正解:
select last_day(add_months(sysdate,0)) from dual
如果把sysydate加法或者减法,结果就是求出加法或者减法之后的月份的最后一天
7.使用Oracle自带函数实现输入5.5,分别得到6和5;
正解:
select round(5.5,0) from dual--得到6
select trunc(5.5,0) from dual--得到5
PS:参看第2题已有解释.
8.给现有日期加上2年.
正解:
select add_months(sysdate,24) from dual
天真的以为add_years存在,试了试,居然没有这个函数.
9.搜索出 users 表中工号以 s 开头的,前 10 条记录.
错解:
select * from users where id like 'S*' and rownum<=10
没有吧,以前在Windows系统中搜索文件常用 ?等,结果现在忘了是在写PL/SQL.惯例,rownum是Oracle特有.
正解:
select * from users where id like 'S%' and rownum<=10
10.插入全年日期进入mgs_psd_report表F1栏.
正解:
测试表test4,类似mgs_psd_report表.
创建test4表语句(我在这里创建test4以备语句测试,实际上一样,不要拘泥于具体的表名):
--(实验表test4
select * from test4
drop table test4
create table test4
(
F1 number,
F2 varchar(20),
F3 number
)
--)
插入语句(mgs_psd_report):
--存储过程.以前存储过程真是没怎么写过,现在补习虽然说有点不习惯.不过还好,基本格式对了,居然写出来了.
create or replace procedure Sp_ShowDate(v_year in varchar2)
as
v_datecount number:=0;--从0开始,因为要算上第一天
v_datelength number;
v_datestart date;--第一天
v_dateend date;--最后一天
begin
--select to_char(sysdate,'yyyy')||'0101' into v_datestart from dual
select to_date((v_year||'0101'),'yyyymmdd') into v_datestart from dual;--第一天
select to_date((v_year||'1231'),'yyyymmdd') into v_dateend from dual;--最后一天
select v_dateend-v_datestart into v_datelength from dual;--不加1,因为第一天加上364或者365相当于365或者366天
while v_datecount<=v_datelength loop
insert into mgs_psd_report(F1) values(to_char(to_date(to_char(v_year||'0101'),'yyyymmdd')+v_datecount,'yyyymmdd'));
v_datecount:=v_datecount+1;
end loop;
end;
--执行.以'2012'年为例
begin
Sp_ShowDate('2012');
end;
居然还真对了,蛮高兴的.
11.写一个存储过程,更新上一题中的F2栏位(可以见我上题创建的test4表语句),更新所有的.
要求:若当天星期六.星期日为N;
5月1日到5月3日,10月1日到10月3日为N2;
其他日期为P.
错解就不贴了,有点长.
正解:
--创建
create or replace procedure Sp_UpdateDate(v_year in varchar2)--年份
as
v_datecount number:=0;--从0开始,因为要算上第一天
v_datelength number;--总天数
v_datestart date;--第一天
v_dateend date;--最后一天
v_datetemp varchar2(20);--日期
v_datetemp2 varchar(20);--星期几
begin
select to_date((v_year||'0101'),'yyyymmdd') into v_datestart from dual;--第一天
select to_date((v_year||'1231'),'yyyymmdd') into v_dateend from dual;--最后一天
select v_dateend-v_datestart into v_datelength from dual;--不加1,因为第一天加上364或者365相当于365或者366天
while v_datecount<=v_datelength loop
select F1 into v_datetemp from mgs_psd_report where F1=to_char(to_date(to_char(v_year||'0101'),'yyyymmdd')+v_datecount,'yyyymmdd');
select to_char(to_date(trim(v_datetemp),'yyyymmdd'),'day','nls_date_language=American') into v_datetemp2 from dual;
if trim(v_datetemp2)='saturday' or trim(v_datetemp2)='sunday' then
update mgs_psd_report set F2='N' where F1=v_datetemp;
else
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') then
update mgs_psd_report set F2='n2' where F1=v_datetemp;
else
update mgs_psd_report set F2='P' where F1=v_datetemp;
end if;
end if;
v_datecount:=v_datecount+1;
end loop;
end Sp_UpdateDate;
--执行
begin
Sp_UpdateDate('2012');
end;
效果图:
12.如何快速清空一个大表(不要清空db中现有数据)
错解:
delete from 表名 --可以回滚
快速!具体的…这个没实践,看书得到的结果.
正解:
truncate table 表名 --不可以回滚，速度更快
13.写一个函数可以进行16进制和10进制的转换.
正解:
10=>16
--函数
create or replace function fun_10to16(v_num in number)
return varchar2
as
v_temp varchar2(20);
begin
select to_char(v_num,'xxxxx') into v_temp from dual;
return v_temp;
end fun_10to16;
--执行
declare
v_test number:=16;
v_temp varchar(20):='';
begin
v_temp:=fun_10to16(v_test);
dbms_output.put_line(v_temp);
end;
select fun_10to16(16) from dual
16=>10
--函数
create or replace function fun_16to10(v_num in varchar2)
return varchar2
as
v_temp varchar2(20);
begin
select to_number (v_num,'xxxxx') into v_temp from dual;
return v_temp;
end fun_16to10;
--执行
select fun_16to10(‘1E’) from dual
这个没多大难度,但是不能忘了function的写法步骤.
14.编写一个函数,实现加减乘除,要求有异常处理.
正解:
create or replace function fun_getresult(v_num1 in number,v_num2 in number,v_symbol in varchar2)
return number
as
ex_error exception;
v_temp number;
begin
if v_symbol='+' then
v_temp:=v_num1+v_num2;
return v_temp;
end if;
if v_symbol='-' then
v_temp:=v_num1-v_num2;
return v_temp;
end if;
if v_symbol='*' then
v_temp:=v_num1*v_num2;
return v_temp;
end if;
if v_symbol='/' then
if v_num2=0 then
raise ex_error;
else
v_temp:=v_num1/v_num2;
return v_temp;
end if;
end if;
exception
when ex_error then
dbms_output.put_line('o cannot be used here!');
end;
--执行
select fun_getresult(12,3,'/') from dual
开始写的时候没有异常处理,只用了 if 判断被除数是否为0,这个就不算是异常处理了.
15.写一个触发器,操作一个表(emp_info)时,向另一个表(emp_info_bk)插入操作的内容.测试向其插入 ” ‘ ” , ” | ” 字符。
正解:
--创建
create or replace trigger tr_replace
before insert or update or delete
on emp_info
for each row
begin
insert into emp_info_bk values(:new.creator,:new.creation_date,:new.id,:new.name,:new.address);
end tr_replace;
--执行
insert into emp_info values(23,'test22','test22',to_date('20130426','yyyymmdd'),'xxxx2')
insert into emp_info values(24,'''','|',to_date('20130426','yyyymmdd'),'xxxx3')
触发器,写的更少了,诶.
–select ”’||’ from dual
||正常引到引号中，就是字符了。
而单引号，需要前边再加一个单引号转义。 ””四个单引号，前后两个表示字符串两端的单引号，中间部分是字符串。而中间有两个单引号，第一个是转义字符，表示把第二个转成字符串的单引号。第二个，就是外围两个单引号引住的实际的字符串的单引号。
16.用一条sql实现以下转换
如
student subject grade
---------------------------
student1 语文 80
student1 数学 70
student1 英语 60
student2 语文 90
student2 数学 80
student2 英语 100
......
转换为:
语文 数学 英语
student1 80 70 60
student2 90 80 100
正解:
select student 姓名,sum(decode(subject,'语文',grade,null)) 语文,sum(decode(subject,'数学',grade,null)) 数学,sum(decode(subject,'英语',grade,null)) 英语
from teststu
group by student
decode用法,要注意了.
17.调用sen_email过程把某个数据发送到[email protected]
正解:
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,'[email protected]','test1','11111111111111111111111111111111');
end;
提示错误: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 99
student2 80 60 100
student3 60 70 20
student4 90 80 80
student5 100 67 85
student6 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用来
select stu,
case when sx<60 then '不及格' else(case when sx>80 then '优秀' else '及格' end) end as sx,
case when yw<60 then '不及格' else(case when yw>80 then '优秀' else '及格' end) end as yw,
case when hx<60 then '不及格' else(case when hx>80 then '优秀' else '及格' end) end as hx
from test3
19.写一个函数
传送的值是: {name}testname{worker_id}S0135{EMAIL}[email protected]等 以{}+value形式的一串有规则的字符
要求根据{}中的內容得到value
如果 输入{name},則得到testname
输入{worker_id},則得到S0135
正解:
--函数
create or replace function fun_getmystr(v_str in varchar2,v_input in varchar2)--v_str总字符,v_input查找字符
return varchar2
as
--v_strinput varchar2(10);--输入字符
v_strlen number;--输入字符串总长度
v_strinputlen number;--查找字符长度
v_strinputpos number;--查找字符串所在位置
v_strtemp varchar2(100);--临时字符串
v_strend number;--下一个{所在位置
v_stroutput varchar2(20);--查找的结果
begin
select length(v_input),length(v_str) into v_strinputlen,v_strlen from dual;--传入字符串总长度和查找字符所在位置
--find position of v_input
select instr(v_str,v_input,1) into v_strinputpos from dual;--传入字符串第一次出现所在位置
select substr(v_str,v_strinputpos+v_strinputlen,v_strlen-v_strinputpos-v_strinputlen+1) into v_strtemp from dual;--去掉前面的
select instr(v_strtemp,'{',1) into v_strend from dual;--查询下一个 "{"所在位置,若没有返回0
if v_strend>0 then--后面还有"{"字符串
select substr(v_strtemp,0,v_strend-1) into v_stroutput from dual;
return v_stroutput;
else
return v_strtemp;
end if;
end fun_getmystr;
--执行
select fun_getmystr('{name}testname{worker_id}S0135{EMAIL}[email protected],'{worker_id}') from dual
实验一下以下代码,对instr,substr 分割字符串 将会是一个很好的掌握.
select instr('{name}testname{worker_id}S0135{EMAIL}[email protected]','{worker_id}',1) from dual;
select substr('{name}testname{worker_id}S0135{EMAIL}[email protected]',length('{worker_id}')+12,length('{name}testname{worker_id}S0135{EMAIL}[email protected]')-12-length('{worker_id}')+1)
from dual;
select instr('testname{worker_id}S0135{EMAIL}[email protected]','{',1)
from dual;
select substr('testname{worker_id}S0135{EMAIL}[email protected]',0,6-1)
from dual;
20.写一个函数,将数字人民币金额转为大写
(a)输入参数为数字,如 123456789
输入参数为数字,如 一亿二千三百四十五万六千七百八十九
(b)输入参数为数字,如 123456789.01
输入参数为数字,如 一亿二千三百四十五万六千七百八十九元一角
(c)输入参数为数字,如 10023
输入参数为数字,如 一万零二十三元
错误:
“`sql
create or replace function fun_tra(v_num1 in number)
return varchar2
as
v_num2 varchar2(20):=to_char(v_num1);–初始化
v_temp varchar2(20);–数字大小写转换
–v_tempout varchar(20);–
v_tempdol varchar(10);–人民币判断
v_count1 number:=0;–当输入为小数时
v_count2 number:=0;–当输入为小数时
–v_show varchar(40);–
v_count number:=length(v_num2);–输入数字位数
begin
select reverse(v_num2) into v_num2 from dual;–反转
if instr(v_num2,’.’)>0 then–带小数部分,?
while v_count>0 loop
select substr(v_num2,v_count,1) into v_temp from dual;
if v_temp=’.’ then v_count1:=v_count-1 ; v_count2:=length(v_num2)-v_count;end if;–记住小数点之前和之后的长度
v_count:=v_count-1;
end loop;
while v_count1>0 loop
select substr(v_num2,v_count,1) into v_temp from dual;
if v_temp=’1′ then v_temp:=’一’;end if;
if v_temp=’2′ then v_temp:=’二’;end if;
if v_temp=’3′ then v_temp:=’三’;end if;
if v_temp=’4′ then v_temp:=’四’;end if;
if v_temp=’5′ then v_temp:=’五’;end if;
if v_temp=’6′ then v_temp:=’六’;end if;
if v_temp=’7′ then v_temp:=’七’;end if;
if v_temp=’8′ then v_temp:=’八’;end if;
if v_temp=’9′ then v_temp:=’九’;end if;
–if v_temp=’.’ then v_temp:=’角’;end if;
–if v_count=length(v_num2)+1 then v_temp:=’元’;end if;
if v_count=1+v_count1 then v_tempdol:=’元’;end if;
if v_count=2+v_count1 or v_count1=6+v_count1 then v_tempdol:=’十’;end if;
if v_count=3+v_count1 or v_count1=7+v_count1 then v_tempdol:=’百’;end if;
if v_count=4+v_count1 or v_count1=8+v_count1 then v_tempdol:=’千’;end if;
if v_count=5+v_count1 then v_tempdol:=’万’;end if;
if v_count=9+v_count1 then v_tempdol:=’亿’;end if;
v_count:=v_count-1;
dbms_output.put(v_temp);
dbms_output.put_line(v_tempdol);
end loop;
dbms_output.put_line(‘角’);
else–不带小数部分读取
while v_count>0 loop
select substr(v_num2,v_count,1) into v_temp from dual;
if v_temp=’1′ then v_temp:=’一’;end if;
if v_temp=’2′ then v_temp:=’二’;end if;
if v_temp=’3′ then v_temp:=’三’;end if;
if v_temp=’4′ then v_temp:=’四’;end if;
if v_temp=’5′ then v_temp:=’五’;end if;
if v_temp=’6′ then v_temp:=’六’;end if;
if v_temp=’7′ then v_temp:=’七’;end if;
if v_temp=’8′ then v_temp:=’八’;end if;
if v_temp=’9′ then v_temp:=’九’;end if;
if v_temp=’.’ then v_temp:=’角’;end if;
–if v_count=length(v_num2)+1 then v_temp:=’元’;end if;
if v_count=1 then v_tempdol:=’元’;end if;
if v_count=2 or v_count=6 then v_tempdol:=’十’;end if;
if v_count=3 or v_count=7 then v_tempdol:=’百’;end if;
if v_count=4 or v_count=8 then v_tempdol:=’千’;end if;
if v_count=5 then v_tempdol:=’万’;end if;
if v_count=9 then v_tempdol:=’亿’;end if;
v_count:=v_count-1;
dbms_output.put(v_temp);
dbms_output.put_line(v_tempdol);
end loop;
end if;
return null;
end;
declare
v_num number:=213192312.2;
v_show number;
begin
v_show:=fun_tra(v_num);
–dbms_output.put_line(v_show);
end;
我写的一个,但是不能读取含有小数的,而且还不能一行输出,但是我感觉这样的逻辑还算可以理解,这道题,头大,真希望有个好的讲解,诶.真是晕这个正确的参考了一下网上的,但是还是有点晕.看来我需要加强一下decode函数.
正确:
```sql
create or replace function fun_tra(v_num1 in number)
return varchar2
as
v_num2 varchar2(20):=to_char(v_num1);--初始化
v_temp varchar2(20);--数字大小写转换
--v_tempout varchar(20);--
v_tempdol varchar(10);--人民币判断
v_count1 number:=0;--当输入为小数时
v_count2 number:=0;--当输入为小数时
--v_show varchar(40);--
v_count number:=length(v_num2);--输入数字位数
begin
select reverse(v_num2) into v_num2 from dual;--反转
if instr(v_num2,'.')>0 then--带小数部分,?
while v_count>0 loop
select substr(v_num2,v_count,1) into v_temp from dual;
if v_temp='.' then v_count1:=v_count-1 ; v_count2:=length(v_num2)-v_count;end if;--记住小数点之前和之后的长度
v_count:=v_count-1;
end loop;
while v_count1>0 loop
select substr(v_num2,v_count,1) into v_temp from dual;
if v_temp='1' then v_temp:='一';end if;
if v_temp='2' then v_temp:='二';end if;
if v_temp='3' then v_temp:='三';end if;
if v_temp='4' then v_temp:='四';end if;
if v_temp='5' then v_temp:='五';end if;
if v_temp='6' then v_temp:='六';end if;
if v_temp='7' then v_temp:='七';end if;
if v_temp='8' then v_temp:='八';end if;
if v_temp='9' then v_temp:='九';end if;
--if v_temp='.' then v_temp:='角';end if;
--if v_count=length(v_num2)+1 then v_temp:='元';end if;
if v_count=1+v_count1 then v_tempdol:='元';end if;
if v_count=2+v_count1 or v_count1=6+v_count1 then v_tempdol:='十';end if;
if v_count=3+v_count1 or v_count1=7+v_count1 then v_tempdol:='百';end if;
if v_count=4+v_count1 or v_count1=8+v_count1 then v_tempdol:='千';end if;
if v_count=5+v_count1 then v_tempdol:='万';end if;
if v_count=9+v_count1 then v_tempdol:='亿';end if;
v_count:=v_count-1;
dbms_output.put(v_temp);
dbms_output.put_line(v_tempdol);
end loop;
dbms_output.put_line('角');
else--不带小数部分读取
while v_count>0 loop
select substr(v_num2,v_count,1) into v_temp from dual;
if v_temp='1' then v_temp:='一';end if;
if v_temp='2' then v_temp:='二';end if;
if v_temp='3' then v_temp:='三';end if;
if v_temp='4' then v_temp:='四';end if;
if v_temp='5' then v_temp:='五';end if;
if v_temp='6' then v_temp:='六';end if;
if v_temp='7' then v_temp:='七';end if;
if v_temp='8' then v_temp:='八';end if;
if v_temp='9' then v_temp:='九';end if;
if v_temp='.' then v_temp:='角';end if;
--if v_count=length(v_num2)+1 then v_temp:='元';end if;
if v_count=1 then v_tempdol:='元';end if;
if v_count=2 or v_count=6 then v_tempdol:='十';end if;
if v_count=3 or v_count=7 then v_tempdol:='百';end if;
if v_count=4 or v_count=8 then v_tempdol:='千';end if;
if v_count=5 then v_tempdol:='万';end if;
if v_count=9 then v_tempdol:='亿';end if;
v_count:=v_count-1;
dbms_output.put(v_temp);
dbms_output.put_line(v_tempdol);
end loop;
end if;
return null;
end;
declare
v_num number:=213192312.2;
v_show number;
begin
v_show:=fun_tra(v_num);
--dbms_output.put_line(v_show);
end;
有网友参考性的留言：
2013-05-11 16:33 ylucifer
LZ，给你一条SQL语句吧，不多解释，相关知识和扩展知识自己有空整理一下吧。有空时，我再把删除重复的SQL也给你吧
select to_char(to_date('2013-01-01', 'YYYY-MM-DD') + level - 1,
'YYYY-MM-DD') day_info
from dual
connect by to_date('2013-01-01', 'YYYY-MM-DD') <
to_date('2014-01-01', 'YYYY-MM-DD');
2013-05-11 16:44 ylucifer
唉，不知道你的业务场景是什么，写了一大堆存储过程，再给你一条SQL吧
select substr(regexp_substr('{name}testname{worker_id}S0135{EMAIL}[email protected]',
'{worker_id}\w+'),
length('{worker_id}') + 1) valueStr
from dual;
我回复的:
你的意思是这个直接可以分割出想要的字符？如果我变换传入的长字符串呢，是不是又得写出语句？要求的是可以任意变换传入的字符串，不用改PL/SQL
