游标,存储过程
1.1什么是游标
用于临时存储一个查询返回的多行数据(结果集,类似于java的jdbc连接返回的ResultSet集合),通过遍历游标,可以逐行访问处理该结果集的数据.
游标的使用方式: 声明---打开--读取---关闭
1.2语法
游标声明:
CURSOR 游标名(参数列表) IS 查询语句;
游标的打开:
OPEN 游标名
游标的取值:
FETCH 游标名 INTO 变量列表
游标的关闭:
CLOSE 游标名
1.3游标的属性
| 游标的属性 | 返回值类型 | 说明 |
| %ROWCOUNT | 整型 | 获取FETCH语句返回的数据行数 |
| %FOUND | 布尔型 | 最近的FETCH语句返回一行数据则为真,否则为加 |
| %NOTFOUND | 布尔型 | 与%FOUND属性返回值相反 |
| %ISOPEN | 布尔型 | 游标已经打开时值为真,否则为假 |
其中%NOTFOUND是在游标中找不到元素的时候返回TRUE,通常用来判断退出循环
1.4创建和使用
示例:使用游标查询emp表中的所有员工的姓名和工资,并将其依次打印出来
-- 使用游标查询emp表中的所有员工的姓名和工资,并将其依次打印出来
declare
-- 声明游标 CURSOR 游标名(参数列表) IS 查询语句;
CURSOR c_emp IS select ename,sal FROM emp;
--声明变量接收游标中的数据
v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;
begin
-- 打开游标
OPEN c_emp;
--遍历游标
LOOP
--获取游标中的数据 如果有的话赋值给变量
FETCH c_emp INTO v_ename,v_sal;
EXIT WHEN c_emp%NOTFOUND;
dbms_output.put_line('姓名:'||v_ename||',薪水:'||v_sal);
END LOOP;
--关闭游标
CLOSE c_emp;
end;
1.5带参数的游标
示例:使用游标查询并打印某部门的员工的姓名和薪资,部门编号为运行时手动输入.
-- 使用游标查询并打印某部门的员工的姓名和薪资,部门编号为运行时手动输入. declare -- 声明游标 CURSOR 游标名(参数列表) IS 查询语句; CURSOR c_emp(v_deptno emp.deptno%TYPE) IS select ename,sal FROM emp where deptno=v_deptno; --声明变量接收游标中的数据 v_ename emp.ename%TYPE; v_sal emp.sal%TYPE; begin -- 打开游标 OPEN c_emp(20); --遍历游标 LOOP --获取游标中的数据 如果有的话赋值给变量 FETCH c_emp INTO v_ename,v_sal; EXIT WHEN c_emp%NOTFOUND; dbms_output.put_line('姓名:'||v_ename||',薪水:'||v_sal); END LOOP; --关闭游标 CLOSE c_emp; end;
给对应级别的员工涨工资，key是empno
-- 给对应级别的员工涨工资，key是empno
declare
-- 声明光标
cursor cemp is
select empno,job from emp;
--声明变量接收光标数据
pempno emp.empno%TYPE;
pempjob emp.job%TYPE;
begin
-- 事务回滚
rollback;
--打开光标
open cemp;
loop
--遍历光标取出一个员工
fetch cemp into pempno,pempjob;
--退出条件
exit when cemp%notfound;
--判断员工的职位
if pempjob='PRESIDENT' then update emp set sal=sal+1000 where empno=pempno;
-- if condition then block ;elsif condition then block;else block;end if;
elsif pempjob='MANAGER' then update emp set sal=sal+800 where empno=pempno;
else update emp set sal=sal+400 where empno=pempno;
end if;
end loop;
--关闭光标
close cemp;
-- oracle的默认事务隔离级别是read committed
--事务的ACID 原子性、一致性、隔离性、持久性
commit;
end;
存储过程和存储函数
数据库存储过程：指存储在数据库中供所有用户程序调用的子程序叫存储过程、存储函数
·相同点：完成特定功能的程序 ·不同点：是否用return语句返回值。存储函数可以return返回值。存储过程不可以通过return语句返回函数值。
1.创建存储过程
create or replace procedure 过程名(参数列表) as begin ..........PLSQL子程序体； end；
2.执行存储过程
--1.exec 存储过程名();
--2.begin
存储过程名();
end;
带参数的存储过程 举例：为指定的员工，涨100块钱的工资；并且打印涨前和涨后的薪水
1、创建一个带参数的存储过程： 给执行的员工涨100块钱的工资，并且打印涨前和涨后的薪水
create or replace procedure raisesalary(eno in number)
as
--定义一个变量保存涨前的薪水
psal emp.sal%TYPE;
begin
--得到员工涨前的薪水
select sal into psal from emp where empno=eno;
--给员工涨100
update emp set sal=sal+100 where empno=eno
--需不需要commit?
--注意：一般不在存储过程或存储函数中，commit和rollback
--打印
dbms_output.put_line('涨前：'||psal||'涨后：'(pasl+100))
end;
--2、如何调用：
begin
raisesalary(7839);
raisesalary(7566);
commit;
end;
存储过程的调试
1.调试存储过程最好放到Oracle数据库所在的系统或虚拟机上，解压SQL developer ，双击运行。 2.为了确保存储过程或函数是可调试的，右键“以编译并进行调试”，点击红色按钮“调试” 3.利用已写好的调用函数进行调试。
1.函数的定义
是一个命名的存储程序，可带参数，并返回一个计算值。必须有return 子句，用于返回函数值。
2.创建存储函数语法
create or replace function 函数名(参数列表)
return 函数值类型
as
begin
PLSQL子程序体;
end;
3.表达式中某个字段为空时，表达式返回值为空。为防止含有表达式的返回值错误，在可能为空的字段上加上NVL(字段名，0)。
--查询某个员工的年收入
create or replace function queryemp_income(eno number) return number
as
--定义变量接收薪水和奖金
p_sal emp.sal%type;
p_comm emp.comm%type;
begin
select sal,comm into p_sal,p_comm from emp where empno=eno;
--nvl为遇空函数，如果p_comm为空则返回0
return nvl(p_comm,0)+p_sal*12;
end;
1.存储过程和存储函数的区别
存储函数可以有一个返回值，存储过程没有返回值
2.in out 参数
存过和函数都可以通过out 指定一个或多个输出参数。可以利用out参数，实现多个返回值。
3.使用存过和存储函数的原则
只有一个返回值的话，用存储函数；否则，用存储过程。
create or replace procedure query (eno in number, pename out varchar2, psal out number, pjob out varchar2 ) as begin select ename,sal,job into pename,psal,pjob from emp where empno=eno; end;
