Oracle从入门到精通最常用命令

最新推荐文章于 2023-08-14 16:06:30 发布

小小小小的爱java

最新推荐文章于 2023-08-14 16:06:30 发布

阅读量934

点赞数 2

分类专栏： Oracle从入门到精通最常用命令文章标签： Oracle从入门到精通最常用命令

本文链接：https://blog.csdn.net/loveyouluobin/article/details/80099107

版权

Oracle从入门到精通最常用命令专栏收录该内容

1 篇文章 0 订阅

订阅专栏

常用sql

改表名 rename table 旧表名 to 新表名 
表头 create table 表名(字段名 类型 约束,..)
增列 alter table 表名 add 列名 类型  约束；
改列 alter table 表名 change 旧列名 新列名 类型 约束
删列 alter table 表名 drop 列名
插行 insert into 表名 values(值1,值2......)
改行 update star set age=22 where id=2
删行 delete from 表名 where 列名=值

查库 show databases;
查字符集 show create database db; 
进库 use lb 查有多少张表:show tables;
查建表语句:show create table 表名
查看表结构 desc 表名;  

但所有行:select*from from1;
查1行:SELECT*FROM star WHERE id=3 WHERE price>3000 HAVING TYPE=1
多行:select*from form1 where id in(1,3,5,7,15,20) HAVING price>3000

模糊 select*from from1 where name like %波_;
区间 select*from from1 where price between 2000 and 6000;  [bɪˈtwin] 
降序 select * from from1 order by price asc升/desc降;
去重并年龄升序SELECT DISTINCT price FROM star ORDER BY 年龄 ASC; 

逻辑和 select * from from1 where price>3000 and age>30;
select*from user where username='张三' and password='123456'
逻辑或 select * from from1 where price>5000 or name like "周%";
逻辑非 select * from from1 where not(name like "周%");

查1列/多个列:SELECT 姓名 FROM star; 
并筛选:SELECT id,年龄 FROM star WHERE 年龄>30; 

聚合算条目数不能有where: select count(*) from from1
聚合算总和值 select sum(price) from from1
聚合最大值 select max(age) from star;
聚合最小值 select min(age) from from1;
分组聚合算平均值 SELECT TYPE, AVG(price)FROM star GROUP BY TYPE;

分组:整个表分组并把年龄*2的结果 单独列出 SELECT*,年龄*2 FROM star
以年龄分组 并把年龄大于30单独统计 SELECT 姓名,年龄>30 FROM star
以分组形式把姓名列出:SELECT TYPE,GROUP_CONCAT(姓名)FROM star GROUP BY TYPE
各个分组身份的平均值 select type,avg(price) from star group by type;
各个分组身份的最大的 select type,min(price) from star group by type;
各个分组身份的条目数 select type,count(*) from star group by type;

各个分组身份的条目数 并大于2的显示条目
select type,count(*)from star group by type having count(*)>2;
每组 条目里年龄大于30的显示条目;WHERE只能在分组前
SELECT TYPE,COUNT(*)FROM star WHERE 年龄>30 GROUP BY TYPE
每组 条目里年龄大于30的 并且条目大于2的 显示条目;HAVING可以分组后再过滤
SELECT TYPE,COUNT(*)FROM star WHERE 年龄>30 GROUP BY TYPE HAVING COUNT(*)>2

多表

MySQL多表 商品表的外键和分类表的主键
添加一个外键:alter table 表名 add foreign key(外键字段的字段名) references 主键表名 (主键字段名) ['ɔ:ltə] [ˈfɒrən][ˈrefrənsiz]  [ˈkætəgɔri]
alter table star add foreign key(type)references category(cid)
删除一个外键：alter table 表名 drop foreign key 约束名
ALTER TABLE star DROP FOREIGN KEY star_ibfk_1

- 一对多(重点)
  - 应用场景:学生和班级、商品和分类、用户和订单
    - 建表原则:在多的一方添加一个外键，指向一的一方的主键
- 多对多(重点)
  - 应用场景:商品和订单、学生和选课
  - 建表原则：创建一张第三方表，该表除了id字段之外，还至少得有两字段，分别作为外键指向两张表的主键
- 一对第一
  - 应用场景:人和身份证号
  - 建表原则:先当成一对多来处理，再给多的一方的外键字段添加一个唯一约束

交叉查询得到的结果是一个笛卡尔积，这个结果中包含错误数据。
select*from star,category

连接查询:为了筛选交叉查询中获取带的正确数据(重点)
  其实就是在交叉查询的基础上添加一个条件进行筛选正确数据

满足条件则显示，不满足则不显示 两种结果一样
隐式内连接查询 查询结果是a的外键star.type=b的主键(cid)/两个int相同就显示 b的主键对应的数据(cname) 简写的结果一模一样只是精简了代码
  - select * from a表,b表where 条件(a表的外键=b表的主键)

SELECT*FROM star,category WHERE star.type=category.cid
SELECT*FROM star s,category c WHERE s.type=c.cid 可以用别名简化
显式 内连接查询 显示里面的,on只能用主外键关联作为条件,如果还有其它条件,后面加where
  - select * from a表 inner join b表 on 条件

SELECT*FROM star s JOIN category c ON s.type=c.cid
SELECT*FROM star s JOIN category c ON s.type=c.cid 简化

外连接查询：显示主表 的所有数据，从表 的数据若满足条件则显示，不满足则以null显示
- 左外连接:以join左边的表为主表/star
select * from a表left outer join b表 on 条件

SELECT*FROM star s LEFT  OUTER JOIN category c ON s.type=c.cid
- 右外连接:以join右边的表为主表 /category
  - select * from a right outer join b on 条件
SELECT*FROM star s RIGHT  OUTER JOIN category c ON s.type=c.cid

子查询(重点)
定义：一个select语句中嵌套另外一个完整的select语句，也就是说一个select语句作为另外一个select语句的条件

1--先查出最高身价明星 2再根据最高身价查出明星信息
SELECT MAX(price) FROM star
SELECT*FROM star WHERE price=5050
子查询就可以合并成一句查询 嵌套
SELECT*FROM star WHERE price=(SELECT MAX(price)FROM star)

--1查宝宝的身价2.以宝宝的身份去查询身价大于这个数的明星信息--
SELECT price FROM star WHERE NAME="宝宝"
SELECT*FROM star WHERE price>5000
SELECT*FROM star WHERE price>(SELECT price FROM star WHERE NAME="宝宝")

--1查出小白的类别2 根据这个类别查同类别明星信息--
SELECT TYPE FROM star WHERE NAME="小白"
SELECT*FROM star WHERE TYPE=2
SELECT*FROM star WHERE TYPE=(SELECT TYPE FROM star WHERE NAME="小白")
SELECT*FROM star WHERE TYPE=(SELECT TYPE FROM star WHERE NAME="小白")AND NAME!="罗永浩"//后面还可以加上不等于的语句

SELECT*FROM star WHERE TYPE=(SELECT cid FROM category WHERE cname="宝剑队"); //查所有类别为宝剑队的明星

联合查询(了解)：合并两个select语句查询到的结果集

- union,去重
- union all，不去重

分页查询(重点)

- 关键字:limit a,b
  - a代表从第几个下标开始查询
SELECT*FROM star LIMIT 0,4; //第二次 4,4
  - b代表查询多少条数据，也就是每页显示的数据条数
- 将客户端传入的要查询的页数"page"转换成a
 a=(page-1)*b
对明星进行按价格从高到低排序，然后取第一个明星信息
SELECT * FROM star ORDER BY price DESC LIMIT 0,1

SELECT ename,job FROM emp WHERE deptno=10 //10号部门员工和工作
SELECT deptno,AVG(sal)FROM emp GROUP BY deptno 以部门分组 查出平均工资

Oracle数据库 [ˈɒrəkl]

安装

安装全部下一步,检查先决条件时的网络配置需求,勾选用户已验证,最后一步更改两个用户名把是否锁定帐户勾取消设置密码密码 scott 密码tiger hr 密码hr

连接

http://pc5268-20140727:1158/em/ 用户名sys 密码root 连接身份sysdba 也可以用ip地址

instantclient_12_1目录下 dos执行 sqlplus scott/tiger@192.168.44.68:1521/orcl 第一次要输用户名和密码scott 密码tiger

用NavicatPremium要在工具->选项->环境->OCI环境,填instantclient_11_2\oci.dll 下载的软件和下载的版本要一致32/64位

sql基础常识

在Oracle的学习之中，重点使用的是SQL语句，而所有的SQL语句都要在scott用户下完成，这个用户下一共有四张表，可以使用：

SELECT * FROM tab;

表结构详解

1、部门表：dept [dɪˈpɑ:tmənt]department

№	名称	类型	描述
1	DEPTNO	NUMBER(2)	表示部门编号，由两位数字所组成
2	DNAME	VARCHAR2(14)	部门名称，最多由14个字符所组成
3	LOC	VARCHAR2(13)	部门所在的位置

2、雇员表：emp [ɪmˈplɔɪi:] employee

№	名称	类型	描述
1	EMPNO	NUMBER(4)	雇员的编号，由四位数字所组成
2	ENAME	VARCHAR2(10)	雇员的姓名，由10位字符所组成
3	JOB	VARCHAR2(9)	雇员的职位包括管理管理的管理
4	MGR	NUMBER(4)	上级的字段雇员对应的领导编号，领导也是雇员
5	HIREDATE	DATE	雇员的雇佣日期
6	SAL	NUMBER(7,2)	基本工资，其中有两位小数，五倍整数，一共是七位
7	COMM	NUMBER(7,2)	奖金，佣金
8	DEPTNO	NUMBER(2)	雇员所在的部门编号

3、工资等级表：salgrade [ˈsæləri] salary grade [greɪd]

№	名称	类型	描述
1	GRADE	NUMBER	工资的等级
2	LOSAL	NUMBER	此等级的最低工资
3	HISAL	NUMBER	此等级的最高工资

4、资金表：bonus [ˈbəʊnəs]

№	名称	类型	描述
1	ENAME	VARCHAR2(10)	雇员姓名
2	JOB	VARCHAR2(9)	雇员职位
3	SAL	NUMBER	雇员的工资
4	COMM	NUMBER	雇员的奖金

SCOTT用户表空间下的表 SALGRADE工资等级表 BONUS奖金表 DEPT部门表 EMP员工表

HI用户下的表 REGIONS地区 COUNTRIES国家 LOCATIONS地址 DEPARTMENTS部门 EMPLOYEES员工 JOBS职位 JOB_HISTORY职位历史

sql结构化查询语言分类语言:四类语言

DDL数据定义语言操作数据库结构 create drop alter

DML数据操纵语言操纵的是数据 insert增 update改 delete删

DCL数据控制语言控制权限grant revoke

DQL数据查询语言 select

查询语法: select 查询列名 from 表名

查询列名处为要查询的最后过滤规则:查询全部列用* 单个/多个列用列名别名用,隔开全部列加单个/多个用表名.*,列名,列名

sql编写顺序 :select/选择结果要查询/显示的列 from/来自表名 where 条件判断 group by 分组的条件 having 分组之后的条件过滤 order by 排序

编写顺序:select..from..shere..group by..having.order by..

执行顺序 from..where..group by..having..select..order by..

limit属于mysql独有的方言 select*sal, from emp

规则:

Oracle体系结构数据库–实例orcl–表空间/用户操作表空间–数据文件

创建一个项目:先创建一个用户->再创建表空间/指定用户操作的表空间->创建表

和mysql最大区别 Oracle: 表是属于用户的 Oracle是属于多用户的

基本查询

select*from emp –查询emp表

或者 select emp.* from emp

select ename from emp 查姓名单列

SELECT ENAME from emp WHERE ENAME=’JONES’ 查单列并加条件

select ename as “姓&名”,job 工作 from emp –只查出两列并以别名姓名和工作显示表头

SELECT emp.*,job from emp查整个表并在后面查多出一列job的列

SELECT emp.*, sal**12 from emp 年薪 from emp;–查询所有多输出一列sal并计算查询员工年薪以别名显示

select 2+2*5 from dual –运算查询

别名中间有特殊字符需要使用双引号包含整个别名 as可以省略
select distinct job from emp –去除重复数据查询去除了重复的工作多行是每一行对应都相同就去除重复的

–null值不能参与运算 nvl(null,5)专门判断第一参数如果为null就返回参数2否则返回参数1

select sal,comm, nvl(comm,0)+sal from emp –查询出工资奖金和总收入总收入解决null不能计算用nvl

select concat(‘我爱’,’java’)from dual;–通用两个字符串拼接字符串用用单引

select ‘我爱’||’Java’||’go’from dual;–两个字符串拼接字符串用用单引

条件查询/where 符合条件的行

where后面的写法就是条件查询出符合条件的行

关系运算符 > >= < <= = != <> 逻辑 or and not

其它in在集合内 in null专门判断是null is not null不为null between..and在区间内 like模糊 any任意 all所有 exists存在

select*from emp where comm is not null and sal>1500–查能获得到奖金并且工资大于1500的员工资金不是null的条件并且..

select*from emp where comm is null and not(sal>1500)–没有奖金并且工资不大于1500的

select*from emp where sal between 1500 and 3000 –工资1500-3000区间

select*from emp where HIREDATE between to_date(‘1981-1-1’,’yyyy-mm-dd’) and to_date(‘1981-12-31’,’yyyy-mm-dd’); –查1981年1月1号到12月31日入职的员工转日期查询

select*from emp where ename=’smith’or empno=7499 –名字是smith或者编号7499都行

select*from emp where empno in(7369,7499,7521) –员工确定编号的几个确定列的范围中的多个

select*from emp where ename like ‘_M%’ –模糊查询员工名字第二个字符是M后面任意的

select*from emp where empno!=7369 order by comm desc nulls last,HIREDATE asc nulls last–排除编号7369 以奖金降序排入职按升序排遇到空值放后面

单行函数

单行函数/对单个值进行处理数值函数字符函数日期转换通用nvl
多行函数/聚合函数 sum avg max min count

数值函数

select ceil(-13.9)from dual–13 向下取整
select floor(-12.9)from dual–13 向上取整
select round(3.155,2)from dual–3.16 四舍五入留2位小数
select trunc(3.155,2)from dual–3.15 直接截断留2位小数
select mod(9,2)from dual–1 取模整除后的余数

字符

select concat(‘我爱’,’Java’)from dual–我爱Java 字符串拼接
select substr(‘abcdef’,2,3)from dual–bcd 字符串截取从第二个开始截三位开始位置0或1
select length(‘abcdef’)from dual–6 字符串长度
select replace(‘abcdef’,’a’,’b’)from dual–bbcdef 字符串替换
select trim(’s’from’sscdess’)from dual–cde 去掉两边的s

日期

select sysdate from dual –2018-02-28 23:31:07 获取当前时间

select ename,round((sysdate-hiredate)/7)from emp–获得员工入职的当前时间减入职时间 /7的周数的四舍五入

select ename,round(sysdate-hiredate)from emp –员工入职天数

select ename,round(MONTHS_BETWEEN(sysdate,hiredate))from emp–获得员工入职的月数专用函数

select ename,round(MONTHS_BETWEEN(sysdate,hiredate)/12)from emp–获得员工入职的年数

select add_months(sysdate,3)from emp–三个月以后的日期是多少

转换

select to_number(‘10’)+24 from dual;–34 字符串转数值

select ‘10’+24 from dual;–34 运算时会自动转数值不是就报错

经数值转字符:

select sal,to_char(sal,’9999.99’)from emp;–1600-1600.00 对工资经数值转字符处理后对比只能用9去匹配格式化字符串这里是前面最多可以四位数后面加两个小数 1600-1600.00 16000报错
select sal,to_char(sal,'$9,999.99')from emp;--1600-$1,600.00 格式化为加美元符加千位符

日期转字符
select to_char(sysdate,’yyyy-mm-dd’) from dual;–2018-02-28格式化只显示日期
select to_char(sysdate,’yyyy’) from dual;–2018 只显示年
select to_char(sysdate,’ddd’) from dual;–059 现在距今年过了几天 dd距这个月过了几天 d 距这个星期过了几天
select to_char(sysdate,’day’) from dual;–webnesday 现在星期几的英语 dy星期几的缩写

字符转日期
select*from emp where HIREDATE between to_date(‘1981-1-1’,’yyyy-mm-dd’) and to_date(‘1981-12-31’,’yyyy-mm-dd’); –查1981年1月1号到12月31日入职的员工转日期查询

通用

处理空值nvl

-null值不能参与运算 nvl(null,5)专门判断第一参数如果为null就返回参数2否则返回参数1

select sal,comm, nvl(comm,0)+sal from emp –查询出工资奖金和总收入总收入解决null不能计算用nvl

nvl2 相当于判断第一个参数是不是Null 的三元运算符
select nvl2(null,5,6)from dual –6如果第一个参数是null返回第三个否则返回第二个
select nvl2(2,5,6)from dual –6如果第一个参数是null返回第三个否则返回第二个

条件表达式 case

select ename,case ename when’KING’ then ‘王者’ when ‘ALLEN’ then ‘艾伦’ else ‘路人甲’ end from emp

独有的未匹配到的为null

select ename,decode(ename,’KING’,’王者’,’SMITH’,’史密斯’)from emp

多行函数/聚合函数

where是在分组之前执行的条件过滤不能接聚合可以接单行 having是分组之后的条件过滤可以接聚合

sql编写顺序 select..from..where..group by..having..order by..

sql执行顺序 from..where..group by..having..select..order by..

函数计数count 最大max 最小min 平均avg 求和sum 会忽略空值

select count(*)from emp;–查询所有员工人数 14
select sum(comm)from emp;–查询奖金总金额 2200
select avg(comm)from emp–查询全公司平均奖金 550 会忽略空值不行
select sum(comm)/count(*) from emp;–用总奖金除总人数就是对的 157.1

笛卡尔积/两张表的乘积

–笛卡尔积的概念就是同时查询两张表,列数相加,行数相乘

select*from emp,dept –员工14行和部门4行查出了56行

–在笛卡尔积基础上筛选出有意义的数据隐式内连接可以加别名
select*from emp,dept where emp.deptno=dept.deptno;

内连接:内连接(自然连接): 只有两个表相匹配的行才能在结果集中出现内连接是只显示满足条件外左连接显示左边全部的和右边与左边相同的右连接显示右边全部的和左边与右边相同的

加别名内隐式连接 员工8列14行部门3列4行查出了11列14行员工表在前部门前在后

select*from emp e,dept d where e.deptno=d.deptno;

–显式内连接inner join ..on 查询出两张表交叉的以后的内容和隐式一样结果
select*from emp e inner join dept d on e.deptno=d.deptno;

以下都是查几列的并加条件的内连接查询

–查询员工编号,姓名经理编号姓名
from来自于员工的两张并取别名来查,再第一张的编号和第二张的经理编号就得出了是领导的员工编号最后select过滤出来的就是员工编号姓名经理编号姓名的列
select e.empno,e.ename,e.mgr,m.ename from emp e,emp m where e.mgr=m.empno

–查询员工编号,姓名部门经理编号姓名
select e1.empno,e1.ename,e1.mgr,m1.ename,d1.dname from emp e1,emp m1,dept d1 where e1.mgr=m1.empno and e1.deptno=d1.deptno;
–查询员工编号姓名部门经理编号姓名部门
select e1.empno,e1.ename,d1.dname,e1.mgr,m1.ename,d2.dname from emp e1,emp m1,dept d1,dept d2 where e1.mgr=m1.empno and e1.deptno=d1.deptno and m1.deptno=d2.deptno

–查询出每个雇员的姓名，工资，部门名称，工资在公司的等级及其领导的姓名，领导的工资，以及领导所对应的等级
select DISTINCT e.ENAME,e.SAL,d.DNAME,g.GRADE,s.ENAME mgr_name,g2.grade m_grade from emp e,dept d,SALGRADE g,emp s,salgrade g2 where e.DEPTNO=d.DEPTNO and e.SAL BETWEEN g.LOSAL and g.HISAL and s.EMPNO=e.MGR and s.sal between g2.losal and g2.hisal

外连接: left/right join on
左外连接: 就是在等值连接的基础上加上左主表中的未匹配数据
右外连接:等值连接的基础上加上被右连接表的不匹配数据

全外连接 full join on

全外连接是在等值连接的基础上将左表和右表的未匹配数据都加上

select*from emp e left outer join dept d on e.deptno=d.deptno –左外连接

子查询/嵌套:

一个查询语句嵌套另一个查询内容解决复杂查询单行子查询出来的结果只有单个值 > >= = > >= != 多行子查询出来的结果有多行 in any all exists

–查询最高工资的员工信息先查信息 where条件为最大的
select*from emp where sal=(select max(sal)from emp)–7839

单行子查询:子查询出来的结果只有一行

–查询出比员工编号7654的工资高同时和7788号从事相同工作的员工信息先查员工信息再条件查工资比7654高同时..
select*from emp where sal>(select sal from emp where empno=7654) and job=(select job from emp where empno=7788)

查询每个部门最低工资的员工信息和他所在部门信息先查部门..
select deptno,min(sal) minsal from emp group by deptno–先查各部门最低工资的分组再查每个部门最低的员工连接他所在的部门
select*from emp e1,(select deptno,min(sal)minsal from emp GROUP BY deptno)t1, dept d1 where e1.DEPTNO =t1.deptno and e1.sal=t1.minsal and e1.deptno=d1.deptno;

多行子查询 in , not in , any , all, exists,

in表示值是否存在子查询结果集中 not in反之 any任意是表示子查询结果中任意一个，all表示子查询结果中的所有

– 查询出比10号部门任意员工薪资高的员工信息

select sal from emp where deptno = 10; –先查10号部门里任意员工工资再以条件查整个表
select * from emp where sal >any(select sal from emp where deptno = 10);

–查询所有是领导的员工信息编号是 mgr里的去重
select*from emp where empno in(select distinct mgr from emp)
select*from emp where empno =any(select distinct mgr from emp)
–查询所有不是领导的员工信息有空值is not null
select*from emp where empno not in(select distinct mgr from emp where mgr is not null)
select*from emp where empno !=all(select distinct mgr from emp where mgr is not null)

exists(查询语句): 存在如果查询语句,有结果,则返回true,否则返回false

–如果有7339编号存在就查询所有
select * from emp where exists(select * from emp where empno=7369);

– 查询有员工的部门信息
select * from dept d where exists(select * from emp e where e.deptno=d.deptno);
– 关联子查询和非关联子查询

关联子查询: 子查询依赖外层查询
先执行外层查询,然后再执行内层查询
非关联子查询: 子查询可以单独执行,不依赖外层查询条件
先执行子查询,再执行外层查询,内层子查询只执行一次
按照行划分: 单行子查询多行子查询

按照出现的位置划分:
      select 
      from   当作一张表处理,需要取别名
      where 
      having

select 后面接子查询:

select ename,deptno from emp;—- 查询员工姓名和部门名称下面再接子查询
select ename,(select dname from dept d where d.deptno=e.deptno) from emp e;

– from后面接子查询 ( 注意: 子查询当作表处理子查询一定要取一个别名)

薪资高于10号部门平均工资的所有员工信息having后面接子查询

select deptno,avg(sal) from emp group by deptno having avg(sal)>(select avg(sal) from emp where deptno=30);

练习一: 找到员工表中工资最高的前三名(只要前三条)

select * from emp order by sal desc;
rownum : 伪列 ,代表的是行号

rownum 默认起始值是 1 ,每查询出一条记录, rownum++
rownum :主要运用在分页查询

– 查询行号大于3的所有记录

select rownum,emp.* from emp where rownum > 3;

– 查询行号小于3的所有记录
select rownum,emp.* from emp where rownum < 3;

select rownum,emp.* from emp where rownum >=1;

– rownum没有顺序了
/*
from .. where … group by .. having .. select ..rownum.. order by
*/
select emp.* from emp order by sal desc;

select rownum,tt.* from (select emp.* from emp order by sal desc) tt;

– 查询工资最高的前三名
select rownum,tt.* from (select emp.* from emp order by sal desc) tt where rownum <=3;

练习二:找到员工表中薪水大于本部门平均薪水的员工

– 1.分组统计每个部门的平均薪水
select deptno , avg(sal) avgsal from emp group by deptno;
– 2. 连接查询结果
select * from emp e,(select deptno , avg(sal) avgsal from emp group by deptno) tt
where e.deptno = tt.deptno and e.sal > tt.avgsal;

练习三:统计每年入职员工的个数
select hiredate from emp;

– 只显示年
select to_char(hiredate,’yyyy’) from emp;

– 分组统计每年入职个数
select to_char(hiredate,’yyyy’) yy,count(1) cc from emp group by to_char(hiredate,’yyyy’);

– 1. 先将1987竖起来
select case yy when ‘1987’ then cc end
from
(select to_char(hiredate,’yyyy’) yy,count(1) cc from emp group by to_char(hiredate,’yyyy’)) tt;

– 2. 去除1987中空值
select sum(case yy when ‘1987’ then cc end) “1987”
from
(select to_char(hiredate,’yyyy’) yy,count(1) cc from emp group by to_char(hiredate,’yyyy’)) tt;

– 3. 计算Total
select sum(cc) “TOTAL”
from
(select to_char(hiredate,’yyyy’) yy,count(1) cc from emp group by to_char(hiredate,’yyyy’)) tt;

– 4. 合并1987和total

select sum(cc) “TOTAL”,

sum(case yy when ‘1987’ then cc end) “1987” from

(select to_char(hiredate,’yyyy’) yy,count(1) cc from emp group by to_char(hiredate,’yyyy’)) tt;

– 5. 最终结果
select sum(cc) “TOTAL”,

sum(case yy when ‘1987’ then cc end) “1987”,
sum(case yy when ‘1980’ then cc end) “1980”,
sum(case yy when ‘1981’ then cc end) “1981”,
sum(case yy when ‘1982’ then cc end) “1982”

from

(select to_char(hiredate,’yyyy’) yy,count(1) cc from emp group by to_char(hiredate,’yyyy’)) tt;

rownum 和rowid

– 查询第5-10条记录
– 1.查询前10条记录
select rownum line,emp.* from emp where rownum <=10;

– 2. 查询行号>=5的记录
select * from (select rownum line,emp.* from emp where rownum <=10) tt where line >=5;

rowid: 伪列,代表的每行记录在磁盘中存放的真实的物理地址rowid,用在索引查询

select rowid,emp.* from emp where deptno>20 order by sal;

集合运算: 并集,交集,差集

员工表 , 经理表
工资,婚否工资,婚否

所有员工的婚否

工资大于1500 或者 20号部门下的员工– 并集

select * from emp where sal > 1500
union
select * from emp where deptno=20;

– 工资大于1500 并且 20号部门下的员工–交集
select * from emp where sal > 1500
intersect
select * from emp where deptno=20;

– 工资大于1500 并且不是20号部门下的员工–差集
select * from emp where sal > 1500
minus
select * from emp where deptno=20;

– 集合运算注意事项
/*
1.列的数量要一致如果不足,可以用null补齐
2.列的类型要保持一致
3.列的顺序要一致

*/select ename,sal from emp where sal > 1500
union
select ename,null from emp where deptno = 20;

表空间

数据库 —> 实例Orcl —> 表空间(用户) —> 数据文件

地球 —> China —> 省份(用户) —> 土地

创建表空间 2创建用户 root3 用户去建表

       create tablespace 表空间的名称
       datafile '存放的未知'
       size   初始大小
       autoextend on
       next  每次增长多少

1创建表空间

先需要登录管理员帐号 system 密码root

create tablespace shenzhen
datafile ‘c:\shenzhen.dbf’
size 100m
autoextend on
next 10m;

– 删除表空间drop tablespace shenzhen;

2创建用户

create user 用户名 identified by 密码 default tablespace 表空间名称
*/
create user zhangsan
identified by zs408
default tablespace shenzhen;

3用户授权

grant create session to zhangsan;

revoke create session from zhangsan;

– 授予DBA(数据库管理员)角色,拥有权限
grant dba to zhangsan;

select * from scott.emp;
update scott.emp set ename=’ALLEN’ where ename = ‘ALL%EN’;

4创建表

格式:

create table 表名(字段字段类型 [约束],字段字段类型 [约束]
);

char(最大长度) : 固定长度字符串 char(10) hello , 占10个, 不满10 用空格补满

字段类型:

varchar2(最大长度) : 可变长度字符串varchar2(10) hello , 占5个

number(总长度,小数位数)

date : 年月日时分秒

timestamp : 时间戳 , 精确到毫秒

Long : 大字符串类型, 最大支持保存2G

CLOB : Character Large Object 字符串大对象最大4G

BLOB : Binary Large Object 二进制大对象 4G

– 创建表
create table person(
name varchar2(20),
age number,
salary number(10,2)
);

还可以– 使用子查询创建表, 相当于复制表,包含了表结构和表数据,但是不包含约束
create table emp as select * from scott.emp;
select * from emp;

使用子查询创建表,只要表的结构后面故意条件是没有的条件create table emp1 as select * from scott.emp where empno=1234567;
select * from emp1;

修改表

– 修改表格式: 添加列add, 重定义列 modify, 修改列名 rename, 删除列 drop, 修改表名,rename

alter table person add ( phone varchar2(11),address varchar2(20) );

– 重定义列 modify
alter table person modify phone number;

– 修改列名 rename
alter table person rename column phone to mobile;

– 删除列名 drop
alter table person drop column mobile;

– 修改表名
rename person to person1;

删除表 drop table person1;

表的约束

表的五大约束:

 主键约束: primary key , 非空唯一
 唯一约束: unique , 唯一可以为空
 非空约束: not null, 不能为空
 检查约束: check(条件),mysql中可以写,但是mysql直接忽略掉

 外键约束: foreign key , 约束从表中的记录必须参考主表中的记录

单表约束

create table person(
    pid number primary key,
    name varchar2(20) unique,
    age number not null,
    sex varchar2(20) check(sex in('男','女','妖'))
);
-- male,female,ladyboy
insert into person values(1,'zhangsan',18,'妖');
insert into person values(2,'lisi',18,'妖');

外键约束外键约束: foreign key , 约束从表中的记录必须参考主表中的记录主表是商品类型从表是商品表有主键是外键的是主表

create table category(
       cid number primary key,
       cname varchar2(20)
);

create table product(
       pid number primary key,
       pname varchar2(20),
       cno number
);

insert into category values(1,'手机数码');
insert into product values(100,'锤子',11);

truncate table product;
-- 添加外键约束
alter table product add foreign key(cno) references category(cid);
insert into product values(100,'锤子',1);

-- 删除主表: 先取消外键约束,再删除自己
drop table category cascade constraint;

-- 级联删除,添加外键约束
-- 
alter table product add foreign key(cno) references category(cid) on delete cascade;

-- 删除category主表中的记录
-- 级联删除: 先去从表中查看有没有关联的数据,如果有,则先删除从表中的记录,再删除主表中记录
delete from category where cid=1;

select * from category;
select * from product;

增删改表操作

/*
       插入数据 insert into 
*/
-- 使用子查询插入数据
select * from emp;
select * from emp1;

-- 将10号部门的所有员工信息插入到emp1中
insert into emp1 select * from scott.emp where deptno=10;

/*
      删除所有数据:
      delete     和      truncate
      逐条删除数据       先删除再创建表
      DML                 DDL
      支持事务           不支持事务
                         效率高
*/

通过Navicat Premium 实现Oracle基本操作

system root，连接后。我们点击面板上的“其他”下的选项“表空间->新建表空间->弹出设置界面

表空间类型不要改名随便填.DBF 大小100选m 路径是服务器上的地址自动扩展on 下一个大小20M 其它不要填保存服务器对应路径就有个文件了

新建用户:点击面板上的“用户”选项->新建用户->设置界面

用户名”处填写的用户名字母一定是大写,密码设置,默认表空间”处，我们选择理科前面我们新建的表空间临时表空间选temp

“成员属于”中我们选择了“DBA”授予和默认就可以保存了。其实对于一般的用户，我们只需赋予connect、resource、服务器权限create view这几个权限即可。但是这里我们为了演示方便就直接选择“DBA”就可以保存了..

事务

/*
   事务: 一组操作,要么都成功,要么都失败

   事务特型:
       原子性
       一致性
       隔离性
       持久性

   不考虑隔离级别的问题：　脏读，　虚读／幻读，不可重复读

   mysql隔离级别:
        READ UNCOMMITTED 读取未提交  脏读，　虚读／幻读，不可重复读
        READ COMMITTED   读取已提交, 虚读／幻读，不可重复读
        REPEATABLE READ  可重复读, 虚读／幻读，
        SERIALIAZABLE   串行化 

  Oracle隔离级别:
        READ COMMITTED (默认)
        SERIALIAZABLE
        READ ONLY 

   事务回滚点/保存点
        savepoint 保存点的名称

        rollback to 保存点  

    主要使用场景: 数据迁移的时候, 1000万  100万提交一次, 50万的时候,            
*/
create table lou(
   f number primary key
);



-- PLSQLB编程
declare
  -- 声明部分
begin
  -- 业务逻辑
  insert into lou values(1);
  insert into lou values(2);
  insert into lou values(3);
  savepoint aa;
  insert into lou values(3);
  insert into lou values(4);
  insert into lou values(5);
  commit;
exception -- 捕获异常
  when others then -- 处理异常
     rollback to aa;
     commit;
end;

select * from lou;

视图:对查询结果的封装

语法: create [or replace] view 视图名称 as 查询语句

/*
   视图view: 
       实际是对查询结果的封装,视图本身不存储任何数据,所有的数据都存放在原来的表中
       作用:
           封装复杂查询语句
           屏蔽表中的细节

       语法:
           create [or replace] view 视图名称 as 查询语句 [with read only]
*/
-- 封装员工表
create or replace view view_test1 as select ename,job,mgr from emp;
select * from view_test1;

-- 同义词
create synonym yuangong for view_test1;
select * from yuangong;


-- 通过视图去修改数据
update view_test1 set ename='SMITHaaaa' where ename='SMITH';

select * from emp;

-- 创建只读视图
create or replace view view_test2 as select ename,job,mgr from emp with read only;
update view_test2 set ename='SMITHaaaa' where ename='SMITH';

序列生成自增的编号

语法: create sequence 序列的名称

/*
    序列: 
        1,2,3,4,5,6,7
    模拟类似auto_increment作用
    作用: Oracle中主要是用来生成自增的编号 

    语法:
        create sequence 序列的名称
        start with 从几开始
        incrementby 每次增长多少
        minvalue | nominvalue
        maxvalue | nomaxvalue
        cycle | nocycle
        cache n 缓存

        cache 5
              1,2,3,4,5
                        6,7,8,9,10 

      序列的属性:
          currval : 当前值 , 必须是调用过一次nextval之后
          nextval  : 下一个值  

      注意: 序列中的数,一旦取过了, 无论回滚还是异常,都是永不回头的向下递增                
*/
-- 1,3,5,7,9,1,3,5,7,9.....
create sequence seq_test1
start with 1
increment by 2
minvalue 1
maxvalue 9
cycle
cache 3;

-- 获取当前值
select seq_test1.currval from dual;

-- 获取下一个值
select seq_test1.nextval from dual;


-- 通常创建序列的写法
create sequence seq_test2;
select seq_test2.nextval from dual;

truncate table lou;

select * from lou;

insert into lou values(seq_test2.nextval);

索引 int_coll 索引后查询巨快

什么是索引: 一种已经排好序的数据结构能够查询效率, 索引还可以提高排序效率

语法: create index 索引名称 on 表名(列名1,列名2….);

主键约束: 自带唯一索引唯一约束: 自带唯一索引

-- 创建测试数据500万
-- 创建一张表
create table wbw(
   c1 number primary key,    
   c2 varchar2(20),
   c3 varchar2(20)
); 

-- 插入500万条记录索引
create sequence seq_wbw;


-- PLSQL中的循环
declare

begin
   for i in 1..5000000 loop
     insert into wbw values(seq_wbw.nextval,'c2:'||i,'c3:'||i);
   end loop;  
   commit;  
end;

select count(*) from wbw;

-- 在没有创建索引的情况下 6.489s
select * from wbw where c2='c2:4000000';

-- 0.023
select * from wbw where c1=4000000;


-- 创建索引
create index wbw_c2 on wbw(c2);

PLSQL编程

Procedure Language 实际上是Oracle对SQL语言的能力扩展,让SQL语言拥有了if条件判断,for循环处理

declare -- 声明部分相当于语法:java-- class--- main
变量名  变量类型 := 初始值 
变量名  emp.sal%type; -- 引用类型的变量 知道具体的列
变量名 emp%rowtype;  -- 记录型变量  记录一行
变量名 number;
begin 
-- 业务逻辑
dbms_output.put_line('hello world !'); 相当于是java打印
end;

基本语法

declare
i number :=5000;--先定义变量i并赋值
begin
  dbms_output.put_line('你好呀'||i);--打印结果是:你好呀5000
end;

declare
  vsal emp.sal%type;--先定义 引用类型的变量vsal 为具体列
begin
  select sal into vsal from emp where empno=7369;--给变量赋值 为查询结果编号7369的工资
 dbms_output.put_line(vsal);--打印vsal 是emp表里工资 条件是编号7369输出是:4600
end;

declare
  vrow emp%rowtype;--先定义 记录型变量vsal
begin
select * into vrow from emp where empno=7369;----给变量赋值 为编号是7369的一行记录
 dbms_output.put_line(vrow.empno||':'|| vrow.ename);--打印 7369:SMITH/史密斯
end;

if条件判断

/*
  if条件判断
     if 条件1 then
      elsif 条件2 then
      else
      end if;
*/
-- 根据不同年龄输出信息
declare
   age number := &请输入;--&会弹出输入框 请输入 为输入名称输入的 为值
begin
   if age<=18 then
     dbms_output.put_line('未成年人');  
   elsif age>18 and age<=24 then
     dbms_output.put_line('年轻人');
   elsif age>24 and age<48 then
     dbms_output.put_line('中年人');
   else
     dbms_output.put_line('老年人');
   end if;  
end;

三种循环

三种循环语法
for循环: for 变量名 in 起始值..结束值  loop
       end loop; 

while循环语法:     while 条件 loop
          end loop;
         loop

exit循环语法:    exit when 退出的条件
     循环体
   end loop;

-- 输出1-10
declare

begin
  for i in 1..10 loop
    dbms_output.put_line(i);
  end loop;
end;

-- 输出10-1
declare

begin
  for i in reverse 1..10 loop
    dbms_output.put_line(i);
  end loop;
end;

-- 输出1-10
declare
  i number :=1; --变量i赋值为1
begin
  while i<=10 loop
    dbms_output.put_line(i);
    i := i+1;--i的变化为+1
  end loop;
end;


-- 输出1-10
declare
   i number :=1;
begin
   loop
     exit when i>10;
     dbms_output.put_line(i);
     i := i+1;
   end loop;
end;

游标是对查询结果集的封装

   游标: (光标/指针)  是对查询结果集的封装, 相当于是jdbc中的ResultSet
      语法:
        声明游标:
cursor 游标名 is 查询语句
cursor 游标名(参数名 参数类型) is 查询语句 where empno=参数名

        开发步骤:
            1.打开游标 open 游标名
            2.从游标中提取数据:
                   fetch 游标名 into 变量
                         游标名%notfound  没有数据
                         游标名%found     找到数据
            3.关闭游标 close 游标名               


-- 输出所有员工的信息
declare
   cursor vemps is select * from emp; -- 声明游标
    vrow emp%rowtype;-- 声明变量
begin
     open vemps; --1. 打开游标
       loop  --2. 提取数据
        fetch vemps into vrow;--赋值给vrow
         exit when vemps%notfound;-- 判断无数据就退出查询
                dbms_output.put_line('姓名:'||vrow.ename||' 工资:'||vrow.sal);-- 打印数据
   end loop;
       close vemps; -- 关闭游标
end;


-- 输出指定部门的员工信息
declare
   -- 声明游标
   cursor vemps(vdeptno number) is select * from emp where deptno = vdeptno;
      vrow emp%rowtype;  -- 声明记录型变量
begin
      open vemps(20);-- 1. 打开游标
  loop-- 2.循环遍历游标
      fetch vemps into vrow;
      exit when vemps%notfound;--判断没有数据就退出
      -- 打印数据
      dbms_output.put_line('姓名:'||vrow.ename||' 工资:'||vrow.sal);
   end loop;
      close vemps;-- 3. 关闭游标
end;

游标练习

-- 按照职位涨工资,总裁涨1000 , 经理涨800 , 其他人涨400
/*
   游标: 所有员工
   遍历所有员工
       根据职位涨工资    
*/
declare
  -- 声明游标
  cursor vemps is select * from emp;    
  -- 声明记录型变量
  vrow emp%rowtype; 
begin
  -- 1.打开游标
  open vemps;

  -- 2.游标 循环提取数据给变量
  loop
     fetch vemps into vrow;
     exit when vemps%notfound;--游标没有查询了才退出

     -- 根据职位涨工资    
     if vrow.job = 'PRESIDENT' then
        update emp set sal=sal+1000 where empno=vrow.empno;
     elsif vrow.job = 'MANAGER' then
        update emp set sal=sal+800 where empno=vrow.empno;  
     else
        update emp set sal=sal+400 where empno=vrow.empno;
     end if;
  end loop;
  -- 关闭游标
  close vemps;

  -- 提交数据
  commit;
end;


/*
  excel产品 --- > xml,json   php,python
  扩展: 使用for循环遍历游标

*/
declare
 -- 声明游标
 cursor vemps is select * from emp;
begin
  for vrow in vemps loop
      dbms_output.put_line(vrow.ename);
  end loop;
end;

例外相当于是java异常

/*
  例外 (意外): 相当于是java异常
    语法:
      declare
         声明部分
      begin
         业务逻辑
      exception
         处理例外
         when 例外1 then
           when 例外2 then
              when others then
              end;

  常见的系统的例外
      zero_divide : 除零例外
      value_error : 类型转换
      no_data_found : 没有找到数据例外
      too_many_rows : 查询出多行记录,但是赋值给了单行变量
*/
declare
  i number;   
   vrow emp%rowtype; 
begin
  -- i := 5/0;
  -- i := 'aaa';
  -- select * into vrow from emp where empno=1234567;
    select * into vrow from emp ;
exception
  when too_many_rows then
    dbms_output.put_line('查询出多行记录,但是赋值给了单行变量');
  when no_data_found then
    dbms_output.put_line('发生了没有找到数据例外');
  when value_error then
     dbms_output.put_line('发生了类型转换的例外');
  when zero_divide then  
     dbms_output.put_line('发生除零例外');
  when others then
     dbms_output.put_line('发生了未知的例外');
end;

自定义例外

/*
     自定义例外
         声明:
              例外名称 exception;
         抛出自定义例外:
              raise 例外名称
*/
declare
   -- 声明例外
   no_emp_found exception;           
begin
  -- 抛出例外
   raise no_emp_found;
exception
  -- 捕获例外
  when no_emp_found then
    dbms_output.put_line('自定义例外');   
  when others then
    dbms_output.put_line('未知例外');   
end;


- 查询指定编号的员工,若这个员工没有找到,则抛出自定义的例外
-- 正确做法:
declare
   -- 声明例外
   no_emp_found exception;
   -- 定义记录型变量
   vrow emp%rowtype;
   -- 声明游标
   cursor vemps is select * from emp where empno=1234567;
begin
   -- 打开游标
   open vemps;

   -- 向下移动游标
   fetch vemps into vrow;

   -- 判断游标是否找到数据
   if vemps%notfound then
      raise no_emp_found;
   end if;

   -- 关闭游标
   close vemps;
exception 
   when no_emp_found then
     dbms_output.put_line('捕获了自定义的例外');
   when others then
     dbms_output.put_line('未知的例外');   
end;

存储过程编译好的PLSQL代码片断,封装在数据库中相当于方法调用的时候传参数

/*相当于是在java main函数里面写了一些业务逻辑
-- 将main函数中的业务逻辑抽取成单个的方法
    存储过程: 实际上是将一段已经编译好的PLSQL代码片断,封装在数据库中
           作用:
              1. 提高执行效率
              2. 提高代码复用性

          语法:
              create [or replace] procedure 过程名称(参数1 in|out 参数类型,参数2 in|out 参数类型)
              is | as
                 -- 声明部分
              begin
                 -- 业务逻辑
              end;
*/ 
-- in代表输入参数 out代表输出参数
-- 给指定员工涨薪,并打印涨薪前和涨薪后的工资
-- 员工编号 : 输入参数
-- 涨多少 : 输入参数
/*
   1. 查询当前工资
   2. 打印涨薪前工资
   3. 涨工资
   4. 打印涨薪后的工资
   5. 提交数据
*/
-- select * from emp where empno=1234567; ---> 发送给数据库 ---> 分析编译---> 执行

-- 给指定员工涨工资, 并且打印涨工资前的工资和涨工资之后的工资
-- 参数: 员工编号 in
-- 参数: 涨薪数量 in
create or replace procedure proc_updatesal(vempno in number,vcount in number)
is
  -- 声明变量,接收当前工资
  vsal emp.sal%type;
begin
  -- 1.查询当前工资
  select sal into vsal from emp where empno=vempno;
  -- 2.打印当前工资
  dbms_output.put_line('涨薪前:'||vsal);
  -- 3.涨工资update
  update emp set sal=vsal+vcount where empno=vempno; 
  -- 4.打印涨薪后的工资
  dbms_output.put_line('涨薪后:'||(vsal+vcount));
  -- 5.提交事务
  commit;
end; 

-- 调用存储过程,方式1
call proc_updatesal(7369,100);

-- 方式2
declare

begin
   proc_updatesal(7369,200);
end;

-- 封装一个获取指定编号员工年薪的存储过程
-- 参数: 员工编号 in
-- 参数: 年薪     out
create or replace procedure proc_getyearsal(vempno in number,vyearsal out number)
is       
begin
   select sal*12+nvl(comm,0) into vyearsal from emp where empno=vempno;
end;

-- 调用获取年薪
declare
   yearsal number;
begin
   proc_getyearsal(7369,yearsal);

   dbms_output.put_line('年薪:'||yearsal);
end;


-- 获取指定编号员工的年薪
/*
   编号: in  输入
   年薪: out 输出
*/
create or replace procedure proc_getyearsal(vempno in number,vyearsal out number)
is

begin
  select sal*12+nvl(comm,0) into vyearsal from emp where empno=vempno;
end;

-- plsql代码片断中调用
declare
   yearsal number;
begin
   proc_getyearsal(7369,yearsal);
   dbms_output.put_line(yearsal);
end;


-- 封装存储过程,输出的是游标类型, 所有员工
/*
   sys_refcursor : 系统引用游标
*/
create or replace procedure proc_getemps(vemps out sys_refcursor)
is

begin
    -- 打开游标, 谁调用谁关闭
    open vemps for select * from emp;   
end;

declare
  vemps sys_refcursor;
  vrow emp%rowtype;
begin
  -- 调用存储过程
  proc_getemps(vemps);

  loop
     fetch vemps into vrow; 
     exit when vemps%notfound;  
     dbms_output.put_line(vrow.ename);
  end loop;
  -- 关闭游标
  close vemps;
end;

存储函数

/*
    存储函数: 实际上是将一段已经编译好的PLSQL代码片断,封装在数据库中    
          作用:
              1. 提高执行效率
              2. 提高代码复用性
         语法:
create [or replace] function 函数名称(参数1 in|out 参数类型) return 返回类型
              is
              begin
              end; 

          存储过程和存储函数:
              1. 函数有返回值,过程没有
              2. 函数可以直接在SQL语句中使用,过程不可以
              3. 函数能实现的功能,过程能实现
              4. 过程能实现的功能,函数也能实现

              5. 函数和过程本质上没有区别

           通常情况下,我们自己开发封装的是存储过程
*/
-- 存储函数: 获取指定编号员工的年薪
-- 参数 : 员工编号
-- 返回 : 年薪
create or replace function func_getyearsal(vempno number) return number
is 
  -- 声明变量
  vyearsal number;
begin
  select sal*12+nvl(comm,0) into vyearsal from emp where empno=vempno;
  return vyearsal;
end;

-- 调用
declare
  yearsal number; 
begin
  yearsal := func_getyearsal(7369);
  dbms_output.put_line('年薪:'||yearsal);
end;
-- 直接在SQL语句中使用
select emp.*,func_getyearsal(empno) from emp;



/*
    使用java调用数据库中封装好了存储过程

    用户注册 ---> 数据校验
    javaEE后台什么校验都不写, 直接将所有校验都丢给数据库存储过程

    java调用存储过程的步骤:
         JDBC开发步骤:
               1. 注册驱动
               2. 获取连接
               3. 获取执行SQL的对象
               4. 执行SQL
               5. 获取结果
               6. 释放资源
*/
-- java 调用一个获取年薪的存储过程
-- 练习 : java调用输出类型为游标的存储过程
/*
   cursor 游标名 is 查询结果

   sys_refcursor : 系统引用游标, 游标类型声明
*/
create or replace procedure proc_getemps(vemps out sys_refcursor)
is

begin
   -- 输出sys_refcursor20号部门下的所有员工
   -- 打开游标的时候,指定结果集
   open vemps for select * from emp where deptno=20;
   -- 谁用谁关闭
end;
-- 调用
declare
  emps sys_refcursor;
  vrow emp%rowtype;
begin
  -- 已经打开并且指定了结果集
  proc_getemps(emps);

  --循环提取游标中的数据
  loop
     fetch emps into vrow;  
     exit when emps%notfound;

     dbms_output.put_line('姓名:'||vrow.ename);
  end loop;
  -- 关闭游标
  close emps;
end;


/*
   自定义例外
   存储过程
   存储函数
   java调用存储过程

   触发器:
         当用户对表执行了insert | update | delete 这些操作的时候,
                        可以使用触发器去触发一段PLSQL代码逻辑的执行
           作用:
                监听表中数据变化
                对表中数据进行校验             


           语法:
                create or replace trigger 触发器名称
                before | after
                insert | update | delete
                on 表名
                [for each row]
                declare

                begin

                end;  

         触发器的分类:
               行级触发器: 一条SQL语句, 影响了多少行记录, 触发器就会执行多少次
                      两个内置对象:
                               :new   新的记录  
                               :old   旧的记录
                      update emp set sal=sal+100;

                           :new.sal  ---> sal+100
                           :old.sal  ---> sal

               语句级触发器: 一条SQL语句, 无论影响了多少行记录, 都只触发一次                                
*/
-- 若用户向表中插入数据之后, 打印一句话
create or replace trigger tri_test1
after
insert 
on emp
declare

begin
   dbms_output.put_line('有人插入了....');
end;

insert into emp(empno,ename) values(9527,'华安');
-- 执行一条更新工资的语句

-- 周二老板不在,不能办理员工入职(不能向员工表中插入数据)
-- 触发器
-- before insert
-- 判断今天是否是周二
select trim(to_char(sysdate,'day')) from dual;

create or replace trigger tri_checkday
before
insert
on emp
declare
   vday varchar2(20);
begin
   -- 查询当前周几
   select trim(to_char(sysdate,'day')) into vday from dual;
   -- 判断是否为周二,若为周二,则需要中断插入操作
   if vday = 'tuesday' then
     --                   -20000 - -20999
     raise_application_error(-20001,'周二老板不在,不能插入');
   end if;
end;

insert into emp(empno,ename) values(9527,'华安');

select * from emp;

-- 语句级触发器
create trigger tri_test3
before
update
on emp
declare

begin
  dbms_output.put_line('语句级触发器'); 
end;

-- 行级触发器
create or replace trigger tri_test4
before
update
on emp
for each row
declare

begin
  dbms_output.put_line('行级触发器,旧的工资:'||:old.sal||'  新的工资:'||:new.sal); 
end;

update emp set sal=sal+100;

-- 6个月 ---> 人事 加薪 ---> 加10块钱 ---> 老板签字
-- 校验员工薪资 调整后的工资一定要 大于 薪资调整前的工资
-- 触发器:  before update on emp
-- 行级触发器
create or replace trigger tri_checksal
before
update
on emp
for each row
declare

begin
  -- 调整后的工资 <= 薪资调整前的工资 ,则中断更新操作
  -- :new.sal    <= :old.sal
  if :new.sal <= :old.sal then
     raise_application_error(-20002,'坑爹的,降薪啦!');
  end if;
end;

update emp set sal=sal-100;

/*
     使用触发器模拟类似auto_increment功能

     当用户插入的时候,若为sid为null,则给sid赋值一个编号

*/
create table stu(
     sid number primary key,
     name varchar2(20)
);

-- 创建一个序列
create sequence seq_stu;

-- 触发器: before insert on stu
-- 行级触发器
create or replace trigger tri_auto
before
insert 
on stu
for each row
declare

begin
   -- 从序列中查询一个数字出来,赋值给sid
   select seq_stu.nextval into :new.sid from dual;
end;

-- 同样一张表,有时候自己指定id, 有时候需要数据库自动生成id
insert into stu values(null,'zs');
insert into stu values(4,'zs');
select * from stu;

小小小小的爱java

关注

2
点赞
踩
12

收藏

觉得还不错? 一键收藏
0
评论
Oracle从入门到精通最常用命令

常用sql改表名 rename table 旧表名 to 新表名表头 create table 表名(字段名类型约束,..)增列 alter table 表名 add 列名类型约束；改列 alter table 表名 change 旧列名新列名类型约束删列 alter table 表名 drop 列名插行 insert into 表名 values(值1,值2....
复制链接

扫一扫