oracle--plsql2---函数、包、变量

函数
函数用于返回特定的数据。建立函数时，函数头部必须包含return子句，在函数体内必须包含return语句返回的数据。可以使用create function来建立函数
一般java的函数都返回一个值，这个函数一般也返回一个值。
示例：输入员工姓名，返回员工的年薪
create function sp_fun2 (spName varchar2) return
number is yearSal number(7,2);
begin
--执行部分
select sal*12+nvl(comm,0)*12 into yearSal from emp where ename=spName;
return yearSal;
end;

/

函数调用
在sqlplus中调用
var income number 定义一个变量
call annual_income('SCOTT') into:income;  --annual_incomec是函数的名称 scott是你要查询的参数，形参； income是定义的接收结果的变量
print income
在java中调用
select annual_income('SCOTT') from dual;
可以通过rs.getInt(1)得到返回的结果

包
用于在逻辑上组合过程和函数，由包规范和包体两部分组成。
创建包，create package
create package sp_package is
procedure update_sal (name varchar2 ,newsal number);
function annual_income(name varchar2) return number;
end;
/
包的规范只包含了过程和函数的说明，但是没有过程和函数的实现代码，包体用于实现包规范中的过程和函数
建立包体，createpackage body
create or replace package body sp_package is
procedure update_sal(name varchar2 ,newsal number)
is
begin
update emp set sal=newsal where ename=name;
end;

function annual_income(name varchar2)
return number is
annual_salary number;
begin
select sal*12+nvl(comm,0) into annual_salary from emp where ename=name;
return annual_salary;
end;
end;
/

调用包的过程或是函数
exec sp_package.update_sal('SCOTT',120);

变量
在pl/sql中 包括有4种变量，标量变量、复合变量、参照变量、lob
1.标量类型（scalar）
定义一个变长的字符串
v_ename varchar2(10);
定义一个小数，范围-9999.99~9999.99
v_sal number(6,2);
定义一个小数，并给一个初始值5.4，  :=是pl/sql的赋值符号
v_sal number(6,2):=5.4;
定义一个日期类型
v_hiredate date;
定义一个布尔变量，不能为空，初始值为false
v_valid boolean not null default false;
--输入员工号，显示姓名、工资、个人所得税
--税率0.03为例，
declare
c_tax_rate number(3,2):=0.03;
v_ename varchar2(5);
v_sal number(7,2);
v_tax_sal number(7,2);
begin
--执行
select ename,sal into v_ename,v_sal from emp where empno=&no;
--计算所得税
v_tax_sal:=v_sal*c_tax_rate;
--输出
dbms_output.put_line('姓名：'||v_ename||'   工资'||v_sal||'    交税：'||v_tax_sal);
end;
/
标量使用%type类型
例如；把上例的v_ename varchar2(5);  修改为
v_ename emp.ename%type;  意思是v_ename类型和emp表中ename字段，保持类型和长度都一致。
2.复合类型（composite）
用于存放多个值的变量，主要包括
(1)pl/sql记录--------指：类似于高级语言的结构体
(2)pl/sql表--------------相当于高级语言的数组，但需要注意，高级语言数组下标不能为负数，而这里是可以的，并且表元素的下标没有限制
(3)嵌套表
(4)varray动态数组
pl/sql记录的示例代码
declare
type emp_record_type is record(
name emp.ename%type,
salary emp.sal%type,
title emp.job%type
);--type表示定义一个pl/sql记录类型，类型包含了3个数据 name salary title，
sp_record emp_record_type;---定义了一个变量，变量的类型是刚才定义的类型
begin
select ename,sal,job into sp_record
from emp where empno=7788;
dbms_output.put_line('员工名：'||sp_record.name);
end;
/
pl/sql表的示例代码：
declare
 --定义了一个表类型，该类型用于存放emp.ename%type 类型的数据
type sp_table_type is table of emp.ename%type
index by binary_integer;--意思是下标按整数排序，如果是整数，意味着可以是负数
sp_table sp_table_type;
begin
select ename into sp_table(0)
from emp where empno=7788;
dbms_output.put_line('员工名：'||sp_table(0));
end;
/
3.参照变量
指用于存放数值指针的变量，通过使用参照变量，可以使应用程序共享相同的对象，从而降低占用的空间。
分为2种，游标变量(ref cursor) 和 对象类型变量(ref obj_type)
(1)游标变量
定义时，不需要指定相应的select语句，但使用时(open)，需要指定select，这样游标就会与select结合了
--编写一个块，输入部门号，显示部门所有人工资和姓名
declare
--定义游标类型
type sp_emp_cursor is ref cursor;
--定义变量，用于下面的循环
v_ename emp.ename%type;
v_sal emp.sal%type;
--定义游标变量
test_cursor sp_emp_cursor;
begin
--执行
--把test—cursor和一个select结合,意思是这个游标指向了，这个select查询的结果集
open test_cursor for select ename ,sal from emp where deptno=&no;
--循环取出
loop
fetch test_cursor into v_ename ,v_sal;
--判断工资，决定是否更新
--loop需要设置退出，判断退出条件，判断cursor是否为空
exit when test_cursor%notfound;
dbms_output.put_line('名字'||v_ename||'   工资：'||v_sal);
end loop;
--关闭游标
close test_cursor;
end;
/
4.lob