oracle存储过程+PL/SQL体学习笔记

PL/SQL体

变量赋值

赋值方式有两种：

• 直接赋值

  v_name varchar2(20) := 'zhagnsan';
  

• 语句赋值

  select '昆明市' into v_addree from dual;
  

-- Created on 2020/12/30 by ADMINISTRATOR 
declare
  --声明部分
  -- 姓名
  v_name varchar2(20) := 'zhagnsan';
  --薪水
  v_sal number;
  --地址
  v_addree varchar2(200);

begin
  -- 直接赋值
  v_sal := 2000;

  --语句赋值
  select '昆明市' into v_addree from dual;
  --  '姓名：'||v_name 拼接字符串
  dbms_output.put_line('姓名：'||v_name ||',薪水:'||v_sal||'，地址:'||v_addree);
end;

引用型变量

变量的类型和长度取决于 表中列的类型和长度

语法： 变量名称 表名.列名%TYPE

-- Created on 2020/12/30 by ADMINISTRATOR 
declare
  --声明部分
  -- 姓名    ac01表中的aac003字段是什么类型和长度，v_name就是什么类型和长度
  v_name ac01.aac003%type;
  --单位id
  v_sal ac01.sab004%type;

begin
  select AAC003, SAB004
    into v_name, v_sal
    from ac01
   where AAE005 = '12322';

  --  '姓名：'||v_name 拼接字符串
  dbms_output.put_line('姓名：' || v_name || ',单位id:' || v_sal);
end;

记录型变量

接收表中的一整行记录，相当于Java中的一个对象

语法： 变量名称 表名%ROWTYPE

-- 查询ac01中AAE005 = '12322'的用户的姓名和单位id
declare 
  -- 记录型变量
  v_emp ac01%rowtype;
begin
  select * into v_emp from ac01 where AAE005 = '12322';
  
  dbms_output.put_line('姓名：' || v_emp.aac003 || ',单位id:' || v_emp.sab004);
  
end;

if …else

-- 查询判断ac01表中的记录数
declare 
  -- 声明变量接收ac01表中的记录数
  v_count number;
begin
  select count(1) into v_count from ac01;
  
  dbms_output.put_line(v_count); -- 2954453条记录
  
  --判断打印
  if v_count>=2000000 then
    dbms_output.put_line('表ac01中的记录数超过了两百万，为：'||v_count||'条。'); 
    elsif v_count>=1000000 and v_count<2000000 then
      dbms_output.put_line('表ac01中的记录数在一百万到两百万之间，为：'||v_count||'条。'); 
      else
        dbms_output.put_line('表ac01中的记录数在一百万以下，为：'||v_count||'条。');
        end if;
end;

循环

– 查询判断ac01表中的记录数
declare
– 声明循环变量
begin

​ loop

​ exit when number>10;

​ dbms_output.put_line(number);

​ number=number+1;

​ end loop;

end;

游标

用于临时存储一个查询返回的多行数据（类似于java中jdbc连接返回的结果集ResultSet），通过遍历游标，可以逐行访问处理该结果集的数据。

游标使用步骤：声明—>打开—>读取—>关闭

无参游标

-- 使用游标查询ac01表中所有用户的名字和单位id
declare
  -- 声明游标
  cursor c_ac01 is
    select aac003, sab004 from ac01;
  --声明变量接受游标中的数据
  v_name ac01.aac003%type;
  --单位id
  v_sal ac01.sab004%type;
begin
  --打开游标
  open c_ac01;

  --遍历游标
  loop
    --获取游标中的数据
    fetch c_ac01
      into v_name, v_sal;
    exit when c_ac01%notfound;
    dbms_output.put_line('姓名：' || v_name || ',单位id:' || v_sal);
  end loop;

  --关闭游标
  close c_ac01;

end;

游标的属性

游标的属性	返回值类型	说明
%rowcount	整型	获得fetch语句返回的数据行数
%found	布尔型	若最近的fetch语句返回一行数据则为真，否则为假
%notfound	布尔型	若游标找不到元素时为真，否则为假
%isopen	布尔型	若游标打开时值为真，否则为假

有参游标

-- 传递一个参数5，查询部门号为5的所有用户的名字和单位id
declare
  -- 声明游标
  cursor c_ac01（v_部门号 ac01.部门号%type） is
    select aac003, sab004 from ac01
    where 部门号 = v_部门号;
  --声明变量接受游标中的数据
  v_name ac01.aac003%type;
  --单位id
  v_sal ac01.sab004%type;
begin
  --打开游标 同时传递一个参数5，查询部门号为5的所有用户的名字和单位id
  open c_ac01(5);

  --遍历游标
  loop
    --获取游标中的数据
    fetch c_ac01
      into v_name, v_sal;
    exit when c_ac01%notfound;
    dbms_output.put_line('姓名：' || v_name || ',单位id:' || v_sal);
  end loop;

  --关闭游标
  close c_ac01;

end;

oracle存储过程

无参存储过程

首先通过Procedures创建存储过程

create or replace procedure study01 is
--声明变量
begin
  dbms_output.put_line('hello world');
end study01;

通过PL/SQL工具 Test Window调用存储过程

begin
  --通过plsql调用存储过程
  study01;
end;

有参存储过程

通过传入ac01.aae005参数来查询用户的姓名和单位ID

create or replace procedure study02(i_id in ac01.aae005%type) is
  --声明变量
  v_name ac01.aac003%type;
  --单位id
  v_sal ac01.sab004%type;
begin
  
  --查询ac01表中的某个用户的姓名和单位ID并赋值给变量
  select aac003,sab004 into v_name,v_sal from ac01 where aae005 = i_id;
  dbms_output.put_line('姓名：' || v_name || ',单位id:' || v_sal);

end study02;

begin
  --通过plsql调用存储过程
  study02('12322');  --传递参数
end;

带输出参数的存储过程

--输入aae005查询某个用户（'12322'）信息，将单位ID作为返回值输出，给调用程序使用
create or replace procedure study03(i_id in ac01.aae005%type, o_sal out ac01.sab004%type) is
begin
  --查询ac01表中的某个用户的姓名和单位ID并赋值给变量
  select sab004 into o_sal from ac01 where aae005 = i_id;
  
end study03;

调用

declare
  --声明变量接收存储过程中的输出参数（单位ID）
  v_sal ac01.sab004%type;

begin
  --通过plsql调用存储过程
  study03('12322',v_sal);
  dbms_output.put_line(v_sal);
end;