oracle数据库学习记录（持续更新中...）
--------------------------------------------day1-------------------------------------------------
1.为什么要使用数据库
2.什么是数据库
3.数据库的三层结构 https://www.cnblogs.com/wangjian920110/p/5454969.html4.主流数据库
微软：SqlServer和access（小巧、免费、数据量不大）
Mysql（开源）轻量级数据库
Ibm：db2（海量数据的存储）
oracle：大型数据库、比较安全
sysbase：专注于linux下的开发，金融领域较多
思考：（1）项目中如何选择数据库？
1.项目标的（预算）
2.功能要求
3.安全性和稳定性
4.并发（用户数量）
5.操作系统）（linux Unix Windows Macos）
oracle的服务：
5.表空间 http://blog.csdn.net/z69183787/article/details/23526423
SYSAUX SYSTEM TEMP USER UNDO分别对应oradata目录下的五个DBF文件
数据库实例：
6.sys system用户权限
conn 切换用户
disc 断开用户
passw user system 修改密码
show user 查看当前用户
exit 退出数据库
desc +表名：查看表结构
7.交互式命令
（1）&可以替代变量，变量执行时需要输入
select * from emp where deptno = '&deptno'; 查找表
（2）ed[it]:编辑指定sql文件的内容
（3）spool可以将屏幕sqlplus上的内容输入到文件中
spool off 结束输入
--------------------------------------------day2-------------------------------------------------
---创建表空间
---问题K创建一二个名为hp的表空间，指定数据名为hp.dbf，大小为10M
create tablespace hp
datafile 'G:\oracle_project\hp\hp.dbf'
size 10M
autoextend on next 5M
maxsize unlimited;
drop tablespace hp ---删除表空间
---创建用户
--create user 用户名 identified by 密码 [default tablespace 表空间名] [temporary tablespace temp]（临时表空间）
create user hope identified by 123456; --用户+密码
create user hope1 identified by 123456 default tablespace users;--用户hope1+密码123456+表空间users
create user hope2 identified by 123456 default tablespace users temporary tablespace temp;
---修改用户密码
alter user hope identified by 654321;
----删除用户
drop user hope;
drop user hope cascade; ---删除相关数据
--赋予权限和角色
--系统权限允许用户执行某些数据库的操作
--对象权限：允许用户对某一特定对象执行特定操作
--角色是具有名称的一组权限的组合
--connect：临时用户
--resource：更为可靠和正式的用户
--dba：数据库管理员角色
--一般情况下，普通用户，有connect和resource权限就足够了
--赋予权限
grant connect to hope2 --connect角色
grant resource to hope2;
grant connect,resource to hope2;--同时赋予两个角色
grant select,update on scott.emp to hope2;----给hope2赋予查询和修改scott的权限
--撤销权限和角色
revoke resource from hope2;
revoke connect,resource from hope2;---同时撤销两个角色
---修改表
update scott.emp set sal=5000 where
---oracle的数据类型
--cahr：可以存储定长的字符串 char(200) 存储10个剩下的空格补齐 解析快
--varchar:可以存储变长的字符串
--varchar2：可以存储变长的字符串 carchar2(200)存储10个剩下的闲置
--如何选择char 还是varchar2：已知字符串长度用char，不确定字符串长度用varchar2
--nchar和nvarchar2可以存储unicode的字符集
---number ==number(38,0) 默认38位整数，
--number(p) ==number(15,0) p-->整数位数，
--number(p,s) ==number(15,2) p--->总位数，s---->小数点后位数 存储整型和浮点型
--PS：不建议使用varchar，Integer，float，double
--日期数据类型：
--sysdate：日期函数：显示当前日期
select sysdate from dual;--显示系统当前日期
--systimestamp:比date更精确的一个时间
select systimestamp from dual;
---to_date(字符串，格式)
select to_date('2018-01-05','yyyy-MM-dd') from dual;--将一个字符串转化为日期格式
--Date：日期类型，存储日期和时间
--TimeStamp：比Date更精确的一种日期类型
--默认的日期格式：
--中文的oracle： 日-月-年 例1998年7月1日--->'01-7月-1998'
--英文的oracle：day-mon-year 例1998年7月1日--->'01-JUL-1998'
--LOB数据类型
--BLOB：存储二进制对象，图像、音频、视频
--CLOB：存储字符格式的大型数据
--创建表：解决表结构的问题：表有几个字段，每个字段的长度是多少，每个字段是什么数据类型
create table student(stunum number(4),stuname varchar2(10),age number(2),addr varchar2(50));
--问题：往表中插入数据可能会出现的问题，比如：重复插入数据，内容不正确，格式不正确
--如何保证数据库表中数据的完整性和一致性？
--常见的约束： 主键（primary key） 外键（foreign key） 唯一（unique） 非空（not null） 默认约束（default）
--用户自定义（check）
--什么是主键约束？唯一非空
--给表定义一个主键，主键是用来保证表记录的唯一非空
--建表时添加主键
create table student(stunum number(4) primary key,stuname varchar2(10),age number(2),addr varchar2(50));
--查找
insert into student values(1001,'tong',20,'nanyang');
--再次插入相同数据或者主键为null，违反主键约束，不允许插入
--创建课程表
create table course(cno number(4) primary key,cname varchar2(20),cscore number(4));
--创建一张成绩表 主键：学号+课程号 称为联合主键
--一张表只能有一个主键
create table score(stunum number(4),cno number(4),score number(5,2),constraint pk_score primary key (stunum,cno));
create table course(cno number(4) constraint pk_course primary key ,cname varchar2(20),cscore number(2));
作业：
1.思考：删除表空间时如何删除关联的数据文件
drop tablespace hp including contents and datafiles;
--------------------------------------------day3-------------------------------------------------
1.主键、外键、唯一、非空、默认、用户自定义约束是什么，什么作用，怎么用
2.建表时如何创建约束？
3.如果建表时未添加约束，建好表后，如何添加？
4.Oracle中如何修改表结构
5.序列是什么，什么用，怎么用，注意哪些
1.主键：保证字段的唯一非空，一张表只能由一个主键，一个主键可以由多个字段组成，称为联合主键
create table test(id number(4) primary key,name varchar2(20));
student(sno,sname,age,addr,idcard); --主键sno
course(cno,cname,cscore); --主键cno
score(sno,cno,score);--主键(sno,cno) 外键 sno cno
--外键作用？如果Student表中没有1001学生信息，那么score表中也不应有该学生成绩。
--往成绩表插入数据时，如果学生表中么有该学号，或者课程表中没有该课程，不允许插入
create table student(sno number(4) primary key,
sname varchar2(20),
age number(3),
addr varchar2(50),
idcard number(18));
create table course(cno number(4)primary key,
cname varchar2(20),
cscore number(2));
drop table score cascade constraints;
第一种
create table score(
stuno number(4) references student(sno), --创建第一各外键约束
couno number(4) references course(cno), --创建第二个外键约束
score number(5,2),
constraint pk_score primary key(stuno,couno)); --创建一个主键约束
第二种：
create table score(
stuno number(4)constraint fk_stuno references student(sno),
couno number(4)constraint fk_couno references course(cno),
score number(5,2),
constraint pk_score primary key(stuno,couno));
第三种：
create table score(
stuno number(4),
couno number(4),
score number(5,2),
constraint fk_stuno foreign key(stuno) references student(sno),
constraint fk_couno foreign key(couno) references course(cno),
constraint pk_score primary key(stuno,couno));
--总结2：作为外键的字段名并不要求和被参考表一致
--总结3：定义外键约束后，往表中插入数据会参考父表中的值，只有父表中存在改值才会成功
insert into student values(1001,'zhangsan',19,'henan',55555555);
--查看student表记录
select * from student;
--向course表中插入数据
insert into course values(999,'java基础',4);
--查看course表记录
select * from course;
--总结4：定义外键约束后，删除父表中内容前需要先将相关子表记录删除。
delete from student where sno=1001; --删除不掉，违反完整约束条件
--正确做法
delete from score where stuno=1001; --先删子记录
delete from student where sno=1001; --再删父记录
唯一约束：
第一种
create table student(sno number(4) primary key,
sname varchar2(20) not null,
age number(3),
addr varchar2(50),
idcard number(18) unique);
第二种
create table student(sno number(4) primary key,
sname varchar2(20) not null,
age number(3),
addr varchar2(50),
idcard number(18)constraint uk_idcard unique);
第三种
create table student(sno number(4) primary key,
sname varchar2(20) not null,
age number(3),
addr varchar2(50),
idcard number(18),
constraint uk_idcard unique(idcard));
create table student(sno number(4) primary key,--主键约束
sname varchar2(20) not null, --非空约束
age number(3) default 18, --默认约束，默认18
addr varchar2(50),
idcard number(18) unique); --唯一约束
create table student(sno number(4) primary key,--主键约束
sname varchar2(20) not null, --非空约束
age number(3) default 18 check(age between 10 and 45), --默认约束，默认18,自定义约束，年龄在10--45
addr varchar2(50),
idcard number(18) unique); --唯一约束
--定义性别为男或女
create table student(sno number(4) primary key,
sname varchar2(20) not null,
age number(3) default 18 check(age between 10 and 45),
gender char(10) check(gender in('male','female')),
addr varchar2(50),
idcard number(18) unique);
--总结1：定义了完整性约束后，输入的数据要符合约束的要求，否则不能插入
--给student表添加字段regdate类型是date
alter table student add regdate date;
alter table student add province varchar2(350);
--删除刚添加的字段
alter table student drop column regdate;
--删除多列
alter table student drop (province,regdate);
--修改字段类型和长度
alter table student modify idcard char(20);
--修改表名student -->stu
alter table student rename to stu;
--修改字段名
alter table stu rename column gender to sex;
--添加主键约束
alter table student add constraint pk_sno primary key(sno);
--添加唯一约束
alter table student add constraint uk_idcard unique(idcard);
--添加非空约束
alter table student modify sname not null;
--添加默认约束
alter table student modify age default 18;
--添加检查约束
alter table student add constraint ck_age check(age between 18 and 50);
--给表添加外键约束
create table score(
sno number(4),
score number(5,2));
alter table score add constraint fk_sno foreign key(sno) references student(sno);
alter table score disable constraint fk_sno;
--启用某个约束
alter table score enable constraint fk_sno;
--删除约束
alter table score drop constraint fk_sno;
--删除student主键约束
alter table student drop primary key;
alter table score drop constraint pk_sno;
序列：有序的数字组成的一个集合 1 2 3 4 5 6 7
序列，序列是一个独立的数据库对象，作用是生成主键
--创建序列：
create sequence seq01 --序列名
start with 3 --序列起始值
increment by 1 --序列的步长
maxvalue 9999 --最大值
minvalue 0 --最小值
nocycle --nocycle（序列不循环） cycle（序列循环使用）
cache 20; --cache(为了加快序列的生成速度，每次生成20个值放在缓存中)
--nocache(不向缓存中存放序列值，使用一次生成一次)
--currval：返回序列的当前值不会引起序列的自增
--nextval：返回序列的下一个值会引起序列的自增
--查看序列的下一个值，会引起序列自增
select seq01.nextval from dual; --第一次使用序列，必须使用序列名.nextval
--查看序列的当前值
select seq01.currval from dual;
--主键的不连续
insert into student values(seq01.nextval,'zhang',18,'male','河南南阳',111111111);
--删除序列
drop sequence seq01;（序列名）
--------------------------------------------day4-------------------------------------------------
--将表emp的结构复制给emp1（不复制表内容，只复制表结构，也不复制表的约束）
create table emp1 as select * from emp where 1=2;
--复制emp表（内容结构都复制,不复制约束）
create table emp2 as select * from emp;
--扩展：创建一张表，只复制表的几个字段ename、sal、job
create table emp3(ename,sal,job) as select ename,sal,job from emp;
--扩展：创建表emp4，复制emp表中部门10中的员工信息
create table emp4 as select * from emp where deptno=10;
--1.sql的简介
--SQL:Structured Query Language 结构化查询语言的简称
--Oracle:C/S 客户端发起请求，通过网络传递给服务器，服务器对请求进行相应，将结果传递给客户端并显示
--2.sql组成
--DDL(数据定义语言Data Definition Language)：create alter drop 针对数据库对象的操作
--create user create tablespace create table create sequence
--create view create index create package create trigger create procedure
--注意：DDL语言使用的时候不需要提交，系统会自动提交
--DML语言(数据操纵语言data manipulation language)：select update insert delete针对表中的数据的操作
--DCL语言(数据控制语言data control Language)：控制存取权限 grant revoke
--TCL(事务控制语言 Transaction Control Language)：commit rollback savepoint
--注意：事务控制语言主要是针对DML操作
--3.DML语言
--(1)insert
--向emp1中插入记录
insert into emp1 values(1001,'shelly','manager',7839,date'2018-1-09',3000,null,10);
--给五个字段插入
insert into emp1(empno,ename,sal,job,deptno)values(1002,'ellen',4000,'salseman',20);
--问题1：从emp表中复制20部门员工信息放入到emp1表中
insert into emp1 select * from emp where deptno=20;
--问题2：往emp1表中插入一条姓名为ellen记录，其余内容跟emp1表中的其他内容不变，empno在原来的基础上+1000
insert into emp1(empno,ename,job,sal,deptno) select empno+1000,ename,job,sal,deptno from emp1 where ename='ellen';
insert into emp1(empno,ename,job,sal,deptno) select empno+1000,ename,job,5000,deptno from emp1 where ename='ellen';
insert into emp1(empno,ename,job,sal,deptno) select 3001,'lily','anal',3000,30 from dual;
--等价于insert into emp1(empno,ename,job,sal,deptno) value(3001,'lily','anal',3000,30);
--插入多条数据
insert into emp1(empno,ename,sal,job)
select 9999,'lilei',3500,'mana' from dual union
select 9998,'lucy',4500,'mana' from dual union
select 9997,'hanmei',5500,'mana' from dual;
--union:Oracle的集合运算，求并集,使用时会去重
--union all:求并集运算，不去重
insert into emp1(empno,ename,sal,job)
select 9999,'lilei',3500,'mana' from dual unionall
select 9998,'lucy',4500,'mana' from dual unionall
select 9997,'hanmei',5500,'mana' from dual;
--(2)delete
--删除表中所有数据(不删表结构)
delete from emp1;
--删除表中的某几行记录
--问题:删除emp1表中部门编号为10的信息
delete from emp1 where deptno=10;
--（3）update
--问题：更新emp1表中员工的工资，如果工资小于1500，则给此员工涨1000
update emp1 set sal=sal+1000 where sal<1500;
--(4)select
---（1）查询表中所有字段
select * from emp1;
---(2)查询emp1表中员工的姓名，工资，奖金，姓名编号
select ename,sal,comm,deptno from emp1;
---(3)查询emp1表中部门30中员工的
select ename,sal,comm,deptno from emp1 where deptno=30;
--注意事项：
---（1）字段的数据类型，长度、精度都要符合表结构的要求
---（2）插入数据的个数应该与字段的个数一致
---（3）插入数据时要注意日期格式的处理
----日期格式的处理
----date声明
----（1）insert into emp1 values(1001,'shelly','manager',7839,date'2018-1-09',3000,null,10);
----使用日期默认格式
----（2）insert into emp1 values(1001,'shelly','manager',7839,'09-1月-2018',3000,null,10);
----使用to_date将字符串转换为日期
----（3）insert into emp1 values(1001,'shelly','manager',7839,to_date('2018-1-09','yyyy-mm-dd'),3000,null,10);
--4.DDL语言
--5.DCL语言
--6.TCL语言
savepoint
rollback
commit
--创建一个保存点：
savepoint mark1; --保存断点mark1类似于存档
update emp1 set sal=sal+500 where comm is null; --执行操作
rollback to mark1; --在未提交前可以回滚到断点mark1处
commit; --提交事务，提交后不可回滚
--7.Oracle的运算符
--算术运算符：+ - * /
--连接运算符 ||
--问题：查询emp1中的员工姓名，在员工姓名前加上dear
select 'dear'||ename from emp1;
--问题：查询每个员工的工资显示为：Ellen的工资为:800
select ename||'的工资是:'||sal from emp1;
--比较运算符 > >= < <= <> (!=) = is null between..and.. in like
--问题：查询工资大于3000的员工的信息
select * from emp1 where sal>3000;
--问题：查询部门编号不为20的员工信息 不等于<>和!=
select * from emp where deptno!=20;
select * from emp where deptno<>20;
--is null用来做空值的比较
--问题：查询部门中奖金为空值的员工信息
select * from emp where comm is null;
--问题：查询部门中奖金不为空值的员工信息
select * from emp where comm is not null;
---------注意：奖金为0于奖金为空不同
--between....and....查询工资在2000-5000之间的员工信息（包含边界）
select * from emp where sal between 2000 and 5000;
select * from emp where sal >= 2000 and sal<=5000;
--in：后边加一个集合
--问题：查询部门10或部门20中的员工的信息
select * from emp where deptno in(10,20);
select * from emp where deptno=10 or deptno=20;
--like：模糊查询
--通配符：
--- _：表示匹配一个字符，必须有一个字符
--- %: 表示匹配任意多个字符 匹配0个或多个字符
---[]：表示匹配方括号[]中的任意一个字符
--问题：查询姓名中带有llen的员工信息
select * from emp where ename like '%LLEN%';
--问题：查询姓名以A开头，以N结束的员工信息
select * from emp where ename like 'A%N';
--问题：查询员工姓名长度为5的员工 (五个下划线)
select * from emp where ename like '_____';
--逻辑运算符 优先级 not> and >or
--问题：查询emp表中部门10中的MANAGER或者部门20中的CLERK的员工信息
select * from emp where deptno=10 and job='MANAGER' or deptno=20 and job='CLERK';
--问题：查询emp表中部门10或者20中的MANAGER中的员工信息
select * from emp where (deptno=10 or deptno=20) and job='MANAGER';
select * from emp where deptno in(10,20) and job='MANAGER';
--运算符的优先级
--算术运算符>连接运算符>比较运算符>逻辑运算符
-------------------------------------------------day5-----------------------------------------------------
--1.列别名和表别名
--针对字段的别名称为列别名 语法：字段名(as)字段别名
select 'dear'||ename as 姓名 from emp; --加一个列别名
--as可以省略
select 'dear '||ename 姓名 from emp;
--别名可以加双引号，双引号可以省略
select 'dear '||ename "姓名" from emp;
--有一种情况双引号不可省略：别名中有空格
select 'dear '||ename "姓 名" from emp;
--表别名
select ename,sal,job from emp where deptno=10;
--给emp表一个别名 e 语法：表名 表别名 不能加as
select e.ename,e.sal,e.job from emp e where e.deptno=10;
--规则：一旦给了表别名之后，所有字段的使用都要使用：表别名.字段名
--2.select ...from...where...group by...having...order by
--注意：
--（1）select....from....必不可少,当没有表名时用dual凑
select sysdate from dual;
--（2）该语句的执行顺序：where...group by...having...order by
--（3）这些关键字不能调换顺序
--3.排序 order by 正序、逆序、单列排、多列排
--问题：查询emp表中的姓名、工资，按照工资降序排列
select ename,sal from emp order by sal desc;
--问题：查询emp表中的姓名、工资，按照工资升序排列
select ename,sal from emp order by sal asc;
select ename,sal from emp order by sal; --默认升序
--总结：语法：select.. from .. order by 字段名 asc/desc;
--asc升序 desc降序 默认升序
--问题：查询部门20的员工的姓名和工资，并按照工资进行降序
select ename,sal from emp where deptno=20 order by sal desc;
--多字段排序
--问题：查询emp表中员工的姓名、部门编号、工资，先按照部门进行升序排列，同部门内部按照工资降序排列
select ename,deptno,sal from emp order by deptno asc,sal desc;
--总结：多列排序语法order by 字段名1 asc\desc,字段名2 asc\desc, .....
--执行顺序：先按照字段1排序，在字段1排序的基础上按照字段2排序
--4.rownum rowid：oracle的伪列
--rownum相当于给查询结果的每一行编一个序号，并没有存储在emp表中
--rownum主要是用来写分页过程
select rownum,e.* from emp e;
select rownum,e.* from emp e where deptno=30;
select rownum,e.ename from emp e;
--问题1：查询emp表的前5条记录
select * from emp where rownum<=5;
--问题2：查询emp表的3-5条记录
select * from(select rownum ro,e.* from emp e)where ro>=3 and ro<=5;
--问题3：查询emp表中工资最高的前五名，员工姓名、工作、工资 (先排序再取)
select * from (select ename,job,sal from emp order by sal desc) where rownum<=5;
--问题4：查询emp表中工资最高的3-5名员工的姓名、工作、工资
select * from (select rownum ro,e.* from (select ename,job,sal from emp order by sal desc) e) where ro>=3 and ro<=5;
--rowid:记录物理地址 不随记录的查询结果中顺序的改变而改变
select rowid,e.* from emp e where ename = 'SMITH';
--总结：（1）rownum可以使用< <= =1 不能使用> >= =2....
--5.函数：单行函数（日期函数、字符串处理、数字函数、转换函数、其他）
--6.聚合函数 max、min、avg、sum、count
--问题：求emp表中员工的总数 count()括号内可以写字段名
select count(*) from emp; --求表的总行数
select count(ename) from emp;
select count(1) from emp;
--问题：求emp表中工资最高的员工工资
select max(sal) from emp;
--问题：求emp表中工资最小的员工工资
select min(sal) from emp;
--问题：求emp表中工资的平均值
select avg(sal) from emp;
--问题：求emp表中每个月支出（工资+奖金）的总数
select sum(sal)+sum(comm) from emp;
--nvl(字段名,0)先判断变量是否为null 如果为null返回0，如果不为null返回这个值
select sum(sal+nvl(comm,0)) from emp;
--7.group by分组
--问题：求emp表中每个部门员工的总人数，平均工资，请列出部门编号和总人数，及平均工资
select deptno,count(*),avg(sal) from emp group by deptno;
--问题：求部门10中的员工个数，员工的平均工资，请列出部门编号和总人数，及平均工资
select deptno,count(*),avg(sal) from emp where deptno=10 group by deptno;
--group by规则：出现在select后面的字段，除了聚合函数之外，其他内容必须出现在group by后边
--否则报错：不是单组分组函数
--问题：查询部门10的员工人数和平均工资
select count(*),avg(sal) from emp where deptno=10;
--问题：查询emp表中每个部门，每个职位的员工人数 ：多字段分组问题
select deptno,job,count(*) from emp group by deptno,job;
--注意：多字段分组，分组的顺序是按照字段的先后顺序来的
--问题：查询emp表中每个部门的人数，并且按照部门编号进行降序排列
select deptno,count(*) from emp group by deptno order by deptno desc;
select deptno,count(*) from emp group by deptno order by count(*) desc;
--8.having关键字 作用？使用时的约束？
--having主要和group by连用，针对聚合函数条件进行二次条件限定
--问题：查询emp表中每个部门的人数，找出人数大于3的部门，并按照总人数降序排序
select deptno,count(*) from emp group by deptno having count(*)>3 order by conut(*) desc;
---注意事项：
--（1）涉及到聚合函数的条件不能出现在where子句中，不能出现在group by后面,from后面
--聚合函数可以出现在select子句，having子句，order by 子句后面
--（2）where子句中放的是普通条件，不涉及到聚合函数的条件
--（3）having子句中不能放普通条件，必须放与聚合函数有关的条件
------------------------------------------day6-------------------------------------------------
--Oracle的函数
--1.字符函数：处理字符串
--ascii 返回字符的ascii码
select ascii('a')from dual;
select chr(97) from dual;
--lower,upper,initcap
--字母小写转大写
select ascii(upper('a'))from dual;
--字母大写转小写
select ascii(lower('A'))from dual;
--第一个字符转大写其他小写
select initcap(ename) from emp;
--ltrim,rtrim,trim
--注意事项:ltrim rtrim是字符级别的截断。在截断的时候按照字符匹配
select ltrim('ellen','el')from dual; --结果为 n
select rtrim('ellenellneen','ne')from dual;
--trim截取集只能是一个字符
select trim('n'from 'ellenellneen') from dual;
--去掉字符串左右的空格，中间的无法截取
select trim(' ellen ') from dual;
--lpad rpad 字符串补全函数
select lpad('hao',2,'ni')from dual;
select lpad('hao',5,'ni')from dual;
select lpad('hao',10,'ni')from dual;
select rpad('ni',1,'hao')from dual;
select rpad('ni',5,'hao')from dual;
select rpad('ni',10,'hao')from dual;
--concat length substr replace
--concat字符串连接,和连接运算符||作用相同
select concat('dear ',ename) from emp;
--length求字符段长度：查询emp表中，员工姓名为5个字符的员工信息
select * from emp where length(ename)=5;
--substr求子串:从第3个字符开始截取，截取长度为5的子串
select substr('hello world',3,5)from dual;
--从第3个字符开始截取
select substr('hello world',3)from dual;
--replace 字符串替换函数，
--将el替换为en
select replace('shelly','el','en') from dual;
--总结：函数可以嵌套调用
--数字函数
--sign求符号
select sign(-5)from dual;
--ceil 取上整
select ceil(5.6)from dual;
--floor取下整
select floor(5.6)from dual;
--round四舍五入 第二位参数代表精度
select round(3.14,1) from dual;
select round(356,-2) from dual; --答案400 负数向前舍入
--trunk截断 直接截断，不四舍五入
select trunc(3.1415926,4)from dual;
select trunc(356,-2) from dual; --答案300
--日期函数
--sysdate系统当前日期 systimestamp显示更精确
select sysdate from dual;
select systimestamp from dual;
--add_months 当前的系统日期上加第二各参数的月份
select add_months(sysdate,2)from dual;
--months_between(date,date) 查找两个日期之间相差的月数
select months_between(sysdate,hiredate)from emp;
select months_between(sysdate,add_months(sysdate,1))from dual;
--last_day(date)返回当前月的最后一天
select last_day(sysdate)from dual;
select last_day('08-2月-2018')from dual;
--next_day(date,char)返回从当前日期算起的下一个星期二
select next_day(sysdate,'星期二')from dual;
--round 日期的四舍五入 以15为界限
select round(date'2018-01-16','MONTH')from dual;
--trunc日期的截取
select trunc(date'2018-01-16','MONTH')from dual;
--extract():
select extract(year from sysdate) as 年,
extract(month from sysdate) as 月,
extract(day from sysdate) as 日,
extract(hour from systimestamp) as 时,
extract(minute from systimestamp) as 分,
extract(second from systimestamp) as 秒 from dual;
--转换函数
--to_char to_date将日期和数字转换为指定格式的字符串
select to_char(sysdate,'D') from dual; --结果为2
--nvl nvl2
--问题：对emp表中员工，如果奖金为空给两百元奖金，如果不为空，在原有基础上加100
update emp1 set comm=nvl2(comm,comm+100,200);
--coalesec 返回参数列表中第一个非空表达式的结果 可传多个参数
--问题：查询emp表中所有员工的月收入
select ename,sal+nvl(comm,0) as 月收入 from emp;
select ename,coalesce(sal+comm,sal) as 月收入 from emp;
--decode可传多个参数
--问题：按照职位提升工资，Manager提升为原来的1.5倍，analyst提升为原来的1.2倍，
--salesman提升为原来的1.1倍，否则提升1.05倍
update emp1 set sal=decode(job,
'MANAGER',sal*1.5,
'ANALYST',sal*1.2,
'SALESMAN',sal*1.1,
sal*1.05);
------------------------------------------day7-------------------------------------------------
--1.表连接（内连接，外连接（左外连接，右外连接，全外连接））
--2.表连接：两表连接，三表连接，四表连接
--3.子查询：单行子查询，多行子查询
--4.子查询：any all
--5.视图：什么是视图？视图的作用？怎么创建和删除视图？
--6.索引：什么是索引？索引的作用？怎么创建合适的索引？创建删除和维护索引语法？
--1.问题：查询每个员工的部门名称，列出员工姓名和部门名称
select e.ename,d.dnamefrom emp e,dept dwhere e.deptno=d.deptno
--表连接中别名的使用：
--为了避免出现“未明确定义列”的错误，建议使用表别名.字段名的方式引用字段
--总结：select...from表1，表2，表3,... where 连接条件1 and 连接条件2 and ...
--连接条件：e.deptno=d.deptno等值连接的条件，所以这种连接成为等值连接
--不等值连接
--2. 问题：查询员工表中，每个员工的工资等级，列出员工姓名，工资和工资等级
select e.ename,e.sal,s.gradefrom emp e,salgrade s
where e.salbetween s.losaland s.hisal;
--自连接：
--3. 查询所有比自己领导入职早的员工的姓名和上级领导的姓名
select w.enameas员工姓名,m.enameas上级领导姓名
from emp w,emp mwhere w.mgr=m.empnoand w.hiredate<m.hiredate;
--内连接：
--1.问题：查询每个员工的部门名称，列出员工姓名和部门名称
select e.ename,d.dnamefrom emp einnerjoin dept don e.deptno=d.deptno;
--总结：select...from表1 inner join 表2 on 连接条件 where 查询条件
--问题：查询部门10中每个员工的工资等级
select e.ename,s.gradefrom emp e
innerjoin salgrade s
on e.salbetween s.losaland s.hisal--连接条件
where e.deptno=10; --查询条件
--问题：查询emp表中部门名称为ACCOUNTING的员工的姓名，和部门位置
select e.ename,d.locfrom emp e,dept d
where e.deptno=d.deptnoand d.dname='ACCOUNTING';
select e.ename,d.locfrom emp einnerjoin dept d
on e.deptno=d.deptno
where d.dname='ACCOUNTING';
--查询高于自己部门平均工资的员工的信息，列出部门平均工资
--（1）获得每个部门的平均工资
select deptno,avg(sal) avg_sal from empgroupby deptno; --x
--(2) 将x表和emp表做表连接
select e.*,x.*from emp e,xwhere e.deptno=x.deptnoand e.sal>x.avg_sal;
--(3)替换x
select e.*,x.*from
emp e,(select deptno,avg(sal) avg_sal from empgroupby deptno)x
where e.deptno=x.deptnoand e.sal>x.avg_sal;
--笔试题讲解：
select a.au_name,a.age,sum(b.qty)from authors a,books b
where a.au_id=b.au_idand(age>30or age<18)
groupby a.au_name,a.age;
--执行顺序：
--先进行表连接
--连接后的表进行筛选（年龄大于30，或小于18）
--分组
--求库存总量
--笔试题讲解：
--问题：查询每个员工的员工姓名，部门名称，以及工资等级
select e.ename,d.dname,s.gradefrom
emp e,dept d,salgrade s
where e.deptno=d.deptnoand e.salbetween s.losaland s.hisal;
--左外连接：left join
select e.*,d.*from dept d leftjoin emp eon e.deptno=d.deptno;
--问题：查询没有没有员工的部门信息
select d.*from dept d leftjoin emp eon e.deptno=d.deptnowhere e.empnoisnull;
--总结：select...from左表 left join 右表 on...where...
--注意：左连接中，左表是主表
--右外连接
select d.*,e.*from emp e rightjoin dept don e.deptno=d.deptno;
--注意：左表 right join右表
--右外连接中，右表是主表
--全外连接
insertinto emp(empno,ename,sal,job,comm,hiredate)
values(1001,'ELLEN',4500,'MANAGER',NULL,'18-10月-2017');
select d.*,e.*from emp e fulljoin dept don e.deptno=d.deptno;
--子查询：
--单行子查询：查询只返回一个值
--问题：查询和scott工作相同的员工姓名，不包含scott在内
select enamefrom empwhere
job=(select jobfrom empwhere ename='SCOTT')and ename<>'SCOTT';
--查询所有在ACCOUNTING部门的员工的工号和姓名，按照姓名升序排列
select empno,enamefrom emp
where deptno=(select deptno from deptwhere dname='ACCOUNTING')
orderby enameasc;
--查询blake的上级领导的姓名和工资
select ename,salfrom emp
where empno=(select mgr from empwhere ename='BLAKE');
--查询薪水高于部门30的最低薪水的员工信息
select*from empwhere sal>(selectmin(sal)from emp where deptno=30);
--查询哪些部门最低薪水高于部门30的最低薪水，列出这些部门薪水最低的员工信息
--(1)查询哪些部门最低薪水高于部门30的最低薪水 --x
select deptno,min(sal)from emp
groupby deptno
havingmin(sal)>(selectmin(sal)from emp where deptno=30);
--(2)将emp表与x做表连接
select e.*from emp e leftjoin xon e.deptno=x.deptno;
--(3)替换：
select e.*,x.*from
(select deptno,min(sal) min_sal from emp
groupby deptno
havingmin(sal)>(selectmin(sal)from emp where deptno=30))x
innerjoin emp e
on e.sal=x.min_sal;
--查询和SALESMAN同部门，但是不是SALESMAN的员工的信息
select*from empwhere
deptnoin(selectdistinct deptno from empwhere job='SALESMAN')
and job<>'SALESMAN';
--ANY
-->any:大于最小的
--<any：小于最大的
--查询比任何一个SALESMAN的薪水高但不是SALESMAN的员工信息。
select*from empwhere sal>any(select sal from empwhere job='SALESMAN')and job<>'SALESMAN';
--all
-->all:大于最大的
--<all:小于最小的
--查询比所有一个SALESMAN的薪水高但不是SALESMAN的员工信息。
select*from empwhere sal>all(select sal from empwhere job='SALESMAN')and job<>'SALESMAN';
select*from empwhere sal>(selectmax(sal)from emp where job='SALESMAN')and job<>'SALESMAN';
--1.求出自己部门的平均工资
select deptno,avg(sal) avg_sal from emp group by deptno;
--2.表连接，列出高于平均工资的员工信息
select e.*,x.*from emp e,
(select deptno,avg(sal) avg_sal from emp group by deptno)x
where e.deptno=x.deptno and e.sal>avg_sal;
--1.查询自己的领导的姓名
select e1.ename,e2.ename from emp e1,emp e2
where e1.mgr=e2.empno and e1.hiredate<e2.hiredate;
--问题：按部照门名称和工作查看emp表每个门每部种职位每个月的总开支并且按照总开支进行降序排列
select d.dname,e.job,sum(s al)from emp e,dept d where e.deptno=d.deptno group byd.dname,e.job
order by sum(sal) desc;
--问题：查询和scott相同部门相同职位的员工
select * from emp where (deptno,job)=(select deptno,job from emp where ename='SCOTT')and ename!='SCOTT';
--查询每个部门下面工资最低的员工信息
select deptno,min(sal) from emp group by deptno;
select * from emp where (deptno,sal) in (select deptno,min(sal) from emp group by deptno);
--行内视图 ：出现在from后面的一个select语句
--问题：查询出薪水比本部门平均薪水高的员工信息
select deptno, avg(sal) avg_sal from emp group by deptno;
select e.* ,avg_sal from emp e,(select deptno, avg(sal) avg_sal from emp group by deptno)x
where e.deptno=x.deptno and e.sal>avg_sal;
--所有涉及到行内视图的方法都可以用三步走
--列出每个部门的员工人数和部门编号
select deptno,count(*) from emp group by deptno;
--列出每个部门的员工人数和部门名称
select d.dname,count(*) from emp e,dept d where d.deptno=e.deptno group by d.dname;
--exists(select .....from....)
--如果select语句返回的结果集为空，exists(select .....from....)结果是false
--如果不为空，结果是true
--查询有员工的部门编号和部门名称
select d.deptno,d.dname from dept d
where exists(select * from emp e where d.deptno=e.deptno);
--集合操作
--union/union all 求并集 union去重，union all不去重
--intersect 求交集
--minus 求差集
--交集
--显示职位是MANAGER且薪水大于2500的员工信息
select * from emp where job = 'MANAGER' intersect select *from emp where sal>2500;
--差集
--显示职位是MANAGER但是薪水小于2500的员工信息
select * from emp where job = 'MANAGER' minus select *from emp where sal>=2500;
--注意：集合操作关键字前后都是select语句，
--集合操作要求两个或多个集合必须是同一种类型的集合
------------------------------------------day8-----------------------------------------------
--(1)什么是视图
----视图是一张虚表，就是对select查询结果给取一个名字。
----select查询的表称为基表
----视图不存储数据，数据存储在基表中，视图只是保存一个映射关系
create view test as select ename,sal ,job from emp;
--（2）视图的作用
----简化复杂查询
----限制数据访问
--例子：查询每个部门的员工人数和部门名称
select d.dname,count(*) from dept d,emp e where d.deptno=e.deptno group by d.dname;
--创建视图，存储上述结果
create view numEmp as
select d.dname,count(*) co from dept d,emp e
where d.deptno=e.deptno group by d.dname;
--查询人数大于3的部门的名称和人数 （简化复杂查询）
select * from numEmp where co>3;
--注意：创建视图的时候，如果遇到select语句中有聚合函数，需要给聚合函数别名
--例子 限制数据访问：
create user hope identified by 123456;
grant connect to hope;
create view empSimple as select empno,ename,job,mgr,hiredate,deptno from emp;
grant select on scott.empSimple to hope;
--注意事项：（1）视图的查询和表的查询相同
-----（2）视图中不包含任何数据，视图只是一个映射关系
-----（3）当基表发生改变的时候，视图也会随之改变
--（3）怎么创建和删除视图
--语法：create[or replace]view 视图名 as select....from....[with read only]
--with read only 表明视图是一个只读视图，只能查询，无法修改
--视图的分类：
--简单视图：简单的查询语句 不涉及表连接和聚合函数
create view v_emp(no,name) as select empno,ename from emp;
--复杂视图：涉及到聚合函数，但不涉及表连接
--连接视图：涉及到表连接的视图
create view emp_dept as select e.*,d.* from emp e,dept d where e.deptno=d.deptno;
create or replace view v_emp(编号,姓名) as select empno,ename from emp with read only;
--能不能对视图进行DML操作？
--能，但不是所有的视图都可以DML操作
-----(1)非只读的简单视图可以DML操作，事实上是针对基表的操作
create or replace view test as select empno,ename from emp;
insert into test values(1010,'shelly');
--视图的插入实际上是对基表的操作，对视图的操作不能违反基表的约束
-----（2）with read only 复杂视图和连接视图不能进行DML操作
--删除
drop view test;
--（1）什么是索引
-----类比图书的目录：索引是一种加快数据访问速度的机制
-----索引需要占用磁盘空间，维护索引需要资源开销，所以索引并不是越多越好
--（2）索引的作用：加快数据访问速度
--（3）如何创建合适的索引
--（4）怎么创建和删除索引
--单列索引：
create index idx_ename on emp(ename);
--语法：create index 索引名 on 表名（字段名）
--符合索引：
create index idx_ename_deptno on emp(ename,deptno);
--唯一索引
create unique index idx_ename on emp(ename);
--注意：在某一字段上创建一个唯一索引，Oracle自动在该字段创建一个唯一约束
--删除
drop index idx_ename;
--重建索引(更新索引，类似更新目录)
alter index idx_ename rebuild;
--注意事项L如果定义了主键约束和唯一约束，Oracle会自动在该字段上创建一个唯一索引
------------------------------------------day9-----------------------------------------------
--1.pl/sql（procedure Language/sql）是什么？
--是对Oracle标准SQL语言的扩展，在oracle数据库上编程的语言
--2.为什么要用PL/SQL？
----（1）不能进行模块化编程
-------例如：淘宝下订单，如果仅用SQL语句，下一个订单可能需要多条SQL语句，写好一个模块，专门
-------完成下订单功能，需要使用时，传入参数调用即可
----（2）执行速度：传统的SQL要涉及到网络传输时间，DBMS对SQL语句的编译时间，效率不高，
-------PL/SQL：sql语句写在数据库中，只需编译一次，并且省去了网络传输时间，效率较高
----（3）安全性：sql语句如果直接写在程序或客户端中，在网络传输过程中会产生不安全因素，比如SQL注入
-------PL/SQL：SQL语句写在数据库中，不需要网络传输，避免此问题
----（4）传统SQL浪费带宽
--3.缺点：一致性不好：oracle的存储过程在其他数据库中不能使用，需要重新编写
--4.快速入门：
--问题：编写一个过程，往emp表中插入一条记录
create procedure prol is
begin
insert into emp1(empno,ename,sal,deptno) values(1011,'lili',3000,20);
end;
--如何调用
exec prol
--总结语法：
---1.创建过程语法
create procedure 过程名(参数1 参数类型1,参数2 参数类型2.....) is
begin
--执行部分
exception
--异常处理部分
end;
---2.过程调用语法
exec 过程名(参数1 参数类型1,参数2 参数类型2.....);
---3.过程中，语句后面需要加分号
--创建一个过程，输入一个员工编号，删除emp表中对应员工记录
create procedure pro2(no number) is
begin
delete from emp1 where empno = no;
end;
exec pro2(1011);
--注意：number数据类型不能加长度
--出现编译错误使用show error 查看
--5.PL/SQL可以做什么？（过程，函数，包（包体），触发器）==>Pl/SQL块
--6.PL/SQL块编程
---（1）注释：单行注释：-- 多行注释/* */
---（2）标识符的命名规范：
--当定义变量时，以 v_ 开头
--当定义常量时，以c_开头
--当定义游标时，以_crusor结尾
--当定义例外时，以e_开头
---（3）块结构
declare（可选）
--声明变量，常量，游标，例外和复杂的数据类型
begin
--执行部分：要执行的PL/SQL语句
exception
--异常处理部分
end;
--案例：只包括执行部分的sql块，输出hello world
begin
dbms_output.put_line('hello,world!');
end;
--
set serveroutput on;
--注意1.dbms_output.put_line('串'); Oracle中的输出语句
---dbms_output是oracle提供的包，类似于java的包，该包中包含一些过程，put_line是dbms_output的一个过程
--包含定义部分和执行部分的PL/SQL块
--案例：根据用户输入的雇员编号，输出该雇员的名字
declare
v_ename varchar2(10);
begin
select ename into v_ename from emp where empno=&no;
dbms_output.put_line('姓名'||v_ename);
end;
--变量的定义：变量名 变量类型
--select .... into ....from是对变量v_ename的赋值
--empno=&no：&no符号表示从键盘键入一个empno
--案例：将上述PL/SQL块改为一个过程
create or replace procedure pro3(v_empno number) is
v_ename emp.ename%type;
begin
select ename into v_ename from emp where empno=v_empno;
dbms_output.put_line('姓名是'||v_ename);
end;
--总结：过程中，变量的定义是出现在is和begin之间的，没有declare
--emp.ename%type表示v_ename的类型和emp表的ename的数据类型和长度相同
--案例：包含定义部分，执行部分和例外处理部分的PL/SQL块
--问题：如果上述案例中，输入了一个不存在的员工编号，这个时候会报错，怎么处理？
declare
v_ename emp.ename%type;
begin
select ename into v_ename from emp where empno=&no;
dbms_output.put_line(v_ename);
exception
when no_data_found then
dbms_output.put_line('编号不存在');
end;
--异常处理的基本结构语法：
exception
when 异常名称 then
/*
对异常处理的代码
*/
when 异常名称 then
--对异常处理的代码
end;
--有些时候，异常用来做逻辑跳转
--案例：输入一个员工编号，显示员工姓名，如果输入了不存在的员工编号，就在emp表中添加一条记录
--（1002，‘傻逼’）
declare
v_ename emp.ename%type;
v_empno emp.empno%type:=&no;
begin
select ename into v_ename from emp where empno=v_empno;
dbms_output.put_line('姓名是'||v_ename);
exception
when no_data_found then
insert into emp(empno,ename) values(v_empno,'傻逼2');
end;
--变量的赋值：用:= 例如v_empno emp.empno%type:='jone'
declare
v_emp emp%rowtype;
begin
select * into v_emp from emp where empno=7839;
dbms_output.put_line(v_emp.ename||v_emp.sal);
end;
--总结：%rowtype表示记录类型 v_emp emp%rowtype
--使用v_emp.字段名
--7.过程的进一步讲解
---（1）过程中不但可以指定输入参数，也可以指定输出参数
create procedure 过程名(参数1 in 参数类型1,参数2 out 参数类型2.....) is
begin
--执行部分
exception
--异常处理部分
end;
--注意1：过程可以指定多个输入和输出参数，分别是 参数 in 参数类型 /参数 out 参数类型
--注意2：如果没有指明，默认输入参数in
---编写一个过程，可以输入员工姓名，新的工资，可以修改雇员工资
create procedure pro4(name in varchar2,new_sal in number) is
begin
update emp1 set sal = new_sal where ename=name;
end;
--------------------------------------------day10-------------------------------------------------
--1.认识PL/SQL的函数
--2.创建包和包体
--3.PL/SQL变量的类型
--1.认识PL/SQL的函数
--问题：输入一个雇员姓名，返回年薪
--函数类型写在函数名后边
create or replace function sp_func(spName varchar2) return number is
yearSal number(7,2);
begin
select sal*12+nvl(comm,0)*12 into yearSal from emp where ename=spName;
return yearSal;
end;
--总结
--语法：
/*create [or replace] function 函数名(参数1 类型1，参数2 类型2，....) return 返回值类型 is
--变量，常量和游标的定义
begin
--执行部分
exception
--异常处理部分
end
*/
--调用函数1 var全局变量
var income number; --定义全局变量：var 变量名 变量类型
call sp_func('SCOTT') into:income; --全局变量赋值：call 函数 into:全局变量名
print income; --打印全局变量：print 全局变量名
--调用函数2
select sp_func('SCOTT') from dual;
--包：组织管理过程和函数的一种机制，主要有两部分组成：包规范和包体
--包里面主要是声明一下过程和函数，过程和函数的实现放在包体中
--问题：创建一个包，包含一个过程，包含一个函数，
--------过程：输入员工姓名，新的工资，更新员工工资
--------函数：输入员工姓名，计算员工工资
--创建包：只是声明过程和函数
create or replace package sp_package is
procedure update_sal(name emp.ename%type,newSal emp.sal%type);
function annual_income(name emp.ename%type) return number;
end;
--总结
--语法
/*
create [or replace] package 包名 is
过程1声明;
过程2声明;
...
函数1声明;
函数2声明;
...
end;
*/
--创建包体：过程和函数的实现
create or replace package body sp_package is
procedure update_sal(name emp.ename%type,newSal emp.sal%type) is
begin
update emp set sal=newSal where ename=name;
end;
function annual_income(name emp.ename%type) return number is
yearSal number(7,2);
begin
select sal*12+nvl(comm,0)*12 into yearSal from emp where ename=name;
return yearSal;
end;
end;
--创建包体语法：
/*
create [or replace] package body 包名 is
过程1实现体；
过程2实现体；
...
函数1实现体
函数2实现体
...
end
*/
--包中过程和函数的调用：包名.过程名 或者 包名.函数名
exec sp_package.update_sal('SCOTT',4500);
var income number;
call sp_package.annual_income('SCOTT') into:income;
print income;
select sp_package.annual_income('SCOTT') from dual;
--触发器：触发器是指隐含执行的存储过程，当定义触发器时，必须置顶触发事件和触发操作。
--常见的触发事件包括：insert，update，delete语句。
--触发器是一个PL/SQL块，可以通过create trigger来创建
--PL/SQL变量：标量类型，复合类型，参照类型，lob（large object）类型
--标量类型： 基本类型
--变量名[constant] 数据类型[not null] [:=值]或者[default 值]；
--%type:标量类型的一种
--案例：
/*
定义一个变长的字符串：v_ename varchar2(20);
定义一个小数：v_yearsal number(7,2);
定义一个小数，给初始值为3.14：v_sal number(3,2):=3.14;
定义一个常量PI给默认值为3.14：PI constant number(3,2):=3.14;
定义一个日期类型：v_hiredate date;
定义一个布尔类型：v_invid boolean not null default false;
*/
--编写一个PL/sql块，输入员工编号，显示员工的姓名和工资，以及个人所得税（税率0.05）
declare
v_tax_rate constant number(3,2):=0.05;
v_ename emp.ename%type;
v_sal emp.sal%type;
v_personal_tax number(7,2);
begin
select ename,sal into v_ename,v_sal from emp where empno=&no;
v_personal_tax:=v_sal*v_tax_rate;
dbms_output.put_line('姓名：'||v_ename||'工资：'||v_sal||'个人所得税：'||v_personal_tax);
end;
--复合类型的一种：
--（1）PL/SQL的记录类型
--（2）PL/SQL的表类型
--（3）嵌套表类型
--（4）varray类型
--PL/SQL的记录类型：%rowtype 跟表的类型一致
--输入一个员工编号，显示该员工的所有信息
declare
v_emp emp%rowtype;
begin
select * into v_emp from emp where empno=&no;
dbms_output.put_line('姓名：'||v_emp.ename||'工作'||v_emp.job);
end;
----记录类型：类似于结构体
declare
type emp_record_type is record(name emp.ename%type,salary emp.sal%type,dept emp.deptno%type);
sp_record emp_record_type;
begin
select ename,sal,deptno into sp_record from emp where empno=&no;
dbms_output.put_line('姓名：'||sp_record.name||'工资：'||sp_record.salary||'部门：'||sp_record.dept);
end;
-----记录类型的语法：
/*
type 类型名 is record(变量1 数据类型1，变量2 数据类型2....)
变量名 类型名；
*/
----PL/sql表类型
--表类型：类似于数组
--定义表类型时：
---1.不定义表的大小，动态表
---2.需要定义表中每个数据的类型
---3.表类型的下标可以为负，正
declare
type emp_table_type is table of emp.ename%type index by binary_integer;
sp_table emp_table_type;
begin
select ename into sp_table(-1) from emp where empno=&no;
dbms_output.put_line('姓名：'||sp_table(-1));
end;
--表类型
/*
type 类型名 is table of 数据类型 index by binary_integer;
变量名 类型名
*/
--参照类型：游标
---案例：输入一个部门名称，显示该部门所有员工的姓名和工资
declare
type sp_emp_cursor is ref cursor;
test_cursor sp_emp_cursor;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
open test_cursor for select ename,sal from emp where deptno=&no;
loop
fetch test_cursor into v_ename,v_sal;
exit when test_cursor%notfound;
dbms_output.put_line('姓名：'||v_ename||'工资：'||v_sal);
end loop;
close test_cursor;
end;
--总结：
/*
（1）type sp_emp_cursor is ref cursor： 表示sp_emp_cursor是一个游标类型
（2）test_cursor sp_emp_cursor; 定义一个游标变量test_cursor，是sp_emp_cursor类型
（3）open test_cursor for 查询语句
（4）fetch 游标变量名 into 变量1，变量2
（5）exit when test_cursor%notfound 定义游标循环退出条件
（6）close test_cursor 关闭游标
*/
---写法2：
declare
curcor v_cursor is select ename,sal from emp where deptno=&no;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
open v_cursor;
loop
fetch v_cursor into v_ename,v_sal;
exit when v_cursor%notfound;
dbms_output.put_line('姓名：'||v_ename||'工资：'||v_sal);
end loop;
close v_cursor;
end;
/*
cursor 游标变量名 is 查询语句
变量名 变量类型
.....
begin
open 游标变量
fetch 游标变量 into 变量1，变量2，....
close 游标变量
end
*/
--写法3：
declare
cursor v_cursor is select ename,sal from emp where deptno=&no;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
for v_cursors in v_cursor loop
v_ename:=v_cursors.ename;
v_sal:=v_cursors.sal;
dbms_output.put_line('姓名：'||v_ename||'工资：'||v_sal);
end loop;
end;
---for循环自动打开关闭游标
--------------------------------------------day11-------------------------------------------------
--1.Pl/SQL流程控制
--2.动态PL/SQL
--3.异常
--1.Pl/SQL流程控制
---------if else
--案例1 ：编写一个存储过程，输入一个雇员姓名，如果该员工工资低于2000，工资增加10%
create or replace procedure updateSal(spName varchar2) is
v_sal emp.sal%type;
begin
select sal into v_sal from emp where ename=spName;
if v_sal<2000 then
update emp set sal=sal*1.1 where ename = spName;
end if;
end;
--总结
/*
if...
then...
end if
如果if后面条件满足，则执行then后边语句
*/
--案例2：编写一个过程，输入一个员工姓名，若奖金非空，加100，若为空或0，奖金设置为200
create or replace procedure updateComm(spName varchar2) is
v_comm emp.comm%type;
begin
select nvl(comm,0) into v_comm from emp where ename = spName;
if v_comm=0 then
update emp set comm=200 where ename=spName;
else
update emp set comm=comm+100 where ename=spName;
end if;
end;
--总结
/*
if...
then...
else...
end if
*/
--案例3，编写一个存储过程，如果职位是president工资加1000，manager +500 其他+200
create or replace procedure updateSal1(spName varchar2) is
v_job emp.job%type;
begin
select job into v_job from emp where ename = spName;
if v_job='PRESIDENT' then
update emp set sal=sal+1000 where ename=spName;
elsif v_job='MANAGER' then
update emp set sal=sal+500 where ename=spName;
else
update emp set sal=sal+200 where ename=spName;
end if;
end;
--总结
/*
if...
then...
elsif....
then....
else....
end if;
*/
---------loop end loop
--案例4：创建一张表users，循环插入10条记录
create table users(id number(3),
name varchar2(10));
create or replace procedure insertTable(spName varchar2) is
v_num number:=1;
begin
loop
insert into users values(v_num,spName);
v_num:=v_num+1;
exit when v_num=11;
end loop;
end;
--总结：loop....end loop：首先定义一个循环变量，其次在loop和end loop之间一定要写退出条件，否则死循环
--案例5：编写一个存储过程，可以输入用户名，并循环users中添加数据
create or replace procedure insertTable1(spName varchar2) is
v_num number:=11;
begin
while v_num<=20 loop
insert into users values(v_num,spName);
v_num:=v_num+1;
end loop;
end;
--总结：while 循环条件 loop 执行内容；循环控制语句；end loop
--案例6：使用for循环
create or replace procedure insertTable2(spName varchar2) is
begin
for i in 21..30 loop
insert into users values(i,spName);
end loop;
end;
--总结：for 循环变量 loop in 21..30 循环语句 end loop
--案例7 用三种循环语句实现1加到100
--（1）
declare
v_counter number:=1;
v_sum number:=0;
begin
loop
v_sum:=v_sum+v_counter;
v_counter:=v_counter+1;
exit when v_counter=101;
end loop;
dbms_output.put_line('和是：'||v_sum);
end;
declare
v_counter number:=1;
v_sum number:=0;
begin
while v_counter<=100 loop
v_sum:=v_sum+v_counter;
v_counter:=v_counter+1;
end loop;
dbms_output.put_line('和是：'||v_sum);
end;
declare
v_sum number:=0;
begin
for i in 1..100 loop
v_sum:=v_sum+i;
end loop;
dbms_output.put_line('和是：'||v_sum);
end;
--案例8：case语句，从键盘接受一个输入，输入A，输出优秀，输入B，输出良好，输入C输出中等，
-------其他情况输出不及格
declare
v_grade char(5):=&grade;
begin
case v_grade
when 'A' then
dbms_output.put_line('优秀');
when 'B' then
dbms_output.put_line('良好');
when 'C' then
dbms_output.put_line('中等');
else
dbms_output.put_line('不及格');
end case;
end;
--总结：
/*
case 变量
when 值1 then
执行语句1
when 值2 then
执行语句2
....
else
执行语句
end case;
*/
--**********************************
--2.动态SQL
--动态SQL：编译期间SQL语句不确定，并且在运行时允许变化
--应用场合：要执行一个DDL语句时；需要增加程序的灵活性时，使用包DBMS_SQL动态执行SQL语句
--案例1：编写SQL块，创建一张test1表
begin
execute immediate 'create table test1(id number(5),name varchar(10))';
end;
--总结：PL/SQL块不能直接执行DDL语句，所以可以用动态的sql去执行
--execute immediate sql语句
declare
stmt varchar2(150):='create table test1(id number(5),name varchar(10))';
begin
execute immediate stmt;
end;
--案例2：编写一个PL/SQL块，向test1中插入一条记录，要求从键盘获取
declare
v_id test1.id%type:=&id;
v_name test1.name%type:=&name;
begin
execute immediate 'insert into test1 values(:1,:2)' using v_id,v_name;
end;
--总结：execute immediate sql语句 using 值1，值2，...；
--:1,:2占位符 需要用到变量的地方用其代替
--案例3：查询test1，从键盘接收id，输出其姓名
declare
v_id test1.id%type:=&id;
v_name test1.name%type;
begin
execute immediate 'select name from test1 where id=:1' into v_name using v_id;
dbms_output.put_line('姓名是：'||v_name);
end;
--总结：execute immediate SQL语句 into 变量 using 变量
---（1）sql语句中并无into
---（2）where id=:1 :1是占位符 表示在运行时需要一个值替代
--案例4：键盘输入一个员工编号，查询emp表中该员工的姓名和工资，工资小于2000，增加500，返回增加后工资
declare
v_empno emp.empno%type:=&no;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
select ename,sal into v_ename,v_sal from emp where empno=v_empno;
if v_sal<2000 then
execute immediate 'update emp set sal=sal+500 where empno=:1 returning sal into :2'
using v_empno returning into v_sal;
end if;
dbms_output.put_line('新工资是：'||v_sal);
end;
--总结：execute immediate SQL 语句 useing 变量1 returning into 变量2
--update emp set sal=sal+500 where empno=:1 returning sal into :2 意思是更新sal，返回更新后的工资
--案例5：从键盘接受一个员工编号，删除其信息
declare
v_empno emp.empno%type:=&no;
begin
execute immediate 'delete from emp where empno=:1' using v_empno;
end;
--总结：占位符在这里可以是:num的形式
--**********************************
--3.异常处理
---编写一个PL/SQL块，查询emp表中员工的工资
declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp;
exception
when too_many_rows then
dbms_output.put_line('找到多条记录！：');
when others then
dbms_output.put_line('未知异常');
end;
---案例2：从键盘输入有个empno，查询该员工的工资，如果小于2000，报异常
--自定义异常
declare
v_sal emp.sal%type;
v_empno emp.empno%type:=&no;
myexp exception;
begin
select sal into v_sal from emp where empno=v_empno;
if v_sal<2000 then
raise myexp;
end if;
exception
when myexp then
dbms_output.put_line('涨工资！！！！！！！！！！');
end;
---总结
--（1）declare预定义一个异常
--（2）在执行部分触发一个异常 raise myexp
--（3）在exception中处理异常
--------------------------------------------day12--------------------------------------------
--触发器：当用户登录或，退出，修改记录，或者是对对象进行操作，进行ddl时，引起某个存储过程的执行
--把这样一个隐含的调用称为触发器
--1.当用户登录时，希望可以自动记录用户名字和登录时间
--当用户在周四或者非工作日对表进行修改时，不允许这样做
--当用户删除一条记录时，希望可以将删除记录自动备份到另外一张表中
--解决方法：触发器
--触发器分类：dml触发器（insert update delete）ddl触发器，系统事件触发器（登录、退出）
--案例1：创建一张my_emp表，给该表添加记录，提示：添加一条数据
--语句级别触发器：针对增删改查只触发一次操作
--创建表的语句
create table my_emp(id number(5),name varchar2(10));
--创建触发器
create or replace trigger tri1
after insert on scott.my_emp
begin
dbms_output.put_line('添加一条数据');
end;
insert into my_emp values(101,'hewie');
--案例2：在my_emp表修改id为101的员工信息时，修改一次提示一次
--for each row 表名是一个行级触发器：修改一行，触发一次
create or replace trigger tri2
after update on scott.my_emp
for each row
begin
dbms_output.put_line('修改一条数据');
end;
update my_emp set name='hewie' where id=101;
--创建触发器的语法
/*
create [or replace] trigger trigger_name
before/after insert/update/delete on table_name
[for each row]
[when condition]
begin
--触发的操作
end;
*/
--案例3：在删除my_emp表中的记录时，提示删除一条记录
create or replace trigger tri3
after delete on scott.my_emp
begin
dbms_output.put_line('删除一条数据');
end;
--案例4：在删除记录时，触发报错，不让删除
create or replace trigger tri4
before delete on scott.my_emp
begin
raise_application_error(-20001,'禁止删除');
end;
delete from my_emp where id=101;
-- raise_application_error是一个存储过程，可以传入两个参数，
--第一个参数是错误号范围：-20000到-20999，第二个参数是错误提示信息
--案例5：为了禁止工作人员在周四删除my_emp表中信息，创建一个触发器，完成这个操作
create or replace trigger tri5
before delete on scott.my_emp
begin
if to_char(sysdate,'day')='星期四' then
raise_application_error(-20002,'周四不允许删除');
end if;
end;
--案例6：禁止工作人员在周四修改员工信息（insert update delete）,不同操作保存信息不同
create or replace trigger tri6
before insert or update or delete on scott.my_emp
begin
if to_char(sysdate,'day')='星期四' then
case
when inserting then
raise_application_error(-20003,'就是不让你添加');
when updating then
raise_application_error(-20004,'就是不让你修改');
when deleting then
raise_application_error(-20004,'就是不让你删除');
end case;
end if;
end;
--当触发器中同时包含多个触发事件时，为了区分触发事件，可以使用三个条件 inserting updating deleting
--案例7：修改员工工资时，要求新工资必须比原工资高，创建一个触发器,输出新老工资
create or replace trigger tri7
before update on scott.emp
for each row
begin
if :new.sal<:old.sal then
raise_application_error(-20006,'工资不能低于原工资');
else
dbms_output.put_line('原来的工资是：'||:old.sal||'新的工资：'||:new.sal);
end if;
end;
update emp set sal=2000 where ename='SMITH';
--:new update操作完成后的列的值
--:old update操作完成前的列的值
--案例8：编写一个触发器，当用户删除一张表的记录时，自动将删除的id和name备份到另一张表中
create table my_emp_bak as select * from my_emp where 1=2;
create or replace trigger tri8
before delete on scott.my_emp
for each row
begin
insert into my_emp_bak values(:old.id,:old.name);
end;
delete my_emp;
--禁用触发器
alter trigger tri1 disable;
--启用触发器
alter trigger tri1 enable;
--删除触发器
drop trigger tri1;
--案例9：触发器 控制员工的新工资不能低于的原来的工资但是也不能高于原来工资的20%
create or replace trigger tri9
before update on scott.emp
for each row
begin
if :new.sal<:old.sal then
raise_application_error(-20007,'工资不能低于原工资');
elsif :new.sal>:old.sal*1.2 then
raise_application_error(-20008,'工资不能高于原工资20%');
else
dbms_output.put_line('原来的工资是：'||:old.sal||'新的工资：'||:new.sal);
end if;
end;
update emp set sal=5000 where ename='SMITH';
--oracle的系统触发器：主要是针对oracle的系统事件的一个触发器
--oracle定义的常用的系统事件：
--oracle_client_ip_address:返回客户端的ip
--oracle_datebase_name:返回数据库名
--oracle_login_user:返回登录用户名
--ora_sysevent:返回触发器的系统事件名
--ora_des_encrypted_password:返回加密后的密码
--案例：为了记录用户登录和退出的时间，可以建立登录和退出的触发器
create table logon_table(
username varchar2(10),
logon_time date,
logoff_time date,
address varchar2(20));
--创建登录触发器
create or replace trigger logon
after logon on database
begin
insert into logon_table(username,logon_time,address)
values(ora_login_user,sysdate,ora_client_ip_address);
end;
--创建退出触发器
create or replace trigger logoff
before logoff on database
begin
insert into logon_table(username,logoff_time,address)
values(ora_login_user,sysdate,ora_client_ip_address);
end;
--普通用户没有权限创建系统触发器
--ddl触发器
--编写一个触发器，可以记录用户所进行的ddl操作
create table my_ddl_event(event varchar2(20),username varchar2(10),time date);
create or replace trigger ddl_tri
after ddl on scott.schema
begin
insert into my_ddl_event values(ora_sysevent,ora_login_user,sysdate);
end;
--作业：创建一张学生表stu(id,name,birthdate)，编写一个触发器，
--要求：如果学生年龄小于18岁，则不允许插入
create table stu(id number(5),name varchar2(10),birthdate date);
create or replace trigger tri_stu
before insert on scott.stu
for each row
begin
dbms_output.put_line('年龄:'||floor(months_between(sysdate,:new.birthdate)/12));
if floor((months_between(sysdate,:new.birthdate)/12))<18 then
raise_application_error(-20010,'年龄低于18');
end if;
end;
insert into stu values(123,'hewie',date'1990-05-12');
