当前位置：首页 > news >正文

PL/SQL入门到实践

news 文章来源：https://blog.csdn.net/qq_44286009/article/details/140063168 2024/7/6 18:28:20

一、什么是PL/SQL

PL/SQL是Procedural Language/Structured Query Language的缩写。
PL/SQL是一种过程化编程语言，运行于服务器端的编程语言。
PL/SQL是对SQL语言的扩展。PL/SQL结合了SQL语句和过程性编程语言的特性，可以用于编写存储过程、触发器、函数等数据库对象。它现在是Oracle数据库系统的核心语言，Oracle的许多部件都是由PL/SQL编写的。

二、PL/SQL特点

扩展性：PL/SQL是Oracle对SQL的扩展，增加了过程处理语句（控制结构），使SQL的功能更加强大。
过程化：PL/SQL不仅支持SQL的数据查询、数据操纵和数据定义功能，还具有强大的过程化功能。
块结构（模块化）：PL/SQL是一种块结构的语言，组成PL/SQL程序的单元是逻辑块，每个块都可以划分为三个部分：声明部分、执行部分和异常处理部分。
可重用性：PL/SQL代码可以定义存储过程和函数，在客户端需要的时候可以调用，实现了模块的可重用性。
可移植性：PL/SQL代码可以使用任何ASCII文本编辑器编写，对任何Oracle能够运行的操作系统都非常便利。
改善性能（降低网络拥挤）：PL/SQL是以整个语句块发给服务器，降低了网络拥挤，从而提高性能。因为SQL语句是以语句为单位进行发送的，在网络环境下会占用大量的服务器时间，可能导致网络拥挤。

三、PL/SQL的结构

PL/SQL 块概念

块（block）是 PL/SQL的基本程序单元，编写pl/sql程序实际上就是编写pl/sql块，要完成相对简单的应用功能，可能只需要编写一个pl/sql块，要完实现复杂的功能，可能需要再一个pl/sql块中嵌套其他的pl/sql块。

一个 PL/SQL 块通常由三个部分组成：声明部分、执行部分和异常处理部分。

PL/SQL 块的基本结构

DECLARE  -- 声明变量、常量、类型、游标等  
BEGIN  -- 执行 SQL 语句和 PL/SQL 语句  
EXCEPTION  -- 处理异常  
END;

DECLARE 部分（可选）

在这一部分，你可以声明 PL/SQL 变量、常量、类型、游标、子程序等。这些声明的对象在 PL/SQL 块的其余部分（BEGIN...END 之间）是可见的。

示例：

DECLARE  v_name VARCHAR2(50);  
BEGIN  -- 使用变量...  
END;

BEGIN 部分（必需）

这是 PL/SQL 块的主要部分，包含要执行的 SQL 语句和 PL/SQL 语句。你可以在这里对前面声明的变量进行赋值、执行查询、控制流语句（如 IF、LOOP、CASE 等）等。

DECLARE  v_name VARCHAR2(50);  
BEGIN  v_name := 'John Doe';  DBMS_OUTPUT.PUT_LINE('Hello, ' || v_name);  
END;

EXCEPTION 部分（可选）

如果在 BEGIN...END 部分的代码执行过程中出现了异常（如除零错误、无效的 SQL 语句等），那么代码会跳转到 EXCEPTION 部分进行处理。在 EXCEPTION 部分，你可以捕获异常并编写相应的处理代码。

DECLARE  v_divisor NUMBER;  
BEGIN  v_divisor := 0;  DBMS_OUTPUT.PUT_LINE(10 / v_divisor); -- 这会引发一个异常  
EXCEPTION  WHEN ZERO_DIVIDE THEN  DBMS_OUTPUT.PUT_LINE('除数不能为零！');  
END;

PL/SQL 块类型

匿名块：没有名称的 PL/SQL 块，通常在 SQL*Plus 或其他工具中直接执行。
子程序：包括存储过程和函数，它们有名称，可以在 PL/SQL 程序中调用。
触发器：与数据库表事件关联的 PL/SQL 块，当满足特定条件时自动执行。
包：将相关的 PL/SQL 类型、变量、常量、子程序等组合到一起的数据库对象。

四、PL/SQL中的标识符

标识符定义规范

pl/sql程序设计中的标识符定义与sql的标识符定义的要求相同。

标识符名不能超过30字符
第一个字符必须为字母
不分大小写
不能是sql保留字

变量推荐命名方法

五、PL/SQL块的数据类型

基本数据类型

基本数据类型使用

-- 声明变量
DECLAREv_name varchar2(100);v_id varchar2(100);v_number NUMBER :=1;--给变量赋值
BEGIN --主体部分SELECT name,loginid INTO v_name,v_id FROM TAUSER WHERE userid = '1';dbms_output.put_line(v_name||','||v_id||','||v_number);
END;

参照表的列类型

使用方式：表名.字段名%type

-- 声明变量
DECLAREv_name tauser.name%type;v_id tauser.loginid%type;v_number NUMBER :=1;--给变量赋值
BEGIN --主体部分SELECT name,loginid INTO v_name,v_id FROM TAUSER WHERE userid = '1';dbms_output.put_line(v_name||','||v_id||','||v_number);
END;

记录类型

格式：

type 类型名 is record(变量名1,变量名2,
......);

-- 声明变量
DECLARETYPE user_record IS RECORD (v_name tauser.name%TYPE,v_id tauser.loginid%type);u user_record;
BEGIN --主体部分SELECT name,loginid INTO u FROM TAUSER WHERE userid = '1';dbms_output.put_line(u.v_name||','||u.v_id);
END;

参照记录类型

格式：表名%rowtype

-- 声明变量
DECLAREu tauser%rowtype;
BEGIN --主体部分SELECT * INTO u FROM TAUSER WHERE userid = '1';dbms_output.put_line(u.name||','||u.loginid);
END;

注意：

在PL/SQL中，%ROWTYPE属性定义了一个记录类型，该记录类型的结构与指定的表或视图的整行结构相匹配。因此，如果你使用%ROWTYPE，那么该记录类型将包含表或视图中的所有列。

以下为错误用法：

六、流程控制语句

if语句

语法格式：

IF condition THEN  -- 如果condition为真，则执行这里的代码  [statements]  
[ELSIF condition THEN  -- 如果需要额外的条件检查，可以使用ELSIF（可选）  -- 如果前面的条件都为假，但此条件为真，则执行这里的代码  [elsif_statements]  
...] 
ELSE  -- 如果condition为假，并且提供了ELSE部分，则执行这里的代码  [else_statements]  END IF;

示例：

DECLARE  v_number NUMBER := 10;  
BEGIN  IF v_number > 0 THEN  DBMS_OUTPUT.PUT_LINE('The number is positive.');  ELSIF v_number = 0 THEN  DBMS_OUTPUT.PUT_LINE('The number is zero.');  ELSE  DBMS_OUTPUT.PUT_LINE('The number is negative.');  END IF;  
END;

循环语句

在PL/SQL中，有几种不同类型的循环可以使用，包括FOR循环、WHILE循环和LOOP（无限循环，通常与EXIT WHEN条件一起使用）。以下是这些循环的使用方式的示例：

FOR 循环

FOR循环通常用于遍历一个集合，如数组或游标。对于数字范围，可以使用FOR ... IN ... LOOP结构。

BEGIN  FOR i IN 1..10 LOOP  DBMS_OUTPUT.PUT_LINE('Value of i: ' || i);  END LOOP;  
END;

在PL/SQL中，1..10 是一个范围（range）的表示法，它定义了一个从1开始到10结束（包括1和10）的整数序列。这种表示法经常用于FOR循环中，以便迭代一个指定的数字范围

WHILE 循环

WHILE循环在条件为真时重复执行代码块。

DECLARE  v_counter NUMBER := 1;  
BEGIN  WHILE v_counter <= 10 LOOP  DBMS_OUTPUT.PUT_LINE('Value of v_counter: ' || v_counter);  v_counter := v_counter + 1;  END LOOP;  
END;

LOOP 循环（与 EXIT WHEN 一起使用）

LOOP语句创建了一个无限循环，直到遇到EXIT WHEN条件。

DECLARE  v_counter NUMBER := 1;  
BEGIN  LOOP  DBMS_OUTPUT.PUT_LINE('Value of v_counter: ' || v_counter);  v_counter := v_counter + 1;  EXIT WHEN v_counter > 10;  END LOOP;  
END;  
/

在PL/SQL中，EXIT语句用于立即终止当前循环（LOOP、FOR或WHILE循环）的执行，并将控制权转移到循环之后的代码。当与WHEN条件结合使用时（即EXIT WHEN），它允许在特定条件满足时退出循环。

CURSOR FOR LOOP

当使用游标时，可以使用FOR ... IN ... LOOP结构来遍历游标的结果集。

DECLARE  CURSOR c_employees IS  SELECT employee_id, first_name, last_name FROM employees;  
BEGIN  FOR r_employee IN c_employees LOOP  DBMS_OUTPUT.PUT_LINE('Employee ID: ' || r_employee.employee_id ||   ', Name: ' || r_employee.first_name || ' ' || r_employee.last_name);  END LOOP;  
END;  
/

在这个示例中，我们定义了一个名为c_employees的游标来从employees表中检索数据，并使用FOR ... IN ... LOOP结构来遍历游标的结果集。在循环体中，我们可以访问游标中每一行的列值。

七、游标

游标的概念

游标是指向查询结果的指针，指针指向哪条记录，提取的就是哪条记录的数据。

游标的使用流程

声明游标：定义游标的名称、声明光标变量以及定义光标属性等。
打开游标：执行查询并将结果存储在游标变量中。
读取游标：通过FETCH语句从游标中提取数据，并逐行处理。
关闭游标：使用CLOSE语句关闭游标，释放资源。

游标的属性

游标的属性在PL/SQL中提供了关于游标当前状态和数据检索的详细信息。无论是显式游标还是隐式游标，它们都具有一些共同的属性。以下是游标的属性及其解释：

%FOUND：
- 类型：布尔型（Boolean）
- 描述：此属性返回一个布尔类型的值，用于指示游标的指针是否指向有效的数据行。如果游标指向的数据不为空（即存在有效数据），则返回TRUE；否则返回FALSE。
%NOTFOUND：
- 类型：布尔型（Boolean）
- 描述：与%FOUND相反，此属性在游标指向的数据为空（即没有有效数据）时返回TRUE。当游标已经遍历完所有数据或尚未开始检索数据时，此属性通常也返回TRUE。
%ROWCOUNT：
- 类型：数值型（Numeric）
- 描述：此属性返回一个数值，表示游标指向的缓冲区（或结果集）中的数据条数。它通常用于跟踪游标已经检索了多少行数据，或者在执行DML操作后确定受影响的行数。
%ISOPEN：
- 类型：布尔型（Boolean）
- 描述：此属性返回一个布尔类型的值，用于判断当前游标是否打开。如果游标处于打开状态，则返回TRUE；否则返回FALSE。请注意，隐式游标的%ISOPEN属性始终为FALSE，因为隐式游标由Oracle数据库自动管理。

这些属性可以帮助开发人员编写更健壮和可维护的PL/SQL代码，因为它们提供了关于游标状态和数据检索的详细信息。例如，开发人员可以使用%FOUND和%NOTFOUND属性来控制循环的迭代，或者使用%ROWCOUNT属性来验证DML操作的结果。

需要注意的是，不同的游标类型（如静态游标、动态游标、隐式游标等）可能在某些方面有所不同，但它们通常都支持上述属性。此外，在使用游标时，还需要注意游标的打开、读取和关闭等操作，以确保正确地处理数据并释放资源。

游标的使用

基本使用

DECLARE  v_name tauser.name%TYPE;v_id tauser.loginid%TYPE;CURSOR tauser_cursor IS SELECT name,loginid FROM TAUSER t ;
BEGIN  OPEN tauser_cursor;FETCH tauser_cursor INTO v_name,v_id;WHILE tauser_cursor%FOUND LOOPdbms_output.put_line(v_name||','||v_id);FETCH tauser_cursor INTO v_name,v_id; --让游标指向下一行end LOOP;
END;

注意：

错误用法：

参数化游标

DECLARE  v_name tauser.name%TYPE;v_id tauser.loginid%TYPE;CURSOR tauser_cursor(u_id varchar2) IS SELECT name FROM TAUSER t WHERE userid = u_id ;
BEGIN  OPEN tauser_cursor('1');  -- 传入实际参数 这里传入userid为1FETCH tauser_cursor INTO v_name;WHILE tauser_cursor%FOUND LOOPdbms_output.put_line(v_name);FETCH tauser_cursor INTO v_name;end LOOP;
END;

游标For循环

DECLARE  CURSOR tauser_cursor IS SELECT name,loginid FROM TAUSER t  ;
BEGIN  FOR u IN tauser_cursor LOOPdbms_output.put_line(u.name);end LOOP;END;

带参数的游标For循环

DECLARE  CURSOR tauser_cursor(u_id varchar2) IS SELECT name,loginid FROM TAUSER t WHERE userid=u_id ;
BEGIN  FOR u IN tauser_cursor('1') LOOPdbms_output.put_line(u.name);end LOOP;END;

隐式游标

在 PL/SQL 中，隐式游标（Implicit Cursor）是 Oracle 数据库自动为你管理的一种游标，主要用于处理那些不需要显式声明和控制的 SQL 语句。最常见的隐式游标是当你执行 DML（数据操纵语言）语句（如 INSERT、UPDATE、DELETE）和单行 SELECT INTO 语句时，Oracle 会自动为你创建一个隐式游标。

隐式游标的主要特点是：

自动管理：你不需要显式地声明、打开、获取数据和关闭隐式游标。Oracle 数据库会自动为你完成这些操作。
属性：隐式游标有一些属性，如 %NOTFOUND、%FOUND、%ROWCOUNT 和 %ISOPEN。但由于隐式游标是自动管理的，%ISOPEN 总是返回 FALSE，因为它在查询完成后会自动关闭。

示例：

假设你有一个名为 employees 的表，并且你想删除某个员工（假设其 ID 为 1001）：

BEGIN  DELETE FROM employees WHERE employee_id = 1001;  IF SQL%NOTFOUND THEN  DBMS_OUTPUT.PUT_LINE('没有找到员工 ID 1001');  ELSE  DBMS_OUTPUT.PUT_LINE('员工 ID 1001 已被删除');  END IF;  
END;

在上面的示例中，SQL%NOTFOUND 是一个隐式游标的属性，用于检查 DELETE 语句是否影响了任何行。如果没有找到员工 ID 1001，则输出“没有找到员工 ID 1001”；否则输出“员工 ID 1001 已被删除”。注意，这里我们使用了 SQL%NOTFOUND 而不是 employees_cursor%NOTFOUND，因为这是一个隐式游标操作，没有显式声明的游标。

八、异常

在PL/SQL中，异常（Exception）是用于处理运行时错误的一种机制。当PL/SQL块中的代码遇到错误时，它会抛出一个异常。如果没有适当的异常处理代码，程序将终止并返回一个错误消息给用户。但是，通过编写异常处理部分（EXCEPTION section），你可以捕获异常并采取适当的措施，如记录错误信息、回滚事务或提供友好的错误消息给用户。

在PL/SQL中，有两种类型的异常：

预定义异常：Oracle已经为常见的错误情况定义了异常。例如，NO_DATA_FOUND（当SELECT INTO语句没有返回任何行时）和TOO_MANY_ROWS（当SELECT INTO语句返回多行时）。预定义异常不需要显式声明，但你可以在EXCEPTION部分中捕获它们。
用户定义异常：你可以使用DECLARE部分中的EXCEPTION关键字来声明你自己的异常。这允许你定义和处理特定的错误情况。

示例：使用预定义异常

DECLARE  v_employee_name VARCHAR2(50);  
BEGIN  SELECT first_name INTO v_employee_name  FROM employees  WHERE employee_id = 1001;  -- 如果这里employee_id为1001的员工不存在，将会抛出NO_DATA_FOUND异常  DBMS_OUTPUT.PUT_LINE('Employee name: ' || v_employee_name);  
EXCEPTION  WHEN NO_DATA_FOUND THEN  DBMS_OUTPUT.PUT_LINE('No employee found with ID 1001');  WHEN OTHERS THEN  DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);  
END;

示例：使用用户定义异常

DECLARE  v_employee_salary NUMBER;  salary_too_high EXCEPTION; -- 声明用户定义异常  
BEGIN  SELECT salary INTO v_employee_salary  FROM employees  WHERE employee_id = 1001;  IF v_employee_salary > 100000 THEN  RAISE salary_too_high; -- 触发用户定义异常  END IF;  -- 其他代码...  
EXCEPTION  WHEN salary_too_high THEN  DBMS_OUTPUT.PUT_LINE('Salary is too high for this employee');  WHEN OTHERS THEN  DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);  
END;

九、函数和过程

函数和过程介绍

1.Oracle提供可以把PL/SQL程序存储在数据库中，并可以再任何地方来运行它，这样就叫存储过程或函数。

2.过程和函数统称PL/SQL子程序，它们是被命名的PL/SQL块，均存储在数据库中，并通过输入、输出参数或者输入/输出参数预期调用者交换信息。

3.过程和函数的唯一区别是函数总向调用者返回数据（函数必须有返回值），而过程则不返回数据（过程没有返回值）。

函数的创建

创建语法：

CREATE OR REPLACE FUNCTION function_name (  parameter1 datatype [DEFAULT value],  parameter2 datatype [DEFAULT value],  ...  
) RETURN return_datatype  
IS  -- 声明变量  variable_declaration;  
BEGIN  -- 函数体  -- 执行逻辑，返回结果  RETURN result;  
EXCEPTION  -- 异常处理  WHEN exception_name THEN  -- 处理异常的代码  ...  
END;

其中：

function_name：函数的名称。
parameter1, parameter2, ...：函数的参数列表。每个参数都有一个名称和数据类型，可以有一个默认值。
return_datatype：函数返回值的数据类型。
variable_declaration：在 IS 和 BEGIN 之间，你可以声明函数体内将使用的变量。
RETURN result;：函数体中的这一行表示函数将返回的值。
EXCEPTION 部分是可选的，用于处理在函数执行过程中可能发生的异常。

函数的参数类型

在 PL/SQL 中，函数（Function）允许你封装一段逻辑代码，并可以传递参数和返回结果。PL/SQL 是 Oracle 数据库中的过程化 SQL 语言扩展，它允许你在数据库中创建存储过程、函数、触发器等。

函数的参数可以有不同的模式（Mode）和数据类型。在 PL/SQL 中，函数的参数主要有以下几种模式：

IN 模式：这是默认的参数模式，用于向函数传递值。在函数体内，这些参数是只读的，不能被修改。
OUT 模式：这种参数用于从函数返回多个值。在调用函数之前，OUT 参数不需要被赋值。在函数体内，OUT 参数可以被赋予新的值，并在函数执行完毕后返回给调用者。
IN OUT 模式：这种参数既可以接收传入的值，也可以返回修改后的值。

下面是一个简单的 PL/SQL 函数示例，该函数接受一个 IN 模式的 NUMBER 类型参数，并返回它的平方：

CREATE OR REPLACE FUNCTION square_number(p_number IN NUMBER)  
RETURN NUMBER IS  v_result NUMBER;  
BEGIN  v_result := p_number * p_number;  RETURN v_result;  
END square_number;

注意：无论是返回值类型还是参数类型，都只需要写数据类型，不需要写括号。

例如：varchar2,number是正确的，varchar2(50),number(7,2)这种写法是错误的。

函数的调用

一旦你创建了函数，你就可以在 SQL 语句或 PL/SQL 块中调用它。

示例：

SELECT square_number(5) FROM DUAL;

过程的创建

在PL/SQL中，存储过程（Stored Procedure）是存储在数据库中的一组为了完成特定功能的SQL语句集。存储过程可以被视为一个命名的PL/SQL块，它可以从应用程序中被调用，并且可以接受参数和返回状态值。

以下是PL/SQL中存储过程的基本创建语法：

CREATE OR REPLACE PROCEDURE procedure_name   [ (parameter_name [ IN | OUT | IN OUT ] data_type [, ...] ) ]   
IS   [declaration_section]   
BEGIN   executable_section   
[EXCEPTION   exception_handling_section]   
END [procedure_name];

CREATE OR REPLACE PROCEDURE: 用于创建或替换一个已存在的存储过程。
procedure_name: 存储过程的名称。
parameter_name: 参数的名称。
IN | OUT | IN OUT: 参数的模式。IN是默认值，表示参数是输入参数；OUT表示参数是输出参数，用于从存储过程返回数据；IN OUT表示参数既可以输入也可以输出。
data_type: 参数的数据类型。
declaration_section: 可选部分，用于声明变量、常量、游标等。
executable_section: 存储过程的主体部分，包含要执行的PL/SQL语句和逻辑。
exception_handling_section: 可选部分，用于处理在executable_section中可能发生的异常。

下面是一个简单的存储过程示例，它接受一个输入参数并插入到一张表中：

CREATE OR REPLACE PROCEDURE insert_into_employees (p_emp_id IN NUMBER, p_emp_name IN VARCHAR2)   
IS   
BEGIN   INSERT INTO employees (emp_id, emp_name) VALUES (p_emp_id, p_emp_name);   COMMIT;   
EXCEPTION   WHEN OTHERS THEN   ROLLBACK;   RAISE_APPLICATION_ERROR(-20001, 'An error occurred: ' || SQLERRM);   
END insert_into_employees;

在这个示例中，存储过程insert_into_employees接受两个输入参数p_emp_id和p_emp_name，并将它们插入到employees表中。如果在插入过程中发生任何错误，它会回滚事务并抛出一个自定义的错误。

过程的调用

要调用存储过程，你可以使用EXECUTE语句或者匿名PL/SQL块：

EXECUTE insert_into_employees(100, 'John Doe');

BEGIN  insert_into_employees(100, 'John Doe');  
END;

函数和存储过程示例

从Oracle自定义函数和存储过程案例学习PL/SQL的使用-CSDN博客

十、包

在 PL/SQL 中，包（Package）是一个数据库对象，它允许你将相关的 PL/SQL 类型、变量、常量、子程序、游标等组合成一个逻辑单元。包的主要目的是封装和组织代码，提高代码的可读性、可维护性和重用性。

一个包由两部分组成：包头（Package Specification）和包体（Package Body）。

包头（Package Specification）

定义了包中公共类型、变量、常量、子程序、游标等的接口。
其他数据库对象或用户只能看到和引用包头中定义的这些公共元素。
包头中的声明决定了哪些元素是公共的（可以在包外部访问），哪些是私有的（只能在包内部访问）。

包体（Package Body）

包含了包头中声明的公共元素和私有元素的实现。
私有元素在包体外部是不可见的，但它们可以在包体内的任何子程序中引用。
包体可以包含类型、变量、常量、子程序、游标等的定义和实现。

使用包的好处

封装性：包可以将相关的代码和数据封装在一起，隐藏实现细节，只暴露必要的接口。
重用性：通过封装公共的类型、子程序等，包提高了代码的重用性。
性能优化：由于包中的代码和数据在内存中只存储一次，因此可以提高访问速度和执行效率。
易于维护：通过组织和封装代码，包使得代码更易于阅读、理解和维护。
安全性：通过控制对包中元素的访问权限，可以提高数据的安全性。

示例

以下是一个简单的 PL/SQL 包示例：

包头（my_package_spec.pks）：

CREATE OR REPLACE PACKAGE my_package AS  -- 公共类型  TYPE my_type IS TABLE OF VARCHAR2(50);  -- 公共常量  CONSTANT my_constant NUMBER := 100;  -- 公共子程序  FUNCTION get_data RETURN my_type;  PROCEDURE process_data(p_data IN my_type);  
END my_package;

包体（my_package_body.pkb）

CREATE OR REPLACE PACKAGE BODY my_package AS  -- 私有变量  PRIVATE my_private_var NUMBER := 0;  -- 公共子程序实现  FUNCTION get_data RETURN my_type IS  v_data my_type := my_type('Data1', 'Data2', 'Data3');  BEGIN  RETURN v_data;  END get_data;  PROCEDURE process_data(p_data IN my_type) IS  BEGIN  -- 处理数据的逻辑  FOR i IN 1..p_data.COUNT LOOP  DBMS_OUTPUT.PUT_LINE(p_data(i));  END LOOP;  END process_data;  
END my_package;

在这个示例中，我们创建了一个名为 my_package 的包，它包含一个公共类型 my_type、一个公共常量 my_constant、一个公共函数 get_data 和一个公共过程 process_data。包体还包含一个私有变量 my_private_var。