存储过程

存储过程

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 dateis v_date date;begin  select sysdate into v_date from dual;  dbms_output.put_line('返回当前时间');  return v_date;end;执行declarev_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 numberisv_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;执行declarev_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)isno_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,salarycreate 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)isbegin  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;执行declarev_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;