Oracle-存储过程提高篇

1.Rownum的应用

select deptno,count(*) personNum ,avg(sal) avgSal from scott.emp group by deptno order by personNum desc;

select deptno,personNum,avgSal from (
       select deptno,count(*) personNum ,avg(sal) avgSal from scott.emp group by deptno order by personNum desc
       ) 
where rownum <=1;

2.编写一个存储过程，计算显示部门人数最多的部门号,人数及平均工资。

create or replace procedure P1
is
  v_deptno scott.emp.deptno%type; 
  v_pn number;
  v_avg scott.emp.sal%type;
begin
   select deptno,PersonNum,avgSal into v_deptno,v_pn,v_avg from (
       select deptno,count(*) personNum ,avg(sal) avgSal from scott.emp group by deptno order by personNum desc
       ) 
   where rownum <=1;
  dbms_output.put_line(v_deptno||' '||v_pn||' '||v_avg);
end P1;

调用：
begin
  P1;
end;

3.返回多个值的存储过程.创建一个存储过程，以部门号为参数，返回该部门的人数和平均工资

create or replace procedure P2
(
       v_deptno in scott.emp.deptno%type,
       personNum out number,
       avgSal out scott.emp.sal%type
)
is
begin
   select count(*),avg(sal) into personNum,avgSal from scott.emp where deptno = v_deptno;
exception 
     when NO_DATA_FOUND then 
       DBMS_OUTPUT.PUT_LINE('The department don’t exists!');
end P2;


调用：
declare
   personNum number;
   avgSal  scott.emp.sal%type;
begin 
  P2(30,personNum,avgSal);
  DBMS_OUTPUT.PUT_LINE(personNum||'----------------------'||avgSal);
end;

4.创建一个存储过程，以部门号为参数，返回该部门的人数和最高工资。

create or replace procedure P3
(
     v_deptno in scott.emp.deptno%type,
     personNum out number,
     maxSal out scott.emp.sal%type
)
as
begin
  select count(*),max(sal) into personNum,maxSal from scott.emp where deptno = v_deptno;
exception 
     when NO_DATA_FOUND then 
       DBMS_OUTPUT.PUT_LINE('The department don’t exists!');
end P3;
调用：
declare
   personNum number;
   maxSal  scott.emp.sal%type;
begin 
  P3(30,personNum,maxSal);
  DBMS_OUTPUT.PUT_LINE(personNum||'----------------------'||maxSal);
end;

select count(*),max(sal) from scott.emp where deptno = 30;

5.创建一存储过程，计算全体学生某门课程的平均成绩。（思考有一in 模式参数和一out模式参数）

create or replace procedure P4
(
     p_kch IN xs_kc.kch%TYPE,
     p_avg OUT xs_kc.cj%TYPE  
)
as
begin
     select avg(cj) into p_avg  from xs_kc where kch  = p_kch;
end P4;

调用：
declare
    p_avg  xs_kc.cj%TYPE;
begin
  P4('101',p_avg);
  DBMS_OUTPUT.PUT_LINE('----------------------'||p_avg);
end;

select avg(cj)   from xs_kc where kch  = 101;

6.存储过程和游标结合的例子:

创建一个存储过程，以部门号为该存储过程的in类型参数，
查询该部门的平均工资，并输出该部门中比平均工资高的员工号、员工名。(for 循环遍历 游标)

create or replace procedure P5
( v_deptno in scott.emp.deptno%type)
as 
  v_sal scott.emp.sal%type;
begin
 select avg(sal) into v_sal from scott.emp where deptno = v_deptno ;
 DBMS_OUTPUT.PUT_LINE(v_deptno||'---号部门的平均工资为-----'||v_sal);
 for v_emp in (select * FROM  scott.emp WHERE deptno=p_deptno AND sal>v_sal)
   loop
     DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||v_emp.ename);
     end loop;
end P5;

调用：
begin
 P5(20);
end;

7.用存储过程进行模糊查找，如查找ename中包含L的雇员信息

create or replace procedure P6
(varEmpName Scott.emp.ename%type)
as
  cursor c1 is select * from scott.emp where ename like '%'||varEmpName||'%';
begin
  for v_1 in c1
    loop
      dbms_output.put_line(v_1.empno||' '||v_1.ename||' '||v_1.job||' '||v_1.deptno);
      end loop;
end P6;

调用：
begin
  P6('L');
end;

8.修改存储过程

CREATE OR REPLACE PROCEDURE
删除存储过程
DROP PROCEDURE procedure_name;

9.存储过程注意的五个地方：

1、SQL 语言出错（何时用IN 参数，何时用OUT参数）。
2、SQL*PLUS 出错 （什么操作，可以在什么用户下）。
3、Procedure的框架出错（过程头和过程体出错，特别是类型的说明特例）。
4、何时需要用到游标，游标的遍历出错。
5、存储过程调用时，注意实参（形参是OUT参数的必须有实参，另外IN参数的值传递给过程体内后是否有意义 ）。

10.小结

分为定义和调用两部分
定义（in, out ,in out）
调用：程序块调用
begin
       Procedure_name(参数列表);
end;       
注意：in  参数和out 参数在调用时的区别，out 参数需定义为变量。
注意当返回信息为结果集时，存储过程中应该包含游标