SQL语句综合应用&PL/SQL编程

SQL语句综合应用

实验目的

1. 掌握Oracle中表、视图、索引、序列等基础知识。

2．掌握创建和使用表的不同SQL语句操作方法。

3. 掌握视图和索引的创建及使用方法。

4．掌握序列的创建和使用方法。

5. 掌握SQL连接查询的使用。

6．掌握SQL子查询的使用。

实验内容

1.创建一个表person，包括字段有姓名、性别、出生日期、工作和家庭地址。

create table person(
pername varchar2(20) not null,
gander char(2),
birthday date,
addr varchar2(40)
)
select * from person --查看表结构

2.为表person增加email列，观察是否增加了email列；然后删除email列。

alter table person add(email varchar2(20))--添加列
select* from person--查看表结构
alter table person drop column email --删除email列
select* from person--再次查看表结构

3.基于person表创建简单视图，并在姓名列创建索引。

--创建视图
create or replace view person_view 
as 
select * from person
--在pername列上创建索引
create index index_pername on person(pername)

4.为person表增加一个ID字段，创建序列并使用该序列为ID字段赋值。

添加字段

--添加id列
alter table person add(ID varchar2(20))
--查看表结构
select * from person

创建序列

--创建序列seq_person
create sequence seq_person
increment by 1--每次自增1
start with 1--初始值为一
minvalue 1 nomaxvalue--最小值为1，无上限
nocycle--不循环

更新person表数据

UPDATE person SET id = seq_person.NEXTVAL;
select * from person--查看表数据

5.已知Oracle的示例数据库（使用scott用户），完成下列查询：

--（1）列出所有员工的年薪，按年薪从低到高排序。

select emp.*,sal*12+nvl(comm,0) 年薪 from emp order by 年薪 asc

--（2）列出各个部门的mgr(经理)的最高薪金。

select deptno,max(sal) 最高薪资 from emp where job='MANAGER' group by deptno

--（3）列出所有部门的详细信息和部门人数。

SELECT d.*, COUNT(e.empno) AS 部门人数 
FROM dept d LEFT JOIN emp e ON d.deptno = e.deptno
GROUP BY d.deptno, d.dname, d.loc;

--（4）列出每个部门工作的员工数量及部门名称。

SELECT d.deptno, d.dname, COUNT(e.empno) AS num_empFROM dept d 
LEFT JOIN emp e ON d.deptno = e.deptno
GROUP BY d.deptno, d.dname; 

--（5） 列出没有任何员工的部门信息。

select * from dept d where not exists (select * from emp e where e.deptno=d.deptno)

--（6） 检索不在NEW YORK工作的员工信息。

select * from emp where deptno not in (select deptno from dept where loc='NEW YORK')

--（7）删除工作地点在NEW YORK的所有员工信息。

delete from emp where deptno in (select deptno from dept where loc='NEW YORK')
--查看表数据，对应不在纽约工作的员工信息是否删除
select * from emp

PL/SQL编程

实验目的

1．练习利用PLSQL Developer编写和管理存储过程、存储函数和触发器等

2．掌握存储过程、存储函数、触发器高级数据库对象的基本作用。

3．掌握存储过程、存储函数、触发器的建立、修改、查看、删除操作。

实验内容

1．创建一个显示雇员总人数的存储过程emp_count,并执行该存储过程

--创建存储过程
create or replace procedure emp_count 
as empno_count  number(4); --定义参数
begin
  select count(empno) into empno_count from emp;--给参数赋值
  dbms_output.put_line('雇员总人数：'||empno_count);
  end;
 --执行存储过程
call emp_count();

2．编写显示雇员信息的存储过程EMP_LIST，并引用EMP_COUNT存储过程

--创建存储过程
CREATE OR REPLACE PROCEDURE emp_list
IS
BEGIN
  emp_count;--调用存储过程
  FOR emp_rec IN (SELECT * FROM scott.emp)
  LOOP--循环输出
    DBMS_OUTPUT.PUT_LINE('员工: ' || emp_rec.ename || ',工作: ' || emp_rec.job);
  END LOOP;
END;
--调用存储过程
call emp_list();

3．创建函数，实现功能为：在scott.emp表和scott.dept表中查询出任意给定职工号的职工姓名及职工所在部门的名称。

--创建函数
CREATE OR REPLACE FUNCTION get_emp_dept(eno IN NUMBER) --in 类型的参数
RETURN VARCHAR2
IS 
    ename VARCHAR2(20);--定义变量
    dname VARCHAR2(20);
BEGIN
    SELECT e.ename, d.dname INTO ename, dname --通过查询语句为变量赋值
    FROM emp e JOIN dept d 
    ON e.deptno = d.deptno 
    WHERE e.empno = eno;
    
    RETURN ename || '在 ' || dname || '部门工作'; --输出员工姓名以及所在部门名
END;
--查询mepno为7788的员工姓名以及所在部门
select get_emp_dept(7788) from dual;

4．创建触发器，实现更新dept表中的deptno值，级联更新emp表中相应值。

--创建触发器，只要更改数据就会触发
CREATE OR REPLACE TRIGGER dept_change_deptno
AFTER UPDATE OF deptno ON dept 
FOR EACH ROW
BEGIN
    UPDATE emp 
    SET deptno = :NEW.deptno--更改deptno
    WHERE deptno = :OLD.deptno;
END;
--把20部门编号改成70部门
update dept set deptno=70 where deptno=20 
--查看部门表的信息，看看触发器是否触发成功
select *from dept;