oracle存储子程序

储存子程序

是以独立对象的形式存储在数据库服务器中的

• 存储过程：目的是执行某些操作，可以没有返回值；
• 存储函数：目的是执行某些操作并必须返回一个具体值。

存储过程

存储过程是一种命名PL/SQL程序块，他将一些相关的SQL语句和流程控制语句组合在一起，用于执行某些特定的操作或者任务。
存储过程可以被赋予参数，存储在数据库中，可以被用户调用。
由于存储过程是已经编译好的代码，所以在调用的时候不必再次进行编译，从而提高了程序的运行效率。
使用存储过程可以实现程序的模块化设计。

创建语法

CREATE [OR REPLACE] PROCEDURE  过程名  -- OR REPLACE是一个可选的关键字，建议用户使用此关键字，
        [(参数名 [IN | OUT | IN OUT] 数据类型, …)] -- 输入|输出|输入输出（默认操作是IN）
{IS | AS}
      [说明部分]
BEGIN
	  语句序列
      [EXCEPTION 出错处理]
END [过程名]；

• IS后面是一个完整的PL/SQL语句
• 可定义局部变量和游标
• 不能以declare开始

示例

create or replace procedure find_day
is
begin
	DBMS_OUTPUT.PUT_LINE('‘当前系统日期为:'||SYSDATE);
END find_day;

过程调用

存储过程创建后，以编译的形式存储于数据库的数据字典中。如果不被调用，存储过程是不会执行的。

通过存储过程的名称调用存储过程时，实参的数量、顺序、类型要与形参的数量、顺序、类型相匹配。

• IN模式的参数，实际参数可以是一个具体的值，或是一个已经赋值的变量。
• OUT模式的参数，实际参数必须是一个变量，而不能是常量。当调用存储过程后，此变量就被赋值了。
• IN OUT模式的参数，则实际参数必须是一个已经赋值的变量。当存储过程完成后，该变量将被重新赋值。

调用方法

• 使用EXECUTE命令调用存储过程（存储过程不带输出参数中可以使用）。

  execute  p_name [实参表];
                    这里的参数传递为常量。
  

• 作为一个独立的表达式被调用。

  begin
  	p_name [实参表];
  end;
  

示例-员工涨薪

从键盘随机输入员工编号和涨薪额度，实现对该员工涨指定数量的工资。

create or replace procedure 
add_sal(e_id in test_emp.emp_id%type,e_sal in test_emp.sal%type)
is
begin
	update test_emp set sal = sal + e_sal where emp_id = e_id;
	commit;
end add_sal;

-- 编写调用方法
begin
	add_sal(1001,1111);
end;
/

输出结果展示

删除存储过程

• DROP PROCEDURE add_sal；

存储函数

创建语法

语法存储过程类似

create [or replace] function fun_name
(参数 IN 数据类型) -- 在存储函数中只能写‘IN’，或者省略
return 数据类型
[is|as]
	[说明部分]
begin
	语句序列
        RETURN （表达式） -- 存储函数依据return 来返回数值
	  [EXCEPTION
         例外处理程序] 
end fun_name;

关于return的几点要求：

• 靠RETURN语句返回结果，并且只能返回一个结果。
• 不能约束返回值的长度、精度等。
• 在函数定义的头部，参数列表之后，必须包含一个RETURN语句来指明函数返回值的类型。
• 定义中，必须至少包含一个RETURN语句，来指明函数的返回值。
• 谨记return 在is 后面

代码示例1

返回表中最高工资的数额

create function max_sal
return number
is
emp_sal test_emp.sal%type;
begin
	select max(sal) into emp_sal from test_emp;
	return emp_sal;
end max_sal;

函数调用

代码示例2

创建一个存储函数：根据给定的员工号，返回该员工的详细信息。
调用此存储函数：输出员工的详细信息。

create or replace function test1
(emp_no in number)
return test_emp%rowtype is
vrows test_emp%rowtype;
begin
	select * into vrows from test_emp where emp_id = emp_no;
	return vrows;
end test1;

结果

存储函数管理

修改

create or replace function;

删除

drop function

查看错误详情

show error