定义:
存储过程是一个预编译的SQL语句
优点是允许模块化的设计,就是说只需创建一次,以后在该程序中就可以调用多次。
如果某次操作需要执行多次SQL,使用存储过程比单纯SQL语句执行要快。
可以用一个命令对象来调用存储过程。
1 存储过程语法
CREATE[OR REPLACE] PROCEDURE [schema.] procedure_name[(argument[{IN|OUT|IN OUT}] datatype[,…])] {IS|AS} pl/sql_body;
procedure_name为存储过程的名称,
argument是参数名, in是入参,out是出参
datatype是对应参数的数据类型,
pl/sql_body是该存储过程真正进行的处理操作的PL/SQL块,
OR REPLACE是可选项,如果存在一个同名的存储过程,则先删除后创建,
关键字IS|AS是等价的,用来引出过程体。
例如
create or replace procedure proc3
(
v_deptno in number,
v_sal out number,
v_cnt out number
)
--in 表示入参;
--out 表示出参;
as
begin
select avg(sal),count(*) into v_sal,v_cnt from emp where deptno=v_deptno;
exception
when no_data_found then
dbms_output.put_line('没有找到部门');
when others then
dbms_output.put_line('系统异常');
end;
--调用
/*注意,调用带有出参数据的存储过程要另外定义两个变量接收存储
过程的出参,整体:创建存储过程时创建两个出参v_sal,v_cnt 接收emp表中查到的数据,
调用proc3时,还要在定义两个变量v_avgsal,v_count来接收v_sal,v_cnt的数据*/
declare
v_avgsal number;
v_count number;
begin
proc3(&输入部门编号,v_avgsal,v_count);
dbms_output.put_line(trunc(v_avgsal)||'====='||v_count);
end;
2 函数
把程序存储在数据库中,并可以在任何地方运行它,存储过程和存储函数唯一的区别就是存储函数总向调用者返回数据,而过程则不返回数据。
语法:
create [or replace] function function name [(参数名 in|out 参数的数据类型)]
--or replace 作用:名字相同的存储过程,新建的覆盖原先的
return 返回的数据类型
is|as --固定语法
--函数使用过程中,需要声明变量,记录类型,cursor
begin --开始
--存储函数中的执行体
exception --异常处理
--处理函数执行过程中的异常
end;
注意:单个参数既可以做入参也可以做出参。
create or replace function fun_sal(eno emp.empno%type)
return emp.sal%type
as
s emp.sal%type;
begin
select sal into s from emp where empno=eno;
exception
when no_data_found then
dbms_output.put_line('不存在该员工');
return s;
end;
begin
dbms_output.put_line(fun_sal(&no));
end;
begin
dbms_output.put_line(fun_sal(&no));
end;
--调用直接可以用select fun_sal(&no) from dual