存储过程笔记

/***************************工作整理**********************/

/*
  存储过程 Procedure
  创建存储过程必须有create procedure权限
*/

--打开显示
set serveroutput on;
--创建一个存储过程
create or replace procedure p_update_emp
as
 cursor c is
  select * from emp for update;
begin
 for v_emp in c loop
  if(v_emp.deptno = 10) then
   update emp set sal=sal+10 where current of c;
  elsif(v_emp.deptno = 20) then
   update emp set sal=sal+20 where current of c;
  else
   update emp set sal=sal+50 where current of c;
  end if ;
 end loop;
 commit;
end;
/

--查询所有存储过程
/*
  这里比较好玩:根据一般数据字典表中的列命名,这里p_update应为procedure_name
  但是不是,它是object_name,procedure_name为空
*/
select * from user_procedures;

--执行存储过程 (方式一)
begin
  p_update_emp;
end;
--执行存储过程 (方式二)
exec p_update_emp;

--创建带参数存储过程
/*
 in : 表输入参数
 out: 表输出参数
 不加关键字的表示默认输入参数
 两个都加的既表示输入参数又表示输出参数

 注意：存储过程的参数只能指定类型，而不能指定大小
 如  v_a number(2)是错误的，正确为v_a number
*/
create or replace procedure p_in_out
 (v_a in number,v_b number,v_ret out number,v_temp in out number)
as
 begin
  if(v_a > v_b) then
   v_ret := v_a ;
  else
   v_ret := v_b ;
  end if ;
  v_temp := v_temp +1 ;
 end;
/

--执行带参存储过程
declare
 v_a number := 3 ;
 v_b number := 4 ;
 v_ret number;
 v_temp number := 5 ;
begin
 p_in_out(v_a,v_b,v_ret,v_temp);
 dbms_output.put_line(v_ret);
 dbms_output.put_line(v_temp);
end;
/
--结果打印 4 和 6

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

/***************************学校整理********************************/

【存储过程输出】

存储过程思路：

1: 查询语句
2：确定参数
3：调用执行，实参赋值形参。

show error;     //查看程序错误

 

创建一个存储过程，以部门号为参数，输出该部门的平均工资。

create or replace procedure //create创建，or replace可以重复名，procedure关键字。

show_emp                  //存储过程的名字

(p_deptno emp.deptno%type)//向存储过程传送的参数

as                        //定义游标，变量部分

v_sal emp.sal%type;     

begin

select avg(sal) into v_sal
from emp
where deptno=p_deptno;
dbms_output.put_line(v_sal);

end;

exec show_emp(10);//执行语句

游标编译一次执行一次
存储过程编译一次可多次运行
存储过程与游标的应用

 create or replace procedure show_emp
 as
  Cursor myCur is select * from emp;
 begin
  for v_emp in mycur loop
        dbms_output.put_line(v_emp.empno||'    '||v_emp.ename||'    '||v_emp.sal);
    end loop;
 end;

 

 

 create or replace procedure show_emp
 as
 Cursor myCur is select avg(sal) avgsal from emp group by deptno;
 
 begin
 for v_emp in mycur loop
         dbms_output.put_line(v_emp.avgsal);
     end loop;
 end;

 

 

 

//当需要多个返回值的时候用out，【返回值，可以送到前台页面】
create or replace procedure return_dept(p_deptno emp.deptno%type,
                                        p_avgsal out emp.sal%type,p_count out number)
 as

begin

 select avg(sal),count(*) into p_avgsal ,p_count
from emp
where deptno=p_deptno;

end;

//功能类似于exec show_emp(10);
declare
 p_avgsal emp.sal%type;
 p_count number;
begin
  return_dept(10,p_avgsal,p_count);
  dbms_output.put_line(p_avgsal||'       '||p_count);
end;

 

【存储过程返回值】

创建一个存储过程，以部门号为参数，输出该部门的平均工资。[PL/SQL,复杂SQL。dbms_output.put_line();不能送到前台页面。]
参数:
type:
输出：dbms_output.put_line
select avg(sal)
from emp
where deptno=10;
create or replace procedure show_emp(p_deptno emp.deptno%type)
as
   v_sal emp.sal%type;
begin
select avg(sal) into v_sal
from emp
where deptno=p_deptno;
dbms_output.put_line(v_sal);
end;
exec show_emp(10);

创建一个存储过程,以部门号为参数，返回该部门的人数和平均工资。【返回值，可以送到前台页面】

参数实现返回值，OUT，而且必须写。
多参数，而且多个out参数，必须用pl/sql程序块调用存储过程。

select avg(sal),count(*) into p_avgsal,p_count
from emp
where deptno=10;

 

create or replace procedure return_dept(p_deptno emp.deptno%type,
                                        p_avgsal out emp.sal%type,p_count out number)
 as

begin

 select avg(sal),count(*) into p_avgsal ,p_count
from emp
where deptno=p_deptno;

end;

declare
 p_avgsal emp.sal%type;
 p_count number;
begin
  return_dept(10,p_avgsal,p_count);
  dbms_output.put_line(p_avgsal||'       '||p_count);
end;
注意：out，一定先定义，再使用。dbms_output.put_line为了在sql*plus看到结果。

 

创建一个存储过程,输出各个员工信息。

 create or replace procedure show_emp
 as
  Cursor myCur is select * from emp;
 begin
  for v_emp in mycur loop
        dbms_output.put_line(v_emp.empno||'    '||v_emp.ename||'    '||v_emp.sal);
    end loop;
 end;
exec show_emp;

 create or replace procedure show_emp
 as
 Cursor myCur is select avg(sal) avgsal from emp group by deptno;
 
 begin
 for v_emp in mycur loop
         dbms_output.put_line(v_emp.avgsal);
     end loop;
 end;

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