中文乱码问题解决 1.查看服务器端编码 select userenv('language') from dual; 我实际查到的结果为:AMERICAN_AMERICA.ZHS16GBK 2.执行语句 select * from V$NLS_PARAMETERS 查看第一行中PARAMETER项中为NLS_LANGUAGE 对应的VALUE项中是否和第一步得到的值一样。 如果不是，需要设置环境变量. 否则PLSQL客户端使用的编码和服务器端编码不一致,插入中文时就会出现乱码. 3.设置环境变量 计算机->属性->高级系统设置->环境变量->新建 设置变量名:NLS_LANG,变量值:第1步查到的值， 我的是 AMERICAN_AMERICA.ZHS16GBK 4.重新启动PLSQL,插入数据正常 ///////////////////////////////////////////////////// //语句查询 //不为空 select * from emp where comm is not null; //转义字符 escape相当于是告诉数据库 \ 是一个转义字符 select * from emp where ename like '%\%%' escape '\'; select * from emp where ename like '%#%%' escape '#'; //条件表达式 条件表达式: 通用的方式: case 列名 when 值1 then 新内容 when 值2 then 新内容 when 值3 then 新内容 else 默认值 end Orcale特有的方式: decode(ename,if1,then1,if2,then2,if3,then3,默认值) //举例 select ename from emp; select case ename when 'SMITH' then '斯密斯' when 'ALLEN' then '艾伦' when 'WARD' then '沃德' else '隔壁老王' end "别名" from emp; select decode(ename,'SMITH','曹操','ALLEN','关羽','貂蝉') from emp; //group by 一般是要聚合函数一起使用的. select deptno,avg(sal) from emp group by deptno ; 凡和聚合函数同时出现的列名，一定要写在group by 之后 having是分组后筛选，不能离开group by 单独存在 有having一定有group by，有group by 不一定有having where和having的区别: - 1.having是在分组后对数据进行过滤.where是在分组前对数据进行过滤 - 2.having后面可以使用聚合函数(统计函数)where后面不可以使用聚合函数 - 3.WHERE是分组前记录的条件，如果某行记录没有满足WHERE子句的条件，那么这行记录不会参加分组；而HAVING是对分组后数据的约束。 //不等号的两种写法 select * from emp where empno !=7499 select * from emp where empno <>7499 ////////////////////////////////////////////////////////// SQL编写顺序: select..from..where..group by ..having .. order by .. SQL执行顺序: from..where..group by..having..select.. rownum.. order by.. //////////////////////////////////////////////// 转换函数和日期函数 x to_char,to_date, to_number//1.日期转成字符串,返回的值 to_char(sysdate,'d') 每周第几天 to_char(sysdate,'dd') 每月第几天 to_char(sysdate,'ddd') 每年第几天 to_char(sysdate,'ww') 每年第几周 to_char(sysdate,'mm') 每年第几月 to_char(sysdate,'q') 每年第几季 to_char(sysdate,'yyyy') 年 select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;//2字符串转成日期格式select to_date('2017-06-08','yyyy-mm-dd') from dual;//3.日期函数//当前时间select sysdate from dual;-- 查询员工入职的天数select emp.*, ceil(sysdate - hiredate) from emp;-- 查询员工入职的周数select emp.*, ceil((sysdate - hiredate)/7) from emp;-- 查询员工入职的月数select emp.*, months_between(sysdate,hiredate) from emp;-- 查询员工入职的年份select emp.*, months_between(sysdate,hiredate)/12 from emp;-- 几个月后的日期select add_months(sysdate,3) from dual; /////// ////////////////////////////////////////////////////////////// //第一天课程 select * from dept; /* Oracle体系结构: 数据库 ----> 实例(orcl) ---> 表空间(逻辑单位)(用户) ---> 数据文件(物理单位) 地球 ----> 国家 ---> 省份(省长,公民) ---> 中粮,山川河流 Oracle和mysql区别: 收费, 不开源 Oracle特有的方言/特有的语法 安全级别高 假设要做一个项目: mysql : 新建一个数据库 Oracle : 新建一个表空间,指定一个用户,让这个用去去创建相应的表里 mysql : 多数据库 Oracle : 多用户操作 ,所有的表都是放在用户下面 基本查询操作: SQL分类: DDL: 数据定义语言,定义的表的结构 , create , alter, drop ,truncate DML: 数据操纵语言, 操纵表中数据 , insert , update, delete DCL: 数据控制语言, 控制一些安全级别, 授权,取消授权 grant revoke DQL: 数据查询语言, 查询数据 , select , from , where 查询语句的基本结构: select 显示的列名 from 从哪张表查 [where 条件 ] */ select * from tab; select 1+1; -- 崩溃........ mysql可以,Oracle不行 /* dual : oracle中虚表/伪表, 主要是用来补齐语法结构的 */ select 1+1 from dual; select * from dual; -- 查询emp所有信息 select * from emp; /* 别名查询 : as 关键字, as可以省略 如果别名中包含特殊字符或者关键字,请用双引号 扩起来 注意: 单引号 ' ' 字符串值 , 基本上只有在别名的时候才会使用双引号 */ select ename as 姓名 from emp; select ename as "姓 from 名" from emp; /* 去除重复数据 distinct 注意: 如果是多列, 必须每一列数据都相同才算重复 */ select job,deptno from emp; select distinct job,deptno from emp; /* 四则运算: + - * / null值问题 , null : 未知的,不可预知的内容 不能做四则运算 */ select 8/2 from dual; -- 查询员工的年薪 select sal*12 from emp; -- 查询员工的年薪 select sal*12,sal*12+comm from emp; -- 错误 -- nvl 函数 : 判断第一个参数是否为null 如果是,则返回第二个参数, 否则返回第一个参数 select sal*12,sal*12+nvl(comm,1) from emp; /* 字符串连接: 通用的方式: concat(str1,str2) : str1+str2 Oracle特有的方式: || : 拼接字符串 A公司 Oracle ---> B公司 : mysql ---> */ -- 查询 姓名:ename select concat('姓名:',ename) from emp; select concat('abc','efg') from dual; select 'abc' || 'efg' || 'hij' from dual; /* 条件查询: where后面的写法: 操作符: 比较运算符: > >= = < <= != <> 逻辑运算符: and or not 其它运算符: between.. and .. : 在区间内 [10,100] [10,50) in : 在集合内 not in : like : 模糊查询 exists(查询语句) : 存在的意思 is null : 判断是否为空 is not null : 判断不为空 */ -- 查询每月能得到奖金的员工信息 select * from emp where comm != null; --错误的演示 select * from emp where comm is not null; -- 查询工资在1500--3000之间的员工信息 select * from emp where sal between 1500 and 3000; select * from emp where sal>=1500 and sal <=3000; -- 查询名字在某个范围的员工信息('JONES','SCOTT','BLAKE') --区分大小写 select * from emp where ename in('JONES','SCOTT','BLAKE'); -- 查询员工姓名第三个字符是A的员工信息 /* like : % : 匹配多个字符 _ :匹配单个字符 */ select * from emp where ename like '__A%'; -- 查询名称中包含%的员工信息 insert into emp(empno,ename) values(9527,'HUA%AN'); select * from emp where ename like '%%%'; --所有的数据都查询出来 select * from emp where ename like '%/%%'; -- escape相当于是告诉数据库 \ 是一个转义字符 select * from emp where ename like '%\%%' escape '\'; select * from emp where ename like '%#%%' escape '#'; delete from emp where empno = 9527; --排序操作 /* order by 列名 排序规则 nulls first|last asc : ascend (默认) desc : descend 多列排序: 排序条件两个 */ --按照工资进行排序 select * from emp order by sal desc; --按照奖金进型排序 -升序 select * from emp order by comm; --按照奖金进型排序 -降序 select * from emp order by comm desc; select * from emp order by comm desc nulls last; -- 按照部门升序进行, 按照工资降序进行排序 select * from emp order by deptno asc,sal desc; /* 字符串函数: */ -- 截取字符串 -- 不管起始索引写0还是写1 都是从第一个字符开始截取 -- 第一个参数字符串, 第二起始索引, 第三个是长度 select substr('abcdefg',0,3) from dual; --abc select substr('abcdefg',1,3) from dual; --abc select substr('abcdefg',2,3) from dual; --bcd -- 获取字符串长度 select length('abcdefg') from dual; -- trim() 默认去除是字符两边的空格 select ' hello ' from dual; select trim(' hello ') from dual; -- 字符串替换 select replace('hello','ll','xx') from dual; /* 数值型的函数: 四舍五入 : round 向上取整: ceil 向下取整: floor */ select round(45.926,2) from dual; --45.93 select round(45.926,1) from dual; --45.9 select round(45.926,0) from dual; --46 select round(45.926,-1) from dual; --50 select round(45.926,-2) from dual; --0 -- trunc() 截断 : 不会进位 select trunc(45.926,2) from dual; --45.92 select trunc(45.926,1) from dual; --45.9 select trunc(45.926,0) from dual; --45 select trunc(45.926,-1) from dual; --40 select trunc(45.926,-2) from dual; --0 -- 向大的取整 select ceil(-12.5) from dual; -- -12 -- 向小的取整 select floor(-12.5) from dual; -- -13 select abs(-12.5) from dual; --12.5 select mod(9,2) from dual; --1 --------------------日期函数-------------------------------- -- 查询当前的日期 -- 服务器中的时间 select sysdate from dual; -- 2017/6/8 17:46:08 -- 查询员工入职的天数 select emp.*, ceil(sysdate - hiredate) from emp; -- 查询员工入职的周数 select emp.*, ceil((sysdate - hiredate)/7) from emp; -- 查询员工入职的月数 select emp.*, months_between(sysdate,hiredate) from emp; -- 查询员工入职的年份 select emp.*, months_between(sysdate,hiredate)/12 from emp; -- 几个月后的日期 select add_months(sysdate,3) from dual; select sysdate+23 from dual; select sysdate+1.5 from dual; /* 字符串函数, 数值型, 日期型的函数 转换函数: to_char to_number to_date to_char(sysdate,'d') 每周第几天 to_char(sysdate,'dd') 每月第几天 to_char(sysdate,'ddd') 每年第几天 to_char(sysdate,'ww') 每年第几周 to_char(sysdate,'mm') 每年第几月 to_char(sysdate,'q') 每年第几季 to_char(sysdate,'yyyy') 年 */ -- to_number 鸡肋, oracle自动转换 select '100'+10 from dual; -- 110 select to_number('100') + 10 from dual; -- 110 -- to_char select sal from emp; select to_char(sal,'$999,999.999') from emp; select to_char(sal,'L999,999.999') from emp; -- 日期转字符 select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; select to_char(sysdate,'yyyy') from dual; select to_char(sysdate,'mm') from dual; select to_char(sysdate,'mon') from dual; select to_char(sysdate,'month') from dual; -- 一个星期中第几天 select to_char(sysdate,'d') from dual; -- 5 -- 一个月中第几天 select to_char(sysdate,'dd') from dual; -- 08 -- 一年中第几天 select to_char(sysdate,'ddd') from dual; --159 select to_char(sysdate,'dddd') from dual; --1595 -- ----------------字符转成日期格式---------------------------------- select to_date('2017-06-08','yyyy-mm-dd') from dual; -- 查询1980 -- 1985年入职的员工信息 select * from emp where hiredate between to_date('1980/01/01','yyyy/mm/dd') and to_date('1985/12/31','yyyy/mm/dd'); select nvl(null,10) from dual; -- 10 select nvl(1,10) from dual; -- 1 -- 如果第一个参数为null , 则返回第三个参数, 否则返回第2个参数 select nvl2(null,5,6) from dual; -- 6 select nvl2(1,5,6) from dual; -- 5 -- 判断两个数,是否相等, 如果相等,则返回null 否则返回第一个参数 select nullif(5,6) from dual; -- 5 select nullif(5,5) from dual; -- null -- 找第一个不为null select coalesce(null,null,3,null,1) from dual; -- 3 /* 条件表达式: 通用的方式: case 列名 when 值1 then 新内容 when 值2 then 新内容 when 值3 then 新内容 else 默认值 end Orcale特有的方式: decode(ename,if1,then1,if2,then2,if3,then3,默认值) 解码 */ -- 给emp表中员工的姓名取一个中文名称 select ename from emp; select case ename when 'SMITH' then '斯密斯' when 'ALLEN' then '艾伦' when 'WARD' then '沃德' else '隔壁老王' end from emp; select decode(ename,'SMITH','曹操','ALLEN','关羽','貂蝉') from emp; /* 聚合函数: count() max() min sum avg 聚合函数直接忽略空值 */ -- 查询员工的奖金综合 select sum(comm) from emp; --2200 -- 查询员工人数 select count(*) from emp; --14 select 1 from emp; select count(2) from emp; -- 直接写一个常量,稍微高效 -- 查询员工的平均奖金 select avg(comm) from emp; --550 select sum(comm)/count(2) from emp; -- 157 /* 分组查询 group by 分组条件 select 分组的条件,分组之后的操作 from 表名 group by 分组的条件 [having 条件过滤] where | having where 是在分组之前执行,不能接聚合函数,可以接单行函数 having 分组之后执行, 可以接聚合函数 SQL编写顺序: select..from..where..group by ..having .. order by .. SQL执行顺序: from..where..group by..having..select..order by.. */ -- 查询部门的平均工资,并且过滤平均工资大于2000的部门 -- 1.统计所有部门的平均工资 select deptno,avg(sal) from emp group by deptno ; --2 .条件过滤 select deptno,avg(sal) from emp group by deptno having avg(sal)>2000; select * from emp; select sysdate from dual; select * from emp;
2.第二天课程
//左外链接查询 select * from emp e ,dept d where e.deptno=d.deptno(+)//查询到null的值 //使用(+)表示左连接或者右连接，当(+)在左边表的关联条件字段上时是左连接，如果是在右边表的关联条件字段上就是右连接 select * from emp e left outer join dept d on e.deptno=d.deptno//本身就可以 ///////////////////////////////////////////////// in 和exists -- 查询不是领导的员工信息 select distinct mgr from emp where mgr is not null select * from emp where empno not in(select distinct mgr from emp where mgr is not null) exists(查询语句) : 存在的意思 如果查询语句,有结果,则返回true 否则返回false exists 和 in 通常可以替换使用 建议使用exists 效率高, 数据量很大的时候 */ /////////////////////////////////////////////////// //rownum 代表第几个查询到的数据 rownum : 伪列 , 是Oracle提供一个代表每行记录的序号 rownum 默认值只会从1 开始, 每查询出一条记录, 就加 1 应用: 查询6-10 条数据 //select rownum hanghao, e.* from emp e where rownum <=10 select tt.* from(select rownum hanghao, e.* from emp e where rownum <=10) tt where tt.hanghao>5 ///////////////////////////////////////////////////////// //rowid 在物理磁盘的位置 rowid : 伪列, 代表的是每条记录在磁盘上所存放的真实的位置/物理地址 运用在索引查询的时候 rownum : 伪列, 代表的是行号 主要是在Oracle分页操作的时候使用 -- rowid -- 华为的笔试题 : 去除表中重复的记录 create table tt( tname varchar2(10) ); insert into tt values('aa'); insert into tt values('bb'); insert into tt values('cc'); select rowid,tt.* from tt; -- 查询的时候去除重复 select distinct tname from tt; -- 删除表中重复的记录,保留rowid最小的记录 delete from tt t1 where rowid >(select min(rowid) from tt t2 where t1.tname = t2.tname ); //将行转列 xxxxxxxxxx select to_char(hiredate,'yyyy') aa,count(1) cc from emp group by to_char(hiredate,'yyyy');-- 1. 将1987竖起来 case when endselect case aa when '1987' then cc end "1987"from(select to_char(hiredate,'yyyy') aa,count(1) cc from emp group by to_char(hiredate,'yyyy')) tt;-- 2. 去除所有的nullselect sum(case aa when '1987' then cc end) "1987"from(select to_char(hiredate,'yyyy') aa,count(1) cc from emp group by to_char(hiredate,'yyyy')) tt;-- 3. 计算totalselect sum(cc) "Total"from (select to_char(hiredate,'yyyy') aa,count(1) cc from emp group by to_char(hiredate,'yyyy')) tt;--4. Total 和 1987 拼接起来select sum(cc) "Total", sum(case aa when '1987' then cc end) "1987"from (select to_char(hiredate,'yyyy') aa,count(1) cc from emp group by to_char(hiredate,'yyyy')) tt;--5. 所有的结果select sum(cc) "Total", sum(case aa when '1980' then cc end) "1980", sum(case aa when '1981' then cc end) "1981", sum(case aa when '1982' then cc end) "1982", sum(case aa when '1987' then cc end) "1987"from (select to_char(hiredate,'yyyy') aa,count(1) cc from emp group by to_char(hiredate,'yyyy')) tt; ////////////////////////////////////////////// xxxxxxxxxx /* 回顾: Oracle安装 Oracle基本体系结构: 数据库 --- > 实例(ORCL) ---> 表空间(用户) ---> 数据文件 SQL基本查询: select 要显示的列 from 表名 [where 条件] dual : 虚表/伪表 主要是用于补齐语法结构 条件查询: like 如果包含特殊字符 escape 查询语句中空值的问题: null is null is not null 排序时候: order by 列名 nulls first|last 函数: 数值函数: round trunc ceil floor mod 字符函数: substr length replace trim concat || 日期函数: sysdate, months_between(), add_months 日期可以做运算 转换函数: to_number to_char(value,'format_str') to_date(value,'format_str') 通用函数: nvl(v1,v2) nvl2 nullif 条件表达式: case 列 when val1 then val2 else end decode(列名,if1,then1,if2,then2,default) 分组查询: select 分组的条件,分组之后的操作 from 表名 group by 分组条件 having 条件过滤 *//* 笛卡尔积 :没有实际意义,两张表的乘积*/select * from emp;select * from dept;select * from emp,dept;/* 内连接 : 等值内连接/隐式内连接 不等值内连接 显式内连接 : .. inner join.. on 连接的条件 */-- 隐式内连接select * from emp e,dept d where e.deptno = d.deptno;select * from emp e,dept d where e.deptno != d.deptno;--显式内连接 , inner 可以省略select * from emp e inner join dept d on e.deptno = d.deptno;select * from emp e join dept d on e.deptno = d.deptno;-- on 执行顺序 比 where 靠前-- emp , dept 生成的一张中间表,先生成的笛卡尔积,再使用 where 条件过滤-- on 带着条件生成结果/* 自连接: 自己连接自己*/-- 查询员工的编号,姓名,经理编号,经理姓名select e1.empno,e1.ename "员工姓名",e1.mgr,m1.ename "经理姓名" from emp e1,emp m1 where e1.mgr=m1.empno;-- 查询员工的编号,员工姓名,员工的部门名称,经理编号,经理姓名,经理部门名称select e1.empno,e1.ename ,d1.dname ,e1.mgr,m1.ename ,d2.dnamefrom emp e1,emp m1 ,dept d1,dept d2where e1.mgr=m1.empno and e1.deptno = d1.deptno and m1.deptno = d2.deptno;-- 查询员工的编号,员工姓名,员工的部门名称,工资等级,经理编号,经理姓名,经理部门名称,经理的工资等级select e1.empno,e1.ename ,d1.dname ,s1.grade ,e1.mgr,m1.ename ,d2.dname,s2.gradefrom emp e1,emp m1 ,dept d1,dept d2,salgrade s1,salgrade s2where e1.mgr=m1.empno and e1.deptno = d1.deptno and m1.deptno = d2.deptnoand e1.sal between s1.losal and s1.hisaland m1.sal between s2.losal and s2.hisal;-- 查询员工的编号,员工姓名,员工的部门名称,工资等级(中文),经理编号,经理姓名,经理部门名称,经理的工资等级(中文)select e1.empno,e1.ename "员工姓名",d1.dname ,case s1.grade when 1 then '一级' when 2 then '二级' when 3 then '三级' when 4 then '四级' when 5 then '五级' else '超级' end "员工工资等级",e1.mgr,m1.ename ,d2.dname,decode(s2.grade,1,'一级',2,'二级',3,'三级',4,'四级',5,'五级','超级') "经理工资等级"from emp e1,emp m1 ,dept d1,dept d2,salgrade s1,salgrade s2where e1.mgr=m1.empno and e1.deptno = d1.deptno and m1.deptno = d2.deptnoand e1.sal between s1.losal and s1.hisaland m1.sal between s2.losal and s2.hisal;/* 外连接查询: 左外连接: 以左表为基础将,左表中所有的记录都查询出来,如果右表没有对应的记录,用null填充 右外连接: 以右表为基础将,右表中所有的记录都查询出来,如果左表没有对应的记录,用null填充 left outer join .. on right outer join ..on outer 可以省略 Oracle特有的写法 (+) + 的是null 如果没有对应的记录,就用null填充 通用的写法要好些吧! */-- 左外连接select * from emp e left outer join dept d on e.deptno = d.deptno;select * from emp e,dept d where e.deptno = d.deptno(+);-- 右外连接select * from emp e right outer join dept d on e.deptno = d.deptno;select * from emp e,dept d where e.deptno(+) = d.deptno;insert into emp(empno,ename) values(9527,'huaan');delete from emp where empno=9527;select * from salgrade;/* 子查询: 查询语句中嵌套查询语句 解决一些复杂的查询需求 按照行列划分: 单行子查询: 查询出来结果只有一行 操作符: > >= = < <= != <> 多行子查询: 查询出来的结果有多行 操作符: in not in any 任何/任意 all 所有 exists 存在的意思 按照出现的位置: select from where having*/-- 查询工资最高的员工信息-- 1.查询最高工资是多少 5000select max(sal) from emp;-- 2. 看谁的工资等于最高工资select * from emp where sal = 5000;select * from emp where sal = (select max(sal) from emp);-- 查询出比雇员7654的工资高,同时 和7788从事相同工作的员工信息-- 1.雇员7654的工资 1250select sal from emp where empno=7654;-- 2. 7788从事的工作 ANALYSTselect job from emp where empno = 7788;-- 3. 结果:select * from emp where sal > (select sal from emp where empno=7654) and job=(select job from emp where empno = 7788);-- 查询每个部门最低工资的员工信息和他所在的部门信息-- 1. 每个部门最低工资select deptno,min(sal) minsal from emp group by deptno;select * from emp e1, (select deptno,min(sal) minsal from emp group by deptno) t1where e1.deptno = t1.deptno and e1.sal = t1.minsal;-- 2.连接部门表,查询信息select * from emp e1, (select deptno,min(sal) minsal from emp group by deptno) t1, dept d1where e1.deptno = t1.deptno and e1.sal = t1.minsal and e1.deptno = d1.deptno;-- 查询是领导的员工信息-- 1. 得到所有经理的编号 int i=3 int j =4 i = jselect distinct mgr from emp where mgr is not null; -- (7839,7782,7698)-- 2. 结果:select * from emp where empno in(select distinct mgr from emp);-- 查询不是领导的员工信息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 exists 和 in 通常可以替换使用 建议使用exists 效率高, 数据量很大的时候 */select * from emp where exists(select * from emp where empno = 1234567);select * from emp where 1=2;select * from emp where exists(select * from emp where empno=7369);select * from emp where 1=1;-- 查询有员工的部门信息select * from dept d1 where exists(select * from emp e1 where e1.deptno = d1.deptno);-- Select接子查询 -- 获取员工的名字和部门的名字 select ename,deptno from emp; select ename,(select dname from dept d1 where e1.deptno = d1.deptno) from emp e1;-- from后面接子查询 -- 查询emp表中经理信息 --1. 查询经理的编号 select distinct mgr from emp where mgr is not null; -- 2.结果 select * from ( select distinct mgr from emp where mgr is not null) m1, emp e1 where m1.mgr = e1.empno; -- where 接子查询 -- 薪资高于10号部门平均工资的所有员工信息 -- 1. 10号部门平均工资 2916 select avg(sal) from emp where deptno = 10; -- 2.结果 select * from emp where sal > (select avg(sal) from emp where deptno = 10);-- having后面接子查询 -- 有哪些部门的平均工资高于30号部门的平均工资 -- 1.30号部门的平均工资 1566 select avg(sal) from emp where deptno = 30; -- 2. 分组统计每个部门的平均工资 select deptno,avg(sal) from emp group by deptno; --3. 结果: select deptno,avg(sal) from emp group by deptno having avg(sal) > (select avg(sal) from emp where deptno = 30);-- 找到员工表中工资最高的前三名/* rownum : 伪列 , 是Oracle提供一个代表每行记录的序号 rownum 默认值从1 开始, 每查询出一条记录, 就加 1 SQL执行顺序: from..where..group by..having..select.. rownum ..order by..*/select rownum,emp.* from emp;select rownum,emp.* from emp order by sal;select rownum,emp.* from emp where rownum >4; --没有任何记录select rownum,emp.* from emp where rownum >=1; --14条记录select rownum,emp.* from emp where rownum <4; --排序 salselect emp.* from emp order by sal desc;select rownum,t1.* from (select emp.* from emp order by sal desc) t1;select rownum,t1.* from (select emp.* from emp order by sal desc) t1 where rownum < 4;--找到员工表中薪水大于本部门平均薪水的员工-- 1. 分组统计每个部门的平均工资select deptno , avg(sal) avgsal from emp group by deptno;-- 2. 结果:select * from emp e1, (select deptno , avg(sal) avgsal from emp group by deptno) t1 where e1.deptno = t1.deptno and e1.sal > t1.avgsal;--统计每年入职员工的个数select hiredate from emp;select to_char(hiredate,'yyyy') from emp;select to_char(hiredate,'yyyy'),count(1) from emp group by to_char(hiredate,'yyyy');select to_char(hiredate,'yyyy') aa,count(1) cc from emp group by to_char(hiredate,'yyyy');-- 1. 将1987竖起来 case when endselect case aa when '1987' then cc end "1987"from(select to_char(hiredate,'yyyy') aa,count(1) cc from emp group by to_char(hiredate,'yyyy')) tt;-- 2. 去除所有的nullselect sum(case aa when '1987' then cc end) "1987"from(select to_char(hiredate,'yyyy') aa,count(1) cc from emp group by to_char(hiredate,'yyyy')) tt;-- 3. 计算totalselect sum(cc) "Total"from (select to_char(hiredate,'yyyy') aa,count(1) cc from emp group by to_char(hiredate,'yyyy')) tt;--4. Total 和 1987 拼接起来select sum(cc) "Total", sum(case aa when '1987' then cc end) "1987"from (select to_char(hiredate,'yyyy') aa,count(1) cc from emp group by to_char(hiredate,'yyyy')) tt;--5. 所有的结果select sum(cc) "Total", sum(case aa when '1980' then cc end) "1980", sum(case aa when '1981' then cc end) "1981", sum(case aa when '1982' then cc end) "1982", sum(case aa when '1987' then cc end) "1987"from (select to_char(hiredate,'yyyy') aa,count(1) cc from emp group by to_char(hiredate,'yyyy')) tt;/* rowid : 伪列, 代表的是每条记录在磁盘上所存放的真实的位置/物理地址 运用在索引查询的时候 rownum : 伪列, 代表的是行号 主要是在Oracle分页操作的时候使用*/select rownum,emp.* from emp;select rowid,emp.* from emp; -- 0x1001select rowid,emp.* from emp order by sal desc;-- 查询表中第5-10条记录-- 1.查询前10条记录select rownum hanghao,e1.* from emp e1 where rownum <=10;select rownum hanghao,e1.* from emp e1;-- 2. 查询第5-10的记录select *from (select rownum hanghao,e1.* from emp e1 where rownum <=10) t1where hanghao >=5;-- rowid -- 华为的笔试题 : 去除表中重复的记录create table tt( tname varchar2(10));insert into tt values('aa');insert into tt values('bb');insert into tt values('cc');select rowid,tt.* from tt;-- 查询的时候去除重复select distinct tname from tt;-- 删除表中重复的记录,保留rowid最小的记录delete from tt t1 where rowid >(select min(rowid) from tt t2 where t1.tname = t2.tname );select * from tt;select * from emp;select * from dept;/* 集合运算: 将多个查询合并,差集, 交集 数据有可能来自于不同表 员工表, 经理表 姓名, 婚否 查询本公司所有人的婚姻状况 */-- 并集运算: 将两个查询结果合并在一起-- 工资大于1500,或者20号部门下的员工select * from emp where sal > 1500 or deptno =20;-- 工资大于1500select * from emp where sal > 1500;-- 20号部门下的员工select * from emp where deptno = 20;select * from emp where sal > 1500unionselect * from emp where deptno = 20;select * from emp where sal > 1500union allselect * from emp where deptno = 20; -- 交集运算 : 两个集合相交的部分, 共有的部分-- 工资大于1500,或者20号部门下的员工select * from emp where sal > 1500intersectselect * from emp where deptno = 20;-- 差集运算 : 一个集合减去另外一个集合-- 工资大于1500,不是20号部门下的员工select * from emp where sal > 1500minusselect * from emp where deptno = 20;/* 集合运算的注意事项: 1. 列的出现顺序要一致 2. 列的数量要一致 3. 如果列数不够,null来凑或者使用相同类型的数来凑*/select ename,sal from emp where sal > 1500unionselect ename,sal from emp where deptno = 20;-- 出现的顺序要一致 错误select ename,sal from emp where sal > 1500unionselect sal,ename from emp where deptno = 20;-- select ename,sal from emp where sal > 1500unionselect ename,0 from emp where deptno = 20;select ename,sal from emp where sal > 1500unionselect ename,null from emp where deptno = 20;-- 统计 薪资 大于 薪资最高的员工 所在部门 的平均工资 和 薪资最低的员工 所在部门 的平均工资 的平均工资 的员工信息。-- sal > 10 100 20 50 75 -- sal > 75 的员工信息 -- 有两个及以上直接下属的员工信息 -- 1.统计mgr的出现的次数select mgr from emp;select mgr,count(1) from emp where mgr is not null group by mgr;select mgr,count(1) from emp where mgr is not null group by mgr having count(1)>=2;select mgr from emp where mgr is not null group by mgr having count(1)>=2;select * from emp where empno in(select mgr from emp where mgr is not null group by mgr having count(1)>=2)
3,第三天课程
1.关联子查询2.非关联子查徐/* 分情况: 1.关联子查询: 子查询中的条件,关联外部查询记录 先从外部查询取一条记录,然后再执行子查询中的内容 2.非关联子查询: 子查询可以单独执行,不依赖外部查询 先执行括号中的子查询*/-- 薪资最高的员工的员工信息select * from emp e where sal = (select max(sal) from emp);--非关联的查询 ////////////////////////////////////////// 创建一块区域 --切换到管理员的账户下 create tablespace xiongan datafile 'c:/xiongan.dbf' size 100m autoextend on next 20m ; drop tablespace xiongan; -- 创建用户 create user *** identified by root default tablespace xiongan; --指定一个籍贯 -- 授予角色 grant connect to ***; -- 拥有最高权限 grant dba to ***; select * from scott.emp; /////////////////////////////////////// xxxxxxxxxx /* 创建表: create table 表名( 列名 列的类型 [列的约束], 列名 列名类型 [列的约束] ); 列的类型: char : 固定长度 varchar : 现在可以使用,但是不保证以后还能使用 varchar2 : Oracle中的类型,可变长度类型, 不可以超过最大长度 char(10) hello 不够用空格凑 ,凑满10个 varchar2(10) hello 只存放5个字符的长度 number[(总长度,小数位数)] 小数位数 < 总长度 date : 年月日时分秒 timestamp : 时间戳 年月日时分秒,精确到秒的下一级 Long : 大文本的数据类型 CLOB : CHAR LARGE OBJECT BLOB : BINARY LARGE OBJECT */-- 2017/6/11 15:23:43select current_date from dual;-- 11-JUN-17 03.24.08.511000 PM +08:00select current_timestamp from dual;-- 创建一个学生表(学号,姓名,性别)create table student( sno number, sname varchar2(6), sex varchar2(2));-- 演示插入数据insert into student values(9527,'华安安','男');insert into student values(9527,'华安安安','男');-- 使用子查询创建表select * from scott.emp;-- 将表结构和表中数据都复制过来了create table emp as select * from scott.emp;select * from scott.emp where 1=2;-- 只拷贝表的结构create table emp1 as select * from scott.emp where 1=2;select * from emp1;-- 修改表结构 学生表(学号,姓名,性别) alter table student add mobile varchar2(11);-- 同时添加多列alter table student add ( email varchar2(20), birthday date ); -- 修改列 modifyalter table student modify sname varchar2(20);-- 修改列名 rename columnalter table student rename column sex to gender;-- 删除列名alter table student drop column birthday;-- 修改表名rename student to stu;drop table stu; /////////////////////////////////////////////////////// //表的创建 /* 创建表: create table 表名( 列名 列的类型 [列的约束], 列名 列名类型 [列的约束] ); 列的类型: char : 固定长度 varchar : 现在可以使用,但是不保证以后还能使用 varchar2 : Oracle中的类型,可变长度类型, 不可以超过最大长度 char(10) hello 不够用空格凑 ,凑满10个 varchar2(10) hello 只存放5个字符的长度 number[(总长度,小数位数)] 小数位数 < 总长度 date : 年月日时分秒 timestamp : 时间戳 年月日时分秒,精确到秒的下一级 Long : 大文本的数据类型 CLOB : CHAR LARGE OBJECT BLOB : BINARY LARGE OBJECT */ -- 2017/6/11 15:23:43 select current_date from dual; -- 11-JUN-17 03.24.08.511000 PM +08:00 select current_timestamp from dual; -- 创建一个学生表(学号,姓名,性别) create table student( sno number, sname varchar2(6), sex varchar2(2) ); -- 演示插入数据 insert into student values(9527,'华安安','男'); insert into student values(9527,'华安安安','男'); -- 使用子查询创建表 select * from scott.emp; -- 将表结构和表中数据都复制过来了 create table emp as select * from scott.emp; select * from scott.emp where 1=2; -- 只拷贝表的结构 create table emp1 as select * from scott.emp where 1=2; select * from emp1; -- 修改表结构 学生表(学号,姓名,性别) alter table student add mobile varchar2(11); -- 同时添加多列 alter table student add ( email varchar2(20), birthday date ); -- 修改列 modify alter table student modify sname varchar2(20); -- 修改列名 rename column alter table student rename column sex to gender; -- 删除列名 alter table student drop column birthday; -- 修改表名 rename student to stu; drop table stu; /////////////////////////////////////////////////////// 表的五大约束 /* 表的五大约束: 1.主键约束: 主要用是ID上, 非空并且唯一 2.唯一约束: 唯一但是可以为空 3.非空约束: 不能为空 4.检查约束: 检查插入的值是否复合指定的规则 check(条件) 语法在mysql中可以写,但是mysql直接忽略了 5.外键约束: 约束从表中的记录,必须存在于主表中 ,多表约束时使用 约束好不好 ? js --- > java后台 ----> 数据库 */ create table person( pid number primary key, pname varchar2(20) unique, mobile varchar2(11) not null, gender varchar2(2) check(gender in('男','女','妖')) ); insert into person values(1,'zs','123','男'); insert into person values(2,'zs','123','男'); insert into person values(3,'ww',null,'男'); --错误的演示 insert into person values(3,'ww','1234','未'); /* 外键约束: 约束从表A中的记录,必须存在于主表B中 ,多表约束时使用 插入数据: 先看主表中有没有对应的记录,再往从表插入记录 删除数据: 先删除从表中记录,再删除主表中记录 */ -- 商品分类表 分类ID, 分类名称 create table category( cid number primary key, cname varchar2(20) ); -- 商品表 商品ID, 商品名称 分类ID create table product( pid number primary key, pname varchar2(20), cno number ); -- 往分类表中插入一条记录 insert into category values(1,'手机数码'); -- 插入了一条无效的数据,所以要添加外键约束 insert into product values(10,'锤子手机',11); --删除垃圾数据 delete from product where cno = 11; -- 添加外键约束 alter table product add foreign key(cno) references category(cid); -- 插入数据失败 insert into product values(10,'锤子手机',11); insert into product values(10,'锤子手机',1); delete from category; -- 强制删除表中数据 -- 1.先 然后再删除自己 ,导致数据库中不完整 drop table category cascade constraint; -- 添加外键约束 alter table product add foreign key(cno) references category(cid) on delete cascade; -- 级联删除: 在删除主表记录的时候,先删除从表中对应的记录,然后再删除主表的记录 delete from category; ///////////////////////////////////////////////// 对数据的增删改查 -- 子查询插入数据 insert into emp1 select * from emp where deptno=20; select * from category; insert into category values(1,'手机数码'); insert into category(cid) values(2); -- 下面写法是mysql特有的, 就不应该学会 insert into category values(3,'手机数码'),(4,'手机数码'),(5,'手机数码'),(6,'手机数码'),(7,'手机数码'); -- 更新数据 update category set cname='鞋靴箱包' where cid=2; update category set cname='鞋靴箱包' ; -- 删除数据: delete delete from category where cid =2; /* delete 和 truncate的区别: delete: DML 数据可以回滚,支持事务操作 一条一条删除 truncate : DDL 不支持事务,不可以回滚 效率高: 先删除表,再创建表 商城: m user ( flag : 0,1,2) 标记帐号 */ select * from emp where deptno=20; select * from emp1; select * from category; select * from product; //////////////////////////////////////////////// //5.视图 xxxxxxxxxx /* 视图: 虚表, 视窗 实际上是对查询结果的一个封装,封装之后是一个虚表, 视图本身不存储任何数据 所有的记录都存放在原来的表中 作用: 1.封装复杂的查询结果 2.屏蔽表中的细节 name 语法: create [or replace] view 视图名称 as 查询语句 [with read only];*/create or replace view view_emp as select ename,job,mgr,hiredate from emp;select ename,job,mgr,hiredate from emp;select * from view_emp;-- 视图修改数据update view_emp set ename='SMITH2' where ename='SMITH';-- 创建一个只读视图create or replace view view_emp2 as select ename,job,mgr,hiredate from emp with read only;update view_emp2 set ename='SMITH' where ename='SMITH2';select * from emp;-- 同义词: 相当于是取了别名-- 增加源码被反编译的难度,迷惑-- 如果一个表名太长了,就可以考虑取一个同义词,名称变短create synonym yuangong for view_emp2;select * from yuangong;-- 封装复杂的查询语句create view view_yy as select sum(cc) "Total", sum(case aa when '1980' then cc end) "1980", sum(case aa when '1981' then cc end) "1981", sum(case aa when '1982' then cc end) "1982", sum(case aa when '1987' then cc end) "1987"from (select to_char(hiredate,'yyyy') aa,count(1) cc from emp group by to_char(hiredate,'yyyy')) tt;select * from view_yy; //////////////////////////////////////////// ///序列///////////////////////////// xxxxxxxxxx * 序列: mysql : auto_increment,ID自动增长 主要是用来模拟类似于mysql中的这种ID自动增长作用 语法: create sequence 序列名 start with 起始值 increment by 每次递增 maxvalue n | nomaxvalue minvalue n | nominvalue cycle | nocycle 达到最大值之后是否循环 cache n | nocache 缓存的意思 ,向后缓存多少个数 cache 5 1,2,3,4,5,6,7.... 1,2,3,4,5 用了5 6,7,8,9,10 currval : 需要在调用一次nextval之后才能够调用 nextval : 从序列中取下一个值, 用不回头的向下递增,无论发生异常还是回滚*/create sequence seq_teststart with 1increment by 2maxvalue 20minvalue 0cyclecache 3;select seq_test.currval from dual;select seq_test.nextval from dual;-- 正常的写法: create sequence seq_test2;insert into category(cid) values(seq_test2.nextval);select * from category; //索引////////////// -- 创建一张测试表 create table tt( t1 varchar2(20), t2 varchar2(20) ); -- 插入500万条数据 -- 创建序列 create sequence seq_tt; -- 批量插入500万条数据 declare begin for i in 1..4000000 loop insert into tt values('aa'||seq_tt.nextval,'bb'||seq_tt.nextval); end loop; commit; end; select count(1) from tt; -- 在没有索引的情况下,查询 t1 = 'aa2000000' --0.699 select * from tt where t1 = 'aa2000000'; -- 创建索引的情况下,查询 t1 = 'aa2000000' --0.033 create index ind_tt_t1 on tt(t1); select * from tt where t1 = 'aa2000000'; -- 在只有t1有索引的情况下 t1 = 'aa2000000' and t2 = 'bb2000000'; select * from tt where t1 = 'aa2000000' and t2 = 'bb2000000'; --0.026 -- 创建一个复合索引 create index ind_tt_t1_t2 on tt(t1,t2); select * from tt where t1 = 'aa2000000' and t2 = 'bb2000000'; --0.022 select * from tt where t2 = 'bb2000000'; -- 0.902 没有索引 //声明的函数 declare -- 声明部分 变量名 变量的类型 := 初始值; vsal emp.sal%type 引用类型数据 v_row emp%rowtype; 记录型变量 begin --业务逻辑 end; -- 输出7369的工资信息 declare -- v_sal number; v_sal emp.sal%type; begin --赋值 select sal into v_sal from emp where empno = 7369; --输出 dbms_output.put_line(v_sal); end; -- 输出7369的员工信息 declare v_row emp%rowtype; begin select * into v_row from emp where empno = 7369; dbms_output.put_line(v_row.ename||v_row.sal); end; select * from emp where empno = 7369; //输出一个菱形 declare v_size number:=20; begin for y in -v_size..v_size loop for x in -v_size..v_size loop if abs(y)+abs(x) <= v_size and x>0 then dbms_output.put('*'); else dbms_output.put(' '); end if; end loop; dbms_output.put_line(''); end loop; end;
4.第四天课程
//循环和else if xxxxxxxxxx /* 循环: for 变量名 in [reverse] 起始值..结束值 loop end loop;*///第一种 输出1到10declare v_i number:=1;begin while v_i <=10 loop dbms_output.put_line(v_i); v_i := v_i +1; end loop;end;//第二种declare v_i number :=1;begin loop exit when v_i>10; dbms_output.put_line(v_i); v_i := v_i +1; end loop;end;//第三种declarebegin for i in 1..10 loop dbms_output.put_line(i); end loop;end;//第四种declare //输出10..到1begin for i in reverse 1..10 loop dbms_output.put_line(i); end loop;end;/* if 条件判断 if 条件1 then elsif 条件2 then else end if;*/declare v_i number:= &aaa;begin if v_i<18 then dbms_output.put_line('未成年人'); elsif v_i>=18 and v_i <=24 then dbms_output.put_line('青年人'); elsif v_i>24 and v_i <=48 then dbms_output.put_line('中年人'); else dbms_output.put_line('老年人'); end if;end; //游标 //基本知识点. 语法: 1.声明游标 cursor 游标名 is 查询语句 cursor 游标名(参数名 参数类型) is 查询语句 where 条件 = 参数名 2. 提取数据 ,一次只能提取一条记录 fetch 游标名 into 变量名 3. 判断是否有数据 游标名%found :找到 游标名%notfound : 没有找到 使用步骤: 1.声明游标 2.打开游标 3.从游标中取数据 4.关闭游标 系统引用游标: 变量名 sys_refcursor; open 变量名 for 查询语句 //基本的使用 declare --1.声明游标 cursor vrows is select * from emp; --声明一个变量,用于接收一行记录 v emp%rowtype; begin --2.打开游标 open vrows; --3.提取数据 loop fetch vrows into v; exit when vrows%notfound; --没有找到的时候,循环就退出了 --输出相关的内容 dbms_output.put_line(v.ename||' '||v.job||' '||v.sal); end loop; --关闭游标 close vrows; end; //相当于有一个构造函数,当给这个游标一个变量的时候 -- 输出指定部门的员工名称和工资 -- 游标 declare --声明游标 cursor vrows(dno number) is select * from emp where deptno = dno; --声明一个记录型变量 v emp%rowtype; begin --1. 打开游标 open vrows(20); --2. 循环提取数据 loop fetch vrows into v; exit when vrows%notfound; dbms_output.put_line('姓名:'||v.ename||' 工资:'||v.sal); end loop; close vrows; end; //当使用for循环的时候, 游标会自动打开,自动关闭 -- 使用for循环 所有员工 -- for循环自动打开,自动关闭 declare cursor vrows is select * from emp; begin for v in vrows loop dbms_output.put_line('姓名:'||v.ename||' 工资:'||v.sal); end loop; end; //使用系统引用来操作游标 declare vrows sys_refcursor; v emp%rowtype; begin --打开游标 open vrows for select * from emp; --2 循环遍历 loop fetch vrows into v; exit when vrows%notfound; dbms_output.put_line('姓名:'||v.ename||' 工资:'||v.sal); end loop; close vrows; end; -- 按照员工工作给所有员工涨工资,总裁涨1000,经理涨800,其他人涨400 /* 1.结果集,所有员工 2.判断职位 3.更新工资 4.commit; */ declare -- 声明一个游标 cursor vrows is select * from emp; v emp%rowtype; begin --打开游标 open vrows; --循环遍历所有的员工 loop --提取一行记录 fetch vrows into v; --循环退出的条件 exit when vrows%notfound; --根据职位判断,更新工资 if v.job = 'PRESIDENT' then update emp set sal = sal+1000 where empno = v.empno; elsif v.job = 'MANAGER' then update emp set sal = sal+800 where empno = v.empno; else update emp set sal = sal+400 where empno = v.empno; end if; end loop; --提交语句 commit; close vrows; end; //异常 /* 例外: 意外 ,相当于是java中的异常 declare --声明 begin --业务逻辑 exception --处理例外 when 例外1 then ... when 例外2 then ... when others then ... end; 常见的异常: zero_divide : 发生除零异常 value_error : 类型转换异常 no_data_found: 没有找到数据异常 相当于是null空指针异常 too_many_rows : 查询出的结果是多行,但是赋值了一个单行记录 声明一个异常 异常名 exception; 抛出自定义的异常: raise 异常名; */ // declare v_i number; vrow emp%rowtype; begin // -- v_i := 5/0; // -- v_i := 'abc'; // -- select * into vrow from emp where empno=1234567; select * into vrow from emp; exception when zero_divide then dbms_output.put_line('发生了除零异常'); when value_error then dbms_output.put_line('类型转换异常'); when no_data_found then dbms_output.put_line('没有数据'); when too_many_rows then dbms_output.put_line('查询出的结果是多行,但是赋值了一个单行记录'); when others then dbms_output.put_line('发生了其它异常..'); end; //自定义异常的 既要查询指定员工,还要抛出自定义的异常 // select * into vrow from emp where empno=8888; 上面直接抛出没有数据异常//所以不能在开始这样写,但是在声明时可以 declare --声明一个游标 cursor vrows is select * from emp where empno=8888; v emp%rowtype; --自定义的异常 no_emp exception; begin --1.打开游标 open vrows; --2.将游标向下移动一行 fetch vrows into v; --3. 判断游标是否没有找到 if vrows%notfound then raise no_emp; end if; close vrows; exception when no_emp then dbms_output.put_line('没有找到员工'); when others then dbms_output.put_line('发生了未知异常'); end; //存储的过程 xxxxxxxxxx /* 存储过程: 实际上将一段已经编译好了的PLSQL代码片断,封装在Oracle数据库 1.提高业务逻辑执行效率 2.将复杂的业务逻辑就相当于是封装成了一个过程 语法: create [or replace] procedure 过程的名称(参数1 in|out 参数类型,参数2 in|out 参数类型) is|as -- 声明的部分 begin -- 业务逻辑 end; in : 代表的是输入参数 out : 代表的输出参数 u = new User() function test(int i,User u){ u.setUsername("zs"); 输出参数是需要重新赋值的,输入参数可以不必 syso(i) } */-- 给指定员工涨薪,并打印涨薪前和涨薪后的工资/* 员工编号? 涨多少 ?*/create or replace procedure proc_updatesal(eno in number,scount in number)is vsal number;begin -- 查询涨薪前的工资 select sal into vsal from emp where empno = eno; -- 打印涨薪前的工资 dbms_output.put_line('涨薪前:'||vsal); -- 更新工资 update emp set sal = vsal+scount where empno = eno; -- 打印涨薪后的工资 dbms_output.put_line('涨薪后:'||(vsal+scount)); commit;end;//在下面调用执行过程方式1:call proc_updatessal(7369,10)//给7368涨薪10元方式2: declare begin proc_updatetesal end; //存储函数 xxxxxxxxxx /* 存储函数: 实际上将一段已经编译好了的PLSQL代码片断,封装在Oracle数据库 1.提高业务逻辑执行效率 2.将复杂的业务逻辑就相当于是封装成了一个函数 语法: create [or replace] function 函数名(参数1 in|out 参数类型) return 返回值类型 is|as begin end; 过程和函数的区别: 1. 函数一定要有返回值,过程可以没有 2. 函数可以直接在sql语句中使用,过程不行 3. 过程能够的实现的功能,函数能够实现 4. 函数能实现的功能,过程也能够实现 5. 函数存在意义是给过程调用,在工作中通常调用的是过程 6. 函数和过程本质上木有区别 输入类型 in 默认可以不写 */-- 查询指定员工的年薪//存储函数/////////////////////////////////////////////////////////////-- 员工编号 ? -- 返回 : 年薪create or replace function func_getyearsal(eno number) return numberis -- 声明一个变量,接收年薪 vyearsal number; begin select sal*12 + nvl(comm,0) into vyearsal from emp where empno = eno; -- 返回结果 return vyearsal;end;//调用函数declare vsum number;begin vsum := func_getyearsal(7369); dbms_output.put_line('年薪:'||vsum); //输出编号是7369的年薪end;-- 调用统计年薪的--//存储过程/////////////////////////////////////////////////////////////////////////-- 输入: 员工编号-- 输出: 年薪create or replace procedure proc_getyearsal(eno in number,vyearsal out number)is begin -- 查询员工年薪 select sal*12 + nvl(comm,0) into vyearsal from emp where empno = eno; end;// 调用有输出参数的存储过程declare vsum number;begin proc_getyearsal(7369,vsum); dbms_output.put_line('年薪:'||vsum);end; //使用java调用 xxxxxxxxxx //接上边的存储过程和存储函数public class TestProcedure { /* * create or replace procedure proc_getyearsal(eno in number,vyearsal out number) is begin -- 查询员工年薪 select sal*12 + nvl(comm,0) into vyearsal from emp where empno = eno; end; * */ @Test public void test1() throws Exception{// 1.注册驱动 Class.forName("oracle.jdbc.driver.OracleDriver");// 2.创建连接 String url ="jdbc:oracle:thin:@192.168.80.100:1521:orcl"; Connection conn = DriverManager.getConnection(url, "***", "root");// 3.获取执行SQL的对象 String sql ="{call pro_updatesal(?,?)}"; CallableStatement call = conn.prepareCall(sql);// 封装参数 call.setInt(1, 7369);// 注册输出类型的参数 call.registerOutParameter(2, OracleTypes.NUMBER);// 4.执行SQL call.execute(); // 如果执行的是查询操作就返回true , 增删改的操作就是false// 5.处理结果 int sum = call.getInt(2); System.out.println("年薪:"+sum);// 6.释放资源 call.close(); conn.close(); } @Test /* * create or replace procedure proc_getemp(dno in number,vrows out sys_refcursor) is begin open vrows for select * from emp where deptno = dno; end; * */ public void test2() throws Exception{// 1.注册驱动 Class.forName("oracle.jdbc.driver.OracleDriver");// 2.创建连接 String url ="jdbc:oracle:thin:@192.168.42.100:1521:orcl"; Connection conn = DriverManager.getConnection(url, "***", "root");// 3.获取执行SQL的对象 String sql ="{call proc_getemp(?,?)}"; CallableStatement call = conn.prepareCall(sql);// 4.封装输入类型的参数 call.setInt(1, 10);// 5.注册输出类型的参数 call.registerOutParameter(2, OracleTypes.CURSOR);// 6.执行SQL call.execute();// 7.处理结果 System.out.println(call.getClass().getName()); OracleCallableStatement call2 = (OracleCallableStatement)call; ResultSet rs = call2.getCursor(2); while(rs.next()){ System.out.println(rs.getObject("empno")); System.out.println(rs.getObject("ename")); System.out.println(rs.getObject("job")); System.out.println(rs.getObject("sal")); System.out.println("=================================="); }// 8.释放资源 rs.close(); call2.close(); conn.close(); } } //触发器 /* 触发器: 当我们执行 一些操作 insert | update | delete 操作,在这些操作之前或者之后, 我们可以去触发一系列其它的业务逻辑PLSQL代码的执行 逃生的门, 简单看一眼,肯定不会触发警铃 推/拿锤子去修一修, 会出触发警铃报警 语法: create [or replace] trigger 触发器的名称 before | after insert | update | delete on 表名 [for each row] declare --声明 begin --逻辑 end; 作用: 1. 校验数据 2. 在数据改变之前或者之后去做一些封装 触发器的分类: 语句级触发器: 不管影响多少行,都只会触发一次 不能得到影响行的记录 行级触发器: 影响多少行, 就触发多少次触发器中的业务逻辑 可以得到行的记录, 当前行中的内容 :new , 新更新的记录 :old , 旧的记录 */ // 插入员工之后输出一句话 create or replace trigger tri_insert1 after insert on emp declare begin dbms_output.put_line('hello trigger...'); end; insert into emp(empno,ename) values(9527,'huaan'); //校验指定的员工时间不能插入 create or replace trigger tri_insert2 before insert on emp declare --声明一个变量,保存当前星期几 vday varchar2(20); begin -- 周二的时候不能向员工表中插入数据 -- 1. 查看当前日期是周几 select trim(to_char(sysdate,'day')) into vday from dual; -- 2.判断当前日期是否等于周二 if vday = 'tuesday' then -- raise zero_divide; --抛出一个系统异常 raise_application_error(-20001,'周二老板不在,不能插入'); end if; end; //更新操作 - 语句级触发器 create or replace trigger tri_update1 after update on emp declare begin dbms_output.put_line('tri_update1 语句级触发器'); end; update emp set sal = sal+100; //////////////////////////////////////////////////////////////////行级触发器////////////////////// -- 判断员工涨工资后的工资一定要大于涨工资前的工资 -- 行级触发器 每行记录都需要做判断, 更新前后的工资 create or replace trigger tri_updatesal before update on emp for each row declare begin -- 在数据更新到数据库之前,需要做一个判断 if :old.sal >= :new.sal then raise_application_error(-20002,'更新后的工资不能小于更新前的工资'); end if; end; -- :new.sal = sal+100; -- :old.sal = sal update emp set sal = sal-50; /* 模拟mysql中ID自动增长 insert into p values(null,'zs'); 编写一个触发器, 在插入数据之前, 给ID赋值 赋值的ID要有一个规则 : 自动递增 , 1,2,3,4,5,6,7... 思想:在插入数据的时候给id赋值 */ create table p(//建表 pid number primary key, pname varchar2(20) ); -- 创建一个序列 //创建一个序列 create sequence seq_p; -- 创建一个触发器, 需要得到插入的行记录 :new create or replace trigger tri_pid before insert on p for each row declare begin -- 给pid赋值 :new.pid// :new.pid是更新后的数据 select seq_p.nextval into :new.pid from dual; 来自一张虚表 end; insert into p values(null,'zs');
