数据库管理与PL/SQL编程

 数据库管理与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 ）表示要接收从控制台输入的变量。

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

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

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

提示：声明变量v_name保存员工姓名（以可以匹配任意类型的%type声明），v_sal保存员工工资（以可以匹配任意类型的%type声明），c_tax_rate为税率数字类型长度为3保留2为小数，v_tax_sal为所得税数字类型长度为7保留2为小数（所得税为工资*率）

 

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

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

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

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

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

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

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

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

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

（5）编写一个存储过程insert_proc，该过程可以向test_table表添加记录，执行该存储过程。

4、带参数存储过程

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

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

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

5、函数

（1）不带参数的函数

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

并调用此函数。

(2)带输入参数的函数

创建一个函数ret_minsal，以部门号为参数（p_deptno number），返回该部门最低工资，并调用该函数。

提示：参看书303页

6、利用简单循环向myproc用户的test_table表中插入10条记录

提示：循环语法

Loop

Sequence_of_statement;

Exit [when condition];

End loop;

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

注意在表名前加模式名myproc

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页）

 

打开数据库