游标(光标): 是用来操作查询结果集,相当于是JDBC中ResultSet
语法: cursor 游标名[(参数名 参数类型)] is 查询结果集
开发步骤:
1. 声明游标
2. 打开游标 open 游标名
3. 从游标中取数据 fetch 游标名 into 变量
游标名%found :找到数据
游标名%notfound : 没有找到数据
4. 关闭游标 close 游标名
系统引用游标
1. 声明游标 : 游标名 sys_refcursor
2. 打开游标: open 游标名 for 结果集
3. 从游标中取数据
4. 关闭游标
for循环遍历游标:
不需要声明额外变量
不需要打开游标
不需要关闭游标
Oracle:> declare
--游标
cursor vrows is select * from 表1;
--s声明变量,记录一行数据
vrow 表1%rowtype;
begin
--1.打开游标
open vrows;
--2.从游标提取数据
--循环取数据
loop
fetch vrows into vrow;
exit when vrows%notfound;
dbms_output.put_line('姓名:'||vrow.ename ||' 工资: ' || vrow.sal);
end loop;
--3.关闭游标
close vrows;
end;
--输出指定部门下的员工姓名和工资
Oracle:>
declare
--声明游标
cursor vrows(dno number) is select * from 表1 where deptno = dno;
--声明变量
vrow 表1%rowtype;
begin
--1.打开游标 , 指定10号部门
open vrows(10);
--2. 循环遍历,取数据
loop
fetch vrows into vrow;
exit when vrows%notfound;
dbms_output.put_line('姓名:'||vrow.ename ||' 工资: ' || vrow.sal);
end loop;
close vrows;
end;
--系统引用游标
--输出员工表中所有的员工姓名和工资
Oracle:> declare
--声明系统引用游标
vrows sys_refcursor;
--声明一个变量
vrow 表1%rowtype;
begin
--1.打开游标
open vrows for select * from 表1;
--2.取数据
loop
fetch vrows into vrow;
exit when vrows%notfound;
dbms_output.put_line('姓名:'||vrow.ename ||' 工资: ' || vrow.sal);
end loop;
close vrows;
end;
--扩展内容----使用for循环遍历游标
Oracle:>declare
--声明一个游标
cursor vrows is select * from 表1;
begin
for vrow in vrows loop
dbms_output.put_line('姓名:'||vrow.ename ||' 工资: ' || vrow.sal || '工作:'|| vrow.job);
end loop;
end;
Oracle:>select * from 表1;
例子:--按照员工工作给所有员工涨工资,总裁涨500,经理涨300,其他人涨200
Oracle:>declare
--声明游标
cursor vrows is select * from emp;
--声明一个变量
vrow emp%rowtype;
begin
--1.打开游标
open vrows;
--2.循环取数据
loop
--取数据
fetch vrows into vrow;
--退出条件
exit when vrows%notfound;
--根据不同的职位,涨工资 总裁涨500,经理涨300,其他人涨200
if vrow.job = 'PRESIDENT' then
update emp set sal = sal + 500 where empno = vrow.empno;
elsif vrow.job = 'MANAGER' then
update emp set sal = sal + 300 where empno = vrow.empno;
else
update emp set sal = sal + 200 where empno = vrow.empno;
end if;
end loop;
--3.关闭游标
close vrows;
--4.提交事务
commit;
end;
抛出异常
语法:
declare
--声明变量
begin
--业务逻辑
exception
--处理异常
when 异常1 then
...
when 异常2 then
...
when others then
...处理其它异常
end;
zero_divide : 除零异常
value_error : 类型转换异常
too_many_rows : 查询出多行记录,但是赋值给了rowtype记录一行数据变量
no_data_found : 没有找到数据
自定义异常:
异常名 exception;
raise 异常名
Oracle:>declare
vi number;
vrow emp%rowtype;
begin
--select * into vrow from emp;
select * into vrow from emp where empno=1;
exception
when zero_divide then
dbms_output.put_line('发生了除零异常');
when value_error then
dbms_output.put_line('发生了类型转换异常');
when too_many_rows then
dbms_output.put_line(' 查询出多行记录,但是赋值给了rowtype记录一行数据变量');
when no_data_found then
dbms_output.put_line('没有找到数据异常');
when others then
dbms_output.put_line('发生了其它异常' || sqlerrm);
end;
例子:--查询指定编号的员工,如果没有找到,则抛出自定义的异常
Oracle:>declare
--声明游标
cursor vrows is select * from emp where empno=1;
--声明一个记录型变量
vrow emp%rowtype;
--声明一个自定义异常
no_emp exception;
begin
--1.打开游标
open vrows;
--2.取数据
fetch vrows into vrow;
--3.判断游标是否有数据
if vrows%notfound then
raise no_emp;
end if;
close vrows;
exception
when no_emp then
dbms_output.put_line('发生了自定义的异常');
end;
存储过程: 实际上是封装在服务器上一段PLSQL代码片断,已经编译好了的代码
1.客户端取调用存储过程,执行效率就会非常高效
语法:
create [or replace] procedure 存储过程的名称(参数名 in|out 参数类型,参数名 in|out 参数类型)
is | as
--声明部分
begin
-业务逻辑
end;
例子:--给指定员工涨薪,并打印涨薪前和涨薪后的工资
Oracle:>create or replace procedure proc_updatesal(vempno in number,vnum in number)
is
--声明变量.记录当前工资
vsal number;
begin
--查询当前的工资
select sal into vsal from emp where empno = vempno;
--输出涨薪前的工资
dbms_output.put_line('涨薪前:'||vsal);
--更新工资
update emp set sal = vsal + vnum where empno = vempno;
--输出涨薪后的工资
dbms_output.put_line('涨薪后:'||(vsal+vnum));
--提交
commit;
end;
--方式1
Oracle:>call proc_updatesal(1000,10);
--方式2 用的最多的方式
Oracle:>declare
begin
proc_updatesal(1000,100);
end;
存储函数: 实际上是一段封装是Oracle服务器中的一段PLSQL代码片断,它是已经编译好了的代码片段
语法:
create [or replace] function 存储函数的名称(参数名 in|out 参数类型,参数名 in|out 参数类型) return 参数类型
is | as
begin
end;
存储过程和函数的区别:
1.它们本质上没有区别
2.函数存在的意义是给过程调用 存储过程里面调用存储函数
3.函数可以在sql语句里面直接调用
4.存储过程能实现的,存储函数也能实现,存储函数能实现的,过程也能实现
默认是 in
例子:查询指定员工的年薪
Oracle:>create or replace function func_getsal(vempno number) return number
is
--声明变量.保存年薪
vtotalsal number;
begin
select sal*12 + nvl(comm,0) into vtotalsal from emp where empno = vempno;
return vtotalsal;
end;
--调用存储函数
declare
vsal number;
begin
vsal := func_getsal(1000);
dbms_output.put_line(vsal);
end;
--查询员工的姓名,和他的年薪
select ename,func_getsal(empno) from emp;
--查询员工的姓名和部门的名称
--查询指定员工的年薪--存储过程来实现
--参数: 员工编号
--输出: 年薪
Oracle:>create or replace procedure proc_gettotalsal(vempno in number,vtotalsal out number)
is
begin
select sal*12 + nvl(comm,0) into vtotalsal from emp where empno = vempno;
end;
declare
vtotal number;
begin
proc_gettotalsal(1000,vtotal);
dbms_output.put_line('年薪:'||vtotal);
end;
Oracle:>select * from emp where empno = 123;
触发器: 当用户执行了 insert | update | delete 这些操作之后, 可以触发一系列其它的动作/业务逻辑
作用 :
在动作执行之前或者之后,触发业务处理逻辑
插入数据,做一些校验
语法:
create [or replace] trigger 触发器的名称
before | after
insert | update | delete
on 表名
[for each row]
declare
begin
end;
触发器的分类:
语句级触发器: 不管影响多少行, 都只会执行一次
行级触发器: 影响多少行,就触发多少次
:old 代表旧的记录, 更新前的记录
:new 代表的是新的记录
--新员工入职之后,输出一句话: 欢迎
Oracle:>create or replace trigger tri_test1
after
insert
on emp
declare
begin
dbms_output.put_line('欢迎');
end;
Oracle:>insert into emp(empno,ename) values(1,'lisi');
--数据校验, 星期6休息, 不能办理新员工入职
--在插入数据之前
--判断当前日期是否是周六
--如果是周六,就不能插入
Oracle:>create or replace trigger tri_test2
before
insert
on emp
declare
--声明变量
vday varchar2(10);
begin
--查询当前
select trim(to_char(sysdate,'day')) into vday from dual;
--判断当前日期:
if vday = 'saturday' then
dbms_output.put_line('星期六,不能办理入职');
--抛出系统异常
raise_application_error(-20001,'星期六,不能办理入职');
end if;
end;
Oracle:>insert into emp(empno,ename) values(2,'zhangsan');
Oracle视图
--创建一个视图
Oracle:>create or replace view 视图名 as select 表字段 from 表;
--通过视图修改数据
Oracle:>update 视图名 set 字段='值' where 字段='值';
--创建一个只读视图
Oracle:>create or replace view 视图名 as select 表字段 from 表 with read only;
Oracle:>update 视图名 set 字段='值' where 字段='值';