Oracle数据库PLSQL编程和存储过程

一、PLSQL编程

1.1、使用PLSQL实现 Hello world！

-- Created on 2022/8/22 by ADMINISTRATOR 
declare
  -- 这是申明变量的地方，还包括游标
  -- 相当于public class A()
  i integer;
begin
  -- Test statements here
  -- 执行部分
  -- public static void main(String args[]){}
​
  -- 异常处理
  dbms_output.put_line('hello world!');
end;

 

1.2、有关变量的定义和使用

PLSQL编程中的变量主要有两大类：

1. 普通数据类型：（char、varchar2、data、number、boolean、long）

2. 特殊变量类型：（引用型变量、记录型变量）

1.2.1普通变量的申明和打印：

赋值方式：

1. 直接赋值：使用 := 符号进行变量的赋值，可以在声明变量时直接赋值，也可以在 begin 中直接赋值；

2. 语句赋值：使用 select……into……赋值（语法：select 值 into 变量）

-- 声明一个人的信息，姓名、薪水、地址
declare
  -- Local variables here
  -- 姓名
  v_name varchar2(50) := '张三丰';
​
  -- 薪水
  v_salary number;
​
  -- 地址
  v_addr varchar2(200);
begin
  -- Test statements here
  -- 直接赋值
  v_salary := 15900;
​
  -- 语句赋值
  select '江苏连云港' into v_addr from dual;
  
  dbms_output.put_line('姓名：' || v_name || '，薪水：' || v_salary || '，地址：' || v_addr);
  
end;

 姓名：张三丰，薪水：15900，地址：江苏连云港 

1.2.2引用型变量的使用

引用型变量的类型和长度取决于表中字段的类型和长度

通过表名.列名%TYPE来指定变量的类型和长度，例如：v_id A_XX.A_ID%TYPE;

【查询A_XX表中ID为2的员工信息，包括ID，地址，并打印出来】

declare
  -- Local variables here
  -- ID
  v_id A_XX.A_ID%TYPE;
  -- 地址
  v_addr A_XX.ADDRESS%TYPE;
begin
  -- Test statements here
  -- 语句赋值
  select a_id , address into v_id , v_addr from A_XX where a_id = 2;
  
  dbms_output.put_line('ID：' || v_id || '，地址：' || v_addr);
  
end;

 ID：2，地址：xyz 

使用引用变量的好处：

相比较普通类型变量需要知道变量的类型和长度而言，引用变量不用考虑表中字段的类型和长度，使得PLSQL编程更加灵活，更加适应数据库的动态更新。

1.2.3记录型变量的使用

记录型变量是用来存储一行数据，相当于JAVA中对象的使用；

基本语法为：变量名 表名%ROWTYPE，例如：v_AA A_XX%ROWTYPE;

declare
  -- Local variables here
  -- ID
  v_AA A_XX%ROWTYPE;
​
begin
  -- Test statements here
  -- 语句赋值
  select * into v_AA from A_XX where a_id = 2;
  
  dbms_output.put_line('ID：' || V_AA.A_ID || '，地址：' || V_AA.ADDRESS);
  
end;

 ID：2，地址：xyz 

记录型变量适用于一行中变量多的情况下

1.3、流程控制语句

1.3.1条件分支

begin
    IF 条件1 THEN 执行1
        ELSIF 条件2 THEN 执行2
        ELSE 执行3
    END IF;
end;

注意关键字ELSIF

1.3.2循环语句

Oracle中有三种循环，这里介绍loop循环

begin
    LOOP
        EXIT WHEN 退出循环条件
    END LOOP;
end;

 

【打印数字1-10】

declare
  -- Local variables here
  -- 设置循环变量并赋初值
  v_num NUMBER := 1;

begin
  -- Test statements here
  -- LOOP循环
  LOOP
    EXIT WHEN V_NUM > 10;
    dbms_output.put_line(V_NUM);
    -- 循环自增
    V_NUM := V_NUM + 1;
  END LOOP;
end;

输出结果：

1
2
3
4
5
6
7
8
9
10

 

二、游标

2.1什么是游标

用于临时储存一个查询返回的多行数据，（结果集，相当于JAVA中的Resultset结果集），可以通过游标遍历，逐行访问处理这个结果集中的数据。

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

2.2语法

-- 声明游标
CURSOR 游标名[(参数列表)] IS 查询语句;

-- 游标打开
OPEN 游标名;

-- 游标的取值
FETCH 游标名 INTO 变量列表;

-- 游标的关闭
CLOSE 游标名;

 

2.3游标的属性

游标的属性	返回值类型	说明
%ROWCOUNT	整型	获得FETCH语句返回的数据行数
%FOUND	布尔型	最近的FETCH语句返回一行数据为真，否则为假
%NOTFOUND	布尔型	最近的FETCH语句返回一行数据为假，否则为真
%ISOPEN	布尔型	游标打开为真，否则为假

【游标的使用】

declare
  -- 声明一个游标
  CURSOR C_AA IS
    SELECT A_ID, ADDRESS FROM A_XX;
  -- 定义引用变量接收游标中的值
  V_ID   A_XX.A_ID%TYPE;
  V_ADDR A_XX.ADDRESS%TYPE;
begin
  -- 打开游标
  OPEN C_AA;
  -- 读取游标中的值
  LOOP
    -- 获取游标中的数据，如果有，赋值给变量
    FETCH C_AA
      INTO V_ID, V_ADDR;
    EXIT WHEN C_AA%NOTFOUND;
    dbms_output.put_line('ID：' || V_ID || '，地址：' || V_ADDR);
  END LOOP;
  -- 关闭游标
  CLOSE C_AA;
end;

 

三、存储过程

3.1存储过程的概念

之前我们编写的PLSQL程序可以对表进行操作、判断、循环逻辑处理的过程，但无法重复使用；

可以理解为我们将这些程序代码写在了main方法中，作为匿名程序使用，而JAVA是通过将匿名程序封装成方法或对象来实现代码的复用性；

作为PLSQL程序而言，是通过将程序代码存储起来，来实现程序过程的复用性，而这些被存储起来的PLSQL程序，被称之为存储过程

3.2存储过程的作用

1. 在开发程序过程中，为了一个特定的业务功能，需要不断地向数据库进行连接关闭，（连接关闭是很耗费资源的），需要对数据库进行多次的I/O读写，性能较低；如果将这些业务放到PLSQL程序中，在应用程序中只需要调用PLSQL就可以完成连接关闭一次数据库，可以大大提高效率。

2. Oracle官方：能够让数据库操作的，不要放在程序代码中。

3.3语法

create or replace procedure 过程名称[(参数列表)] is
begin
  
end 过程名称;

 

【示例】

create or replace procedure p_hello is
begin
  dbms_output.put_line('hello');
  
end p_hello;

 

根据参数类型，可以将其分为三类：

1. 不带参数的

2. 带输入参数

3. 带输入输出参数（返回值）

3.4无参存储

【示例】

在PROCEDURES中创建一个新的无参存储，并打印hello：

create or replace procedure p_hello is
begin
  dbms_output.put_line('hello');
  
end p_hello;

 

使用Test Window，调用刚才创建的无参存储：

-- Created on 2022/8/22 by ADMINISTRATOR 
declare 
  -- Local variables here
  --i integer;
begin
  -- Test statements here
  p_hello;
end;

 

输出结果：

hello

 

注意：

• is和as是可以互用的，用哪一个都可以；

• 过程中没有declare关键字，declare用在语句块中；

 

3.5带输入参数的存储过程

【示例：使用带输入参数的存储过程打印编号为1的地址】

PROCEDURES带输入参数的存储过程：

create or replace procedure p_queryaddr(i_id IN A_XX.A_ID%TYPE) is
  --变量声明
  V_ADDR A_XX.ADDRESS%TYPE;
begin

  SELECT ADDRESS INTO V_ADDR FROM A_XX WHERE A_ID = I_ID;
  dbms_output.put_line('地址：' || V_ADDR);

end p_queryaddr;

 

Test Window 调用带输入参数的存储过程：

declare 
  -- Local variables here
  --i integer;
begin
  -- Test statements here
  p_queryaddr(1);
end;

 

输出结果：

地址：xxx

 

3.6带有输入和输出参数的存储过程

【示例】

PROCEDURES：

create or replace procedure p_queryaddr_OUT(i_id IN A_XX.A_ID%TYPE , o_addr OUT A_XX.ADDRESS%TYPE) is

begin

  SELECT ADDRESS INTO O_ADDR FROM A_XX WHERE A_ID = I_ID;

end p_queryaddr_OUT;

 

Test Windows：

declare
  -- Local variables here
  V_ADDR A_XX.ADDRESS%TYPE;
begin
  -- Test statements here
  p_queryaddr_OUT(1, V_ADDR);
  
  dbms_output.put_line(V_ADDR);

end;

 

输出结果：

xxx