SQL语言中PL/SQL的实例教程

尼耳多
263 阅读9分钟

PL/SQL是Oracle对SQL语言和Oracle关系数据库的程序性扩展。PL/SQL是 "结构化查询语言的程序性语言扩展 "的缩写。

对PL/SQL的需求是什么？嗯，如果你看一下SQL语言，它只是一种查询语言。SQL不能做像Python或Java这样的编程语言能做的事情。为了在SQL的基础上增加更多的功能，Oracle开发了一种叫做PL/SQL的语言，它通过增加程序性语言中的结构，如函数、循环、变量、对象等来扩展SQL。因此，PL/SQL成为一种结构性语言，比SQL更强大。

在这篇文章中，让我们了解一下PL/SQL的基本原理以及它是如何工作的。我将分享一些示例代码，以便你能实际地理解这些概念。让我们直接进入。

Oracle Live SQL

你不需要安装任何东西来跟随本教程。你可以使用Oracle提供的在线编辑器，也就是Oracle Live SQL。如果你还没有一个Oracle账户，请注册一个新的账户。你可以打开一个SQL工作表并开始编码。

PL/SQL的基本语法

PL/SQL程序的基本语法看起来像这样。

DECLARE 
   <declaration section> 
BEGIN 
   <executable command(s)>
EXCEPTION 
   <exception handling> 
END;

程序以DECLARE 部分开始，这是可选的。在这一节中，你可以声明你的变量、常量、作为游标的记录等，这些都是临时存储的数据。

在声明部分之后，执行部分以BEGIN 关键字开始。你可以在这部分编写程序逻辑。本节将以END 关键字结束。这是一个强制性的部分，所有的动作都在这里发生。

你也可以用EXCEPTION 部分来处理异常情况。这是一个可选的部分。

PL/SQL中的Hello World程序

让我们看看如何在PL/SQL中编写你的第一个程序。如果你想在PL/SQL中打印一些东西，你可以使用*dbms_output.put_line()*并在括号中传递你想打印的数据。

下面是一个PL/SQL程序，它将打印 "Hello World "作为输出。


BEGIN 
   dbms_output.put_line('Hello World'); 
END;

你可以在你的SQL工作表中编写代码，选择你要执行的代码，然后点击 "运行 "按钮。你将能够在你的代码下面的窗口中看到输出。

输出。

PL/SQL Hello World Program

PL/SQL中的变量

在PL/SQL中，你必须在使用变量之前声明你的变量。你可以在声明部分声明和初始化你的变量，在可执行部分为变量分配新的值。

这就是你在PL/SQL中声明变量的方式。

variable_name datatype;

如果你想初始化这个变量，你可以通过在声明的同时赋值来实现。

variable_name datatype := value;

注意，在PL/SQL中我们使用**:=作为赋值操作符**。

你还需要提到变量的数据类型（数字、整数、十进制、浮点数、实数、char、varchar2、布尔值等）。

让我们看一个PL/SQL程序的例子来了解变量是如何被创建和使用的。下面的PL/SQL程序将找到两个数字的总和。

DECLARE 
   a integer := 10; 
   b integer := 20; 
   sumOfNumbers integer; 
BEGIN 
   sumOfNumbers := a + b; 
   dbms_output.put_line(sumOfNumbers);
END;

输出。

PL/SQL Variables

PL/SQL中的字符串串联

如果你想在显示输出的同时添加一个文本，你可以在**||**操作符的帮助下完成。这个操作符用于PL/SQL中的字符串连接。

请看下面的例子。

DECLARE 
   a integer := 10; 
   b integer := 20; 
   sumOfNumbers integer; 
BEGIN 
   sumOfNumbers := a + b; 
   dbms_output.put_line('Value of c: ' || sumOfNumbers); 
END;

输出。

PL/SQL String Concatenation

PL/SQL中的注释

注释是用来使代码更容易被人类理解的。在执行代码的时候，系统会忽略注释。你可以在你的代码中写任何东西作为注释。

在PL/SQL中，我们用"-"来写单行注释，用/* */来写多行注释。

-- this is a single line comment

/*  this is a 
    multi-line
    comment   */

PL/SQL Comments

在PL/SQL中使用表的数据

让我们看看如何连接到一个表并在PL/SQL中使用该表的数据。要做到这一点，让我们先创建一个表。

CREATE TABLE EMPLOYEES( 
   ID   INT NOT NULL, 
   NAME VARCHAR(10) NOT NULL, 
   AGE INT NOT NULL, 
   ADDRESS VARCHAR (25), 
   SALARY   DECIMAL (18, 2),        
   PRIMARY KEY (ID) 
);

输出。

PL/SQL creating a table

现在，让我们在这个表中插入一些值。

INSERT INTO EMPLOYEES (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (1, 'Anupam', 32, 'Mumbai', 20000.00 );  

INSERT INTO EMPLOYEES (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (2, 'Aman', 25, 'Delhi', 15000.00 );  

INSERT INTO EMPLOYEES (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (3, 'Piyush', 23, 'Bangalore', 30000.00 );

输出。

PL/SQL inserting into a table

现在，这个表看起来像这样。

PL/SQL selecting data from a table

让我们看看如何将这些数据引入我们的PL/SQL代码。

你可以使用**%type**来声明一个与指定表的列的类型相同的变量。

让我们看一个例子。

DECLARE 
   e_id employees.id%type := 1; 
   e_name  employees.name%type; 
   e_addr employees.address%type; 
   e_sal  employees.salary%type; 
BEGIN 
   SELECT name, address, salary INTO e_name, e_addr, e_sal 
   FROM employees 
   WHERE id = e_id;  
   dbms_output.put_line 
   ('Employee ' ||e_name || ' from ' || e_addr || ' earns ' || e_sal); 
END;

输出。

PL/SQL using data from a table

正如你在上面的代码中所看到的，你可以从表中选择数据并将其放入PL/SQL变量中。

PL/SQL中的IF-ELSIF-ELSE语句

你可以使用PL/SQL中的IF和ELSE 语句来告诉计算机，如果条件为真，就这样做。否则，如果条件为假，就做另一件事。你可以在ELSIF 语句的帮助下检查多个条件。

让我们看一个在PL/SQL中使用这些条件语句的例子。

DECLARE 
   a number(2) := 21; 
   b number(2) := 10; 
BEGIN 
   IF (a = b) then 
      dbms_output.put_line('a is equal to b'); 
   ELSIF (a < b) then 
      dbms_output.put_line('a is less than b'); 
   ELSE 
      dbms_output.put_line('a is greater than b'); 
   END IF; 
END;

输出。

PL/SQL if else statements

如果你想检查多个条件，你可以添加多个ELSIF语句。IF块应该以*END IF;*语句结束。

PL/SQL中的循环

在编程中，我们使用循环来执行一连串的指令，不断重复直到达到某个条件。在PL/SQL中，我们有不同类型的循环，如基本的PL/SQL循环、while循环和for循环。

假设我们想打印从1到5的数字，与其写5次dbms_output.put_line()，我们可以运行一个循环来轻松实现它。让我们看看如何使用PL/SQL中不同的循环来实现它。

PL/SQL中的基本循环

declare
    i number;
begin
    i:=1;
    loop
        dbms_output.put_line(i);
        i:=i+1;
        exit when i>5;
    end loop;
end;

输出。

PL/SQL basic loop

循环将以LOOP 语句开始，以 END LOOP; 语句结束。在这个例子中，每次循环运行时，我们将变量i的值递增1。

你可以在END LOOP语句前添加一个退出条件。如果这个退出条件为真，那么该循环将停止执行。

PL/SQL中的While循环

DECLARE 
   i number; 
BEGIN
    i:=1;
    WHILE i <= 5 LOOP 
      dbms_output.put_line(i);
      i:=i+1; 
   END LOOP; 
END;

输出。

PL/SQL while loop

while循环以关键字WHILE、一个条件和关键字LOOP开始。循环将以*END LOOP;*语句结束。只要条件为真，while循环就会执行。

PL/SQL中的For循环

DECLARE 
   i number;  
BEGIN 
   FOR i in 1 .. 5 LOOP 
      dbms_output.put_line(i); 
  END LOOP; 
END;

输出。

PL/SQL for loop

for循环以FOR 关键字、变量名、IN 关键字、数字范围和LOOP 关键字开始。在for循环中，你不需要手动增加变量。它将在每次迭代后自动递增。循环将以*END LOOP;*语句结束。

PL/SQL中的子程序

PL/SQL中的子程序（也叫PL/SQL块）是一个程序单元/模块，当它被调用时，会执行一个特定的任务。

PL/SQL提供了两种类型的子程序。

函数函数返回一个单一的值。这些主要用于计算和返回一个值。
程序程序不直接返回一个值。这些主要是用来执行一个动作。

PL/SQL中的过程

让我们看看PL/SQL中过程的一个例子。

CREATE OR REPLACE PROCEDURE greetings 
AS 
BEGIN 
   dbms_output.put_line('Hello World!'); 
END;

输出。

PL/SQL procedure

我们已经创建了一个简单的过程，当它被调用时将打印 "Hello World"。

现在让我们来看看如何调用一个过程。你可以使用EXECUTE 命令来调用过程。

EXECUTE greetings;

输出。

PL/SQL executing a procedure

你也可以用以下方式调用它。

BEGIN 
   greetings; 
END;

输出。

PL/SQL executing a procedure 2

IN和OUT模式

程序可以使用IN模式接受数值，使用OUT 模式输出数值。

让我们来看看一个PL/SQL程序的例子，它使用过程找到两个数字的最大值。

CREATE OR REPLACE PROCEDURE findMax(x IN number, y IN number, z OUT number) IS
BEGIN
    IF x>y THEN
        z:=x;
    ELSE
        z:=y;
    END IF;
END;

PL/SQL procedure in out mode

我们已经创建了一个过程，它接受两个数字并输出这两个数字的最大值。现在，让我们通过传递参数来调用该过程。

DECLARE
    a number;
    b number;
    c number;
BEGIN
    a := 10;
    b := 5;
    findMax(a,b,c);
    dbms_output.put_line(c);
END;

PL/SQL procedure in out mode example

PL/SQL中的函数

一个函数和一个过程是一样的，只是它返回一个值。

让我们创建一个函数的例子，返回我们之前创建的雇员 表中的记录数。

CREATE OR REPLACE FUNCTION totalEmployees RETURN number IS 
   total number(2) := 0; 
BEGIN 
   SELECT count(*) into total FROM employees; 
   RETURN total; 
END;

PL/SQL functions

我们已经创建了一个函数，用来返回employees 表中的行数。当你定义这个函数时，确保你在函数声明中提供了返回类型（这里，返回类型是数字）。

让我们来调用这个函数。

DECLARE 
   total number(2); 
BEGIN 
   total := totalEmployees(); 
   dbms_output.put_line('Total no. of Employees: ' || total); 
END;

PL/SQL functions example

PL/SQL中的游标

当我们使用变量时，我们一般不能存储由SQL查询返回的多行或多条记录。这就是为什么我们在PL/SQL中有游标。

游标可以保存由SQL语句返回的多条记录。

一般来说，有两种类型的游标。隐式和显式。

隐式游标

隐式游标是由Oracle在执行SQL查询时自动生成的。你可以把最近的隐式游标称为SQL游标，它总是有一些属性。下面给出了SQL游标的一些属性和使用情况。

%FOUND如果SELECT INTO语句返回一条或多条记录，或者INSERT, UPDATE, 或DELETE语句影响一条或多条记录，返回TRUE。否则，它将返回FALSE。
%NOTFOUND如果SELECT INTO语句没有返回任何记录，或者INSERT, UPDATE, 或者DELETE语句没有影响任何记录，那么返回TRUE。否则，它将返回FALSE。
%ROWCOUNT返回由SELECT语句返回的或由INSERT, UPDATE, 或DELETE语句影响的行的数量。

下面是一个在PL/SQL中使用隐式游标的例子。

DECLARE  
   total_rows number(2); 
BEGIN 
   UPDATE employees SET salary = salary + 1000; 
   IF sql%notfound THEN 
      dbms_output.put_line('No employees selected'); 
   ELSIF sql%found THEN 
      total_rows := sql%rowcount;
      dbms_output.put_line( total_rows || ' employees selected '); 
   END IF;  
END;

输出。

PL/SQL implicit cursors

显式游标

显式游标是由用户或程序员创建的。你应该在PL/SQL程序的声明部分声明显式游标。

一般来说，你用显式游标做4件事情。声明，打开，获取，和关闭。

下面给出了一个在PL/SQL中创建显式游标的例子。

DECLARE
    e_id employees.id%type;
    e_name employees.name%type;
    CURSOR C1 IS select id,name from employees;
BEGIN
    OPEN C1;
    LOOP
        FETCH C1 into e_id, e_name;
        EXIT WHEN C1%notfound; 
        dbms_output.put_line(e_id ||' '|| e_name); 
    END LOOP;
    CLOSE C1;
END;

输出。

PL/SQL explicit cursors

你可以看到游标C1存储了由SQL查询返回的多条记录，我们用一个循环来打印它们。

PL/SQL中的触发器

在PL/SQL中，触发器 是当某些事件发生时自动触发/执行的存储程序。这个事件可以是一个DML，DDL，或者任何DB操作。

下面给出了一个基于UPDATE操作的触发器的例子。如果雇员 表中的任何记录被更新，这个触发器将自动被触发。

CREATE OR REPLACE TRIGGER displayChanges BEFORE UPDATE ON employees
FOR EACH ROW WHEN (NEW.ID > 0)
BEGIN
    dbms_output.put_line('Old Salary: ' || :OLD.salary);
    dbms_output.put_line('New Salary: ' || :NEW.salary);
END;

PL/SQL triggers

现在，让我们在employees 表中更新一个雇员的工资。

UPDATE employees set salary=80000 where name='Anupam';

PL/SQL triggers example

你可以看到，当UPDATE操作完成后，触发器被触发。新旧工资显示在输出中。

avatar
文章
阅读
粉丝