数据库管理与PL/SQL编程

￡神魔★判官ぃ 2022-08-10 04:51 123阅读 0赞

数据库管理与PL/SQL编程

**实验要求和目的**

**掌握用户和权限管理，掌握PL/SQL块语句、存储过程及函数的运用，了解数据库备份与恢复。**

**注意此次实验需提交到实验平台（平台实验项目编号4中）。**

**实验内容：**

**PL/SQL基础语法提示：**

1）合法字符

用PL/SQL程序时，允许使用下列字符：

所有大、小写字母

数字0到9

符号：（）+-\*/〈 〉=！~；：.‘@ %，“\# $ ^ & \_ | \{ \} ？ \[ \]

2）变量

变量是PL/SQL中用来处理数据项所用的名字。读者根据下列规则选择变量名称：

变量必须以字母（A~Z）开头。

其后跟可选的一个或多个字母，数字（0~9）或特殊字符$、\# 或\_。

变量长度不超过30个字符。

变量名中不能有空格。

3）基本知识：一个完整的PL/SQL语句块由3个部分组成：

Declare

声明部分，定义变量、常量数据、游标、异常、局部子程序等

Begin

执行部分，实现块的功能

Exception

异常处理部分，处理程序执行过程中产生的异常

End；

4)存储过程:

CREATE \[OR REPLACE\] PROCUDURE procedure\_name  
(arg1 \[model1\] datatype1, arg2\[model2\] datatype2)  
IS \[AS\]  
PL/SQL Block;  
arg1,arg2用于指定过程的参数,IS/AS用于开始一个PL/SQL块.当指定参数数据类型时,不能指定其长度.  
在建立过程的时间,既可以指定输入参数(IN),也可以指定输出参数(OUT)以及输入输出参数(IN OUT).如果不定义参数模式,则默认为输入参数,如果要定义输出参数,则需要指定OUT关键字,如果定义输入输出参数,要指定IN OUT关键字.一般使用execute(或exec) 过程名 或者 call 过程名来调用过程.  
set serveroutput on打开oracle的输出.  
5)函数

CREATE \[OR REPLACE\] FUNCTION function\_name  
(arg1 \[model1\] datatype1,arg2 \[model2\] datatype2)  
RETURN datatype  
IS|AS  
PL/SQL Block;  
arg1,arg2指定函数的参数,当不指定参数数据类型时,不能指定其长度  
RETURN 指定函数返回的数据类型.注意,函数前面必须要有RETURN子句.在函数体内至少含有一条RETURN子句.函数的参数MODEl和过程的一样

** **

**必做题**

1、PL/SQL语句块

（1）包含声明部分、执行部分的pl/sql块：由控制台（即屏幕输入工号）输入员工工号（empno字段），获得此员工的姓名（ename字段）及工资（sal字段）并将姓名保存到变量v\_name中将工资保持到变量v\_sal中，使用dbms\_output.put\_line(…)在屏幕上显示输入工号对应的员工姓名及工资

提示：先需声明变量v\_name，变量类型和大小可以查看emp结构获得enam字段的数据类型和大小，在查询条件中使用**地址符&（**empno=&no** ）表示要接收从控制台输入的变量**。

![1.1_2874_29.png][]

（2）包含定义部分、执行部分和例外处理部分的pl/sql块：将前面的pl/sql块完善，如果输入了不存在的员工工号，就会出现异常，应当做例外处理，提示：您输入的工号有误！。

提示：ORACLE事先预定义了一些例外，no\_data\_found就是找不到数据的例外；异常处理部分以关键子exception开始，以end结束。（参考书263页）

![1.2_2842_29.png][]

（3）输入员工号，显示雇员姓名，工资，所得税（税率为0.03，申明此变量使赋初值为0.03）

提示：声明变量v\_name保存员工姓名（以可以匹配任意类型的%type声明），v\_sal保存员工工资（以可以匹配任意类型的%type声明），c\_tax\_rate为税率数字类型长度为3保留2为小数，v\_tax\_sal为所得税数字类型长度为7保留2为小数（所得税为工资\*率）

![1.3_2839_29.png][]

3、用户管理综合练习与简单存储过程（无参数）：

（1）建立用户myproc，口令myproc，默认表空间为users

提示：create user 用户名 identified by 口令 default tablespace 表空间名

![3.1_2889_29.png][]

（2）给用户myproc 赋予角色：connect ；赋予系统权限：create table、create procedure

提示：grant 系统权限或角色 to 用户

![3.2_2859_29.png][]

（3）使myproc用户有users表空间上50m的使用权限

提示：alter user 用户名 quota 匹额大小 on 表空间

![3.3_2833_29.png][]

（4）切换到myproc用户，在该用户下创建test\_table表，test\_table表包含2个字段：tnum 字段，数字类型；tinfo字段，定长字符类型，长度为10个字符

提示：create table 表名 (列名 数据类型，…)

![3.4_2820_29.png][]

（5）编写一个存储过程insert\_proc，该过程可以向test\_table表添加记录，执行该存储过程。

![3.5_2812_29.png][]

4、带参数存储过程

（1）编写一个过程update\_proc，可以修改雇员的工资，此过程带2个输入参数：spname varchar2 ，newsal  number，通过spname参数来传递ename字段的值，newsal参数来传递sal字段的值，执行过程时输入实参雇员名SMITH(ename字段)与新工资(sal字段)1200，可修改雇员SMITH的工资为1200。

提示：如果不指定参数模式,则默认的为

IN,也可以显示的指定输入模式IN，此过程雇员名与新工资都为输入参数。注意：定义形参时不能定义它的长度或精度、刻度。

![4_28923_29.png][]

5、函数

（1）不带参数的函数

创建一个函数get\_users，通过查询user\_users表获得当前用户的用户名（username字段），

并调用此函数。

![5.1_2836_29.png][]  
(2)带输入参数的函数

创建一个函数ret\_minsal，以部门号为参数（p\_deptno number），返回该部门最低工资，并调用该函数。

提示：参看书303页

![5.2_2823_29.png][]  
6、利用简单循环向myproc用户的test\_table表中插入10条记录

提示：循环语法

Loop

Sequence\_of\_statement;

Exit \[when condition\];

End loop;

可以创建存储过程insert\_dataproc实现或pl/sql块语句实现，参看书（278页）

注意在表名前加模式名myproc  
![6.1_2835_29.png][]

7、游标

当在PL/SQL块中执行查询语句和数据操纵语句时，ORACLE会在内存中分配一个缓冲区，缓冲区中包含了处理过程的必须信息和查询语句返回的数据集，为了处理SELECT语句返回多行数据的情况，ORACLE提供了游标来处理。

游标的操作步骤：

(1)定义游标：cursor cursor\_name is select \_statement;

(2)打开游标：open cursor\_name;

(3)检索游标：fetch cursor\_name into variable\_list|record\_variable；

(4)关闭游标：close cursor\_name;

根据输入的部门号查询出该部门所有员工姓名和他的工资，部门号在程序运行时指定。

提示：由于某个部门的人数是不定的，可能由多个，因此需要采用游标来处理（参看书281页）

![7.1_2821_29.png][]

打开数据库

[1.1_2874_29.png]: http://218.196.43.18:8888/userfiles/1.1%2874%29.png
[1.2_2842_29.png]: http://218.196.43.18:8888/userfiles/1.2%2842%29.png
[1.3_2839_29.png]: http://218.196.43.18:8888/userfiles/1.3%2839%29.png
[3.1_2889_29.png]: http://218.196.43.18:8888/userfiles/3.1%2889%29.png
[3.2_2859_29.png]: http://218.196.43.18:8888/userfiles/3.2%2859%29.png
[3.3_2833_29.png]: http://218.196.43.18:8888/userfiles/3.3%2833%29.png
[3.4_2820_29.png]: http://218.196.43.18:8888/userfiles/3.4%2820%29.png
[3.5_2812_29.png]: http://218.196.43.18:8888/userfiles/3.5%2812%29.png
[4_28923_29.png]: http://218.196.43.18:8888/userfiles/4%28923%29.png
[5.1_2836_29.png]: http://218.196.43.18:8888/userfiles/5.1%2836%29.png
[5.2_2823_29.png]: http://218.196.43.18:8888/userfiles/5.2%2823%29.png
[6.1_2835_29.png]: http://218.196.43.18:8888/userfiles/6.1%2835%29.png
[7.1_2821_29.png]: http://218.196.43.18:8888/userfiles/7.1%2821%29.png