大型数据库技术（六）PL/SQL语言 -中

最新推荐文章于 2024-07-23 19:46:40 发布

passionfruit28175

最新推荐文章于 2024-07-23 19:46:40 发布

阅读量184

点赞数

文章标签：数据库 sql

本文链接：https://blog.youkuaiyun.com/m0_74089390/article/details/134083041

版权

使用PL/SQL编写子程序

一、sql伪列

PL/SQL所支持的SQL元素包括：

SQL运算符

SQL伪列

DML语句

事务控制语句

动态SQL语句

SQL内置函数

伪列只能在SQL语句中使用。

包括：

ROWID

ROWNUM

LEVEL

CURRVAL和NEXTVAL

ROWNUM在查询中返回当前的行序号。
常用于限制要处理的行的总数。 
 例如：
SELECT * FROM students
WHERE rownum<=3
ORDER BY last_name

LEVEL仅仅用在对表执行层次树遍历的SELECT语句， LEVEL值反映结构化查询所返回的每一个数据行间的 结构关系。

在SELECT语句中使用STRAT WITH和CONNECT BY子句定 义一个查询的结构关系，该结构相当于一个倒置树， 它由一个根节点和不同级别的子节点构成。 根节点的LEVEL值为1，一级子节点的LEVEL值为2，二 级子节点值为3，依次类推。

SELECT LEVEL,employee_id,first_name,last_name,job_id,manager_id
FROM HR.employees
START WITH job_id='FI_MGR'
CONNECT BY PRIOR employee_id=manager_id

上述语句的执行顺序为 ：

 首先查找满足START WITH条件的根数据行；

 选择每一个根数据行的子行，每个子数据行与其父行之间必

须满足CONNECT BY指定的条件；

 选择子行的下一级数据行，如此重复，直至查询完所有数据

行为止；

 如果SELECT语句中包含有WHERE子句，则从上述数据行中删

除不满足条件的数据行。

CURRVAL和NEXTVAL

 这两个伪列和序列一起使用。

 假定用户trainee已创建了序列student_sequence:

SELECT trainee.student_sequence.currval FROM dual;

SELECT trainee.student_sequence.nextval FROM dual;

二、使用游标

游标提供了⼀种从表中检索数据并进⾏操作的灵活⼿段，游标主要⽤在服务器上，处理由客户端发送给服务器端的SQL 语句，或是批处理、存储过程、触发器中的数据处理请求。游标的作⽤就相当于指针，通过游标PL/SQL 程序可以⼀次处理查询结果集中的⼀⾏，并可以对该⾏数据执⾏特定操作，从⽽为⽤户在处理数据的过程中提供了很⼤⽅便。

PL/SQL游标分为显式游标和隐式游标两种。

①游标操作包括：

声明游标

CURSOR cursor_name [(parameter[,parameter]...)] [RETURN return_type]
 IS select_statement；

打开游标

提取和处理游标数据

FETCH cursor_name INTO variable_list | record_variable

fetch cur_name into {variable};

关闭游标

close cur_name;

②游标的属性

游标有四种属性：

％ NOTFOUND

％ FOUND

％ ROWCOUNT

％ ISOPEN

例子：

任务描述：
 例如我们要修改某一间教室的座位数量，如果根据给
定的教室编号没有查找到相应的记录，则在表中插入
该条记录。
任务求解：
 使用隐式游标的%NOTFOUND属性。

BEGIN
UPDATE rooms
SET number_seats = 100
WHERE room_id = 99980;
IF SQL%NOTFOUND THEN
INSERT INTO rooms (room_id, number_seats)
VALUES (99980, 100);
END IF;
END;

任务描述：
 将历史专业学生的信息从students表中提取出来，注
册历史系课程301，并将注册信息填入到表
registered_students中，将姓名和学号填入到临时
表temp_table中。
任务求解：

DECLARE
v_StudentID students.id%TYPE;
v_FirstName students.first_name%TYPE;
v_LastName students.last_name%TYPE;
CURSOR c_HistoryStudents IS
SELECT id, first_name, last_name
FROM students
WHERE major = 'History';
BEGIN
OPEN c_HistoryStudents;
LOOP
FETCH c_HistoryStudents INTO v_StudentID, v_FirstName, v_LastName;
EXIT WHEN c_HistoryStudents%NOTFOUND;
INSERT INTO registered_students (student_id, department, course)
VALUES (v_StudentID, 'HIS', 301);
INSERT INTO temp_table (num_col, char_col)
VALUES (v_StudentID, v_FirstName || ' ' || v_LastName);
END LOOP;
CLOSE c_HistoryStudents;
COMMIT;
END;

定义参数的游标：

SQL> declare
 2 cursor cur_emp(var_job in varchar2:='SALESMAN')
 3 is select empno,ename,sal
 4 from emp
 5 where job=var_job;

open cur_emp('MANAGER');

例子：

声明⼀个检索emp表中雇员信息的游标，然后打开游标，并指定检索职务是 “MANAGER” 的雇员信息，接着使⽤fetch...into语句和while循环读取游标中的所有雇员信息，最后输出读取的雇员信息，代码如下。

SQL> set serveroutput on
SQL> declare
 2 /*声明游标，检索雇员信息*/
 3 cursor cur_emp (var_job in varchar2:='SALESMAN')
 4 is select empno,ename,sal
 5 from emp
 6 where job=var_job;
 7 type record_emp is record //声明⼀个记录类型（RECORD类型）
 8 (
 9 /*定义当前记录的成员变量*/
 10 var_empno emp.empno%type,
 11 var_ename emp.ename%type,
 12 var_sal emp.sal%type
 13 );
 14 emp_row record_emp; //声明⼀个record_emp类型的变量
 15 begin
 16 open cur_emp('MANAGER'); //打开游标
 17 fetch cur_emp into emp_row; //先让指针指向结果集中的第⼀⾏，并将值保存到emp_row中
 18 while cur_emp%found loop
 19 dbms_output.put_line(emp_row.var_ename||' 的 编 号是'||emp_row.var_empno||',⼯资 
  是'||emp_ row.var_sal);
 20 fetch cur_emp into emp_row; //让指针指向结果集中的下⼀⾏，并将值保存到emp_row中
 21 end loop;
 22 close cur_emp; //关闭游标
 23 end;
 24 /

DECLARE
CURSOR c_HistoryStudents IS
SELECT id, first_name, last_name
FROM students
WHERE major = 'History';
BEGIN
FOR v_StudentData IN c_HistoryStudents LOOP
INSERT INTO registered_students (student_id, department, course)
VALUES (v_StudentData.id, 'HIS', 301);
INSERT INTO temp_table (num_col, char_col)
VALUES (v_StudentData.id,
v_StudentData.first_name || ' ' || v_StudentData.last_name);
END LOOP;
COMMIT;
END;

带参数的游标例子：

将用户指定专业学生的信息从students表中提取出来 ，注册指定系开设的指定课程，并将注册信息填入到 表registered_students中，将姓名和学号填入到临 时表temp_table中。现在我们要求将学生的专业代码 （students.major）参数化，可以多次使用这个游标 将不同专业的学生选择某个系开设的某门课程信息进 行注册。

DECLARE
v_StudentID students.id%TYPE;
v_FirstName students.first_name%TYPE;
v_LastName students.last_name%TYPE;
v_Department classes.department%TYPE;
v_Course classes.course%TYPE;
CURSOR c_Students(p_Major students.major%TYPE) IS
SELECT id, first_name, last_name
FROM students
WHERE major = p_Major;

BEGIN
v_Department:='CS';
v_Course:=101; 
OPEN c_Students('Computer Science');
LOOP
FETCH c_Students INTO v_StudentID, v_FirstName, v_LastName;
EXIT WHEN c_Students%NOTFOUND;
INSERT INTO registered_students (student_id, department, course)
VALUES (v_StudentID, v_Department, v_Course);
INSERT INTO temp_table (num_col, char_col)
VALUES (v_StudentID, v_FirstName || ' ' || v_LastName);
END LOOP;
CLOSE c_Students;
COMMIT;
END;

SELECT FOR UPDATE游标

在游标定位下，修改或删除表中指定的数据行，游标 查询语句中必须使用FOR UPDATE选项。

游标查询语句的语法格式为：

SELECT …FROM …

FOR UPDATE [ OF column [,column]…] [NOWAIT]

例子：

学生注册某课程后，注册信息登记在表 registered_students中，通过考试后，如果合格则

获得相应的学分。现在要求将注册了HIS 101课程的 学生所获得的学分加入到学生的总学分上。

DECLARE
v_NumCredits classes.num_credits%TYPE;
CURSOR c_RegisteredStudents IS
SELECT *
FROM students
WHERE id IN (SELECT student_id
FROM registered_students
WHERE department= 'HIS'
AND course = 101 
AND grade IN ('A', 'B', 'C', 'D') )
FOR UPDATE OF current_credits;

BEGIN
SELECT num_credits
INTO v_NumCredits
FROM classes
WHERE department = 'HIS' AND course = 101;
FOR v_StudentInfo IN c_RegisteredStudents LOOP
UPDATE students
SET current_credits = current_credits + v_NumCredits
WHERE CURRENT OF c_RegisteredStudents;
END LOOP;
COMMIT;
END;

隐式游标：

在 SCOTT 模式下，把 emp 表中销售员（即 SALESMAN ）的⼯资上调 20% ，然后使⽤隐式游标 sql 的%rowcount 属性输出上调⼯资的员⼯数量，代码如下。

SQL> set serveroutput on
SQL> begin
 2 update emp
 3 set sal=sal*(1+0.2)
 4 where job='SALESMAN'; //把销售员的⼯资上调20%
 5 if sql%notfound then //若update语句没有影响到任何⼀⾏数据
 6 dbms_output.put_line('没有雇员需要上调⼯资');
 7 else //若update语句⾄少影响到⼀⾏数据
 8 dbms_output.put_line('有'||sql%rowcount||'个雇员⼯资上调20%');
 9 end if;
 10 end;
 11 /

三、创建存储过程

存储过程是⼀种命名的 PL/SQL 程序块，它既可以没有参数，也可以有若⼲个输⼊、输出参数，甚⾄可以有多个既作输⼊⼜作输出的参数，但它通常没有返回值。存储过程被保存在数据库中，它不可以被 SQL语句直接执⾏或调⽤，只能通过 EXECUT 命令执⾏或在 PL/SQL 程序块内部被调⽤。由于存储过程是已经编译好的代码，所以其被调⽤或引⽤时，执⾏效率⾮常⾼。

1.创建存储过程的语法如下：

CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter1 [{IN|OUT|IN OUT}] datatype
[{:=|DEFAULT} expression]
[, parameter2 [{IN|OUT|IN OUT}] datatype
[{:=|DEFAULT} expression]
…])]
{IS|AS}
[declarations]
BEGIN
code
[EXCEPTION
exception_handlers]
END

例子：

创建⼀个存储过程，该存储过程实现向 dept 表中插⼊⼀条记录，代码及运⾏结果如下。

SQL> create or replace procedure pro_insertDept is
 2 begin
 3 insert into dept values(77,' 市 场 拓 展 部 ','JILIN'); 
//插⼊数据记录
 4 commit; //提交数据
 5 dbms_output.put_line(' 插 ⼊ 新 记 录 成 功 ！ '); 
//提⽰插⼊记录成功
 6 end pro_insertDept;
 7 /
过程已创建

执行procedu存储过程：

SQL> execute pro_insertDept;

在 PL/SQL 块中调⽤存储过程 pro_insertDep ，然后执⾏这个PL/SQL 块

SQL> set serverout on
SQL> begin
 2 pro_insertDept;
 3 end;
 4 /

2.存储过程的参数：

存储过程中的参数传递

 有三种参数模式：IN、OUT、IN OUT。

 参数传递方法有两种：

 参数位置对应法

 参数命名传递法

 采用命名传递法时，调用的参数顺序不必和定义时的 参数顺序一致。

①in

创建⼀个存储过程，并定义 3 个 in 模式的变量，然后将这3 个变量的值插⼊到 dept 表中，代码及运⾏结果如下

SQL> create or replace procedure insert_dept(
 2 num_deptno in number, //定义in模式的变量，它存储部门编号
 3 var_ename in varchar2, //定义in模式的变量，它存储部门名称
 4 var_loc in varchar2) is
 5 begin
 6 insert into dept
 7 values(num_deptno,var_ename,var_loc); //向dept表中插⼊记录
 8 commit; //提交数据库
 9 end insert_dept;
 10 /
过程已创建

在 PL/SQL 块中调⽤存储过程 insert_dept ，然后使⽤“ 指定名称 ” 的⽅式向其传⼊参数值，最后执⾏当前的 PL/SQL 块，代码及运⾏结果如下。

SQL> begin
2 insert_dept(var_ename=>' 采 购 部 ',var_loc=>' 成都',num_deptno=>15);
 3 end;
 4 /
PL/SQL 过程已成功完成


//按位置传递参数
SQL> begin
 2 insert_dept(28,'⼯程部','洛阳');
 3 end;
 4 /

②OUT模式参数

这是⼀种输出类型的参数，表⽰这个参数在存储过程中已经被赋值，并且这个参数值可以传递到当前存储过程以外的环境中，关键字out位于参数名称之后。下⾯来看⼀个实例。

创建⼀个存储过程，要求定义两个 out 模式的字符类型的参数，然后在dept 表中检索到的⼀⾏部门信息存储到这两个参数中，代码及运⾏结果如下。

SQL> create or replace procedure select_dept(
 2 num_deptno in number, /定义in模式变量，要求输⼊部门编号
 3 var_dname out dept.dname%type, //定义out模式变量，可以存储部门名称并输出
 4 var_loc out dept.loc%type) is
 5 begin
 6 select dname,loc
 7 into var_dname,var_loc
 8 from dept
 9 where deptno = num_deptno; //检索某个部门编号的部门信息
 10 exception
 11 when no_data_found then //若select语句⽆返回记录
 12 dbms_output.put_line(' 该 部 门 编 号 的 不 存 在 '); //输出信息
 13 end select_dept;
 14 /
 过程已创建

⾸先在 PL/SQL 块中声明若⼲变量，然后调⽤ select_dept存储过程，并将定义的变量传⼊该存储过程，以便接收out参数的返回值，代码如下。

SQL> set serverout on
SQL> declare
2 var_dname dept.dname%type; //声明变量，对应过程中的out模式的var_dname
3 var_loc dept.loc%type; //声明变量，对应过程中的out模式的var_loc
4 begin
5 select_dept(99,var_dname,var_loc); //传⼊部门编号，然后输出部门名称和位置信息
6 dbms_output.put_line(var_dname||'位于：'||var_loc); //输出部门信息
 7 end;
 8 /

在pl/sql中调用直接用procedure的名称，在sql*plus得加个exec

③in out

在执⾏存储过程时， IN 参数不能被修改，它只能根据被传⼊的指定值（或是默认值）为存储过程提供数据，⽽OUT 类型的参数只能等待被赋值，⽽不能像IN 参数那样为存储过程本⾝提供数据。但 IN OUT参数可以兼顾其他两种参数的特点，在调⽤存储过程时，可以从外界向该类型的参数传⼊值；在执⾏完存储过程之后，可以将该参数的返回值传给外界。下⾯来看⼀个例⼦

创建⼀个存储过程，在其中定义⼀个 “in out” 参数，该存储过程⽤来计算这个参数的平⽅或平⽅根，代码及运⾏结果如下。

SQL> create or replace procedure pro_square(
2 num in out number, //计算它的平⽅或平⽅根，这是⼀个"in out"参数
3 flag in boolean) is //计算平⽅或平⽅根的标识，这是⼀个"in"参数
4 i int := 2; //表⽰计算平⽅，这是⼀个内部变量
5 begin
6 if flag then //若为true
7 num := power(num,i); //计算平⽅
8 else
9 num:=sqrt(num); //计算平⽅根
10 end if;
11 end;
12 /

调⽤存储过程 pro_square ，计算某个数的平⽅或平⽅根，代码如下。

SQL> declare
2 var_number number; //存储要进⾏运算的值和运算后的结果
3 var_temp number; //存储要进⾏运算的值
4 boo_flag boolean; //平⽅或平⽅根的逻辑标记
5 begin
6 var_temp :=3; //变量赋值
7 var_number :=var_temp;
8 boo_flag := false; //false表⽰计算平⽅根；true表⽰计算平⽅
9 pro_square(var_number,boo_flag); //调⽤存储过程
10 if boo_flag then
11 dbms_output.put_line(var_temp ||' 的 平 ⽅是：'||var_number);//输出计算结果
12 else
13 dbms_output.put_line(var_temp ||' 平 ⽅ 根是：'||var_number);
14 end if;
15 end;
16 /
本例运⾏结

④IN参数的默认值

SQL> create or replace procedure insert_dept(
2 num_deptno in number, //定义存储部门编号的IN参数
3 var_dname in varchar2 default ' 综 合 部 ', //定义存储部门名称的IN参数，并初始默认值
4 var_loc in varchar2 default '北京') is
5 begin
6 insert into dept values(num_deptno,var_dname,var_loc); //插⼊⼀条记录
7 end;
8 /
过程已创建

四、函数

函数⼀般⽤于计算和返回⼀个值，可以将经常需要使⽤的计算或功能写成⼀个函数。

1.创建函数

create [or replace] function
fun_name[(parameter1[,parameter2]...) return data_type is
 [inner_variable]
begin
 plsql_ sentence;
[exception]
 [dowith _ sentences;]
end [fun_name];

例子：

定义⼀个函数，⽤于计算 emp 表中指定某个部门的平均⼯资，代码及运⾏结果如下。

SQL> create or replace function get_avg_pay(num_deptnonumber) return number is //创建⼀个函数，该函数实现计算某个部门的平均⼯资，传⼊部门编号参数
2 num_avg_pay number; //保存平均⼯资的内部变量
3 begin
4 select avg(sal) into num_avg_pay from emp where deptno=num_deptno; //某个部门的平均⼯资
5 return(round(num_avg_pay,2)); //返回平均⼯资
6 exception
7 when no_data_found then //若此部门编号不存在
8 dbms_output.put_line('该部门编号不存在');
9 return(0); //返回平均⼯资为0
10 end;
11 /
函数已创建

2.调用函数

调⽤函数 get_avg_pay ，计算部门编号为 10 的雇员平均⼯资并输出，代码如下。

SQL> set serveroutput on
SQL> declare
2 avg_pay number; /定义变量，存储函数返回值
3 begin
4 avg_pay:=get_avg_pay(10); //调⽤函数，并获取返回值
5 dbms_output.put_line(' 平 均 ⼯ 资 是 ： '||avg_pay); //输出返回值，即员⼯平均⼯资
6 end;
7

3.删除函数

drop function fun_name;

五、包

程序包由 PL/SQL 程序元素（如变量、类型）和匿名 PL/SQL 块（如游标）、命名PL/SQL 块（如存储过程和函数）组成。程序包可以被整体加载到内存中，这样就可以⼤⼤加快程序包中任何⼀个组成部分的访问速度。实际上程序包对于⽤户来说并不陌⽣，在PL/SQL 程序使⽤DBMS_OUTPUT.PUT_ LINE 语句就是程序包的⼀个具体应⽤，其中，DBMS_OUTPUT 是程序包，⽽ PUT_LINE 就是其中的⼀个存储过程。程序包通常由规范和包主体组成，下⾯分别进⾏讲解。

1.程序包的规范

create [or replace ] package pack_name is
[declare_variable];
[declare_type];
[declare_cursor];
[declare_function];
[declare_ procedure];
end [pack_name];

CREATE OR REPLACE PACKAGE ClassPackage AS
--这个过程将一个新学生加入到某个班级里
PROCEDURE AddStudent(p_StudentID IN students.id%TYPE,
p_Department IN classes.department%TYPE, 
p_Course IN classes.course%TYPE);
-- 这个过程从某班级中删除一个学生
PROCEDURE RemoveStudent (…)；
-- 过程RemoveStudent抛出的异常
 e_StudentNotRegistered EXCEPTION;
-- 保存学生信息的表类型
 TYPE t_StudentIDTable IS TABLE OF students.id%TYPE
INDEX BY BINARY_INTEGER;
END ClassPackage;


SQL> create or replace package pack_emp is
2 function fun_avg_sal(num_deptno number) return number; //获取指定部门的平均⼯资
3 procedure pro_regulate_sal(var_job varchar2,num_proportion number); //按照指定⽐例上调指定职务的⼯资
 4 end pack_emp;
 5 /
程序包已创建

从上⾯的代码中可以看到，在 “ 规范 ” 中声明的函数和存储过程只有头部的声明，⽽没有函数体和存储过程主体，这正是规范的特点

2.程序包的主体

与创建 “ 规范 ” 不同的是，创建 “ 程序包主体 ” 使⽤ CREATE PACKAGE BODY语句，⽽不是 CREATE PACKAGE ，这⼀点需要读者注意，创建程序包主体的代码如下：

create [or replace] package body pack_name is
 [inner_variable]
 [cursor_body]
 [function_title]
 {begin
 fun_plsql;
 [exception]
 [dowith _ sentences;]
 end [fun_name]}
 [procedure_title]
 {begin
 pro_plsql;
 [exception]
 [dowith _ sentences;]
 end [pro_name]}
...
end [pack_name];

创建程序包 pack_emp 的主体，在该主体中实现对应“ 规范 ” 中声明的函数和存储过程，代码及运⾏结果如下

SQL> create or replace package body pack_emp is
 2 function fun_avg_sal(num_deptno number) return numberis //引⼊"规范"中的函数
 3 num_avg_sal number; //定义内部变量
 4 begin
 5 select avg(sal)
 6 into num_avg_sal
 7 from emp
 8 where deptno = num_deptno; //计算某个部门的平均⼯资
 9 return(num_avg_sal); //返回平均⼯资
 10 exception
 11 when no_data_found then //若未发现记录
 12 dbms_output.put_line('该部门编号不存在雇员记录');
 13 return 0; //返回0
 14 end fun_avg_sal;
 15
 16 procedure pro_regulate_sal(var_job varchar2,num_proportion number) is //引⼊"规范"中的存 
 储过程
 17 begin
 18 update emp
 19 set sal = sal*(1+num_proportion)
 20 where job = var_job; //为指定的职务调整⼯资
 21 end pro_regulate_sal;
 22 end pack_emp;
 23 /
程序包体已创建

调用包里的函数和存储过程

创建⼀个匿名的 PL/SQL 块，然后通过程序包 pack_emp 调⽤其中的函数 fun_avg_sal 和存储过程 pro_regulate_sal ，并输出函数的返回结果，代码及运⾏结果如下。

SQL> set serveroutput on
SQL> declare
2 num_deptno emp.deptno%type; //定义部门编号变量
3 var_job emp.job%type; //定义职务变量
4 num_avg_sal emp.sal%type; //定义⼯资变量
5 num_proportion number; //定义⼯资调整⽐例变量
6 begin
7 num_deptno:=10; //设置部门编号为10
8 num_avg_sal:=pack_emp.fun_avg_sal(num_deptno); //计算部门编号为10的平均⼯资
9 dbms_output.put_line(num_deptno||' 号 部 门 的 平 均 ⼯ 资是：'||num_avg_sal); //输出平均 ⼯资
10
11 var_job:='SALESMAN'; //设置职务名称
12 num_proportion:=0.1; //设置调整⽐例
13 pack_emp.pro_regulate_sal(var_job,num_proportion); //调整指定部门的⼯资
14 end;
15 /

例子：

CREATE OR REPLACE FUNCTION AlmostFull (
p_Department classes.department%TYPE,
p_Course classes.course%TYPE)
RETURN BOOLEAN IS
v_CurrentStudents NUMBER;
v_MaxStudents NUMBER;
v_ReturnValue BOOLEAN;
v_FullPercent CONSTANT NUMBER := 90;
BEGIN
SELECT current_students, max_students
INTO v_CurrentStudents, v_MaxStudents
FROM classes
WHERE department = p_Department AND course = p_Course;
IF (v_CurrentStudents / v_MaxStudents * 100) > v_FullPercent THEN
v_ReturnValue := TRUE;
ELSE
v_ReturnValue := FALSE;
END IF;
RETURN v_ReturnValue;
END AlmostFull;