知识点概览:
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;