应付期末考试，这个就够了！Oracle数据库、PLSQL编程

第 01 章 数据库设计

数据库原理知识

1.数据库(Database,简称DB)是长期储存在计 算机内、有组织的、可共享的大量数据集合

2.数据库管理系统（Database Management System，简称DBMS）是位于用户与操作系统之间的一层数据管理软件。

3.数据库系统（Database System，简称DBS）是指在计算机系统中引入数据库后的系统构成。

逻辑设计

1对1
方案2：“负责”与“职工”两关系合并：
职工（职工号，姓名，年龄，产品号）
产品（产品号，产品名，价格）
1对n
方案2：联系形成的关系与n端对象合并
 仓库（仓库号，地点，面积）；
 产品（产品号，产品名，价格，仓库号，数量）
m对n
学生（学号，姓名，年龄，性别）
课程（课程号，课程名，学时数）
选修（学号，课程号，成绩）

概念设计

E-R图三种基本元素

实体、属性、联系

E-R图的基本思想

分别用矩形框、椭圆形框和菱形框表示实体 、属性和联系。

使用无向边将属性与其相应的实体连接起来。

将联系分别和有关实体相连接，注明联系类型。

第 02 章 Oracle 入门

SQL*PLUS 的常用命令（连接命令、解锁、修改密码、帮助命令、查看表结构、交互式命令）

(1)HELP命令
功能：查看某个命令的使用方法
格式：HELP [topic]
参数topic表示将要查询的命令名称。
例如，想查看SHUTDOWN命令的使用方式：
 SQL> HELP shutdown
可以使用 HELP index 命令来查看SQL*PLUS所有命令清单
(2)CONNECT
功能：以某个用户身份连接到某个数据库
格式: 
CONN[ECT] 用户名/密码@网络服务名 [as sysdba/sysoper]
当用特权用户身份连接时，语句末尾必须带上
 as sysdba 或是 as sysoper
例如：
SQL> CONN scott/tiger@orcl
(3)DISCONNECT
功能：用来断开与当前数据库的连接
格式: DISC[ONNECT]
例如：
 SQL> DISC
(4)PASSWORD
功能1：该命令用于修改当前用户的密码。
 格式1: PASSW[ORD]
例如：
 SQL> PASSW 
功能2：该命令用于修改某个用户的密码。
格式2: PASSW[ORD] 用户名
例如：
 SQL> PASSW scott
注意，如果要想修改其它用户的密码，需要用sys或者system登陆。
(6)DESCRIBE命令
功能：用来查看数据库对象的结构 
格式: DESC[RIBE] 对象名 
例如，查看scott用户中的emp表的结构：
 SQL> DESC scott.emp
 
SQL> GET d:\b.sql 
 --把d:\b.sql 文件内容加载到当前缓冲区中

ORACLE 体系结构（ORACLE 服务器，ORACLE 实例，ORACLE 数据库，内存结构，初始化参数文件）

体系结构：Oracle服务器，关键文件，用户进程，服务器进程

ORACLE 服务器=实例+服务器

ORACLE 实例：一组oracle后台进程/线程以及在服务器分配的共享内存区

实例=共享内存结构（SGA）+后台进程

ORACLE 数据库：一系列物理文件的集合。内存结构（表空间>段>盘区>数据块）

datafiles redo log files control files

数据文件 重做日志文件 控制文件

*.dbf *.log *.ctl

可以在数据库运行期间利用ALTER SYSTEM来修改初始化参数，并且修改后立即生效， 但修改并不保存在初始化参数文件中。

管理 Oracle 数据库（启动和关闭数据库与实例，数据库的特殊状态）

打开数据库和实例

startup [nomount | mount | open | force] [restrict] [pfile=filename]

nomount：启动实例，访问初始化参数文件，分配SGA区，创建实例进程；没有打开控制文件、数据文件、重做日志文件，无法访问数据库。

mount：启动实例并装载数据库，创建实例进程，打开控制文件；但没打开数据文件和重做日志文件。

open：启动例程、装载并打开数据库，默认选项

force：强制关闭数据库并重新启动数据库

restrict：以受限会话方式启动数据库，只允许具有restricted session权限的用户访问数 据库。

pfile：用于指定启动实例时所使用的文本参数文件。

关闭数据库和实例

shutdown [normal | transactional | immediate | abort]

正常方式(NORMAL)

立即方式(IMMEDIATE)

事务处理方式(TRANSACTIONAL)

中止方式(ABORT)

静默状态和挂起状态是两种特殊的数据库状态。  
当数据库处理静默状态时，只有SYS和SYSTEM用户能够在数据库中进行操作。  
当数据库处于挂起状态时，数据库I/O操作都会 被暂时停止。  
利用这两种数据库状态，数据库管理员可以完成 一些特殊的管理和维护操作。

第 03 章 SQL 语句基础（增、删、改、查）

select

select * from emp where deptno=46;

SELECT ename, sal
FROM emp
WHERE sal BETWEEN 1000 AND 1500;--工资在1000至1500之间
SELECT empno, ename, sal, mgr
FROM emp
WHEREmgr IN (7902, 7566, 7788);--在这三个之间

在雇员表中，求部门的雇员数超过5个人的部门号和部门人数。
select deptno,count(*)
from emp
group by deptno
having count(*)>5;

ORDER BY 子句出现在SELECT语句的最后。
例如： SELECT ename, job, deptno, hiredate
FROM emp
ORDER BY hiredate;

SQL> col sal for $999,999.99;
SQL> select sal from emp where empno=46;
         SAL
------------
  $10,000.00

insert

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (9000, 'GREEN', 'SALESMAN', 7782, sysdate, 2000, null, 10);
--插入多行
insert into salesman (id, name, job, hiredate)
	select empno, ename, job, hiredate
	from emp
	where job = 'SALESMAN';

Update

update emp
set sal=8000
where ename='Anne';
--更新多列
update emp
set (job, deptno) = (select job, deptno
                     from emp
                     where empno = 9000)
where empno = 7369;                    

delete

delete from dept
where deptno = 50;

第 04 章 数据库中的事务

事务特点

数据库事务的基本属性（ACID） 
原子性（Atomicity）： 事务要么完全成功，要么就完全失败。
一致性（Consistency）： 数据库总会保持一致性的状态，不存在局部事务。
隔离性（Isolation）： 在事务提交以前，由该事务所做的更改，只对做这种更改的当前会话可见。 
持久性（Durability）： 事务完成以后，就不能被取消。

事务的命令（commit,rollback,savepoint）

显式事务处理:
COMMIT：数据库事务提交，将变化写入数据库。
ROLLBACK：数据库事务回滚，撤销对数据的修改。
SAVEPOINT ：创建保存点，用于事务的阶段回滚。
--SAVEPOINT 保存点名称; 
--ROLLBACK to 保存点名称;

事务的原理（事务处理前后数据的特点）

对于每个事务，在这个事务提交前事务中所做
的任何改变都是临时的，即可以取消的（回滚到原始的状态），此时数据具有如下特征: 
数据可以回滚到修改之前的原始状态。 
当前用户可以使用SELECT语句查操作后的结果。
其他用户不能查看这个DML语句所做的修改。 
受影响的数据行加上行一级的排它锁，其他用户不能更改受影响行中的数据。

第 05 章 PLSQL 基础

标识符、变量、常量、替代变量、绑定变量

【标识符的命名规范】：
变量必须以字母开头，长度不超过3 0个字符。 
其后跟可选的一个或多个字母、数字( 0 ~ 9 )或特殊 字符$、# 或_。 
不区分大小写。 
不能与程序中引用的字段名相重，若相重，会被当作列名来试用。

emp_name emp.ename%TYPE;
--行记录与使用
v_dept dept%ROWTYPE 
v_dept.deptno
--绑定变量，引用绑定变量前面要加上“：”
SQL>VARIABLE g_name varchar2(50);
SQL>BEGIN
 :g_name:=:g_name||’Hello~~’ ;
 dbms_output.put_line(:g_name);
END;
--替代变量
 &变量名
 job ='&job_title' ;

控制语句（选择、循环）

SQL> set serveroutput on;
  
declare
 v_num number := &a;
begin
 if mod(v_num, 2) = 0 then
 dbms_output.put_line(v_num || '是偶数.');
 else
 dbms_output.put_line(v_num || '是奇数.');
 end if;
end;

declare
 x number := &x;
 y number;
begin
 if x > 0 then
 y := 3 * x + 2;
 elsif x=0 then
 y := 0;
 else
 y := 3 * x - 2;
 end if;
 dbms_output.put_line('y = ' || y);
end;

Case <selector>
When   <expression 1> then plsql_sentences_1;
When   <expression 2> then plsql_sentences_2;
When   <expression 3> then plsql_sentences_3;
When   <expression 4> then plsql_sentences_4;
End case;

举例： 用loop函数求前100个自然数的和，输出到屏幕上去。
Declare 
   Sum_i  int:=0;
     i  int:=0;
Begin
 Loop
   i：=i+1；
   Sum_i=Sum_i+i;
   Exit when i=100;
  End loop;
 Dbms_output.putline(‘’);
 End;

Declare 
   Sum_i int:=0;
   I int:=0;
Begin
 While i<=99 loop
   i=i+1;
   Sum_i=Sum_i+i;
End loop;
Dbms_output.putline();
End;
举例：前100个自然数中偶数之和
Declare
Sum_i :int=0;
Begin
 For i in reverse 1..100 loop
 If mod(i,2) =0 then
Sum_i=Sum_i+i;
End if;
End loop;
Dbms_output.putline();
End

游标（游标的四步骤处理（游标定义，打开游标，获取数据，关闭游标）、游标 for 语句）

SQL> set serveroutput on;
SQL> declare
  var_ename varchar2(50);
  var_job varchar2(50);
 cursor cur_emp				--创建游标
  is select ename,job
  from emp
  where empno=7499;
begin
  open cur_emp				--打开游标
  fetch cur_emp into var_ename,var_job;		--获取数据
 if cur_emp%found then
  dbms（‘’||   ||’, ’||）;
 else
  dbms（‘’）;
 end if;
 close cur_emp;				--关闭游标
end;

--检索出前10个员工的编号和姓名
declare
cursor emp_cursor is select empno,ename from emp ;
row_emp emp_cursor%ROWTYPE;
begin 
open emp_cursor ;
for i in 1..10 loop
fetch emp_cursor into row_emp;
dbms_output.put_line(row_emp.empno||'  '||row_emp.ename);
end loop;
close emp_cursor;
end;

异常（异常分类、异常处理、异常传播原理）

DECLARE
v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;
BEGIN
select ename,sal into v_ename, v_sal
 from emp
 where empno= 7369;
DBMS_OUTPUT.PUT_LINE('雇员名称：'|| v_ename);
DBMS_OUTPUT.PUT_LINE('雇员工资：'|| v_sal);
EXCEPTION
WHEN NO_DATA_FOUND THEN
 DBMS_OUTPUT.PUT_LINE('NO_DATA');
WHEN TOO_MANY_ROWS THEN
 DBMS_OUTPUT.PUT_LINE('TOO_MANY');
WHEN OTHERS THEN
 DBMS_OUTPUT.PUT_LINE(' OTHERS ');
END;

DECLARE
 e_emps EXCEPTION;
 PRAGMA EXCEPTION_INIT ( e_emps, -2292);
 v_deptno dept.deptno%TYPE := 30;
BEGIN
 DELETE FROM dept
 WHERE deptno = v_deptno;
 COMMIT;
EXCEPTION
 WHEN e_emps THEN
 DBMS_OUTPUT.PUT_LINE('有表引用本表的部门号
'||v_deptno) ;
END;

第 06 章 存储过程、函数、触发器

过程定义、调用

创建过程P_DEPT，实现功能：输出各部门的 部门名称
create or replace procedure p_dept
as
 cursor cur_dept is
 select dname from dept;
begin
 for v_dept in cur_dept loop
 dbms_output.put_line('部门名称：' || v_dept.dname);
 end loop;
end p_dept;

execute p_dept;	--直接调用
Begin			--匿名块调用
	p_dept;
End;

参数的使用

三种参数模式：IN、OUT和IN OUT。

函数定义、调用

创建函数f_getName，根据传入的雇员编号返回雇员姓名。其中要有找不到该雇员的异常处理。
create or replace function f_getName
 (pi_empno emp.empno%type)
 return varchar2
as
 v_ename emp.ename%type;
begin
 select ename into v_ename from emp
 where empno = pi_empno;
 return (v_ename);
exception
 when no_data_found then
 return ('No data found');
 when others then
 return ('Other errors'); 
end f_getName;

-- （1）将函数的返回值赋值给一个变量
declare
 v_no number := 7369;
 v_name varchar2(20);
begin 
 v_name := f_getname(v_no);
 dbms_output.put_line(v_name);
end; 
-- （2）将函数的返回值赋值给一个全局变量
SQL>variable tt varchar2(10);
SQL>exec :tt:=f_getName(7788);
-- （3）在SELECT语句中使用
select empno, 
from emp;

触发器定义（DML 触发器，替代触发器）

CREATE OR REPLACE TRIGGER trg_emp_dml
AFTER INSERT OR UPDATE OR DELETE ON emp
DECLARE
  v_count NUMBER;
  v_sal   NUMBER(6,2);
BEGIN
  IF INSERTING THEN 
    SELECT count(*) INTO v_count FROM emp;
    DBMS_OUTPUT.PUT_LINE(v_count);
  ELSIF UPDATING THEN
    SELECT avg(sal) INTO v_sal FROM emp;
    DBMS_OUTPUT.PUT_LINE(v_sal);
  ELSE
    FOR v_dept IN (SELECT deptno,count(*) num FROM emp 
          GROUP BY deptno) LOOP
      DBMS_OUTPUT.PUT_LINE(v_dept.deptno||' '||v_dept.num);
    END LOOP;
  END IF;
END trg_emp_dml; 

设计题

1、创建过程P_DEPT，实现功能：输出各部门的部门名称

create or replace procedure p_dept
as
 cursor cur_dept is
 select dname from dept;
begin
 for v_dept in cur_dept loop
 dbms_output.put_line('部门名称：' || v_dept.dname);
 end loop;
end p_dept;

2、创建函数f_getName，根据传入的雇员编号返回雇员姓名。其中要有找不到该雇员的异常处理。

create or replace function f_getName
 (pi_empno emp.empno%type)
 return varchar2
as
 v_ename emp.ename%type;
begin
 select ename into v_ename from emp
 where empno = pi_empno;
 return (v_ename);
exception
 when no_data_found then
 return ('No data found');
 when others then
 return ('Other errors'); 
end f_getName;

3、声明一个游标，用于检索指定员工的雇员信息，然后使用游标的%found属性来判断是否检索到指定员工编号的雇员信息

declare
 cursor cur_emp(v_empno number) is select * from emp where empno=v_empno;
 row_emp emp%rowtype;
begin
 open cur_emp(&v_empno);
 fetch cur_emp into row_emp;
 if cur_emp%found then
 dbms_output.put_line(row_emp.ename||'-'||row_emp.empno||'-'||row_emp.job);
 else
 dbms_output.put_line('no !!!!');
 end if;
 close cur_emp;
end;

4、在匿名块中输入某部门号，检索该部门的所有雇员姓名和工资。要求使用有参游标，在游标中使用参数传递部门号，若未传递参数则默认检索部门号10的雇员信息。

declare
 cursor cur_emp(p_deptno emp.deptno%type:=10) is
 select * from emp
 where deptno = p_deptno;
begin
 for v_emp in cur_emp(&v_deptno) loop
 dbms_output.put_line('name: '||v_emp.ename);
 dbms_output.put_line('sal:  ' || v_emp.sal);
 end loop;
end;

5、为emp表创建一个触发器，当执行插入操作时，统计操作后员工人数；当执行更新工资操作时，统计更新后员工平均工资；当执行删除操作时，统计删除后各个部门的人数。

CREATE OR REPLACE TRIGGER trg_emp_dml
AFTER INSERT OR UPDATE OR DELETE ON emp
DECLARE
  v_count NUMBER;
  v_sal   NUMBER(6,2);
BEGIN
  IF INSERTING THEN 
    SELECT count(*) INTO v_count FROM emp;
    DBMS_OUTPUT.PUT_LINE(v_count);
  ELSIF UPDATING THEN
    SELECT avg(sal) INTO v_sal FROM emp;
    DBMS_OUTPUT.PUT_LINE(v_sal);
  ELSE
    FOR v_dept IN (SELECT deptno,count(*) num FROM emp 
          GROUP BY deptno) LOOP
      DBMS_OUTPUT.PUT_LINE(v_dept.deptno||' '||v_dept.num);
    END LOOP;
  END IF;
END trg_emp_dml;