1、什么是存储过程
存储过程Procedure是一组为了完成特定功能的SQL语句集合，经编译后存储在数据库中，用户通过指定存储过程的名称并给出参数来执行。它可以接受参数、输出参数，并可以返回单个或多个结果集以及返回值。
2、存储过程基本语法
存储过程的一般格式如下：
CREATE [OR REPLACE] PROCEDURE procedure_name
IS [AS]
声明部分
BEGIN
执行部分
EXCEPTION
异常处理部分
END;
调用存储过程：
call procedure_name();
---------------------------------
exec procedure_name();
---------------------------------
begin
pro_update_emp();
end;
写一个简单的存储过程使emp表的sal值增加300。
CREATE or replace procedure pro_update_emp
as
begin
update emp set sal=sal+300;
end;
调用存储过程
call pro_update_emp ();
3、数据类型
3.1 %type 数据类型：
当使用%TYPE属性定义变量时，Oracle会自动地按照数据库表中相应的列来确定新变量的类型和长度。
如下，将emp表的ename字段的数据类型（如 ‘varchar(2)’）赋给变量 v_ename
v_ename emp.ename%type
3.2 %ROWTYPE数据类型：
如果一张表中包含较多的列，则可以使用%ROWTYPE来表示表中一行记录的变量的数据类型。
如下：将dept表中一行中各字段的数据类型(‘number’,’varchar2(50)’,varchar2(50))赋给v_dept_row,非常便利，可以直接查询后将一行数据赋值。
（注：语句中 into 执行赋值操作，将查询结果直接赋值给 v_dept_row）
CREATE or replace procedure pro_update_emp
as
v_dept_row dept%rowtype;
begin
select * into v_dept_row from dept where deptno=11;
end;
3.3 %record数据类型：
自定义记录的数据类型，声明一个行数据类型，将每列的数据类型进行自定义。
type emp_record_type is RECORD( //声明自定义数据类型的名字为 emp_record_type
ename emp.ename%type,
sal emp.sal%type,
comm emp.comm%type,
total_sal sal%type
);
v_emp_record emp_record_type; //定义变量v_emp_record的数据类型为 emp_record_type
3.4 TABLE数据类型：
TABLE(索引表)相当于一个键值集合，键是唯一的，用于查找对应的值。键可以是整数或字符串。
declare
type dept_table_type is table of dept%rowtype index by binary_integer; //声明table数据类型 dept_table_type
v_dept_table dept_table_type; //声明v_dept_table的数据类型为 dept_table_type
begin
select * into v_dept_table(0) from dept where deptno=11; //按索引查询并赋值
select * into v_dept_table(1) from dept where deptno=12;
end;
4、带参数的存储过程
4.1 输入参数 (IN) ：当为过程定义参数时，如果不指定参数模式，则默认为输入参数
（注：使用输出语句之前应提前运行打开输出环境变量语句’set serveroutput on’）
create or replace procedure
pro_query_emp0(v_no in emp.empno%type) //in 代表输入参数，可省略
as
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno=v_no; //将会有提醒输入v_no参数的值
dbms_output.put_line('该员工薪水为：'||v_sal); //输出语句
exception
when no_data_found then
dbms_output.put_line('找不到该员工！');
end;
调用：
call pro_query_emp0(11)
4.2 输出参数 (OUT) ：将查询结果赋值给输入参数 out_sql中
create or replace procedure
pro_query_emp(v_no in emp.empno%type, out_sal out number)
as
begin
select sal into out_sal from emp where empno=v_no;
exception
when no_data_found then
dbms_output.put_line('找不到该员工！');
end;
调用：
declare
v_no emp.empno%type;
v_sal emp.sal%type;
begin
v_no:=&no;
pro_query_emp(v_no,v_sal);
dbms_output.put_line('薪水是：'||v_sal);
end;
4.3 输入输出参数 (IN OUT)：既作输入参数，又作输出参数
create or replace procedure pro_testinout
(param_num in out number)
as
begin
select sal into param_num from emp
where empno=param_num;
end;
调用：
declare
inout_num number;
begin
inout_num:=&no;
pro_testinout(inout_num);
dbms_output.put_line('工资是：'||inout_num);
end;
5、三种传值方式
先创建一个存储过程
create or replace procedure
pro_add_dept(v_deptno number,v_dname varchar2, v_loc varchar2)
as
begin
insert into dept values(v_deptno,v_dname,v_loc);
end;
按位置传值：
exec pro_add_dept(70,'研发部','北京');
按名称传值:
call pro_add_dept(v_deptno=>90,v_loc=>'南京',v_dname=>'软件部');
混合传值：
exec pro_add_dept(100,v_loc=>'南京',v_dname=>'软件部');
6、流程控制语句
6.1、条件控制语句 IF THEN
基本格式
IF 条件表达式1 THEN
语句段1
ELSIF 条件表达式2 THEN
语句段2
......
ELSIF 条件表达式n
语句段n
END IF;
实例：如果奖金comm为空，则将其更新为 v_emp.sal的0.1倍，如果小于1000则设为1000，如果以上条件都不满足，则将comm在原有的基础上加0.1倍。
if v_emp.comm is null then
update emp set comm=v_emp.sal*0.1 where empno=v_emp.empno;
elsIf v_emp.comm<1000 then
update emp set comm=1000 where empno=v_emp.empno;
else
update emp set comm=comm+comm*0.1 where empno=v_emp.empno;
end if;
6.2、条件控制语句 CASE
基本格式
CASE
WHEN 条件表达式1 THEN
语句段1;
WHEN 条件表达式2 THEN
语句段2;
......
ELSE
语句段n;
END CASE;
实例：将v_sal小于3000的评为A级工资，在3000至5000之间的评为B级工资，将其他的评为C级工资。
case
when v_sal<3000 then dbms_output.put_line('A级工资');
when v_sal>=3000 and v_sal<5000 then dbms_output.put_line('B级工资');
else dbms_output.put_line('C级工资');
end case;
6.3、LOOP 循环
基本格式：
LOOP
EXIT [WHEN 条件表达式]
语句段;
END LOOP;
实例：循环向dept中插入数据
create or replace procedure pro_insert_dept
as
type dept_table_type is table of dept%rowtype index by binary_integer;
i number(1):=0;
v_dept_table dept_table_type;
begin
v_dept_table(0).deptno:='14';
v_dept_table(0).dname:='政法研发部';
v_dept_table(0).loc:='上海';
v_dept_table(1).deptno:='15';
v_dept_table(1).dname:='邮政金融部';
v_dept_table(1).loc:='北京';
v_dept_table(2).deptno:='16';
v_dept_table(2).dname:='系统集成部';
v_dept_table(2).loc:='深圳';
loop
exit when i>2;
insert into dept values( v_dept_table(i).deptno,v_dept_table(i).dname,v_dept_table(i).loc);
i:=i+1; //每次循环i+1，当i的值大于2时结束循环
end loop;
end;
6.3、While循环
基本格式：
WHILE 条件表达式 LOOP
语句段;
END LOOP;
实例：
while i<=2 loop
insert into dept values(v_dept_table(i).deptno,v_dept_table(i).dname,v_dept_table(i).loc);
i:=i+1;
end loop;
6.4 FOR循环
基本格式：
（注：如加上reverse表示倒叙循环执行语句）
FOR 循环变量 in [REVERSE] 初值表达式..终值表达式 LOOP
语句段;
END LOOP;
实例：
for i in 0..v_dept_table.count-1 loop
insert into dept values( v_dept_table(i).deptno,v_dept_table(i).dname,v_dept_table(i).loc);
end loop;