oracle存储过程笔记记录

原文百度文库地址：http://wenku.baidu.com/view/73f3ad1a59eef8c75fbfb392.html

一、存储过程和函数的学习
1）、存储过程函数的概念：
开发一个存储过程或函数目的是把一个PL/SQL块存进数据库中，并在以后重复使用。
例如：创建一个记录用户名和当前日期的无参数过程
create procedure log_execution is
begin
  insert into log_table(user_id,log_date)
  values(user,sysdate);
end;
/

调用存储过程 execute log_execution

2）、创建存储过程的语法
create [or replace] precedure 过程名
  [(参数名[in|out|in out] 数据类型，...)]
{is|as}
  [说明部分]
begin
  语句序列
  [exception 出错处理]
end [过程名];

例如：下列存储过程给某一指定的员工涨指定数量的工资
create precedure raise_salary
  (emp_id  integer, v_increase real)
is
begin
  update emp set sal=sal+v_increase where empno=emp_id;
  commit;
end;
/
例如：下列过程根据给定的员工号返回员工的姓名、工资和奖金等信息。
create or replace precedure query_emp
  (v_emp_no in emp.empno%type,
   v_emp_name out emp.ename%type,
   v_emp_sal out emp.sal%type,
   v_emp_comm out emp.comm%type)
is
begin
  select ename, sal, comm into v_emp_name, v_emp_sal,v_emp_comm
    from emp where empno=v_emp_no;
end query_emp;
/

3）、创建存储函数的语法
create [or replace] function
  函数名[(参数名 [in] 数据类型...)]
    return 数据类型
{is|as}
  [说明部分]
begin
    语句序列
  return （表达式）
    [excetpion 例外处理程序]
end [函数名];

例如：根据员工号获取该员工工资的查询
create or replace function get_sal(v_empno in emp.empno%type)
return numble
is
v_sal emp.sal%type:=0
begin
    select sal into v_sal from emp where empno=v_empno;
  return (v_sal);
exception
  when no_date_found or too_many_rows then
    dbms_output.put_line('发生系统错误');
  when others then
    dbms_output.put_line(sqlerrm);
end get_sal;
/
注意：is后面的说明部分v_sal emp.sal%type:=0，要赋初始值；return (v_sal)要有

括号

例如：编写一个函数，计算几个人的平均工资，并在函数体的循环过程中输出结果。
create or replace function average_sal(v_n in number(3))
return number
is
  cursor c_emp is select empno,sal from emp;
  v_total_sal emp.sal%type:=0;
  v_counter number;
  v_emp_no emp.empno%type;
begin
  for r_emp in c_emp in c_emp loop
    exit when c_emp% rowcount>v_n or c_emp%notfound;
  v_total_sal:=v_total_sal+r_emp.sal;
  v_counter:=c_emp%rowcount;
  v_emp_no:=r_emp.empno;
  dbms_output.put_line('loop='||v_counter||'; Empno='||v_emp_no);
  end loop;
  return (v_total_sal/v_counter);
end average_sal;
/
这段代码我也看不懂，不知道r_emp这是干嘛的。不知道后面那段逻辑的意思。

4）、存储过程和函数的区别
①返回值的方法不同：
存储函数：有零个或者多个参数，丹不能又out参数。返回值只有一个值，靠return子

句返回
存储过程：有零个或多个参数，过程不返回值，其返回值是靠out参数带出来的。
②调用方法不同：
调用过程的语句可以作为单独的可执行语句在PL/SQL块中单独出现。如：过程名（实

际参数1，实际参数2。。）
调用函数的语句不能作为可执行语句当度出现在PL/SQL块中。如：变量名：=函数名（

实际参数1，实际参数2。。）

5）、存储过程和函数中的出错处理
例如：根据给定的员工号，删除该员工记录。
create or replace procedure fire_emp(p_emp_no in emp.empno%type)
is
  invalid_employee exception;--(定义错误)
begin
  delete from emp where empno=p_emp_no;
  if SQL%NOTFOUND THEN
    raise invalid_employee;--(触发错误)
  end if;
  commit work;
excetion
  when invalid_employee then --(处理错误)
    rollback work;
    insert into exception_table(line_nr,line) values(1,'employee dose not

exist.');
  when others then
    dbms_output.put_line(sqlerrm);
end fire_emp;
/
在sql*plus中调用该过程删除9999号员工，execute fire_emp(9999);
PL/SQL procedure successfully completed 调用过程成功。虽然在emp表中没有员工

号为9999的员工，但过程成功执行，没有被中断。

6）、过程和函数的管理命令
创建一个新过程或函数 create procedure/function
创建或修改一个已有的过程或函数 create or replace procedure/function
删除一个已有的过程或函数  drop procedure/function
查看编译错误  show errors

7)参数传值
采用未知对应法向形式参数传质
调用环境                     调用过程的例子：               调用函数的例子
SQL*Plus                  execute raise_salary(7788,1000);   select

get_sal(7788) from dual;
另外的存储过程或者函数、块 raise_salary(7788,1000);          select

get_sal(7788) into 变量 from dual;
预编译程序                 exec SQL raise_salary(7788,1000); exec SQL

select get_sal(7788) into 变量 from dual;

①在PL/SQL块中如何调用
过程的调用
例：从PL/SQL块中调用过程query_emp，执行过程后，输出变量测试结果。
declare
  v_empno emp.empno%type:=7654
  v_ename emp.ename%type;
  v_sal emp.sal%type;
  v_comm emp.comm%type;
begin
  query_emp(v_empno,v_ename,v_sal,v_comm);
  dbms_output.put_line(v_eanme||'  '||v_sal||'  '||v_comm);
end;
/

函数的调用
例：从一个PL/SQL块中调用get_sal函数。
declare
  v_empno number:=7654;
  v_sal number;
begin
  v_sal:=get_sal(v_empno);
  dbms_output.put_line(v_sal);
end;
/

②在SQL*PLUS中如何调用
过程的调用
例：调用过程query_emp
set serveroutput on
accept p_emp_no prompt 'please enter the employee number:'
variable v_emp_name varchar2(14);
variable v_emp_sal number;
variable v_emp_comm number;
execute query_emp(&p_emp_no,v_emp_name,v_emp_sal,v_emp_comm);
execute dbms_output.put_line('Information for employee: '||to_char

(&p_emp_no));
execute dbms_output.put_line('The name is: '||v_emp_name);
execute dbms_output.put_line('The salary is: '||to_char(v_emp_sal));
execute dbms_output.put_line('The commission is: '||to_char(v_emp_comm));

函数的调用
函数不能作为一条语句单独出现，只能在别的过程中，作为别的过程的参数。
例：调用函数get_sal
execute dbms_output.put_line('员工号7654的工资是：'||get_sal(7654));
或 select get_sal(7654) from dual;

8）、授予权限
grant select, insert,update on emp to scott;
grant execute on hire_emp to Green;

9)、总结存储过程和函数的优点
①提高数据的安全性和完整性
②改善操作性能
③节省存储量
④模块化