精华内容
下载资源
问答
  • SQL语句练习题

    2016-01-02 21:45:26
    精选部分SQL语句练习题,方便sql语句的熟悉与掌握
  • sql语句练习题

    2017-07-28 10:16:54
    sql语句练习题

    主要以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;
    


    展开全文

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,831
精华内容 732
关键字:

sql语句练习题