Orcale学习问题

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 关闭之前，程序无法访问输出参数和返回值的数据。

 

4．C#调用oracle存储过程（例子）

Oracle方面
（１）创建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;
（２）测试过程存储
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

　 

5.一个操作oracle的c#类

/***********************************************************************
 * 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 得到1（SQLServer）

　　Asin,Acos,Atan,Atan2 返回弧度

　　弧度角度互换(SQLServer，Oracle不知道)
　　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中的null以10代替)
　　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-4，4为同音，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:事务结束清除数据（在事务结束时自动删除表的数据）