Oracle_One

第一课：客户端
        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<e2.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 ,deptno,grade,avg_sal 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.hisal)
             ) t1
            join dept on (t1.deptno = dept.deptno)
            where t1.grade =
            (
              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)
               )
            )
         结果如下：

        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);    
     ) 

第二十四课 
    备份与恢复 
    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<n或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) 
     ) 

    事务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约束 
    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.nextvalue from dual; 
    insert into article values(seq.nextval,'y','yy'); 
    insert into article values(seq.nextval,'x','xx'); 
    insert into article values(seq.nextval,'z','zz'); 
    insert into article values(seq.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属性 
    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 dept.deptno%type:=50; 
        v_count number; 
    begin    
        --update emp set sal:=sal/2 where deptno=v_deptno; 
        --select deptno into v_deptno from emp where deptno=7369; 
        select count(*) into v_count from emp; 
        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 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 errlog 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; 

    --带参数的存储过程 
    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_b) then 
       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; 

    --触发器 
    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; 
    --违反约束条件 
    create or replace trigger trig 
     after update on dept for each row 
    begin 
     update emp set deptno=:NEW.deptno where deptno=:OLD.deptno; 
    end;