oracle学习笔记(续)

第二十八~三十课 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;