20个案例掌握PL/SQL基础

九九

11 年前

有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过程把某个数据发送到xx@xxx.com

正解:

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;

提示错误: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}xx@xxx.com等以{}+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}xx@xxx.com,'{worker_id}') from dual

实验一下以下代码,对instr,substr 分割字符串将会是一个很好的掌握.

select instr('{name}testname{worker_id}S0135{EMAIL}xx@xxx.com','{worker_id}',1) from dual;

    select substr('{name}testname{worker_id}S0135{EMAIL}xx@xxx.com',length('{worker_id}')+12,length('{name}testname{worker_id}S0135{EMAIL}xx@xxx.com')-12-length('{worker_id}')+1)
    from dual;

    select instr('testname{worker_id}S0135{EMAIL}xx@xxx.com','{',1)
    from dual;

    select substr('testname{worker_id}S0135{EMAIL}xx@xxx.com',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}xx@xxx.com',
'{worker_id}\w+'),
length('{worker_id}') + 1) valueStr
from dual;

我回复的:

你的意思是这个直接可以分割出想要的字符？如果我变换传入的长字符串呢，是不是又得写出语句？要求的是可以任意变换传入的字符串，不用改PL/SQL