关闭

Orcale学习问题

753人阅读 评论(0) 收藏 举报
1.     NET访问ORACLE数据库
   命名空间System.Data.OracleClient 
  System.Data.OracleClient中访问Oracle数据库的连接串是:
User ID=用户名; Password=密码; Data Source=服务名
 
System.Data.OleDb 中的访问 Oracle 数据库的连接串是:
Provider=MSDAORA.1; User ID=用户名; Password=密码; Data Source=服务名
2.     Oracle 中的数据类型
 Oracle 的数据类型和 SQL Server 相比,SQL Server 的大多数据类型很容易找到 .NET 中比较接近的类型,Oracle 中的类型就离 .NET 类型远了许多。
 
  number: 数字类型,一般是 Number(M,N),M是有效数字,N是小数点后的位数(默认0),这个是按十进制说的。 
nvarchar2: 可变长字符型(Unicode),这个比较像 SQL Server 的 nvarchar.
nchar: 定长字符型(Unicode)。 
nclob: "写作文"的字段,存储大量字符(Unicode)时用。 
date: 日期类型,比较接近 SQL Server 的 datetime。
Oracle 中字段不能是 bit 或者 bool 之类的类型,一般是 number(1) 代替的。
 
和 SQL Server 一样在 SQL 命令中,字符类型需要用单引号(')隔开,两个单引号('')是单引号的字符转义
比较特殊的是日期类型:比如要写入 2004-7-20 15:20:07 这个时刻需要如下写:

UPDATE ... SET ... = TIMESTAMP '2004-7-20 15:20:07' ...

注意这里使用了 TIMESTAMP 关键字,并使用单引号隔开;另外请注意日期格式,上面的格式是可识别的,Oracle 识别的格式没有 SQL Server 那般多。这是和 SQL Server 不同的地方。

3.访问 Oracle 过程/函数

SQL Server 作程序时经常使用存储过程,Oracle 里也可以使用过程,还可以使用函数。Oracle 的过程似乎是不能有返回值的,有返回值的就是函数了(SQL Server 存储过程是可以有返回值的)。

.NET 访问 Oracle 过程/函数的方法很类似于 SQL Server,例如:

OracleParameter[] parameters = {
    new OracleParameter("ReturnValue", OracleType.Int32, 0, ParameterDirection.ReturnValue, true, 0, 0, "", DataRowVersion.Default, Convert.DBNull )
    new OracleParameter("参数1", OracleType.NVarChar, 10),
    new OracleParameter("参数2",  OracleType.DateTime),
    new OracleParameter("参数3",  OracleType.Number, 1)
 };

parameters[1].Value = "test";
parameters[2].Value = DateTime.Now;
parameters[3].Value = 1;                        // 也可以是 new OracleNumber(1);

OracleConnection connection = new OracleConnection( ConnectionString );
OracleCommand command = new OracleCommand("函数/过程名", connection);
command.CommandType = CommandType.StoredProcedure;

foreach(OracleParameter parameter in parameters)
     command.Parameters.Add( parameter );

connection.Open();
command.ExecuteNonQuery();
int returnValue = parameters[0].Value; //接收函数返回值
connection.Close();

Parameter 的 DbType 设定请参见 System.Data.OracleClient.OracleType 枚举的文档,比如:Oracle 数据库中 Number 类型的参数的值可以用 .NET decimal 或 System.Data.OracleClient.OracleNumber 类型指定; Integer 类型的参数的值可以用 .NET int 或 OracleNumber 类型指定。等等。

上面例子中已经看到函数返回值是用名为"ReturnValue"的参数指定的,该参数为 ParameterDirection.ReturnValue 的参数。

不返回记录集(没有 SELECT 输出)的过程/函数,调用起来和 SQL Server 较为类似。但如果想通过过程/函数返回记录集,在 Oracle 中就比较麻烦一些了。

在 SQL Server 中,如下的存储过程:

CREATE PROCEDURE GetCategoryBooks
(
    @CategoryID int
)
AS
SELECT * FROM Books
WHERE CategoryID = @CategoryID
GO

在 Oracle 中,请按以下步骤操作:

(1)创建一个包,含有一个游标类型:(一个数据库中只需作一次)

CREATE OR REPLACE PACKAGE Test
  AS
       TYPE Test_CURSOR IS REF CURSOR;
END Test;

(2)过程:

CREATE OR REPLACE PROCEDURE GetCategoryBooks
(
     p_CURSOR out Test.Test_CURSOR,    -- 这里是上面包中的类型,输出参数
     p_CatogoryID INTEGER
)
AS
BEGIN
     OPEN p_CURSOR FOR
           SELECT * FROM Books
           WHERE CategoryID=p_CatogoryID;
END GetCategoryBooks;

(3).NET 程序中:

OracleParameters parameters = {
     new OracleParameter("p_CURSOR", OracleType.CURSOR, 2000, ParameterDirection.Output, true, 0, 0, "", DataRowVersion.Default, Convert.DBNull),
     new OracleParameter("p_CatogoryID", OracleType.Int32)
};

parameters[1].Value = 22;

OracleConnection connection = new OracleConnection( ConnectionString );
OracleCommand command = new OracleCommand("GetCategoryBooks", connection);
command.CommandType = CommandType.StoredProcedure;

foreach(OracleParameter parameter in parameters)
     command.Parameters.Add( parameter );

connection.Open();
OracleDataReader dr = command.ExecuteReader();

while(dr.Read())
{
    // 你的具体操作。这个就不需要我教吧?
}
connection.Close();

另外有一点需要指出的是,如果使用 DataReader 取得了一个记录集,那么在 DataReader 关闭之前,程序无法访问输出参数和返回值的数据。
 
Oracle方面
(1)创建Oracle过程存储
create or replace procedure proce_test(paramin in varchar2,paramout out varchar2,paraminout in out varchar2)
as
  varparam varchar2(28);
begin
  varparam:=paramin;
  paramout:=varparam|| paraminout;  
end;
(2)测试过程存储
declare
  param_out varchar2(28);
  param_inout varchar2(28);
begin
  param_inout:='ff';   
  proce_test('dd',param_out,param_inout);   
  dbms_output.put_line(param_out);
end;

C#方面
引用Oracle组件 
using System;
using System.Data;
using System.Data.OracleClient;    

namespace WebApplication4
{
     public class OraOprater
     {
         private OracleConnection conn=null;
         private OracleCommand cmd=null;
         public OraOprater()
         {
              string mConn="data source=ora9i.ora.com;user id=ora;password=ora";  //连接数据库
              conn=new OracleConnection(mConn);
              try
              {
                   conn.Open();
                   cmd=new OracleCommand();
                   cmd.Connection=conn;
              }
              catch(Exception e)
              {
                   throw e;
              }
         }

         public string SpExeFor(string m_A,string m_B)
         {
                //存储过程的参数声明
              OracleParameter[] parameters={
                                 new OracleParameter("paramin",OracleType.VarChar,20),
                                new OracleParameter("paramout",OracleType.VarChar,20),
                                 new OracleParameter("paraminout",OracleType.VarChar,20)
                                                };
              parameters[0].Value=m_A;
              parameters[2].Value=m_B;
              parameters[0].Direction=ParameterDirection.Input;
              parameters[1].Direction=ParameterDirection.Output;
              parameters[2].Direction=ParameterDirection.InputOutput;
              try
              {
                   RunProcedure("proce_test",parameters);
                  return parameters[1].Value.ToString();
              }
              catch(Exception e)
              {
                   throw e;
              }
         }

         private void RunProcedure(string storedProcName,OracleParameter[] parameters)
         {
              cmd.CommandText=storedProcName;//声明存储过程名
              cmd.CommandType=CommandType.StoredProcedure;
              foreach(OracleParameter parameter in parameters)
              {
                   cmd.Parameters.Add(parameter);
              }
              cmd.ExecuteNonQuery();//执行存储过程
         }
     }
}
测试结果:ddff
  
/***********************************************************************
 * Module:  OraHelper.cs http://csharp.xdowns.com
 * Author:  hellopj
 * Purpose: Definition of the Class OraHelper
 ***********************************************************************/

using System;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
using System.Collections;
using System.Configuration;
namespace OraHelper
{
 public abstract class OraHelper 
 {
   public static readonly string CONN_STRING_NON_DTC = ConfigurationSettings.AppSettings["ConnStr"].Trim();
  public static OracleConnection conn = new OracleConnection(CONN_STRING_NON_DTC);

  public static void OpenConnection()
  {
   if (conn.State != ConnectionState.Open)
    conn.Open();
  }
  public static void CloseConnection()
  {
   if (conn.State == ConnectionState.Open)
    conn.Close();
  }
  public static DataSet getDataSet(string cmdText) 
  {
   
   OracleDataAdapter Adapter = new OracleDataAdapter(cmdText,conn);
   DataSet Data = new DataSet();
   Adapter.Fill(Data);
   return Data;
  }
  public static OracleDataReader ExecuteReader(string cmdText) 
  {
   
   OracleCommand cmd = new OracleCommand();
   cmd.Connection=conn;
   cmd.CommandText=cmdText;
   OracleDataReader rdr = cmd.ExecuteReader();
   cmd.Parameters.Clear();
   return rdr;
  }
  public static int ExecuteNonQuery(string cmdText) 
  {
   try
   {
    OracleCommand comm=new OracleCommand(cmdText,conn);
    comm.ExecuteNonQuery();
    return 1;
   }
   catch
   {
    return 0;
   }
  }
  public static void ExecuteNonQuery(string cmdText,OracleConnection conn) 
  {
   try
   {
    OracleCommand comm=new OracleCommand(cmdText,conn);
    comm.ExecuteNonQuery();
   }
   catch
   {
    
   }
  }
  public static string ExecuteScalar(string cmdText) 
  {
   string status="";
   try
   {
    OracleCommand comm=new OracleCommand(cmdText,conn);
    status=comm.ExecuteScalar().ToString();
    return status;
   }
   catch(Exception err)
   {
    //return "-1";
    return err.Message.ToString();
    
   }
  }
  private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
  public static int ExecuteNonQuery(CommandType cmdType, string cmdText, params OracleParameter[] cmdParms) 
  {
   OracleCommand cmd = new OracleCommand();
   cmd.Connection = conn;
   cmd.Parameters.Clear();
   cmd.CommandText=cmdText;
   foreach (OracleParameter parm in cmdParms)
    cmd.Parameters.Add(parm);
   int val=cmd.ExecuteNonQuery();
   cmd.Parameters.Clear();
   return val;
  }

  public static int ExecuteNonQuery(OracleTransaction trans, CommandType cmdType, string cmdText, params OracleParameter[] cmdParms) 
  {
   OracleCommand cmd = new OracleCommand();
   PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
   int val = cmd.ExecuteNonQuery();
   cmd.Parameters.Clear();
   return val;
  }

   
  public static OracleDataReader ExecuteReader(CommandType cmdType, string cmdText, params OracleParameter[] cmdParms) 
  {
   
   OracleCommand cmd = new OracleCommand();
   try 
   {
    PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
    OracleDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
    cmd.Parameters.Clear();
    return rdr;
   
   }
   catch (Exception e) 
   {
    conn.Close();
    throw e;
   }  
  }
  
  
  public static object ExecuteScalar(string connString, CommandType cmdType, string cmdText, params OracleParameter[] cmdParms) 
  {
   OracleCommand cmd = new OracleCommand();
   PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
   object val = cmd.ExecuteScalar();
   cmd.Parameters.Clear();
   return val;
  }

   
  public static object ExecuteScalar(CommandType cmdType, string cmdText, params OracleParameter[] cmdParms) 
  {
   
   OracleCommand cmd = new OracleCommand();
   PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
   object val = cmd.ExecuteScalar();
   cmd.Parameters.Clear();
   return val;
  }
  
  public static int getScalar(CommandType cmdType, string cmdText, params OracleParameter[] cmdParms) 
  {
   
   OracleCommand cmd = new OracleCommand();
   PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
   object val = cmd.ExecuteScalar();
   cmd.Parameters.Clear();
   int nVal  = Convert.ToInt32(val);
   return nVal;
   
  }

  
  
  public static void CacheParameters(string cacheKey, params OracleParameter[] cmdParms) 
  {
   parmCache[cacheKey] = cmdParms;
  }

   
  public static OracleParameter[] GetCachedParameters(string cacheKey) 
  {
   OracleParameter[] cachedParms = (OracleParameter[])parmCache[cacheKey];
   
   if (cachedParms == null)
    return null;
   
    
   OracleParameter[] clonedParms = new OracleParameter[cachedParms.Length];

    
   for (int i = 0, j = cachedParms.Length; i < j; i++)
    clonedParms[i] = (OracleParameter)((ICloneable)cachedParms[i]).Clone();

   return clonedParms;
  }

   
  private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, CommandType cmdType, string cmdText, OracleParameter[] cmdParms) 
  {
   
   if (conn.State != ConnectionState.Open)
    conn.Open();
   cmd.Connection = conn;
   cmd.CommandText = cmdText;
   cmd.CommandType = cmdType;
   //if (trans != null)
   //cmd.Transaction = trans;
   if (cmdParms != null) 
   {
    foreach (OracleParameter parm in cmdParms)
     cmd.Parameters.Add(parm);
   }
  }

  public static int GetID(string SeqName) //得到序列的nextval
  {
   OracleCommand cmd = new OracleCommand();
   using (OracleConnection conn = new OracleConnection(CONN_STRING_NON_DTC)) 
   {
    string SqlText="select "+SeqName+".nextval from dual";
    
    PrepareCommand(cmd, conn, null, CommandType.Text,SqlText,null);
    int val = Convert.ToInt32(cmd.ExecuteScalar());
    cmd.Parameters.Clear();
    return val;
   }
  }
   }
}
 
6.SQL与ORACLE函数比较
绝对值
  S:select abs(-1) value
  O:select abs(-1) value from dual

  取整()
  S:select ceiling(-1.001) value
  O:select ceil(-1.001) value from dual

  取整(小)
  S:select floor(-1.001) value
  O:select floor(-1.001) value from dual

  取整(截取)
  S:select cast(-1.002 as int) value
  O:select trunc(-1.002) value from dual

  四舍五入
  S:select round(1.23456,4) value 1.23460
  O:select round(1.23456,4) value from dual 1.2346

  e为底的幂
  S:select Exp(1) value 2.7182818284590451
  O:select Exp(1) value from dual 2.71828182

  取e为底的对数
  S:select log(2.7182818284590451) value 1
  O:select ln(2.7182818284590451) value from dual; 1

  取10为底对数
  S:select log10(10) value 1
  O:select log(10,10) value from dual; 1

  取平方
  S:select SQUARE(4) value 16
  O:select power(4,2) value from dual 16

  取平方根
  S:select SQRT(4) value 2
  O:select SQRT(4) value from dual 2

  求任意数为底的幂
  S:select power(3,4) value 81
  O:select power(3,4) value from dual 81

  取随机数
  S:select rand() value
  O:select sys.dbms_random.value(0,1) value from dual;

  取符号
  S:select sign(-8) value -1
  O:select sign(-8) value from dual -1

----------
数学函数

  圆周率
  S:SELECT PI() value 3.1415926535897931
  O:不知道

  sin,cos,tan 参数都以弧度为单位
  例如:select sin(PI()/2) value 得到1SQLServer

  Asin,Acos,Atan,Atan2 返回弧度

  弧度角度互换(SQLServerOracle不知道)
  DEGREES:弧度-〉角度
  RADIANS:角度-〉弧度

  ---------数值间比较

  求集合最大值
  S:select max(value) value from
  (select 1 value
  union
  select -2 value
  union
  select 4 value
  union
  select 3 value)a

  O:select greatest(1,-2,4,3) value from dual

  求集合最小值
  S:select min(value) value from
  (select 1 value
  union
  select -2 value
  union
  select 4 value
  union
  select 3 value)a

  O:select least(1,-2,4,3) value from dual

  如何处理null(F2中的null10代替)
  S:select F1,IsNull(F2,10) value from Tbl
  O:select F1,nvl(F2,10) value from Tbl

  --------数值间比较

  求字符序号
  S:select ascii('a') value
  O:select ascii('a') value from dual

  从序号求字符
  S:select char(97) value
  O:select chr(97) value from dual

  连接
  S:select '11'+'22'+'33' value
  O:select CONCAT('11','22')||33 value from dual

   
子串位置 --返回3
  S:select CHARINDEX('s','sdsq',2) value
  O:select INSTR('sdsq','s',2) value from dual

  模糊子串的位置 --返回2,参数去掉中间%则返回7
  S:select patindex('%d%q%','sdsfasdqe') value
  O:oracle没发现,但是instr可以通过第四个参数控制出现次数
  select INSTR('sdsfasdqe','sd',1,2) value from dual 返回6

  求子串
  S:select substring('abcd',2,2) value
  O:select substr('abcd',2,2) value from dual

  子串代替返回aijklmnef
  S:SELECT STUFF('abcdef', 2, 3, 'ijklmn') value
  O:SELECT Replace('abcdef', 'bcd', 'ijklmn') value from dual

  子串全部替换
  S:没发现
  O:select Translate('fasdbfasegas','fa','' ) value from dual

  长度
  S:len,datalength
  O:length

  大小写转换 lower,upper

  单词首字母大写
  S:没发现
  O:select INITCAP('abcd dsaf df') value from dual

  左补空格(LPAD的第一个参数为空格则同space函数)
  S:select space(10)+'abcd' value
  O:select LPAD('abcd',14) value from dual

  右补空格(RPAD的第一个参数为空格则同space函数)
  S:select 'abcd'+space(10) value
  O:select RPAD('abcd',14) value from dual

  删除空格
  S:ltrim,rtrim
  O:ltrim,rtrim,trim

  重复字符串
  S:select REPLICATE('abcd',2) value
  O:没发现

  发音相似性比较(这两个单词返回值一样,发音相同)
  S:SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe')
  O:SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe') from dual
  SQLServer中用SELECT DIFFERENCE('Smithers', 'Smythers') 比较soundex的差
  返回0-44为同音,1最高

  --------------日期函数

  系统时间
  S:select getdate() value
  O:select sysdate value from dual

  前后几日
  直接与整数相加减

  求日期
  S:select convert(char(10),getdate(),20) value
  O:select trunc(sysdate) value from dual
  select to_char(sysdate,'yyyy-mm-dd') value from dual

  求时间
  S:select convert(char(8),getdate(),108) value
  O:select to_char(sysdate,'hh24:mm:ss') value from dual

   
取日期时间的其他部分
  S:DATEPART DATENAME 函数(第一个参数决定)
  O:to_char函数第二个参数决定

  参数---------------------------------下表需要补充
  year yy, yyyy
  quarter qq, q (季度)
  month mm, m (m O无效)
  dayofyear dy, y (O表星期)
  day dd, d (d O无效)
  week wk, ww (wk O无效)
  weekday dw (O不清楚)
  Hour hh,hh12,hh24 (hh12,hh24 S无效)
  minute mi, n (n O无效)
  second ss, s (s O无效)
  millisecond ms (O无效)
  ----------------------------------------------

  当月最后一天
  S:不知道
  O:select LAST_DAY(sysdate) value from dual

  本星期的某一天(比如星期日)
  S:不知道
  O:SELECT Next_day(sysdate,7) vaule FROM DUAL;

  字符串转时间
  S:可以直接转或者select cast('2004-09-08'as datetime) value
  O:SELECT To_date('2004-01-05 22:09:38','yyyy-mm-dd hh24-mi-ss') vaule FROM DUAL;

  求两日期某一部分的差(比如秒)
  S:select datediff(ss,getdate(),getdate()+12.3) value
  O:直接用两个日期相减(比如d1-d2=12.3
  SELECT (d1-d2)*24*60*60 vaule FROM DUAL;

  根据差值求新的日期(比如分钟)
  S:select dateadd(mi,8,getdate()) value
  O:SELECT sysdate+8/60/24 vaule FROM DUAL;
三:高总结
一、有关表的操作
   1)建表
   create table test as select * from dept;     --从已知表复制数据和结构
   create table test as select * from dept where 1=2;     --从已知表复制结构但不包括数据
   2)插入数据:
   insert into test select * from dept;
二、运算符
 
   算术运算符:+ - * / 可以在select 语句中使用
   连接运算符:||     select deptno|| dname from dept;                    
   比较运算符:> >=   = != <   <=    like   between   is null    in
   逻辑运算符:not   and   or 
   集合运算符:      intersect ,union, union all,   minus 
     要求:对应集合的列数和数据类型相同
           查询中不能包含long 列
           列的标签是第一个集合的标签
           使用order by时,必须使用位置序号,不能使用列名
  例:集合运算符的使用:
      intersect ,union, union all,   minus 
    select * from emp intersect select * from emp where deptno=10 ;
    select * from emp minus select * from emp where deptno=10;
    select * from emp where deptno=10 union select * from  emp where deptno in (10,20);      --不包括重复行
    select * from emp where deptno=10 union all select * from emp where deptno in (10,20); --包括重复行
三,常用 ORACLE 函数
          sysdate为系统日期     dual为虚表
一)日期函数[重点掌握前四个日期函数]
1,add_months[返回日期加(减)指定月份后(前)的日期]
select sysdate S1,add_months(sysdate,10) S2,
                    add_months(sysdate,5) S3 from dual;
2,last_day [返回该月最后一天的日期]
   select last_day(sysdate) from dual;
3,months_between[返回日期之间的月份数]
   select sysdate S1, months_between('1-4月-04',sysdate) S2,
                     months_between('1-4月-04','1-2月-04') S3 from dual
4,next_day(d,day): 返回下个星期的日期,day为1-7或星期日-星期六,1表示星期日   
    select sysdate S1,next_day(sysdate,1) S2,
                      next_day(sysdate,'星期日') S3 FROM DUAL  
5,round[舍入到最接近的日期](day:舍入到最接近的星期日)
   select      sysdate S1,
        round(sysdate) S2 ,
        round(sysdate,'year') YEAR,
        round(sysdate,'month') MONTH ,
        round(sysdate,'day')   DAY from dual
6,trunc[截断到最接近的日期]
   select      sysdate S1,
        trunc(sysdate) S2,
        trunc(sysdate,'year') YEAR,
        trunc(sysdate,'month') MONTH ,
        trunc(sysdate,'day')   DAY from dual
7,返回日期列表中最晚日期
   select greatest('01-1月-04','04-1月-04','10-2月-04') from dual
二)字符函数(可用于字面字符或数据库列)
   1,字符串截取
   select substr('abcdef',1,3) from dual
   2,查找子串位置
   select instr('abcfdgfdhd','fd') from dual
   3,字符串连接
   select 'HELLO'||'hello world' from dual;
   4, 1)去掉字符串中的空格
        select ltrim('   abc') s1,
               rtrim('zhang   ') s2,
               trim('   zhang    ') s3 from dual
      2)去掉前导和后缀
        select trim(leading 9 from 9998767999) s1,
               trim(trailing 9 from 9998767999) s2,
               trim(9 from 9998767999) s3 from dual;
 
   5,返回字符串首字母的Ascii值
     select ascii('a') from dual
   6,返回ascii值对应的字母
     select chr(97) from dual
   7,计算字符串长度
     select length('abcdef') from dual
   8,initcap(首字母变大写) ,lower(变小写),upper(变大写)
     select lower('ABC') s1,
             upper('def') s2,
             initcap('efg') s3 from dual;
   9,Replace
     select replace('abc','b','xy') from dual;
   10,translate
     select translate('abc','b','xx') from dual;      -- x是1位
   11,lpad [左添充] rpad [右填充](用于控制输出格式)
     select lpad('func',15,'=') s1, rpad('func',15,'-') s2 from dual;
     select lpad(dname,14,'=') from dept;
   12, decode[实现if ..then 逻辑]
   select deptno,decode(deptno,10,'1',20,'2',30,'3','其他') from dept;
三)数字函数
   1,取整函数(ceil 向上取整,floor 向下取整)
    select ceil(66.6) N1,floor(66.6) N2 from dual;
   2, 取幂(power) 和 求平方根(sqrt)
    select power(3,2) N1,sqrt(9) N2 from dual;
   3,求余
    select mod(9,5) from dual;
   4,返回固定小数位数 (round:四舍五入,trunc:直接截断)
    select round(66.667,2) N1,trunc(66.667,2) N2 from dual; 
   5,返回值的符号(正数返回为1,负数为-1)
    select sign(-32),sign(293) from dual;
四)转换函数
  1,to_char()[将日期和数字类型转换成字符类型]
      1)   select to_char(sysdate) s1,
                  to_char(sysdate,'yyyy-mm-dd') s2,
                  to_char(sysdate,'yyyy') s3,
                  to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') s4,
                  to_char(sysdate, 'hh24:mi:ss') s5,
                  to_char(sysdate,'DAY') s6   from dual;
       
      2)   select sal,to_char(sal,'$99999') n1,to_char(sal,'$99,999') n2 from emp
 2, to_date()[将字符类型转换为日期类型]     
      insert into emp(empno,hiredate) values(8000,to_date('2004-10-10','yyyy-mm-dd'));
 3, to_number() 转换为数字类型      
       select to_number(to_char(sysdate,'hh12')) from dual; //以数字显示的小时数
     
五)其他函数
      user:
        返回登录的用户名称  
        select user from dual;
      vsize:
        返回表达式所需的字节数
        select vsize('HELLO') from dual;
      nvl(ex1,ex2):      
         ex1值为空则返回ex2,否则返回该值本身ex1(常用)  
           例:如果雇员没有佣金,将显示0,否则显示佣金
           select comm,nvl(comm,0) from emp;
      nullif(ex1,ex2): 
        值相等返空,否则返回第一个值
           例:如果工资和佣金相等,则显示空,否则显示工资
           select nullif(sal,comm),sal,comm from emp;
      coalesce:  
        返回列表中第一个非空表达式
        select comm,sal,coalesce(comm,sal,sal*10) from emp;
   nvl2(ex1,ex2,ex3) :
        如果ex1不为空,显示ex2,否则显示ex3
          如:查看有佣金的雇员姓名以及他们的佣金     
     select nvl2(comm,ename,') as HaveCommName,comm from emp;
六)分组函数
     max   min   avg count   sum
    1,整个结果集是一个组
     1) 求部门30 的最高工资,最低工资,平均工资,总人数,有工作的人数,工种数量及工资总和
        select max(ename),max(sal),
               min(ename),min(sal),
               avg(sal),
               count(*) ,count(job),count(distinct(job)) ,
               sum(sal) from emp   where deptno=30;
    2, 带group by 和 having 的分组
      1)按部门分组求最高工资,最低工资,总人数,有工作的人数,工种数量及工资总和
        select deptno, max(ename),max(sal),
                       min(ename),min(sal),
                       avg(sal),
                       count(*) ,count(job),count(distinct(job)) ,
                       sum(sal) from emp   group by deptno;
      2)部门30的最高工资,最低工资,总人数,有工作的人数,工种数量及工资总和       
        select deptno, max(ename),max(sal),
                       min(ename),min(sal),
                       avg(sal),
                       count(*) ,count(job),count(distinct(job)) ,
                       sum(sal) from emp   group by deptno having deptno=30;
    3, stddev     返回一组值的标准偏差
          select deptno,stddev(sal) from emp group by deptno;
       variance   返回一组值的方差差
          select deptno,variance(sal) from emp   group by deptno;
   4, 带有rollup和cube操作符的Group By
          rollup 按分组的第一个列进行统计和最后的小计
          cube   按分组的所有列的进行统计和最后的小计
        select deptno,job ,sum(sal) from emp group by deptno,job;
        select deptno,job ,sum(sal) from emp group by rollup(deptno,job);   
             
         cube 产生组内所有列的统计和最后的小计
        select deptno,job ,sum(sal) from emp group by cube(deptno,job);
四、临时表
    只在会话期间或在事务处理期间存在的表.
    临时表在插入数据时,动态分配空间   
    create global temporary table temp_dept
    (dno number,
    dname varchar2(10))
    on commit delete rows;
    insert into temp_dept values(10,'ABC');
    commit;
    select * from temp_dept; --无数据显示,数据自动清除
     on commit preserve rows:在会话期间表一直可以存在(保留数据)
     on commit delete    rows:事务结束清除数据(在事务结束时自动删除表的数据) 
0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:157378次
    • 积分:2477
    • 等级:
    • 排名:第14885名
    • 原创:63篇
    • 转载:106篇
    • 译文:0篇
    • 评论:38条