Oracle存储过程、包详解

1.1、只包括执行部分的pl/sql块
SQL>set serveroutput on --打开输出选项   
begin    /*执行部分——要执行的pl/sql语句和sql语句*/
dbms_output.put_line('hello');   
end;  
/
/*dbms_output是oracle所提供的包(类似java的开发包)，该包包含一些过程，put_line就是dbms_output包的一个过程*/
包含定义部分和执行部分的pl/sql块
1.2、包含定义部分和执行部分的pl/sql块
SQL>declare  
v_ename varchar2(5); --定义字符串变量   
begin  
select ename into v_ename from emp where empno=&aa;   
dbms_output.put_line('雇员名：'||v_ename);   
end;   
/  
1.3、  包含定义部分，执行部分和例外处理部分的pl/sql块
SQL>declare  
--定义变量   
v_ename varchar2(5);   
v_sal  number(7,2);   
begin  
--执行部分   
select ename,sal into v_ename,v_sal from emp where empno=&aa;   
--在控制台显示用户名   
dbms_output.put_line('用户名是：'||v_ename||' 工资：'||v_sal);   
--异常处理   
exception   
when no_data_found then  
dbms_output.put_line('朋友，你的编号输入有误！')；   
end;   
/  

2.1、过程
SQL>create procedure sp_pro(spName varchar2, newSal number) is 
begin 
--执行部分，根据用户名去修改工资
update emp set sal=newSal where ename=spName;
end;
/ 
SQL>exec sp_pro1('SCOTT',4556);   --调用
2.2、函数
--输入雇员的姓名，返回该雇员的年薪  
SQL>create function annual_incomec(name varchar2)   
return number is  
annual_salazy number(7,2);   
begin  
--执行部分   
select sal*12+nvl(comm, 0) into annual_salazy from emp where ename=name;   
return annual_salazy;   
end;   
/  
--在sqlplus中调用函数 
SQL>var income number
call annual_incomec('SCOTT') into: income;
print income
2.3、  包
--包用于在逻辑上组合过程和函数，它由包规范和包体两部分组成。 
实例1： 
--创建一个包sp_package 
--声明该包有一个过程update_sal 
--声明该包有一个函数annual_income 
SQL>create package sp_package is  
procedure update_sal(name varchar2, newsal number);   
function annual_income(name varchar2) return number;   
end;  
/
实例2：
--给包sp_package实现包体 
SQL>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;   
/  
如何调用包的过程或是函数 
SQL> call sp_package.update_sal('SCOTT', 1500);
2.4、触发器
常用的触发事件包括insert,update,delete语句，而触发操作实际就是一个pl/sql块。可以使用create trigger来建立触发器。 

3.1、标量定义的案例
定义一个变长字符串 
v_ename varchar2(10); 
定义一个小数,范围 -9999.99~9999.99 
v_sal number(6,2); 
定义一个小数并给一个初始值为5.4 :=是pl/sql的赋值号 
v_sal2 number(6,2):=5.4; 
定义一个日期类型的数据
v_hiredate date; 
定义一个布尔变量，不能为空，初始值为false 
v_valid boolean not null default false; 
案例1：
SQL> 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;   
/  
输出的结果是：
姓名是：SCOTT工资：2900 交税：87
PL/SQL procedure successfully completed
案例2：
标量(scalar)——使用%type类型
比如上例的v_ename，这样定义： v_ename emp.ename%type; 
3.2、复合变量(composite)
用于存放多个值的变量。主要包括这几种： 
 pl/sql记录 
 pl/sql表 
 嵌套表
 varray 
3.2.1、pl/sql记录
类似于高级语言中的结构体，需要注意的是，当引用pl/sql记录成员时，必须要加记录变量作为前缀(记录变量.记录成员)如下：
SQL>declare  
--定义一个pl/sql记录类型emp_record_type,类型包含3个数据name,salary,title。说白了，就是一个类型可以存放3个数据，主要是为了好管理   
type emp_record_type is record(   
name   emp.ename%type,   
salary emp.sal%type,   
title  emp.job%type);   
--定义了一个sp_record变量，这个变量的类型是emp_record_type   
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;  
/
3.2.2、pl/sql表
相当于高级语言中的数组，但是需要注意的是在高级语言中数组的下标不能为负数，而pl/sql是可以为负数的，并且表元素的下标没有限制。实例如下： 
SQL>declare  
--定义了一个pl/sql表类型sp_table_type，该类型是用于存放emp.ename%type   
--index by binary_integer 表示下标是整数   
type sp_table_type is table of emp.ename%type    
index by binary_integer;   
--定义了一个sp_table变量，这个变量的类型是sp_table_type   
sp_table sp_table_type;   
begin  
select ename into sp_table(-1) from emp where empno = 7788;   
dbms_output.put_line('员工名:' || sp_table(-1));   
end;  
/
说明： 
sp_table_type 是pl/sql表类型 
emp.ename%type 指定了表的元素的类型和长度 
sp_table 为pl/sql表变量 
sp_table(0) 则表示下标为0的元素 
注意：如果把select ename into sp_table(-1) from emp where empno = 7788;变成select ename into sp_table(-1) from emp;则运行时会出现错误，错误如下： 
ORA-01422:实际返回的行数超出请求的行数 
解决方法是：使用参照变量