Oracle数据库存储过程和创建过程

存储过程

oracle提供可以把pl/sql程序存储在中，并可以在任何地方来运行它。这样就叫存储过程或函数。存储过程和函数统称为pl/sql子程序，它们是被命名的pl/sql块，均存储在数据库中，并通过输入、输出参数或输入/输出参数与其调用者交换信息。

存储过程和函数的唯一区别是：函数有个返回值，而存储过程没有。

创建函数

create or replace function 函数名(

arg1 type1, –省略标记为 in

arg2 (in,out,in out) type2 –不要加长度约束

)

return return_type

is|as

类型.变量的说明

begin

执行部分

exception

其他语句

end;

注意：在声明函数时所定义的参数的类型和return后面的类型都不要加长度约束。

--无参函数

create or replace function fun1 
return date
is 
v_date date;
begin
  select sysdate into v_date from dual;
  dbms_output.put_line('返回当前时间');
  return v_date;
end;

执行

declare
v_mydate date;
begin
  v_mydate := fun1();
  dbms_output.put_line(v_mydate);
end;

--有参函数
--获取某部门的工资和以及部门人数

create or replace function sumsal(
p_deptno employees.department_id%type default 90,
p_count out number
)
return number
is
v_sum_sal number;
begin
  select sum(salary),count(*) into v_sum_sal,p_count from employees where department_id=p_deptno;
  return v_sum_sal;
end;

执行

declare
v_deptno number := 50;
v_sum_salary number;
v_count number;
begin
  v_sum_salary := sumsal(v_deptno,v_count);
  dbms_output.put_line(v_sum_salary||'---'||v_count);

  v_sum_salary := sumsal(p_count => v_count);
  dbms_output.put_line(v_sum_salary||'---'||v_count);
end;

函数声明时所定义的参数称为形式参数，应用程序调用时为函数传递的参数称为实际参数。

参数传递格式

位置表示法：即按照函数声明的，依照顺序将参数传递 名称表示法：形式参数 => 实际参数。可以传部分参数，而且可以不用按函数参数顺序传递。 混合表示法：使用位置表示法所传递的参数必须放在名称表示法所传递的参数前面。

无论是哪种传递格式，实际参数和形式参数之间的数据传递只有两种

传址法：将实际参数的地址指针传递给形式参数。输入参数均采用传址法传递数据。 传值法：将实际参数的数据拷贝到形式参数。输出，输入/输出参数均采用传值法。

创建过程

create or replace procedure name_procedure(

arg1 type1, –省略标记为 in

arg2 (in,out,in out) type2 –不要加长度约束

)

is|as

类型.变量的说明

begin

执行部分

exception

其他语句

end;

create or replace procedure del_by_empno(
p_empno in emp.employee_id%type
)
is
no_result exception;
begin
  delete from emp where employee_id = p_empno;
  if sql%notfound then
    raise no_result;
  end if;
  dbms_output.put_line('成功删除'||p_empno||'号员工');
exception
  when no_result then dbms_output.put_line('数据不存在');
  when others then dbms_output.put_line(sqlcode||'---'||sqlerrm);
end;

--根据id查员工name,salary

create or replace procedure get_emp_by_id(
p_empno employees.employee_id%type,
p_ename out employees.last_name%type,
p_sal out employees.salary%type
)
is

begin
  select last_name,salary into p_ename,p_sal from employees where employee_id=p_empno;
  dbms_output.put_line('员工'||p_empno||'已找到');
exception
  when no_data_found then dbms_output.put_line('无此员工');
  when others then dbms_output.put_line('不明原因');
end;

执行

declare
v_empno employees.employee_id%type := &empno;
v_ename employees.last_name%type;
v_sal employees.salary%type;
begin
  get_emp_by_id(v_empno,v_ename,v_sal);

  dbms_output.put_line(v_ename||'-----'||v_sal);

end;