Oracle学习笔记之存储过程
存储过程
在Oracle中，可以在数据库中定义子程序，这种程序块称为存储过程(procedure)。它存放在数据字典中，可以在不同用户和应用程序之间共享，并可实现程序的优化和重用。使用存储过程的优点是：
(1) 过程在服务器端运行，执行速度快。
(2) 过程执行一次后代码就驻留在高速缓冲存储器，在以后的操作中，只需从高速缓冲存储器中调用已编译代码执行，提高了系统性能。
(3) 确保数据库的安全。可以不授权用户直接访问应用程序中的一些表，而是授权用户执行访问这些表的过程。非表的授权用户除非通过过程，否则就不能访问这些表。
(4) 自动完成需要预先执行的任务。过程可以在系统启动时自动执行，而不必在系统启动后再进行手工操作，大大方便了用户的使用，可以自动完成一些需要预先执行的任务。
注意：在用户的定义中不能使用下列对象创建语句：
CREATE VIEW、CREATE DEFAULT 、CREATE RULE 、CREATE PROCEDURE 、CREATE TRIGGER
SQL命令创建存储过程:
语法格式：
CREATE [OR REPLACE] PROCEDURE [schema.]procedure_name 如下通过实例描述存储过程结构和写法：
例如：[A1]
写一个存储过程：
例如：该存储过程根据人以部门编号，查询该部门中员工工资以及员工编号，如果如果员工工资低于该部门平均工资，此时将该员工工资涨薪为平均工资；
例如：上面题加一个条件，张薪资以后，将涨薪资的人员信息以游标形式返回；
例如：写一个存储过程将emp中empno和ename查询结果以out类型游标返回；
包中声明游标：
createorreplacepackage t is
type record_type isrecord(empno number,ename varchar2(20));
type cursor_type isrefcursorreturn record_type;
end t;
存储过程代码：
createorreplaceprocedure myProcedure(cur_out out t.cursor_type)is
begin
open cur_out forselect empno,ename from emp;
end myProcedure;
测试代码：
-- Created on 2017/10/19 by ADMINISTRATOR
declare
type cur_type isrefcursor;-- 弱类型游标
cur cur_type;
type record_type isrecord(empno number,ename varchar2(20));
employee record_type;
begin
myprocedure(cur);
loop
fetch cur into employee;
dbms_output.put_line(employee.ename||':'||employee.empno);
exitwhen cur%notfound;
endloop;
end;
实例2：
方法：
create or replace function login(username in varchar2,pwd in varchar2) return number is
res number(5);
flag number(1):=0;
begin
select count(*) into res from emp where ename=username and empno=pwd;
if res>0 then
flag:=1;
end if;
return flag;
end login;
存储过程：
create or replace procedure EmpProcedure(username in varchar2,pwd in varchar2,controlFlag in varchar2,
cur_emp out pac.cur_type1,cur_dpet out pac.cur_type2
) is
flag number(1);
begin
flag:=login(username,pwd);--验证用户是否登陆成功
if flag=1 --登陆成功
then
--判断controlFlag 1 查看该员工信息 2 查看emp表数据 3 查看该员工部门信息
case controlFlag
when 1 then --查看该员工信息
open cur_emp for
select * from emp where empno=pwd;
when 2 then
open cur_emp for
select * from emp;
when 3 then
open cur_dpet for
select * from dept where deptno=(select deptno from emp where emp.empno=pwd);
end case;
else--登陆失败
dbms_output.put_line('登陆失败');
end if;
end EmpProcedure;
包：
type r is record(empno number(5),ename varchar2(20),job varchar2(20),mgr number(5),hiredate date,sal number(15,2),comm number(15,2),deptno number(5));
type cur_type1 is ref cursor return r;
type p is record(deptno number(5),dname varchar2(20),loc varchar2(20));
type cur_type2 is ref cursor return p;
测试：
-- Created on 2017/10/21 by ADMINISTRATOR
declare
type r is record(empno number(5),ename varchar2(20),job varchar2(20),mgr number(5),hiredate date,sal number(15,2),comm number(15,2),deptno number(5));
type cur_type1 is ref cursor ;--注意不能声明为return 强制类型
type p is record(deptno number(5),dname varchar2(20),loc varchar2(20));
type cur_type2 is ref cursor;
cur1 cur_type1;
cur2 cur_type2;
res_1 r;
res_2 p;
flag number(1):=2;
begin
empprocedure('SMITH','123',flag,cur1,cur2);
if flag=1 or flag=2 then
loop
fetch cur1 into res_1;
exit when cur1%notfound;
dbms_output.put_line(res_1.empno||res_1.ename||res_1.hiredate);
end loop;
elsif flag=3 then
loop
fetch cur2 into res_2;
exit when cur2%notfound;
dbms_output.put_line(res_2.deptno||res_2.dname||res_2.loc);
end loop;
end if;
Exception when others then dbms_output.put_line('系统异常');
end;
305上课实例：
包：
create or replace package empdeptPac is
type cur_empType is ref cursor return emp%rowtype;
type cur_deptType is ref cursor return dept%rowtype;
end empdeptPac;
函数：
create or replace function emplogin(username in varchar2,pwd in varchar2) return boolean is
total number(10);
flag boolean :=false;
begin
select count(*) into total from emp where ename =username and empno=pwd;
if total>0 then
flag:=true;
else flag:=false;
end if;
return flag;
end emplogin;
存储过程：
create or replace procedure empdeptpro(username in varchar2,pwd in varchar2,flag in varchar2,
empitems out empdeptpac.cur_empType, deptitems out empdeptpac.cur_deptType, empres out emp%rowtype
) is
f boolean ;
begin
f:=emplogin(username,pwd);
if f then
case flag
when 'A' then--查询所有员工信息
open empitems for select * from emp;
when 'B' then--查询所在部门信息
open deptitems for select dept.* from emp,dept where dept.deptno=emp.deptno and emp.empno=pwd;
when 'C' then--查询个人信息
select emp.* into empres from emp where emp.empno=pwd;
end case;
end if;
end empdeptpro;
测试：
-- Created on 2017/11/4 by ADMINISTRATOR
declare
type curtype_dept is ref cursor;
type curtype_emp is ref cursor ;
cur_dept curtype_dept;
cur_emp curtype_emp;
empRow emp%rowtype;
begin
empdeptpro('SMITH',7369,'A',cur_emp,cur_dept,empRow);
loop
fetch cur_emp into empRow;
dbms_output.put_line(empRow.empno);
exit when cur_emp%notfound;
end loop;
end;
① IN：表示参数是输入给过程的；
② OUT：表示参数在过程中将被赋值，可以传给过程体的外部；
③ IN OUT：表示该类型的参数既可以向过程体传值，也可以在过程体中赋值。
