oracle 笔记

=====================================day1====================================

ORACLE公司
 
ORACLE 数据库

 a) 安装和删除
   orace9i   9.0.1.1  版本
             9.2.1.1  

   超级用户 sys   --- 类似于SQLserver
的sa    （董事长）
   密码: change_on_install

  system ---  第2个超级用户(总经理)
   密码: manager

 服务中 ：
   OracleServiceACCP
       核心系统服务 一定要启动它
       ACCP 就是刚才定的数据库的名字SID 
   OracleOraHome90TNSListener
        oracle监听器 也是要启动的
        应用程序和ORACLE之间的连接

如果监听器无法启动 如何解决??
a) D:/oracle/ora90/network/ADMIN
    listener.ora
改HOST 为
  HOST = localhost

b) 某个键值给优化大师优化没了
    需要改注册表
    <1>改注册表 
           ImagePath  值D:/oracle/ora90/BIN/TNSLSNR
 
    <2> 如果改不了注册表
     命令行去启动
      cmd
      TNSLSNR
      窗口最小化 不要关闭

使用ORACLE
    oracle自带的工具
     sql*plus
        window版
        dos版

普通用户
  用户名:scott
  口令:tiger

    select * from tab; 查询有哪些表
    desc <表名>  例如desc dept
        查询dept表的结构
    exit 退出 工具
    clear screen 清除屏幕

  第三方的工具
     pl/sql developer
 
  图形界面 但是在图形界面中写sql语句不
方便
  
   Enterprise Management Console
改文件
  D:/oracle/ora90/network/ADMIN
      tnsnames.ora
    HOST = localhost 

 三个工具中  选一个方便使用的来操作

 ORACLE启动以后 启动Tomcat会出现问题??
    ORACLE启动后 会占用8080端口
    而Tomcat默认使用8080端口
 修改Tomcat的启动端口
    server.xml
        8080 改为 4001

 c) sql语句 (ANSI92)
   查询
     select * from dept;
     select deptno,dname from dept;
     select deptno,dname from dept
       where deptno = 10;
     select deptno,dname from dept
       order by deptno desc;

     select deptno,dname from dept
       order by deptno desc,
                dname asc;
     --查询没有经理的员工 (is null)
     --不能用 =null
     select * from emp
        where mgr is null;
   修改
     --改员工的津贴为1000
     update emp
        set comm = 1000;
     
     --使用edit修改上次执行的sql语句
     --对多个字段进行修改
        update emp
           set comm=1500,
               sal = 2500,
               job = '销售员'
        where empno =7782;
 
     --把某个字段改为空值
       update emp
           set comm=null
           where mgr is null;
     --作条件判断的时候是 is null/is not null(非空)
      --修改的时候 只能是set comm=null
   
    delete 删除
       --删除满足条件的记录
       delete from emp where
           empno = 7782;
       --删除表中所有的记录
       delete from emp;
       --删除多个条件的记录
       delete from emp
        where deptno =10 and
           sal > 300;
       delete from emp
        where deptno =10 or
           job = 'CLERK';
 d) ORACLE的数据类型

       ORacle       最大长度    SQLserver

字符    varchar2 变长  (4000)     varchar
        char     定长  (2000)     char

数字    number(n) n位整数        int / numeric(n)
        number(n,m) 小数         decimal(n,m)
        number -- 38位有效数字
                  可以表示小数
                  也可以表示整数

日期    date  年月日时分秒       datetime

大对象   文本 clob   (4G)            text (2G)
         二进制 blob  (4G)           image (2G)

   --建立表student
    create table student(--学生表
       stuid number(4), --学号
       stuname varchar2(20),  --姓名
       sex  char(2),  --性别
       birthday date, --生日
       score    number(5,2) --分数  
    );  

   --加入主键
   alter table student add primary key (stuid) ;

   --建立表的同时加主键
     create table student(--学生表
       stuid number(4), --学号
       stuname varchar2(20),  --姓名
       sex  char(2),  --性别
       birthday date, --生日
       score    number(5,2), --分数  
       primary key (stuid,stuname)
    );  
或
     create table student(--学生表
       stuid number(4) primary key, --学号
       stuname varchar2(20),  --姓名
       sex  char(2),  --性别
       birthday date, --生日
       score    number(5,2) --分数  
    );  
  --插入数据
   默认格式'07-5月-05' 2005年5月7号
   insert into student values
    (1000,'Mike','男','07-5月-05',89.5);
    设置日期的格式
      alter session set nls_date_format = 'yyyy-mm-dd';
    insert into student values
    (1001,'Jack','男','1805-05-01',90);
  恢复默认格式
     alter session set nls_date_format =
  'dd-mon-rr';
     rr 世纪的年(100年)
 
  如果需要格式永久生效 需要修改注册表
      insert into student values
    (1002,'Rose','女','1805-05-01',90);
 
注册表 LOCAL_MACHINE/SOFTWARE/ORACLE/HOME0
    增加一个字符串  NLS_DATE_FORMAT 
           值      yyyy-mm-dd
                   dd-mm-yyyy

    insert into student(stuid,stuname)
       values (1004,'Marry');

    insert into student(stuid,stuname,score)
        values (1005,'Mar',null);
    insert into student(stuid,stuname,score,birthday)
        values (1006,'Marley',90,sysdate);

 
 数字类型  number
     整数 number(n) 最大长度是n的整数
           number(4) 
        范围    -9999 到 9999
     小数 number(p,s) 有效数字是p
                      小数位数是s
       number(7,2)   7位有效数字
                     2位小数 
       范围  -99999.99 到 99999.99
     还可以直接使用number  可以表示整数和小数都可以
        精度很高 10^-38  到 10^38次方
     
 字符类型
    varchar2  变长 varchar2(20)  最大4000个字节
      '张三'    -> 实际存放就是4个字节
    char      定长  char(20)  最大2000个字节
      '张三'  -> 实际存放 后填16空格 补齐到20个字节
        char(2)  存放性别 '男' '女'
 
 
 大对象
    字符型大对象  4G 文本
       clob (character Large of object)
               

    二进制大对象   图像和声音  4G
      blob   (binary Large of object)    

    BFile   超过4G
      binary File
       在数据库中存放指针 指向硬盘的一个文件

 日期类型
    Date    大小7个字节
       年月日  还带有时分秒  

体系结构
  sqlserver 分成若干个数据库
      use pubs 选数据库

  oracle 分成用户  就一个数据库
      连接到一个用户 connect scott/tiger

  connect sys/change_on_install as sysdba
  show user 察看当前所在的用户
 
自己建立用户:
   <1> 只有超级用户才能建立用户
    create user zhangsan identified by h123;
   
    grant connect to zhangsan; --登陆
    grant resource to zhangsan; --建立数据库对象

删除用户
   <1> 只有超级用户才能删除用户 
     drop user  zhangsan cascade;

修改用户的密码:
     <1> 在自己的用户下修改自己密码
        alter user zhangsan identified by a456;

     <2> 超级用户可以改所有用户的密码
        connect system/manager
        alter user system identified by s123;
        alter user sys identified by sys123;
        alter user scott identified by t123;

建立一个超级用户 <-> system权限相当
    connect system/manager
    create user zhangsan identified by h123;
    grant dba to zhangsan;

JDBC操作ORACLE数据库
  <1> jdbc-odbc桥
  <2> jar包驱动 D:/oracle/ora90/jdbc/lib/classes12.jar
    a) thin: 不需要安装Oracle的客户端 (****)

    accp是数据库的名字
                     
    Class.forName("oracle.jdbc.driver.OracleDriver");
    String url = "jdbc:oracle:thin:@127.0.0.1:1521:accp";
    String user = "scott";
    String pass = "tiger";
    Connection conn = DriverManager.getConnection(url,user,pass);

    b) fat驱动: 需要安装Oracle的客户端
                效率高
       myaccp是通过net configuration tools
            配置的一个连接串
    Class.forName("oracle.jdbc.driver.OracleDriver");
    String url = "jdbc:oracle:oci8:@myaccp";
    String user = "scott";
    String pass = "tiger";
    Connection conn = DriverManager.getConnection(url,user,pass);

 
 sql语句中使用函数
 Oracle中的函数
    分2类  单行函数
           分组函数 

  单行函数
    日期的函数
       Oracle:    sysdate   系统时间
       sqlserver: getdate()

select sysdate from dual;

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
   from dual;

  select sysdate+1 from dual;
        数字和日期进行运算 数字就表示天数
  select sysdate - 7 from dual;

  两个日期类型的数据相减 得到天数
     select (sysdate-hiredate) as 天数 from emp;

  add_months(date,num)  得到的是date 经过
                            num个月后的日期

 select add_months(sysdate,12) from dual;
    1年后的今天
 select add_months(sysdate,-12) from dual;
    1年前的今天

  months_between(date1,date2)
     返回2个日期之间的月数
      date1 - date2

  select months_between(sysdate,hiredate)
    from emp;

 select months_between(hiredate,sysdate)
    from emp;

 所在月的最后一天 last_day(date)  返回日期所在月的最后一天
  select last_day(sysdate) from dual;

  select last_day(sysdate)-2 from dual;
     倒数第三天
 
  select add_months(last_day(sysdate)+1,-1)+2
   from dual; 本月的第三天
 

 NEXT_DAY( SYSDATE,'MONDAY') 下一个星期一是几号？？
  select NEXT_DAY( SYSDATE,'星期一') from dual;

转换函数
   to_char() 把日期或数字 转化为字符串

 可以取出日期中的年月日  (转换的掩码)
  select to_char(sysdate,'yyyy') from dual;
   select to_char(sysdate,'mm') from dual;
 select to_char(sysdate,'dd') from dual;

 select to_char(sysdate,'d') from dual;
 select to_char(sysdate,'day') from dual;
 select to_char(sysdate,'ddd') from dual;

 select to_char(sysdate,
 'yyyy-mm-dd hh24:mi:ss d day ddd') from dual;
 
 转换数字 成字符串
   select to_char(234) from dual;

 to_date 转换字符串 成为日期

   alter session set nls_date_format='dd-mm-yyyy';
不想修改默认格式  插入'dd-mm-yyyy'作为日期？？？

 insert into student(stuid,birthday)
   values (1122,to_date('1972-08-05','yyyy-dd-mm'));

 insert into student(stuid,birthday)
   values (1122,to_date('08-05-1972 09:40:22','dd-mm-yyyy hh24:mi:ss'));

  to_number  把字符串转成数字
   select to_number('56') from dual;
  
  select to_number('aa') from dual; --不能转的

数字函数
   大于n的最小整数
   select CEIL(5.6)   from dual;
   select CEIL(-5.6)   from dual; 
 
   小于n的最大整数
   select floor(-5.6) from dual;
 select floor(5.6)   from dual;

   求余数
    select mod(3,2) from dual;
    select mod(-3,2) from dual;

   四舍五入
    select round(4.5) from dual;
 select round(4.4) from dual;
  select round(4.567,2) from dual;
     保留2位小数

   截断数字(舍去尾数)
    select trunc(4.5) from dual;
  select trunc(4.567,2) from dual;

  select to_char(trunc(sysdate),'hh24:mi:ss') from dual;

字符函数
   连接字符串 ||
       select 'Hello'||' '||'World' from dual;

       select ename||'-'||job from emp;

    select to_char(sysdate,'yyyy')||'年'||
to_char(sysdate,'mm')||'月'||
to_char(sysdate,'dd')||'日'
     from dual;

select to_char(sysdate,'yyyy"年"mm"月"dd"日"')
  from dual;

 INITCAP 首字母大写
  select initcap(ename),ename from emp;

 

=====================================day2=====================================

 字符函数
    concat 连接  ||
   <1>显示dname和loc中间用-分隔
     select deptno,dname||'----'||loc from dept;
    
     dual哑元表   没有表需要查询的时候 可以用它
         select 'Hello World' from dual;
         select 1+1 from dual;
         查询系统时间
          select sysdate from dual;
   <2>  initcap 首字母大写
       select ename,initcap(ename) from emp;
   <3>  lower   转换为小写字符
        select ename,lower(ename) from emp;
   <4> upper 转换为大写
        update dept set loc=lower(loc);
        update dept set loc=upper(loc);
   <5> LPAD 左填充
        select deptno,lpad(dname,10,' '),loc from dept;
   <6> RPAD 右填充
   <7> LTRIM 去除左边的空格
                      
       RTRIM 去除右边的空格

       ALLTRIM  去除两边的空格

   <8>replace    替换
      translate  转换
       select ename,replace(ename,'S','s') from emp;
           用's'去替换ename中的'S'
        select ename,translate(ename,'S','s') from emp;
   <9> ASCII 求ASC码
       chr   asc码变字符
         select ascii('A') from dual;
         select chr(97) from dual;
         select 'Hello'||chr(9)||'World' from dual;
                           '/t' ascii码是 9
                           '/n' ascii码是 10

         select 'Hello'||'/t'||'World' from dual;    

   <10> substr 字符截取函数
           select ename,substr(ename,1,3) from emp;
               从第1个位置开始 显示3个字符
           select ename,substr(ename,4) from emp;
              从第4个位置开始显示后面所有的字符
   <11> instr 测试字符串出现的位置
          select ename,instr(ename,'S') from emp;
             'S'第1次出现的位置
  select ename,instr(ename,'T',1,2) from emp;                
             从第1个位置开始 测试'T'第2次出现的位置 
  <12> length 字符串的长度
        select ename,length(ename) from emp;

混合函数
      求最大值
   select greatest(100,90,80,101,01,19) from dual;
  
      求最小值
   select least(100,0,-9,10) from dual;

      空值转换函数 nvl(comm,0) 字段为空值 那么就返回0 否则返回本身
   select comm,nvl(comm,0) from emp;
      comm 类型和 值的类型是 一致的
  
复杂的函数
   decode   选择结构  (if ... elseif .... elesif ... else结构)
   
要求:
   sal=800  显示低工资  
   sal=3000  正常工资
   sal=5000  高工资
   
    只能做等值比较

  select sal,decode(sal,800,'低工资',3000,'正常工资',5000,'高工资','没判断')
  from emp;
 表示如下的if  else 结构
     if sal=800 then
        '低工资'
     else if sal =3000 then
        '正常工资'
     else if  sal = 5000 then
         '高工资'
     else
        '没判断'
     end if
         

   sal > 800   变换       sal -800 > 0  

 判断正负
  sign(x)   x是正  1
            x是负  -1
            x是0   0
    select sign(-5) from  dual;

   sal>800   sign(sal-800) = -1  sal<800
             sign(sal-800) = 0   sal=800
             sign(sal-800) = 1 sal > 800       

 如何做大于小于的比较？？？？
  sal<1000  显示低工资   sal-1000<0   sign(sal-1000) = -1
   1000<=sal<=3000  正常工资
   3000<sal<=5000  高工资

  select sal,decode(sign(sal-1000),-1,'低工资',
                  decode(sign(sal-3000),-1,'正常工资',0,'正常工资',1,decode(sign(sal-5000),-1,'高工资',0,'高工资',1,'没判断')
))  from emp;

   select sal,decode(
             sign(sal-1000),-1,'低工资',
            decode(sign(sal-3000),-1,'正常工资',
                            0,'正常工资',1,
            decode(sign(sal-5000),-1,'高工资','高工资')
             )) as 工资状态 from emp;
     
  一般的情况  decode(x,y1,z1,y2,z2,z3)
      if x= y1 then
          z1         
      else if x = y2 then
          z2
      else
         z3
      end if 

分组函数  
    返回值是多条记录 或计算后的结果
      group by
      sum
      avg
      count

<1>  计算记录的条数 count

  select count(*) from emp;
  select count(1) from emp;

  

  select count(comm) from emp; 字段上count 会忽略空值
      comm不为空值的记录的条数

  统计emp表中不同工作的个数 ????
   select count(distinct job) from emp;

   select distinct job from emp;
   select distinct job,empno from emp;
   select job,empno from emp;
    得到的效果是一样的,distinct 是消去重复行
                       不是消去重复的列
 <2>group  by 分组统计
     --在没有分组函数的时候
     --相当于distinct 的功能
     select job from emp group by job;

     select distinct job from emp;

   --有分组函数的时候
   --分组统计的功能
   统计每种工作的工资总额是多少??
     select job,sum(sal) from emp
          group by job;       --行之间的数据相加

     select sum(sal) from emp;  --公司的工资总额

 统计每种工作的平均工资是多少?? 
     select job,avg(sal) from emp
          group by job;   

    select avg(saL) from emp; --整个公司的平均工资

 显示平均工资>1000的工作?？？
   <1>统计每种工作的平均工资是多少
   <2>塞选出平均工资>1000的工作     
 

    从分组的结果中筛选 having
   select job,avg(sal) from emp
          group by job
          having avg(sal) > 1000;
   group by 经常和having搭配来筛选

 显示平均工资>公司的平均工资 的工作?？？
select job,avg(sal) from emp
          group by job
          having avg(sal) > (select avg(sal)
          from emp);

计算工资在1000以上的各种工作的平均工资 大于2000的工作？？？
  select job,avg(sal) from emp
          where sal > 1000
          group by job
          having avg(sal) > 2000 ;
  where / group by /having 出现的顺序不能变

    一般group by  和 having搭配
        表示对分组后的结果的筛选
     where子句 --- 用于对表中数据的筛选  
 
  <3> max min
   select max(sal) from emp;
     公司的最高工资
   select min(sal) from emp ;
     公司的最低工资

找每个部门的最高和最低的工资??
  select deptno,max(sal),min(sal) from emp
     group by deptno;
找每个工作的最高和最低的工资??
  select job,max(sal),min(sal) from emp
     group by job;
找每个部门中每种工作的最高和最低的工资??
  select deptno,job,max(sal),min(sal)
   from emp
   group by deptno,job;

 select max(sal),min(sal)
   from emp
   group by deptno,job;

    单个字段如果没有被分组函数所包含，
       而其他字段又是分组函数的话     
      一定要把这个字段放到group by中去

约束
   主键约束 --  每个表要有主键，唯一的标识一行数据
   非空约束
   唯一性约束
   外键约束
   检查约束

     脚本(SCRIPT)
        create table cla( --班级表
          id number(2) primary key, --班级编号
          cname varchar2(20) not null --班级名字
       );

        create table stu( --学生表
          xh number(4) primary key, --学号是主键
          xm varchar2(20) not null, --姓名非空
          age number(2) check (age between 10 and 90),--年龄在10到90之间(10<= age  <=90 )
          birthday date,
          shenfenzheng number(18) unique, --身份证唯一 
          classid number(2) references cla(id) -- 班级编号外键
           --(引用的一定是主键或唯一性约束的字段)
         );
       
      <1>建立表的同时使用约束
  create table student( --学生表
           xh number(4) primary key, --学号主键
           xm varchar2(10) not null, --姓名不能为空
           sex char(2)  check (sex in ('男','女')), --性别
           birthday date unique, --日期
           sal number(7,2) check (sal between 500 and 1000),--奖学金 sal >=500 and sal <=1000
     classid number(2) references cla(id)
        );  --必须要先有cla表才对
            --一定先建立班级cla表
 
   主键约束 primary key
           not null
           check
           unique 唯一约束          

 create table student( --学生表
           xh number(4) constraint pk_stu primary key, --学号主键
           xm varchar2(10) constraint nn_stu not null, --姓名不能为空
           sex char(2) constraint ck_stu_sex check (sex in ('男','女')), --性别
           birthday date constraint uq_bir unique, --日期
           sal number(7,2) constraint ck_sal check (sal between 500 and 1000)--奖学金 sal >=500 and sal <=1000
        );
      <2>建立约束的同时给约束指定名字，便于删除
        create table cla( --班级表
          id number(2) constraint pk_cla primary key, --班级编号
          cname varchar2(20) constraint nn_cla not null --班级名字
       );
     
      create table stu( --学生表
          xh number(4) constraint pk_stu primary key, --学号是主键
          xm varchar2(20) constraint nn_stu not null, --姓名非空
          age number(2) constraint ck_stu check (age between 10 and 90),--年龄在10到90之间(10<= age  <=90 )
          birthday date,
          shenfenzheng number(18) constraint uq_stu unique, --身份证唯一 
          classid number(2) constraint fk_stu references cla(id) -- 班级编号外键
           --(引用的一定是另外表的主键或唯一性约束的字段)
         );
  
 
      <3>建完表后加约束
 
 学生表student
        create table student( --学生表
           xh number(4), --学号
           xm varchar2(10), --姓名
           sex char(2), --性别
           birthday date, --日期
           sal number(7,2) --奖学金
        );
 加约束
   加主键
    alter table student add constraint pk_stu
    primary key (xh);
   加非空
    alter table student modify (xm not null);
   检查约束
    alter table student add check(sex in ('男','女'));
    alter table student add constraint ck_sal check(sal between 500 and 1000));

 给student加班级字段
   alter table student add (classid number(2));

   班级表cla
    create table cla( --班级表
          id number(2), --班级编号
          cname varchar2(20) --班级名字
       );

添加 主键
 alter table cla add constraint pk_cla
       primary key (id);
加 not null
 alter table cla modify
       (cname not null);

               学生表student
      create table student( --学生表
          xh number(4) ,
          xm varchar2(20) , --姓名非空
          age number(2),--年龄在10到90之间(10<= age  <=90 )
          birthday date,
          shenfenzheng number(18), --身份证唯一 
          classid number(2) -- 班级编号外键
           --(引用的一定是另外表的主键或唯一性约束的字段)
         );

加外键约束
alter table student add constraint fk_stu
    foreign key (classid) references cla(id);

加主键
alter table student add constraint pk_stu
 primary key (xh);

加not null
alter table student modify(xm not null);

加检查约束
alter table student add constraint cc_age
 check (age >= 10 and age <=90);

加唯一约束
  alter table student add constraint
      uq_sfz unique(shenfenzheng);

加外键约束
 alter table student add constraint
    fk_stu foreign key (classid)
     references cla(id);

如何删除约束
  
  alter table student drop constraint
        fk_stu;
可以用一个统一的格式来删除
  alter table 表名 drop constraint 约束名

  <4>如何查看约束?? 约束一定加在表上

    一个表上到底有哪些约束???
  select constraint_name,constraint_type
      from user_constraints
        where table_name = 'STUDENT'
--查看表上有什么约束
  select * from user_constraints;
--查看约束作用在什么字段上
  select * from user_cons_columns
   where CONSTRAINT_NAME='PK_STU';

user_constraints数据字典表

  <5>约束是如何起作用的??

       create table cla( --班级表
          id number(2) constraint pk_cla primary key, --班级编号
          cname varchar2(20) constraint nn_cla not null --班级名字
       );
     
      create table stu( --学生表
          xh number(4) constraint pk_stu primary key, --学号是主键
          xm varchar2(20) constraint nn_stu not null, --姓名非空
          age number(2) constraint ck_stu check (age between 10 and 90),--年龄在10到90之间(10<= age  <=90 )
          birthday date,
          shenfenzheng number(18) constraint uq_stu unique, --身份证唯一 
          classid number(2) constraint fk_stu references cla(id) -- 班级编号外键
           --(引用的一定是另外表的主键或唯一性约束unique的字段)
         );
  
   主键 = 非空 + 唯一
   非空
   唯一 = 有值的话  值要不同
          null的话  都是可以的
   外键 = 有值 一定要在被引用的表的数据中
          null的话  是可以的

<3>关联查询 子查询
  功能正确 - 〉sql语句能查出数据
  性能要好 - 〉sql语句尽量使用上索引

 关联查询:
    多个表
      放在from子句后面 一定要带连接的条件
      不然的话 变成 笛卡尔积

      连接条件个数  = 表的个数 - 1

      参加连接的字段 只要存放的数据代表相同的含义
      就可以连接在一起 （不一定非要是外键关系或名字一样）

   内连接  (多个表 完全匹配的记录查询出来)
    select dname,ename from dept,emp
      where dept.deptno = emp.deptno and dept.deptno=10;

标准SQL的写法
   select dname,ename from dept inner join emp
     on dept.deptno = emp.deptno
     where dept.deptno=10;
  连接的条件要使用on 来表示
  筛选条件使用where来表示
   
 select dept.deptno,dname,ename from dept,emp
      where dept.deptno = emp.deptno

   外连接 (多个表中部分匹配的记录查询出来)
     左外连接
     右外联结
     全外联结
    
    查询部门名，员工名，以及没有员工的部门名???
 外连接
  select dept.deptno,dname,ename from dept,emp
      where dept.deptno = emp.deptno(+)
   (+)的对面侧的表(dept)中没有匹配的记录显示出来

  select dept.deptno,dname,ename from dept,emp
      where dept.deptno(+) = emp.deptno
 
 标准sql来写
  --左外连接
  --dept表中没有匹配的记录显示
   select dept.deptno,dname,ename
      from dept left outer join emp
      on dept.deptno = emp.deptno;
     
  --右外联接
  --emp表中没有匹配的记录显示
   select dept.deptno,dname,ename
      from dept right outer join emp
      on dept.deptno = emp.deptno;

   --全外联接
   --2个表没有匹配的记录都显示
   select dept.deptno,dname,ename
      from dept full outer join emp
      on dept.deptno = emp.deptno;
 

  --自连接
   --自己的表和自己连接到一起
   --通过别名把一张表 变成2张表

    显示员工 和其直接上级的名字？？？？
    select a.ename as 员工,b.ename as 经理
    from emp a,emp b
    where a.mgr = b.empno(+);

    a -- 员工的信息
    b -- 领导的信息

  --Sqlserver中的top N
   --ORACLE rownum的伪列 (用于分页显示)

   select rownum,emp.* from emp

   查询emp表中的前3行记录

     select * from emp where rownum <= 3

   查询emp表中的第三行记录

     select * from emp where rownum > 2
      and rownum < 4;

   <1>1个表的数据的分页显示
     emp 分页的条件: 每页显示4条

   第3页的内容:
      1-4     page 1
      5-8     page 2
      9-12    page 3
   
    select * from (select rownum lineno,emp.* from emp) where lineno >= 9 and lineno <= 12;

  <2> 2个表的数据查询的分页显示

    select dname,empno,ename from
      dept a,emp b
    where  a.deptno = b.deptno
 

select * from
(select rownum lineno, dname,empno,ename from
      dept a,emp b
    where  a.deptno = b.deptno )
where lineno >=9 and lineno <= 12;

 --Oracle中的层次查询(connect by 语句)
     使用伪列 level

    select lpad('+',level*2,' ')||ename from emp
    connect by prior empno = mgr
    start with mgr is null;

       select lpad('+',level*2,' ')||ename from emp
    connect by prior empno = mgr
    start with ename='BLAKE';

  --子查询
    a) 关联子查询
      
      求工资最高的前2名员工???
       select * from
      (select * from emp order by sal desc)
        where rownum <= 2;
     
       名次比较 转化为 人数比较
      select * from emp a where
       (select count(*) from emp where
             sal > a.sal) <=1

    b) 非关联子查询

      比部门30的平均工资高的所有员工
       select * from emp where sal >
         (select avg(sal) from emp where deptno=30)

 

 

=====================================day3=====================================

-----集合运算SQL语句
   集合概念
       (1,2,3,4,5,6)  A
        (4,5,6)       B

  A和B 交集　(4,5,6) 公有的元素  intersect

　A和B 并集　所有的元素　(1,2,3,4,5,6) union 去重复值
        (1,2,3,4,5,6,4,5,6)   union all 不去重复值

  A和B 差集　A-B  A中去除B中的元素 （1,2,3)
            B-A  B中去除A中的元素  空集
      minus
 
 create table A(
   num number(2));
insert into A values (1);
insert into A values (2);
insert into A values (3);
insert into A values (4);
insert into A values (5);
insert into A values (6);

 create table B(
   num number(2));

insert into B values (4);
insert into B values (5);
insert into B values (6);
 
  集合运算的基本要求 : 对应的字段的数据类型是一样的
                      字段的个数要一样
    A和B 交集
        select num from a
        intersect
        select num from b;
    A和B 并集
        select num from b
        union
        select num from a;

        select num from b
        union all
        select num from a;
   A和B的差集
　　　a-b
       select num from a
       minus
       select num from b;
     b-a
       select num from b
       minus
       select num from a;

create table grade( --选修课程
  xm varchar2(20) , --姓名
  subject varchar2(20),　--课程
  mark number(5,2) ,　--分数
  primary key (xm,subject)
);

insert into grade values ('Mike','Java',90);
insert into grade values ('Mike','C++',85);

insert into grade values ('Martin','C++',87);
insert into grade values ('Martin','Java',80);
insert into grade values ('Martin','C#',76);

insert into grade values
 ('Rose','Java',87);

查询包含了Mike所选课程的别的同学的姓名？？？

　select distinct xm from grade
   where subject in (select
    subject from grade where xm='Mike')
and xm <> 'Mike';

思路:
 前提是Mike选了课程的

  Mike所选课程的集合　- 别的同学选课的集合
　　= 空集

select  xm from grade a
   where
 not exists (  (select subject from
      grade where xm='Mike')
minus
  ( select subject from  
    grade where xm=a.xm)
) and a.xm <> 'Mike'
group by xm;

-- 技巧性的查询语句
 1. emp如何备份一张表???
   复制数据：
    create table emp_bak as select
         * from emp;

     新的表 emp_bak数据和原表emp完全一样
        emp表中的约束 不能复制过去

   复制结构:
    create table emp_nbak as select * from emp
      where 1=2;

   sqlserver的复制表:
     select * into aa from emp;
           建立表aa 插入emp中的数据到aa中

     select * into bb from emp where 1=2;
           复制结构

       
 2. emp表中的数据 一次性的插入到表emp_nbak中??

   insert into emp_nbak select * from emp;

   insert into emp_nbak(empno,ename) select empno,
     ename from emp;

 3.  别名
   列的别名
      select ename "姓   名"
       from emp;

   表的别名
      select a.ename,b.dname from emp a ,dept b
        where a.deptno = b.deptno 

总结: 数据库 中的sql语句
   <1> DDL (DATA Define Language) 数据定义语言
       create ...
       drop ..
          drop table emp; -- 删除表的结构和数据
          delete from emp; -- 删除数据,可以恢复
            delete from emp where deptno=10;
          truncate table emp; --删除数据,不能恢复被删除的数据 
       alter ..
         create table student(
            stuid number(4) primary key,
            stuname varchar2(20),
            mark number(5,2)
         );
        --增加字段birthday
         alter table student add (birthday date);
        --修改字段stuname的长度
         alter table student modify (stuname varchar2(10));
        --删除字段mark
         alter table student drop column mark;
        --给表改名字
         rename student to stu;
         rename stu to student;

       truncate ...
         truncate table emp;

    <2> DML (Data manupulate language) 数据操作语言

      select
        查询语句
      insert
         insert into emp(empno,ename,deptno)
            values (9999,'Mike',20);
         insert into emp select * from emp2;
 
      update
         update emp set ename='Mike',
                        job='MANAGER',
                        sal=900
             where empno=7369;
 --给每个人涨工资20%
          update emp set sal = sal * 1.2;
 --修改每个人的工资为其所在部门的平均工资
         update emp a set sal = (select avg(sal) from emp
               where deptno=a.deptno);
                       

      delete
          delete from emp;
          delete from emp where deptno=10;

   <3> TCL(事务控制语言) Tansaction Control Language
        commit
        rollback
        savepoint
   事务: DML语句必须形成一个整体来执行
        delete
        update
        insert
   银行转帐:
        帐户zhangsan          帐户lisi
        2000                   3000
         张三转1000 到lisi的帐户上????
        
          -1000                 +1000

    create table account(
       acc_name varchar2(20) primary key,
       num number(7,2)
    );

    insert into account values ('zhangsan',1000);
    insert into account values ('lisi',3000);

    转帐
       update account set num=num-1000
           where acc_name='zhangsan';
      
       update account set num=num+1000
           where acc_name='lisi';
   
    Oracle: 写在一个存储过程中

      create or replace procedure p1
      as
      begin
           update account set num=num-1000
             where acc_name='zhangsan';
          
            
           update account set num=num+1000
             where acc_name='lisi';

           commit;
       when others then
          rollback;

      end;

   java语言编程实现事务:
JDBC本身存在事务模型的(显式的事务)

         Connection conn = DriverManager.getConnection();
         conn.setAutoCommit(false);
           ....
         conn.commit(); /conn.rollback();

   sqlserver中的事务: 显式事务 (自动提交)
          set implicit_transactions on  打开隐式事务
          set implicit_transactions off 关闭隐式事务
     
      明确的声明隐式事务
          begin transaction

           update account set num=num-1000
           where acc_name='zhangsan';
      
           
           update account set num=num+1000
            where acc_name='lisi';
          if @@ERROR<> 0
            rollback transaction
          else
             commit transaction

在Oracle中 DML语句所形成的事务提交的方法:
   <1> commit 提交事务

   <2> 未提交的事务 后紧跟一条DDL/DCL(grant revoke) 
        语句 都会导致事务的提交的

  savpoint  分解事务(大的事务 分解为若干小的事务)

      update emp set sal=560 where empno=7499;

      savepoint  c1;

      update dept set dname='AA' where deptno=30;

      savepoint  c2;

      delete from emp where empno=7499;

      rollback to c2; --delete给回滚
      rollback to c1; --回滚update dept
      rollback;       --回滚update emp

   <4> DCL (Data Control Language) 数据控制语言
     grant --- 授予权限
     revoke -- 回收权限

     a) 建立用户的时候
         grant connect,resource to <用户>

     b) 用户scott 能够访问 用户B的某张表???
         scott: emp dept
         zhangsan: student

       connect system/manager
       grant connect,resource to zhangsan
        identified by h123;
       connect zhangsan/h123;
       create table student(
         stuid number(4) primary key,
         stuname varchar2(20)
       );
       insert into student values (1000,'JACK');

    grant select
          update  on <对象> to <用户>
          delete
          insert  

          all(select/update/delete/insert)

    revoke select on <对象> from <用户>

    级联授权(with grant option)
     用户A           用户B       用户C
      student
    
            ----->         -->
             grant select on student
                to B with grant option

                             grant select on A.student
                              to C 

  约束: 表上或字段上的约束

       数据的完整性

    主键
    非空
    检查
    外键
    唯一性
    缺省值 default

    create table bj(  --班级
        cid number(2) primary key,
        cname varchar2(20) not null
    );
        
    create table student( --学生
       stuid number(4) primary key, --主键
       stuname varchar2(20) not null,  --非空
       age number(2) check (age between 10 and 50),
       sfz varchar2(18) unique,
       classid number(2) references bj(cid)
            on delete cascade,
       sex varchar2(2) default '男' check (sex in ('男','女'))       
      );

   外键中被引用的字段 一定是主键或者具有唯一性约束的字段

    create table grade(
      stuid number(4),
      subid number(2),
      mark number(5,2),
      primary key (stuid,subid)
    )
      
   建完表以后在加约束

 create table student( --学生
       stuid number(4),
       stuname varchar2(20), 
       age number(2),
       sfz varchar2(18),
       classid number(2),
       sex varchar2(2)   
      );
--加主键约束
 alter table student add primary key (stuid);
--删除主键约束
 alter table student drop primary key;

--加非空约束
 alter table student modify (stuname not null);

--加检查
 alter table student add constraint ck_1
   check (age >=20 and age<=60);
--删除约束
 alter table student drop constraint ck_1;

--加唯一性
 alter table student add (sfz unique);

--加外键
 alter table student add foreign key (classid)
   references bj(cid)
   on delete cascade

---其他数据库对象
  <1> 序列
     sqlserver :
         create table subject(
          subid int identity(1,2) primary key,
          ...);
    oracle中如何实现自增长的字段???
        a)建立一个序列的对象
         create sequence seq1;
       
        b)   create table subject(
          subid number primary key,
          subname varchar2(20)
          );
  
        c)  insert into subject (subid,subname)
             values (seq1.nextval,'aa');
        伪列 nextval ---下一个值
             currval --- 当前值

       create sequence seq2
         start with 1000
         increment by 5
         maxvalue 2000
         minvalue 900
         cycle
         nocache    ---- 没有缓存
           cache 20 ---- 默认的cache是20个    

   1000
      1005 --- 1010 ---             缓冲了以后的19个号

  用途: 产生一个唯一的编号，
       但是编号的连续性是不能保证的

   <2> 视图 view
     表 存放数据
     视图 存放的是一条查询语句

      create or replace view v_emp
      as select ename,sal from emp where deptno=10;

      select * from v_emp;

 

=====================================day4=====================================

 <2> 视图 view
     表 存放数据
     视图 存放的是一条查询语句  (基表)

      create or replace view v_emp
      as select ename from emp where deptno=10;

      select * from v_emp;
    作用: a) 隐藏信息 增加安全性

       emp (sal)

       create or replace view rs_emp as
        select ename,empno,job from emp ;

       select * from rs_emp;

      select * from user_views; --数据字典表(用户所拥有的视图)

   b) 报表 : view
         select * from <VIEW>

     视图可以 实时反应表中的变化的
    
     视图是否可以修改???
       <1> 简单的视图(单表查询,没有分组和关联查询)
               是可以通过视图修改基表的
       <2> 关联查询,分组函数的视图是不能修改的
with read only 只读视图
        create or replace view v_emp
        as select ename from emp where deptno=20
        with read only;
 
with check option 检查select语句的where条件是否被违背

        create or replace view v_emp
        as select empno,ename,deptno from emp where deptno=20
        with check option;
    不能修改数据 使得修改后的结果违背where条件
    插入:
       insert into v_emp values (9999,'kl',10);
         无法插入 违背where条件
    删除: delete from v_emp where deptno=10;
            和where条件无关

原则: 都设计只读视图 -> 简化查询
       尽量不要通过视图 修改基表

   <3> 同义词synonym --- 提高系统的安全性
    
  私有同义词 -- 属于建立它的用户
     create synonym myemp for scott.emp;
        为scott下的emp表建立同义词myemp

      select * from scott.emp -> select * from myemp;

   a) zhangsan/h123
          select * from scott.emp;
          create synonym myemp for scott.emp;
          select * from myemp;
          update myemp set sal=500;

   b) scott/tiger
        emp 使zhangsan能访问它
         grant select on emp to zhangsan;
         grant update on emp to zhangsan;
  公有的同义词
    超级用户才能建立
     create public synonym memp for scott.emp;
    所有的用户 都能访问这个同义词
     connect zhangsan/h123
     select * from memp;
   把scott下的emp表的查询权限授予所有的用户
     connect scott/tiger
     grant select on emp to public;

 设计时候的使用:
   人事系统
       grant connect,resource to hrms identified by hrms;
            employee
            dept
         grant select on employee to test;

   销售系统
       grant connect,resource to sales identified by sales;
            orders
            customers  
   财务系统
       grant connect,resource to cw identified by cw;
            salary

   公用的用户:
       test / test123
       connect (insert / delete / update )
                不能执行DDL (drop table) 
                系统运行
       resource
                系统开发的时候 授予的权限

        建立synonym
           create synonym emp for hrms.employee;

   <4> 索引
      作用: 加快查询速度（select）
         功能正确....
         性能好......???
     索引一定是建立在表的字段上的
     索引的维护是自动进行的

      a) 都某些具有约束的字段 自动建立索引
             primary key -> index
             unique      -> index

      b) 自己根据需要来建立索引
           create index idx_ename on emp(ename);
             在表emp的ename字段上建立索引idx_ename
           drop index idx_ename;
             删除索引idx_ename

           create index idx_a on emp(job,sal)

          emp (empno主键) -> 索引

              select * from emp where empno=7369;
   表emp字段empno没有索引
                  对emp表中的数据逐行塞选
  表emp字段empno有索引
         先在索引中查找empno=7369
            ->  rowid(每条数据的物理地址) -> 直接
           利用rowid到emp中去定位

   select * from emp; -> 无法使用任何索引
                          全表扫描

   select * from emp where ename like '张%';
     只有带有where条件的select语句才可以使用上索引
       create index idx_en on emp(ename);
 Oracle中索引的使用 可以通过执行计划来看到 
       table access full   全表扫描
       index range scan    索引的范围扫描
       table access by index rowid 通过rowid来访问表的纪录

  <1> 索引过字段是不能使用函数和参与运算的
   a. 查询在1980年参加工作的所有员工???

      create index idx_date on emp(hiredate);

       不能使用hiredate上的索引
      select * from emp
         where to_char(hiredate,'yyyy')='1980';

       可以使用hiredate上的索引
     select * from emp where hiredate >=
        to_date('1980-01-01','yyyy-mm-dd') and
        hiredate < to_date('1981-01-01','yyyy-mm-dd')

   b. 查询在50天以前参加工作的员工？？？
     不能使用hiredate的索引
     select * from emp where hiredate + 50 < sysdate;

     可以使用hiredate上的索引
     select * from emp where hiredate < sysdate  -50;

   功能正确
   性能优良

 数据字典 -> 纪录系统内部结构的表和视图
   sys -> 拥有一套数据字典
   system  -〉察看数据字典 

 命名规则:
      user_xxxx     用户所拥有的对象的表

      all_xxx       用户有权察看的对象的表  

      dba_xxx       整个系统所拥有的对象的表

xxxx --- 对象英文名字的复数形式

  user_tables
  user_views
  user_indexes

 所有的数据字典的列表
      select * from dict;
 
   用户下的所有表(拥有)
    select table_name from user_tables;
   用户下有权访问的表(拥有+别人授权给你可以访问的)
    select table_name from all_tables;

   Oracle系统中的所有用户??
      select * from dba_users;

数据库设计???
   <1> 表结构
 需求分析:
      名词 -〉 表

规范化设计
       1NF -- 表都要有主关键字
       2NF -- 除主键外的所有字段 都依赖于主键
       3NF -- 违反2NF的设计如何解决??
    
    emp empno(pk),deptno,[dname]
    dept deptno,dname

反规范化    -> 保证查询效率的
 
    select ename,dname from emp,dept where
       emp.deptno = dept.deptno

  把dname的字段 放在emp表中
    select ename,dname from emp;

 

   <2> 实现的功能：
        视图
        索引
        存储过程
        触发器

=====================================day5=====================================

 

ORACLE下的编程
  为什么还需要在数据库中编程?
  <1>一条SQL语句完成不了功能
  <2>可能完成 但SQL语句太复杂 效率低下
 

引入编程
   SQL语句 -- 和数据库交互
   ORACLE内部编程语言 PL/SQL   

  PL/SQL语言 (procedure language)
  SQL - 结构化查询语言 - (DDL/DML/TCL/DCL) - 4GL
  PL - 过程化的语言 - (if结构 循环结构等) - 3GL

问题:

 程序的结构？？？
 定义变量？？？
 变量怎么赋值？？？
 数据如何显示？？？？
 逻辑控制语句？？？
 
 如何与数据库交互？？？

<1>PL/SQL程序的结构
  PL/SQL块(block)
    
     declare
      --定义变量,先定义 后使用 强类型语言
      --不区分大小写 
     begin
      --执行语句(SQL语句/逻辑结构if 循环)
      --可以写的SQL语句是DML 和TCL
      --DDL 和DCL是不能直接写在PL/SQL块中
       
     
     exception
      --错误处理程序
      --异常处理
     end;

最简单的PL/SQL块
                begin
                  null; --空语句(什么事都不干 占位置的)
                end; 
<2> 定义变量
定义变量？？？
  变量的类型
     数字    number
             number(4) number(7,2)
     字符    varchar2(20) char(20)
     日期    date
     布尔值  boolean   -- 在数据库中是没有这种类型的
                      --只能用在编程中
           
 
<3> 变量怎么赋值？？？ 数据如何显示？？？？

  赋值语句 :=

    declare
      aa number; --30个字母以内 第一个字母不能是数字
      bb number(5) default 1000; --cc赋初始值1000
      cc number(7,2) := 12.45; --cc赋初始值12.45
      c1 number(7,2) := -5.0; --c1赋初始值-5.0

      dd varchar2(20) := 'Hello';  --字符
      ee char(3);  

      birthday date;   --日期
     
      islogin boolean := false;   --布尔值

      pi constant number := 3.1415; --常量pi                                             --不能改变它的值

--利用数据库的属性来定义变量
      eno emp.empno%type;--%type定义变量的类型和字段一样
      erow emp%rowtype; --%rowtype定义变量的类型和表中的行一样
        --erow.empno/erow.ename
        --利用.的方法来访问行中的每个字段
      enm emp.ename%type;
 
     begin
       --给变量赋值
       --<1>
        aa := 120.24;
        dd := 'SMITH';
     
        birthday := '01-4月-05';
        --系统时间赋值
        select sysdate into birthday from dual;
        select to_char(sysdate,'yyyymmdd') into dd from dual;
 
       --<2>数字类型
        bb := &ggggggggggg;
        --字符类型
        enm := '&输入值';
       --<3> into子句
        --使用列类型变量作为参数
        select sal,ename into cc,dd from emp
        where ename = enm;         
         --使用行类型变量
        select * into erow
        from emp where empno = 7369;
 
       --打印变量的值
       DBMS_OUTPUT.put_line('aa='||aa);
       DBMS_OUTPUT.put_line('bb='||bb);
       DBMS_OUTPUT.put_line('cc='||cc);
       DBMS_OUTPUT.put_line('dd='||dd);
       DBMS_OUTPUT.put_line(erow.empno||'-'||erow.ename||'-'||erow.sal);
     end;

使用DBMS_OUTPUT来输出结果时，必须先
 执行set serverout on 命令

PL/SQL中SELECT语句的要求
〈1〉在PL/SQL块中的select语句必须有into子句
 <2> select语句只能返回一条记录,不能是多条，也不能是0条
 <3> 字段个数和变量的个数要一样

<3> 逻辑控制语句？？？
--循环语句
  3种循环
  (a) loop循环
     格式:loop
           exit when <条件> --当条件成立的时候退出循环
          end loop; 
   
     declare
       i number;
     begin
       i := 0;
       loop
               
         exit when i = 10;
         dbms_output.put_line(i); 
          i := I + 1;
       end loop;  
     end; 

  (b) while循环
         while <条件> loop --当条件不成立的时候退出循环
          
         end loop;

      declare
        i number;
       begin
          i := 6;
          while (i < 10 and i > 5)  loop                 dbms_output.put_line(i);
            i := i + 1;
          end loop;
       end;
   (c) for 循环
       for <循环变量>  in  [reverse] <下界>..<上界> loop
       
       end loop;
   
      declare
         i number;
      begin
        for i in 1..10 loop
           dbms_output.put_line(i);
        end loop;
      end;   

      declare
         i number;
         m number := -5;
         n number; 
      begin
         n := 10;
        for i in m..n loop
           dbms_output.put_line(i);
          
        end loop;
      end;   

    declare
         i number;
         m number := -5;
         n number; 
      begin
         n := 10;
        for i in reverse m..n loop
           dbms_output.put_line(i);
          
        end loop;
      end;   

  if ... else .... 结构
     if <条件> then
    
     elsif <条件> then
    
     elsif <条件> then

        elsif <条件> then
     
      else
      end if;

--给职工涨工资,输入一个员工号,判断员工所在的部门,如果部门          10 涨10%
   20 涨20%
         30 涨30%
如果涨后的工资>5000那么就设为5000
(涨后的工资不能超过5000)
  
   declare
       eno emp.empno%type;
       dno emp.deptno%type;
       v_sal number;
   begin
    --<1>输入员工号
      eno := &员工号;
    /*<2>得到部门号
       */
      select deptno,sal into dno,v_sal from emp
      where empno = eno;
    /*<3>判断*/
       if dno = 10 then
         if v_sal * 1.1 > 5000 then
           update emp set sal = 5000
           where empno = eno;
         else
           update emp set sal = sal * 1.1
       where empno = eno;

         end if;
       elsif dno = 20 then
        if v_sal * 1.2 > 5000 then
         update emp set sal = 5000
         where empno = eno;

        else
         update emp set sal = sal * 1.2
         where empno = eno;

        end if;      

       elsif dno = 30 then
        if v_sal * 1.3 > 5000 then
         update emp set sal = 5000
         where empno = eno;

        else
         update emp set sal = sal * 1.3
         where empno = eno;

        end if;  
       else
         null;   --占位
       end if;
     end;
 

   end;
 

 declare
     eno emp.empno%type;    
   begin
     eno := &员工号;
    
     update emp set sal = least(sal + sal * deptno /100,5000) where empno = eno;
   
     commit;
   end; 

<4> declare
    begin
      --到底能写哪些sql语句
      --直接写的DML TCL
      --间接写的DDL DCL (execute immediate)   
    end; 

    declare
      i number;
     begin
      --所有的DDL语句都不能直接写
      create table test(a number(2));
      --所有的DCL语句都不能直接写
      grant select on emp to mike; 
     end;

    declare
      s1 varchar2(100);
     begin
      --这两类命令可以利用语句execute immediate间接的写
      s1:='create table test1(a number(2))';
      execute immediate s1; 
     end;

    declare
      s2 varchar2(100);
     begin
      --这两类命令可以利用语句execute immediate间接的写
      s2:='drop table test1';
      execute immediate s2; 
     end;

 

<5>异常部分 运行时候的错误是异常
           
   declare
   begin
    exception
     --这部分如何写
   end;

 
 declare
     erow emp%rowtype;
   begin
      select * into erow   from emp
      where empno = 9000;
   exception
     --预定义的异常
     --too_many_rows 关键字(返回多行)
     --no_data_found 关键字(没有返回行)
     when no_data_found then
          dbms_output.put_line('没有返回行');
            
   end;

 declare
     erow emp%rowtype;
   begin
      select * into erow  from emp;

   exception
     --预定义的异常
     --too_many_rows 关键字(返回多行)
     --no_data_found 关键字(没有返回行)
     when too_many_rows then
        dbms_output.put_line('多于1行');
            
   end;

-----简单的异常处理
-- 系统全局变量  sqlcode
                sqlerrm
   declare
     erow emp%rowtype;
   begin
      select * into erow   from emp;

   exception
     when others then
        dbms_output.put_line('错误的编号'||sqlcode);
        dbms_output.put_line('错误的信息'||sqlerrm);
   end;

-- 自定义异常
declare
  eno number(2);
  myexp exception;  --1。定义异常变量
begin
  eno := &部门号;
 
 if  eno > 80 then
  raise myexp;  --2。引发异常
end if;

  insert into dept values (
  eno,'&名字','&地址');
 
  commit;
exception
  when myexp then --3。捕获异常
    dbms_output.put_line('部门号超过80'); 
  when dup_val_on_index then --违反唯一性约束
    dbms_output.put_line('部门号'||eno||'已经存在!!!');
    rollback;
 

end;

 --非预定异常
  declare
     erow emp%rowtype;
     mexp exception;
     pragma exception_init(mexp,-1422);
   begin
      select * into erow   from emp;

   exception
     when mexp then
        dbms_output.put_line('太多行');
   end;
 
--raise 引发异常 (throw java中)

--raise_application_error(-20001,'太多行');
   编号范围 是从-20001  到 -29999
 
  declare
     erow emp%rowtype;
 
   begin
      select * into erow   from emp;

   exception
     when others then
        raise_application_error(-20001,'太多行');
   end;

<游标>  内存中的一块区域,存放的是SQL语句影响的结果
   cursor
     隐式游标  单条的SQL语句 所产生的结果
     显式游标

   1。 隐式游标
     单条sql语句所产生的结果集合
       用关键字SQL表示隐式游标
        4个属性 %rowcount  影响的记录的行数  整数
                %found     影响到了记录 true
                %notfound  没有影响到记录 true
                %isopen    是否打开  布尔值 永远是false

         多条sql语句 隐式游标SQL永远指的是最后一条sql语句的结果
         主要使用在update 和 delete语句上     

   2。 显式游标
      select语句上 使用显式游标
      明确能访问结果集
         for循环游标
         参数游标
           解决多行记录的查询问题
         fetch游标

显示游标
   需要明确定义
   (1)FOR循环游标 (常用的一种游标)

 --<1>定义游标
 --<2>定义游标变量
 --<3>使用for循环来使用这个游标

  --前向游标 只能往一个方向走
  --效率很高

      declare
        --类型定义
        cursor cc is select empno,ename,job,sal
         from emp where job = 'MANAGER';
        --定义一个游标变量
        ccrec cc%rowtype;

      begin
         --for循环
         for ccrec in cc loop
            dbms_output.put_line(ccrec.empno||'-'||ccrec.ename||'-'||ccrec.job||'-'||ccrec.sal);

         end loop;
      
      end;
   (2) fetch游标
     --使用的时候 必须要明确的打开和关闭

      declare
        --类型定义
        cursor cc is select empno,ename,job,sal
         from emp where job = 'MANAGER';
        --定义一个游标变量
        ccrec cc%rowtype;

      begin
        --打开游标
         open cc;
        --loop循环
         loop
            --提取一行数据到ccrec中
            fetch cc into ccrec;        
            --判断是否提取到值，没取到值就退出
            --取到值cc%notfound 是false
            --取不到值cc%notfound 是true
            exit when cc%notfound;
            dbms_output.put_line(ccrec.empno||'-'||ccrec.ename||'-'||ccrec.job||'-'||ccrec.sal);           

         end loop;
        --关闭
         close cc; 
      
      end;
  游标的属性4种
       %notfound  fetch是否提到数据 没有true 提到false
       %found      fetch是否提到数据 有true 没提到false
       %rowcount  已经取出的记录的条数
       %isopen    布尔值 游标是否打开

 declare
        --类型定义
        cursor cc is select empno,ename,job,sal
         from emp where job = 'MANAGER';
        --定义一个游标变量
        ccrec cc%rowtype;
  

      begin
        --打开游标
         open cc;
        --loop循环
         loop
            --提取一行数据到ccrec中
            fetch cc into ccrec;        
            --判断是否提取到值，没取到值就退出
            --取到值cc%notfound 是false
            --取不到值cc%notfound 是true
            exit when (cc%notfound or cc%rowcount =3);

            dbms_output.put_line(cc%rowcount||'-'||ccrec.empno||'-'||ccrec.ename||'-'||ccrec.job||'-'||ccrec.sal);           

         end loop;
        --关闭
         close cc; 
      
      end;

<例子>
  declare
       cursor cc is select dept.dname,
        emp.ename,emp.sal from
         dept,emp where dept.deptno = emp.deptno;
       ccrec cc%rowtype;
  begin
      for ccrec in cc loop
     
       dbms_output.put_line(ccrec.dname||'-'||ccrec.ename||'-'||ccrec.sal);
      end loop;
  

  end; 
   (3)参数游标
 按部门编号的顺序输出部门经理的名字
     declare
       --部门
       cursor c1 is select deptno from dept;
       --参数游标c2,定义参数的时候
       --只能指定类型,不能指定长度 
       --参数只能出现在select语句=号的右侧
       cursor c2(no number,pjob varchar2) is select emp.* from emp
         where deptno = no and job=pjob;

    /*
       no = 10  pjob = 'MANAGER'
          select * from emp where deptno = 10 and job = 'MANAGER';  
     */
       c1rec c1%rowtype;
       c2rec c2%rowtype;
       --定义变量的时候要指定长度
       v_job varchar2(20);
     begin
       --部门
        for c1rec in c1 loop
          --参数在游标中使用
          for c2rec in c2(c1rec.deptno,'MANAGER') loop
            dbms_output.put_line(c1rec.deptno||'-'||c2rec.ename);

          end loop;
        end loop;
     end;
<综合例子>
  求购买的商品包括了顾客"Dennis"所购买商品的顾客（姓名）；**************
   思路:
      Dennis (A,B)

      别的顾客 (A,B,C) (A,C) (B,C)  C
  declare
   --Dennis所购买的商品
   cursor cdennis is select productid
         from purcase where customerid=(
           select customerid from
           customer where name = 'Dennis');
   --除Dennis以外的每个顾客
   cursor ccust is select customerid
        from customer where name <> 'Dennis';
   --每个顾客购买的商品
   cursor cprod(id varchar2) is
     select productid from purcase
          where customerid = id;

   j number ;
   i number;
   c1rec cdennis%rowtype;
   c2rec ccust%rowtype;
   c3rec cprod%rowtype;
   cname varchar2(10);
  begin
    --顾客循环
    for c2rec in ccust loop
     i:=0;
     j:=0;
     for c1rec in cdennis loop
        i := i + 1;
       
      --每个顾客买的东西
       for c3rec in cprod(c2rec.customerid) loop

           if (c3rec.productid = c1rec.productid) then
               j := j + 1;
           end if;

        end loop;  
     
     end loop;
                       
       if (i=j) then
        select name into cname from
           customer where customerid = c2rec.customerid;
        DBMS_output.put_line(cname);       
       end if;

    end loop;

  end;
  (4)引用游标/动态游标
        select语句是动态的
     declare
       --定义一个类型(ref cursor)弱类型   
       type cur is ref cursor;
         --强类型(返回的结果集有要求)
       type cur1 is ref cursor return emp%rowtype;
       --定义一个ref cursor类型的变量  
       cura cur;
       --
       c1rec emp%rowtype;
       c2rec dept%rowtype;
     begin
  DBMS_output.put_line('输出员工')   ;      
       open cura for select * from emp;
       loop
         fetch cura into c1rec;  
         exit when cura%notfound;
         DBMS_output.put_line(c1rec.ename)   ;
       end loop ;
  DBMS_output.put_line('输出部门')   ;
       open cura for select * from dept;
       loop
         fetch cura into c2rec;  
         exit when cura%notfound;
         DBMS_output.put_line(c2rec.dname)   ;

       end loop; 
       close cura;
    end;

=====================================day6=====================================

使用PL/SQL块编程实现，注意必要的异常处理

set serverout on

1.输入一个员工号，输出该员工的姓名、薪金和大概的服务年限（按年月日显示）
declare
 no emp.empno%type; 
 v_enm emp.ename%type;
 v_sal emp.sal%type;
 v_nx varchar2(30);
begin
 no := &员工号;

 select ename,sal,to_char(to_date('00010101','yyyymmdd')+
       (sysdate-hiredate) - 366 -31,'yyyy"年"mm"月"dd"日"') into
        v_enm,v_sal,v_nx
 from emp
 where empno = no;

 dbms_output.put_line('员工姓名:'||v_enm||chr(10)||'工资:'||v_sal||chr(10)||'工作年限:'||v_nx); 
exception
 when others then
   dbms_output.put_line(sqlerrm);
end;

2.接收一个员工号，输出该员工所在部门的名称
declare
 no number;
 dno emp.deptno%type;
 dnm dept.dname%type;
begin
 no := &员工号;
 select deptno into dno from emp where empno = no;
 select dname into dnm from dept where deptno = dno;
 dbms_output.put_line('部门名称是:'||dnm);
exception
 when others then
   dbms_output.put_line(sqlerrm);
end;
 

3.接收一个员工号，如果该员工职位是MANAGER，并且在DALLAS工作那么就给他薪金加15％；
如果该员工职位是CLERK，并且在NEW YORK工作那么就给他薪金扣除5％;其他情况不作处理。

declare
  no emp.empno%type;
  dno dept.deptno%type;
  v_job emp.job%type;
  dnm dept.dname%type;
begin
 
  no := &员工号;
  select deptno,job into dno,v_job from emp
         where empno = no;
  select dname into dnm from dept
       where deptno = dno;

   if v_job = 'MANAGER' and dnm = 'DALLAS' then
     update emp set sal = sal * 1.15 where empno = no;
   elsif v_job = 'CLERK' and dnm = 'NEW YORK' then
     update emp set sal = sal * 0.95 where empno = no;    
   else
     null;
   end if;
exception
 when others then
   dbms_output.put_line(sqlerrm);

end; 

4.接收一个员工号，输出这个员工所在部门的平均工资
   declare
     no  emp.empno%type;
     v_sal number;
   begin
     no := &员工号;

     select avg(sal) into v_sal
        from emp where deptno = (select deptno from emp where empno = no);
     
     dbms_output.put_line('平均工资:'||round(v_sal,2));
    exception
 when others then
   dbms_output.put_line(sqlerrm);
  end;    

5.以交互的方式给部门表插入一条记录，如果出现主键冲突的异常，请显示“部门号已被占用”的字样。
    declare
      erow dept%rowtype;
    begin
      erow.deptno := '&部门号';
      erow.dname := '&部门名';
      erow.loc := '&地址';
     
      insert into dept values (erow.deptno,erow.dname,erow.loc);
    exception
 when others then
   dbms_output.put_line(sqlerrm);
 
    end;
           
  

=====================================day7=====================================

1、建立一个存储过程用来接收一个员工号，返回他的工资和他所在部门的平均工资并作为传出参数传出。
create or replace procedure p1(id number,psal out number,pavgsal out number)
as
begin
  select sal into psal from emp where empno =id;
  select avg(sal) into pavgsal from emp
       where deptno = (select deptno from emp where empno = id);
exception
  when others then
    dbms_output.put_line(sqlerrm);
end;

declare
  s number;
  ps number(7,2);
  id number;
begin
  id := &员工号;
  p1(id,s,ps);
  dbms_output.put_line('员工'||id||chr(10)||'工资'||s||chr(10)||'平均工资'||ps);

end;

2、建立一个存储过程用来接收一个部门号，找出其中的两位最老的员工的员工号，并打印。
create or replace procedure p2(dno number)
as
 cursor c1 is select * from emp where deptno = dno
      order by hiredate;
 c1rec c1%rowtype;

begin
 for c1rec in c1 loop
   dbms_output.put_line('员工号:'||c1rec.empno||chr(9)||'工作时间:'||to_char(c1rec.hiredate,'yyyy/mm/dd'));
   if (c1%rowcount = 2) then
     exit;
   end if;
 end loop;
exception
  when others then
    dbms_output.put_line(sqlerrm);
end;

--调用
execute p2(10);

3、编写一个过程用来传入一个员工号，在emp表中删除一个员工，当该员工是该部门的最后一个员工时
就在dept表中删除该员工所在的部门。

 create or replace procedure p3(eno emp.empno%type)
 as
   dno dept.deptno%type;
   n_count number;
 begin
   select deptno into dno from emp where empno = eno;
   delete from emp where empno = eno;
   select count(*) into n_count from
      emp where deptno = dno;
   if n_count = 0 then
     delete from dept where deptno = dno;
   end if;  
exception
  when others then
    dbms_output.put_line(sqlerrm);
 
 end;

 

  

=====================================day8=====================================

触发器

1.编写一个触发器实现如下功能：
  对修改职工薪金的操作进行合法性检查：
   a) 修改后的薪金要大于修改前的薪金
   b) 工资增量不能超过原工资的10%
   c) 目前没有单位的职工不能涨工资
--update emp set sal = 1000 where empno = 7369;

create or replace trigger tr1
after update of sal on emp
for each row
begin
   if :new.sal <= :old.sal then
       raise_application_error(-20001,'修改后的薪金要大于修改前的薪金');
  elsif :new.sal > :old.sal * 1.1 then
       raise_application_error(-20002,'工资增量不能超过原工资的10%');   
  elsif :old.deptno is null then
       raise_application_error(-20003,'没有单位的职工不能涨工资');          
  end if;
end;

  
2. 在emp表上编写一个触发器，实现如下功能：
  当插入或删除的职工记录属于10号部门时，记录下操作时间,语句的种类(插入/删除),
和涉及的员工号
 
--建立日志表
  create table logtable(
       serial number primary key, --序号
       dmltime date,  --时间
       dmltype varchar2(20), --DML种类
       empno number --员工号
);
 
--建立序列(用于产生logtable的序号)
 create sequence seq1;

--建立触发器
 create or replace trigger tr2
 after insert or delete on emp
 for each row
 when (new.deptno = 10 or old.deptno = 10)  --插入或删除的职工记录属于10号部门时触发 
 --when的条件成立的时候触发
 --对新值和旧值引用的时候不要用:
              
 begin
   if inserting then --插入
      insert into logtable values (seq1.nextval,
                                   sysdate,
                                   'insert语句',
                                   :new.empno);
   elsif deleting then --删除
       insert into logtable values (seq1.nextval,
                                   sysdate,
                                   'delete语句',
                                   :old.empno);         
   end if;
--updating 修改
 end;