PLSQL编程详细

王小小鸭

已于 2023-07-24 11:20:31 修改

阅读量6.8k

点赞数 9

分类专栏： oracle 文章标签：数据库 oracle

于 2023-07-17 21:06:44 首次发布

本文链接：https://blog.csdn.net/clover_oreo/article/details/131760602

版权

oracle 专栏收录该内容

28 篇文章 1 订阅

订阅专栏

结论：比调方法要简单，调方法要创建对象、实例化对象，通过对象去.方法名；调存储过程只要写过程名就行了

2.在SQLPLUS中通过EXEC命令调用:

注意:调用的时候,参数要与定义的参数的顺序和类型一致.

1.概念和目的

1.1. 什么是PL/SQL?

PL/SQL（Procedure Language/SQL）是Oracle对sql语言的过程化扩展 (类似于Basic)；

指在SQL命令语言中增加了过程处理语句（如分支、循环等），使SQL语言具有过程处理能力。

1.2. 程序结构

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

且不区分大小写。

DECLARE
  -- 声明变量、游标。
  I INTEGER;
BEGIN
  -- 执行语句

  --[异常处理]

END;

其中 DECLARE部分用来声明变量或游标（结果集类型变量），如果程序中无变量声明可以省略掉。

1.3. Hello World

BEGIN
  --打印hello world
  DBMS_OUTPUT.PUT_LINE('hello world');
END;

其中DBMS_OUTPUT 为oracle内置程序包，相当于Java中的System.out,而PUT_LINE（）是调用的方法,相当于println（）方法。

运行结果

sqlplus编译运行

还可以通过sqlplus窗口进行编译运行

win+R→cmd→sqlplus→输入用户名：SCOTT，密码TIGER（登录区分大小写！！！）

 begin
    dbms_output.put_line('hello world');
 end;
    /

SQLPLUS中执行PLSQL程序需要在程序最后添加一个 / 标识程序的结束

执行结束后并未显示输出的结果，默认情况下，输出选项是关闭状态的我们需要开启一下

开启输出：

set serveroutput on

运行结果

Oracle客户端自带的命令窗口也可以实现

1.4. 变量

PLSQL编程中常见的变量分两大类：

普通数据类型（char,varchar2, date, number, boolean, long）
特殊变量类型（引用型变量、记录型变量）

声明变量的方式为

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

1.4.1. 普通变量

变量赋值的方式有两种：

直接赋值语句 := 比如: v_name := 'zhangsan'
语句赋值，使用select …into … 赋值：（语法 select 值 into 变量）

【示例】打印人员个人信息，包括：姓名、薪水、地址

-- 打印人员个人信息，包括： 姓名、薪水、地址
DECLARE
  -- 姓名
  V_NAME VARCHAR2(20) := '张三'; -- 声明变量直接赋值
  --薪水
  V_SAL NUMBER;
  --地址
  V_ADDR VARCHAR2(200);

BEGIN

  --在程序中直接赋值
  V_SAL := 5580;

  --语句赋值
  SELECT '云南昆明' INTO V_ADDR FROM DUAL;

  --打印变量
  DBMS_OUTPUT.PUT_LINE('姓名：' || V_NAME || ',薪水：' || V_SAL || ',地址：' ||V_ADDR);

END;

展示结果

1.4.2. 引用型变量

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

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

【示例】查询emp表中7839号员工的个人信息，打印姓名和薪水

加入引用变量

-- 查询emp表中7839号员工的个人信息，打印姓名和薪水
DECLARE
  -- 姓名
  V_NAME EMP.ENAME%TYPE; -- 声明变量直接赋值
  --薪水
  V_SAL EMP.SAL%TYPE;

BEGIN
  --查询表中的姓名和薪水并赋值给变量
  --注意查询的字段和赋值的变量的顺序、个数、类型要一致
  SELECT ENAME, SAL INTO V_NAME, V_SAL FROM EMP WHERE EMPNO = 7839;

  --打印变量
  DBMS_OUTPUT.PUT_LINE('姓名：' || V_NAME || ',薪水：' || V_SAL);

END;

结果展示

引用型变量的好处：

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

1.4.3. 记录型变量

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

语法：变量名称表名%ROWTYPE，例如： v_emp emp%rowtype;

【示例】

查询并打印7839号员工的姓名和薪水

将emp作为对象，enamel和sal作为属性取值

-- 查询emp表中7839号员工的个人信息，打印姓名和薪水
DECLARE
  -- 记录型变量接受一行
  V_EMP EMP%ROWTYPE;

BEGIN
  --记录型变量默认接受表中的一行数据，不能指定字段。
  SELECT * INTO V_EMP FROM EMP WHERE EMPNO = 7839;

  --打印变量，通过变量名.属性的方式获取变量中的值
  DBMS_OUTPUT.PUT_LINE('姓名：' || V_EMP.ENAME || ',薪水：' || V_EMP.SAL);

END;

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

错误的使用：

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

2．返回的行太多了，记录型变量也接收不了

1.5. 流程控制

1.5.1. 条件分支

语法：

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

注意关键字：ELSIF

根据输入分数给定等级

DECLARE
--定义变量：
V_1 NUMBER:='&输入你的分数';
BEGIN
  --判断数据大小
  IF V_1>=90 THEN
    DBMS_OUTPUT.PUT_LINE('A');
  ELSIF V_1>=80 THEN
    DBMS_OUTPUT.PUT_LINE('B');
  ELSIF V_1>=60 THEN
    DBMS_OUTPUT.PUT_LINE('C');
  ELSE
    DBMS_OUTPUT.PUT_LINE('D');
  END IF;
END;

解释：

当第一个表达式为true则执行第一个语句，若第一个表达式为false则判断第二个表达式，

如果第二个表达式为true执行第二个语句，若第二个表达式为false则判断第三个表达式……

直到最后一个表达式判断仍为false，则执行ELSE中的语句。

if条件分支比较大小

declare
  v_data1 number := '56';
  v_data2 number := '32';
begin
  if v_data1>v_data2 then
    dbms_output.put_line(v_data1 || '大');
    else
      dbms_output.put_line(v_data2 || '大');
      end if;
end;

【示例】判断emp表中记录是否超过20条，10-20之间，或者10条以下

--判断emp表中记录是否超过20条，10-20之间，或者10条以下
DECLARE
  --声明变量接受emp表中的记录数
  V_COUNT NUMBER;
BEGIN
  --查询emp表中的记录数赋值给变量
  SELECT COUNT(1) INTO V_COUNT FROM EMP;
  --判断打印
  IF V_COUNT > 20 THEN
    DBMS_OUTPUT.PUT_LINE('EMP表中的记录数超过了20条为：' || V_COUNT || '条。');
  ELSIF V_COUNT >= 10 THEN
    DBMS_OUTPUT.PUT_LINE('EMP表中的记录数在10~20条之间为：' || V_COUNT || '条。');
  ELSE
    DBMS_OUTPUT.PUT_LINE('EMP表中的记录数在10条以下为：' || V_COUNT || '条。');
  END IF;
END;

1.5.2. 循环

在ORACLE中有三种循环方式，这里我们不展开,只介绍其中一种：loop循环

语法：

BEGIN
  LOOP
  	EXIT WHEN 退出循环条件  
  END LOOP;
END;

【示例】打印数字1-10

--打印数字1-10
DECLARE
  --声明循环变量并赋初值
  V_NUM NUMBER := 1;
BEGIN
  LOOP
    EXIT WHEN V_NUM > 10;
    DBMS_OUTPUT.PUT_LINE(V_NUM);  
    --循环变量自增
    V_NUM := V_NUM + 1;  
  END LOOP;
END;

2. 游标

2.1. 什么是游标

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

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

2.2. 语法

游标声明：

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

游标的打开：

OPEN 游标名;

游标的取值：

FETCH 游标名 INTO 变量列表;

游标的关闭：

CLOSE 游标名;

2.3. 游标的属性

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

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

2.4. 创建和使用

【示例】使用游标查询emp表中所有员工的姓名和工资，并将其依次打印出来。

--使用游标查询emp表中所有员工的姓名和工资，并将其依次打印出来。
DECLARE
  --声明游标
  CURSOR C_EMP IS
    SELECT ENAME, SAL FROM EMP;
  --声明变量用来接受游标中的元素
  V_ENAME EMP.ENAME%TYPE;
  V_SAL EMP.SAL%TYPE;
BEGIN
  --打开游标
  OPEN C_EMP;
  --遍历游标中的值
  LOOP 
    --通过FETCH语句获取游标中的值并赋值给变量
    FETCH C_EMP
      INTO V_ENAME, V_SAL;  
    --通过%NOTFOUND判断是否有值,有值打印,没有则退出循环
    EXIT WHEN C_EMP%NOTFOUND; 
    DBMS_OUTPUT.PUT_LINE('姓名:' || V_ENAME || ',薪水:' || V_SAL);  
  END LOOP;
  --关闭游标
  CLOSE C_EMP;
END;

2.5. 带参数的游标

带参数和不带参数的游标对比一下：

【示例】使用游标查询并打印某部门的员工的姓名和薪资，部门编号为运行时手动输入。

--输出所有部门员工姓名和薪水
 SELECT EMPNO，ENAME, SAL FROM EMP;

 --使用游标查询并打印某部门的员工的姓名和薪资，部门编号为运行时手动输入。
DECLARE
  --声明游标传递参数
  CURSOR C_EMP(V_EMPNO EMP.EMPNO%TYPE) IS
    SELECT ENAME, SAL FROM EMP WHERE EMPNO = V_EMPNO;
  --声明变量用来接受游标中的元素
  V_ENAME EMP.ENAME%TYPE;
  V_SAL EMP.SAL%TYPE;
BEGIN
  --打开游标并传递参数
  OPEN C_EMP(7902);
  --遍历游标中的值
  LOOP
      --通过FETCH语句获取游标中的值并赋值给变量
    FETCH C_EMP
      INTO V_ENAME, V_SAL;
     --通过%NOTFOUND判断是否有值,有值打印,没有则退出循环
       EXIT WHEN C_EMP%NOTFOUND;       
    DBMS_OUTPUT.PUT_LINE('姓名:' || V_ENAME || ',薪水:' || V_SAL);
  END LOOP;
  --关闭游标
  CLOSE C_EMP;
END;

注意:%NOTFOUND属性默认值为FLASE,所以在循环中要注意判断条件的位置.如果先判断在FETCH会导致最后一条记录的值被打印两次(多循环一次默认);

FETCH在后输出结果：（两条记录被重复打印）

FETCH在前：

自我小结：

之所以会多打一次是因为打印之前没有做判断无法终止打印，重复打印后才进入上面的判断，此时打印终止，多输出了一条记录。

3. 存储过程

3.1. 概念作用

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

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

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

存储过程作用：

1，在开发程序中，为了一个特定的业务功能，会向数据库进行多次连接关闭(连接和关闭是很耗费资源), 需要对数据库进行多次I/O读写，性能比较低。如果把这些业务放到PLSQL中，在应用程序中只需要调用PLSQL就可以做到连接关闭一次数据库就可以实现我们的业务,可以大大提高效率.

2， ORACLE官方给的建议：能够让数据库操作的不要放在程序中。在数据库中实现基本上不会出现错误，在程序中操作可能会存在错误.(如果在数据库中操作数据,可以有一定的日志恢复等功能.)

3.2. 语法

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

END [过程名称];

根据参数的类型，我们将其分为3类讲解：

l 不带参数的

l 带输入参数的

l 带输入输出参数(返回值)的。

3.3. 无参存储

3.3.1. 创建存储

通过Plsql Developer或者语句创建存储过程

新建→程序窗口→Procedure→命名

【示例】通过调用存储过程打印hello world

创建存储过程:

create or replace procedure p_hello is
--通过调用存储过程打印hello world
begin
  dbms_output.put_line('hello world');
end p_hello;

通过工具查看创建好的存储过程:

3.3.2. 调用存储过程

通过PLSQL程序调用：

BEGIN
 --直接输入调用存储过程的名称
  P_HELLO;

END P_HELLO;

输出结果

结论：比调方法要简单，调方法要创建对象、实例化对象，通过对象去.方法名；调存储过程只要写过程名就行了

2.在SQLPLUS中通过EXEC命令调用:

还可以通过sqlplus窗口进行编译运行

win+R→cmd→sqlplus→输入用户名：SCOTT，密码TIGER（登录区分大小写！！！）

SQLPLUS中执行PLSQL程序需要在程序最后添加一个 / 标识程序的结束

执行结束后并未显示输出的结果，默认情况下，输出选项是关闭状态的我们需要开启一下

开启输出：

set serveroutput on

运行结果

注意：

第一个问题：is和as是可以互用的，用哪个都没关系的

第二个问题：过程中没有declare关键字，declare用在语句块中

3.4. 带输入参数的存储过程

【示例】查询并打印某个员工（如7839号员工）的姓名和薪水--存储过程：要求，调用的时候传入员工编号，自动控制台打印。

在程序窗口：

--查询并打印某个员工（如7839号员工）的姓名和薪水
--要求，调用的时候传入员工编号，自动控制台打印。
CREATE OR REPLACE PROCEDURE P_QUERYNAMEANDSAL(I_EMPNO IN EMP.EMPNO%TYPE) IS
  --声明变量接受查询结果
  V_ENAME EMP.ENAME%TYPE;
  V_SAL   EMP.SAL%TYPE;

BEGIN

  --根据用户传递的员工号查询姓名和薪水
  SELECT ENAME, SAL INTO V_ENAME, V_SAL FROM EMP WHERE EMPNO = I_EMPNO;

  --打印结果
  DBMS_OUTPUT.PUT_LINE('姓名:' || V_ENAME || ',薪水:' || V_SAL);
  
--结尾两种写法，一种是直接 end； 一种是和开头一样 END P_QUERYNAMEANDSAL;
END;

在sql窗口命令调用：

--输出所有部门员工姓名和薪水
 SELECT EMPNO，ENAME, SAL FROM EMP;
 
declare
  i integer;
begin
  p_querynameandsal(7839);
end;

结果

sqlplus命令调用：

exec p_querynameandsal(7902);

执行结果：

3.5. 带输出参数的存储过程

【示例】输入员工号查询某个员工（7839号员工）信息，要求，将薪水作为返回值输出，给调用的程序使用。

分析：有输入（工号），有输出（薪水）

在程序窗口创建存储

--通过调用存储过程打印hello world
CREATE OR REPLACE PROCEDURE P_HELLO IS
BEGIN

  DBMS_OUTPUT.PUT_LINE('hello world');

END P_HELLO;

PLSQL程序调用：

DECLARE
  --声明一个变量接受存储过程的输出参数
  V_SAL EMP.SAL%TYPE;
BEGIN
  P_QUERYSAL_OUT(7839, V_SAL); --注意参数的顺序
  DBMS_OUTPUT.PUT_LINE(V_SAL);
END;

执行结果

注意:调用的时候,参数要与定义的参数的顺序和类型一致.

3.7. JAVA程序调用存储过程

需求:如果一条语句无法实现结果集, 比如需要多表查询,或者需要复杂逻辑查询,我们可以选择调用存储查询出你的结果.

3.7.1. 分析jdk API

通过Connection对象的prepareCall方法可以调用存储过程

用于执行SOL存储过程的界面。JDBC API提供了存储过程SQL转义语法，允许以标准方式为所有RDBMS调用存储过程。此转义语法包含一个结果参数和不包含结果参数的表单。

如果使用，结果参数必须注册为OUT警数。其他参数可用于输入，输出或两者。参数按顺序依次引用，第一个参数为1。
{?= call sprocedure-name>[(<arg1>,<arg2>，...)]} 存储函数必须有返回值（不灵活）

{call <procedure-name>[(<arg1>,<arg2>，...)]} 存储过程没有返回值（推荐）
IN参数值使用从Preparedstatement继承的set方法设置。所有OUT参数的类型必须在执行存储过程之前进行注册;它们的值通过这里提供的get方法在执行get。
A CallableStatement可以返回一个ResultSet对象或多ResultSet对象。多个ResultSet对象使用从Statement ResultSet操作来处理。
为获得最大的便携性，应在处理输出参数值之前处理一个电话ResultSet对象和更新计数。

得出结论:

通过Connection对象调用prepareCall方法传递一个转义sql语句调用存储过程, 输入参数直接调用set方法传递.输出参数需要注册后,执行存储过程,通过get方法获取.参数列表的下标是从1开始的

3.7.2. 实现代码

准备环境：

导入Oracle的jar包

【示例】通过员工号查询员工的姓名和薪资

在idea进行操作

新建项目

新建模块

新建java模块jdbctest 在jdbctest文件下新建一个lib目录

将对应ojdbc6的jar包复制到lib下

添加为库

在src下创建一个类cn.wxx.oracle.test.JdbcTest

实现代码

package cn.itcast.oraclle.test;
import oracle.jdbc.OracleTypes;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;

/*存储过程 通过员工号查询员工的姓名和薪资*/
public class JdbcTest {
    public static void main(String[] args) throws  Exception {
        //1.加载驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");

        //2.获取连接对象
        String url = "jdbc:oracle:thin:@localhost:1521:xe";
        String user ="SCOTT";
        String password = "TIGER";
        Connection conn = DriverManager.getConnection(url, user, password);
        
        //3.获得语句对象
        String sql = "{call p_querysal_out(?,?)}";
        CallableStatement call = conn.prepareCall(sql);

        //4.设置输入参数
        call.setInt(1,7839);

        //5.注册输出参数
        call.registerOutParameter(2, OracleTypes.DOUBLE);

        //6.执行储存过程
        call.execute();

        //7.获取输出参数
        double sal = call.getDouble(2);
        System.out.println("该工号员工薪资为："+sal);

        //8.释放资源
        call.close();
        conn.close();
    }
}

输出展示：

王小小鸭

关注

9
点赞
踩
92

收藏

觉得还不错? 一键收藏
0
评论
PLSQL编程详细

用于临时存储一个查询返回的多行数据（结果集,类似于Java的Jdbc连接返回的ResultSet集合），通过遍历游标，可以逐行访问处理该结果集的数据。游标的使用方式：声明--->打开--->读取--->关闭。
复制链接

扫一扫