17.Oracle11g的PL/SQL基础

news/2024/12/22 1:18:05/

Oracle11g的PL/SQL基础

  • 一、PL/SQL的体系
    • 1、什么是PL/SQL
    • 2、PL/SQL 的优缺点
      • 2.1 PL/SQL的优点
      • 2.2 PL/SQL的缺点
  • 二、PL/SQL的语法
    • 1、PL/SQL代码结构（块）
    • 2、PL/SQL基本语法
      • 2.1 变量声明
      • 2.2 流程控制语法
  • 三、oracle的动态SQL

一、PL/SQL的体系

在这里插入图片描述

1、什么是PL/SQL

  • PL/SQL 是过程语言(Procedural Language)与结构化查询语言(SQL)结合而成的编程语言。
  • PL/SQL 是对 SQL 的扩展支持多种数据类型，如大对象和集合类型，可使用条件和循环等控制结构。
  • 可用于创建存储过程、触发器和程序包，给SQL语句的执行添加程序逻辑。
  • 与 Oracle 服务器和 Oracle 工具紧密集成，具备可移植性、灵活性和安全性。
  • PL/SQL 引擎驻留在 Oracle 服务器中,该引擎接受 PL/SQL 块并对其进行编译执行。

2、PL/SQL 的优缺点

2.1 PL/SQL的优点

  • 支持 SQL，在 PL/SQL 中可以使用：
    • 数据操纵命令
    • 事务控制命令
    • 游标控制
    • SQL 函数和 SQL 运算符
  • 用户把PL/SQL块整个发送到服务器端，oracle服务器端编译、运行，再把结果返回给用户
  • 可移植性，可运行在任何操作系统和平台上的Oralce 数据库
  • 更佳的性能，PL/SQL 经过编译执行
  • 安全性，可以通过存储过程限制用户对数据的访问
  • 与 SQL 紧密集成，简化数据处理。
    • 支持所有 SQL 数据类型
    • 支持 NULL 值
    • 支持 %TYPE 和 %ROWTYPE 属性类型

2.2 PL/SQL的缺点

  • 可读性：PL/SQL的语法比较复杂，特别是当处理复杂的逻辑和大量的代码时，可能会导致代码的可读性降低。这可能会增加代码维护和调试的难度。

  • 性能：与使用其他编程语言进行开发相比，PL/SQL可能在性能方面存在一些局限性。某些情况下，执行复杂的计算或处理大量数据时，PL/SQL可能不如其他编程语言效率高。

  • 约束：PL/SQL在一定程度上与Oracle数据库紧密耦合，这可能会导致代码的可移植性受到限制。如果想将代码迁移到其他数据库系统中，可能需要做一些改动和调整。

  • 缺乏某些编程特性：相较于其他编程语言，PL/SQL在某些编程特性方面可能受到限制。例如，它可能不如其他语言提供丰富的库和框架，或者可能不支持某些现代化的编程概念和技术。

二、PL/SQL的语法

1、PL/SQL代码结构（块）

  • PL/SQL 块是构成 PL/SQL 程序的基本单元
  • 将逻辑上相关的声明和语句组合在一起
  • PL/SQL 分为三个部分，声明部分、可执行部分和异常处理部分

  1. 声明部分：

    DECLARE-- 声明变量、常量、游标等
BEGIN-- 可执行部分的代码
EXCEPTION-- 异常处理部分的代码
END;

  2. 可执行部分：

    BEGIN-- 可执行部分的代码
END;

  3. 异常处理部分：

    EXCEPTION-- 异常处理部分的代码

  4. 具体案例
      当创建一个存储过程时，通常会包含声明部分、可执行部分和异常处理部分。以下是一个包含这三部分的PL/SQL存储过程的示例：

    CREATE OR REPLACE PROCEDURE process_employee_data (employee_id IN NUMBER) 
ISv_employee_name employees.first_name%TYPE;
BEGIN-- 可执行部分SELECT first_name INTO v_employee_nameFROM employeesWHERE employee_id = process_employee_data.employee_id;-- 输出员工姓名DBMS_OUTPUT.PUT_LINE('Employee name is ' || v_employee_name);EXCEPTION-- 异常处理部分WHEN NO_DATA_FOUND THENDBMS_OUTPUT.PUT_LINE('Employee not found');WHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;
/

      在这个例子中，DECLARE部分被省略了，因为在存储过程中声明部分是可选的。BEGIN和END之间的部分是可执行部分，包括了对employees表的查询和输出员工姓名的代码。EXCEPTION部分用于处理可能发生的异常情况，比如在查询中找不到员工信息或者其他错误。

  在PL/SQL块中，声明部分是可选的，而可执行部分是必需的。异常处理部分也是可选的，但通常在编写PL/SQL块时都会包含异常处理部分，以处理可能发生的异常情况。

2、PL/SQL基本语法

  当谈到Oracle 11g的PL/SQL基础时，有几个关键点需要注意。PL/SQL是Oracle数据库的编程语言，它结合了SQL语句和程序设计语言的特性，允许开发人员在数据库中创建存储过程、函数和触发器。

2.1 变量声明

  在PL/SQL中，变量声明是用于指定变量名称、数据类型和初始值的语句，可以声明各种类型的变量，如整数、浮点数、字符等。变量声明可以出现在PL/SQL块的任何位置，但通常在声明部分进行声明。以下是PL/SQL变量声明的基本语法：

DECLAREvariable_name [CONSTANT] data_type [NOT NULL] [:= initial_value];...
BEGIN...
END;

  其中，variable_name是变量的名称，data_type是变量的数据类型，initial_value是变量的初始值，CONSTANT用于声明常量，NOT NULL用于声明变量不允许为空。

以下是一些常用的数据类型和示例：

  • NUMBER: 数字类型，可以指定精度和范围。例如：salary NUMBER(8,2);
  • VARCHAR2: 可变长度字符串类型，最大长度为4000个字节。例如：employee_name VARCHAR2(50);
  • DATE: 日期类型，包括年、月、日、时、分、秒。例如：hire_date DATE := SYSDATE;
  • BOOLEAN: 布尔类型，只有两个可能的值：TRUE和FALSE。例如：is_valid BOOLEAN := TRUE;
  • CURSOR: 游标类型，用于遍历结果集。例如：CURSOR c1 IS SELECT * FROM employees;

在PL/SQL中，变量的命名规则与其他编程语言类似，应该遵循一些基本规则，如：

  • 变量名应该以字母开头，不能以数字或特殊字符开头。
  • 变量名应该具有描述性，能够清楚地表达变量的用途。
  • 变量名不能与PL/SQL关键字重名。

2.2 流程控制语法

  在PL/SQL中，条件控制语句用于根据指定条件执行不同的代码块。PL/SQL中的条件控制语句包括IF语句、CASE语句和循环语句。下面分别介绍这些条件控制语句的基本语法和用法。

2.2.1 条件控制

  PL/SQL提供了条件控制结构，如IF-THEN、IF-THEN-ELSE和CASE语句，可以根据条件执行不同的代码块。

  • IF语句
    IF语句用于根据条件执行不同的代码块。基本的IF语句语法如下：

    IF condition THEN-- 如果条件为真，则执行这里的代码块
ELSIF condition2 THEN-- 如果条件2为真，则执行这里的代码块
ELSE-- 如果以上条件都不为真，则执行这里的代码块
END IF;

    示例：

    DECLAREv_salary employees.salary%TYPE := 5000;
BEGINIF v_salary > 10000 THENDBMS_OUTPUT.PUT_LINE('High salary');ELSIF v_salary > 5000 THENDBMS_OUTPUT.PUT_LINE('Medium salary');ELSEDBMS_OUTPUT.PUT_LINE('Low salary');END IF;
END;

  • CASE语句
    CASE语句用于根据一个表达式的值选择要执行的代码块。它类似于其他编程语言中的switch语句。基本的CASE语句语法如下：

    CASE expressionWHEN value1 THEN-- 如果表达式的值等于value1，则执行这里的代码块WHEN value2 THEN-- 如果表达式的值等于value2，则执行这里的代码块...ELSE-- 如果表达式的值不等于任何值，则执行这里的代码块
END CASE;

    示例：

    DECLAREv_job employees.job_id%TYPE := 'MANAGER';
BEGINCASE v_jobWHEN 'MANAGER' THENDBMS_OUTPUT.PUT_LINE('Manager');WHEN 'CLERK' THENDBMS_OUTPUT.PUT_LINE('Clerk');ELSEDBMS_OUTPUT.PUT_LINE('Other job');END CASE;
END;

2.2.2 循环控制

  在PL/SQL中，循环语句用于重复执行一段代码块，直到满足某个条件或达到某个次数。PL/SQL提供了多种类型的循环语句，包括LOOP循环、WHILE循环和FOR循环。下面分别介绍这些循环语句的基本语法和用法。

  • LOOP循环
    LOOP循环是最基本的循环语句，它会无限循环执行代码块，直到遇到EXIT语句或条件不再满足。基本的LOOP循环语法如下：

    LOOP-- 这里是要重复执行的代码块-- 可以在代码块中使用EXIT来跳出循环
END LOOP;

    示例：

    DECLAREv_counter NUMBER := 1;
BEGINLOOPDBMS_OUTPUT.PUT_LINE('Counter: ' || v_counter);v_counter := v_counter + 1;EXIT WHEN v_counter > 5;END LOOP;
END;

  • WHILE循环：
    WHILE循环会在每次循环之前检查条件，只有当条件为真时才会执行代码块。基本的WHILE循环语法如下：

    WHILE condition LOOP-- 如果条件为真，则执行这里的代码块
END LOOP;

    示例：

    DECLAREv_counter NUMBER := 1;
BEGINWHILE v_counter <= 5 LOOPDBMS_OUTPUT.PUT_LINE('Counter: ' || v_counter);v_counter := v_counter + 1;END LOOP;
END;

  • FOR循环：
    FOR循环用于指定循环的次数，它会在每次循环中自动增加计数器的值。基本的FOR循环语法如下：

    FOR counter IN range LOOP-- 这里是要重复执行的代码块
END LOOP;

    示例：

    DECLAREv_total NUMBER := 0;
BEGINFOR i IN 1..5 LOOPv_total := v_total + i;END LOOP;DBMS_OUTPUT.PUT_LINE('Total: ' || v_total);
END;

  这些循环语句在PL/SQL中非常常用，可以根据不同的需求选择合适的循环类型来实现代码的重复执行。合理使用循环语句可以简化代码逻辑，提高代码的可读性和可维护性。

2.2.3 顺序结构

  • GOTO语句：
    GOTO语句是一种无条件转移语句，它可以使程序跳转到指定的标签处继续执行。在PL/SQL中，GOTO语句的语法如下：

    GOTO label;
...
label:
-- 这里是要执行的代码块

      在上面的语法中，label是一个标签，可以是任何合法的标识符。当执行到GOTO语句时，程序会跳转到指定的标签处继续执行。GOTO语句的使用应该尽量避免，因为它会使程序的流程变得混乱，难以理解和维护。通常情况下，可以通过合理的程序设计和结构化的控制流程来避免使用GOTO语句。

  • NULL语句：
    NULL语句是一种空语句，它不执行任何操作，仅仅是占用一个语句的位置。在PL/SQL中，NULL语句可以用于占位符或者在条件控制语句中表示空语句块。例如：

    IF condition THEN-- 如果条件为真，则执行这里的代码块
ELSENULL; -- 如果条件为假，则不执行任何操作
END IF;

      在上面的例子中，当条件为假时，NULL语句表示不执行任何操作。NULL语句通常用于在条件控制语句中表示空语句块，或者作为占位符使用。

  总的来说，顺序控制是程序执行的基本方式，而GOTO语句应该尽量避免使用，因为它会使程序的流程变得混乱，难以理解和维护。而NULL语句则可以在一些特殊情况下使用，例如在条件控制语句中表示空语句块。

  • oracle11g新增了continue语句
    continue语句可在循环中使用。该语句可将逻辑移到循环结尾，然后再移到循环开头。 例如：

    declarej number:=1;
beginloopj:=j+1;exit      when j>8;continue  when j>4;dbms_output.put_line(to_char(j)||'---');end loop;
end;

      这是一段PL/SQL代码，它使用了循环控制语句来控制程序的流程。具体来说，这段代码使用了loop、exit和continue三个关键字来实现循环控制。

      首先，代码声明了一个变量j，并将其初始化为1。然后使用loop关键字开始一个循环，循环体内部包含三个语句：j:=j+1、exit和continue。其中，j:=j+1表示每次循环j的值加1；exit表示当j>8时退出循环；continue表示当j>4时跳过本次循环，继续执行下一次循环。

三、oracle的动态SQL

  动态SQL是指在程序运行时动态构建SQL语句并执行的一种技术。在Oracle数据库中，动态SQL通常通过使用EXECUTE IMMEDIATE语句来实现。动态SQL的主要特点是可以根据程序运行时的条件动态生成SQL语句，从而实现更灵活的数据操作。

动态SQL通常用于以下情况：

  1. 动态生成查询条件：根据用户输入或程序运行时的条件动态生成查询条件，以实现动态的数据查询。

  2. 动态表名和列名：有时需要根据程序运行时的条件来确定表名和列名，动态SQL可以实现这样的需求。

  3. 动态DDL语句：例如创建表、修改表结构等操作，可以使用动态SQL来实现。

在Oracle中，可以使用EXECUTE IMMEDIATE语句来执行动态SQL，其基本语法如下：

EXECUTE IMMEDIATE dynamic_sql_string [INTO {define_variable[, define_variable]... | record}];

  其中，dynamic_sql_string是一个包含动态SQL语句的字符串，可以是任意合法的SQL语句，如SELECT、INSERT、UPDATE、DELETE等。INTO子句用于将查询结果存储到变量或记录中。

以下是一个简单的动态SQL的示例：

DECLAREsql_stmt VARCHAR2(200);emp_name employees.last_name%TYPE;emp_id employees.employee_id%TYPE;
BEGINsql_stmt := 'SELECT employee_id, last_name FROM employees WHERE employee_id = :id';EXECUTE IMMEDIATE sql_stmt INTO emp_id, emp_name USING 100;DBMS_OUTPUT.PUT_LINE('Employee name is ' || emp_name);
END;

  在上面的示例中，首先声明了一个字符串变量sql_stmt，然后动态构建了一个SELECT语句并将其赋给sql_stmt。接着使用EXECUTE IMMEDIATE语句执行动态SQL，并将查询结果存储到emp_id和emp_name变量中。

  需要注意的是，动态SQL的使用需要谨慎，因为动态SQL可能会存在SQL注入等安全风险。在构建动态SQL时，应该尽量避免直接拼接用户输入的内容，而是应该使用绑定变量或者参数化查询的方式来构建动态SQL，以提高安全性。


http://www.ppmy.cn/news/1231043.html

相关文章

大厂秋招真题【栈】Bilibili2019秋招-简单表达式求值

大厂秋招真题【栈】Bilibili2019秋招-简单表达式求值

文章目录 题目描述与示例题目描述输入描述输出描述示例输入输出 解题思路代码PythonJavaC时空复杂度 华为OD算法/大厂面试高频题算法练习冲刺训练 题目描述与示例 题目描述 给定一个合法的表达式字符串&#xff0c;其中只包含非负整数、加法、减法以及乘法符号&#xff08;不…
阅读更多...
微信小程序配置企业微信的在线客服

微信小程序配置企业微信的在线客服

配置企业微信后台 代码实现 <button tap"openCustomerServiceChat">打开企业微信客服</button>methods: {openCustomerServiceChat(){wx.openCustomerServiceChat({extInfo: {url: 你刚才的客服地址},corpId: 企业微信的id,showMessageCard: true,});} …
阅读更多...
SpringBoot学习笔记-配置MySQL与实现注册登录模块（中）

SpringBoot学习笔记-配置MySQL与实现注册登录模块（中）

笔记内容转载自 AcWing 的 SpringBoot 框架课讲义&#xff0c;课程链接&#xff1a;AcWing SpringBoot 框架课。 CONTENTS 1. 配置JWT验证2. 实现验证登录API3. 实现返回信息API4. 实现注册账号API 本节实现用适合前后端分离的 JWT 验证替代传统的 Session 验证方式&#xff0c…
阅读更多...
音视频项目—基于FFmpeg和SDL的音视频播放器解析（十五）

音视频项目—基于FFmpeg和SDL的音视频播放器解析（十五）

介绍 在本系列&#xff0c;我打算花大篇幅讲解我的 gitee 项目音视频播放器&#xff0c;在这个项目&#xff0c;您可以学到音视频解封装&#xff0c;解码&#xff0c;SDL渲染相关的知识。您对源代码感兴趣的话&#xff0c;请查看基于FFmpeg和SDL的音视频播放器 如果您不理解本…
阅读更多...
算法升级之路（七）-盛最多水的容器

算法升级之路（七）-盛最多水的容器

给定一个长度为 n 的整数数组 height 。有 n 条垂线&#xff0c;第 i 条线的两个端点是 (i, 0) 和 (i, height[i]) 。 找出其中的两条线&#xff0c;使得它们与 x 轴共同构成的容器可以容纳最多的水。 返回容器可以储存的最大水量。 原题链接: 盛最多水的容器 解题思路&…
阅读更多...
Wireshark的数据包它来啦！

Wireshark的数据包它来啦！

通过Wireshark工具&#xff0c;可以轻松的看到网卡的数据信息。通过Wireshark显示的数据包内容信息&#xff0c;通常分七栏&#xff0c;介绍一下&#xff1a; 1No.&#xff1a; 数据包编号。 2.Time Time显示时间&#xff0c;以1号数据包发生开始计时。 3.Source Source显示内容…
阅读更多...
.a文件和.so文件

.a文件和.so文件

C 中的 .a 文件和 .so 文件是两种不同类型的库文件&#xff0c;它们有以下区别&#xff1a; .a 文件&#xff08;静态库文件&#xff09;&#xff1a; 静态库文件是编译时链接的库&#xff0c;它将所有需要的函数和符号都打包在一个文件中。在编译时&#xff0c;编译器将静态…
阅读更多...
Linux嵌入式input子系统

Linux嵌入式input子系统

input子系统框架 设备驱动使用内核提供的接口&#xff0c;向内核上报输入事件&#xff0c;内核处理输入事件并且给用户层提供接口 1.内核用input_dev结构体表示一个输入设备(鼠标&#xff0c;键盘&#xff0c;触摸屏). 2.输入设备需要向内核上报一个事件&#xff0c;内核中用…
阅读更多...
最新文章

Copyright @ 2022~2023