SQL

约束条件

1）NOT NULL
非空，一旦使用该项约束条件，那么被约束的列不能为空值。
添加非空的语法结构如下：
(column_name [constraint constraint_name ]not null);

2）UNIQUE
唯一值约束条件。
添加唯一值约束条件的语法结构如下：
(column_name [constraint constraint_name ] unique
唯一 = 有值的话, 值要不同 ；null的话， 都是可以的。

3）Check
检查（check）约束条件允许定义为了输入的数据被Oracle数据库接收，数据必须满足的条件。可以给表的每个列定义一个检查约束条件。建立某列检查约束条件的语法如下：
column_name [constraint constraint_name ]
CHECK (column_name condition_to_satisfy) ;

4)主键(primary key)和外键(foreign key)约束

​ 我们在建立数据库的时候，需要为每张表指定一个主键(primary key) 。所谓主键就是能够唯一标识表中某一行的属性或属性组，一个表只能有一个主键。 因为主键可以唯一标识某一行记录，所以可以确保执行数据更新、删除的时候不会出现张冠李戴的错误。所以数据库在设计时，主键起到了很重要的作用。

主键约束在表中定义一个主键来唯一确定表中每一行数据的标识符. 主键 = 非空 + 唯一
添加主键约束条件的语法结构如下：
(column_name [constraint constraint_name ] primary key;

外键 (foreign key )主要用来维护两个表之间数据的一致性。当外键用于两个表的联系，两个表必须具有相同类型的属性。该属性应为其中一个表的主键，在另外一个表设置为外键。外键是用来控制数据库中数据的数据完整性的，就是当你对一个表的数据进行操作，和他有关联的一个或更多表的数据能够同时发生改变，这就是外键的作用。

添加外键约束条件的语法结构如下：
(column_name [constraint constraint_name ] references 主表名(primary key);

DML

DROP TABLE <表名>;

删除表

drop table student;

表的名字修改

rename student to stu;

删除语句 delete

DELETE语句删除数据表里的记录,谨慎使用。

delete from student where xh=0001; 删除一条记录

delete from student; 删除所有记录,表结构还在,写日志,可以恢复,速度慢

truncate table student; 删除表中的所有记录,表结构还在,不写日志,无法找回删除的记录,速度快

drop table student; 删除表的结构和数据，无法恢复

dual

dual是一个虚拟表，用来构成select的语法规则

1、查看当前用户select user from dual;2、用来调用系统函数3 、可以用做计算器 select 7*9 from dual;

时间日期函数

<2> ADD_MONTHS 添加月份 得到一个新的日期

select add_months(sysdate,12) from dual;

​ 一年以后的今天

select add_months(sysdate,-12) from dual;

​ 一年以前的今天

<3> last_day 某月的最后一天

​ select last_day(sysdate) from dual;

<4> months_between 两个日期之间的月数

​ select months_between(sysdate,’2005-02-01’) from dual;

数学函数

函数 输入 输出
Abs(n) Select abs(-15) from dual; 15
Ceil(n) Select ceil(44.778) from dual; 45
Floor(n) Select floor(100.2) from dual; 100
Power(m,n) Select power(4,2) from dual; 16
Mod(m,n) Select mod(10,3) from dual; 1
Round(m,n) Select round(100.256,2) from dual; 100.26
Trunc(m,n) Select trunc(100.256,2) from dual; 100.25
Sqrt(n) Select sqrt(4) from dual; 2
Sign(n) Select sign(-30) from dual; -1

字符函数

函数 输入 输出
Initcap(char) Select initcap(‘hello’) from dual; Hello
Lower(char) Select lower(‘FUN’) from dual; fun
Upper(char) Select upper(‘sun’) from dual; SUN
Ltrim(char,set) Select ltrim( ‘xyzadams’,’xyz’) from dual; adams
Rtrim(char,set) Select rtrim(‘xyzadams’,’ams’) from dual; xyzad
Translate(char, from, to) （单个） Select translate(‘jack’,’j’ ,’b’) from dual; back
Instr (char, m, n) Select instr (‘worldwide’,’d’) from dual; 5
Substr (char, m, n) Select substr(‘abcdefg’,3,2) from dual; cd
Concat (expr1, expr2) Select concat (‘Hello’,’ world’) from dual; （concatenate） Hello world

子查询运算符

运 算 符 含 义
IN 与子查询返回结果中任何一个值相等
NOT IN 与子查询返回结果中任何一个值都不等
>ANY 比子查询返回结果中某一个值大
=ANY 与子查询返回结果中某一个值相等
<ANY 比子查询返回结果中某一个值小
>ALL 比子查询返回结果中所有值都大
<ALL 比子查询返回结果中任何一个值都小
EXISTS 子查询至少返回一行时条件为****TRUE
NOT EXISTS 子查询不返回任何一行时条件为****TRUE

集合之间的运算

UNION /UNION ALL是集合的并运算，并运算的结果是参加运算的集合元素的总和。

UNION 和UNION ALL 的区别

UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。

UNION ALL只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数据，那么返回的结果集就会包含重复的数据了。

INTERSECT是集合的交运算。交运算的结果是参加运算的集合元素的公共元素。在SQL语言中它返回查询结果中的相同部分。

MINUS是集合的差运算。差运算的结果是参加运算的集合元素中从第一个集合中去掉二者相同部分元素后剩余的元素。在SQL语言中它返回在第一次查询结果中而不在第二次查询结果中的记录。

decode函数

decode()函数的语法格式如下：
decode(列(或表达式)，匹配值1,返回值1,匹配值2,返回值2,…匹配值n,返回值n,缺省值)

例：select ename,sal,decode(deptno, ‘10’, ‘会计部’, ‘其他部门’) from emp;

case函数

CASE 列(或表达式)
WHEN <匹配值1> THEN <表达式>
WHEN <匹配值2> THEN <表达式>
……
ELSE <表达式>
END

分组中的函数

数据库表emp，如果当中两个字段名为a，b,假设使用group by rollup(a,b)，首先会对(a,b)进行group by ，然后对 a 进行 group by 。最后对全表进行 group by 操作

假设使用group by cube(a,b),，则首先会对(a,b)进行group by，然后依次是(a)，(b)，最后对全表进行group by 操作，一共是2^2=4次grouping

grouping sets就是对參数中的每一个參数做grouping。假设使用group by grouping sets(a,b)。则对(a),(b)进行group by

排名函数

rank()和dense_rank()区别：
–rank()是跳跃排序，有两个第二名时接下来就是第四名；
dense_rank()l是连续排序，有两个第二名时仍然跟着第三名

oracle部分

pl块

PL/SQL 块的格式如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
[DECLARE]

<声明语句 >

BEGIN

<执行语句>

[EXCEPTION]

<异常处理语句 >

END

变量定义的一般格式：

[CONSTANT] [[NOT NULL] {DEFAULT|:=}];

说明

1.每行只能定义一个标识符。

2.如果加上关键字CONSTANT,则表示所定义的标识符为一个常量，必须为它赋初值。

3.如果定义的标识符不能为空，则必须加上关键字NOT NULL,并赋初值。

4.为标识符赋值时，使用赋值符号‘：=’，默认值为空。

声明一个变量，使它的类型与某个变量或数据库基本表中 某列的数据类型一致，可以使用%TYPE。

使用%ROWTYPE属性则可以声明一个类型与某个记录或基于表或游标的行对象一致的数据类型，也称为记录对象。

可以在PL/SQL中执行的SQL语句包括SELECT ，DML(UPDATE、DELETE、INSERT)，事务控制语句（COMMIT、ROLLBACK、SAVEPOINT）。注意DDL语句不可以直接使用。

PL/SQL中最常用的SQL语句是SELECT…INTO

​ SELECT…INTO语句只能查询一个记录的信息，如果没有查询到任何数据，则会产生NO_DATA_FOUND异常；如果查询到多个记录，则会产生TOO_MANY_ROW异常。

INTO句子后的变量用于接收查询的结果，变量的个数、顺序应该与查询的目标数据相匹配，也可以是记录类型的变量。

1
2
3
4
5
6
7
8
9
10
DECLARE
  v_emp emp%ROWTYPE;
  v_ename emp.ename%type;
  v_sal   emp.sal%type;
  BEGIN
  SELECT * INTO v_emp FROM emp WHERE ename='SMITH';
  DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||v_emp.sal);
  select ename,sal INTO v_ename,v_sal FROM emp WHERE empno=7900;
  DBMS_OUTPUT.PUT_LINE(v_ename||' '||v_sal);
  END;

PL/SQL中DML语句对标准SQL语句中的DML语句进行了扩展，允许使用变量。

1
2
3
4
5
6
7
8
9
DECLARE
    v_empno emp.empno%TYPE :=7500;
BEGIN
    INSERT INTO emp(empno,ename,sal,deptno)     
                        VALUES(v_empno,'JOAN',2300,20);
    UPDATE emp SET sal=sal+100 WHERE 
                      empno=v_empno;
  DELETE FROM emp WHERE empno=v_empno;
END;

1.WHERE子句中标识符的区分

系统首先查看WHERE子句中的标识符是否与表中的列名相同，如果相同，则该标识符被解释为列名；如果没有同名列，系统检查该标识符是不是PL/SQL语句块的变量。

2.WHERE子句中字符串比较

（1）填充比较：通过在短字符串后添加空格，使两个字符串达到相同长度，然后根据每个字符的ASCII码进行比较。

（2）非填充比较：根据每个字符的ASCII码进行比较，最先结束的字符串为小。PL/SQL中规定，对定长的字符串（CHAR类型的字符串和字符串常量）采用填充比较；如果比较的字符串中有一个是变长字符串（VARCHAR2类型的字符串），则采用非填充比较。

如果要查询当前DML语句操作的记录的信息，可以在DML语句末尾使用RETURNING语句返回该记录的信息。

RETURNING语句的基本语法：

RETURNING select_list_item INTO variable_list|record_variable;

1
2
3
4
5
6
7
DECLARE
   v_sal emp.sal%TYPE;
BEGIN
   UPDATE emp SET sal=sal+10   WHERE empno=7844 
     RETURNING sal INTO v_sal;
   DBMS_OUTPUT.PUT_LINE(v_sal);
END;

选择结构和循环结构

选择结构

1
2
3
4
5
6
IF语句
IF condition1 THEN statements1;
[ELSIF condition2 THEN statements2;]
……
[ELSE else_statements];
END IF;

条件是一个布尔型变量或表达式，取值只能是TRUE，FALSE，NULL。

&符号：在Oracle中,&符号常用来替换变量提高Oracle数据库系统的交互性

1
Select * from emp where empno=&x;
1
2
3
4
5
6
7
8
9
10
11
12
13
DECLARE
   v_deptno emp.deptno%type;
   v_increment NUMBER(4);
   v_empno  emp.empno%type;
  BEGIN
    v_empno:=&x;
SELECT deptno INTO v_deptno FROM emp WHERE empno=v_empno;
   IF v_deptno=10 THEN v_increment:=100;
    ELSIF v_deptno=20 THEN v_increment:=150;
    ELSIF v_deptno=30 THEN v_increment:=200;
    ELSE  v_increment:=300;    END IF;
  UPDATE emp SET sal=sal+v_increment WHERE empno=v_empno;
   END;

case语句

基本语法

1
2
3
4
5
6
7
CASE
    WHEN condition1 THEN statements1;
     WHEN condition2 THEN statements2;
     ……
      WHEN conditionn THEN statementsn;
   [ELSE    else_statements;]
END CASE;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
DECLARE
   v_deptno emp.deptno%type;
   v_increment NUMBER(4);
   v_empno  emp.empno%type;
  BEGIN
  v_empno:=&x;
  SELECT deptno INTO v_deptno FROM emp WHERE empno=v_empno;
  CASE v_deptno  
      WHEN 10 THEN v_increment:=100;
      WHEN 20 THEN v_increment:=150;
      WHEN 30 THEN v_increment:=200;
    ELSE  v_increment:=300;
   END CASE;
  UPDATE emp SET sal=sal+v_increment WHERE empno=v_empno; 
  END;

简单循环

1
2
3
4
LOOP
    sequence_of_statement;
    EXIT [WHEN condition] ; 
 END LOOP;

例：执行CREATE TABLE temp_table(num_col NUMBER,info_col CHAR(10)) 语句创建temp_table表，然后利用循环向temp_table表中插入50条记录。

1
2
3
4
5
6
7
8
9
DECLARE
  v_counter BINARY_INTEGER := 1;
BEGIN
  LOOP
    INSERT INTO temp_table VALUES (v_Counter, 'Loop index');
    v_counter := v_counter + 1;
    EXIT WHEN v_counter > 50;
  END LOOP;
END;

while循环

语法：

1
2
3
WHILE condition LOOP 
     sequence_of_statement;
END LOOP; 
1
2
3
4
5
6
7
8
DECLARE
 v_counter BINARY_INTEGER :=1;
BEGIN
  WHILE v_counter <= 50 LOOP
     INSERT INTO temp_table VALUES (v_counter, 'Loop index');
     v_counter := v_counter + 1;
  END LOOP;
END;

for循环

1
2
3
4
FOR loop_counter IN [REVERSE]   low_bound..high_bound
 LOOP
     sequence_of_statement;
 END LOOP;

注意：

（1）循环变量不需要显式定义，系统隐含地将它声明为BINARY_INTEGER变量；

（2）系统默认时，循环变量从下界往上界递增计数，如果使用REVERSE关键字，则表示循环变量从上界向下界递减计数；

（3）循环变量只能在循环体中使用，不能在循环体外使用。

1
2
3
4
5
BEGIN
  FOR v_counter IN 1..50 LOOP
    INSERT INTO temp_table VALUES (v_counter, 'Loop Index');
  END LOOP;
  END;

游标

游标的定义：游标（CURSOR）是Oracle系统在内存中开辟的一个工作区，在其中存放SELECT语句返回的查询结果。使用游标时，SELECT语句查询的结果可以是单条记录，多条记录，也可以是零条记录。游标工作区中，存在着一个指针（POINTER）,在初始状态它指向查询结果的首记录。游标一般用来遍历结果集。

游标的类型

（1）显式游标：由用户定义、操作，用于处理返回多行数据的SELECT查询。

（2）隐式游标：由系统自动进行操作，用于处理DML语句和返回单行数据的SELECT查询。

显式游标的操作步骤为定义游标，打开游标，检索游标，关闭游标四步。

定义游标语法格式

CURSOR cursor_name IS select_statement ;

说明

（1）游标必须在PL/SQL块的声明部分进行定义；

（2）游标定义时可以引用PL/SQL变量，但变量必须在游标定义之前定义；

（3）定义游标时并没有生成数据，只是将定义信息保存到数据字典中；

（4）游标定义后，可以使用cursor_name%ROWTYPE定义游标类型变量。

打开游标语法格式
OPEN cursor_name;
说明
（1）检查变量的值
（2）执行游标定义时对应的SELECT语句，将查询结果检索到工作区中。
（3）游标指针指向第一个元组
（4）一旦游标打开，就无法再次打开，除非先关闭
如果游标定义中的变量值发生变化，则只能在下次打开游标时才起作用。

检索游标语法格式
FETCH cursor_name INTO variable_list|record_variable;
说明
（1）在使用FETCH语句之前必须先打开游标
（2）对游标第一次使用FETCH语句时，游标指针指向第一条记录，因此操作的对象是第一条记录，使用后，游标指针指向下一条记录。
（3）游标指针只能向下移动，不能回退
（4）INTO子句中的变量个数、顺序、数据类型必须与工作区中每行记录的字段数、顺序以及数据类型一一对应。

关闭游标语法格式
CLOSE cursor_name;
说明
游标所对应的内存工作区变为无效，释放与游标相关的系统资源。

显式游标的属性

cursor_name%ISOPEN

布尔型。如果游标已经打开，返回TRUE,否则为FALSE。

cursor_name%FOUND

布尔型，如果最近一次使用FETCH语句，有返回结果则为TRUE,否则为FALSE;

cursor_name%NOTFOUND

布尔型，如果最近一次使用FETCH语句,没有返回结果则为TRUE,否则为FALSE;

cursor_name%ROWCOUNT

数值型，返回到目前为止从游标缓冲区检索的元组数。 

1
2
3
4
5
6
7
8
9
10
11
DECLARE
   CURSOR cursor_name IS SELECT…;
BEGIN
   OPEN cursor_name;
   LOOP
      FETCH…INTO…;
      EXIT WHEN cursor_name%NOTFOUND;
      ……
   END LOOP;
   CLOSE cursor_name;
END; 
1
2
3
4
5
6
7
8
9
10
11
12
DECLARE
  CURSOR c_dept_stat IS SELECT deptno,avg(sal) avgsal FROM emp GROUP BY deptno;
  v_dept c_dept_stat%ROWTYPE;
BEGIN
OPEN c_dept_stat;
LOOP
    FETCH c_dept_stat INTO v_dept;
    EXIT WHEN c_dept_stat%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_dept.deptno||' '||v_dept.avgsal);
END LOOP;
CLOSE c_dept_stat;    
END;
1
2
3
4
5
6
7
8
9
10
11
DECLARE
    CURSOR cursor_name IS SELECT…;
BEGIN
    OPEN cursor_name;
    FETCH…INTO…;
    WHILE cursor_name%FOUND LOOP
        FETCH…INTO…;
        ……
    END LOOP;
    CLOSE cursor;
END;
1
2
3
4
5
6
7
8
9
10
11
DECLARE
  CURSOR c_dept_stat IS SELECT deptno,avg(sal) avgsal FROM emp GROUP BY deptno;
  v_dept c_dept_stat%ROWTYPE;
BEGIN
  OPEN c_dept_stat;
  FETCH c_dept_stat INTO v_dept; 
  WHILE c_dept_stat%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE(v_dept.deptno||' '||v_dept.avgsal);
    FETCH c_dept_stat INTO v_dept;
  END LOOP;
  CLOSE c_dept_stat;   END;

for循环检索显示游标格式

1
2
3
4
5
6
7
DECLARE
   CURSOR cursor_name IS SELECT…;
BEGIN
   FOR loop_variable IN cursor_name  LOOP
           ……
   END LOOP;
END;

%rowtype变量是用来作为循环计算器的。

for循环说明：

（1）系统隐含地定义了一个数据类型为%ROWTYPE的变量，并以此作为循环的计算器。

（2）系统自动打开游标，不用显式地使用OPEN语句打开；

（3）系统重复地自动从游标工作区中提取数据并放入计数器变量中。

（4）系统自动进行%FOUND属性检查以确定是否有数据

（5）当游标工作区中所有的记录都被提取完毕或循环中断时，系统自动地关闭游标。

1
2
3
4
5
6
7
DECLARE
  CURSOR c_dept_stat IS SELECT deptno,avg(sal) avgsal FROM emp GROUP BY deptno;
BEGIN
  FOR v_dept IN c_dept_stat LOOP
    DBMS_OUTPUT.PUT_LINE(v_dept.deptno||' '||v_dept.avgsal);
  END LOOP; 
END;

显式游标更新数据：

update/delete 数据 where current of cursor_name

由于COMMIT语句会释放会话拥有的任何锁，因此如果在检索游标的循环内使用COMMIT语句会释放定义游标时对数据加的锁，从而导致利用游标修改或删除数据的操作失败。

一般游标只能与指定的查询相连，即固定指向一个查询的内存处理区域，称为静态游标。动态游标（含有游标变量）也是一个指向多行查询结果集合中当前数据行的指针，由于游标变量则可与不同的查询语句相连，它可以指向不同查询语句的内存处理区域。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DECLARE
  v_deptno  emp.deptno%TYPE;
  CURSOR c_emp IS SELECT * FROM emp WHERE deptno=v_deptno;
  v_emp c_emp%ROWTYPE;
BEGIN
 v_deptno:=&x;
  OPEN c_emp;
  LOOP
     FETCH c_emp INTO v_emp;
     EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_emp.empno||'  '||v_emp.ename||' '||  v_emp.sal ||'  '|| v_deptno);                                   
END LOOP;
CLOSE c_emp;      
END;

SQL%ISOPEN 游标是否打开：布尔值，默认值为null

SQL%FOUND 判断语句是否运行成功：布尔值，默认值为null

SQL%NOTFOUND 判断语句是否运行成功：布尔值，默认值为null

SQL%ROWCOUNT 语句执行影响行数：数值型，默认值为0

异常

异常类型

（1）预定义的Oracle异常

（2）非预定义的Oracle异常

（3）用户定义的异常

异常处理器基本形式

1
2
3
4
5
6
7
8
9
10
11
EXCEPTION

WHEN exception1[OR excetpion2…]THEN sequence_of_statements1;

WHEN exceptioin3[OR exception4…]THEN sequence_of_statements2;

   ……

WHEN OTHERS THEN sequence_of_statementsn;

END;

一个异常处理器可以处理多个异常，但一个异常只能被一个异常处理器处理，异常处理器处理多个异常值时要使用or链接。

使用非预定义异常

一：在定义部分定义异常名，

二：在异常和Oracle错误之间建立关联，

三：在异常处理部分捕捉并处理异常。

当定义Oracle错误和异常之间的关联关系时，需要使用伪过程EXCEPTION_INIT。

一：首先的定义部分定义异常；

二：使用pragma exception_init(exception_name,exception_number)在异常和oracle错误之间建立关联，　　 这时要求用户知道可能出现的错误号

三：最终在异常处理部分捕捉并处理异常。

非预定义异常

1.非预定义是除了预定义异常和自定义异常外，还有一些属于程序本身逻辑错误的异常.

2.oracle为这些非预定义异常定义了错误代码，没有定义异常名

3.处理这些异常需用户自己设置异常名

下面以更新特定雇员的部门号，并处理ORA-02291错误为例，说明使用非预定义异常的方法。示例如下：

1
2
3
4
5
6
7
8
DECLARE
 e_integrity EXCEPTION;--1、定义部分
 PRAGMA EXCEPTION_INIT(e_integrity,-2291); --2、建立关联关系
 BEGIN
 UPDATE emp SETdeptno=&dno WHERE empno=&eno;
 EXCEPTION
 WHEN e_integrityT HEN --3、捕捉处理DBMS_OUTPUT.PUT_LINE(‘该部门不存在’);
 END;

预定义异常和非预定义异常都与Oracle错误有关，并且当出现Oracle错误时会隐含触发相应异常；而自定义异常与Oracle错误没有任何关联，它是由开发人员为特定情况所定义的异常。

当使用自定义异常时，
　1.需要在定义部分（DECLARE）定义异常，
　2.再执行部分（BEGIN）触发异常（使用RAISE语句），
　3.在异常处理部分（EXCEPTION）捕捉并处理异常。

修改7844员工的工资，保证修改后工资不超过6000

1
2
3
4
5
6
7
8
9
10
11
12
DECLARE
 e_highlimit EXCEPTION;
 v_sal emp.sal%TYPE;
BEGIN
 UPDATE emp SET sal=sal+100 WHERE empno=7844 RETURNING sal INTO v_sal;
 IF v_sal>6000  THEN  RAISE e_highlimit; 
END IF;
EXCEPTION
 WHEN e_highlimit THEN
  DBMS_OUTPUT.PUT_LINE('The salary is too large!');
  ROLLBACK；
END;

OTHERS异常处理器是一个特殊的异常处理器，可以捕获所有的异常。通常，OTHERS异常处理器总是作为异常处理部分的最后一个异常处理器，负责处理那些没有被其他异常处理器捕获的异常。

存储过程

存储过程（procedure）是一种命名的PL/SQL程序快，存储过程被保存在数据库中，它不可以被SQL语句直接执行或调用，只能通过EXECUT命令执行或在PL/SQL程序块中内部调用。由于存储过程是已经编译好的代码，所以在被调用或引用时，其执行效率非常高。

存储过程创建格式

1
2
3
4
5
6
7
8
9
10
CREATE [OR REPLACE] PROCEDURE procedure_name
(parameter1_name [mode] datatype [DEFAULT|:=value]
[, parameter2_name [mode] datatype [DEFAULT|:=value],…])
AS|IS
  /*Declarative section is here */
BEGIN
  /*Executable section is here*/ 
EXCEPTION
  /*Exception section is here*/ 
END[procedure_name];

创建一个存储过程，以部门号为参数，查询该部门的平均工资，并输出该部门中比平均工资高的员工号、员工名。

1
2
3
4
5
6
7
8
9
10
11
12
CREATE OR REPLACE PROCEDURE show_emp
(p_deptno emp.deptno%TYPE) AS v_sal emp.sal%TYPE;
BEGIN
SELECT avg(sal) INTO v_sal FROM emp WHERE deptno=p_deptno;
DBMS_OUTPUT.PUT_LINE(p_deptno||'  '||'average salary is:'||v_sal);
FOR v_emp IN (SELECT * FROM emp WHERE deptno=p_deptno AND sal>v_sal) LOOP
  DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||v_emp.ename);
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('The department doesn’’t exists!’); 
 END show_emp;

存储过程调用

在SQL*PLUS中调用

EXEC procedure_name(parameter_list)

例如：EXECUTE show_emp(10)

在PL/SQL块中调用

1
2
3
4
5
6
BEGIN
   procedure_name(parameter_list);
END；
例如：BEGIN
show_emp(20);
END;

存储过程的管理

（1）修改存储过程

CREATE OR REPLACE PROCEDURE

（2）重新编译存储过程

ALTER PROCEDURE procedure_name COMPILE；

（3）删除存储过程

DROP PROCEDURE procedure_name；

（4）查看过程源代码

select text from user_source where name=procedure_name ;

(过程名带引号并大写）

函数

函数和存储过程之间的区别和相似之处

1.函数用于返回特定数据，可以返回一个或多个值。因此在一个函数中必须包含一个或多个RETURN 语句

2.函数调用是PL/SQL表达式的一部分，而过程调用可以是一个独立的PL/SQL语句

函数创建格式

1
2
3
4
5
6
7
8
9
10
CREATE [OR REPLACE] FUNCTION function_name 
(parameter1_name [mode] datatype [DEFAULT|:=value]
[, parameter2_name [mode] datatype [DEFAULT|:=value],…])
RETURN return_datatype 
AS |IS /*Declarative section is here */
BEGIN
  /*Executable section is here*/ 
EXCEPTION
  /*Exception section is here*/ 
END [function_name];

创建一个以部门号为参数，返回该部门最高工资的函数

1
2
3
4
5
6
7
8
9
10
11
12
CREATE OR REPLACE FUNCTION return_maxsal
(p_deptno emp.deptno%TYPE)
RETURN emp.sal%TYPE
AS v_maxsal emp.sal%TYPE;
BEGIN
  SELECT max(sal) INTO v_maxsal FROM emp 
 WHERE deptno=p_deptno;
  RETURN v_maxsal;
EXCEPTION 
  WHEN NO_DATA_FOUND THEN  
  DBMS_OUTPUT.PUT_LINE('The deptno is invalid!');
END return_maxsal;

函数的管理：

（1）修改函数
CREATE OR REPLACE FUNCTION function_name
（2）重新编译函数
ALTER FUNCTION function_name COMPILE；
（3）删除函数
DROP FUNCTION function_name ；
（4）查看函数源代码
select text from user_source where name= function_name;

包

包（package）是包含一个或多个子程序单元（过程、函数等）的容器，包是全局的。

包分为数据库内置包 和用户创建包两种类型。

包由包规范和包体两部分组成，在数据库中独立存储。

包由包规范和包体两部分组成，在数据库中独立存储

包规范声明了软件包中所有内容，如过程、函数、游标、类型、异常和变量等，其中过程和函数只包括原型信息，不包含任何子程序代码。

包体中包含了在包头中的过程和函数的实现代码。包体中还可以包括在规范中没有声明的变量、游标、类型、异常、过程和函数，但是它们是私有元素，只能由同一包体中其他过程和函数使用。

创建包规范语法

1
2
3
4
CREATE OR REPLACE PACKAGE package_name 
 IS|AS
type_definition|variable_declaration|exception_declaration|cursor_declaration| procedure_ declaration |function_ declaration
END [package_name];

注意：

（1）元素声明的顺序可以是任意的，但必须先声明后使用；

（2）所有元素是可选的；

（3）过程和函数的声明只包括原型，不包括具体实现。

创建一个软件包，包括2个变量、2个过程和1个异常。

1
2
3
4
5
6
7
8
9
10
CREATE OR REPLACE PACKAGE pkg_emp
AS 
  minsal   NUMBER;
  maxsal   NUMBER;
  e_beyondbound  EXCEPTION;
  PROCEDURE update_sal(
    p_empno NUMBER, p_sal NUMBER);
  PROCEDURE add_employee(
    p_empno NUMBER,p_sal NUMBER);
END pkg_emp; 
1
2
3
4
5
6
7
create or repalce package body package_name is|as
type_defination|variable_declaration|
exception_declaration|
cursor_declaration|
prcedure_definition|
function_definition
end[package_name]

注意：

（1）包体中函数和过程的原型必须与包规范中的声明完全一致；

（2）只有在包规范已经创建的条件下，才可以创建包体；

（3）如果包规范中不包含任何函数或过程，则可以不创建包体。 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE OR REPLACE PACKAGE BODY pkg_emp  AS
    PROCEDURE update_sal(p_empno NUMBER, p_sal NUMBER) AS
    BEGIN
      SELECT min(sal), max(sal) INTO minsal,maxsal FROM emp;
      IF p_sal BETWEEN minsal AND maxsal THEN
        UPDATE emp SET sal=p_sal WHERE empno=p_empno;
        IF SQL%NOTFOUND THEN
      RAISE_APPLICATION_ERROR(-20000,'The employee doesn''t exist');END IF;
      ELSE
        RAISE e_beyondbound;  END IF;
EXCEPTION                                                    讨论这个过程的功能
      WHEN e_beyondbound THEN
         DBMS_OUTPUT.PUT_LINE('The salary is beyond bound!');
    END update_sal;

补充说明：

RAISE_APPLICATION_ERROR过程包含在DBMS_STANDARD包的，可以重新定义异常错误消息，它为应用程序提供了一种与ORACLE交互的方法。

语法：

RAISE_APPLICATION_ERROR(errorNumber,errorString ）

其中errorNumber是数值在-20000到-20999之间，errorString为自定义的错误信息

包的调用说明：

在软件包头部声明的任何元素是公有的，在包外都是可见的 。

包外：通过package.element形式调用；

包内：直接通过元素名进行调用。

在包体中定义而没有在包头中声明的元素是私有的，只能在包体中引用。

调用软件包pkg_emp中的过程update_sal，修改7844员工工资为3000。调用add_employee添加一个员工号为1357，工资为4000的员工。

1
2
3
4
BEGIN
  pkg_emp.update_sal(7844,3000);
  pkg_emp.add_employee(1357,4000);
END;

触发器的定义：触发器是程序块的一种，触发器的执行是自动进行的，当相应事件发生时就会激发触发器的执行，触发器不接受任何参数。

触发器（TRIGGER）作用

（1）维护那些通过创建表时的声明约束不可能实现的复杂的完整性约束

（2）通过记录已进行的改变及是谁进行了该项改变来检查一个表中的信息。

（3）当一个表发生改变时，自动向其他程序发送需要采取行动的信号

触发器类型

（1）DML触发器:INSERT、DELETE、UPDATE操作触发

（2）系统触发器:数据库启动或关闭之类的系统事件发生时触发，在执行诸如创建表之类的DDL操作时触发

触发器创建格式

1
2
3
4
5
6
7
8
9
10
11
12
CREATE [OR REPLACE] TRIGGER trigger_name
BEFORE|AFTER triggering_event [OF column_name]
ON table_name
[FOR EACH ROW]
[WHEN trigger_condition]
DECLARE
     /*Declarative section is here */
BEGIN
     /*Exccutable section si here*/ 
EXCEPTION
     /*Exception section is here*/ 
END [trigger_name];

创建一个触发器，禁止在休息日改变雇员信息

1
2
3
4
5
6
7
create or replace trigger tr_sec_emp
before insert or update or delete on emp
begin
  if   to_char(sysdate,'DY') in ('星期六','星期日') then
raise_application_error(-20001,'不能在休息日该员工信息');
  end if;
end;

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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;

行级触发器是指执行DML操作时，每操作一记录，触发器就执行一次，一个DML操作涉及到多少个记录，触发器就执行多少次。在行级触发器中可以使用WHEN条件，进一步控制触发器的执行。在触发器体中，可以对当前操作的记录进行访问和操作。

触发语句 ：old ：new
INSERT 未定义，所有字段都为****NULL 当语句完成时，将要被插入的值
UPDATE 更新前行的原始值 当语句完成时，将要被更新的值
DELETE 行被删除前的原始值 未定义，所有字段都为****NULL

为emp表创建一个触发器，当插入新员工时显示新员工的员工号、员工名；当更新员工工资时，显示修改前后员工工资；当删除员工时，显示被删除的员工号、员工名。

1
2
3
4
5
6
7
8
9
10
CREATE OR REPLACE TRIGGER trg_emp_dml_row
BEFORE INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW
BEGIN
   IF INSERTING THEN 
   DBMS_OUTPUT.PUT_LINE(:new.empno||' '||:new.ename);
   ELSIF UPDATING THEN
         DBMS_OUTPUT.PUT_LINE(:old.sal||' '||:new.sal);
   ELSE     DBMS_OUTPUT.PUT_LINE(:old.empno||:old.ename);
   END IF;    END trg_emp_dml_row;

在行级触发器中，可以使用WHEN子句进一步控制触发器的执行。

例:修改员工工资时，保证修改后的工资高于修改前的工资。

1
2
3
4
5
6
7
8
CREATE OR REPLACE TRIGGER trg_emp_update_row
BEFORE UPDATE OF sal ON emp
FOR EACH ROW
WHEN(new.sal<=old.sal)
BEGIN
    RAISE_APPLICATION_ERROR(
                    -20001,'The salary is lower!');
END trg_emp_update_row;

（1）修改触发器

CREATE OR REPLACE TRIGGER trigger_name

（2）重新编译触发器

ALTER TRIGGER trigger_name COMPILE;

（3）禁用、启用触发器

ALTER TRIGGER trigger_name DISALBLE|ENABLE

（4）禁用、启用某个表相关的所有触发器：

ALTER TABLE table_name DISABLE|ENABLE ALL TRIGGERS；

（5）删除触发器

DROP TRIGGER trigger_name;