Oracle知识点笔记
1.1 Oracle基本配置
1.1.1 Oracle安装与启动
Oracle安装：用户种类及初始密码
image.png
在oracle10g\11g中默认scott被锁定。
Oracle数据库的启动
a) 启动两个服务Listener/Service
b) “开始-》运行”-》sqlplus或 sqlplusw
1.2 Oracle用户管理
1.2.1 基本命令
用户连接、解锁、锁定
c) conn/connect scott/tiger; system/orcl; sys/orcl as sysdba;
d) 解锁/锁定：alter user 用户名 account unlock/lock;
解锁： alter user scott account unlock 用户解锁
锁定： alter user scott sccount lock 用户锁定
alter user scott account password expire;设置密码过期
用户的查找
e) 显示当前的用户：show user;
f) 显示系统默认的用户：select * from all_users; 所有用户
dba_users 管理员用户
创建新用户
g) 创建用户密码：SQL> create user xxx identified by 密码 default tablespace xx;
h) 更改密码：SQL> alter user xxx identified by 密码;
i) 给用户授系统权限：
i. 授予连接数据库权限：
a) SQL> grant create session to test;
b) grant connect,resource to xxx;
ii. 授予创建数据库表权限：
a) SQL> grant create table to test;
j) 给用户授对象权限：
i. 授予查询表修改等的权限
a) SQL> grant select on dept to test;
b) SQL> grant all on scott.dept to test;
k) 给用户授角色：
i. SQL>grant connect,resource to test;
ii. 管理权限 with grant option 看一下收回权限后，级联授权的能否收回
grant all on emp to xxx with grant option;按照角色授予权限
l) 收回权限
i. SQL> revoke select on dept from test;
ii. SQL> revoke all on dept from test;
iii. 撤销权限 revoke xx on 表名 from 用户
删除用户
m) SQL> drop user test;
n) SQL> drop user xx cascade;
1.2.2 实例
- 创建用户：必需以dba的身份才能创建用户，否则会提示权限不足
a) 例：以scott的身份创建用户
image.png
b) 例：使用system来创建用户
image.png
- 一般以dba的身份去删除某个用户，如果用其他用户去删除用户，则需要有drop user 的权限。在删除用户时注意，如果要删除的用户已经创建了表，那么就需要在删除时带一个参数cascade。
a) 例1，使用scott删除本身
image.png
- 给用户创建权限，我们创建的用户刚开始是什么权限都没有，登录都不可以
a) 使用刚刚创建的chenzhou来登录
image.png
提示缺少权限，登录被取消
b) Oracle中权限的分类
i. 系统权限：用户对数据库的相关权限
ii. 对象权限：用户对数据库的数据对象操作的权限（select,insert,update,delete,all,create index……）
c) Oracle中角色的分类
i. 预定义角色
ii. 自定义角色
d) 给用户赋予connect角色
image.png
再登录：
image.png
e) 给用户赋予resource的角色，用户有了该角色就能够自己建表
image.png
f) 用户自己建表
image.png
g) 如何使用chenzhou用户来访问scott的emp表
i. 首先给用户赋权
image.png
ii. 赋权之后使用该用户来查询scott中的emp表，结果如下：
image.png
iii. 如果要赋予该用户对该表修改权限，则grant update……
iv. 如果想把对该表的所有权限够赋给该用户则 grant all on emp to chenzhou
- 给用户赋权使用grant，收回权限使用revoke
a) 例子：收回chenzhou用户对emp表的查询权限
image.png
再次查询scott中的emp表时：
image.png
- 对权限进行维护
a) 希望chenzhou用户可以去查询scott中的emp表，还希望他可以把这个权限交给别的对象
i. 如果是对象权限，就加入with grant option
例：grant select on emp to chenzhou with grant option
image.png
ii. 如果是系统权限，就加入with admin option
iii. 注：如果A对象把权限赋给B,然后B再赋给C，当A把B的权限revoke后，C拥有的权限也会被回收掉。
- 使用profile管理用户口令
a) 账户锁定
i. 概述：指定该账户登陆时最多可以输入密码的次数，也可以指定用户锁定的时间（天），一般用dba的身份去执行该命令
ii. 例子：指定scott这个用户最多只能尝试3次登陆，锁定时间为2天
create profile lock_account limit failed_login_attempts 3 password_lock_time 2;
alter user scott profile lock_account;
b) 给账户解锁（dba操作）
i. alter user scott account unlock;
c) 终止口令
i. 概述：为了让用户定期修改密码可以使用终止口令的指令来完成（dba操作）
ii. 例子：给chenzhou用户创建一个profile文件，要求该用户每隔10天要修改自家的登录密码，宽限期为2天。
iii. create profile profilename limit password_life_time 10 password_grace_time 2;
alter user chenzhou profile profilename;
d) 口令历史
i. 概述：如果希望用户在修改密码时，不能使用以前使用过的密码，可以使用口令历史，这样oracle就会将口令修改的信息存放到数据字典中，这样当用户修改密码时，oracle就会对新旧密码进行比较，当发现新旧密码一样时，就提示用户重新输入密码。
ii. 例子：
- 建立profile
create profile password_history limit password_life_time 10 password_grace_time 3 password_reuse_time 10;
psssword_reuse_time //指定口令可以重用时间，即10天后就可以重用
- 分配给某个用户：
e) 删除profile
i. 概述：当不需要某个profile文件时，可以删除该文件。
ii. drop profile password_history[cascade]
1.3 基本数据类型及其sql语法
1.3.1 表管理
o) 查询数据库中有哪些表：select * from tab;
p) 查询某张表的结构：desc 表名;
image.png
q) create table users(
userId number primary key,
userName varchar2(10),
password varchar2(20),
addr varchar2(20));
r) 添加字段
alter table users add(pubdate date);
desc users;
alter table users add(age number)
s) 修改字段
alter table users modify(userName varchar2(20));
insert into users values(32,'aa','aa','aa',sysdate,10);
t) 删除字段
alter table users drop(password);
或者
alter table users drop column password
desc users;
u) 给表添加约束
alter table users add constraint ck check(age>10 and age<60)
alter table users drop primary key;
alter table users add primary key(userId)
v) 重新命名
rename users to test;//将users表重新命名为test
drop table users;//删除表结构
truncate table users //删除记录，记录不可恢复，不写日志（快）
delete from emp //删除记录，但可以恢复，写日志
w) 注意：
mysql中一条INSERT语句插入批量数据的写法：INSERT INTO 表名 VALUES ([列值],[列值])), ([列值],[列值])), ([列值],[列值])); Oracle中不支持这种写法，不过可以采用下面 的方式：
INSERT ALL INTO a表 VALUES(各个值1) INTO a表 VALUES (其它值2) INTO a表 VALUES(其它值3) SELECT * FROM b表，如果SELECT * FROM b表会查询出n条数据的话，就会先往a表插入值1对应的各个字段插入n条记录，然后插入值2各个对应的字段n条记录，然后插入值3对应的各个字段n条记录。注意后边跟的SELECT语句可以随意，不过不是把它SELECT出来的内容插入前边的表里，而是起到前边的多个数据每次插入多少行的作用，这个多少行是和后边跟的SELECT语句查出来几条而定的
1.3.2 数据字典
维护系统对象的一套特殊表和视图
user_xxx 用户拥有的 all_xx 用户有权查看 dba_xxx(sys) 所有的信息
1、 查看所有数据字典对象的名称和用途
select * from dictionary;
2、 查看oracle数据库中所有用户
select username from dba_users;
3、 查看scott用户下所有的表
select table_name from user_tables;
4、 查看scott用户所有的视图
select * from user_views
5、查询scott用户有权查看的表
select table_name,owner from all_tables;
4、 创建表，讲解数据类型
Number(m,n) char(n) varchar2(n) date
a>创建新表
b>利用现有的表创建表
create table test2 as select * from usersinfo;
5、事务控制语言（TCL）可以放SQL后面
Insert into dept values(11,null,null);
Insert into dept values(12,null,null);
Savepoint p1;
Insert into dept values(13,null,null);
Insert into dept values(14,null,null);
Savepoint p2;
Insert into dept values(15,null,null);
Insert into dept values(16,null,null);
rollback to p2;
Select * from dept;
Rollback to p1;
Select * from dept;
Rollabck;
Select * from dept;
image.png
image.png
image.png
image.png
Select distinct * from emp where job=’CLERK’;
Select distinct job,ename from emp where job=’CLERK’;
Select distinct job from emp;
image.png
image.png
image.png
Select (empno||’,’||ename||’,’||job) as OUT_PUT from emp;
image.png
image.png
image.png
image.png
image.png
image.png
image.png
image.png
image.png
image.png
image.png
image.png
image.png
image.png
image.png
image.png
1.4 基本SQL查询
1.4.1 简单查询
1 、select 简单查询
select sid,sname from t_student
select * from t_course
select * from t_score
--2班 女生
select * from t_student where sclass = 2 and SSEX='f'
--查询所有班级
select sclass from t_student
2、 select distinct 查询
select distinct sclass from t_student
3、 给列或表达式取别名
select * from t_student;
--从学生表中检索出2班的性别为女性的学生的信息
select * from t_student where SCLASS=2 and SSEX='f';
--从学生表中检索出所有班级
select SCLASS from t_student;
select DISTINCT SCLASS from t_student;
--学号为‘10001’的学生参加了哪些课程的考试
select CID from t_score where SID='10001'
--给学员编号为10002的学生的各门课成绩进行升序和降序的排列
select SCORE from t_score where SID='10002' order by SCORE asc
select SCORE from t_score where SID='10002' order by SCORE desc
--查询所有姓张的学生信息
select * from t_student where SNAME like '张%';
insert into t_student values('10009','张三','m','01-3月-1985'，'13563921205','2')
select * from t_student where SNAME like '张_'
--利用已有的t_student表生成新表
create table t_student_bak1 as select * from t_student
create table t_student_bak2 as select * from t_student where sclass=2
create table t_student_bak3 as select * from t_student where 1=2
1.4.2 连接查询和子查询—重点
1、查询员工以及他所在的部门
select a.*,b.* from emp a,dept b where a.deptno=b.deptno----（内连接）
2、查询所有部门以及员工姓名
select a.ename,b.deptno,b.dname from emp a,dept b where a.deptno(+)=b.deptno
或者
select a.ename,b.deptno,b.dname from dept b left outer join emp a on b.deptno=a.deptno---（左外连接）
select a.ename,b.deptno,b.dname from emp a right outer join dept b on a.deptno=b.deptno----（右外连接）
3、显示员工以及其直接上级
select a.ename 员工,b.ename 经理 from emp a,emp b where a.mgr=b.empno(+) ----(自连接)
4、查询工资高于平均工资的员工
select * from emp where sal>(select avg(sal) from emp) ------（非关联子查询）
先执行子查询，后执行主查询，叫做非关联子查询
5、查询每个部门最高工资的员工
select * from emp a where (select count(*) from emp where deptno=a.deptno and sal>a.sal)=0 ---(关联子查询)
先执行主查询，后执行子查询，将主查询的当作已经查询出来的结果
也可非关联实现
select * from emp where (deptno,sal) in (select deptno,min(sal) from emp group by deptno)
实例：
--内连接（join on/inner join on）：查询学生总体学习情况：学生姓名，课程名，成绩
select SNAME,CNAME,SCORE from t_student a
join t_score b on a.sid = b.sid
join t_course c on b.cid = c.cid
select SNAME,CNAME,SCORE from t_student,t_score,t_course
where t_student.sid = t_score.sid
and t_score.cid = t_course.cid
--查询及格的学生的学习情况：学生姓名，课程名，成绩
select SNAME,CNAME,SCORE from t_student a
join t_score b on a.sid = b.sid
join t_course c on b.cid = c.cid
where SCORE >= 60
--查询有课程的教师的信息
select * from t_teacher
inner join t_teachercourse
on t_teacher.tid = t_teachercourse.tid
--查询所有教师的工作分配情况，有课程的和没课程的老师都要出现在查询结果中（左外连接）
select * from t_teacher --主表
left outer join t_teachercourse --从表
on t_teacher.tid = t_teachercourse.tid
select * from t_teachercourse --从表
right join t_teacher --主表
on t_teacher.tid = t_teachercourse.tid
--全联接(full join 结果集中除了满足联接条件的记录外,还有左、右表中不满足条件的记录) 左连接和外连接的一个组合，先执行左连接，再执行右连接，删掉重复记录
select * from t_teacher
full join t_teachercourse
on t_teacher.tid = t_teachercourse.tid
--学生和课程有多少可能的组合------------
--交叉连接（cross join ）-------------
select * from t_student
cross join t_course
select * from t_student，t_course
----------子查询----------
--查询比张老师年龄大的教师信息
select * from t_teacher where tage > （select tage from t_teacher where TNAME='张老师'）
--查询参加过课程编号为1的考试的学生信息
select * from t_student where sid = (select sid from t_score where cid=1)---失败
select * from t_student where sid in (select sid from t_score where cid=1)
--查询没有参加过课程编号为1的考试的学生信息
select * from t_student where sid not in (select sid from t_score where cid=1)
--查询所有已经安排教师上课的课程信息
select * from t_course tc where cid in (select cid from t_teachercourse )
select * from t_course tc where exists (select * from t_teachercourse ttc where ttc.cid = tc.cid)
--查询所有没安排教师上课的课程信息
select * from t_course tc where not exists (select * from t_teachercourse ttc where ttc.cid = tc.cid)
--在成绩表中查询出所有学生的最高平均分(from后面的子查询)
select max(avgscore) from (select avg(SCORE) as avgscore from t_score group by sid)
--经典应用分页查询（rownum）
--查询成绩表的前5条记录
select * from t_score where rownum>=1 and rownum<=5
select * from (select rownum as num,SID,CID,SCORE from t_score) where num>=1 and num<=5
--查询成绩表的6-10条记录
select * from t_score where rownum>=6 and rownum<=10--错误，没有数据
select * from (select rownum as num,SID,CID,SCORE from t_score) where num>=6 and num<=10
1.4.3 层次查询
1、显示员工领导关系
select lpad(ename,5*level,'+') from emp connect by prior empno=mgr start with ename='KING'
或者start with mgr is null
connect by 用于执行记录之间的父子关系，start with 用于指定从哪个节点记录开始遍历访问 Level 在整个查询记录中的层次
2、查询BLAKE所领导团队工资总额
select sum(sal) from emp connect by prior empno=mgr start with ename='BLAKE'
1.5 操作符及其SQL函数
1.5.1 操作符
--算术操作符（加(+)、减(-)、乘(*)、除(/)）：检索出课程号是’2’的成绩+10分后的结果
select sid,cid,score+10 as "lastScore" from t_score where cid=2
----------逻辑操作符：and or not
----------检索班级是1班或2班的学生信息
select * from t_student where SCLASS=1 or SCLASS=2
----------检索班级是1班的80后学生信息
select * from t_student where SCLASS=1 and SBIRTHDAY between '1-1月-1980' and '31-12月-1989'
比较操作符（包括 =、<>、<、>、<=、>=、BETWEEN…AND、IN、LIKE 和 IS NULL）：
----------检索班级是1班或2班的学生信息
select * from t_student where SCLASS in(1,2)
--查询 1班的 80 后的学生信息
select * from t_student where SCLASS=1 and SBIRTHDAY between '1-1月-1980' and '31-12月-1989'
----------检索班级不是1班的80后学生信息
select * from t_student where SCLASS<>1 and SBIRTHDAY between '1-1月-1980' and '31-12月-1989'
-- 检索1986 年出生的学生信息
select * from t_student where SBIRTHDAY between '1-1月-1986' and '31-12月-1986'
-- 检索1980 年以前出生的学生信息
select * from t_student where SBIRTHDAY < '1-1月-1980'
--连接运算符:用于将多个字符串或数据值合并成一个字符串
--查询学生信息（把学号和姓名合并成一个列）
select (sid || sname) as "学生信息" from t_student
select ('学号为' || sid || '的学生的姓名是'|| sname) as "学生信息" from t_student
------集合操作符：将两个查询的结果组合成一个结果 ppt9
-------union、union all、intersect、minus
------统计学习操作系统(1)或数据结构(2)的同学学号（union：返回两个查询的不重复的所有行)
select sid from t_score where CID='1'
union
select sid from t_score where CID='2'
-------统计java web(5) 和java框架(6)都为及格的同学学号
-------intersect：相当于对2个查询结果集取交集，也就是只返回两个查询结果集的公共行
select sid from t_score where cid='5' and score >= 60
intersect
select sid from t_score where cid='6' and score >= 60
select * from t_score where cid='5'
update t_score set score='65' where sid='10002' and cid='6'
--统计操作系统(1)70 分及以上但数据结构(2)未达到 65 的同学学号 --10004
select sid from t_score where cid='1' and score > 70
minus
select sid from t_score where cid='2' and score >= 65
select * from t_score where sid='10003'
1.5.2 集合运算
A集合 (1、2、3)
B集合 (2、3、4)
交集：(2、3)
并集：(1、2、3、2、3、4)或(1、2、3、4)
差集：A-B(1) B-A(4)
create table a(id number);//插入1，2，3
create table b(id number);//插入2，3,4
1、 交集
select * from a intersect select * from b
2、并集
select * from a union all select * from b(去掉重复的记录则去掉all)
3、差集
select * from a minus select * from b (A-B)
select * from b minus select * from a (B-A)
1、查询部门10和部门20都有的工作类型
select job from emp where deptno=10 intersect select job from emp where deptno=20;
2、查询部门10的办事员和部门20的经理
select * from emp where deptno=10 and job='CLERK' union all select * from emp where deptno=20 and job='MANAGER'
3、查询部门30中有，而部门10中没有的工作类型
select job from emp where deptno=30 minus select job from emp where deptno=10;
1.5.3 常用函数
1 、聚合函数
count() sum() avg() max() min()
实例：
--------------------------------------分组聚合查询--------------------------------------------
--Min ：最小值
--Max ：最大值
--Sum ：求和
--Avg ：求平均值
--Count：计数
--查询课程表中最多和最少的课时数
select MAX(CHOURS) as 最多的课时数,MIN(CHOURS)as 最少的课时数 from t_course
select * from t_course
--查询当前的学生数
select count(*) from t_student
--查询成绩表中每位同学的最高分，最低分，总分，平均分
select sid,max(SCORE),min(SCORE),sum(SCORE),avg(SCORE) from t_score group by sid
--统计平均成绩超过75分的学生的最高分，最低分，总分，平均分
select sid,max(SCORE),min(SCORE),sum(SCORE),avg(SCORE) from t_score group by sid having avg(SCORE) >75
--group by :select后面的这些字段，要么是在聚合函数里面，要么是在group by字句中，否则回报错
select sid,max(SCORE),min(SCORE),sum(SCORE),avg(SCORE) from t_score group by sid having avg(SCORE) >75
2 、字符函数 （参考PPT演示）
ltrim() 左侧截取
rtrim() 右侧截取
translate('jack','a' ,'b') 结果back
lpad(char1,n[,char2]) 使用char2字符补充在char1字符的左边，最终补足n个字符，如果没有char2字符，则用空格补足n个字符
length () select length(‘abcdefg’) from dual
DECODE(input_value,value,result[,value,result…][,default_result]);
实例：
----------字符函数
select lower('FUN') from dual;--转换为小写
select upper('fun') from dual; --转换为大写
select ltrim(' abcd ') from dual;--abcd
select rtrim(' abcd ') from dual;-- abcd
select trim(' abcd ') from dual;--abcd
select replace ('jack and jue','j','bl') from dual;
3 、数学函数
round[number,[decimal_places]] 四舍五入函数，将number按照指定小数位数进行四舍五入运算的结果
select round(3.567) from dual;四舍五入取整
select round(123.456, 0) from dual; 回传 123
select round(123.456, 1) from dual; 回传 123.5
select round(123.456, 2) from dual; 回传 123.46
select round(123.456, 3) from dual; 回传 123.456
select round(-123.456, 2) from dual; 回传 -123.46
4 、转换函数
to_char(d[,fmt]) 也可number转换 将date数据类型的d转换成字符串类型数据
to_date(字符串，格式)
其他函数
nvl(a,b) 如果a不为null 则返回a,如果a为null则返回b，注意两者的类型要一致
nvl2(a,b,c) ,如果a不为null 则返回b,如果a为null则返回c; b和c类型不同的话，c会转换为b的类型
NULLIF (expr1, expr2) ->相等返回NULL，不等返回expr1
COALESCE (expr1, expr2, ..., exprn) ->返回第一个不为NULL的表达式，各个表达式类型一致
1.5.4 日期函数
系统时间sysdate
add_months(d,no_of_month) 为日期d加上no_of_month月份
select add_months(sysdate,2) from dual;
months_between(d1,d2)返回日期d1和d2之间的月份数
例子：当前时间跟当前时间加上两个月的差额
last_day(d)d所在月份最后一天
例子：查询当前时间所在月份最后一天日期
to_date(‘字符串’,’日期格式化’)
例子：select to_date(‘20100302’,’yyyymmdd’) from dual;
next_day(d,day) 返回d后工作日日期 day取值范围 1—7（周日、周一、周二……周六）
select next_day(sysdate,3) from dual; 返回下周二
例子：
1、查询系统时间(sysdate)
select sysdate from dual
2、查询在12年前参加工作的员工(months_between(d1,d2)返回日期d1和d2之间的月份数)
select empno,ename,hiredate from emp where months_between(sysdate,hiredate)>144
3、查询在当月倒数第三天参加工作的员工（last_day(d)d所在月份最后一天）
select empno,ename,hiredate from emp where last_day(hiredate)-2=hiredate
4、查询每个员工的工作天数（trunc(n[,m])返回截尾取整到小数点后m位的数）
select empno,ename,hiredate,trunc(sysdate-hiredate) from emp
5、显示系统时间为xxxx年xx月xx日，是一年中第几天（DDD），是星期几(DAY)(to_char(d，str)将d显示为str指定的格式)
select to_char(sysdate,'yyyy"年"月"dd"日"DDD DAY') from dual
6、计算每个员工已经工作了多少个月，忽略小数部分(months_between)
select ename,trunc(months_between(sysdate,hiredate) from emp;
7、查询在1987年2月到1987年5月参加工作的员工(to_date(str,formatstr)将str格式字符串日期转换成formatetr指定的格式日期)
select ename,hiredate from emp where hiredate>=to_date('19870201','yyyymmdd') and
hiredate<to_date('19870601','yyyymmdd')
实例：
----------日期函数
select sysdate from dual
select sysdate+1 from dual --不建议
select add_months(sysdate,1) from dual -- 加一月
select add_months(sysdate,12) from dual -- 加一年
--to_char
1.5.5 统计函数
1、统计部门最低工资大于900的部门和最低工资
select deptno,min(sal) from emp group by deptno having min(sal)>900
2、统计每个部门工资在1400元以上的所有员工的工资总额
select deptno,sum(sal) from emp where sal>1400 group by deptno
3、统计不同工作的个数
select count(distinct job) from emp
1.6 数据库对象
1.6.1 序列
1、 创建
create sequence seqa start with 1000 increment by 2;
2、 得到序列自增的数字
select seqa.CURRVAL from dual;(得到当前序列数值)
select seqa.NEXTVAL from dual;（得到序列下一个数值）
3、 插入某表
insert into test values(seqa.NEXTVAL,’aa’);
要插入开始是字母的可以
insert into test values(‘c’||trim(to_char(seqa.nextval,’0000’)),’aa’);
4、 删除序列
drop sequence seqa;
1.6.2 伪列
rowId,rownum伪列
完成分页
1.7 PL/SQL
declare
cursor cemp is select empno,sal from emp order by sal;
pempno emp.empno%type;
psal emp.sal%type;
--涨工资的人数:
countEmp number := 0;
--涨后的工资总额:
salTotal number;
begin
--得到初始的工资总额
select sum(sal) into salTotal from emp;
open cemp;
loop
--1\. 总额 > 5w
exit when salTotal > 50000;
--取一个员工
fetch cemp into pempno,psal;
--2\. notfound
exit when cemp%notfound;
--涨工资
update emp set sal=sal*1.1 where empno=pempno;
--人数+1
countEmp := countEmp + 1;
--2\. 涨后=涨前 + sal * 0.1
salTotal := salTotal + psal * 0.1;
end loop;
close cemp;
commit;
dbms_output.put_line('人数:'||countEmp||' 总额:'||salTotal);
end;
select empno,sal from scott.emp order by sal;
1.8 游标的使用
1 、游标：
%notfound 没有找到数据为真，找到为假
%found 相反
%rowcount 统计影响的行数
2 、显示游标 sql；
更新指定员工工资加500
declare
eno emp.empno%type;
jr emp%rowtype;
begin
eno:=&员工编号;
update emp set sal=sal+500
where empno=eno;
if sql%notfound
then
dbms_output.putline('没有发现你要找的数据');
else
dbms_output.putline(‘更新完毕’);
end if;
end;
3 、隐式游标
游标声明、打开、检索、关闭
声明
cursor 游标名{(形参声明)}
is/as
select 语句;
打开
open 游标名{(实参)};
检索
fatch 游标名 into 变量;
关闭
close 游标名;
例如
每个部门平均工资
declare
dno number;
avgsal number;
cursor cur_1
is
select deptno,avg(sal) avgsal from emp
group by deptno;
begin
open cur_1;
loop
fatch cur_1 into dno,avgsal;
exit when cur_1%notfound;
dbms_output.putline('编号:'||dno||'平均工资：'||avgsal);
end loop;
close cur_1;--关闭
end;
显示指定工作的员工姓名和工资
declare
empjob emp.job%type;
empname varchar2(20);
salary number;
cursor cur2(work varchar2)
is
select ename,sal from emp
where job=work;
begin
empjob:='&输入一个工作种类';
open cur2(empjob);
loop
fatch cur2 into empname,salary;
exit when cur2%notfound;
dbms_output.putline('姓名:'||empname||'工资：'||salary);
end loop;
close cur2;
end;
1.9 触发器
触发器
tnew insert udate
told update dalete
create (or replace) trigger 触发器名
before/after insert/upodate(of 列名）/delete
on 表名/视图名
[for each row]--行级触发器
begin
sql 语句
end;
级联删除
create or replace trigger tri_1
after delete from emp
where deptno=:old.deptno;
end;
delete from dept
where deptno=10;
级联更新
create or replace trigger tri_update
after update of deptno on dept
for each row
begin
update emp set deptno=:new deptno
where deptno=:old deptno;
end;
update dept set deptno=70
where deptno=30;
函数
create {or replace} function 函数名{（形参）}
return 返回值类型
is
声明部分
begin
语句1
end；
例子
create or replace function fun
return vachar2
is
begin
return ‘hello world!’;
end;
select fun from duil;
求工资税函数
create or replace function tax(money number)
return number
is
sal_rate number;
begin
if money<=3500 then
sal_rate:=0;
else if money<=5000 then
sal_rate:=(money-3500)*0.03;
else if money<=8000 then
sal_rate:=(5000-3500)*0.03+(money-5000)*0.1;
else .....
end if;
end if;
end if;
return sal_rate;
end;
1.10 存储过程
存储过程
create （or replace) procedure 存储过程名（形参）
is/as
（声明部分）
begin
语句
（异常处理部分）
end;
用存储过程实现某个工作地点的员工姓名及工资
create or replace procedure p_emp_dept(loca varchar2)
is
empname varchar2(20);
salary number;
cursor c_sal(location varchar2)
is
select ename,sal from emp
where deptno in(select deptno from dept where loc=location);
begin
open c_sal(loca);
dbms_output.putline('姓名:'||'工资：');
loop
fatch c_sal into empname,salary;
exit when c_sal%notfound;
dbms_output.putline(empname||salary);
end loop;
close c_sal;
end;
begin
p_emp_dept(‘纽约’);
end;
tobehero666.png
