尚学堂oracle笔记

第一课：客户端
        1. Sql Plus(客户端），命令行直接输入：sqlplus，然后按提示输入用户名，密码。
        2. 从开始程序运行:sqlplus，是图形版的sqlplus.
        3. http://localhost:5560/isqlplus
       
        Toad：管理， PlSql Developer:
第二课：更改用户
        1. sqlplus sys/bjsxt as sysdba
        2. alter user scott account unlock;(解锁)
第三课：table structure
       
        1. 描述某一张表：desc 表名
        2. select * from 表名
第四课：select 语句：
       1.计算数据可以用空表：比如：.select 2*3 from dual
       2.select ename,sal*12 annual_sal from emp;与select ename,sal*12 "annual sal" from emp;区别，加双引号保持原大小写。不加全变大写。
      
       3. select ename || "abcd" 如果连接字符串中含有单引号，用两个单引号代替一个单引号。
第五课：distinct
        select deptno from emp;
        select distinct deptno from emp;
        select distinct deptno from emp;
        select distinct deptno ,job from emp
        去掉deptno,job两者组合的重复。更多的项，就是这么多项的组合的不重复组合。
第六课：Where
        select * from emp where deptno =10;
        select * from emp where deptno <>10;不等于10       
        select * from emp where ename ='bike';
        select ename,sal from emp where sal between 800 and 1500 (>=800 and <=1500)
        空值处理:
        select ename,sal,comm from emp where comm is (not) null;
        select ename,sal,comm from emp where ename ( not)in ('smith','king','abc');
        select ename from emp where ename like '_A%';_代表一个字母,%代表0个或多个字母. 如果查询%
        可用转义字符.\%. 还可以用escape '$'比如:select ename from emp where ename like '%$a%' escape '$';
第七课: orderby
       
         select * from dept;
         select * from dept order by dept desc;(默认:asc)
         select ename,sal,deptno from emp order by deptno asc,ename desc;
第八课: sql function1:   
        select ename,sal*12 annual_sal from emp
        where ename not like '_A%' and sal>800
        order by sal desc;
        select lower(ename) from emp;
        select ename from emp
        where lower(ename) like '_a%';等同于
        select ename from emp where ename like '_a%' or ename like '_A%';
        select substr(ename,2,3) from emp;从第二字符截,一共截三个字符.
        select chr(65) from dual 结果为:A
        select ascii('a') from dual 结果为:65
        select round(23.652,1) from dual; 结果为: 23.7
        select round(23.652,-1) from dual; 20
 
        select to_char(sal,'$99_999_999') from emp;
        select to_char(sal,'L99_999_999') from emp;人民币符号,L:代表本地符号
        这个需要掌握牢:
        select birthdate from emp;
        显示为:
        BIRTHDATE
        ----------------
        17-12月-80
        ----------------
        改为:
        select to_char(birthdate,'YYYY-MM-DD HH:MI:SS') from emp;
       
        显示:
        
        BIRTHDATE
        -------------------
        1980-12-17 12:00:00
        -------------------
       
        select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual; //也可以改为:HH12
        TO_CHAR(SYSDATE,'YY
        -------------------
        2007-02-25 14:46:14
       
        to_date函数:
        select ename,birthdate from emp where birthdate > to_date('1981-2-20 12:34:56','YYYY-MM-DD HH24:MI:SS');
        如果直接写 birthdate>'1981-2-20 12:34:56'会出现格式不匹配,因为表中的格式为: DD-MM月-YY.
 
       
        select sal from emp where sal>888.88 无错.但
        select sal from emp where sal>$1,250,00;
        会出现无效字符错误.
        改为:
        select sal from emp where sal>to_number('$1.250.00','$9,999,99');
       
        把空值改为0
        select ename,sal*12+nvl(comm,0) from emp;
        这样可以防止comm为空时,sal*12相加也为空的情况.
第九课: Group function 组函数
        max,min,avg ,count,sum函数
       
        select to_char(avg(sal),'99999999,99') from emp;
       
       
        select round(avg(sal),2) from emp;
        结果:2073.21
         
        select count(*) from emp where deptno=10;
        select count(ename) from emp where deptno=10; count某个字段,如果这个字段不为空就算一个.
        select count(distinct deptno) from emp;
        select sum(sal) from emp;
第十课: Group by语句
       
        需求:现在想求,求每个部门的平均薪水.
        select avg(sal) from emp group by deptno;
        select deptno avg(sal) from emp group by deptno;
       
        select deptno,job,max(sal) from emp group by deptno,job;
       
       求薪水值最高的人的名字.
       select ename,max(sal) from emp;出错,因为max只有一个值,但等于max值的人可能好几个,不能匹配.
       应如下求:
       select ename from emp where sal=(select max(sal) from emp);
       Group by语句应注意,
       出现在select中的字段,如果没出现在组函数中,必须出现在Group by语句中.
       
   
  第十一课: Having 对分组结果筛选
      
       Where是对单条纪录进行筛选,Having是对分组结果进行筛选.
     
       select avg(sal),deptno from emp
       group by deptno
       having avg(sal)>2000;
      
       查询工资大于1200雇员,按部门编号进行分组,分组后平均薪水大于1500,按工薪倒充排列.
       select * from emp
       where sal>1200
       group by deptno
       having avg(sal)>1500
       order by avg(sal) desc;
       
   第十二课:字查询
      
       谁挣的钱最多(谁:这个人的名字,  钱最多)
      
       select 语句中嵌套select 语句,可以在where,from后.
      
            
       问那些人工资,在平均工资之上.
      
       select ename,sal from emp where sal>(select avg(sal) from emp);
       查找每个部门挣钱最多的那个人的名字.
       select ename ,deptno from emp where sal in(select max(sal) from ename group by deptno) 查询会多值.
       应该如下:
      
       select  max(sal),deptno from emp group by deptno;当成一个表.语句如下:
       select ename, sal from emp join(select  max(sal) max_sal,deptno from emp group
       by deptno) t on(emp.sal=t.max_sal and emp.deptno=t.deptno);
      
       每个部门的平均薪水的等级.
       分析:首先求平均薪水(当成表),把平均薪水和另外一张表连接.
      
      
第十四课:self_table_connection
      
       把某个人的名字以及他的经理人的名字求出来(经理人及这个人在表中同处一行)
      
       分析:首先求出这个人的名字,取他的编号,然后从另一张表与其相对应编号,然后找到经理的名字.
      
       select e1.ename ,e2.ename from emp e1,emp e2 where e1.mgr= e2.empno.
      
       empno编号和MGR都是编号.
第十15课: SQL1999_table_connections   
        
      select ename,dname,grade from emp e,dept d, sqlgrade s
      where e.deptno = d.deptno and e.sql between s.losal and s.hisal and
      job<>'CLERK';
     
      有没有办法把过滤条件和连接条件分开来? 出于这样考虑,Sql1999标准推出来了.有许多人用的还是
      旧的语法,所以得看懂这种语句.
     
     
     
      select ename,dname from emp,dept;(旧标准).
      select ename,dname from emp cross join dept;(1999标准)
      
      select ename,dname from emp,dept where emp.deptno=dept.deptno (旧)
      select ename,dname from emp join dept on(emp.deptno = dept.deptno); 1999标准.没有Where语句.
      select ename,dname from emp join dept using(deptno);等同上句,但不推荐使用.
     
      select ename,grade from emp e join salgrade s on(e.sal between s.losal and s.hisal);
      join 连接语句, on过滤条件。连接，条件一眼分开。如果用Where语句较长时，连接语句和过滤语句混在一起。
     
      三张表连接：
      slect ename,dname, grade from
      emp e join dept d on(e.deptno=d.deptno)
      join salgrade s on(e.sal between s.losal and s.hisal)
      where ename not like '_A%';
      把每张表连接 条件不混在一起，然后数据过滤条件全部区分开来。读起来更清晰，更容易懂一点。
     
      select e1.ename,e2.ename from emp e1 join emp e2 on(e1.mgr = e2.emptno);
      左外连接：会把左边这张表多余数据显示出来。
      select e1.ename,e2,ename from emp e1 left join emp e2 on(e1.mgr =e2.empno);left 后可加outer
      右外连接：
    select ename,dname from emp e right outer join dept d on(e.deptno =d.deptno); outer可以取掉。
       
      即把左边多余数据，也把右边多余数据拿出来，全外连接。
      select ename,dname from emp e full join dept d on(e.deptno =d.deptno);
16-23 课：求部门平均薪水的等级
       A.求部门平均薪水的等级。
       select deptno,avg_sal,grade from
       (select deptno,avg(sal) avg_sal from emp group by deptno)t
       join salgrade s on(t.avg_sal between s.losal and s.hisal)
      
       B.求部门平均的薪水等级
       select deptno,avg(grade) from
       (select deptno,ename, grade from emp join salgrade s on(emp.sal between s.losal and
       s.hisal)) t
       group by deptno
       C.那些人是经理
       select ename from emp where empno in(select mgr from emp);
       select ename from emp where empno in(select distinct mgr from emp);
      
       D.不准用组函数，求薪水的最高值（面试题）
      
       select distinct sal from emp where sal not in(
       select distinct e1.sal from emp e1 join emp e2 on (e1.sal      
       E.平均薪水最高的部门编号
      
       select deptno,avg_sal from
       (select avg(sal)avg_sal,deptno from emp group by deptno)
       where avg_sal=
       (select max(avg_sal)from
       (select avg(sal) avg_sal,deptno from emp group by deptno)
       )
     
       F.平均薪水最高的部门名称
       select dname from dept where deptno=
      (
        select deptno from
        (select avg(sal)avg_sal,deptno from emp group by deptno)
        where avg_sal=
        (select max(avg_sal)from
        (select avg(sal) avg_sal,deptno from emp group by deptno)
        )
       )
     
      G.求平均薪水的等级最低的部门的部门名称
       
        组函数嵌套
        如：平均薪水最高的部门编号，可以E.更简单的方法如下：
        select deptno,avg_sal from
        (select avg(sal) avg_sal,deptno from emp group by deptno)
        where avg_sal =
        (select max(avg(sal)) from emp group by deptno)
       
        组函数最多嵌套两层
       
        分析：
        首先求
        1.平均薪水： select avg(sal) from group by deptno;
        2.平均薪水等级：  把平均薪水当做一张表，需要和另外一张表连接salgrade
        select  deptno,grade avg_sal from
          ( select deptno,avg(sal) avg_sal from emp group by deptno) t
        join salgrade s on(t.avg_sal between s.losal and s.hisal)
       
        上面结果又可当成一张表。
       
        DEPTNO    GRADE    AVG_SAL
      --------  -------  ----------
        30           3   1566.66667
        20           4   2175
        10           4   2916.66667
        3.求上表平均等级最低值
       
        select min(grade) from
        (
          select deptno,grade,avg_sal from
           (select deptno,avg(sal) avg_sal from emp group by deptno)t
          join salgrade s on(t.avg_sal between s.losal and s.hisa)
         )
        4.把最低值对应的2结果的那张表的对应那张表的deptno, 然后把2对应的表和另外一张表做连接。
         
     select dname,t1.deptno,grade,avg_sal from
     (
     select deptno,avg_sal,grade from
       (select deptno,avg(sal) avg_sal from emp group by deptno) t   
     join salgrade s on (t.avg_sal between s.losal and s.hisal)
     ) t1 join dept on (t1.deptno = dept.deptno)
    where t1.grade =
     (
       select min(grade) from
       (
         select deptno,avg_sal,grade from
           (select deptno,avg(sal) avg_sal from emp group by deptno) t   
         join salgrade s on (t.avg_sal between s.losal and s.hisal)
       )
     )
         结果如下：
        
        DNAME    DEPTNO     GRADE    AVG_SAL
      --------  -------  --------   --------
        SALES        30        3    1566.6667
    
        
       H: 视图（视图就是一张表，一个字查询）
       
       G中语句有重复，可以用视图来简化。
       conn sys/bjsxt as sysdba;
       grant create table,create view to scott;
       conn scott/tiger
       创建视图：
       create view v$_dept_avg-sal_info as
       select deptno,grade,avg_sal from
        ( select deptno,avg(sal) avg_sal from emp group by deptno)t
       join salgrade s on 9t.avg_sal between s.losal and s.hisal)
     
       然后
       select * from v$_dept_avg-sal_info
      
       结果如下：
       DEPTNO      GRADE    AVG_SAL
      --------  -------  ----------
        30           3   1566.66667
        20           4   2175
        10           4   2916.66667
       然后G中查询可以简化成：
       select  dname,t1.deptno,grade,avg_sal from
       v$_dept_avg-sal_info t1
       join dept on9t1.deptno =dept.deptno)
       where t1.grade=
       (
 select min(grade) from v$_dept_avg-sal_info t1
       )
求比普通员工最高薪水还要高的经理人的名称
    先求普通员工的最高薪水
    select max(sal) from emp where empno not in
     (select distinct mgr from emp where mgr is not null);
    
    select ename from emp where empno in
     (select distinct mgr from emp where mgr is not null)
    and sal >
     (
     select max(sal) from emp where empno not in
       (select distinct mgr from emp where mgr is not null) 
     ) ;
 

1.  ---面试题：比较效率   

2.         select * from emp where deptno = 10 and ename like '%A%';//好，将过滤力度大的放在前面,且deptno为整数类型,比较时速度快

3.         select * from emp where ename like '%A% and deptno = 10;

第二十四课
    备份与恢复
    DOS环境下:exp导出,imp导入
    --drop user yugang cascade;
    --cd \
    --cd temp
    =-delete *.*
    --exp
    --create user yugang indentified by yugang default tablespace users quota 10M on         users
    --grant create session,create table,create view to yugang
    --imp the data
    第一次输入的用户名密码为:yugang/yugang
    第二次输入的用户名密码为:导出数据的用户的用户名和密码
    --rollback
    create table emp2 as select * from emp;
第二十五课:rownum
    rownum是在Oracle中在表的后面加的一个尾字段,并且只能使用诸如rownum或rownum<=n这样的        形式,不能与>或>=相连接
    1.求薪水值最高的前5条记录.
    select ename,sal from emp order by sal desc where sal <= 5;
    2.求薪水值最高的第6~10条记录.
    select ename,sal from
     (
       select ename,sal,rownum r from
         (select ename,sal from emp order by sal desc)
     )
    where r >=6 and r <= 10;
第二十六课:homework_dml_transaction
    SQL面试题
    有三张表S、C、SC
    S(SNO、SNAME)代表（学号、姓名）
    C(CNO、CNAME、CTEACHER)代表（课号、课名、老师）
    SC(SNO、CNO、SCGRADE)代表（学号、课号、成绩）
    1.求出没选过郭富城老师的所有学生姓名
    2.列出2门以上（含2门）不及格的学生姓名及平均成绩
    3.既学过1号课程又学过2号课程的所有学生姓名
    1.select sname from s join sc on (s.sno = sc.sno) join c on (c.cno = sc.cno)
    where c.cteacher <> '郭富城';
    2.select sname from s where sno in
     (
       select sno from sc where scgrade < 60 group by sno having count(*) >= 2
     )
    3.select sname from s where sno in
     (
       select sno from sc where cno=1 and sno in
         (select distinct sno from sc where cno=2)
     )

 

1.  89、----------------创建新用户---------------   

2.      1、backup scott//备份   

3.          exp//导出   

4.      2、create user   

5.          create user guohailong identified(认证) by guohailong  default tablespace users quota(配额) 10M on users   

6.          grant create session(给它登录到服务器的权限),create table, create view to guohailong   

7.      3、import data   

8.          imp   

9.  90、-----------事务回退语句--------   

10.     rollback;   

11.        

12. 91、-----------事务确认语句--------   

13.     commit;//此时再执行rollback无效

14. 92、当正常断开连接的时候例如exit,事务自动提交。  当非正常断开连接，例如直接关闭dos窗口或关机，事务自动提交

    事务transaction代表一组不可分割的操作，要么全部执行，要么全部不执行，
    transaction起始于一条DML语句，结束于commit;语句，或者是DCL、DDL语句，在事务未提交前    
    可以通过rollback回滚事务，正常退出事务会自动提交，非正常退出事务会自动回滚。
第二十七课：create table
    create table student
    (
    id number(6),
    name varchar2(20),
    sex number(1),
    age number(3),
    sdate date,
    grade number(2) default 1,
    class number(4),
    email varchar2(50)
    );
    
第二十八~三十课 constraint
    not null,unique(当某字段有unique约束时，可以插入空值，空值之间不重复)、主键、外键、        check约束

--------------给name字段加入 非空 约束，并给约束一个名字，若不取，系统默认取一个------------- 
    create table student
    (
    id number(6),
    name varchar2(20) constraint stu_name_nn not null,
    sex number(1),
    age number(3),
    sdate date,
    grade number(2) default 1,
    class number(4),
    email varchar2(50) unique
    )
    /

--------------两个字段的组合不能重复 约束：表级约束-------------   

    行级约束（放在字段后面）与表级约束（加在表后面）：
    create table student
    (
    id number(6),
    name varchar2(20) constraint stu_name_nn not null,
    sex number(1),
    age number(3),
    sdate date,
    grade number(2) default 1,
    class number(4),
    email varchar2(50),
    constraint stu_email_name_uni unique(name,email)
    )
    /
    主键约束
    create table student
    (
    id number(6) primary key,
    name varchar2(20) constraint stu_name_nn not null,
    sex number(1),
    age number(3),
    sdate date,
    grade number(2) default 1,
    class number(4),
    email varchar2(50),
    constraint stu_email_name_uni unique(name,email)
    )
    /
    create table student
    (
    id number(6),
    name varchar2(20) constraint stu_name_nn not null,
    sex number(1),
    age number(3),
    sdate date,
    grade number(2) default 1,
    class number(4),
    email varchar2(50),
    constraint stu_id_pk primary key(id),
    constraint stu_email_name_uni unique(name,email)
    )
    /
    外键约束
    外键约束被参考的字段必须是主键。
    create table class
    (
    id number(4) primary key,
    name varchar2(20) not null
    )
    /
    create table student
    (
    id number(6),
    name varchar2(20) constraint stu_name_nn not null,
    sex number(1),
    age number(3),
    sdate date,
    grade number(2) default 1,
    class number(4) references class(id),
    email varchar2(50),
    constraint stu_id_pk primary key(id),
    constraint stu_email_name_uni unique(name,email)
    )
    /
    create table student
    (
    id number(6),
    name varchar2(20) constraint stu_name_nn not null,
    sex number(1),
    age number(3),
    sdate date,
    grade number(2) default 1,
    class number(4),
    email varchar2(50),
    constraint stu_id_pk primary key(id),
    constraint stu_class_fk foreign key (class) references class(id),
    constraint stu_email_name_uni unique(name,email)
    )
    /
第三十一课：alter table drop table
    alter table student add(addr varchar2(100));
    alter table student drop(addr);
    alter table student modify(addr varchar2(50));
    若addr字段中有的记录长度大于50，则修改不成功。
    alter table student drop constraint stu_class_fk;
    alter table student add constraint stu_class_fk foreign key (class)
    references class (id);
    drop table class;
第32课：Oracle dictionaries
    desc user_tables;
    select table_name from user_tables;
    select view_name from user_views;
    select constraint_name from user_constraints;
    select constraint_name,table_name from user_constraints;
    desc dictionary;
第33课：indexes_and_views
    索引可以提高读数据的效率，但会降低修改、写数据的效率,索引还会占用一定的存储空间
    create index idx_stu_email on student(email);
    drop index idx_stu_email;
    视图会带来一些维护的代价，比如表结构改了，那么视图也得跟着改变。视图还可以用来保
    护私有数据。
    select index_name from user_indexes;
    select view_name from user_views;
    
第34课：sequences and review
    序列可以自动增长，在sql server中有identity，mysql中有auto_increment
    create table article
    (
    id number,
    title varchar2(1024),
    cont long
    )
    /
    如何保证插入数据时id不重复？
    select max(id) from article;这样做也不行，如果有多个线程同时访问数据，则可能会出现
    数据不一致的现象，比方说第一个用户查询出最大id值是100，第二个用户也查询出最大id是
    100，然后第二个用户新插入一条记录：id是101，然后第一个用户也插入了一条记录：id也是
    101
    可以用Oracle的sequence
    create sequence seq_article_id;
    多个表之问可以共用一个序列，但是一般情况下为每个字段分别建立一个序列
    sequence有两个属性CurrVal、NextVal
    select seq_article_id.nextval from dual;
    insert into article values(seq_article_id.nextval,'y','yy');
    insert into article values(seq_article_id.nextval,'x','xx');
    insert into article values(seq_article_id.nextval,'z','zz');
    insert into article values(seq_article_id.nextval,'q','qq');
第35课：三范式
    第一范式：
    1.每个表都要有主键
    2.列不可分，比如：
    create table stu
    (
    id number,
    name varchar2(20),
    age number
    )
    /    
    insert into stu values(1,'yu',21);
    create table stu2
    (
    inf long
    )
    /
    insert into stu2 values('1_yu_21');
    虽然表stu2可以字符串解析后可以达到和表stu一样的效果，但是显然第一种方法更好，查询数    
    据更加方便，而表stu2违反了第一范式的列不可分原则。
    第二范式：
    当有两个以上字段作主键时，非主键字段不能部分依赖于主键字段，如有一个需求，一门老师教        多名学生，一名学生可以选多个老师的课。然后设计了一张表，有以下字段（老师编号、学生编        号、老师姓名、学生姓名等），其中以老师编号和学生编号作联合主键，则些表就存在部分依赖        ，老师姓名部分依赖于老师编号，不满足第二范式，有数据冗余。要解决这个问题可以设计三张        表。
    第三范式：
    不能存在传递依赖，如有一张表有以下字段：（学号、姓名、班级号、班级名称、班级位置），        其中学号为主键，则班级号依赖于学号，每个学生都有相应的班级号，但是班级名称、班级位置        是依赖于班级号，即它们通过班级号传递依赖于学号，不满足第三范式。
第38课：PL_SQL
    set serveroutput on;
    begin
        dbms_output.put_line('Hello World!!');
    end;
    
    declare
        v_name varchar2(20);
    begin
        v_name:='xiaoyu';
        dbms_output.put_line(v_name);
    end;
    declare
        v_num number:=0;
    begin
        v_num:=2/v_num;
        dbms_output.put_line(v_num);
    exception
        when others then
         dbms_output.put_line('error');
    end;
    --变量声明的规则
    1.变量名不能使用保留字
    2.第一个字符必须是字母
    3.变量名最多包含30个字母
    4.不要与数据库或表名同名
    5.每一行只能声明一个变量
    /*
    这是多行注释
    */
    --常用变量类型
        1.binary_integer：整数，主要用来计数而不是用来表示字段类型
    2.number数字类型
    3.char定长字符串
    4.date日期
    5.long长字符串，最长2GG
    6.boolean类型：可以取值true、false、null，默认为null，另外boolean类型值不可直接打印
    --变量声明
    declare
        v_temp number(1);
        v_count binary_integer:=0;
        v_sal number(7,2):=8888.88;
        v_date date:=sysdate;
        v_pi constant number(3,2):=3.14;
        v_valid boolean:=false;
        v_name varchar2(20) not null:='geniusxiaoyu';
    begin
        dbms_output.put_line('v_temp value:' || v_temp);
    end;
    --变量声明：使用%type属性（可用于同步数据库中的字段类型，比如更改了数据库某字段的类型，使用%type属性后，无需再更改PL_sql中的类型声明）
    declare
        v_empno number(4);
        v_empno2 emp.empno%type;
        v_empno3 v_empno2%type;
    begin
        dbms_output.put_line('test for type');
    end;
    --Table变量类型,相当于数组类型
    declare
        type type_table_emp_empno is table of emp.empno%type index by                     binary_integer;
        v_empnos typa_table_emp_empno;
    begin
        v_empnos(0):=7777;
        v_empnos(1):=7778;
        v_empnos(-1):=6666;
        dbms_output.put_line(v_empnos(-1));
    end;
    --Record变量类型，相当于类
    declare
        type type_record_dept is record
        (
         deptno dept.deptno%type,
         dname dept.dname%type,
         loc dept.loc%type
        );
        v_temp type_record_dept;
    begin
        v_temp.deptno:=50;
        v_temp.dname:='yugang';
        v_temp.loc:='beijing';
        dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname || ' ' ||                 v_temp.loc);
    end;
    --使用%rowtype声明record变量
    declare
        v_temp dept%rowtype;
    begin
        v_temp.deptno:=50;
        v_temp.dname:='yugang';
        v_temp.loc:='beijing';
        dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname || ' ' ||                 v_temp.loc);
    end;
    --SQL语句的运用
    --select语句必须和into语句一块使用并且只能返回一条记录
    --sql%rowcount
    declare
        v_name emp.ename%type;
        v_sal emp.sal%type;
    begin
        select ename,sal into v_name,v_sal from emp where empno=7369;
        dbms_output.put_line(v_name || ' ' || v_sal);
    end;

    declare
        v_temp emp%rowtype;
    begin
        select * into v_temp from emp where empno=7369;
        dbms_output.put_line(v_temp.ename || ' ' || v_temp.eno);
    end;

    declare
        v_deptno dept.deptno%type:=50;
        v_dname dept.dname%type:='mm';
        v_loc dept.loc%type:='bj';
    begin
        insert into dept values(v_deptno,v_dname,v_loc);
        commit;
    end;

     declare
        v_deptno dept2.deptno%type:=50;
        v_count number;
    begin   
        --update emp set sal=sal/2 where deptno=v_deptno;
        --select deptno into v_deptno from emp2 where empno=7369;
        select count(*) into v_count from emp2;
        dbms_output.put_line(sql%rowcount || '条记录被影响！');
        commit;
    end;
/

    --DDL语句，在PLSQL中使用DDL语句要加上execute immediate,两个单引号代表一个单引号
    begin
        execute immediate 'create table tt(name varchar2(20) default ''Army'')';
    end;

    declare
        v_sal emp.sal%type;
    begin
        select sal into v_sal from emp where empno=7369;
        if(v_sal<1200) then
            dbms_output.put_line('low');
        elsif(v_sal<2000) then
            dbms_output.put_line('middle');
        else
            dbms_output.put_line('high');
        end if;
    end;
    
    --循环
    declare
     i binary_integer:=1;
    begin
     loop
     dbms_output.put_line(i);
     i:=i+1;
     exit when (i >= 11);
     end loop;
    end; 

    declare
     j binary_integer:=1;
    begin
     while j<11 loop
       dbms_output.put_line(j);
       j:=j+1;
     end loop;
    end;

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

    --错误处理
    declare
     v_temp number;
    begin
     select empno into v_temp from emp where deptno=10;
    exception
     when too_many_rows then
       dbms_output.put_line('太多记录了');
     when others then
       dbms_output.put_line('error');
    end; 

    declare
     v_temp number;
    begin
     select deptno into v_temp from emp where empno=2222;
    exception
     when no_data_found then
       dbms_output.put_line('没数据');
    end; 

    create table errorlog
    (
    id number primary key,
    errcode number,
    errmsg varchar2(1024),
    errdate date
    );
    
    create sequence seq_errorlog_id start with 1 increment by 1;
    declare
     v_deptno dept.deptno%type:=10;
     v_errcode number;
     v_errmsg varchar2(1024);
    begin
     delete from dept where deptno=v_deptno;
     commit;
    exception
     when others then
       rollback;
       v_errcode:=SQLCODE;
       v_errmsg:=SQLERRM;
       insert into errorlog values(seq_errorlog_id.nextVal,v_errcode,v_errmsg,sysdate);
       commit;
    end;
第47~48课：cursor（重点）
    declare
     cursor c is
       select * from emp;
     v_emp c%rowtype;
    begin
     open c;
     fetch c into v_emp;
     dbms_output.put_line(v_emp.ename);
     close c;
    end; 

    --简单循环
    declare
     cursor c is
       select * from emp;
     v_emp c%rowtype;
    begin
     open c;
     loop
       fetch c into v_emp;
       exit when(c%notfound);
       dbms_output.put_line(v_emp.ename);
     end loop;
    end;  

    declare
     cursor c is
       select * from emp;
     v_emp c%rowtype;
    begin
     open c;
     loop
       fetch c into v_emp;
       --下面两行顺序改变后，将会把最后一条记录打印两遍
       dbms_output.put_line(v_emp.ename);
       exit when(c%notfound);
       
     end loop;
    end;  

    --while循环
    declare
     cursor c is
       select * from emp;
     v_emp emp%rowtype;
    begin
     open c;
     fetch c into v_emp;
     while(c%found) loop
       dbms_output.put_line(v_emp.ename);
       fetch c into v_emp;
     end loop;
     close c;
    end; 

    --for循环
    declare
     cursor c is
       select * from emp;
    begin
     open c;
     for v_emp in c loop
       dbms_output.put_line(v_emp.ename);
     end loop;
     close c;
    end; 

    --带参数的游标
    declare
     cursor c(v_deptno emp.deptno%type,v_job emp.job%type) is
       select ename,sal from emp where deptno=v_deptno and job=v_job;
     --v_emp emp%rowtype;
    begin
     for v_emp in c(30,'JAY') loop
       dbms_output.put_line(v_emp.ename);
     end loop;
    end; 

    --可更新的游标
    declare
     cursor c is
       select * from emp for update;
     --v_temp c%rowtype;
    begin
     for v_temp in c loop
       if(v_temp.sal < 2000) then
         update emp set sal=sal*2 where current of c;
       elsif(v_temp.sal=5000) then
         delete from emp where current of c;
       end if;
     end loop;
     commit;
    end;

第49～50课：procedure
    create or replace procedure p
    is
     cursor c is
       select * from emp for update;
    begin
     for v_temp in c loop
       if(v_temp.deptno=10) then
         update emp set sal=sal+10 where current of c;
       elsif(v_temp.deptno=20) then
         update emp set sal=sal+20 where current of c;
       else
         update emp set sal=sal+50 where current of c;
       end if;
     end loop;
     commit;
    end; 

    --可以使用exec p 来执行函数p

 

    --带参数的存储过程
    create or replace procedure p
     (v_a in number,v_b number,v_ret out number,v_temp in out number)
    is
    begin
     if(v_a       v_ret:=v_b;
     else
       v_ret:=v_a;
     end if;
     v_temp:=v_temp+1;
    end; 

    declare
     v_a number:=10;
     v_b number:=20;
     v_ret number;
     v_temp number:=99;
    begin
     p(v_a,v_b,v_ret,v_temp);
     dbms_output.put_line(v_ret);
     dbms_output.put_line(v_temp);
    end;
    /*
    存储过程在创建过程中如果出现错误，仍然会创建，并且不会提示错误在哪儿，可以使用
    show error来查看错误出现在哪儿。
    */
    create or replace function tax_sal
     (v_sal number)
     return number
    is
    begin
     if(v_sal<2000) then
       return 0.10;
     elsif(v_sal<3000) then
       return 0.20;
     else
       return 0.30;
     end if;
    end;
     
    --触发器

     -- for each row 表示每一行的更新都会记录，不加的话，更新多行也只记录一条记录
    create table emp_log
    (
    uname varchar2(20),
    action varchar2(10),
    atime date
    );
    create or replace trigger trig
     after/before insert or update or delete on emp2 for each row
    begin
     if inserting then
       insert into emp_log values(USER,'insert',sysdate);
     elsif updating then
       insert into emp_log values(USER,'update',sysdate);
     else
       insert into emp_log values(USER,'delete',sysdate);
     end if;
    end; 

    update dept set deptno=99 where deptno=10;
    --上面这句违反约束条件

      --所以可先创建下面的触发器（触发器运行于检查约束条件之前） 来同步更新参考了该表的其它表的对应字段

     --注：update语句会产生新的记录NEW和旧记录OLD
    create or replace trigger trig
     after update on dept for each row
    begin
     update emp set deptno=:NEW.deptno where deptno=:OLD.deptno;
    end;

 

 

第51～52课：recursion

create table article
(
id number primary key,
cont varchar2(400),
pid number,
isleaf number(1),--0 代表非子节点，1 代表子节点
alevel number(2)
);
/

insert into article values (1, '蚂蚁大战大象', 0, 0, 0);
insert into article values (2, '大象被打趴下了',1, 0,1);
insert into article values (3, '蚂蚁也不好过', 2, 1, 2);
insert into article values (4, '瞎说', 2, 0, 2);
insert into article values (5, '没有瞎说', 4, 1, 3);

insert into article values (6, '怎么可能',  1, 0, 1);
insert into article values (7, '怎么没有可能', 6, 1, 2);
insert into article values (8, '可能性是很大的', 6, 1, 2);

insert into article values (9, '大象进医院了', 2, 0, 2);
insert into article values (10,'护士是蚂蚁', 9, 1, 3);
commit;

 

/*蚂蚁大战大象
    大象被打趴下了
        蚂蚁也不好过
        瞎说
            没有瞎说
        大象进医院了
            护士是蚂蚁
    怎么可能
        怎么没有可能
        可能性是很大的

*/

--创建递归函数

--调试时如果出现“警告: 创建的过程带有编译错误。”，可使用show error来显示具体的错误信息

create or replace procedure p(v_pid article.id%Type, v_level binary_integer) is
  cursor c is select * from article where pid = v_pid;--得到pid的所有子节点(即所有pid等于v_pid的节点)
  v_preStr varchar2(1024) := '';
begin
  for i in 1..v_level loop
    v_preStr := v_preStr || '****';--因为空格可能被Oracle自动去掉，所以换成星号
  end loop;
  for v_article in c loop
    dbms_output.put_line(v_preStr || v_article.cont );
    p(v_article.id,v_level+1);--显示当前节点的所有子节点
  end loop;
end;

 

execute p(0,1);