游标,存储过程

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;