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中的变量
在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中的字符串串联
如果你想在显示输出的同时添加一个文本,你可以在**||**操作符的帮助下完成。这个操作符用于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中的注释
注释是用来使代码更容易被人类理解的。在执行代码的时候,系统会忽略注释。你可以在你的代码中写任何东西作为注释。
在PL/SQL中,我们用"-"来写单行注释,用/* */来写多行注释。
-- this is a single line comment
/* this is a
multi-line
comment */
在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)
);
输出。
现在,让我们在这个表中插入一些值。
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代码。
你可以使用**%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变量中。
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;
输出。
如果你想检查多个条件,你可以添加多个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;
输出。
循环将以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;
输出。
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;
输出。
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;
输出。
我们已经创建了一个简单的过程,当它被调用时将打印 "Hello World"。
现在让我们来看看如何调用一个过程。你可以使用EXECUTE 命令来调用过程。
EXECUTE greetings;
输出。
你也可以用以下方式调用它。
BEGIN
greetings;
END;
输出。
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;
我们已经创建了一个过程,它接受两个数字并输出这两个数字的最大值。现在,让我们通过传递参数来调用该过程。
DECLARE
a number;
b number;
c number;
BEGIN
a := 10;
b := 5;
findMax(a,b,c);
dbms_output.put_line(c);
END;
PL/SQL中的函数
一个函数和一个过程是一样的,只是它返回一个值。
让我们创建一个函数的例子,返回我们之前创建的雇员 表中的记录数。
CREATE OR REPLACE FUNCTION totalEmployees RETURN number IS
total number(2) := 0;
BEGIN
SELECT count(*) into total FROM employees;
RETURN total;
END;
我们已经创建了一个函数,用来返回employees 表中的行数。当你定义这个函数时,确保你在函数声明中提供了返回类型(这里,返回类型是数字)。
让我们来调用这个函数。
DECLARE
total number(2);
BEGIN
total := totalEmployees();
dbms_output.put_line('Total no. of Employees: ' || total);
END;
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程序的声明部分声明显式游标。
一般来说,你用显式游标做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;
输出。
你可以看到游标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;
现在,让我们在employees 表中更新一个雇员的工资。
UPDATE employees set salary=80000 where name='Anupam';
你可以看到,当UPDATE操作完成后,触发器被触发。新旧工资显示在输出中。