Oracle --存储过程

创建语法：
     CRATE [OR REPLACE] PROCEDURE<过程名>
           (<参数1>,[方式1]<数据类型1>,
            <参数2>,[方式2]<数据类型2> 
           
          ... )
          IS|AS
          PL/SQL过程体;
          
参数类型
  输入和输出、输入即输出参数
  A）输入参数
    在过程执行中，如果需要从外部传入数据执行过程，需要提供执行的时候提供输入参数。
		参数命名：in
  B）输出参数
		在获得输入参数执行过程的时候，如果需要获得过程的返回，需要提供输出参数来进行数据
		的返回。
		参数命名：out
  C）输入即输出: in out 
在执行过程的操作中，作为参数输入的操作中，过程将其参数输入数据进行处理后，需要原方式返回给执行者操作的时候，需要在输入参数的基础上将其定义成输出参数。
参数命名：in out

--数据准备
create table t_dept as select *  from dept;
--完整示例：
-- 通过存储过程添加新部门到t_dept表中
-- loc varchar2 :='New York' 指定默认值
create or replace procedure new_dept
  (d_name in varchar2,loc in  varchar2 )
 is
   dept_no number;
  begin 
     -- 先得到当前部门表的最大部门编号
     select max(deptno) into dept_no from t_dept; 
     --在当前最大部门编号的基础上+10作为新部门的部门编号
     dept_no:=dept_no+10;
     --将数据添加到部门表中
     insert  into t_dept values (dept_no,d_name,loc) ;
     commit;
     exception 
        when others then 
        dbms_output.put_line('新增部门失败');
end ;

--调用存储过程
declare
  dname varchar2(30):='研发部';
  loc varchar2(30):='北京';
begin
    new_dept(dname, loc);
    --new_dept(dname);
end;

--删除存储过程
drop procedure new_dept;

/*
in参数类型 这是个输入类型的参数，表示这个参数值输入给过程，供过程使用
*/ 
--完成将一个数成倍增加
create or replace procedure e_double(in_num in number,out_num out number)
as
begin
       out_num:=in_num*2;
end;

declare
   vnum number:= 20;
   vresult number;
begin
      e_double(vnum, vresult);
      dbms_output.put_line(vresult);   
end;

/*
out参数类型 这是个输出类型的参数，表示这个参数在过程中被赋值，可以传给过程体以外的部分或环境
in out参数类型  这种参数类型其实是综合了上述两种参数类型，既向过程体传值，在过程体中也被赋值
而传向过程体外
*/
--示例:根据部门编号得到部门名称和该部门的平均工资
create or replace  procedure dept_sal 
  (deptinfo in out varchar2,avgsal out number)
 is 
  dept_no number; -- 声明变量
begin
       dept_no :=to_number(deptinfo); -- 得到部门编号参数
       select  dname into deptinfo from dept where deptno=dept_no; -- 将参数设置为部门名称
       select avg(sal) into avgsal from emp where deptno=dept_no; -- 得到部门的平均工资
end;

declare 
  d_name varchar2(20);
  avg_sal number;
begin
  d_name:='&部门编号';
  dept_sal(d_name,avg_sal);
  dbms_output.put_line('部门名称:' || d_name|| ',平均工资:' ||avg_sal);
end;

--存储过程综合案例(返回指定部门的员工信息：编号，姓名，部门编号,部门名称，工资，员工所在部门平均工资)

/*
1.创建视图
2.创建存储过程（返回结果集的游标 c_empavg out sys_refcursor）
3.调用存储过程 输入编号，获得结果集（c_empavg）
4.循环游标，打印提示信息
*/
 
--创建视图
create or replace view vi_emp_avgsal(empno,ename,deptno,dname,sal,avgsal)
as 
select a.empno,a.ename,a.deptno,a.dname,a.sal,b.avgsal from
(select e.empno,e.ename,e.deptno,d.dname,e.sal from emp e,dept d where e.deptno=d.deptno) a,
(select deptno,round(avg(sal),2) as avgsal from emp group by deptno) b
where a.deptno = b.deptno;


select * from vi_emp_avgsal;


--创建存储过程（返回结果集的游标 c_empavg out sys_refcursor）
--游标做输出参数时，类型为sys_refcursor
create or replace procedure p_emp_avgsal(v_deptno in number,c_empavg out sys_refcursor)
as 
  e_deptno exception;
begin
  if v_deptno<0 then
    raise e_deptno;
  end if;
  open c_empavg for 'select * from vi_emp_avgsal where deptno =:v_temp_deptno' using v_deptno;
exception 
  when e_deptno then
     dbms_output.put_line('e_deptno exception ');
     when others then
       dbms_output.put_line('unkonw error exception ');
end;

--调用存储过程
declare
  type t_empavg is ref cursor;
  c_empavg t_empavg;
  v_avgsalrow vi_emp_avgsal%rowtype;
  v_deptno number;
begin
  v_deptno :='&请输入部门编号：';
  p_emp_avgsal(v_deptno,c_empavg);
  loop
      fetch c_empavg into v_avgsalrow;
      exit when c_empavg%notfound;
      dbms_output.put_line('v_avgsalrow.deptno '||v_avgsalrow.deptno);
      dbms_output.put_line('v_avgsalrow.avgsal '||v_avgsalrow.avgsal);
  end loop;
  if c_empavg%isopen then
    close c_empavg;
  end if;
exception 
  when others then
       dbms_output.put_line('unkonw error exception ');
end;

1.假设有张学生成绩表如下:
[姓名][学科][成绩]
 张三  语文   80
 张三  数学   86
 张三  英语   75
 李四  语文   78
 李四  数学   85
 李四  英语   78
现有需求如下 ：
（1）要求根据姓名，把各科成绩显示在一条记录里。显示结果如下：
姓名      语文    数学   英语   总成绩
--------------------------------------------------
李四       78      85     78      241
张三       80      86     75      241
总分      158      171    153     482
使用存储过程实现


--行转列 case when
--多表连接查询 扩充列
--union 关键字 行结果集相加（扩充行）

create or replace view vi_st_sc(name,chinese,math,english,totalscore)
as(
select name,
sum(case when subject='语文' then score else 0 end) as chinese,
sum(case when subject='数学' then score else 0 end) as math,
sum(case when subject='英语' then score else 0 end) as english,
sum(score) as totalscore
from student group by name
)

--利用视图查询结果集
select name,chinese,math,english,totalscore from vi_st_sc
union
select '总分'，sum(chinese),sum(math),sum(english),sum(totalscore) from vi_st_sc;

create or replace procedure pr_stu_in(var_cur out sys_refcursor)
is 
begin
  open var_cur for 
  select name,chinese,math,english,totalscore from vi_st_sc
  union
  select '总分'，sum(chinese),sum(math),sum(english),sum(totalscore) from vi_st_sc;
exception
  when others then
    dbms_output.put_line('unkonw error exception ');
end;    

declare 
    type ref_cursor is ref cursor;
    c_student ref_cursor;
    v_studet vi_st_sc%rowtype;
begin
    pr_stu_in(c_student);
    loop
      fetch  c_student into v_studet;
      exit when c_student%notfound;
       dbms_output.put_line('v_studet.chinese '||v_studet.chinese||' v_studet.math '||v_studet.math);
    end loop;
    if c_student%isopen then
      close c_student;
    end if;
end;