oracle 中函数，游标的使用

原文转载自：http://blog.csdn.net/zhengqiqiqinqin/article/details/9098699

--创建函数并且调用
create function fun1(v_in_ename varchar2)
return number is
v_annual_sal number;
begin
  select (sal+nvl(comm,0))*13 into v_annual_sal from emp where ename=v_in_ename;
  return v_annual_sal;
end;
/

--直接调用函数
var v_annual_sal number;
call fun1('FORD') into:v_annual_sal;
print v_annual_sal;

--包
--需求：编写一个包，该包有一个过程，该过程可以接收用户名和新的薪水
--(将来用于通过用户名去更新薪水)还有一个函数，该函数可以接收一个用户名
--(将来要实现得到该用户的年薪是多少)
create package mypackage1 is
--声明一个过程
procedure pro1(v_in_ename varchar2,v_in_newsal number);
function fun1(v_in_ename varchar2) return number;
end;

--编写包体
create package body mypackage1 is
--具体实现过程
procedure pro1(v_in_ename varchar2,v_in_newsal number) is
begin
  update emp set sal=v_in_newsal where ename=v_in_ename;
end;
function fun1(v_in_ename varchar2) return number is
  v_annual_sal number;
begin
  select (sal+nvl(comm,0))*13 into v_annual_sal from emp 
  where ename=v_in_ename;
  return v_annual_sal;
end;
end;
/  

--pl/sql详细语法
create or replace procedure pro1(v_in_empno number) is
--定义变量
v_tax_rate number :=0.03;
v_sal number;
v_tax number;
v_ename varchar2(32);
begin
select ename,sal into v_ename,v_sal from emp
where empno=v_in_empno;
v_tax:=v_sal*v_tax_rate;
dbms_output.put_line(v_ename||'工资是='||v_sal||'个人所得税='||v_tax);
end;

--pl/sql中使用%type 来提高列的灵活性
create or replace procedure pro1(v_in_empno number) is
--定义变量
v_tax_rate number :=0.03;
v_sal emp.sal%type;
v_tax number;
v_ename emp.ename%type;
begin
select ename,sal into v_ename,v_sal from emp
where empno=v_in_empno;
v_tax:=v_sal*v_tax_rate;
dbms_output.put_line(v_ename||'工资是='||v_sal||'个人所得税='||v_tax);
end;


--【案例】编写一个过程，该过程可以接收一个用户编号，
--并显示该用户的名字，薪水，工作岗位（注意：使用pl/sql记录实现）
create or replace procedure pro1(v_in_empno in number) is
--定义记录数据类型
type zy_emp_record is record(
v_ename emp.ename%type,
v_sal emp.sal%type,
v_job emp.job%type
);
--定义一个变量，类型是zy_emp_record
v_emp_record zy_emp_record;
begin
select ename,sal,job into v_emp_record
from emp where empno=v_in_empno;
dbms_output.put_line('用户名：'||v_emp_record.v_ename||'薪水='||v_emp_record.v_sal);
end;

--游标的使用
create or replace procedure pro1(v_in_deptno number) is
--先定义一个游标变量类型
type zy_emp_cursor is ref cursor;
--定义一个游标变量
v_emp_cursor zy_emp_cursor;
--定义2个变量
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
--执行语句
open v_emp_cursor for select 
ename,sal from emp where deptno=v_in_deptno;
--取出游标指向的每行数据，用循环语句
loop
fetch v_emp_cursor into v_ename,v_sal; --这句话执行后会引起v_emp_cursor向下走
--判断当前游标是否到达最后
exit when v_emp_cursor%notfound;
--输出
dbms_output.put_line('用户名='||v_ename||' 薪水='||v_sal);
end loop;
--关闭游标
close v_emp_cursor;
end;

--游标的使用，在上面的基础上添加工资
create or replace procedure pro1(v_in_deptno number) is
--先定义一个游标变量类型
type zy_emp_cursor is ref cursor;
--定义一个游标变量
v_emp_cursor zy_emp_cursor;
--定义2个变量
v_ename emp.ename%type;
v_sal emp.sal%type;
v_empno emp.empno%type;
begin
--执行语句
open v_emp_cursor for select 
ename,sal,empno from emp where deptno=v_in_deptno;
--取出游标指向的每行数据，用循环语句
loop
fetch v_emp_cursor into v_ename,v_sal,v_empno; --这句话执行后会引起v_emp_cursor向下走
--判断当前游标是否到达最后
exit when v_emp_cursor%notfound;
--输出
dbms_output.put_line('用户名='||v_ename||' 薪水='||v_sal);
if v_sal <2000 then
  update emp set sal=sal+1000 where empno=v_empno;
end if;
end loop;
--关闭游标
close v_emp_cursor;
end;