PL/SQL是一种高级数据库程序设计语言,它是ORACLE对标准数据库语言的扩展，是PL (Procedural Language,过程语言)与SQL (Structured Query Language,结构化查询语言)结合而成的编程语言。它支持多种数据类型，如大对象和集合类型，可使用条件和循环等控制结构，可用于创建存储过程、触发器和程序包，还可以处理业务规则、数据库事件或给SQL语句的执行添加程序逻辑。另外，PL/SQL 还支持许多增强的功能，包括集合类型、面向对象的程序设计和异常处理等。

PL/SQL的特点

PL/SQL是一种可移植的高性能事务处理语言，支持SQL和面向对象编程，具有良好的性能和高效的处理能力。其特点包括以下几方面。

(1)支持SQL

通过使用SQL,用户可以轻松地操纵存储在关系数据库中的数据。PL/SQL中可以使用数据操纵命令、事务控制命令、游标控制命令、SQL函数和SQL运算符，因此可以更加灵活、有效地操纵表中的数据。

(2)可移植性

使用PLSQL编写的应用程序可移植到安装在任何操作系统和平台上的Oralce数据库服务器上。

(3)高性能

SQL是一种非过程语言，一次只能执行一条语句，在连续的语句之间没有关联。PL/SQL一次可处理整个语句块，减少了在应用程序和Oracle服务器之间进行通信所花费的时间，从而提高了性能。PL/SQL 经过编译执行，过程调用快速而高效。

(4)与SQL紧密集成

PL/SQL与SQL紧密集成，支持所有SQL数据类型,支持NULL值,支持%TYPE和%ROWTYPE属性类型，简化数据处理。

(5)安全性强

可以通过PLSQL存储过程对客户机和服务器之间的应用程序逻辑进行分隔，阻止客户机应用程序操纵敏感的Oracle数据，仅允许用户通过存储过程操纵数据，限制用户对数据的访问。

结构

1
2
3
4
5
6
7
8
[DECLARE]
-- declaration statements声明部分
BEGIN
-- executable statements可执行部分
[EXCEPTION]
--exception statements异常处理部分
END

用一个完整的PL/SQL块实现查询雇员号为7934的雇员信息。

1
2
3
4
5
6
7
8
9
Declare
p_sal number(7,0);
P_comm number(7,0);
Begin
select sal,comm into P_sal,p_comm from emp where empno=7934;
Exception
When no_data_found Then
Dbms_output.put_line('员工号不存在');
End;

声明常量或变量

1
2
3
4
<变量常量名>[CONSTANT]<数据类型>[NOT NULL][:=IDEFAULT<初始值>];
--例如：
total constant number: =100;
v_name varchar2 (10) ;

①变量名和常量名必须以字母AZ开头，不区分大小写，其后面可跟-一个或多个字母、数字(09)、特殊字符($、#或_ )，长度不能超过30个字符，变量名和常量名中不能有空格。

②CONSTANT是声明常量的关键字，只在声明常量时使用。

③每一个变量或常量都有一个特定的数据类型。

④每个变量或常量声明占- -行， 行尾使用分号结束。

⑤常量必须在声明时赋值。变量在声明时可以不赋值，如果变量在声明时没有赋初值,那么PL/SQL语言自动为其赋值NULL。若在变量声明中使用了NOT NULL, 则表示该变量是非空变量，即必须在声明时给该变量赋初值，否则会出现编译错误。在PLSQL程序中，变量值是可以改变的，而常量的值不能改变。变量的作用域是从声明开始到PL/SQL程序块结束。

1
2
3
4
5
6
7
--①%TYPE:引用变量和数据库列的数据类型。例如:
empcode emp.empno%TYPE;
--该语句声明了变量empcode,其数据类型与表emp中的empno列数据类型相同。
--②%ROWTYPE:表示表、视图或游标的完整一行的记录类型。 例如:
emp_ex emp%ROWTYPE;
--该语句声明了变量emp ex,它可以用于存储从emp中提取的记录。

编写 PL/SQL程序，使用%TYPE和%ROWTYPE声明变量，用于查
询雇员号及雇员信息。

1
2
3
4
5
6
7
8
9
10
SQL>set serverout on
declare
empcode emp.empno%type;
emp_ex emp%ROWTYPE;
begin
select empno into empcode from emp where ename= 'SMITH';
select * into emp_ex from emp where ename ='ALLEN';
dbms_output.put_line('员工SMITH的雇员号为: '||empcode);
dbms_output.put_line('员工ALLEN的雇员信息为:'||'雇员号'||emp_ex.empno||' '||'工作职位'||emp_ex.job||'薪水'||emp_ex.sal);
end;

记录类型

PL/SQL记录是由- -组相关的记录成员组成的，记录通常用来表示对应数据库表中的一行。使用PL/SQL 记录时应自定义记录类型和记录变量，也可以使用%ROWTYPE属性定义记录变量。引用记录成员时，必须要以记录变量作为前缀。

1
2
3
4
5
6
TYPE <记录类型名> IS RECORD (
<数据项1> <数据类型>[NOTNULL[:= <表达式1>]],
<数据项2> <数据类型>[NOTNULL[:=<表达式2>]],
......
<数据项n> <数据类型>[NOTNULL[:=<表达式n>]]) ;
<记录变量名> <记录类型名> ;

将雇员信息定义为记录类型。

1
2
3
4
5
6
7
8
9
10
11
12
declare
type emp_record_type is record
(v_ename emp.ename%type,
v_job emp.job%type,
v_sal emp.sal%type);
emp_rec emp_record_type;
begin
select ename,job,sal into emp_rec
from emp where empno=&eno;
dbms_output.put_line(emp_rec.v_ename||':'||
emp_rec.v_job||': '||emp_rec.v_sal);
end;

PL/SQL表

PL/SQL表是一种比较复杂的数据结构，与数据库中的表是有区别的。数据库表是一种二维表，是以数据库表的形式存储。这里的表是一种复合数据类型，是保存在数据缓冲区中的没有特别存储次序、可以离散存储的数据结构，它可以是-维的，也可以是二维的。当使用PLSQL表时，首先必须在声明部分定义该类型和变量,然后在执行部分引用该变量。

1
2
TYPE <表类型名> IS TABLE OF <数据类型> INDEX BY BINARY_INTEGER;
<表变量名><表类型名>;

索引表类型的定义。

1
2
3
4
5
6
7
8
9
DECLARE
TYPE ename_table_type IS TABLE OF emp.ename%TYPE
INDEX BY BINARY_INTEGER;
Ename_table ename_table_type;
BEGIN
SELECT ename INTO ename_table(1) FROM emp
WHERE empno= 7902;
Dbms_output.put_line('员工名:'|| ename_table(1));
END;

数组

数组也是一种复合类型，和表类似，与表不同的是声明了-一个数组，就确定了数组中元素的数目。同时，数组存储时，其元素的次序是固定且连续的，而且索引变量从1开始一直到其定义的最大值为止。

1
2
TYPE <数组类型名> IS VARRAY (<MAX_SIZE>) OF <数据类型>;
<表变量名><表类型名> ;

NULL

1
NULL;

赋值

1
<variable>:= <expression> ;

对变量赋值的两种方法。

1
2
3
4
5
6
7
8
declare
v_job varchar2(10);
v_sal number;
begin
v_job:='CLERK';
select max(sal) into v_sal from emp where job=v_job;
dbms_output.put_line(v_sal);
end;

IF…THEN

F…THEN..ELSE

F..THEN..ELSIF

通过IF条件控制语句为不同部门的员工增加工资

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
DECLARE
v_deptno emp.deptno%type;
v_empno emp.empno%type;
v_sal number(6,2);
BEGIN
v_empno:=&no;
SELECT deptno,sal INTO v_deptno,v_sal FROM emp WHERE empno= v_empno;
IF v_deptno =10 THEN
UPDATE emp SET sal=v_sal+100 WHERE empno=v_empno;
ELSIF v_deptno =20 THEN
UPDATE emp SET sal=v_sal+200 WHERE empno=v_empno;
ELSIF v_deptno =30 THEN
UPDATE emp SET sal=v_sal+300 WHERE empno=v_empno;
ELSE
UPDATE emp SET sal=v_sal+400 WHERE empno=v_empno;
END IF;
END;

CASE

带选择器按值比较的CASE语句

1
2
3
4
5
6
7
CASE选择器
WHEN表达式1THEN执行语句1;
WHEN表达式2 THEN执行语句2;
...
WHEN表达式N THEN 执行语句N;
ELSE执行语句N+1;
END CASE;

通过CASE条件控制语句为不同部门的员工增加工资。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
DECLARE
v_deptno emp.deptno%type;
v_empno emp.empno%type;
v_sal number(6,2);
BEGIN
v_empno:= &no;
SELECT deptno,sal INTO v_deptno,v_sal FROM emp WHERE empno= v_empno;
CASE v_deptno
When 10 THEN
UPDATE emp SET sal=v_sal+ 100 WHERE empno= v_empno;
When 20 THEN
UPDATE emp SET sal=v_sal+300 WHERE empno= v_empno;
When 30 THEN
UPDATE emp SET sal=v_sal+300 WHERE empno= v_empno;
When 40 THEN
UPDATE emp SET sal=v_sal+300 WHERE empno= v_empno;
ELSE
Dbms_output.put_line('不存在该部门!');
END CASE;
END;

不带选择器按条件比较的CASE语句

1
2
3
4
5
6
CASE
WHEN条件表达式1 THEN执行语句l;
WHEN条件表达式2 THEN执行语句2;
WHEN条件表达式N THEN执行语句N;
[ELSE执行语句N+1;]
END CASE;

LOOP

1
2
3
4
5
LOOP
statements;
...
EXIT [WHEN condition];
END LOOP;

使用LOOP循环依次输出1~5的立方数。

1
2
3
4
5
6
7
8
9
DECLARE
i number:=1;
BEGIN
LOOP
Dbms_output.put_line(i ||'的立方为'|| i*i*i);
i:=i+1;
EXIT WHEN i>5;
END LOOP;
END;

while

1
2
3
4
5
WHILE condition LOOP
Statementl;
Statement2;
...
END LOOP;

使用WHILE循环依次输出1~5的立方数。

1
2
3
4
5
6
7
8
DECLARE
i number:=1;
BEGIN
WHILE i<=5 LOOP 
Dbms_output.put_line(i||'的立方为'|| i*i*i);
i:=i+1;
END LOOP; 
END;

FOR

1
2
3
FOR counter IN [REVERSE] start_rang...end_range LOOP
statements;
END LOOP;

使用FOR循环依次输出1~5的立方数。

1
2
3
4
5
6
7
DECLARE
i number:=1; 
BEGIN
FOR i in 1..5 LOOP
Dbms_output.put_line(i ||'的立方为'|| i*i*i);
END LOOP;
END;