oracle 语法简介说明 一

select length('adb') from dual;
select lengthB('a赵文忠db') from dual;

select sysdate from dual;
SELECT NEXT_DAY(sysdate,'星期一') from dual;
select to_char(sysdate,'yyyy-mm-dd hh:mm:ss') from dual;
select to_char(sysdate,'yyyy-mm-dd hh24:mm:ss') from dual;
select to_date('10/12月/2010') from dual;
select to_number('4566') from dual;
select user from dual;
select sum(decode(sex,'男',1,0)),sum(decode(sex,'女',1,0))from psy_t_markinfo;
select nvl(sex,'为空') from psy_t_markinfo;
select * from psys_u_deparment where departmentname like '抑郁_';
select * from psys_u_deparment where departmentname like '抑郁%';
select * from psys_u_deparment where departmentname like '%抑郁';
select markid from psy_t_markinfo;
instersect
select markid from psy_t_markinfo;
instersect---查询两个表都出现的数据
create table tt as select * from psy_t_markinfo
case when then when then else end case;
if then elsif then else end if;
---循环
loop
end loop;
while expression  loop
end loop;
for counter  in [reverse] start_value ..end_value   loop ---counter可以不事先声明
end loop;
---异常
 exception
 when  then

 
 ---常用异常
 dup_val_on_index ----向有唯一约束的表中插入重复行
 no_data_found  ---在一个select into 语句中无返回值（给变量赋值）
 too_many_rows  ---在select into 语句中返回了多行（给变量赋值（多个值））
 value_error        ---一个算法、转换、截断或大小约束发生错误
 zero_divide        ---发生零被除
 
 ---自定义异常
   e exception ;
    if 1=2 then
     raise e;
     end if;
    
    
     ----复合变量
     declare
     TYPE myfirstrecord IS RECORD
     (
     MarkID int,
     UserID int
     );
     real_zhaowenzhong myfirstrecord;---实际声明对象，在SQL语句中引用实际声明的对象名而不是定义的对象名（myfirstrecord）
     select MarkID,UserID INTO real_zhaowenzhong from psy_t_markid where MarkID=3;
    
     ---
     declare firstrecord IS RECORD
     (
        dept int,
        depname psy_u_deparment.DepartmentName%TYPE ---这种方式定义的变量和表中的字段类型及长度一样
     );
     real_wenzhong firstrecord;
     select departmentid,departmentname into real_wenzhong from psy_u_department where departmentid=233;
    ----将表中的所有字段都放到复合变量中，而且字段名、类型和长度都一至
    declare
    myrec psy_u_department%rowtype;
    begin
        select * into myrec from psy_u_departmentid  where departmentid=233;
    end;
   
    ---游标的属性,带参数的游标不用定义参数的具体的长度，只需要指定类型即可
    %found;
    %ISOPEN;
    %NOTFOUND;
    %ROWCOUNT;
 
    ---
    declare
     cursor first_cur is select *  from psy_u_department where departmentid=261;
      myrec psy_u_department%rowtype;
       begin
          open first_cur;
          fetch first_cur into myrec;
           while first_cur %found loop
            .....;
            fetch first_cur into myrec;
            end loop;
            close first_cur;
        end;
---带参数游标
declare
 cursor cur_par(departid int) is select sdepartment into   from psy_u_department where departmentid=departid;
  sdepstr psy_u_department.sdepartment %type;
  begin
        open cur_par('001');
        fetch cur_par into sdepstr;
        exit when cur_par%notfound  [or cur_par%notfound is null];--(cur_par%notfound is null 相当于没有做循环即，没有执行fetch)
        .....;
        end loop;
        close cur_par;
   end;
  
  
   ----带参数的游标，不用打开游标和关闭游标，for循环
   declare
    cursor cur_par(deptid int) is  select sdepartment  from psy_u_department where departmentid=departid;
     begin
        for cur in cur_par('001') loop
        .......;
        [dbms_output.put_line('dsd'||cur_par%rowcount )]--%rowcount 相当于一个游标的计时器
        end loop;
     end;
    
     ---判断游标是否打开
     declare
      cursor cur is select id from t where 1=2;
      id t.id%type;
      begin
        if cur%isopen then
        ..;
        else
            open cur;
        end if;
        fetch cur into id;
        close cur;
      end;
     
      ------利用游标修改数据.在SELECT  from where 后面必须加 for update 否则是不让修改数据的
      declare
       cursor cur is select name from t where 1=2 for update;
        namestr varchar2(10);
        begin
            open cur;
            fetch cur into namestr;
            while cur%found  loop
            update t set name=namestr||'ss' where current of cur;-------在游标中的循环中判断游标的当前行的标准格式；
            fetch cur into namestr;
            end loop;
            close cur;
        end;
     
      -----隐式游标(看不到定义游标、打开游标、关闭游标的过程)
      begin
        for cur in (select name from test where 1=2) loop
            DBMS_OUTPUT.PUT_LINE('隐式游标'||'for 循环'||cur.name);
        end loop;
      end;
     
      --------------过程
      ----同样 过程中的参数只给出类型，不需要给出精度或长度
      create [or replace] procedure procedurename
      (
      parameter1[{in | out | inout}] parameter_type,
      parameter2[{in | out | inout}] parameter_type,
      ............;
      parametern[{in | out | inout}] parameter_type
      )
      is|as
      begin
       procedure_body;
      end [procedurename];
     
      ---如果有错误
      show error;
      show error procedure procedurename;
      show error function functionname;
      show error package packagename;
      ----执行存储过程
      execute procedure;--必须是存储过程无参数或是输入参数，参数是常量
     
      declare id int;
      begin
      id:=1;
      procedurename(id);
      end;
     
      begin
      procedurename(1);
      end;
     
      ---创建视图
      create or replace view viewname
      as
      select * from t where 1=1;
      with read only;--可选 该视图是只读的，不能进行增 、删、改操作。
      with check option;---可选 执行更新、删除、增加数据时候必须满足 where 后面的条件。
      ---对视图的增、删、改 其实就是对视图中基表的增、删、改操作。 但是增、删、改操作 如果视图中有两个或两个以上的
      --基表时，就不能直接操作了，必须利用触发器才能完成
      ----查看视图语法信息和存在哪些视图
      desc dba.views;
      desc user.views;
      select text from user_views where view_name='viewname';
     
      ---同义词的作用：应用程序的开发者在写SQL区间段内避免写表的全称而为表（简写表名）起的易于
      --他们记忆编写，公共同义词简化了应用程序的迁移。如果模式名不同而表相同，同义词允许应用程序从一个数据库
      --迁移到另一个数据库。有的时候公共同义词是为了查找所有数据库的用户都使用的共同的表。另一个作用就是使不同的
      --应用程序用不同的名字指到同一个表
      ----同义词的创建
      create synonym dpt for system.psy_u_department;---私有或专有同义词
      drop synonym dpt;---删除同义词
      create public synonym dpt for system.psy_u_department;---公共同义词
      select * from dpt;
      --查询同义词
      desc user_synonyms;
      desc dba_synonyms;
      -----序列
      create sequence mysqe
      start with 1;--从几开始
      increment by 1 --递增
      order;  ---排序
      nocycle;  --不循环
     
      select mysqe.nextval from dual;---查询序列的下一个值
      select mysqe.currval from dual;---查询当前的序列值
     
      ----应用到表中
      create table testtable (id integer,name char(6))
      insert into testtable values(mysqe.nextval,'文');
     
      desc dba_sequences;
      select sequence_name from dba_sequences ;
       select sequence_name from all_sequences ;
        select sequence_name from user_sequences ;
       
        alter sequence mysqe
        increment 3;--修改递增量

------------事务的ACID特性

原子性 Atomicity                             一致性   Consistency

隔离性 Isolation                              永久性   Durability

-----触发器

     -----隔离等级性
      select * from t for update;---（相当于加锁，实际就是一个加锁的过程）查询完毕后马上要修改，这样别人就修改不了，无限期等待，直到当前释放锁 别人才能修改
                                            --避免查询完后，修改数据时，该数据语句被修改，即避免覆盖别人的数据。
    ---触发器中不允许写commit;rollback                                       
       create or replace trigger testdel--- 行级触发器。在t表上创建触发器，当删除t表上对于的数据后，同时删除tt表中的对应数据。
       alter delete on t
       for each row;
      begin
      delete from tt where id=:old.id;
      end testdel;
     
      create or replace trigger t_del
      alter delete on t
      for each row;
      begin
        if :old.t.id=12 then
            raise_application_error(2,'不允许删除');
        end if;
      end;
      -----序列应用行级触发器
      create or replace trigger squ
      before insert on testtable -----表示在插入前做预处理
      for each row---在每一行上都做预处理
      declare
       rowno int;
       begin
        select mysqe.NextVal into rowno from dual;
        :new.id:=rowno;
        end;
       insert into testtable values (1,'sf');--这里的1 就不起作用了，实际的序号是按序列产生的值填充的。
      ---语句级触发器
      create table wenzhonglog(usercode varchar2(12),happentime date,remarks varchar2(120));
     
      create or replace trigger statment
      alter insert or delete or update on wenzhonglog
      begin
        if inserting then
         insert into wenzhonglog values('system',sysdate,'插入');
        elsif updating then
            insert into wenzhonglog values('system',sysdate,'更新');
        else
            insert into wenzhonglog values('system',sysdate,'删除');
        end if;
      end;
     
      ---------替换触发器(只能创建在视图上（该视图必须具有两个或两个以上的基表）)
      create or replace trigger insteadtri
      instead of insert on viewname
      for each row
      begin
      ..................;
      end;