ORCLE知识点总结

来源：互联网发布：电脑性能评测软件编辑：程序博客网时间：2024/06/15 17:31

DAY1 4

1.序列触发器 4

2.模板： 4

查询Oracle中所有用户信息 5

1.查看所有用户： 5

2.查看用户或角色系统权限 5

3.查看角色( 5

4.查看用户对象权限： 5

5.查看所有角色： 6

6.查看用户或角色所拥有的角色： 6

7.查看哪些用户有sysdba或sysoper系统权限 6

8.SqlPlus中查看一个用户所拥有权限 6

9、Oracle删除指定用户所有表的方法 6

10、删除用户 6

11、获取当前用户下所有的表： 6

12、删除某用户下所有的表数据: 7

13、禁止外键 ORACLE数据库中的外键约束名都在表user_constraints中可以查到。 7

14、 ORACLE禁用/启用外键和触发器 7

--启用脚本 7

--禁用脚本 8

二.DAY2 10

1.插入数据 10

2. 序列 10

3.提交与回滚 10

4.修改数据 11

6.查询语句 11

7.基础查询 11

8.模糊查询（LIKE） 11

9.多表关联查询： 12

10.oracle伪列 12

11.子查询 12

三．DAY3 12

1.日期函数 12

2.转换函数 13

3.分组函数 13

4.分析函数 13

操作符及SQL函数 14

SQL函数 14

1. 算数操作符 14

2. 比较操作符 14

3.连接操作符 15

4.集合操作符 15

5.操作符优先级 16

SQL函数 16

1.单行函数： 16

（1）字符串函数 17

（2）数字函数 18

（3）日期函数 18

数据库对象 19

1.数据库对象 19

同义词 19

2序列 19

3视图 20

4索引 20

四、DAY4 21

1. 使用PL/SQL 21

1.1 PL/SQL 是过程语言 21

1.2 PL/SQL块 21

1.2_2 21

1.3 PC/SQL语言特性 22

1.4 PL/SQL语言特征 22

1.5数据类型 23

1.6控制结构 23

1.7 PL/SQL 的优点总结 24

1.8总结 25

2. 子程序和程序包 26

2.1过程定义 26

2.1.2过程的创建和执行 26

2.1.3无参过程 26

2.1.4带输入参数的过程 27

2.1.5输入输出综合过程 28

2.1.6同时为输入和输出参数过程 28

2.1.7过程总结 29

2.2函数 29

2.2.1 29

2.2.2定义函数的限制： 29

2.2.3访问函数的两种方式： 30

2.2.4创建函数 30

2.2.5 SQL语句调用函数 30

2.2.6 PL/SQL语句调用函数 30

2.2.7根据部门编号返回部门名称 30

2.2.8使用SQL语句访问函数 31

2.3过程函数比较 31

2.4程序包 32

2.4.1程序包组成 32

程序包规范 32

程序包主体 32

2.4.2创建程序包规范 33

2.4.3创建程序包主体 33

2.4.4有关子程序和程序包的信息 34

2.4.5程序包的删除 34

2.4.6程序包的优点 34

2.5程序包中的游标 34

2.6总结 35

3触发器 35

3.1触发器 35

3.2触发器组成 36

3.3触发器分类 36

3.3.1 DML触发器 36

3.3.2 INSTEAD OF 触发器 37

3.3.3 系统触发器 38

3.4启用、禁用和删除触发器 38

3.5查看有关触发器的信息 39

3.6总结 39

一.DAY1

1.序列触发器

----------------------网上查的资料

ORACLE中只能用序列来增长，不可能实现自动增长的除非你用触发器，

1. 用alert语句给表新增一个字段，然后用序列给这个字段赋值，有多少条记录就赋多少，这个相信你自己也字段怎么做

2.写一个insert触发器，插入记录的同时更新该条记录的新字段信息用序列

语法：

CREATE OR REPLACE TRIGGER trigger_name

[OR <INSERT | DELETE| UPDATE[OF column_list]>]

ON [schema.].table_name

[REFERENCING [NEW AS new_alias] [OLD AS old_alias]]

[FOR EACH ROW]

[WHEN (condition)]

PL/SQL Block;

2.模板：

CREATE SEQUENCE user_seq

START WITH 1

INCREMENT BY 1;

CREATE SEQUENCE 序列名

[START WITH 1] --开始

[INCREMENT BY 1 -- 每次加多少

[MAXVALUE 2000] --最大值

[MINVALUE 1] --最小值

[NOCYCLE] --不循环

[NOCACHE]; --不缓存

序列操作

访问序列的值

NEXTVAL 返回序列的下一个值（先执行）

CURRVAL 返回序列的当前值(第一次NEXTVAL初始化之后才能使用CURRVAL)（后执行）

例子：
create sequence sq_add_user  --创建序列实现自动加1
       increment by 1
       start with 1
       nomaxvalue
       nocycle
create or replace trigger tr_sq_add_user  --调用
before insert on tb_user for each row
begin
  select sq_add_user.nextval into :new.userID from dual;
end;

查询Oracle中所有用户信息

1.查看所有用户：

select * from dba_users;

select * from all_users;

select * from user_users;

Oracle获取当前用户的登录信息，可用sql语句查询。

select user from dual;

2.查看用户或角色系统权限(直接赋值给用户或角色的系统权限)：

select * from dba_sys_privs;

select * from user_sys_privs; (查看当前用户所拥有的权限)

3.查看角色(只能查看登陆用户拥有的角色)所包含的权限

sql>select * from role_sys_privs;

4.查看用户对象权限：

select * from dba_tab_privs;

select * from all_tab_privs;

select * from user_tab_privs;

5.查看所有角色：

select * from dba_roles;

6.查看用户或角色所拥有的角色：

select * from dba_role_privs;

select * from user_role_privs;

7.查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)

select * from V$PWFILE_USERS

8.SqlPlus中查看一个用户所拥有权限

SQL>select * from dba_sys_privs where grantee='username'; 其中的username即用户名要大写才行。

比如： SQL>select * from dba_sys_privs where grantee='TOM';

9、Oracle删除指定用户所有表的方法

select 'Drop table '||table_name||';' from all_tables where owner='要删除的用户名(注意要大写)';

10、删除用户

drop user user_name cascade; 如：drop user SMCHANNEL CASCADE

11、获取当前用户下所有的表：

select table_name from user_tables;

12、删除某用户下所有的表数据:

select 'truncate table ' || table_name from user_tables;

13、禁止外键ORACLE数据库中的外键约束名都在表user_constraints中可以查到。

其中constraint_type='R'表示是外键约束。

启用外键约束的命令为：alter table table_name enable constraint constraint_name

禁用外键约束的命令为：alter table table_name disable constraint constraint_name

然后再用SQL查出数据库中所以外键的约束名：

select 'alter table '||table_name||' enable constraint '||constraint_name||';' from user_constraints where constraint_type='R'

select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints where constraint_type='R'

14、ORACLE禁用/启用外键和触发器

--启用脚本

SET SERVEROUTPUT ON SIZE 1000000

BEGIN

for c in (select 'ALTER TABLE '||TABLE_NAME||' ENABLE CONSTRAINT '||constraint_name||' ' as v_sql from user_constraints

where CONSTRAINT_TYPE='R') loop

DBMS_OUTPUT.PUT_LINE(C.V_SQL);

begin

EXECUTE IMMEDIATE c.v_sql;

exception when others then

dbms_output.put_line(sqlerrm);

end;

end loop;

for c in (select 'ALTER TABLE '||TNAME||' ENABLE ALL TRIGGERS ' AS v_sql from tab where tabtype='TABLE') loop

dbms_output.put_line(c.v_sql);

begin

execute immediate c.v_sql;

exception when others then

dbms_output.put_line(sqlerrm);

end;

end loop;

end;

commit;

--禁用脚本

SET SERVEROUTPUT ON SIZE 1000000

BEGIN

for c in (select 'ALTER TABLE '||TABLE_NAME||' DISABLE CONSTRAINT '||constraint_name||' ' as v_sql from user_constraints

where CONSTRAINT_TYPE='R') loop

DBMS_OUTPUT.PUT_LINE(C.V_SQL);

begin

EXECUTE IMMEDIATE c.v_sql;

exception when others then

dbms_output.put_line(sqlerrm);

end;

end loop;

for c in (select 'ALTER TABLE '||TNAME||' DISABLE ALL TRIGGERS ' AS v_sql from tab where tabtype='TABLE') loop

dbms_output.put_line(c.v_sql);

begin

execute immediate c.v_sql;

exception when others then

dbms_output.put_line(sqlerrm);

end;

end loop;

end;

commit;

----------------------网上查的资料

二.DAY2

1.插入数据

INSERT INTO 表名[(列名)] VALUES(值列表);

INSERT INTO tb_user(id,user_name,user_password)

VALUES (2015,’xm’,’a123’);

注意事项：

插入一行数据时，values里面的值需要与前面的列名一一对应；

插入值的数据类型必须与对应列的数据类型相匹配；

插入的数据项，要求符合约束的要求；

尽量不要省略列名，而是写全所需插入的列。

2.序列

CREATE SEQUENCE user_seq START WITH 1 INCREMENT BY 1;

CREATE SEQUENCE 序列名

[START WITH 1] --开始

[INCREMENT BY 1 -- 每次加多少

[MAXVALUE 2000] --最大值

[MINVALUE 1] --最小值

[NOCYCLE] --不循环

[NOCACHE]; --不缓存

序列操作

访问序列的值

NEXTVAL 返回序列的下一个值（先执行）

CURRVAL 返回序列的当前值(第一次NEXTVAL初始化之后才能使用CURRVAL)（后执行）

SQL> ALTER SEQUENCE user_seq MAXVALUE 5000; --更改序列

SQL> DROP SEQUENCE user_seq; --删除序列

3.提交与回滚

DML语言，需要commit

比如update，delete，insert等修改表中数据的;

其他，如：DDL语言，就不需要写commit

比如create，drop等改变表结构的，（因为内部隐藏了commit）。

回滚类似撤回，delect的语句在没有commit的情况下可以回滚

4.修改数据

UPDATE 表名称

SET 列名称 =新值

[,列名称 =新值, …]

[WHERE 列名称 =条件值];

根据id修改用户密码

UPDATE tb_user SET user_password =’abc’ WHERE id = ‘1’；

5.删除数据

DELETE FROM 表名称

[WHERE 列名称 =值];

根据id删除用户

DELETE FROM tb_user WHERE id = ‘1‘;

6.查询语句

查询（SELECT）语句用于从表中选取数据，结果被存储在一个虚拟的结果表中。

SELECT <列名>

FROM <表名>

[WHERE <查询条件表达式>]

[ORDER BY <排序的列名>[ASC或DESC]]

7.基础查询

查询全部行和列SELECT * FROM tb_user;

查询所有数据

SELECT id, user_name, user_password

FROM tb_user

WHERE user_name=’a’ and user_password=’a123’;

8.模糊查询（LIKE）

SELECT info_title, info_linkman, info_phone

FROM tb_info

WHERE info_title LIKE ‘%培训%’;

通配符：

“%”代表零个或多个字符。

“_”代表一个且只能是一个字符。

9.多表关联查询：

等值连接:返回两个表中所有能匹配的记录

SELECT t.*,i.* FROM tb_type t , tb_info i WHERE t.type_sign = i.info_type and i.info_state = 0 and i.info_payfor = 0andt.type_inter = ‘信息类别’; --t,i为表的别名

左关联：以左表为基础，即使右表中没有匹配，也从左表返回所有的行

SELECT t.*,i.* FROM tb_type t LEFT JOIN tb_info i ON t.type_sign = i.info_type WHERE (条件);

右关联：以右表为基础，即使左表中没有匹配，也从右表返回所有的行

SELECT t.*,i.* FROM tb_type t RIGHT JOIN tb_info i ON t.type_sign = i.info_type WHERE (条件);

全关联：只要其中一个表中存在匹配，就返回行

SELECT t.*,i.* FROM tb_type t FULL JOIN tb_info i ON t.type_sign = i.info_type WHERE (条件);

10.oracle伪列

伪列可以从表中查询，但不能插入、更新和删除它们的值；

ROWID 是表中行的存储地址，该地址可以唯一地标识数据库中的一行；

ROWNUM 是查询返回的结果集中行的序号，可以使用它来限制查询返回的行数,通常我们用来做分页。

11.子查询

子查询是指将一条SQL语句嵌入到另一条SQL语句中。数据库引擎将子查询做为虚拟表执行查询操作。子查询可作为连接语句中的一个表，可作为选择语句中的一个值，也可以是SQL查询子句。

SELECT * FROM (

SELECT a.*, ROWNUM rn FROM (

SELECT * FROM tb_info ORDER BY info_date DESC ) a

WHERE ROWNUM <= 5

) WHERE rn >= 2;

三．DAY3

1.日期函数

日期函数对日期值进行运算，并生成日期数据类型或数值类型的结果

日期函数包括：

ADD_MONTHS:日期加月数

MONTH_BETWEEN:求两个日期间相差的天数

LAST_DAY:求日期所在月的最后一天

ROUND:日期的四舍五入round

TRUNC:截取本年、本月、本季度的第一天trunc

NEXT_DAY:求下一个星期几对应的日期

EXTRAC :Textract获得日期的某个部分，如年月日

Select sysdate,add_months(sysdate,12) from dual;加1年

Select sysdate,add_months(sysdate,1) from dual;加1月

2.转换函数

转换函数将值从一种数据类型转换为另一种数据类型

常用转换函数：

TO_CHAR:按照指定格式转化字符串

TO_DATE:将字符串转换成日期

TO_NUMBER:将数字字符串转换成数字

Select sysdate,to_char(sysdate+7,yyyy-mm-dd HH24:MI:SS’) from dual;加1星期

Select sysdate,to_char(sysdate+1,yyyy-mm-dd HH24:MI:SS’) from dual;加1天

Select sysdate,to_char(sysdate+1/24,yyyy-mm-dd HH24:MI:SS’) from dual;加1小时

Select sysdate,to_char(sysdate+1/24/60,yyyy-mm-dd HH24:MI:SS’) from dual;加1分钟

Select sysdate,to_char(sysdate+1/24/60/60,yyyy-mm-dd HH24:MI:SS’) from dual;加1秒

Select month_between(sysdate,to_date(‘2014_5_5’,’yyyy-mm-dd’))”相差月数” from dual;

3.分组函数

分组函数基于一组行来返回结果/为每一组行返回一个值

（AVG 平均值MIN MAX SUM求和COUNT记录个数）

4.分析函数

分析函数（想为查询的数据编号）

根据一组行来计算聚合值/为每组记录返回多行/用于计算完成聚集的累计排名、移动平均数

Row_number返回连续的序号，不论值是否相等

Rank具有相等值得行排位相同，序号随后跳跃

Dense_rank具有相等值得行排位相同，序号是连续的

给拍好序的查询结果中的每一行返回一个唯一的编号，sal值相同的编号也不同

Select row_number() over(order by sal desc) as 编号,empno,ename,deptno,sal from emp;

按照部门编号分组，组内编号，sal值相同的编号也不同

Select row_number() over(partitipn by depno order by sal desc) as 编号,empno,ename,deptno,sal from emp;

每组内从1开始编号，组内编号，sal值相同的编号相同，后续编号跳过，如1，2，2，4，5，5，7

Select rank() over(order by sal desc) as 编号,empno,ename,deptno,sal from emp;

Select rank() over(partitipn by depno order by sal desc) as 编号,empno,ename,deptno,sal from emp;

每组内从1开始编号，组内编号，sal值相同的编号相同，后续编号连续，如1，2，2，3，4，4，5

Select dense_rank() over(order by sal desc) as 编号,empno,ename,deptno,sal from emp;

Select dense_rank() over(partitipn by depno order by sal desc) as 编号,empno,ename,deptno,sal from emp;

操作符及SQL函数

SQL函数（算数操作符/比较操作符/逻辑操作符/集合操作符/连接操作符）

1.算数操作符

算术操作符用于执行数值计算

可以在SQL语句中使用算术表达式，算术表达式由数值数据类型的列名、数值常量和连接它们的算术操作符组成

算术操作符包括加(+)、减(-)、乘(*)、除(/)

检索出课程号是2的成绩+10分后的结果

Select sid,cid,socre+10 as “lastscore” from t_score where cid=2;

2.比较操作符

比较操作符用于比较两个表达式的值

比较操作符包括 =、!=、<、>、<=、>=、BETWEEN…AND、IN、LIKE和 IS NULL等

检索1980年前出生的学生信息

Select * from t_student where sbirthday < ‘01-1月-1980’;

检索1986年出生的学生信息

Select * from t_student where sbirthday between ‘01-1月-1980’ and ‘31-12月-1986’;

检索班级是1班或2班的学生信息

Select * from t_student where sclass in (1,2); in--or

Select * from t_student where sclass=1 or sclass=2;

3.连接操作符

Select (‘学号为’||sid||’的同学姓名是’||sname) as 学生信息 from t_student;

4.集合操作符

（是纵向合并，与表连接查询相反这是横向连接）

Select sid from t_score where cid=1

Union

Select sid from t_score where cid=2;

Select sid from t_score where cid=1

Union all

Select sid from t_score where cid=2;

交集

Select sid from t_score where score>=60 and cid=1

intersect

Select sid from t_score where score<60 and cid=2;

Select sid from t_score where score>=70 and cid=1

Minus

Select sid from t_score where score>=65 and cid=2;

5.操作符优先级

6.SQL函数

带有一个或者多个参数并返回一个值

1.单行函数：

（1）字符串函数

一行一列

转换成小写

转换成大写

去掉左边字符

去掉右边字符ps：去掉左/右/两边的空格

按字符替换

按字符组替换

插入

截取

链接

/把数字转换成字符/

这种用的不多

这种用的多

（2）数字函数

（3）日期函数

数据库对象

模式

1.数据库对象

（同义词序列视图索引）

1.同义词

1.是现有对象的一个别名

简化SQL语句/隐藏对象的名称和所有者

提供对对象的公共访问

两种类型：

私有同义词：只能在其模式内访问，且不能与当前模式的对象同名

共有同义词：可被所有数据库用户访问（想访问同义词，得具备对原始数据的访问权限）

一创建一个用户（用system/123登录，创建了一个用户test1），查了一下emp，发现访问不了，所以授予权限（链接角色/创建私有同义词和共有同义词/对emp增删改查的权限赋给test1），然后创建了一个私有同义词，再创建一个共有同义词

2序列

步骤

创建测试表

“延迟段”技术

创建序列

插入测试数据nextval（下一个值）和currva（当前值）l

更改修改序列

3视图

（视窗）(存了一个查询语句放在数据库端)（可以当成表来用）

Select * from emp;

Select * from dept;

表链接

Select empno,ename,dname from emp,dept where emp.deptno=dept.deptno;

Select * from (Select empno,ename,dname from emp,dept where emp.deptno=dept.deptno;

) empdept;

步骤

查询

将查询封装到视图中

创建带有order by/group by子句的视图

修改视图的数据

从表（键保留表）update v_dept_emp set ename=’Tom’ where empno=7521;

Select * from v_dept_emp;

Select * from emp;

主表（非键保留表）

（ps:从表是员工/主表是领导）

Foece关键字允许视图有错误即先建视图再建表

4索引

（提高查询效率即书的目录）

普通索引

CREATE INDEX 索引名 ON 表名(索引字段)

create index ix_deptor on depositor(identity) ;

唯一索引

唯一索引确保在定义索引的列中没有重复值

Oracle 自动在表的主键列上创建唯一索引

使用CREATE UNIQUE INDEX语句创建唯一索引

组合索引

组合索引是在表的多个列上创建的索引

索引中列的顺序是任意的,但经常根据某个列查询的话，要将这个列放在前面。

如果 SQL 语句的 WHERE 子句中引用了组合索引的所有列或大多数列，则可以提高检索速度

反向键索引

反转索引列键值的每个字节

通常建立在值是连续增长的列上，这种列创建B数索引会使得数据节点分布不均，但如果将列上的数字反转如1234-à4321，就可以使数据均匀地分布在整个索引上

创建索引时使用REVERSE关键字

四、DAY4

1.使用PL/SQL

PL/SQL 是过程语言(Procedural Language)与结构化查询语言(SQL)结合而成的编程语言

1.1PL/SQL块

DECLARE

v_salary number(7,2);//声明部分:定义变量、游标和自定义异常

BEGIN

select sal into v_salary from emp

where ename = 'SMITH'//可执行部分:包含 SQL 和 PL/SQL 语句

FOR UPDATE OF sal;

if v_salary <1500 then

update emp set sal = sal + 100

where ename ='SMITH';

select sal into v_salary from emp

where ename = 'SMITH';

DBMS_OUTPUT.PUT_LINE('加薪:100 当前薪水'||v_salary);

end if;

commit;

EXCEPTION

when others then//异常处理：出现错误时需要执行的操作

DBMS_OUTPUT.PUT_LINE('出错:'||SQLERRM);

END;

1.2概念

PL/SQL块是构成 PL/SQL 程序的基本单元

PL/SQL块将逻辑上相关的声明和语句组合在一起

PL/SQL块分为三个部分，声明部分、可执行部分和异常处理部分

[DECLARE

declarations]

BEGIN

executable statements

[EXCEPTION

handlers]

END;

1.3 PC/SQL语言特性

代码对大小写不敏感

PL/SQL中的一些特殊符号

符号

说明

赋值操作符

连接操作符

单行注释

/* */

多行注释

<< >>

标签

范围操作符

<>, !=

不等于

1.4 PL/SQL语言特征

PL/SQL 块中的变量和常量

可执行部分可以使用变量和常量，使用前必须在声明部分声明

声明时必须指定数据类型，每行声明一个标识符

声明变量语法：variable_name datatype [(size)] [:= |default init_value] ;

说明

variable_name 变量名称

datatype 变量类型

size 指定变量的范围

init_value 指定变量的初始值

给变量赋值有两种方法：

使用赋值语句 := 或 default

SET SERVEROUTPUT ON; --SQLPLUS环境中设置

DECLARE

v_deptno number(2) := 10; --声明变量的同时赋值

v_dname varchar2(14);

BEGIN

select dname into v_dname from dept

where deptno = v_deptno; --使用select 语句赋值

dbms_output.put_line('部门编号是' || v_deptno ||'的部门名称是: '|| v_dname);

EXCEPTION --异常处理语句

when others then

dbms_output.put_line('出错:'||SQLERRM);

END;

1.5数据类型

标量数据类型

属性数据类型

LOB数据类型

复合数据类型

引用数据类型

1.6控制结构

PL/SQL 支持的流程控制结构：

条件控制

IF 语句

CASE 语句（两种）

循环控制

LOOP 循环

WHILE 循环

FOR 循环

顺序控制

GOTO 语句

NULL 语句

跳转结构

异常定义

异常处理

系统常用异常（2）

用户自定义异常

用户自定义异常机制应用步骤

DECLARE 中声明异常

自定义异常名 EXCEPTION;

根据条件注册异常，即什么情况下将产生自定义的异常

RAISE 自定义异常名

异常处理：

EXCEPTION

WHEN 自定义异常名 THEN

RAISE_APPLICATION_ERROR(error_number,error_message);

error_number:错误号，取值范围-20000到-20999

error_message:错误信息，最大为2048个字节

1.7 PL/SQL 的优点总结

<支持 SQL，在PL/SQL 中可以使用：

数据操纵命令

事务控制命令

游标控制

SQL 函数和 SQL运算符

<支持面向对象编程 (OOP)

<可移植性，可运行在任何操作系统和平台上的Oralce数据库

<更佳的性能，PL/SQL经过编译执行

<与 SQL 紧密集成，简化数据处理。

支持所有 SQL 数据类型

支持 NULL 值

支持 %TYPE 和 %ROWTYPE 属性类型

<安全性，可以通过存储过程限制用户对数据的访问

1.8总结

PL/SQL 是一种可移植的高性能事务处理语言

PL/SQL 引擎驻留在 Oracle服务器中

PL/SQL 块由声明部分、可执行部分和异常处理部分组成

PL/SQL 数据类型包括标量数据类型、属性类型等

控制结构包括条件控制、循环控制和顺序控制

PL/SQL 支持动态 SQL

运行时出现的错误叫做异常

异常可以分为预定义异常和用户定义的异常

2.子程序和程序包

存储过程独立使用/函数参与表达式

2.1过程定义

过程是一组为了完成特定功能的符合数据库程序脚本规范的程序，经编译后存储在数据库中，然后由一个应用程序或其他PL/SQL程序调用。从根本上讲，过程就是命名的PL/SQL程序块

2.1.2过程的创建和执行

创建过程的语法：

CREATE [OR REPLACE] PROCEDURE <procedure_name> --过程名称

[(parameter list)] --参数列表

AS | IS

[local variable declaration] --局部变量声明（不写DECLARE）

BEGIN

<executable statements> --可执行语句

[EXCEPTION --异常处理

exception handlers]

END [procedure_name];

执行过程: (命令行或PL/SQL块)

DECLEAR

BEGIN --PL/SQL块，SQL窗口

procedure_name[(parameters_list)];

END;

2.1.3无参过程

创建过程

CREATE OR REPLACE PROCEDURE sp_helloWorld

BEGIN

DBMS_OUTPUT.PUT_LINE('Hello World!');

END sp_helloWorld;

执行

Begin

sp_helloWorld;

End；

2.1.4带输入参数的过程

（1）创建过程（过程名后带参数-形参）

CREATE OR REPLACE PROCEDURE sp_outputInfo(v_info varchar2)

BEGIN

DBMS_OUTPUT.PUT_LINE(v_info);

END sp_outputInfo;

执行（过程名后带参数-实参）

Begin

sp_outputInfo(‘Good moring!’);

End;

（2）

CREATE OR REPLACE PROCEDURE

find_empsal(v_empno number)

v_ename varchar2(20);

v_sal number;

BEGIN

SELECT ename,sal INTO v_ename,v_sal from emp

where empno= v_empno;

DBMS_OUTPUT.PUT_LINE('雇员的姓名是：'||v_ename||'工资是：'||v_sal);

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('雇员编号未找到');

END find_empsal;

（3）

CREATE OR REPLACE PROCEDURE sp_dept_insert

(v_deptno NUMBER,

v_dname varchar2,

v_loc varchar2)

BEGIN

INSERT INTO dept VALUES(v_deptno,v_dname,v_loc);

COMMIT;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('添加失败！原因为'||SQLERRM);

ROLLBACK;

END sp_dept_insert;

2.1.5输入输出综合过程

创建

CREATE OR REPLACE PROCEDURE

find_empsal_out(v_empno in number,v_ename out varchar2, v_sal out number)

BEGIN

SELECT ename,sal INTO v_ename,v_sal FROM emp WHERE empno=v_empno;

END find_empsal_out;

执行

DECLARE

v_ename varchar2(20); --员工姓名（输出）

v_sal number; --工资（输出）

BEGIN

find_empsal_out(7935,v_ename,v_sal); --7035是输入参数

DBMS_OUTPUT.PUT_LINE('员工姓名：'||v_ename||' 员工工资'||v_sal);

END;

2.1.6同时为输入和输出参数过程

创建

CREATE OR REPLACE PROCEDURE sp_dept_dname_exist

(io_value IN OUT VARCHAR2)

v_count NUMBER;

BEGIN

SELECT COUNT(*) INTO v_count FROM dept WHERE dname=io_value;

IF(v_count>0) THEN

io_value:='存在';

ELSE

io_value:='不存在';

END IF;

END sp_dept_dname_exist;

执行

DECLARE

io_value varchar2(20):=‘ACCOUNTING';

BEGIN

sp_dept_dname_exist(io_value);

DBMS_OUTPUT.PUT_LINE('部门名称ACCOUNTING'||io_value||'!');

END;

2.1.7过程总结

过程参数的三种模式：

用于接受调用程序的值

默认的参数模式

OUT

用于向调用程序返回值

IN OUT

用于接受调用程序的值，并向调用程序返回更新的值

Ps：将过程的执行权限授予其他用户：

--授权给指定用户

GRANT EXECUTE ON find_empsal TO user1;

--授权给所有用户

GRANT EXECUTE ON find_empsal TO PUBLIC;

删除过程：

DROP PROCEDURE find_empsal;

2.2函数

2.2.1函数是可以返回值的命名的 PL/SQL子程序。

函数必须返回且只能返回一个值

CREATE [OR REPLACE] FUNCTION

<function_name> [(param1,param2)]

RETURN <datatype>

IS|AS

[local declarations]

BEGIN

Executable Statements;

RETURN result;

EXCEPTION

Exception handlers;

END [function_name];

2.2.2定义函数的限制：

函数只能接受 IN 参数，而不能接受IN OUT 或OUT 参数

形参不能是 PL/SQL特有的类型

函数的返回类型也必须是数据库类型

2.2.3访问函数的两种方式：

使用 SQL 语句

select 函数 from dual;

使用 PL/SQL 块

begin

end;

2.2.4创建函数

CREATE OR REPLACE FUNCTION func_hello

RETURN VARCHAR2

BEGIN

RETURN '朋友，您好';

END;

2.2.5 SQL语句调用函数

SELECT func_hello FROM DUAL;

2.2.6 PL/SQL语句调用函数

DECLARE

v_info VARCHAR2(30); --声明接收返回值的变量

BEGIN

v_info := func_hello; --调用函数，并接收返回值

DBMS_OUTPUT.PUT_LINE(v_info);

END;

2.2.7根据部门编号返回部门名称

CREATE OR REPLACE FUNCTION f_dept_getname_byno

(v_deptno NUMBER)

RETURN VARCHAR2

v_dname VARCHAR2(14);

BEGIN

SELECT dname INTO v_dname FROM dept WHERE deptno=v_deptno;

RETURN v_dname;

EXCEPTION

WHEN NO_DATA_FOUND THEN

RETURN '错误！该编号的部门不存在！';

END f_dept_getname_byno;

2.2.8使用SQL语句访问函数

select f_dept_getname_byno(10) FROM dual;

2.3过程函数比较

过程

函数

参数模式可以是IN,OUT或IN OUT

参数模式只能是IN模式

在语法规范中不包含 RETURN 子句

在语法规范中必须包含 RETURN 子句

不返回任何值

必须返回单个值

Execute 过程名

PL/SQL块执行

Select 函数名 from dual;

PL/SQL块执行

过程：

创建过程时声明参数（指定输入，输出）

声明本地变量

BEGIN

[给输出参数赋值]

[输出语句]

捕获异常

返回异常信息

END;

函数：

创建函数时声明参数（参数列表）指定返回类型

声明本地变量

BEGIN

给变量赋值

返回变量值

捕获异常

返回异常信息

END;

2.4程序包

程序包是对相关过程、函数、变量、游标和异常等对象的封装

程序包由规范和主体两部分组成

2.4.1程序包组成

程序包规范

CREATE [OR REPLACE]

PACKAGE

package_name

IS|AS

[Public item declarations]

[Subprogram specification]

END [package_name];

程序包主体

CREATE [OR REPLACE] PACKAGE BODY

package_name

IS|AS

[Subprogram bodies]

[Initialization]

END [package_name];

2.4.2创建程序包规范

CREATE OR REPLACE PACKAGE pkg_dept

PROCEDURE sp_dept_insert(v_deptno number,v_dname varchar2,v_loc varchar2); --声明过程

FUNCTION f_dept_getname_byno(v_deptno number) return varchar2; --声明函数

END pkg_dept;

2.4.3创建程序包主体

CREATE OR REPLACE PACKAGE BODY pkg_dept

--过程sp_dept_insert

PROCEDURE sp_dept_insert(v_deptno number,v_dname varchar2,v_loc varchar2)

BEGIN

INSERT INTO dept VALUES(v_deptno,v_dname,v_loc);

COMMIT;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('添加失败，原因为：'||SQLERRM);

ROLLBACK;

END sp_dept_insert;

--函数f_dept_getname_byno

FUNCTION f_dept_getname_byno(v_deptno number) RETURN VARCHAR2

v_dname varchar2(14);

BEGIN

SELECT dname INTO v_dname FROM dept WHERE deptno=v_deptno;

RETURN v_dname;

EXCEPTION

WHEN NO_DATA_FOUND THEN

RETURN '错误！该编号的部门不存在！';

END f_dept_getname_byno;

END pkg_dept;

2.4.4有关子程序和程序包的信息

USER_OBJECTS 视图包含用户创建的子程序和程序包的信息

SELECT object_name, object_type

FROM USER_OBJECTS

WHERE object_type IN ('PROCEDURE', 'FUNCTION',

'PACKAGE', 'PACKAGE BODY');

USER_SOURCE 视图存储子程序和程序包的源代码

SELECT line, text FROM USER_SOURCE

WHERE NAME = ‘FIND_EMPSAL’; --注意名字大写

2.4.5程序包的删除

只删除程序包主体而不删除包规范

DROP PACKAGE BODY package_name;

将程序包全部删除

DROP PACKAGE package_name;

2.4.6程序包的优点

模块化

更轻松的应用程序设计

信息隐藏

新增功能

性能更佳

2.5程序包中的游标

ORACLE中的过程不能直接返回结果集，必须借助REF游标

程序包声明游标案例

CREATE OR REPLACE PACKAGE pkg_cur

TYPE deptcursor IS REF CURSOR;

PROCEDURE sp_getalldept(dept_cur OUT deptcursor);

END pkg_cur;

程序包的主体

CREATE OR REPLACE PACKAGE BODY pkg_cur

PROCEDURE sp_getalldept(dept_cur OUT deptcursor)

BEGIN

OPEN dept_cur FOR SELECT * FROM dept;

END sp_getalldept;

END pkg_cur;

执行(在SQL*PLUS中) ：动态游标只能在包内或客户端程序中使用，不能在plsql块中使用。

SQL>VARIABLE test_cur REFCURSOR;

SQL>EXECUTE pkg_cur.sp_getalldept(:test_cur);

SQL>PRINT test_cur;

2.6总结

子程序是命名的 PL/SQL 块，可带参数并可在需要时随时调用

有两种类型的PL/SQL子程序，即过程和函数

过程用户执行特定的任务，函数用于执行任务并返回值

程序包是对相关类型、变量、常量、游标、异常、过程和函数等对象的封装

程序包由两部分组成，即包规范和包主体

使用程序包的优点是：模块化、更轻松的程序设计、信息隐藏、新增功能以及性能更佳

3触发器

3.1触发器

触发器是当特定事件出现时自动执行的存储过程

特定事件可以是执行更新的DML语句和DDL语句

触发器自动触发，不能被显式调用

触发器的功能：

提高数据的安全性

实现数据审计

实现复杂的数据完整性规则

实现复杂的非标准的相关完整性规则

自动生成数据值

3.2触发器组成

触发器由三部分组成：

触发事件

可能导致触发器被触发的DML 事件和 DDL 事件

触发条件

限制触发器触发的条件

触发操作

包含一些 SQL 语句和代码，它们在发出了触发器语句且触发限制的值为真时运行

3.3触发器分类

按触发事件的不同，触发器分为三大类

3.3.1 DML触发器

DML触发器是指在表上建立的由DML语句触发的触发器

DML触发器分为两类：

行级触发器：

语句级触发器

语法：

CREATE OR REPLACE TRIGGER trigger_name

[OR <INSERT | DELETE| UPDATE[OF column_list]>]

ON [schema.].table_name

[REFERENCING [NEW AS new_alias] [OLD AS old_alias]]

[FOR EACH ROW]

[WHEN (condition)]

PL/SQL Block;

BEFORE和AFTER

DML—BEFORE行级触发器

DML—AFTER行级触发器

DML—BEFORE语句级触发器

DML—AFTER语句级触发器

3.3.2 INSTEAD OF 触发器

INSTEAD OF触发器是定义在视图上而非表上的触发器

INSTEAD OF 触发器只能是行级的，不能是语句级的，定义INSTEAD OF触发器必须加上FOR EACH ROW

INSTEAD OF 触发器不能包含WHEN子句

INSTEAD OF 触发器不能包含BEFORE和AFTER选项

INSTEAD OF触发器语法

CREATE OR REPLACE TRIGGER trigger_name

INSTEAD OF

[OR <INSERT | DELETE | UPDATE [OF column_list]>]

ON [schema].view_name

[REFERENCING [NEW AS new_alias] [OLD AS old_alias]]

<FOR EACH ROW> --必须加

PL/SQL Block;

创建INSTEAD OF触发器

CREATE OR REPLACE TRIGGER tr_v_dept_emp_insteadof

INSTEAD OF INSERT

ON v_dept_emp

FOR EACH ROW

DECLARE

v_temp PLS_INTEGER;

BEGIN

SELECT COUNT(*) INTO v_temp FROM dept WHERE deptno=:NEW.deptno;

IF v_temp=0 THEN

INSERT INTO dept(deptno,dname)VALUES(:NEW.deptno,:NEW.dname);

END IF;

SELECT COUNT(*) INTO v_temp FROM emp WHERE empno=:NEW.empno;

IF v_temp=0 THEN

INSERT INTO emp(empno,ename,deptno)

VALUES(:NEW.empno,:NEW.ename,:NEW.deptno);

END IF;

END tr_v_dept_emp_insteadof;

3.3.3 系统触发器

系统触发器是被ORACLE系统事件自动触发的触发器

启动和关闭数据库

用户登录和退出

DDL操作

系统触发器语法

CREATE [OR REPLACE] TRIGGER trigger_name

<BEFORE | AFTER> system_event

ON <SCHEMA | DATABASE>

[WHEN (condition)]

Trigger_body;

防止用户删除表t_student

CREATE OR REPLACE TRIGGER tr_t_student_ddl

BEFORE DROP ON SCHEMA

BEGIN

IF ora_dict_obj_name= T_STUDENT ' THEN

RAISE_APPLICATION_ERROR(-20003,'表T_STUDENT不允许被删除！');

END IF;

END TRIGGER;

数据库启动和关闭触发器

用户登录和退出触发器

3.4启用、禁用和删除触发器

启用和禁用触发器

ALTER TRIGGER triigger_name DISABLE;

ALTER TRIGGER triigger_name ENABLE;

删除触发器

DROP TRIGGER triigger_name;

3.5查看有关触发器的信息

USER_TRIGGERS 数据字典视图包含有关触发器的信息

SELECT TRIGGER_NAME FROM USER_TRIGGERS

WHERE TABLE_NAME=‘T_STUDENT';

SELECT TRIGGER_TYPE, TRIGGERING_EVENT, WHEN_CLAUSE

FROM USER_TRIGGERS

WHERE TRIGGER_NAME =upper( ‘tr_emp_aft‘);

注意：where条件中名字要大写

3.6总结

触发器是当特定事件出现时自动执行的存储过程

触发器分为 DML 触发器、INSTEAD OF触发器和系统触发器三种类型

DML 触发器的三种类型包括行级触发器、语句级触发器

INSTEAD OF 触发器是定义在视图上的触发器

系统触发器可以是模式级别或数据库级别

0 0