存储过程(procedure)和函数(function)

存储过程

定义

存储过程时一组为了完成特定功能的sql语句，编译后存储在数据库中

案例

1. 打印 hello world

create or replace procedure test_procedure1 as
begin
  dbms_output.put_line('hello world');
end;
-- test_procedure1存储过程名
-- 存储过程和函数中 as和is效果一样

-- 执行
begin
  test_procedure1;
end;
-- 客户端执行
exec test_procedure1;

2. 带有变量

create or replace procedure test_procedure1 as
v_name varchar2(32);
begin
  v_name := '李四';
  dbms_output.put_line('名字：'||v_name);
end;
-- := 赋值

-- 执行
begin
  test_procedure1;
end;

3. 带有参数和变量

create or replace procedure test_procedure1(v_name in varchar2,age in number) as
  v_age number(10);
  out_name varchar2(32);
begin
  v_age  := age;
  out_name := v_name;
  dbms_output.put_line('名字：' || out_name||', 年龄：' || age);
end;
-- 参数中的in 代表输入
-- || 连接字符串
-- 参数不必写具体类型大小

-- 执行
begin
  test_procedure1('zs',22);
end;

4. 形参实参

create or replace procedure test_procedure1(v_name in varchar2,v_age in number) as
begin
  dbms_output.put_line('名字：' || v_name||', 年龄：' || v_age);
end;

-- 执行
declare
name varchar(10);
age number(2);
begin
  name := 'barry';
  age := 12;
  test_procedure1(v_name=>name,v_age => age); //test_procedure1(name,age);
end;

5. in,out参数

create or replace procedure test_procedure1(v_name out varchar2,v_age in number) as
begin
  dbms_output.put_line('年龄：' || v_age);
  select 'lisi' into v_name from dual;
end;
-- in代表输入，out用于输出，参数默认类型是in类型

-- 执行
declare
name varchar(10);
age number(2);
begin
  age := 12;
  test_procedure1(v_name=>name,v_age => age);
  dbms_output.put_line('name: '||name);
end;

6. 异常

create or replace procedure test_procedure1(v_age in out number) as
begin
  v_age := 10 / 0;
  dbms_output.put_line('年龄：' || v_age);
exception
  when others then
    dbms_output.put_line('error');
end;

-- 执行
declare
age number(2);
begin
  test_procedure1(v_age => age);
end;

7. 判断

create or replace procedure test_procedure1(x in out number) as
begin
  if x = 0 then
    begin
      x := 0 - x;
    end;
  elsif x > 20 then
    x := 100;
  end if;
  dbms_output.put_line('x: ' || x);
end;

-- 注意 elsif

declare
  x int;
begin 
  x := 200;
  test_procedure1(x => x);
end;

8. for in 循环

create or replace procedure test_procedure1 as
i int;
begin
  for i in 0..10 loop
  begin 
  dbms_output.put_line(i);
  end;
  end loop;
end;

begin 
  test_procedure1;
end;

9. 删除

drop procedure 存储过程名

函数

语法结构

create [or replace] function 函数名 
([p1,p2...pn])
return datatype
is|as
--声明部分 
begin
-- 程序块
end

-- p1,p2入参列表
-- return datatype函数返回值类型

案例

1. 无参函数

create or replace function test_function1 return varchar2
as 
begin 
  return to_char(sysdate, 'yyyy-MM-dd');
  end;

begin 
  dbms_output.put_line(test_function1);
end;

2. 有参函数

create or replace function test_function1(v_name in varchar2) return varchar2 as
  name varchar2(16);
begin
  name := v_name;
  return name;
end;

select test_function1('lisi') from dual;

3. 删除函数

drop function 函数名;

存储过程和函数区别

1. 存储过程用户在数据库中完成特定操作或者任务（如插入，删除等），函数用于返回特定的数据
2. 存储过程声明用procedure，
3. 存储过程不需要返回类型，函数必须要返回类型
4. 存储过程可独立执行，函数不能作为独立的plsql执行，必须作为表达式的一部分
5. 存储过程只能通过out和in/out来返回值，函数除了可以使用out，in/out以外，还可以使用return返回值
6. sql语句（DML或SELECT)中不可用调用存储过程，而函数可以

应用场景不同

1. 如果需要返回多个值和不返回值，就使用存储过程；如果只需要返回一个值，就使用函数
2. 存储过程一般用于执行一个指定的动作，函数一般用于计算和返回一个值
3. 可以再SQL内部调用函数来完成复杂的计算问题，但不能调用存储过程