Oracle数据库的高级查询、事物、过程及函数

Oracle高级查询、事物、过程及函数
    SQL函数介绍:
        一:SQL函数分类:(1)单行函数:是指输入一行输出一列的函数.
               (2)多行函数:是指输入多行数据输出一个结果,多行函数也称为分组函数.
        二:单行函数
            (1)数值函数:输入和返回的值都是数值型,并且多数函数精确到38位.
                
               函数方法:

　　　　　　　　ABS(N)表示该函数用于返回数字n的绝对值              

 例如:select ABS('-12.3') from dual;
      返回结果:12.3

                    CEIL(N)表示返回大于等于数字n的最小整数.(向上取)                 

   例如:select CEIL('-5.3') from dual;
       返回结果:-5

                    FLOOR(N)表示返回小于等于数字n的最大整数(向下取)                

 例如:select floor('5.3') from dual;
        返回结果:5

                    ROUND(N,[M])该函数用于执行四舍五入运算.     

   例如:select round('12635.714265',3) from dual;
          返回结果:12635.714

                    TRUNC(N,[M])该函数用于截取数字          

  例如:select TRUNC('1245.567',2) from dual;    
         返回结果:1245.56

                    power(n,m)表示取你的次方         

    例如:select power(3,2) from dual;
           返回结果:8

                    sqrt(n)表示n的平方                   

 例如:select sqrt(3) from dual;
        返回结果:9

                    sign(n)表示标示数字n的正负
                     

  例如:select sign(2) from daul;
        返回结果:1,如果n为0则返回0,如果为负数,则返回-1.

　　　　(2)字符函数:输入参数是字符型,返回的是字符型和数值型    
               函数方法:
                   LOWER(CHAR)将字符串转化为小写格式.
                   upper(char)将字符串转化为大写格式.
                   LTRIM(CHAR,[SET]):去掉字符串char左端包含的set中的任何字符.set默认为空格.
                   RTRIM(CHAR,[SET]):去掉字符串char右端包含的set中的任何字符.set默认为空格.
                   REPLACE(CHAR,SEARCH_STRING,[replace_string]):将字符串中的字符替换为指定的字符,search_string表示字符串中的字
符,replace_string表示要替换成的字符串.
                   Substr(char,start,startstring):截取字符串,start表示从字符串char中的第几个起,startstring表示截取几个
                   concat(char1,char2):将字符char1与字符串char2链接在一起.
                   Instr(char,char2):该函数表示查询某个字符串中某个字符的索引,char2表示char字符串中的任意一个字符
            (3)转换函数:将数值从一种数据类型转换为另一种数据类型.
               函数方法:
                   TO_number();将符合特定数字格式的字符串转换成数字值.
                   TO_CHAR(data,[n[,fmt[,nls_param]]]):将日期型转变为字符串,其中fmt用于指定日期格式,nls_param用于指定NLS参数.
                   TO_DATE():将符合特定格式的字符串转变为Date类型的值.
                   NVL(exprl,exprl2):将空值null转变为实际值.exprl为空,返回exprl2,exprl不为空,则返回exprl.
                   NVL2(expl,exprl2,exprl3):用于出来空null,exprl不为空,返回exprl2，exprl为空,则返回exprl3.
            (4)多表查询:
                   分为内外链接,子链接,自连接,联合查询.
                   使用集合操作符:
                    union:返回查询检索到的所有不重复的行(补集).
                    union all:返回检索的所有行,包括重复行(并集).
                    intersect:返回两个查询都检索到的行(交集).
                    minus:返回第一个查询检索到的行减去第二个查询检索的行所剩的行(差集).
            (5)事务处理:用于确保数据库数据的一致性,事务就是一个单元的工作,包括一系列的操作,这些操作要么全部提交成功,要么全部失败.事务确保多个数据的操作作为一个事务单元来处理.
                  提交事务:在事物处理中，用户只需使用COMMMIT语句就可以结束事物,当执行COMMIT语句之后,系统确定事务变化,结束事务，删除保存点,释放锁.                

  例如:update scott.emp set sal=200 where ename='MARY'
         commit;
         select sal from scott.emp ename='MARY'

                   回滚事务:当用户中出现错误时,或是运行程序ide终端用户决定不保存对数据库进行的修改时,就需要进行回滚.                     

  例如:update scott.emp set sal=3000 where ename='MARY'
        ROLLBACK;
        SELECT sal from scotte.emp where ename='MARY'

                    设置保存点:设置保存点是使用SQL命名SAVEPOInt来完成的,也可以使用dbsm_transaction的过程savepoint来设置保存点.
                                    保存点是很有用的事务处理特性,它们可以让用户将单独的大规模事务处理分割成为较小的部分,将它们作为单独的语句进行回滚，事务处理中,其他以前的语句不会受影响.
                   取消事务:为了取消部分事务,用户可以退回到保存点,回退到保存点即可以使用rollback命名.也可以使用包dbms_transaction的过程
rollback_savepoint.            
                  事务的ACID特性:
                        (1)原子性:表示事务处理要么全部进行,要么全部撤销.
                        (2)一致性:表示事务处理要将数据库从一种状态转变为另一种状态.
                        (3)隔离性:表示在事务提交之前,事物处理的效果不能有系统中的其他事务处理看到.
                        (4)持久性:表示一旦提交了事务,它就永远生效(在系统中产生的所有变化将是永久的).

             函数

 --创建函数语法
      create [or replace] function function_name(argument1 [model] datatype1,argument1 [model] datatype1,...)
      return datatype
      is[as]
      PL/SQL BLOCK;
  --创建函数
      create or replace funciton get_user
      return varchar2
      is
      v_user varchar(100);
      begin
      select ename into  v_user from scott.emp where empno='7369';
      return v_user;
      end;
  --使用变量接受函数返回值
     declare
     username varchar(100);
     begin
     username:=get_user;
                    dbms_output.put_line(username);
                    end;
            --可以使用SQL语句直接调用函数,带out和in out参数的函数不能在SQL中被调用
             select get_user from dual;

 

        存储过程

 --创建存储过程
   create [or replace] procedure procedure_name(argument1 [model] datatype1,argument1 [model] datatype1,...)--指定参数数据类型时，不能指定其长度
   is[as]
   PL/SQL BLOCK;
 --调用存储过程
   declare
   begin
   存储过程名/存储过程名(argument);
   end;