oracle sysdba 例外,Oracle数据库之 PL SQL 学习笔记

1、定义基本变量：

2、引用型的变量：

set serveroutput on

declare

pename emp.ename%type;

psal emp.sal%type;

begin

select ename,sal into pename,psal from emp where empno='7521';

dbms_output.put_line(pename||'的薪水是'||psal);

end;

/

3、记录型变量：

set serveroutput on

declare

emp_rec emp%rowtype;

begin

select * 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:=#

begin

if 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;

begin

loop

exit when pnum>10;

dbms_output.put_line(pnum);

pnum:=pnum+1;

end loop;

end;

/

7、for循环

set serveroutput on

declare

pnum 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;

begin

open 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;

begin

open 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;

begin

open 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;

begin

open cemp;

loop

fetch 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 on

declare

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;

begin

select sum(sal) into salTotal from emp;

open cemp;

loop

if 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; -->光标 -->循环-->退出条件：notfound

2、得到系中选修了“大学物理“的学生的成绩

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;

/