数据库武林秘史(二)PL/SQL块结构的入门心法

青年数据库学习互助会 2024-12-13

147

点击上方蓝字，关注我们

想学会更多实用技巧，欢迎加入青学会MOP技术社区（实名社区）。

加入方法：公众号后台回复关键字“加入”获取小助手微信，添加后登记入会。

同时欢迎大家在评论区留言互动交流！社区会不定期举行相关的抽奖、公开分享活动。

如果你有想了解的知识点希望我们发文可以后台私信。

正文开始

在数据处理的江湖中，PL/SQL（Procedural Language/SQL）作为Oracle数据库的核心编程语言，犹如一位隐秘而强大的武者，内藏无尽力量。它不仅继承了SQL对数据查询和操作的强大能力，更引入了过程化编程的丰富特性，使得开发者能够在数据库层面上实现复杂的业务逻辑。

一个月前，小倩初涉这片领域，如今她带着更多的疑问和渴望，再次站在智海大师面前。这一次，他们将聚焦于PL/SQL中最基本也是最重要的概念——PL/SQL块结构和组成元素。这不仅是编写高效、可维护代码的关键，更是成为一位优秀数据库开发者的必经之路。

秘籍：师傅传授PL/SQL块结构秘籍，讲解声明、执行和异常处理部分。

智海大师说：“PL/SQL程序由三个块组成，即声明部分、执行部分和异常处理部分。这就像是练武之人的招式，每一部分都有其独特的作用和意义。”



DECLARE
   -- 声明部分：在此声明PL/SQL用到的变量、类型及游标，以及局部的存储过程和函数
BEGIN
   -- 执行部分：过程及SQL语句，即程序的主要部分
EXCEPTION
   -- 执行异常部分：错误处理
END;

小倩认真地记下了这段代码，她知道这是PL/SQL块的基础结构。

修炼：小白DBA按照秘籍修炼，逐步掌握PL/SQL块的结构和组成元素。

智海大师继续说：“PL/SQL块可以分为三类：无名块、子程序和触发器。无名块是动态构造的，只能执行一次；子程序是存储在数据库中的存储过程、函数及包等；触发器则是数据库操作时自动执行的程序。”

小倩点了点头，她开始尝试编写一个简单的PL/SQL块，来实践她所学的知识。

DECLARE
   v_employee_id EMPLOYEE.EMPLOYEE_ID%TYPE := 100;
BEGIN
   -- 执行部分：插入一条员工记录
   INSERT INTO employees (employee_id, first_name, last_name, email)
   VALUES (v_employee_id, '小倩', 'DBA', 'xiaoqian@moonshot.cn');
   COMMIT;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;

实战：通过实战演练，小白DBA学会如何在程序中使用PL/SQL块。

智海大师看着小倩的操作，微笑着说：“很好，你已经掌握了基本的PL/SQL块结构。现在，让我们通过一些实战案例来加深你的理解。”

案例1：使用RETURNING子句检索INSERT语句中所影响的数据行数

智海大师指导小倩：“RETURNING子句不仅可以用于检索INSERT语句中所影响的数据行数，还可以将列表达式、ROWID和REF值返回到输出变量中。”

DECLARE
   v_rowid EMPLOYEE.ROWID%TYPE;
   v_info VARCHAR2(100);
BEGIN
   INSERT INTO employees (employee_id, first_name, last_name, email)
   VALUES (1000, '智海', '大师', 'zhihai@moonshot.cn')
   RETURNING rowid, first_name || ' ' || last_name INTO v_rowid, v_info;
   
   DBMS_OUTPUT.PUT_LINE('ROWID: ' || v_rowid);
   DBMS_OUTPUT.PUT_LINE('Info: ' || v_info);
END;

案例2：使用记录类型存储逻辑相关的数据

智海大师继续说：“记录类型是把逻辑相关的数据作为一个单元存储起来，这对于处理复杂的数据结构非常有用。”

DECLARE
   TYPE employee_record IS RECORD (
      employee_id EMPLOYEE.EMPLOYEE_ID%TYPE,
      first_name EMPLOYEE.FIRST_NAME%TYPE,
      last_name EMPLOYEE.LAST_NAME%TYPE
   );
   v_employee employee_record;
BEGIN
   SELECT employee_id, first_name, last_name
   INTO v_employee
   FROM employees
   WHERE employee_id = 1000;
   DBMS_OUTPUT.PUT_LINE('Employee: ' || v_employee.first_name || ' ' || v_employee.last_name);
END;

案例3：使用%TYPE和%ROWTYPE简化数据类型定义

智海大师解释道：“使用%TYPE和%ROWTYPE可以简化数据类型的定义，使代码更加灵活和可维护。”

DECLARE
   TYPE employee_type IS TABLE OF employees%ROWTYPE INDEX BY BINARY_INTEGER;
   v_employees employee_type;
BEGIN
   FOR i IN 1..5 LOOP
      SELECT *
      INTO v_employees(i)
      FROM employees
      WHERE employee_id = i;
   END LOOP;
   FOR i IN v_employees.FIRST..v_employees.LAST LOOP
      DBMS_OUTPUT.PUT_LINE('Employee ' || i || ': ' || v_employees(i).first_name);
   END LOOP;
END;

案例4：使用LOB类型处理大对象

智海大师说：“LOB类型用于存储大的数据对象，如BLOB、CLOB等。”

DECLARE
   v_clob CLOB;
BEGIN
   SELECT dbms_lob.substr(clob_column, 4000, 1)
   INTO v_clob
   FROM large_objects
   WHERE id = 1;
   DBMS_OUTPUT.PUT_LINE('CLOB Data: ' || v_clob);
END;

案例5：使用Bind变量和INDEX BY TABLES

智海大师继续指导：“Bind变量和INDEX BY TABLES在处理动态数据和集合时非常有用。”

DECLARE
   VARIABLE v_salary NUMBER;
   TYPE salary_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
   v_salaries salary_table;
BEGIN
   FOR i IN 1..5 LOOP
      SELECT salary
      INTO v_salaries(i)
      FROM employees
      WHERE employee_id = i;
   END LOOP;
   FOR i IN v_salaries.FIRST..v_salaries.LAST LOOP
      DBMS_OUTPUT.PUT_LINE('Salary for Employee ' || i || ': ' || v_salaries(i));
   END LOOP;
END;

案例6：数据类型的转换

智海大师强调：“在PL/SQL中，数据类型的转换是常见的操作，需要熟练掌握。”

DECLARE
   v_char VARCHAR2(10) := '123';
   v_number NUMBER;
BEGIN
   v_number := TO_NUMBER(v_char);
   DBMS_OUTPUT.PUT_LINE('Number: ' || v_number);
END;

案例7：变量赋值和作用范围

智海大师说：“变量的赋值和作用范围是PL/SQL编程中的重要概念。”

DECLARE
   v_global VARCHAR2(20) := 'Global Variable';
BEGIN
   DECLARE
      v_local VARCHAR2(20) := 'Local Variable';
   BEGIN
      DBMS_OUTPUT.PUT_LINE('Local: ' || v_local);
      DBMS_OUTPUT.PUT_LINE('Global: ' || v_global);
   END;
   DBMS_OUTPUT.PUT_LINE('Global: ' || v_global);
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;

案例8：注释的使用

智海大师最后说：“注释是代码的一部分，它帮助我们理解和维护代码。”

-- 这是一个单行注释
/*
  这是一个多行注释
  用于解释复杂的逻辑或代码块
*/

夜幕低垂，智海大师的居所内灯光依旧明亮。小倩站在门外，心中既兴奋又紧张。过去几个月，在大师的指导下，她已经熟练掌握了PL/SQL块结构和组成元素。当她推开门时，智海大师的话让她愣住了。

“你做得很好，但前方还有更险峻的挑战——PL/SQL的流程控制。”大师的目光深邃，“掌握条件判断与循环等结构，才能真正掌控程序流向，实现复杂的逻辑处理。”

小倩心中一震，意识到自己还有更多需要学习。智海大师承诺，下次见面将传授这些关键技巧，并分享一位前辈的故事，这位前辈曾以精湛的流程控制技艺化解了一场数据库危机，被誉为“数据库之王”。

带着新的目标和期待，小倩离开了智海山，准备迎接即将到来的挑战，书写属于自己的传奇。

END

往期文章回顾

MOP社区新闻

青学会MOP技术社区成立了！

青学会专家顾问团成员介绍

金仓专栏

告别繁琐！KingbaseES v9数据库一键安装-青学会&金仓专栏（1）

KingbaseES v9数据库Docker安装-青学会&金仓专栏（2）

KingbaseES数据脱敏-青学会&金仓专栏（3）

KingbaseES后台服务管理-青学会&金仓专栏（4）

电科金仓KES日常运维命令集锦-青学会&金仓专栏（5）

DBA实战小技巧

推荐一款超实用的openGauss数据库安装工具！

  实战：记一次RAC故障排查
  DBA实战运维小技巧安装篇（一）Oracle 主流版本不同架构下的静默安装指南
  DBA实战运维小技巧存储篇（一）根目录满了如何处理
  DBA实战运维小技巧存储篇（二）打包迁移单机数据库至新存储

MOP社区投稿-内核开发

浅谈 PostgreSQL GUC 模块原理

简单解析 IvorySQL 增强 Oracle xml 兼容能力的原理

简单讨论 PostgreSQL C语言拓展函数返回数据表的方式

  简单分析 pg_config 程序的作用与原理
  Redis 日志机制简介（一）：SlowLog
  Redis 日志机制简介（二）：AOF 日志
  Redis 日志机制简介（三）：RDB 日志
  pg_cron插件使用介绍
  Redis 的指令表实现机制简介
  pg几款源码工具介绍
  Redis 事务功能简介

MOP顾问说

MOP顾问说：MOP 三种主流数据库常用 SQL(一)

MOP顾问说：服务器内存

MOP 顾问说：Linux Nice 值与 CPU 优先级揭秘

小倩 sql数据库 sql 数据库结构数据库

文章转载自青年数据库学习互助会，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。