English
资源说明：实验一 SQL*PLUS练习
【实验目的】
(1) 了解Oracle的工作环境和基本使用方法。
(2) 练习标准SQL的数据操作，查询命令及其查询优化。
(3) 学会使用高级SQL命令，排序、分组、自连接查询等。
(4) 学会使用SQL*PLUS命令显示报表，存储到文件等。
【实验内容】
一、 准备使用SQL*PLUS
1. 进入SQL*PLUS
2. 退出SQL*PLUS
3. 显示表结构命令DESCRIBE
SQL>DESCRIBE emp
使用DESCRIBE（缩写DESC）可以列出指定表的基本结构，包括各字段的字段名以及类型、长度、是否非空等信息。
4. 使用SQL*PLUS显示数据库中EMP表的内容
输入下面的查询语句：
SQL>SELECT * FROM emp;
按下回车键执行查询
5. 执行命令文件
START或@命令将指定文件调入SQL缓冲区中，并执行文件内容。
SQL>@ 文件名（文件后缀缺省为.SQL）或
SQL>START 文件名
文件中每条SQL语句顺序装入缓冲区并执行。
二、 数据库命令——有关表、视图等的操作
1. 创建表employee
例1 定义一个人事信息管理系统中存放职工基本信息的一张表。可输入如下命令：
SQL>CREATE TABLE employee
(empno number(6) PRIMARY KEY, /* 职工编号
name varchar2(10) NOT NULL, /* 姓名
deptno number(2) DEFAULT 10, /* 部门号
salary number(7,2) CHECK(salary<100000), /*工资
birth_date date, /*出生年月
soc_sec_num char(9) U NIQUE, /*内部序列号
foreign key(deptno) references dept(deptno));
例2 复制一个与emp表的表结构相同的新空表emp2.
可输入如下命令：
SQL>create table emp2 as
select * from emp
where 1=2;
在命令的where子句中给出1=2，表示条件不可能成立，因而只能复制表结构，而不能复制任何数据到新表中去。另外，还可以复制一个表的部分列定义或部分列定义及其数据。
三、 Oracle数据库数据查询
1、单表查询
2、多表查询
四、 SQL*PLUS常用命令
表1 常用报表格式化名命令
命令 定义
Btitle 为报表的每一页设置底端标题
Column 设置列的标题和格式
Compute 让SQL*PLUS计算各种值
Remark 将某些字标记为注释
Set linesize 设置报表的行宽字符数
Set newpage 设置报表各页之间的行数
Spool 使SQL*PLUS将输出写入文件中
Start 使SQL*PLUS执行一个sql文件
Ttitle 设置报表每页的头标题
Break 让SQL*PLUS进行分组操作
例3 建立一个批命令文件对查询到的数据以报表的形式输出并将其保存到指定的文件中。
处理方法：利用SQL*PLUS语言工具（也可以使用其他文本编辑器）建立批命令的.SQL文件。在“SQL>”提示符下，使用EDIT命令在”E:\”中建立SCGB.SQL文件。
SCGB.SQL文件中的命令组如下：
SQL>EDIT E:\ SCGB.SQL
SET echo off
SET pagesize 30
SET linesize 75
TTITLE’2008年4月10号’CE’公司职员基本情况登记表’R’Page:’ FORMAT 99-
>SQL.PNO SKIP 1 CE’===========================’
BTITLE COL 60 ’制标单位’ TAB 3 ‘人事部’
COLUMN empno heading ‘职工|编号’
COLUMN ename format a10 heading ‘姓 名’
COLUMN job heading ‘工 种’
COLUMN sal format $99,990 heading 工 资’
COLUMN comm Like sal heading ‘奖 金’
COLUMN deptno format 9999 heading ‘部门|编号’
COLUMN hiredate heading ‘参加工作时间’
SPOOL e:\sjbb /*在E盘中建立格式报表输出文件，默认属性为LST BREAK on deptno skip 1
COMPUTE sum of sal comm on deptno
SELECT empno,ename,job,hiredate,sal,comm,deptno from emp
ORDER BY deptno,sal;
SPOOL off /*终止SPOOL功能，关闭其文件。注意，此命令不可省，否则将建立空文件。
五、 实验内容
1、以cs+学号为用户名创建用户，并授予用户创建数据对象的权限。
2、复制emp表，复制表名为emp_学号，然后将emp表中工资低于$2000
的职工插入到复制的表中。
3、对复制的emp表插入一行只包含有职工号,职工名,工资与部门号四个数据
项值的记录。
4、在复制的emp表中将雇员ALLEN提升为经理，工资增至＄2500，
奖（佣 ）金增加40%。
5、删除复制的emp表中工资低于500的记录行。
6、列出10号部门中既不是经理，也不是秘书的职工的所有信息。
7、查找出部门所在地是CHICAGO的部门的职工姓名、工资和工种。
8、统计各部门中各工种的人数、工资总和及奖金总和。
9、查找出工资比其所在部门平均工资高的职工姓名、工种与工资情况。
实验3 Oracle数据库开发环境下PL/SQL编程
【实验目的】
(1)掌握 PL/SQL 的基本使用方法。
(2)在SQL*PLUS环境下运行PL/SQL的简单程序。
(3)应用 PL/SQL 解决实际问题
【实验内容与步骤】
PL/SQL块中的可执行部分是由一系列语句组成的（包括对数据库进行操作的SQL语句，PL/SQL语言的各种流程控制语句等）。在块中对数据库查询，增、删、改等对数据的操作是由SQL命令完成的。在PL／SQL块中，可以使用SQL的数据查询命令，数据操纵命令和事务控制命令。可使用全部SQL函数。PL／SQL中的SQL语句，可使用SQL的比较操作等运算符。但不能使用数据定义语句。
在PL／SQL块中使用SELECT语句时注意几点：
（1）SELECT语句必须含有INTO子句。
（2）INTO子句后的变量个数和位置及数据类型必须和SELECT命令后的字段名表相同。
（3）INTO子句后可以是简单类型变量或组合类型变量。
（4）SELECT语句中的WHERE条件可以包含PL/SQL块中定义的变量及表达式，但变量名不要同数据库表列名相同。
（5）在未使用显式游标的情况下，使用SELECT语句必须保证只有一条记录返回，否则会产生异常情况。
[例3-1] 问题：编写一个过程，求和运算。
SET SERVEROUTPUT ON;
DECLARE
a number:=1;
BEGIN
a:=a+5;
DBMS_OUTPUT.PUT_LINE('和为：'||TO_CHAR(a));
END;
/
【例3-2】：使用%TYPE声明变量，输出制定表中的相关信息。
DECLARE
my_name student.sname%TYPE;
BEGIN
SELECT sname INTO my_name FROM student
WHERE no=’01203001’;
DBMS_OUTPUT.PUT_LINE(my_name);
END;
/
【例3-3】问题：编写一个过程，可以输入一个雇员名，如果该雇员的工资低于2000，就给该员工工资增加10%。
declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp where ename=spName;
if v_sal<2000 then
update emp set sal=sal+sal*10% where ename=spName;
end if;
end;
/
实验内容：
1、用PL/SQL实现：输入empno的值，显示emp表中对应记录的内容。
2、用PL/SQL完成：读入三个数，计算并输出它们的平均值及三个数的乘积。
3、对职工表emp中的雇员SCOTT提高奖金，若工种为MANAGER,则奖金提高其原来的20%;若工种为SALESMAN,则奖金提高其原来的15%;若工种为ANALYST,则奖金提高其原来的10%，其它都按原来的7%提高。
4、用PL/SQL块实现下列操作
公司为每个职工增加奖金：若职工属于30号部门，则增加＄150；若职工属于20号部门，则增加＄250；若职工属于10号部门，则增加＄350。
实验四 PL/SQL存储过程 （2学时）
【实验目的】
(1)了解存储过程的使用方法。
(2)掌握存储过程的使用方法。
【实验要求】
(1)按照业务要求创建实现特定功能的存储过程。
(2)按照业务内容创建实现具有特定功能的函数。
【实验内容】
1、 写存储过程，显示所指定雇员名所在的部门名和位置。
CREATE OR REPLACE PROCEDURE DeptMesg(pename emp.ename%TYPE,
pdname OUT dept.dname%TYPE,ploc OUT dept.loc%TYPE) AS
BEGIN
SELECT dname,loc INTO pdname,ploc
FROM emp,dept
WHERE emp.deptno=dept.deptno AND emp.ename=pename;
END;
/
VARIABLE vdname VARCHAR2(14);
VARIABLE vloc VARCHAR2(13);
EXECUTE DeptMesg('SMITH',:vdname，:vloc);
PRINT vdname vloc;
2、 定义一个为修改职工表（emp）中某职工工资的存储过程子程序，职工名作为形参,若该职工名在职工表中查找不到，就在屏幕上提示“查无此人”然后结束子程序的执行；否则若工种为MANAGER的，则工资加＄1000；工种为SALESMAN，工资加＄500；工种为ANALYST，工资加＄200，否则工资加＄100。
create or replace procedure xggz(name varchar2) is
k_job emp.job%type;
addsal emp.sal%type;
begin
select job into k_job from emp where ename=name;
if k_job=’MANAGER’ then
addsal:=1000;
elsif k_job=’SALESMAN’ then
addsal:=500;
elsif k_job=’ANALYST’ then
addsal:=200;
else
addsal:=100;
end if;
update emp set sal=sal+addsal where ename=name;
exception
when no_data_found then
dbms_output.put_line(‘查无此人”);
end;
3、 通过dept表查询出所有部门号,对每个部门雇员的工资进行调整，将工资高于（包含$2000）$2000的雇员每人增加$500,将工资低于$2000的雇员每人增加到$2000。但应注意雇员工资调整后不应大于$10000,否则显示出错信息,并退出程序。并统计显示各部门人数及工资调整后的总和。
4、 以bs123456（bs学号）用户登录orcl数据库，利用PL/SQL编写程序实现下列功能模块。
1)、创建一个函数，以客户号微参数，返回该客户订购图书的价格总额。
2）、创建一个函数，以订单号为参数，返回该订单购图书的价格总额。
3)、创建一个函数，已出版社名为参数，返回该出版社出版的图书的平均价格。
4)、创建一个函数，以客户号为参数，返回该客户可以获得的礼品名称。
5)、创建一个函数，以图书号为参数，统计该图书被订购的总数量。
湖南文理学院实验报告
课程名称 大型数据库技术 实验名称 五、PL/SQL触发器 成绩
学生姓名 专 业 年级、学号
同组者姓名 实验日期
一、实验目的：
1． 了解触发器的类型。
2． 掌握PL/SQL触发器的使用方法。
二、实验要求：
1. 掌握SQL Developer编译功能，使用SQL Developer完成PL/SQL命令。
2． 按照业务要求创建实现特定功能的触发器。
三、实验步骤：
1． 打开SQLDeveloper，建立system链接、bs+学号连接登录ORCL数据库。
2． 在SQLDeveloper中，用密码tiger解锁scott账号。
3． 触发器注意事项
（1）触发器可以声明为在对记录进行操作之前，在之前(检查约束之前和 INSERT，UPDATE 或 DELETE 执行前)或之后(在检查约束之后和完成 INSERT， UPDATE 或 DELETE 操作)触发.。
（2）一个 FOR EACH ROW 执行指定操作的触发器为操作修改的每一行都调用一次。
（3）SELECT 并不更改任何行，因此不能创建 SELECT 触发器。这种场合下规则和视图更适合。
（4）触发器和某一指定的表格有关，当该表格被删除时，任何与该表有关的触发器同样会被删除。
（5）在一个表上的每一个动作只能有一个触发器与之关联。
（6）在一个单独的表上，最多只能创建三个触发器与之关联，一个INSERT触发器，一个DELETE触发器和一个UPDATE触发器。
1. 实例讲解Oracle数据库自带的几个触发器
Oracle数据库自带的几个触发器（最简单触发器格式）示例如下：
--
create or replace trigger MDSYS.sdo_drop_user
after drop on DATABASE
declare
stmt varchar2(200);
BEGIN
if dictionary_obj_type = 'USER' THEN
stmt := 'DELETE FROM SDO_GEOM_METADATA_TABLE ' ||
' WHERE SDO_OWNER = ''' || dictionary_obj_name || ''' ';
EXECUTE IMMEDIATE stmt;
end if;
end;
---
create or replace trigger SYS.aurora$server$startup after startup on database
call dbms_java.server_startup
--
create or replace trigger SYS.JIS$ROLE_TRIGGER$ after drop on database
when (ora_dict_obj_type='ROLE')
begin
sns_context.role_dropped(ora_dict_obj_name);
http_security_cascade.principal_dropped(ora_dict_obj_name);
end;
--删除前备份数据的器
Create Or Replace Trigger YSPJ.T_Bill_reMain_Del
Before delete On bill_remain
FOR EACH ROW
Begin
Insert into BILL_REMAIN_TIGER
Values(:old.BILL_REMAINID,:old.BILL_TYPEID,:old.REMAIN_NUM,:old.ADD_TIME,:old.ORG_ID,:old.STATE,:old.BILL_ID,'删除记录',Sysdate,user);
End;
四、实验内容：
1、 验证触发器
创建触发器，当用户对test表执行DML语句时，将相关信息记录到日志表。
--创建测试表
CREATE TABLE test
(
t_id NUMBER(4),
t_name VARCHAR2(20),
t_age NUMBER(2),
t_sex CHAR
);
--创建记录测试表
CREATE TABLE test_log
(
l_user VARCHAR2(15),
l_type VARCHAR2(15),
l_date VARCHAR2(30)
);
--创建触发器
CREATE OR REPLACE TRIGGER test_trigger
AFTER DELETE OR INSERT OR UPDATE ON test
DECLARE
v_type test_log.l_type%TYPE;
BEGIN
IF INSERTING THEN --INSERT触发
v_type := 'INSERT';
DBMS_OUTPUT.PUT_LINE('记录已经成功插入，并已记录到日志');
ELSIF UPDATING THEN --UPDATE触发
v_type := 'UPDATE';
DBMS_OUTPUT.PUT_LINE('记录已经成功更新，并已记录到日志');
ELSIF DELETING THEN
v_type := 'DELETE';
DBMS_OUTPUT.PUT_LINE('记录已经成功删除，并已记录到日志');
END IF;
INSERT INTO test_log VALUES(user,v_type,
TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss'));
END;
/
--下面分别执行DML语句
INSERT INTO test VALUES(101,'zhao',22,'M');
UPDATE test SET t_age = 30 WHERE t_id = 101;
DELETE test WHERE t_id = 101;
--然后查看效果
SELECT * FROM test;
SELECT * FROM test_log;
3、创建触发器，它将映射emp表中每个部门的总人数和总工资。
--创建映射表
CREATE TABLE dept_sal
AS
SELECT deptno,COUNT(empno) AS total_emp,SUM(sal) AS total_sal FROM emp GROUP BY deptno;
DESC dept_sal;
--创建触发器
CREATE OR REPLACE TRIGGER emp_info
AFTER INSERT OR UPDATE OR DELETE ON emp
DECLARE
CURSOR cur_emp IS
SELECT deptno,COUNT(empno) AS total_emp,SUM(sal) AS total_sal FROM emp GROUP BY deptno;
BEGIN
DELETE dept_sal; --触发时首先删除映射表信息
FOR v_emp IN cur_emp LOOP
--DBMS_OUTPUT.PUT_LINE(v_emp.deptno || v_emp.total_emp || v_emp.total_sal);
--插入数据
INSERT INTO dept_sal
VALUES(v_emp.deptno,v_emp.total_emp,v_emp.total_sal);
END LOOP;
END;
/
--对emp表进行DML操作
INSERT INTO emp(empno,deptno,sal) VALUES('123','10',10000);
SELECT * FROM dept_sal;
DELETE EMP WHERE empno=123;
SELECT * FROM dept_sal;
4、创建触发器，它记录表的删除数据
--创建表
CREATE TABLE employee
(
id VARCHAR2(4) NOT NULL,
name VARCHAR2(15) NOT NULL,
age NUMBER(2) NOT NULL,
sex CHAR NOT NULL
);
DESC employee;
--插入数据
INSERT INTO employee VALUES('e101','zhao',23,'M');
INSERT INTO employee VALUES('e102','jian',21,'F');
--创建记录表
CREATE TABLE old_employee AS
SELECT * FROM employee;
DESC old_employee;
--创建触发器
CREATE OR REPLACE TRIGGER tig_old_emp
AFTER DELETE ON employee --
FOR EACH ROW --语句级触发，即每一行触发一次
BEGIN
INSERT INTO old_employee
VALUES(:old.id,:old.name,:old.age,:old.sex); --:old代表旧值
END;
/
--进行测试
DELETE employee;
SELECT * FROM old_employee;
5、创建触发器，比较emp表中更新的工资。
CREATE OR REPLACE TRIGGER sal_emp
BEFORE UPDATE ON emp
FOR EACH ROW
BEGIN
IF :OLD.sal > :NEW.sal THEN
DBMS_OUTPUT.PUT_LINE('工资减少');
ELSIF :OLD.sal < :NEW.sal THEN
DBMS_OUTPUT.PUT_LINE('工资增加');
ELSE
DBMS_OUTPUT.PUT_LINE('工资未作任何变动');
END IF;
DBMS_OUTPUT.PUT_LINE('更新前工资 ：' || :OLD.sal);
DBMS_OUTPUT.PUT_LINE('更新后工资 ：' || :NEW.sal);
END;
/
--执行UPDATE查看效果
UPDATE emp SET sal = 3000 WHERE empno = '7788';
6、需要对在表上进行DML操作的用户进行安全检查，看是否具有合适的特权。
Create table foo(a number);
Create trigger biud_foo
Before insert or update or delete
On foo
Begin
If user not in (‘DONNY’) then
Raise_application_error(-20001, ‘You don’t have access to modify this table.’);
End if;
End;
/
即使SYS，SYSTEM用户也不能修改foo表。
2、 利用PL/SQL编写程序实现下列触发器
1)、编写一个数据库触发器，当任何时候某个部门从dept表中删除时，该触发器将从emp表中删除该部门的所有雇员。（要求：emp表、dept表均为复制后的表）
2）、创建一个触发器，当客户下完订单后，自动统计该订单的所有图书的价格总额。
3)、创建一个触发器，禁止客户在非工作时间（早上8:00前，晚上17:00后）下订单。
五、实验心得
本源码包内暂不包含可直接显示的源代码文件，请下载源码包。
