本文深入浅出地介绍了PLSQL的基础知识与高级特性，涵盖PLSQL概览、块概念、变量、SQL语句、控制语法、自定义数据类型、游标、例外处理、存储过程和函数等内容。通过实例演示了如何在Oracle数据库中高效处理数据。
知识点概览：
PLSQL 概览、PLSQL 的块概念、PLSQL变量|、PLSQL 中的SQL语句、
PLSQL 的控制语法、PLSQL 中的复杂自定义数据类型、
PLSQL 中的游标、PLSQL 的例外处理、PLSQL 中的存储过程和函数
知识点讲解：
PLSQL 概览：
PLSQL 是Oracle 公司在SQL 基础上进行扩展而成的一种过程语言。PLSQL 提供了典型的高级语言特
性，包括封装，例外处理机制，信息隐藏，面向对象等；并把最新的编程思想带到了数据库服务器和工具
集中。
与 与Java, C# 相比 ， ，PLSQL 的优势是：SQL 语言可以直接写到PLSQL 的“块”中或者是PLSQL 的过程、
向 函数中。没有必要向java 那样先创建Statement 对象来执行SQL; 这使得PLSQL 成为很强大的事务处理语
用 言，即：使用SQL 来处理数据，使用控制结构来处理业务逻辑。
PLSQL 在Oracle 数据库服务器（在存储过程、函数、数据库触发器，Package 包中使用）和Oracle 开发
工具集（在； 开发工具组件的触发器中使用）；Form Developer ，Report Developer 还可以使用共享库
（包含使用PLSQL 写的过程和函数，扩展名为PLL 的文件）; SQL 数据类型也可以在PLSQL 中使用，结合
SQL 提供者的直接访问，这些共享数据类型整合了PLSQL 和Oracle 的数据库字典。PLSQL 消除了存取数据
库的便利性与过程语言之间的障碍。
PLSQL 的另一个显著好处在于它可以通过减少来回交互减轻网络流量压力、节省时间：
PLSQL块概念
PLSQL 是一种类PASCAL 语言，每一段程序都是由Block 组成的：
DECLARE (Optional)
Variables, cursors, user-defined exceptions
BEGIN (Mandatory)
SQL statements
PL/SQL statements
EXCEPTION (Optional)
Actions to perform when errors occur
END; (Mandatory)
使用分号作为一句SQL 或者PLSQL 语句的结束；
块结构关键字（DECLARE, BEGIN,EXCEPTION 后面不跟分号；
END 后面需带分号；
你可以把一句SQL 语句写在一行上，但一般不建议这么做，因为代码不够漂亮！
PLSQL 的块包括三种：匿名块、存储过程、函数；
PLSQL 的变量
PLSQL 的变量类型：
1 、系统内置的常规简单变量类型： 比如大多数 数据库表的字段类型都可以作为变量类型；
2、用户自定义复杂变量类型： 比如记录类型；
3 、引用类型：保存了一个指针值；
4 、大对象类型（LOB ）：保存了一个指向大对象的地址；
PLSQL 的变量 类型举例：
布尔类型 数值类型 BFILE类型 日期类型 BLOB类型 LONG类型 字符串类型
SQLPLUS 变量
PLSQL 本身没有输入输出功能，如果要想 像命令行运行C 程序那样可以接收输入值，那你必须依赖执行环境
把值传给PLSQL 块，比如iSQL Plus 执行环境或者PLSQL Developer 的Command Window 执行环境中，
种 有一种substitution 变量 可以用来接收输入值；而另一种Host 变量可以把运行时的值传出到执行环境中。
关 有关SQLPLUS 变量，我们会在以后详细展开；
PLSQL的 的 变量声明语法：
identifier [CONSTANT] datatype [NOT NULL]
[:= | DEFAULT expr];
说明：
1 、变量命名建议遵循通用规则，比如v_name 表示一个变量，c_name 表示一个常量；
2 、一般建议每一行声明一个变量，这样程序的可读性比较好；
3 、如果声明了变量，但未进行初始化，则在没有赋值之前该变量的值为NULL; 一个好的编程习惯是对所有声明
的变量进行初始化赋值。
4 、在同一个块中，避免命名与数据库表中的字段名相同的变量；
PLSQL 特有的%TYPE 属性来声明与XX 类型一致的变量类型：
identifier Table.column_name%TYPE;
可绑定变量（ （Bind Variable 也称为Host Variable , 非PLSQL 变量）：
VARIABLE g_salary NUMBER
BEGIN
SELECT salary
INTO :g_salary
FROM employees
WHERE employee_id = 178;
END;
/
PRINT g_salary
可绑定变量是一种在缩主环境中定义的变量，所谓 缩主 环境一般指示SQLPLUS 执行环境或者是
PLSQL Developer 的Command Window 执行环境；可绑定变量可用于在运行时把值传递给PLSQL, 创建语法：
VARIABLE return_code NUMBER
VARIABLE return_msg VARCHAR2(30)
的 大家注意，在标准的PLSQL 中定义变量是不能用VARIABLE 关键字的，此关键字只在SQLPLUS 执行环境中有效，
可使用PRINT 语句输出变量内容。
在PLSQL 中使用这种变量时，前面加”:”, 以示区分。
DBMS_OUTPUT.PUT_LINE() 介绍：
在接下来的实验中，经常需要在调试程序时输出中间变量的值，我们可使用Oracle 内置的Package 中的函数：
DECLARE
v_sal NUMBER(9,2) := &p_annual_sal;
BEGIN
v_sal := v_sal/12;
DBMS_OUTPUT.PUT_LINE ('The monthly salary is ' ||
TO_CHAR(v_sal));
END;
上述例子中，我们使用DBMS_OUTPUT.PUT_LINE () 输出变量v_sal 的值；
解释：&p_annual_sal 在Plsql Developer 的SQL window 执行环境中，可用于提示用户输入一个具体
的值。
注意：在SQLPLUS 中执行DBMS_OUTPUT.PUT_LINE () 前，必须先执行：
SET SERVEROUTPUT ON ，而在PLSQL Developer 的SQL Window 中则不需要这句话。
PLSQL 中的注释语句：
1 、多行注释类似于java 或者 C ， ， 用 使用 /* 和 和 */
2 、单行注释是在语句后面使用–
SQL 函数在PLSQL 的过程语句中的使用：
哪些可以用？ 哪些不可以用？
大多数SQL 函数都可以在PLSQL 的过程语句中使用，比如：
单行的数值和字符串函数、数据类型转换函数、日期函数、时间函数、求最大、最小值的GREATEST, LEAST
函数等；
但有些函数在PLSQL 的过程语句中是不能使用的，比如：
Decode 函数、分组函数（AVG, MIN, MAX, COUNT, SUM, STDDEV, and VARIANCE ）等；
块嵌套和变量范围：
PLSQL的块是可以嵌套的，变量的作用范围与其他语言类似
变量限定词： 假设我们在块嵌套的程序中，里层和外层有相同的变量声明，而里层的程序要访问外层的同名变量
该怎么办呢？
答案是：使用块限定词，请看例子：
<<outer>>
DECLARE
birthdate DATE;
BEGIN
DECLARE
birthdate DATE;
BEGIN
...
outer.birthdate :=
TO_DATE( ' 03-AUG-1976 ' ,
' DD-MON-YYYY ' );
END;
....
END;
这个例子中，birthdate 是同名变量，限定词outer 表示外层，里层要访问外层的bithdate 时使用
outer.birthdate这种格式
PLSQL中的SQL语句：
SELECT INTO 语句： 用于把从数据库查询出内容存入变量
DECLARE
v_hire_date employees.hire_date%TYPE;
v_salary employees.salary%TYPE;
BEGIN
SELECT hire_date, salary
INTO v_hire_date, v_salary
FROM employees
WHERE employee_id = 100;
...
END;
/
注意点：该语句支持单行的查询结果，果 如果Where 条件控制不好，导致多行查询结果，则会引发Too_many_rows的例外
INSERT 、UPDATE 、DELETE 、MERGE 语句：
在 PLSQL 中执行这些SQL 语句和直接执行这些语句差不多，只不过可以在SQL 语句中使用PLSQL声明的变量；
BEGIN
INSERT INTO employees
(employee_id, first_name, last_name, email,
hire_date, job_id, salary)
VALUES
(employees_seq.NEXTVAL, 'Ruth', 'Cores', 'RCORES',
sysdate, 'AD_ASST', 4000);
END;
/
DECLARE
v_sal_increase employees.salary%TYPE := 800;
BEGIN
UPDATE employees
SET salary = salary + v_sal_increase
WHERE job_id = 'ST_CLERK';
END;
/
DECLARE
v_deptno employees.department_id%TYPE := 10;
BEGIN
DELETE FROM employees
WHERE department_id = v_deptno;
END;
/
DECLARE
v_empno employees.employee_id%TYPE := 100;
BEGIN
MERGE INTO copy_emp c
USING employees e
ON (e.employee_id = v_empno)
WHEN MATCHED THEN
UPDATE SET
c.first_name = e.first_name,
c.last_name = e.last_name,
c.email = e.email,
. . .
WHEN NOT MATCHED THEN
INSERT VALUES(e.employee_id, e.first_name, e.last_name,
. . .,e.department_id);
END;
PLSQL中的控制语句
和其他语言一样，控制主要包括判断和循环；
判断语句的语法与其他语言类似：
IF condition THEN
statements;
[ELSIF condition THEN
statements;]
[ELSE
statements;]
END IF;
CASE selector
WHEN expression1 THEN result1
WHEN expression2 THEN result2
...
WHEN expressionN THEN resultN
[ELSE resultN+1;]
END;
需要注意的是对NULL的判断处理：一般人容易犯错误或者不容易记住
循环语句的语法与其他语言类似：有基本循环、For 循环、Wihle循环三种
LOOP
statement1;
. . .
EXIT [WHEN condition];
END LOOP;
WHILE condition LOOP
statement1;
statement2;
. . .
END LOOP;
FOR counter IN [REVERSE]
lower_bound..upper_bound LOOP
statement1;
statement2;
. . .
END LOOP;
嵌套循环和Label
...
BEGIN
<<Outer_loop>>
LOOP
v_counter := v_counter+1;
EXIT WHEN v_counter>10;
<<Inner_loop>>
LOOP
...
EXIT Outer_loop WHEN total_done = 'YES';
-- Leave both loops
EXIT WHEN inner_done = 'YES';
-- Leave inner loop only
...
END LOOP Inner_loop;
...
END LOOP Outer_loop;
END;
Label 一般用不着，只有在使用goto 语句，或者内部循环需要访问外部的同名变量的时候才需要，而一般这
种做法也是不被提倡的。
PLSQL中的复杂自定义数据类型（类似Java构造器感觉）
概述：PLSQL 中常用的自定义类型就两种：记录类型、PLSQL 内存表类型（根据表中的数据字段的简单和复杂
程度又可分别实现类似于简单数组和记录数组的功能）
记录类型的定义语法：
TYPE type_name IS RECORD
(field_declaration[, field_declaration]…);
identifier type_name;
这里的field_declaration 的具体格式可以是：
field_name {field_type | variable%TYPE
| table.column%TYPE | table%ROWTYPE}
[[NOT NULL] {:= | DEFAULT} expr]
%ROWTYPE 属性：在PLSQL 中 %ROWTYPE 表示某张表的记录类型 或者是用户指定以的记录类型，使用此
属性可以很方便的定义一个变量，其类型与某张表的记录或者自定义的记录类型保持一致。极大的方便了
Select * into ….的语句实用
举例：
DECLARE
emp_rec employees%ROWTYPE;
BEGIN
SELECT * INTO emp_rec
FROM employees
WHERE employee_id = &employee_number;
INSERT INTO retired_emps(empno, ename, job, mgr, hiredate,
leavedate, sal, comm, deptno)
VALUES (emp_rec.employee_id, emp_rec.last_name, emp_rec.job_id,
emp_rec.manager_id, emp_rec.hire_date, SYSDATE, emp_rec.salary,
emp_rec.commission_pct, emp_rec.department_id);
COMMIT;
END;
/
PLSQL 内存表即Index By Table , 这种结构类似于数组，使用主键提供类似于数组那样的元
素访问。： 这种类型必须包括两部分：1 、使用BINARY_INTEGER ； 类型构成的索引主键； 2 、另外一个简单类型
。 或者用户自定义类型的字段作为具体的数组元素。 这种类型可以自动增长，所以也类似于可变长数组。
语法：
TYPE type_name IS TABLE OF
{column_type | variable%TYPE
| table.column%TYPE} [NOT NULL]
| table.%ROWTYPE
[INDEX BY BINARY_INTEGER];
identifier type_name;
PLSQL 内存表应用举例：
下面定义的两个内存表中的元素都是简单数据类型，所以相当于定义了两个简单数组；
DECLARE
TYPE ename_table_type IS TABLE OF
employees.last_name%TYPE
INDEX BY BINARY_INTEGER;
TYPE hiredate_table_type IS TABLE OF DATE
INDEX BY BINARY_INTEGER;
ename_table ename_table_type;
hiredate_table hiredate_table_type;
BEGIN
ename_table(1) := 'CAMERON';
hiredate_table(8) := SYSDATE + 7;
IF ename_table.EXISTS(1) THEN
INSERT INTO ...
...
END;
/
备注：对PLSQL 内存表中某个元素的访问类似于数组，可以使用下表，因为BINARY_INTEGER 这种数据类型
在 的值在-2147483647 ... 2147483647 范围内，所以下表也可以在这个范围内。
PLSQL中的游标
游标概论：游标是一个私有的SQL 工作区域，Oracle 数据库中有两种游标，分别是隐式游标和显式游标，
隐 式 游标不易被用户和程序员察觉和意识到，实际上Oracle 服务器使用隐式游标来解析和执行我们提交的SQL
语句；而显式游标是程序员在程序中显式声明的；通常我们说的游标均指显式游标。
隐式游标的几个有用属性：
显式游标：对于返回多行结果的SQL 语句的返回结果，可使用显式游标独立的处理器中每一行的数据。
显式游标的相关函数可以做到：
1 、一行一行的处理返回的数据。
2 、保持当前处理行的一个跟踪，像一个指针一样指示当前的处理的记录。
3 、允许程序员在PLSQL 块中人为的控制游标的开启、关闭、上下移动；
在程序中对显式游标控制的一般过程：
如果你觉得像前面那个例子那样对一个游标的遍历很麻烦的话，可以考虑使用For 循环，For 循环省去了游标的
声明、打开、提取、测试、关闭等语句，对程序员来说很方便，语法如下：
FOR record_name IN cursor_name LOOP
statement1;
statement2;
. . .
END LOOP;
游标能否带有参数？答案是肯定的：
CURSOR cursor_name
[(parameter_name datatype, ...)]
IS
select_statement;
FOR UPDATE NOWAIT 语句：
有的时候我们打开一个游标是为了更新或者删除一些记录，这种情况下我们希望
用 在打开游标的时候即锁定相关记录，应该使用for update nowait 语句，倘若锁定失败我们就停止不再继续，以免
出现长时间等待资源的死锁情况。
SELECT ...
FROM ...
FOR UPDATE [OF column_reference][NOWAIT];
WHERE CURRENT OF cursor ：
我们经常要逐条处理游标中的每一条记录，在循环体内做Update 或者 Delete
有 时需要有Where 指向游标的当前记录，的 有没有简单一点的的Where 条件写法呢？
答案是肯定的，就是。。。
DECLARE
CURSOR sal_cursor IS
SELECT e.department_id, employee_id, last_name, salary
FROM employees e, departments d
WHERE d.department_id = e.department_id
and d.department_id = 60
FOR UPDATE OF salary NOWAIT;
BEGIN
FOR emp_record IN sal_cursor
LOOP
IF emp_record.salary < 5000 THEN
UPDATE employees
SET salary = emp_record.salary * 1.10
WHERE CURRENT OF sal_cursor;
END IF;
END LOOP;
END;
/
PLSQL中的例外处理
PLSQL 中的例外一般有两种：
1 、Oracle 内部错误抛出的例外：这又分为预定义例外（有错误号+ 常量定义）和 和 非预定义例外
（仅有错误号，无常量定义）
2 、程序员显式的抛出的例外
PLSQL 中的 例外捕获和传递：与其他语言类似，如果例外在当前块中被处理，则到此为止，否则会被传递到
外层（外层BLOCK 或者 外层调用者函数）
PLSQL 中的例外处理一般语法：
处理预定义的例外：有些常见例外，Oracle 都已经预定义好了，使用时无需预先声明，比如：
NO_DATA_FOUND 和 和 TOO_MANY_ROWS 是最常见的例外，大多数Block 中都建议对这两种例外
有处理； 完整的预定义例外的列表，
请参考：PL/SQL User’s Guide and Reference, “Error Handling.” （百度搜索，下载点很多）
OTHERS 的处理：
Others 表明我们程序员未能预计到这种错误，所以全部归入到others 里面去了，单发生这种的情况是，我们还是希望了解当时发生的Oracle 错误号和相关描述信息，怎样才能取到呢？Oracle 提供了两个内置函数SQLCODE 和 和 SQLERRM 分别用来返回Oracle 错误号和错误描述
处理非预定义的Oracle 错误：
此类错误属于Oracle 错误，有编号，但无错误名称定义，使用时需要先声明，并进行错误初始化：
Oracle 内部错误号很多，想了解全部的Ora 错误号，请参考：http://www.ora-code.com/
处理用户自定义的错误:
这种错误一般是程序员根据具体的业务逻辑定义的应用类错误，需要先声明后使用：
定义和处理过程如下：
RAISE_APPLICATION_ERROR() 函数：对于用户自定义的业务错误，如果觉得先定义再使用很麻烦，那么也可以简单的使用raise_application_error() 来简化处理。它可以无需预先定义错误，而在需要抛出错误的地方直接使用此函数抛出例外，例外可以包含用户自定义的错误吗和错误描述；
PLSQL中的存储过程和函数：
语法：
CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter1 [mode1] datatype1,
parameter2 [mode2] datatype2,
. . .)]
IS|AS
PL/SQL Block;
自己去深入了解吧，这里是重点！！！说不清
今日作业：重点存储过程！！
-- 学生表：学号、姓名、性别、年龄、班级
select * from t2_student;
create table t2_student(
sno varchar2(11) primary key,
sname varchar2(20) not null,
sex varchar2(3),
age number(3),
classid varchar2(5),
classname varchar2(20),
constraint c2_1 check(sex in ('男','女'))
);
-- 成绩表：学号、课程、成绩
select * from t2_grade;
create table t2_grade(
sno varchar2(11),
cno varchar2(5),
cname varchar2(20)，
grade number,
constraint c2_2 check(grade >=0 and grade <=100)
);
insert into t2_student values('s001','张三','男',15,'c001','18班');
insert into t2_student values('s002','李华','女',15,'c001','18班');
insert into t2_student values('s003','李斯','男',16,'c002','19班');
insert into t2_student values('s004','王武','男',16,'c002','19班');
insert into t2_student values('s005','赵柳','女',16,'c002','19班');
insert into t2_student values('s006','孙琪','女',17,'c002','19班');
insert into t2_grade values('s001','c01','语文',90);
insert into t2_grade values('s001','c02','数学',80);
insert into t2_grade values('s002','c01','语文',70);
insert into t2_grade values('s002','c02','数学',90);
insert into t2_grade values('s003','c02','数学',80);
insert into t2_grade values('s004','c02','数学',91);
insert into t2_grade values('s005','c02','数学',92);
insert into t2_grade values('s006','c02','数学',93);
-- 以下都是存储过程的练习，要求使用存储过程操作
-- 1、插入3条数据到学生表
select * from t2_student;
create or replace procedure proc_insert_student
(v_age in t2_student.age%TYPE)
is
begin
insert into t2_student values('s007','老八','男',v_age,'c003','20班');
insert into t2_student values('s008','老九','女',v_age,'c003','20班');
insert into t2_student values('s009','老十','男',v_age,'c003','20班');
commit;
end proc_insert_student;
begin
cheirmin.proc_insert_student(18);
end;
-- 2、打印所有18岁的学生
--使用存储过程加游标
create or replace procedure proc_select_age_student
(v_age t2_student.age%TYPE)
is
cursor stu_cursor1 is
select sname,age from t2_student where age = v_age;
c_sname t2_student.sname%TYPE :='';
c_age t2_student.age%TYPE :=0;
begin
open stu_cursor1;
loop
fetch stu_cursor1 into c_sname,c_age;
exit when stu_cursor1%notfound;
dbms_output.put_line(c_sname||' - '||c_age);
end loop;
close stu_cursor1;
end proc_select_age_student;
--调用存储过程
begin
cheirmin.proc_select_age_student(18);
end;
--只使用游标
declare
v_age t2_student.age%TYPE := 0;
v_sname t2_student.sname%TYPE :='';
cursor stu_cursor1 is
select sname,age from t2_student where age = 18;
begin
open stu_cursor1;
loop
fetch stu_cursor1 into v_sname,v_age;
exit when stu_cursor1%rowcount > 10 or stu_cursor1%notfound;
dbms_output.put_line(v_sname||' - '||v_age);
end loop;
close stu_cursor1;
end;
-- 3、更新“张三”的年龄为20岁，返回更新后的数据
select * from t2_student where sname = '张三';
create or replace procedure proc_update_age
(v_age in t2_student.age%TYPE,
c_name out t2_student.sname%TYPE,
c_age out t2_student.age%TYPE)
is
begin
update t2_student set age = v_age where sname = '张三';
commit;
select sname,age into c_name,c_age from t2_student where sname = '张三';
end proc_update_age;
--执行代码块测试上述存储过程；成立
declare
v_name varchar2(20);
v_age number(3);
begin
cheirmin.proc_update_age(20,v_name,v_age);
dbms_output.put_line(v_name||' - '||v_age);
end;
-- 4、将“张三”的年龄、性别、班级数据赋值给“李斯”
select * from t2_student where sname = '张三';
select * from t2_student where sname = '李斯';
create or replace procedure proc_select_and_update
as
v_age t2_student.age%TYPE;
v_sex t2_student.sex%TYPE;
v_classid t2_student.classid%TYPE;
v_classname t2_student.classname%TYPE;
begin
select age,sex,classid,classname
into v_age,v_sex,v_classid,v_classname
from t2_student where sname = '张三';--查
update t2_student
set age = v_age,sex = v_sex,classid = v_classid,classname = v_classname
where sname = '李斯';--赋值
end proc_select_and_update;
--执行代码块测试上述存储过程；成立
begin
cheirmin.proc_select_and_update;
end;
-- 5、将“19班”学生的所有成绩全部在原有基础上加10分
select * from t2_student;
select * from t2_grade;
update t2_grade set grade = grade+10 where sno in(
select sno from t2_student where classname = '19班');
create or replace procedure proc_update_grade
is
cursor p_cursor2 is
select sno from t2_student where classname = '19班';
c_sno t2_student.sno%TYPE :=' ';
begin
open p_cursor2;
loop
fetch p_cursor2 into c_sno;
exit when p_cursor2%notfound;
update t2_grade set
grade = (case when grade <90 then (grade +10) else 100 end)
where sno = c_sno;
end loop;
close p_cursor2;
end proc_update_grade;
--执行代码块测试上述存储过程；成立
begin
cheirmin.proc_update_grade;
end;
-- 6、查询“张三”的语文课成绩（一个入参）
select * from t2_student;
select * from t2_grade;
create or replace procedure proc_select_grade
(v_cname in t2_grade.cname%TYPE,
c_grade out t2_grade.grade%TYPE)
is
begin
select grade into c_grade
from t2_grade where sno =
(select sno from t2_student where sname = '张三')
and cname = v_cname;
end proc_select_grade;
--执行代码块测试上述存储过程；成立
declare
v_grade t2_grade.cname%TYPE;
begin
cheirmin.proc_select_grade('语文',v_grade);
dbms_output.put_line(v_grade);
end;
-- 7、删除“张三”的数学成绩
create or replace procedure proc_delete_grade
is
begin
delete from t2_grade where sno =
(select sno from t2_student where sname = '张三')
and cname = '数学';
end proc_delete_grade;
begin
cheirmin.proc_delete_grade;
end;
扫一扫
1258
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
