一.DAY1
1.序列触发器
----------------------网上查的资料
ORACLE中只能用序列来增长,不可能实现自动增长的 除非你用触发器,
1. 用alert语句给表新增一个字段,然后用序列给这个字段赋值,有多少条记录就赋多少,这个相信你自己也字段怎么做
2.写一个insert触发器,插入记录的同时更新该条记录的新字段信息 用序列
语法:
CREATE OR REPLACE TRIGGER trigger_name
<BEFORE | AFTER>
<INSERT | DELETE| UPDATE[OF column_list]>
[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.是现有对象的一个别名
简化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
AS
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)
AS
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)
AS
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)
AS
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)
AS
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)
IS
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过程总结
过程参数的三种模式:
IN
用于接受调用程序的值
默认的参数模式
OUT
用于向调用程序返回值
IN OUT
用于接受调用程序的值,并向调用程序返回更新的值
Ps:将过程的执行权限授予其他用户:
--授权给指定用户
GRANT EXECUTE ON find_empsal TO user1;
--授权给所有用户
GRANT EXECUTE ON find_empsal TO PUBLIC;
删除过程:
DROP PROCEDURE find_empsal;
2.2函数
函数必须返回且只能返回一个值
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
IS
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
AS
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块执行 |
过程: 创建过程时声明参数(指定输入,输出) AS 声明本地变量 BEGIN [给输出参数赋值] [输出语句] 捕获异常 返回异常信息 END; | 函数: 创建函数时声明参数(参数列表) 指定返回类型 AS 声明本地变量 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
AS
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
AS
--过程sp_dept_insert
PROCEDURE sp_dept_insert(v_deptno number,v_dname varchar2,v_loc varchar2)
AS
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
AS
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
AS
TYPE deptcursor IS REF CURSOR;
PROCEDURE sp_getalldept(dept_cur OUT deptcursor);
END pkg_cur;
程序包的主体
CREATE OR REPLACE PACKAGE BODY pkg_cur
AS
PROCEDURE sp_getalldept(dept_cur OUT deptcursor)
IS
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
<BEFORE | AFTER>
<INSERT | DELETE| UPDATE[OF column_list]>
[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
<INSERT | DELETE | UPDATE [OF column_list]>
[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 触发器是定义在视图上的触发器
系统触发器可以是模式级别或数据库级别