Oracle存储过程语法

模板

-- declare后面定义变量或游标；如果不需要定义变量或游标，可以省略declare
declare
  -- 变量名前面建议加上 v_
  v_name integer;
begin
  -- 执行语句/异常处理;
end;

声明变量

变量名 变量类型(变量长度)

赋值的两种方式

1. v_name := ‘Tom’
2. select 值 into 变量 from 表名;

普通变量

declare
  v_name nvarchar2(5) := 'Tom';
  v_age number;
  v_loving nvarchar2(20);

begin
  v_age := 20;
  select 'coding' into v_loving from dual;
  -- 输出语句
  -- 字符串之间的连接使用符号：||
  dbms_output.put_line(v_name || ',' || v_age || ',' || v_loving);
end;

引用型变量

如果一个变量未来可能被赋的值的类型或长度不确定时，可以使用 表名.字段名%TYPE 来指定变量的类型

-- 查询emp表中120560这个员工的姓名和年龄，并打印出来
declare
  v_name emp.emname%TYPE;
  v_age emp.emage%TYPE;

begin
  select emname,emage into v_name,v_age from emp where id = '120560';

  dbms_output.put_line(v_name || ',' || v_age);
end;

记录型变量(慎用，会有效率问题)

如果定义一个变量的类型为 emp%ROWTYPE ，可以把查询出来的这一整条记录赋值给这个变量，使用的时候使用 变量名.字段名 就可以访问到每个字段了。如果查询结果有多条记录，要使用游标来存储。

declare
  v_emp emp%ROWTYPE
begin
  select * into v_emp from emp where id = '120560';

  dbms_output.put_line(v_name || ',' || v_age);
end;

条件分支

语法：

begin
  if 条件1 then 执行1
  elsif 条件2 then 执2		--注意：是elsif，els没有e
  else 执行3
  end if;
end;

实例：

declare
  v_number number;
begin
  select count(*) into v_number from emp;
  if v_number < 20 then
    dbms_output.putline('记录数在20以下，记录数为：' || v_count);
  else
    dbms_output.putline('记录数在20以上，记录数为：' || v_count);
end;

循环

语法：

begin
  loop
    exit when 退出循环条件
  end loop;
end;

实例：

-- 输出1-10
declare
  v_num number := 1;
begin
  loop
    exit when v_num > 10;
    dbms_output.putline(v_num);
    v_num := v_num + 1;
  end loop;
end;

游标

用于临时存储一个查询返回的多行数据。

游标的使用方式：声明=>打开=>读取=>关闭

-- 游标声明
cursor 游标名(参数列表) is 查询语句
-- 游标打开
open 游标名;
-- 游标取值
fetch 游标名 into 变量列表;
-- 游标关闭
close 游标名;

属性	返回值类型	说明
%found	boolean	最近的 fetch 语句返回一行数据则为真，否则为假
%notfound	boolean	与 %found 相反

实例：

declare
  -- 声明游标
  cursor c_emp is select emname, emage from emp;
  v_emname emp.emname%type;
  v_emage emp.emage%type;
begin
  -- 打开游标
  open c_emp;
    loop
      -- 遍历游标
      fetch c_emp into v_emname,v_emage;
      -- 满足条件推出循环
      exit when c_emp%notfound;
      dbms_output.putline(v_emname || '-' || v_emage);
    end loop;
  -- 关闭游标
  close c_emp;
end;

注意：fetch 和 exit when 的顺序一定不能改变

带参数的游标

实例：

declare
  -- 给游标添加参数，并给sql语句添加where条件
  cursor c_emp(v_deptno emp.deptno%type) is select emname, emage from emp where deptno = v_deptno;
  v_emname emp.emname%type;
  v_emage emp.emage%type;
begin
  -- 传入参数
  open c_emp(10);
    loop
      fetch c_emp into v_emname,v_emage;
      exit when c_emp%notfound;
      dbms_output.putline(v_emname || '-' || v_emage);
    end loop;
  close c_emp;
end;

存储过程

语法：

create or replace procedure 存储过程名(参数列表) is/as
-- 注意：没有declare
begin
end 存储过程名;

定义存储过程：

create or replace procedure p_sayhello is
begin
  dbms_output.putline('hello world!');
end p_sayhello;

在 PL/SQL 中调用存储过程：

begin
  p_sayhello;
end;

在命令行中调用存储过程：

exec p_sayhello;

-- 开启输出语句的开关
set serveroutput on

带输入参数的存储过程

定义存储过程：

create or replace procedure p_getinfo(i_emcode in emp.emcode%type) is
v_name emp.emname%type;
v_age emp.emage%type
begin
  select emname,emage into v_name,v_age from emp where emcode = i_emcode;
  dbms_output.putline(v_name || '-' || v_age);
end p_getinfo;

调用存储过程：

begin  p_getinfo(120560);end;

带输出参数的存储过程

定义存储过程：

create or replace procedure p_getinfo_out(i_emcode in emp.emcode%type, o_emname out emp.emname%type) isbegin  select emname into o_emname from emp where emcode = i_emcode;end p_getinfo_out;

调用存储过程：

declare  v_name emp.emname%type;begin  p_getinfo_out(120560, v_name);  dbms_output.putline(v_name);end;