PLSQL编程

2021-11-26 15:08:40

PLSQL编程

概念

  1. PL/SQL(Procedure Language/SQL)
  2. PLSQL是Oracle对SQL语言的过程化扩展
  3. 指在SQL命令语言中增加了过程处理语句（如分支，循环等），使SQL语言具有过程处理能力

程序结构

通过PLSQL Developer工具Test Windows创建程序模板或者通过语句在SQL Windows编写。

提示：PLSQL语言是不区分大小写的

PL/SQL可以分为三个部分：声明部分，可执行部分，异常处理部分

declare 
  -- 声明变量，游标
  i integer;
begin
  -- 执行语句
  
  -- [异常处理]
end;

Hello World

-- Created on 2021/10/1 by PRAY 
declare 
  -- Local variables here
  i integer;
begin
  dbms_output.put_line('HelloWorld');
  
end;

-----------------------------------------
--DOS窗口打开输出
set serveroutput on

变量

分类

  1. 普通数据类型（char,vharchar2,date,number,boolean,long）
  2. 特殊变量类型（引用型变量，记录型变量）

声明变量的方式

变量名 变量类型(变量长度)
例如：v_name varchar2(20)

普通变量

变量赋值的方式有两种：

  1. 直接赋值语句 :=
  2. 语句赋值，使用select...into...赋值（语法select值into变量）
-- Created on 2021/10/1 by PRAY 
declare
  --姓名
  v_name varchar(20) := '张三';
  --薪水
  v_sal numeric;
  --地址
  v_addr varchar(200);
begin
  -- 直接赋值
  v_sal := 1580;
  -- 语句赋值
  select '珠海市' into v_addr from dual;
  dbms_output.put_line(v_name || '+' || v_sal || '+' || v_addr);
end;

引用型变量

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

语法：通过表名.列名%TYPE指定变量的类型和长度，例如v_name emp.ename%TYPE


declare
  v_name emp.ename%TYPE;

  v_sal emp.sal%TYPE;

begin

  select t.ename, t.sal into v_name, v_sal from emp t where t.empno = '7839';

  dbms_output.put_line('姓名：' || v_name || ',薪水：' || v_sal);

end;

引用型变量的好处：

使用普通变量的定义方式，需要知道表中列的类型，而使用引用类型，不需要考虑列的类型，使用%TYPE是非常好的编程风格，因为它使得PLSQL更加灵活，更加适应与对数据库定义的更新。

记录型变量

用于接收表中的一整行记录，相当于Java中的一个对象
语法：变量名称，表名%ROWTYPE
例如：v_emp emp%rowtype;

declare
  
   v_emp emp%ROWTYPE;

begin

  select * into v_emp from emp t where t.empno = '7839';

  dbms_output.put_line('姓名：' || v_emp.ename || ',薪水：' || v_emp.sal);

end;

如果一个表中，有100个字段，那么你程序如果要使用这100个字段的话，如果使用引用型变量一个个声明，会特别麻烦，记录型变量可以方便的解决这个问题。

记录型变量只能存储一个完整的行数据。

流程控制

条件分支

语法

IF 条件1 THEN 执行1

 ELSIF 条件2 THEN 执行2
 
 ELSE 执行3
 
END IF

案例

-- 根据emp的记录数
-- 如果记录数大于20,则输出对应的结果
-- 如果记录数10-20之间，则输出对应的结果
-- 如果记录数在10以下，则输出对应的结果
declare
  v_number number;
begin

  select count(1) into v_number from emp t; 

  if v_number > 20 then 
       dbms_output.put_line('emp记录数大于20输出：' || v_number);
    elsif v_number >= 10 then 
       dbms_output.put_line('emp记录数大于10输出：' || v_number);
    else 
        dbms_output.put_line('emp记录数小于10输出：' || v_number); 
  end if;

end;

循环

在ORACLE中有三种循环，loop循环，for循环，while循环

Loop循环
-- 语法格式
LOOP
 EXIT WHEN 退出循环条件
END LOOP

案例

declare
  v_number number := 1；
begin
 loop
   exit when v_number > 10;
   dbms_output.put_line(v_number);
   v_number := v_number + 1;
 end loop;   
end;
For循环
-- 案例
for i in 0..5 loop
    dbms_output.put_line(i);
end loop;
While循环
declare 
  i number;
begin
  i := 2;
  while i<=5 loop
     dbms_output.put_line(i);
     i:=i+1;
  end loop;
end;

游标

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

·使用方式
声明，打开，读取，关闭

--游标声明
CURSOR 游标名[(参数列表)] IS 查询语句；
--游标打开
OPEN 游标名
--游标读取
FETCH 游标名 INTO 变量列表;
--游标关闭
CLOSE 游标名

游标的属性

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

其中，%NOTFOUND实在游标中找不到元素的时候返回TRUE，通常用来判断退出循环

无参游标案例

-- 使用游标查询emp表中所有员工的姓名和工资，并将其依次打印出来
declare 
  -- 声明游标
 cursor c_emp is select t.ename,t.sal from emp t;
 
 -- 声明接收的变量
 v_ename emp.ename%TYPE;
 v_sal emp.sal%TYPE;
 
begin
  -- 打开游标
  OPEN c_emp;
  LOOP
  -- 游标读取
  FETCH c_emp INTO v_ename,v_sal;
  
  exit when c_emp%Notfound;
  dbms_output.put_line(v_ename ||'-' || v_sal);
  
  END LOOP;
  -- 关闭游标
  CLOSE c_emp;
  
end;

有参游标案例

-- 使用游标查询emp表中所有员工的姓名和工资,并根据部门查询其对应的结果
declare 
  -- 声明游标
 cursor c_emp(v_deptno emp.deptno%TYPE) is select t.ename,t.sal from emp t where t.deptno = (v_deptno);
 
 -- 声明接收的变量
 v_ename emp.ename%TYPE;
 v_sal emp.sal%TYPE;
 
begin
  -- 打开游标
  OPEN c_emp(30);
  LOOP
  -- 游标读取
  FETCH c_emp INTO v_ename,v_sal;
  
  exit when c_emp%Notfound;
  dbms_output.put_line(v_ename ||'-' || v_sal);
  
  END LOOP;
  -- 关闭游标
  CLOSE c_emp;
  
end;

存储过程

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

可以理解之前的代码全部编写在了main方法中，是匿名程序，JAVA可以通过封装对象和方法来解决复用问题。

PLSQL是将一个个PLSQL的业务处理过程存储起来进行复用，这些被存储起来的PLSQL程序称之为存储过程。

存储过程的作用：

  1. 在开发程序中，为了一个特定的业务功能，会想数据库进行多次连接关闭（连接和关闭是很耗费资源），需要对数据库进行多次I/O读写，性能比较低。如果把这些业务放到PLSQL中，在应用程序中只需要调用PLSQL就可以做的连接关闭一次数据库就可以实现我们的业务，可以大大提高效率。
  2. ORACLE官方建议：能够让数据库操作的不要放在程序中，在数据库中实现基本上不会出错，在程序中操作可能会存在错误(如果在数据库中操作数据，可以有一定的日志恢复等功能)

语法

CREATE OR REPLACE PROCEDURE 过程名称[(参数列表)] is
BEGIN

END[过程名称]

分类

根据参数的类型，分为3类

  1. 不带参数的
  2. 带输入参数的
  3. 带输入输出参数的

无参存储过程

创建存储过程，打开PLSQL，Program Windows$\Rightarrow$Procedure​

create or replace procedure p_helloworld is
-- 在此声明变量
begin
  dbms_output.put_line('helloworld');
end p_helloworld;

-- 调用存储过程，在plsql程序中，直接使用存储过程的名称。
begin
 p_helloworld;
end;
-- 调用存储过程，DOS窗口
exec  p_helloworld;
-- 如果没有输出，需要开启输出
set serveroutput on;

注意：

  1. is和as是可以互用
  2. 存储过程中没哟declare关键字，declare用在语句块中

带输入参数的存储过程

create or replace procedure p_query(i_empno in emp.empno%TYPE) as

v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;


begin
  select t.empno,t.sal into v_ename,v_sal from emp t where t.empno = i_empno;
  dbms_output.put_line(v_ename || '-' || v_sal);
end;

-- 调用存储过程，在plsql程序中，直接使用存储过程的名称。
begin
 p_query(7839);
end;
-- 调用存储过程，DOS窗口
exec p_query(7839);

带输出参数的存储过程

create or replace procedure p_query_out(i_empno in emp.empno%TYPE,o_sal out emp.empno%TYPE) as

begin
  select t.sal into o_sal from emp t where t.empno = i_empno;
  
end;

-- 调用存储过程，在plsql程序中，直接使用存储过程的名称。
declare 
  v_sal emp.sal%TYPE;
begin
  p_query_out(7839,v_sal);
  dbms_output.put_line(v_sal);
end;

Java调用存储过程

public class ProcedureTest {
	public static void main(String[] args) throws Exception {
		
		//1.加载驱动
		Class.forName("oracle.jdbc.driver.OracleDriver");
		
		//2.获取连接对象
		String url = "jdbc:oracle:thin:@192.168.168.128:1521:orcl";
		String username = "scott";
		String password = "tiger";
		Connection connection = DriverManager.getConnection(url,username,password);
		
		//3.调用存储过程
		String sql = "{call p_query_out(?,?)}";  //转义后的SQL语句
		CallableStatement prepareCall = connection.prepareCall(sql);
		
		//4.设置输入参数
		prepareCall.setInt(1, 7839);
		
		//5.注册输出参数
		prepareCall.registerOutParameter(2, OracleTypes.DOUBLE);
		
		//6.执行存储过程
		prepareCall.execute();
		
		//7.获取输出参数
		System.out.println("----------------------");
		System.out.println(prepareCall.getDouble(2));
		System.out.println("----------------------");
		
		//8.关闭连接
		prepareCall.close();
		connection.close();
		
	}
}
上一篇：数据库高级查询（员工部门表）示例2


下一篇：1.1 Oracle数据库学习教程-基本的查询命令