PLSQL——存储过程、函数_plsql执行存储过程的sql语句

1.概述

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

过程和函数的唯一区别是：
函数总向调用者返回数据，而过程则不返回数据。

2.存储过程

存储过程，就是一段存储在数据库中执行某块业务功能的程序模块。它是由一段或者多段的PL/SQL代码块或者SQL语句组成的一系列代码块。

创建存储过程的语法
create [or replace] procedure 存储过程名[(参数 in|out|in out 参数数据类型,…)]
is|as
声名部分;
begin
plsql代码块;
exception
异常处理;
end;

如：

--无参数存储过程
create or replace procedure p1
is 
begin
   for v in (select * from emp) loop
      dbms_output.put_line(v.ename||','||v.job||','||v.sal||','||v.deptno);
   end loop;
end;

select * from user_objects where object_type='PROCEDURE';
1
2
3
4
5
6
7
8
9
10

存储过程的调用
过程名字（）;

1. 在plsql块中调用
2. call 名字();
3. execute命令调用（sqlplus中输出）
   Sqlplus :
   set serverout on;
   Begin
   过程名();
   end;

删除存储过程
drop procedure p1;

参数的三种模式
IN 用于接受调用程序的值。默认的参数模式
OUT 用于向调用程序返回值
IN OUT 用于接受调用程序的值，并向调用程序返回更新的值

--写一个存储过程，根据传入部门编号，查询该部门下的所有员工，并打印员工信息
create or replace procedure p1(v_deptno in number)
is 
begin
   for v in (select * from emp where deptno=v_deptno) loop
      dbms_output.put_line(v.ename||','||v.job||','||v.sal||','||v.deptno);
   end loop;
end;
--运用检验
declare
   v_dno number(10):=20;
begin
  p1(10);   --传值
  dbms_output.put_line('-----------------');
  p1(v_dno);  --传变量
  dbms_output.put_line('-----------------');
  p1(v_deptno=>30);  --按位传值
end;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

--根据传入的员工编号，查询出员工信息并以输出参数的方式返回
--创建一个存储进程
create or replace procedure p2(v_empno in number,v_emp out emp%rowtype)
is
begin
  select * into v_emp from emp where empno=v_empno;
end;
--运用检验
declare
  v emp%rowtype;
begin
  p2(7369,v);
  dbms_output.put_line(v.empno||','||v.ename||','||v.job||','||v.deptno);
end;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

create or replace procedure p3(v_emp in out emp%rowtype)
is
begin
  select * into v_emp from emp where empno=v_emp.empno;
end;

declare
   v_e emp%rowtype;
begin
   --要给v_e里的empno赋值
   v_e.empno:=7369;
   p3(v_e);
   dbms_output.put_line(v_e.empno||','||v_e.ename||','||v_e.job||','||v_e.deptno);
end;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

注：将过程的执行权限授予其他用户

--存储过程所在的用户执行赋权限
grant EXECUTE ON 存储过程名 to 用户名;
1
2

3.函数

函数是通过关键字function按照自己的需求把复杂的业务逻辑封装进PL/SQL函数中，函数提供一个返回值，返回给使用者。

创建函数的语法

create [or replace] function 函数名称[(形式参数 参数类型,...)]
return 返回值类型
is
   声名变量;
begin
  plsql代码块;
  return 返回值;--return后面的内容不执行
end;
1
2
3
4
5
6
7
8

如:

create or replace function f1
return varchar2
is
begin
   return 'hello';
end;
1
2
3
4
5
6

访问函数的方式

1. 使用 SQL 语句 Select 函数名字() from dual;
2. 使用 PL/SQL 块

declare
   v varchar2(30);
begin
   v:=f1;
  dbms_output.put_line(v);
end;
1
2
3
4
5
6

--求二个数的和
create or replace function f1(n number,n2 number)
return number
is
begin  
   return n + n2;
end;
1
2
3
4
5
6
7

--求一个数的阶乘
create or replace function f1(n number)
return number
is
   --声名一个变量保存返回值
   res number(10):=1;
begin
   for i in 1..n loop
      res:=res*i;
   end loop;
   return res;
end;
1
2
3
4
5
6
7
8
9
10
11
12