PL/SQL语言

最新推荐文章于 2023-09-03 12:28:52 发布

xy58451921

最新推荐文章于 2023-09-03 12:28:52 发布

阅读量305

点赞数

分类专栏：数据库文章标签： sql 数据库 java Powered by 金山文档

本文链接：https://blog.csdn.net/xy58451921/article/details/129278764

版权

数据库专栏收录该内容

5 篇文章 0 订阅

订阅专栏

一、PLSQL编程

1. 概念和目的

什么是PL/SQL?

PL/SQL（Procedure Language/SQL）

PLSQL是Oracle对sql语言的过程化扩展 (类似于Basic)

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

存储过程的好处：减少数据库与服务器的网络交互

2.程序结构

通过Plsql Developer工具的Test Window 创建程序模版或者通过语句在SQL Window编写

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

Beautiful一下大写换小写

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

PL/SQL的编写：test Window 相当于右击新建了.java文件

-- Created on 2018/3/21 by ADMINISTRATOR

DECLARE
  -- 声明变量、游标。  
  -- 如果程序没有变量 是可以省略的
  I INTEGER;
  i integer；
BEGIN
  -- 执行语句
  -- 相当于方法体
  --[异常处理]

END;

3.Hello World

BEGIN

  --打印hello world
  dbms_output.put_line('hello world');
  DBMS_OUTPUT.PUT_LINE('hello world');

END;

F8 执行并且控制台输出 dbms_output中输出

在command Window中也可以编写运行PLSQL程序：

SQL>setserveroutputon
SQL>BEGIN
  2
  3    --打印helloworld
  4
  5    DBMS_OUTPUT.PUT_LINE('hello world');
  6
  7  END;
  8  /

/ --用以表示PL/SQL过程已成功完成。

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

4.变量

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

普通数据类型（char,varchar2, date, number, boolean, long）

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

声明变量的方式为

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

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

4.1. 普通变量

变量赋值的方式有两种：

直接赋值语句 := 比如: v_name := 'zhangsan'

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

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

-- 打印人员个人信息，包括：姓名、薪水、地址

DECLARE
  -- 姓名
  V_NAME VARCHAR2(20) := 'zhangsan'; -- 声明变量直接赋值
  --薪水
  V_SAL NUMBER;
  --地址
  V_ADDR VARCHAR2(200);

BEGIN

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

  --语句赋值  select不能单独使用 要配合from
  SELECT'上海市传智播客'INTO V_ADDR FROMDUAL;

  --打印变量   || 相当于字符拼接
  DBMS_OUTPUT.PUT_LINE('姓名：' || V_NAME || ',薪水：' || V_SAL || ',地址：' ||V_ADDR);

END;

4.2. 引用型变量

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

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

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

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

DECLARE
  -- 姓名
  V_NAME EMP.ENAME%TYPE; -- 声明变量直接赋值
  --薪水
  V_SAL EMP.SAL%TYPE;

BEGIN
  --查询表中的姓名和薪水并赋值给变量
  --注意查询的字段和赋值的变量的顺序、个数、类型要一致   查出 EMPNO = 7839 对应的姓名和性别
  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更加灵活，更加适应于对数据库定义的更新。

4.3. 记录型变量

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

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

【示例】

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

-- 查询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．返回的行太多了，记录型变量也接收不了

5.流程控制

5.1. 条件分支

语法：

BEGIN

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

注意关键字：ELSIF

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

DECLARE
  --声明变量接受emp表中的记录数
  V_COUNT NUMBER;

BEGIN

  --查询emp表中的记录数赋值给变量

  SELECTCOUNT(1) INTO V_COUNT FROM EMP;

  --判断打印

  IF V_COUNT > 20THEN
    DBMS_OUTPUT.PUT_LINE('EMP表中的记录数超过了20条为：' || V_COUNT || '条。');

  ELSIF V_COUNT >= 10THEN
    DBMS_OUTPUT.PUT_LINE('EMP表中的记录数在10~20条之间为：' || V_COUNT || '条。');

  ELSE
    DBMS_OUTPUT.PUT_LINE('EMP表中的记录数在10条以下为：' || V_COUNT || '条。');

  ENDIF;

END;

5.2. 循环

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

语法：

BEGIN
  LOOP
  EXITWHEN 退出循环条件  
  ENDLOOP;
END;

【示例】打印数字1-10

DECLARE
  --声明循环变量并赋初值
  V_NUM NUMBER := 1;

BEGIN

  LOOP
  
    EXITWHEN V_NUM > 10;
    
    DBMS_OUTPUT.PUT_LINE(V_NUM);
  
    --循环变量自增
    V_NUM := V_NUM + 1;
  
  ENDLOOP;

END;

6.游标

6.1 什么是游标

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

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

6.2语法

游标声明：

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

游标的打开：

OPEN 游标名;

游标的取值：

FETCH 游标名 INTO 变量列表;

游标的关闭：

CLOSE 游标名;

6.3 游标的属性

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

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

6.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判断是否有值,有值打印,没有则退出循环
    EXITWHEN C_EMP%NOTFOUND;
  
    DBMS_OUTPUT.PUT_LINE('姓名:' || V_ENAME || ',薪水:' || V_SAL);
  
  ENDLOOP;

  --关闭游标
  CLOSE C_EMP;

END;

6.5 带参数的游标

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

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

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(10);

  --遍历游标中的值
  LOOP
       --通过%NOTFOUND判断是否有值,有值打印,没有则退出循环
       EXITWHEN C_EMP%NOTFOUND;
       
       --通过FETCH语句获取游标中的值并赋值给变量
    FETCH C_EMP
      INTO V_ENAME, V_SAL;

    DBMS_OUTPUT.PUT_LINE('姓名:' || V_ENAME || ',薪水:' || V_SAL);


  ENDLOOP;

  --关闭游标
  CLOSE C_EMP;

END;

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