函数和包的创建与调用方法，触发器，标量，复合类型，参照类型变量及其举例

函数，和过程本身是一家，用于返回特定的数据，在函数头部必须包含return，一般只能返回一个值

--接第25讲
一、函数的创建与调用
1.函数的创建案例
--输入雇员的姓名，返回雇员的年薪
create function sp_fun2(spName varchar2) return number is  yearSal number(7,2);
  --spName为形参，为要传递的值 ，yearSal自取的名，数据类型
begin --执行部分
select sal*12+nvl(comm,0)*12 into yearSal from emp where ename=spName;
return yearSal;
end;
/
show error;
2.函数的调用案例
var abc number;   --定义一个变量
call sp_fun2('SCOTT') into:abc;

二、包的创建与调用
1.包的作用：用以逻辑上组合函数和过程，由包规范和包体组成
2.创建包 ，所创建后会在左边列表找到
create package sp_package is --创建一个名为sp_package的包
  procedure update_sal(name varchar2,newsal number);  --声明该包有一个过程updatge_sal
  function annual_income(name varchar2) return number;  --声明该包有一个函数annual_income
end;
/
3.创建包体  --给包sp_package实现包体
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)*12 into annual_salary from emp where ename=name;
 return annual_salary;
end;
end;
/
4.调用包   --包名，包中的过程或函数名，及其参数
exec sp_package.update_sal('SCOTT',120);   --先包名，再包里的函数名和过程名，如是其它方案的包，还要在包前加方案名

三、触发器
隐含的执行的存储过程，必须指定触发的事件和触发的操作，实际操作就是一个pl/sql块
常用触发事件包括insert,update,delete等
使用create trigger建立触发器
如删除主主键，触发删除外键

四、定义并使用变量
变量主要有四大类型，标量类型scalar（存储最简单的变量），复合类型composite，参照类型reference， lob类型(large object)，主讲前三个。
1.标量类型(scalar)变量，标量只能存放单个数据，包括很多类型，定义变量和常量的语法，
定义标量的案例：
v_ename varchar2(10);
v_sal number(6,2);
v_sal2 number(6,2):=5.4   --定义一个小数，设置初始值为5.4 :=赋值符号
v_hiredate date;
v_valid boolean not null default....   --定义布尔
赋值符号为是等号前加冒号，如:=
标量调用的案例
以输入员工号，显示员工的姓名，工资，个人所得税，税率为0.03为例
declare
c_tax_rate number(3,2):=0.03;   --定义税率
--v_ename varchar2(5);  --定义用户名，这里定义的字符太短，超5个字符就出错
v_ename emp.ename%type; --可以加%type，和表emp的ename字段大小保持一致
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;
/

2、复合类型(composite)变量就像数组一样可存放多个值，定义一个复合类型变量相当地定义了一个结构体或类
例：
declare  --定义的关键字
type emp_record_type --定义了一个名为emp_record_type的记录类型
is
record(name emp.ename%type,salary emp.sal%type,title emp.job%type); --这个类型会包含三个数据，name,salary,title
sp_record  emp_record_type; --定义一名为sp_record变量，指定这个变量为上面定义emp_record_type类型
begin
select ename,sal,job into sp_record from emp where empno=7788;--sp_record这个复合变量包含3个数据类型
dbms_output.put_line('员工名：'||sp_record.name||'  工资是:'||sp_record.salary);
end;
/
注：pl/sql表，也是复合变量

3.数组变量
相当高级语言中的数组，有点不一样，下标可以为负数，但高级语言下标不能为负数
--pl/sql表实例
declare
type sp_table_type is table of emp.ename%type index by binary_integer;
  --定义了一个pl/sql表类型sp_table_type,该类型用于存放emp.emp%type类型的数据，
  --index by binary_integer表示下标是按整数排序，也可以是负整数
sp_table sp_table_type;  --定义了一个sp_table变量，变量类型是上面定义的sp_table_type类型
begin
select ename into sp_table(0) from emp where empno=7788;     --sp_table(0)，这里的0可以是任一整数，下面也要相同
dbms_output.put_line('员工名：'||sp_table(0)); --输出员工名
end;
/

4.参照变量
如果把where条件去掉就会用参照变量 参照类型
用的最多的游标变量ref cursor，一个指针在那里来回指，还有对象类型变量，
定义游标时不指定select语句，使用时才需要指定

使用pl/sql编写一个块，可输入部门号，并显示该部门所有员工姓名和工资
declare
type sp_emp_cursor is ref cursor;--定义sp_emp_cursor是ref cursor游标类型
test_cursor sp_emp_cursor;
v_ename emp.ename%type; --定义变量
v_sal emp.sal%type;
begin
--把test_cursor和一个select结合，因为test_cursor为一个结果集，这个结果集是用select查出来的
open test_cursor for select ename,sal from emp where deptno=&no;   --把这个游标指向这个结果集
loop  --开始循环取出
   fetch test_cursor into v_ename,v_sal; --把游标test_cursor里的内容取出到v_ename,和v_sal中
   exit when test_cursor%notfound;  --判断当test_cursor为空退出
   dbms_output.put_line('名字是：'||v_ename||'工资是：'||v_sal);
end loop;
end;
/
注：最后输入部门号，才能输出，不是用户号