存储过程和函数是pl/sql中的命名的pl/sql块,它通过编译后存储在数据库中,在使用时通过名字来调用就好。在之前我们使用的都是declare匿名块,这样的方式代码的重用性不高,因为是匿名块,也没有办法调用,就像在java中的匿名方法对象等,只使用一次。因此使用存储过程和函数后,就实现了类似java代码的封装,使用的时候通过名字来调用。
为什么要使用存储过程和函数?
1.匿名块无法直接被jdbc调用
2.复杂的流程,涉及到判断,逻辑的处理,像java一样封装起来,提高代码重用
3.优点:预编译、执行效率高,代码重用,减轻网络负担。
存储过程与函数的区别在于,存储过程没有返回值,而函数有返回值,函数侧重返回的结果
1.存储过程
--存储过程的创建 create or replace procedure pro3(--or replace 不是必须的语句,可以省略,create or replace的意思就是创建或者替换 -- 参数名 参数类型 )as--或者is也可以 --变量声明 begin -- 程序块 end;
/** 存储过程的调用 1.cell 存储过程名(参数) jdbc调用 2.execute 存储过程名(参数) sqlplus或者命令窗口中执行 3.sql语句块中 begin 存储过程名(参数) end; */ -- 存储过程使用示例:查看部门信息及每个部门的人数 -- 创建存储过程 create or replace procedure pro1( v_deptno dept.deptno%type ) as v_count number; begin -- 在存储过程中使用select语句时,必须配合into将结果存入变量中 select count(*) into v_count from emp where deptno = v_deptno; dbms_output.put_line('部门人数为:' || v_count); end; -- 调用存储过程 call pro1(10);--调用存储过程时,如果没有参数,直接写call pro1即可,不要加() -- 调用存储过程 declare begin pro1(20); end; /*关于存储过程参数 存储过程的参数如果没有时,只写存储过程名调用,不加(), 存储过程的参数可以有多个,中间以,分隔 存储过程的参数可以有输入参数,也可以有输出参数,输入参数用in表示,输出参数用out表示,使用out参数时,就像函数有了返回值 */ create or replace procedure pro1( v_deptno in dept.deptno%type, v_count out number ) as begin -- 在存储过程中使用select语句时,必须配合into将结果存入变量中 select count(*) into v_count from emp where deptno = v_deptno; dbms_output.put_line('部门人数为:' || v_count); end; declare --当有输出参数时,就不能用call方式调用了,因为要传递输出参数,需要在匿名块中创建参数 countno number; begin pro1(10, countno); dbms_output.put_line('10----' || countno); -- 上面的传参方式为按顺序传参,还可以按名字传参 pro1(v_deptno => 20,v_count => countno); dbms_output.put_line('20----' || countno); -- 还可以混合传递,即假设有5个参数,前两个用顺序传参,后三个用名字传参,都可以 end;
2.函数
-- 创建函数,创建函数与存储过程相似,创建时,两者如果没有参数的话,都不加(),调用也不加,不同的是,函数有返回值, create or replace function fun1( 参数列表 )return 返回值类型 as -- 声明局部变量 begin 代码块 return 函数结果; end; -- 创建函数 create or replace function fun1( v_deptno dept.deptno%type )return number as v_count number(7); begin select count(*) into v_count from emp where deptno = v_deptno; return v_count; end; -- 调用函数,函数可以在sql语句中直接调用 select deptno,dname,fun1(deptno) from dept ; -- 在程序块中调用时,要定义变量接收返回值 declare v_count number; begin -- fun1(10) 直接调用错误,会报未定义异常,需要定义变量接收返回值 v_count:=fun1(10); end; create function fun2( v_no emp.empno%type )return emp.ename%type as v_name emp.ename%type; begin select ename into v_name from emp where empno = v_no; return v_name; end; declare v_name varchar2(20); begin v_name := fun2(7369); dbms_output.put_line(v_name); end;