sql语句练习题

最新推荐文章于 2023-03-09 15:57:17 发布

置顶 LawsonJin

最新推荐文章于 2023-03-09 15:57:17 发布

阅读量996

点赞数 3

分类专栏： SQL 文章标签： sql 数据库

本文链接：https://blog.csdn.net/LawsonJin/article/details/75890289

版权

SQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

主要以oracle数据库中scott用户下面的几张表做练习

这里直接贴出查询条件和答案，遇到多表的我会贴出分析过程，以免日后忘记

--===========解锁scott用户并重新设置密码
alter user scott account unlock;
alter user scott identified by tiger;

--===========基本查询
--1.查询出所有emp中的信息，并用中文进行字段重命名
select empno as "编号",ename "名称",job 职位,mgr 上级领导,hiredate 入职时间,sal 工资,comm 奖金,deptno 部门编号 from emp;

--2.查询emp表中员工的job信息，并去除重复信息
select distinct(job) from emp;
    
--3.查询emp表中员工的全年的工资总和(sal总和)
select ename,sal*12 from emp;

--4.查询emp表中员工的全年收入总和(sal+comm的总和)
select ename,sal*12 + nvl(comm,0) from emp;

--5.查询emp表中员工编号，姓名
--输出格式如下：编号:xxx，姓名：xxx
----Concat拼接方式
select  concat(concat('编号:',empno),concat('姓名：',ename)) from emp;
----Oracle的||方式
select '编号:'||empno||'姓名：'||ename from emp;

--=============================================条件查询
--1.查询工资大于1500的员工
select * from emp where sal > 1500;

--2.查询工资大于1500并且有奖金的雇员
select * from emp where sal > 1500 and comm is not null;

--3.查询工资大于1500或者有奖金的雇员
select * from emp where sal > 1500 or comm is not null;

--4.查询工资大于1500并且没有奖金的雇员
select * from emp where sal > 1500 and comm is null;

--5.查询员工姓名为smith的员工
select * from emp where ename = 'SMITH';

--=============================================范围查询
--1.查询工资大于1500但小于3000的全部雇员
---->=,<=方式
select * from emp where sal >= 1500 and sal  <= 3000;
----between and方式                              
select * from emp where sal between 1500 and 3000;

--2.查询1981-1-1到1981-12-31号入职的雇员（between and）
select * from emp where hiredate between to_date('1981-1-1','yyyy-MM-dd') and to_date('1981-12-31','yyyy-MM-dd');

--3.查询员工编号是7369,7654,7566的员工
----OR方式
select * from emp where empno = 7369 or empno = 7654 or empno = 7566;

----IN方式
select * from emp where empno in (7369,7654,7566);

--4.查询雇员姓名是'SMITH','ALLEN','WARD'的雇员信息
----IN方式
select * from emp where ename in ('SMITH','ALLEN','WARD');

--=============================================模糊查询like
--1.查询所有雇员姓名中第二个字符有‘M’的雇员
select * from emp where ename like '_M%';

--2.查询名字中带有‘M’的雇员
select * from emp where ename like '%M%';

--3.查询雇员编号不是7369的雇员信息
----<>方式
select * from emp where empno <> 7369;
----!=方式
select * from emp where empno != 7369;

--=============================================排序 order by
--1.查询雇员的工资进行降序排序
select * from emp order by sal desc;

--2.查询雇员的奖金并做降序排序（关于nulls first/nulls last，这个主要是null值排序在前还是在后）
select * from emp order by comm desc nulls last;

--3.查询雇员的工资做降序排列并且其中奖金部分是升序排序
select * from emp order by sal desc,comm asc;

--===========单行函数
/*
伪表，虚表：dual  没有任何的实际意义，只是为了补全Oracle查询语法
*/
--字符函数
--1.将'smith'转换成大写--关键字:upper
select upper('smith') from dual;

--2.将'SMITH'转换成小写--关键字:lower
select lower('SMITH') from dual;
select lower(ename) from emp;

--3.将'smith'首字母大写--关键字:initcap
select initcap('smith') from dual;

--4.将'helloworld'截取字符串成'hello'--关键字substr
select substr('helloworld',0,5) from dual;

--5.获取'hello'的字符串长度--关键字length
select length('hello') from dual;

--6.将'hello'中的l用x进行替换--关键字replace
select replace('hello','l','x') from dual;

--数值函数
--1.将15.66进行四舍五入（从-2到2）--关键字round
select round(15.66) from dual;   --16
select round(15.66,-2) from dual;--0
select round(15.66,-1) from dual;--20
select round(15.66,0) from dual; --16
select round(15.66,1) from dual; --15.7
select round(15.66,2) from dual; --15.66


--2.将15.66进行截断（从-2到2）--关键字trunc
select trunc(15.66) from dual;   --15
select trunc(15.66,-2) from dual;--0
select trunc(15.66,-1) from dual;--10
select trunc(15.66,0) from dual; --15
select trunc(15.66,1) from dual; --15.6
select trunc(15.66,2) from dual; --15.66

--3.对15/4进行求余数--关键字mod
select mod(15,4) from dual;

--日期函数
--1.查询系统时间--关键字sysdate
select sysdate from dual;
--2.查询雇员进入公司的周数
select ename,(sysdate-hiredate)/7 from emp;

--3.查询雇员进入公司的月数--关键字months_between
select ename,months_between(sysdate,hiredate) from emp;

--4.求出三个月后的日期--关键字add_months
select ename,hiredate,add_months(sysdate,3) from emp;

--转换函数
--1.将系统日期显示为yyyy-mm-dd hh:mi:ss(去掉补零和24小时显示时间)--关键字to_char,fm,24
select to_char(sysdate,'yyyyfm-mm-dd hh24:mi:ss') from dual;

select to_number('99') from dual;
select to_char(99) from dual;
----显示成年月日
select to_char(sysdate,'yyyy')||'年'||to_char(sysdate,'MM')||'月' from dual;

--2.将字符串'1981-1-1'转换成日期类型--关键字to_date
select to_date('1981-1-1','yyyy-MM-dd') from dual;

--通用函数
--1.空值的处理函数
select nvl(comm,0) from emp;

--2.nvl2(判断值,空返回值,非空返回值) 注意是小写L不是数字1
select nvl2('234','1','2') from dual;

--条件表达式
--1.查询员工的job内容并转成中文显示
----decode方式
select decode(job,'CLERK','柜员','SALESMAN','销售','MANAGER','管理','其他') from emp;
select * from emp;
----case when then end方式
select case job when 'CLERK' then '柜员'
when 'SALESMAN' then '销售'
  when 'MANAGER' then '管理'
    else '其他' end from emp;

--======================================多表关联查询
--查询员工编号,员工姓名,员工部门编号,员工部门名称,员工部门地址,中文显示员工工资等级,及领导编号,领导姓名,领导部门编号,领导部门名称,中文显示领导工资等级

select e1.empno,e1.ename,e1.deptno,d1.dname,d1.loc,decode(s1.grade,1,'一级',2,'二级',3,'三级',4,'四级',5,'五级') as sallevel,
       e2.empno,e2.ename,e2.deptno,d2.dname,d2.loc,decode(s2.grade,1,'一级',2,'二级',3,'三级',4,'四级',5,'五级') as sallevel
from emp e1,dept d1,salgrade s1,emp e2,dept d2,salgrade s2
where e1.deptno = d1.deptno and e1.sal between s1.losal and s1.hisal
and e1.mgr = e2.empno 
and e2.deptno = d2.deptno and e2.sal between s2.losal and s2.hisal



--1.查询员工编号,员工姓名,领导编号,领导姓名

--2.查询员工编号,员工姓名,员工部门编号,员工部门名称,员工部门地址,领导编号,领导姓名,领导部门编号,领导部门名称

--3.查询员工编号,员工姓名,员工部门编号,员工部门名称,员工部门地址,员工工资等级,领导编号,领导姓名,领导部门编号,领导部门名称,领导工资等级

--4.查询员工编号,员工姓名,员工部门编号,员工部门名称,员工部门地址,中文显示员工工资等级,领导编号,领导姓名,领导部门编号,领导部门名称,中文显示领导工资等级

select * from emp;
select * from dept;
select * from salgrade;

select e1.empno,e1.ename,e1.deptno,d1.dname,d1.loc,decode(s1.grade,1,'一级',2,'二级',3,'三级',4,'四级',5,'五级') as sallevel,
       e2.empno,e2.ename,e2.deptno,d2.dname,d2.loc,decode(s2.grade,1,'一级',2,'二级',3,'三级',4,'四级',5,'五级') as sallevel
        from emp e1,emp e2,dept d1,dept d2,salgrade s1,salgrade s2
where e1.mgr = e2.empno and e1.sal between s1.losal and s1.hisal
and e1.deptno = d1.deptno and e2.sal between s2.losal and s2.hisal
and e2.deptno = d2.deptno

--======================================外连接
--1.查询员工编号，姓名，领导编号，领导姓名，包括没领导的
----left join on方式
select e1.empno,e1.ename,e2.empno,e2.ename from emp e1 left join emp e2 on e1.mgr = e2.empno;
----Orcl的(+)方式
 select e1.empno,e1.ename,e2.empno,e2.ename from emp e1,emp e2 where e1.mgr = e2.empno(+);
--2.查询出所有部门信息(包括没员工的部门)及部门下的员工信息
 select * from dept,emp where dept.deptno = emp.deptno(+);

--===========子查询
--1.查询比雇员7654工资高，同时从事和7788的工作一样的员工
select * from emp where sal >(select sal from emp where empno = 7654)
and job = (select job from emp where empno = 7788);

--2.查询每个部门最低工资及最低工资的部门名称和雇员名称
select e2.deptno,dname,e2.ename,e2.sal 
from (select deptno,min(sal) minsal from emp group by deptno) e1,emp e2,dept
where e1.deptno = dept.deptno and e1.deptno = e2.deptno and 
e1.minsal = e2.sal;


select * from emp;
select * from dept;
--===========课堂练习
--1.找到员工表中工资最高的前三名
select rownum,empno,ename,sal from emp where rownum <= 3 order by sal desc;

select rownum,e.* from (select rownum,emp.* from emp order by sal desc) e where rownum <= 3;

--2.找到员工表中薪水大于本部门平均工资的所有员工
select e2.ename,e2.deptno,e1.avgsal,e2.sal
 from (select deptno,avg(sal) avgsal from emp group by deptno) e1,emp e2
where e1.deptno = e2.deptno and  e2.sal > e1.avgsal

--3.统计每年入职的员工个数
select to_char(hiredate,'yyyy') hdate,count(*) hcount from emp group by to_char(hiredate,'yyyy');


select sum(hcount) "Total",min(decode(hdate,'1980',hcount)) as "1980",min(decode(hdate,'1981',hcount)) as "1981",min(decode(hdate,'1982',hcount)) as "1982",min(decode(hdate,'1987',hcount)) as "1987" from (select to_char(hiredate,'yyyy') hdate,count(*) hcount 
from emp group by to_char(hiredate,'yyyy'));
--===========分页查询
--1.查询员工表，将员工工资进行降序查询，并进行分页取出第一页，一页三条记录
select rownum,e.* from (select emp.* from emp order by sal desc) e where rownum <= 3;

select * from (select rownum r,e.* from (select emp.* from emp order by sal desc) e) e1 where e1.r > 3 and e1.r <= 6;
/*
pageNO:1
pageSize:3
select * from (select rownum r,e.* from (select 表名.* from 表名 order by 字段 desc) e) e1 
where e1.r > (pageNo-1)*pageSize and e1.r <= pageNo*pageSize;
*/

--===========集合运算（了解）
--1.查询工资大于1200并且job是SALESMAN(intersect)
select * from emp where sal > 1200
intersect 
select * from emp where job = 'SALESMAN'

--2.查询工资大于1200或者job是SALESMAN(union)
select * from emp where sal > 1200
union
select * from emp where job = 'SALESMAN';

--3.求工资大约1200和job是SALESMAN的差集(minus)
select * from emp where sal > 1200
minus
select * from emp where job = 'SALESMAN';

--==========================exists / not exists
/*
select ... where exists(查询语句)
exists：当查询结果不为null，返回true
  当查询结果为null，返回false

*/
--1.查询出有员工的部门
select * from dept where exists (select * from emp where emp.deptno = dept.deptno);

select * from dept where not exists (select * from emp where emp.deptno = dept.deptno);

--===========多行函数
--1.查询所有员工记录数--关键字count
select count(*) from emp;
--2.查询佣金的总数--（如何查询某个字段的总数量）
select sum(sal) from emp;
--3.查询最低工资--关键字min
select min(sal) from emp;
--4.查询最高工资--关键字max
select max(sal) from emp;
--5.查询平均工资--关键字avg
select avg(sal) from emp;
--6.查询20号部门的员工工资总和
select sum(sal) from emp where deptno = 20;
--======================================分组函数
--1.查询部门编号及人数--分组查询关键字group by
select deptno,count(*) from emp group by deptno

--2.查询每个部门编号及平均工资
select deptno,avg(sal) from emp group by deptno

--3.查询部门名称，部门编号，部门平均工资
select dname,dept.deptno,avg(sal) from emp,dept
 where emp.deptno = dept.deptno group by dept.deptno,dname;

--4.查询出部门人数大于5人的部门
select deptno,count(*) from emp group by deptno having count(*)>5;

--5.查询部门编号，部门名称，平均工资且平均工资大于2000
select emp.deptno,dname,avg(sal) from emp,dept 
where emp.deptno = dept.deptno group by emp.deptno,dname having avg(sal) > 2000;

--===========DDL(Data Definition Language)语句管理表
/*
create tablespace 表空间名称
datafile 数据文件路径
size 100m
autoextend on 
next 10m;
*/
grant dba to scott;

--1.创建itcast001表空间
create tablespace itcast001
datafile 'c:/itcast001.dbf'
size 100m
autoextend on
next 10m


create tablespace ccc

--2.创建itcastuser用户,默认表空间itcastuser
create user itcastuser identified by itcastuser
default tablespace itcast001

--3.为itcastuser用户赋予dba权限
grant dba to itcastuser;

--===========================Oracle数据类型
/*
Oracel数据类型
字符类型
      char:固定长度类型
      varchar2:可变长度类型，可保存1333多个汉字
数值类型
      number(3):999
      number(3,2):9.99
日期类型
      date:mysql中日月年,oracle中精确到时分秒,相当于MySQL中的datetime
      timestamp:精确到秒的后9位
大数据类型
      long:大数据字符类型,2G
      Clob:存放字符类型,4G
      Blob:存放二进制类型,4G
*/

--===========================创建表
--4.创建person表，字段为pid，pname
create table person(
  pid number(5),
  pname varchar2(50)
)

select * from person;
--==============================Oracle表中的增删改查
/*
Oracle中的事务需要手动commit提交
*/
--1.为person表新增一条数据
insert into person values(2,'zbz');
commit;

--2.修改person表中的一条数据
update person set pname = 'aj' where pid = 2;
commit;

--3.删除person表中的一条数据
delete from person where pid = 2;
commit; 

--==============================修改表列的属性
--1.给person表增加sex性别列，类型为number(1)
alter table person add sex number(1);

--2.修改person表列sex类型为char(1)
alter table person modify sex char(1);

--3.修改person表sex列为gender
alter table person rename column sex to gender;

--4.删除person表的gender列
alter table person drop column gender;

--5.删除person表中所有数据
delete from person where 1=1;
commit;

--6.摧毁person表（truncate table 表名）
/*
 直接摧毁表结构后重构表，比delete要很快，但是没法按照条件删除
*/
truncate table person;

--=========================================约束
drop table person;
--1.创建person表，pid为主键，pname，gender(主键约束primary key)
--primary key方式
create table person(
   pid number(10) primary key,
   pname varchar2(50),
   gender number(1)
)

insert into person (pid,pname,gender) values (1,'cgx',1);
commit;
insert into person (pid,pname,gender) values (1,'zbz',0);
commit;

--constraint 主键名 primary key(字段),方式
create table person(
   pid number(10),
   pname varchar2(50),
   gender number(1),
   constraint PK_PERSON_PID primary key(pid)
)

--2.创建person表，pname非空，gender(非空约束not null)
create table person(
   pid number(10),
   pname varchar2(50) not null,
   gender number(1)
)

insert into person (pid,gender) values (1,0);


--3.创建person表，pid，pname是唯一，gender(唯一约束unique)
create table person(
   pid number(10),
   pname varchar2(50) unique,
   gender number(1)
)

insert into person (pid,gender) values (3,0);
commit;
--4.创建person表，pid，pname，gender---检查约束check(列名 in (值))
create table person1(
   pid number(10),
   pname varchar2(50),
   gender number(1) check(gender in (1,0))
)

insert into person1 (pid,pname,gender) values (1,'cgx',1);
insert into person1 (pid,pname,gender) values (2,'zbz',0);
commit;
insert into person1 (pid,pname,gender) values (1,'aj',2);
commit;
--=========================================外键约束
/*
constraint fk_order_orderid foreign key(外键) references 对应的表(对应的主键)
*/ 

--1.创建orders表,字段为order_id(主键),total_price
create table orders (
 order_id number(5) primary key,
 total_price number(5)
)

--2.创建order_detail表,字段为detail_id(主键),order_id(外键),item_name
create table order_detail(
   detail_id number(5) primary key,
   order_id number(5),
   item_name varchar2(50),
   constraint FK_ORDER_ORDERID foreign key(order_id) references orders(order_id)
)

--3.为orders表和order_detail表插入数据
insert into orders values (1,99);
commit;

insert into order_detail values (1,1,'照相机');
insert into order_detail values (2,1,'胶卷');
insert into order_detail values (3,1,'红酒');
commit;
--4.删除orders表和order_detail表中的数据
delete from order_detail where 1=1;
delete from orders;
commit;
--==========================Oracle事务（了解）
--1.设置savepoint 回滚点，再次修改数据后用rollback to 回滚点，回滚数据
select * from person;

update person set pname = 'aj' where pid = 2;
savepoint p1;
update person set pname = 'zbz' where pid = 2;
rollback to p1;
commit;

--==========================视图
/*
视图：就是一张虚拟表，本身不存放数据，数据来源于原始表
创建 create [or replace] view 视图名 as sql查询语句；
*/
--1.创建视图
create or replace view view_emp as select * from emp;

--2.查询视图
select * from view_emp;

select * from emp;
--3.修改视图中7369的名字为'smith'
update view_emp set ename = 'smith' where empno = 7499;
commit;

--4.删除视图
drop view view_emp;

--5.创建只读视图（关键字 with read only）

create or replace view view_emp as select empno,ename,hiredate,job,mgr,deptno from emp with read only;

update view_emp set sal = sal + 2000 where ename = 'smith';

delete from view_emp where ename = 'smith'; 
commit;
--==========================序列
/*
序列：类似于MySql的自动增长
create sequence seq_test 
start with 5     
increment by 2    
maxvalue  20
cycle      
cache 5      
*/

create sequence seq_test 
start with 5     
increment by 2    
maxvalue  20
cycle      
cache 5      

select seq_test.nextval from dual;

--1.创建序列
create sequence seq_emp;

--2.如何查询序列（currval,nextval）
select seq_emp.nextval from dual;

--3.删除序列
drop sequence seq_emp

--4.为person表插入数据，用序列为id赋值
select * from emp;

insert into emp (empno,ename) values (33,'cgx');
commit;

insert into emp (empno,ename) values (seq_emp.nextval,'cgx');
commit;
--==========================索引
/*
目的是提高检索速度
语法：create index 索引名称 on 表名(列名);
原则：大数据表才创建索引，
      为经常用到的列创建索引，
      索引的层数不要超过4层，也就是on 表名(列名1,列名2)这里的列名不要超过4个
*/



--创建索引
create index index_person on person(pname);

--删除索引
drop index index_person;

--创建500万条的数据
drop table person;

create table person(
   pid number(10),
   pname varchar2(50)
)

create sequence seq_person;

begin
   for i in 1..5000000 loop
    insert into person values(seq_person.nextval,'测试数据'||i);
   end loop;
   commit;
end;

select * from person where pname ='测试数据4787889'  --2.969s  索引后--0.047


--====================================同义词（了解）
/*
为表创建别名
同义词和视图的区别：不需要dba权限就可以创建同义词
视图可以隐藏列，同义词不可以去掉列只是整张表的复制

创建
create public synonym 同义词名 for 目标表名
删除
drop public synonym 同义词名
*/ 
select * from syn_person;

create public synonym syn_person for itcastuser.person;

drop public synonym syn_person;

--====================================导入导出
/*
PLSqlDeveloper的导入导出方式参照Oracle导入导出.docx
全库导出:exp system/orcl full=y file=expdat.dmp
全库导入:imp system/orcl full=y file=expdat.dmp
按用户导出: exp scott/tiger file=expdat.dmp
按用户导入: imp scott/tiger file=expdat.dmp full=y
*/
select * from emp;

--=====================================pl/sql
/*
PLSQL定义：在数据库服务器上保存的大段可执行方法，供其他开发人员进行调用
PLSQL语法：
[declare]  --定义部分：变量，引用变量，记录型变量，异常
begin
[exception]  --需要捕获异常时写上
end
*/


--定义number变量，定义PI常量，定义记录型变量，定义引用型变量
declare
 i number := 1;               --定义变量
 pstr varchar2(50) := 'hello';
 PI constant number := 3.14;  --定义常量
 pemp emp%rowtype;            --定义记录型变量
 pname emp.ename%type;        --定义引用型变量
begin
  --PI := PI + 1;
  dbms_output.put_line(i);
  dbms_output.put_line(pstr);
  dbms_output.put_line(PI);
  
  select * into pemp from emp where empno = 7499;
  dbms_output.put_line('员工姓名'||pemp.ename||'员工工资'||pemp.sal);
  
  select ename into pname from emp where empno = 7521;
  dbms_output.put_line('员工姓名'||pname);
end;

select * from emp;
--=======================================if分支
/*
if判断分支语法：
begin
  if 判断条件 then
  elsif 判断条件 then
  else
  end if; 
end;
*/
--从控制台输入一个数字，如果数字是1，则输出我是1
declare
  i number := &i;
begin
  if i = 1 then
    dbms_output.put_line('我是1');
  else
    dbms_output.put_line('我不是1');
  end if;  
end;

--如果输入的年龄在16岁以下，输出未成年人，16~40：成年人，40以上 老年人
declare
  i number := &i;
begin
  if i < 16  then
     dbms_output.put_line('未成年人');
  elsif i >= 16 and i <= 40 then
     dbms_output.put_line('成年人');
  else
     dbms_output.put_line('老年人');
  end if; 
end;

--===========================================loop 循环
/*
loop循环
语法1：
begin
  while 循环条件 loop
  end loop;
end;
语法2：(常用)
begin
  loop
    exit when 退出条件
  end loop;
end;
语法3:
begin
  for i in 1..10 loop
  end loop;
end;
                                          
*/
--语法1实现在控制台输出1~10
declare
  i number := 1;
begin
  while i <= 10 loop
     dbms_output.put_line('第一种方式'||i);
     i := i + 1;
  end loop;
end;

--语法2实现在控制台输出1~10(掌握)
declare
 i number := 1;
begin
  loop
    exit when i > 10;
    dbms_output.put_line('第二种方式'||i);
    i := i+1;
  end loop;
end;

--语法3实现在控制台输出1~10
begin
  for i in 1..10 loop
    dbms_output.put_line('第三种方式'||i);
  end loop;
end;

--====================================================光标（游标）
/*
光标:指定记录集，可抓取记录集中的记录
语法：cursor 游标名 is select查询语句
   
提取游标：
open 游标名;
  loop
     fetch 游标名 into 记录型变量
     exit when %游标名notfound;
  end loop;
close 游标名;
*/
select * from emp;
--使用光标输出emp表中7369的员工信息
declare
  cursor cur_emp is select * from emp where empno = 7499;
  pemp emp%rowtype;
begin
  open cur_emp;
    loop
     fetch cur_emp into pemp;
     exit when cur_emp%notfound;
     dbms_output.put_line('员工姓名'||pemp.ename);
    end loop;
  close cur_emp;
end;

--输出指定部门的员工信息
declare
  cursor cur_emp(dno number) is select * from emp where deptno = dno;
  pemp emp%rowtype;
begin
  open cur_emp(10);
    loop
      fetch cur_emp into pemp;
      exit when cur_emp%notfound;
      dbms_output.put_line('员工姓名'||pemp.ename);
    end loop;
  close cur_emp;
end;

--给部门编号为10 的员工涨工资
declare
  cursor cur_emp is select * from emp where deptno = 10;
  pemp emp%rowtype;
begin
  open cur_emp;
    loop
      fetch cur_emp into pemp;
      exit when cur_emp%notfound;
      update emp set sal = sal + 2000 where empno = pemp.empno;
    end loop;
  close cur_emp;
  commit;
end;

select * from emp;

update emp set sal = sal + 2000 where deptno = 10;

--=====================================例外（异常） 
--系统异常，被0除的zero_divide异常
declare
  i number := 1;
begin
  i := i/0;
exception
  when zero_divide then
    dbms_output.put_line('不能除零');
end;

--系统异常，设置错误的value_error异常
declare
 i varchar2(50);
begin
  i := 100;
   dbms_output.put_line(i);
exception
   when value_error then
     dbms_output.put_line('赋值错误');
end;


--系统异常，都可以通过others异常捕获
declare
  i number := 1;
begin 
  i := 'avg';
exception
   when others then
     dbms_output.put_line('有错误');
end;


--自定义异常，查询部门编号为40员工（自定义exception）
declare
  cursor cur_emp is select * from emp where deptno = 40;
  pemp emp%rowtype;
  no_date exception;    --定义自定义异常
begin
  open cur_emp;
       fetch cur_emp into pemp;
       if cur_emp%notfound then
          raise no_date;  --raise抛异常
       end if;
  close cur_emp;
exception
  when no_date then
    dbms_output.put_line('没有找到数据');
end;

--==============================================存储过程（过程）
/*
定义：保存一段可执行的sql语句，方便开发调用过程名
语法：
create [or replace] procedure 过程名(参数  in|out number)
as
begin
end;
*/

--声明pro_add_sal存储过程,作用是给指定员工涨1000工资,并打印出涨前和涨后工资
create or replace procedure pro_add_sal(eno in number)
as
 psal number;
begin
  select sal into psal from emp where empno = eno;
  update emp set sal = sal + 1000 where empno = eno;
  commit;
  dbms_output.put_line('涨前工资'||psal||'涨后工资'||(psal + 1000));
end;

begin
  pro_add_sal(7499);
end;


select * from emp;
--================================================存储函数
/*
定义：保存一段可执行的sql语句，方便开发调用过程名
语法：
create [or replace] function 方法名(参数 in|out number) return 参数类型
as
 定义变量名  类型要和return返回类型一致
begin
  return 变量名;
end;
*/



--声明fun_emp_totalsal存储过程,查询指定员工的年薪
create or replace function fun_emp_totalsal(eno in number) return number
as
 totalsal number;
begin
  select sal*12+nvl(comm,0) into totalsal from emp where empno = eno;
  return totalsal;
end;

declare
  totalsal number;
begin
  totalsal := fun_emp_totalsal(7499);
  dbms_output.put_line('员工年薪'||totalsal);
end;

create or replace procedure pro_emp_totalsal(eno in number,totalsal out number)
as
begin
  select sal*12+nvl(comm,0) into totalsal from emp where empno = eno;
end;

declare
 totalsal number;
begin
  pro_emp_totalsal(7499,totalsal);
  dbms_output.put_line('员工年薪'||totalsal);
end;

--声明fun_emp_dname存储函数,根据部门编号查询出部门名称
create or replace function fun_emp_dname(dno number) return varchar2
as
  pname varchar2(50);
begin
  select dname into pname from dept where deptno = dno;
  return pname;
end;


--在select 调用存储函数
select ename,fun_emp_dname(deptno) from emp;

/*
存储过程与存储函数的区别：
1.定义的语法不一样procedure,function
2.function有返回值
3.function可以在select中进行调用
4.存储过程可以通过out类型来返回参数
*/

---存储过程根据部门编号返回员工
create or replace procedure pro_emp_list(dno number,emplist out sys_refcursor)
as
begin
    open emplist for select * from emp where deptno = dno;
end;

declare
 emplist sys_refcursor;
 pemp emp%rowtype;
begin
   pro_emp_list(10,emplist);
    loop
      fetch emplist into pemp;
      exit when emplist%notfound;
      dbms_output.put_line(pemp.ename);
    end loop;
   close emplist;
end;
--=======================================使用jdbc访问oracle对象（掌握）
--导入Oracle10g驱动包  ojdbc14

select * from emp;

--==========================================触发器
/*
触发器：满足一定设定的触发条件，立即执行

语法：create [or replace] trigger 触发器名
before|after
insert|update|delete
on 表名
for each row  --:new,:old
begin
end;

*/

--添加一个员工后打印一句话“一个新员工添加成功”
create or replace trigger tri_emp_insert_after
after
insert
on emp
begin
  dbms_output.put_line('一个新员工添加成功');
end;

select * from emp;

insert into emp (empno)values (1);
commit;
--不能在休息时间办理入职
/*
raise_application_error(参数1，参数2);
参数1：错误代码 在 -20001~  -20999‘
参数2：提示错误信息
*/
create or replace trigger tri_emp_insert_before
before
insert
on emp
declare
 today varchar2(50);
begin
  select to_char(sysdate,'day') into today from dual;
  
  if trim(today) != 'sunday' then
    raise_application_error(-20001,'HR系统出现bug');
  end if;
end;

--不能给员工降薪
create or replace trigger tri_emp_update_before
before
update
on emp
for each row
begin
  if :new.sal < :old.sal then
     raise_application_error(-20002,'工商局内部错误代码：0x00001');
  end if;
end;

update emp set sal = sal - 2000


--触发器应用（通过序列在插入数据的时候，将ID用序列赋值）
select * from emp;

create or replace trigger tri_emp_insertId_before
before
insert
on emp
for each row
begin
  select seq_emp.nextval into :new.empno from dual;
end;

insert into emp (ename) values ('cgx');
commit;
select * from emp;