20个案例掌握PL SQL 基础文档格式.docx

1、oracle外连接中左.右.全连接的区别=4.如何删除重复的记录.1 select * from 表 where Id in （select Id from 表 group by Id having count（Id） 1）仔细看了一下代码,自己居然粗心地把所有重复记录数据的都按 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）

2、from table10 -记住oracle中独有的标识列字段rowid,11 -查询所有重复id但是不包括最小（min）的id,删除之;最大的效果同理.12 -但是是不是还有个问题,id必须是递增而且是设为主键的,要不然,这道例子.13 ）14 ）5.返回今天星期几（5.1返回 星期五;5,2返回Friday）5.11 select to_char（sysdate,daynls_date_language=simplified chinese5.2nls_date_language=American这个和和第一题类似.注意5.1的simplified chinese后面的3个单引号,因为是字

3、符串.所以在simplified chinese要加上单引号,但是因为两个单词中间有空格,再加上一层,再有一个单引号就是转义符,变成了 .反正有点混乱,看我在论坛问的这个解答:6.返回当前月的最后一天.1 select last_day（add_months（sysdate,0） from dual如果把sysydate加法或者减法,结果就是求出加法或者减法之后的月份的最后一天7.使用Oracle自带函数实现输入5.5,分别得到6和5;1 select round（5.5,0） from dual-得到62 select trunc（5.5,0） from dual-得到5参看第2题已有解释.

4、8.给现有日期加上2年.1 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特有.1 select * from users where id like S%10.插入全年日期进入mgs_psd_report表F1栏.测试

5、表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

6、 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|yyyymmdd） into v_datestart from dual;11 select

7、to_date（v_year|1231） 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_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

8、（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 dual16=10 2 create or replace function fun_16to10（v_num in va

9、rchar2） 7 select to_number （v_num, 9 end fun_16to10;10 -执行 11 select fun_16to10（1E） from dual这个没多大难度,但是不能忘了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 nu

10、mber; 7 if v_symbol=+ 8 v_temp:=v_num1+v_num2; 9 return v_temp;10 end if;11 if v_symbol=-12 v_temp:=v_num1-v_num2;13 return v_temp;14 end if;15 if v_symbol=*16 v_temp:=v_num1*v_num2;17 return v_temp;18 end if;19 if v_symbol=/20 if v_num2=0 then21 raise ex_error;22 else23 v_temp:=v_num1/v_num2;24 ret

11、urn v_temp;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,开始写的时候没有异常处理,只用了 if 判断被除数是否为0,这个就不算是异常处理了.15.写一个触发器,操作一个表（emp_info）时,向另一个表（emp_info_bk）插入操作的内容.测试向其插入 , | 字符。 2 create or replace trigger tr_replace 3 before insert

12、or update or delete 4 on emp_info 5 for each row 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,to_date（20130426dong2）11 insert into emp_info values（24,|dong3触发器,写的更少了,诶.-select | from

13、dual|正常引到引号中，就是字符了。而单引号，需要前边再加一个单引号转义。四个单引号，前后两个表示字符串两端的单引号，中间部分是字符串。而中间有两个单引号，第一个是转义字符，表示把第二个转成字符串的单引号。第二个，就是外围两个单引号引住的实际的字符串的单引号。16.用一条sql实现以下转换如student subject grade-student1 语文 80student1 数学 70student1 英语 60student2 语文 90student2 数学 80student2 英语 100.转换为: 语文 数学 英语student1 80 70 60student2 90 80

14、100select student 姓名,sum（decode（subject,语文,grade,null） 语文,sum（decode（subject,数学,grade,null） 数学,sum（decode（subject,英语,grade,null） 英语from teststugroup by studentdecode用法,要注意了.17.调用sen_email过程把某个数据发送到xxcreate or replace procedure Sp_SendMyEmail（v_From in varchar2,v_To in varchar2,v_Subject in varchar2,

15、v_Body in varchar2）as v_Cc VARCHAR2（20） := NULL; v_Bcc VARCHAR2（20）: 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;

16、-执行 Sp_SendMyEmail（2,xxtest1111111*11111111111111111end;提示错误:ORA-29278: SMTP transient error: 421 Service not available.说明这个写的正确,另外想问一点:如果xx.xxx.x.x在存储过程中已经初始化了,但是存储过程传递的参数中还有这些变量,难道必须得我这么做,在外面调用时候还得初始化一下?不然怎么传参?求解.18.列出总分成绩处于第5位的学生;另写一个sql语句得到大于或者等于80的为优秀,大于或者等于60的为及格,小于60分显示不及格Stu 数学 语文 化学student1

17、 50 100 99student2 80 60 100student3 70 20student4 90 80student5 67 85student6 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 sx80 then 优秀 else 及格 end） end as sx,3 case when yw end） end as yw,4 case when hx end） end