Oracle数据库之 PL SQL 学习笔记

2018-01-30 19:18:58来源:cnblogs.com作者:我的世界2018人点击

分享

1、定义基本变量:

2、引用型的变量:set serveroutput on declarepename emp.ename%type;psal emp.sal%type; beginselect ename,sal into pename,psal from emp where empno='7521'; dbms_output.put_line(pename||'的薪水是'||psal); end;/3、记录型变量:set serveroutput on declareemp_rec emp%rowtype; beginselect * into emp_rec from emp where empno='7698'; dbms_output.put_line(emp_rec.ename||'的薪水是'||emp_rec.sal);end;/4、if语句的使用/*判断用户从键盘的输入*/set serveroutput on--接受一个键盘输入--num:地址值,含义是在该地址上保存了输入的值accept num prompt'请输入一个数字'; declare--定义变量从键盘的输入pnum number:=&num;beginif pnum=0 then dbms_output.put_line('您输入的是0');  elsif pnum=1 then dbms_output.put_line('您输入的是1');  elsif pnum=2 then dbms_output.put_line('你输入的是2');  else dbms_output.put_line('其他');end if; end;/5、while循环:set serveroutput on declare pnum number := 1; begin  while pnum <= 10 loop    dbms_output.put_line(pnum);    pnum := pnum + 1;  end loop;end;/6、loop循环set serveroutput on declare pnum number:=1; beginloop  exit when pnum>10;   dbms_output.put_line(pnum);   pnum:=pnum+1; end loop; end;/7、for循环set serveroutput on declarepnum number:=1;begin  for pnum in 1..10 loop    dbms_output.put_line(pnum);   end loop;end;/(推荐使用loop循环)8、光标的使用--查询并打印员工的姓名和薪水set serveroutput on/*光标的属性%found:光标找到记录       %notfound:光标找不到记录*/declare--定义一个光标cursor cemp is select ename,sal from emp;--为光标定义对应的变量pename emp.ename%type;psal emp.sal%type; begin--打开光标open cemp;loop  --取一条记录  fetch cemp into pename,psal;  exit when cemp %notfound;  dbms_output.put_line(pename||'的薪水是'||psal);end loop;--关闭光标close cemp; end;/9、实例---给员工涨工资:set serveroutput on declare cursor cemp is  select empno,perjob from emp;pempno emp.empno%type;pjob emp.perjob%type; beginopen cemp;loop  fetch cemp into pempno,pjob;  exit when cemp %notfound;  if pjob='PRESIDENT' then update emp set sal=sal+1500 where empno=pempno;    elsif pjob='ANALYST' then update emp set sal = sal+1000 where empno=pempno;    elsif pjob='SALESMAN' then update emp set sal = sal+500 where empno=pempno;    else update emp set sal = sal+300 where empno=pempno;  end if; end loop;close cemp;commit;(如果update了数据,需在后面加上commit)end;/10、光标的其他属性及其使用实例①%isopen:if cemp%isopen then dbms_output.put_line('光标一打开');②%rowcount: (总共影响的行数)dbms_output.put_line('行数:'||cemp %rowcount);11、光标数的限制:默认情况下oracle数据库只允许在同一个会话中打开300个光标查看光标属性:切换到sys管理员用户下,conn sys/root as sysdba 就可以切换到sys用户修改默认的光标数:alter system set open_cursors=400 scope=both;(scope的取值有三个参数:memory:只更改当前实例,不更改系统参数文件spfile      :只更改参数文件,不更改当前实例,需重启数据库才能生效both     :是以上两者如果设置错了,可以使用rollback命令回滚11、带参数的光标set serveroutput on declare cursor cemp(dno number) is select ename from emp where deptno=dno;pename emp.ename%type;beginopen cemp(10);loop  fetch cemp into pename;  exit when cemp %notfound;  dbms_output.put_line(pename);end loop;close cemp;end;/12、系统例外:no_data_found (没有找到数据)too_many_rows  (select ... into 语句匹配多个行)zero_divide         (被零除)value_error         (算术或转换错误)timeout_on_resource (在等待资源时发生超时)  13、自定义例外:set serveroutput on declare pename emp.ename%type;no_emp_found exception;cursor cemp is select ename from emp where empno=12;beginopen cemp;fetch cemp into pename;if cemp%notfound then  raise no_emp_found;end if;close cemp;exception  when no_emp_found then dbms_output.put_line('找不到员工');  when others then dbms_output.put_line('其他');end;/14、统计每年入职的员工数set serveroutput on declare cursor cemp is select to_char(hiredate,'yyyy') from emp;pdate varchar2(4);count80 number := 0;count81 number := 0;count82 number := 0;count87 number := 0;beginopen cemp;loopfetch cemp into pdate;exit when cemp%notfound;if pdate='1980' then count80:=count80+1;  elsif pdate='1981' then count81:=count81+1;  elsif pdate='1982' then count82:=count82+1;  else count87:=count87+1;end if;end loop; dbms_output.put_line('总共:'||(count80+count81+count82+count87));dbms_output.put_line('1980:'||count80);dbms_output.put_line('1981:'||count81);dbms_output.put_line('1982:'||count82);dbms_output.put_line('1987:'||count87);close cemp;end;/15、案例二:为员工涨工资/*做之前先分析SQL语句select empno,sal from emp order by sal asc;-->光标-->循环-->退出条件:1.工资总额>5w  2.%notfound 变量:1.初始值  2.如何得到涨工资的人数:countEmp number :=0;涨后的工资总额:salTotal number;select sum(sal) into salTal from emp;张后的工资总额=涨前的工资总额 + sal * 0.1*/set serveroutput ondeclare cursor cemp is select empno,sal from emp order by sal;pempno emp.empno%type;psal emp.sal%type; countEmp number:=0;salTotal number; stop_sal exception; beginselect sum(sal) into salTotal from emp;open cemp;loopif salTotal < 50000 then (加入限制只有工资总额在5000以内才执行下面的代码)exit when salTotal>50000;fetch cemp into pempno,psal;exit when cemp%notfound;countEmp := countEmp+1;update emp set sal=sal*1.1 where empno=pempno;salTotal := salTotal + psal*0.1;else raise stop_sal;end if;end loop;close cemp;commit;dbms_output.put_line('涨工资人数:'||countEmp||'工资总额:'||salTotal);exception  when stop_sal then dbms_output.put_line('涨工资结束');  when others then dbms_output.put_line('其他'); end;/16、综合案例四1、由于最后的结果也是一张表,所以先创建一张表create table msg1(coursename varchar2(20),dname varchar2(20),count1 number,count2 number,count3 number,avggrade number); SQL语句1、得到有哪些系         select dno,dname from dep; -->光标 -->循环-->退出条件:notfound2、得到系中选修了“大学物理“的学生的成绩select grade fromm sc where cno=(select cno from course where cname=??) and sno in (select sno from student where dno =??);-->带参数的光标 -->循环 -->退出条件 -->notfound 变量:1、初始值 2、如何得到     每个分数段的人数     count1 number,count2 number,count3 number     每个系选修了大学物理的学生的平均成绩     avggrade number         1、算术运算         2、sql语句查询set serveroutput on declare--系的光标cursor cdept is select dno,dname from dep;pdno dep.dno%type;pdname dep.dname%type;--成绩光标cursor cgrade(coursename varchar2,depno number) is select grade from sc where cno=(select cno from course where cname=coursename)and sno in (select sno from student where dno=depno);pgrade sc.grade%type;--每个分数段的人数count1 number;count2 number;count3 number;--每个系选修了大学物理的学生的平均成绩avggrade number;--课程名称pcourseName varchar2(20) := '大学物理'; begin  --打开系的光标open cdept;loop  --取一个系的信息  fetch cdept into pdno,pdname;  exit when cdept %notfound;  --初始化工作  count1:=0;count2:=0;count3:=0;  --系的平均成绩  select avg(grade) into avggrade from sc where cno=(select cno from course where cname=pcourseName)   and sno in (select sno from student where dno=pdno);   --取系中选修了大学物理的学生成绩  open cgrade(pcourseName,pdno);  loop    --取一个学生的成绩    fetch cgrade into pgrade;    exit when cgrade%notfound;       --判断成绩的范围    if pgrade<60 then count1:=count1+1;      elsif pgrade>=60 and pgrade<85 then count2:=count2+1;      else count3:=count3+1;         end if;     end loop;  close cgrade;  --保存当前的结构  insert into msg1 values(pcourseName,pdname,count1,count2,count3,avggrade);end loop;close cdept;dbms_output.put_line('数据查询成功!');end;/     

最新文章

123

最新摄影

闪念基因

微信扫一扫

第七城市微信公众平台