oracle 语法简介说明一

﹏ヽ暗。殇╰゛Y 2022-09-27 06:36 172阅读 0赞

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;