Oracle数据库使用

1.Oracle和sqlserver数据类型对应

SqlServer数据类型	SqlServer长度	Oracle数据类型
bigint	8	NUMBER(19)
binary	50	RAW(50)
bit	1	NUMBER(2)
char	10	CHAR(10)
datetime	8	DATE
decimal	9	NUMBER(18)
float	8	BINARY_DOUBLE
image	16	BLOB
int	4	NUMBER(10)
money	8	NUMBER(19,4)
nchar	10	NCHAR(10)
ntext	16	NCLOB
numeric	9	NUMBER(18)
nvarchar	50	NVARCHAR2(50)
real	4	BINARY_FLOAT
smalldatetime	4	DATE
smallint	2	NUMBER(5)
smallmoney	4	NUMBER(10,4)
sql_variant		BLOB
text	16	CLOB
timestamp	8	RAW(8)
tinyint	1	NUMBER(3)
uniqueidentifier	16	BLOB
varbinary	50	RAW(50)
varchar	50	VARCHAR2(50)

 

2.Oracle新建自增列

create table SYTEST
(
  syid   NUMBER(10) not null,
  syname VARCHAR2(100) not null,
  sydate VARCHAR2(100)
)

//创建序列

create sequence SYID increment by 1 start with 1 minvalue 1 maxvalue 9999999999999 nocache order;

//创建触发器实现自动插入

create or replace trigger SYIDtrigger
before insert on SYTEST
for each row
begin
    select SYID.nextval into :new.SYID from dual;
end ;

要使自增列自动插入数据，必须在插入sql语句指定插入字段。

序列清零必须先删除序列，再新建序列 drop sequence SYID;

 

3.oracle确认连接字符串

安装目录下搜索tnsnames.ora，用记事本打开，找到要连接的数据库，将指定部分复制到DataSource即可

User Id=test;Password=123;Data Source=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = mydb)))

http://www.cnblogs.com/gsk99/archive/2011/04/14/2016263.html

 

4.关键字作为列，操作时关键字加上""即可，加了""的关键字必须大写

select * from Nation where "NAME"='汉族';

 

5.查询前几条数据用法，利用隐藏关键字rownum

select NationId,nationname from Nation where rownum<2 and nationname like '土%' order by NationId desc

 

6.查询出的两个字段拼接

Oracle字符串连接使用“||”进行字符串拼接，其使用方式和MSSQLServer中的加号“+”一样，前提是字段都是字符型,假如不是,就用to_char()方法转换成字符型

除了“||”，Oracle还支持使用CONCAT()函数进行字符串拼接，比如执行下面的SQL语句：
SELECT CONCAT('工号:',FNumber) FROM T_Employee

如果CONCAT中连接的值不是字符串，Oracle会尝试将其转换为字符串，比如执行下面的SQL语句： 
SELECT CONCAT('年龄:',FAge) FROM T_Employee

与MYSQL的CONCAT()函数不同，Oracle的CONCAT()函数只支持两个参数，不支持两个以上字符串的拼接，比如下面的SQL语句在Oracle中是错误的：
SELECT CONCAT('工号为',FNumber,'的员工姓名为',FName) FROM T_Employee
WHERE FName IS NOT NULL
运行以后Oracle会报出下面的错误信息：
参数个数无效

如果要进行多个字符串的拼接的话，可以使用多个CONCAT()函数嵌套使用，上面的SQL可以如下改写：
SELECT CONCAT(CONCAT(CONCAT('工号为',FNumber),'的员工姓名为'),FName) FROM
T_Employee
WHERE FName IS NOT NULL

select to_char(NationId)||nationname from Nation where rownum<2 and nationname like '土%' order by NationId desc

select concat(NationId,nationname) from Nation where rownum<2 and nationname like '土%' order by NationId desc

 

7.查询出的字段起别名可以使用as，也可以不用as，和mssql不同的是as别名不能添加单引号，但是可以添加双引号

    select to_char(NationId)||nationname as 别名 from VGS_Nation where rownum<2 and nationname like '土%' order by NationId desc;
    select concat(NationId,nationname) as "别名" from VGS_Nation where rownum<2 and nationname like '土%' order by NationId desc;

 

8.获取当前时间

getdate()是sql server中取系统当前时间的函数

oracle中是sysdate

 

9.Oracle instr函数与SqlServer charindex的区别

 INSTR(C1,C2[,I[,J]])      http://www.cnblogs.com/ivan0626/p/3664548.html

【功能】在一个字符串中搜索指定的字符,返回发现指定的字符的位置;

【说明】多字节符(汉字、全角符等)，按1个字符计算

【参数】 C1 被搜索的字符串

            C2 希望搜索的字符串

            I 搜索的开始位置,默认为1

            J 第J次出现的位置,默认为1

 

10.in或like参数化

string sql = "SELECT UserRoleName FROM UserRole WHERE UserRoleId IN (SELECT trim(regexp_substr(str, '[^,]+', 1, level)) strRows FROM (SELECT :UserRoleId as str from dual ) t CONNECT BY instr(str, ',', 1, level -1) >0)";
helper.CommandText(sql);
helper.AddParameter(":UserRoleId", item.UserRoleId.TrimEnd(',')); 

注意：整形和字符串都不用加单引号，如string professionCode = string.Join(",", code);为正确，string professionCode = string.Join("','", code);则查找不出数据。

like参数化：where ProfessionName like '%'||:ProfessionName||'%'

      参考：http://bbs.csdn.net/topics/392004454

              http://blog.sina.com.cn/s/blog_5e5207e001018wk8.html

 

11.对整形列数量+1

     update Users set TodayErrorLoginCount=TodayErrorLoginCount+1 where UserId=1

 

12.修改序列当前值为指定的值

    第一步：删除重建序列，如：起始值为1，增量为1(按照原来设置条件重建序列)；

    第二步：运行下面sql，其中5就是要修改的序列的当前值，SEQ_Grade为序列名称

declare
  LastValue integer;
begin
  loop
    select SEQ_Grade.nextval into LastValue from dual;
    exit when LastValue >= 5;
  end loop;
end;

  不删除修改方法 

select seq_grade.nextval from dual;--查询出来是15，需要重置当前值为5
alter sequence seq_grade increment by -10; --注意是-(15-5)
select seq_grade.nextval from dual;--再查一遍，走一下,重置为1了 
alter sequence seq_grade increment by 1;--还原

 

13.数据库bool类型

    可以使用Number(1,0)或char(1)，建议使用char(1)并有默认值，数据存入使用1和0,保证通用

 

14.Oracle关键字作为列名称

   C#参数化sql语句对列名和参数化变量名加上"，如

   string sql="update grade set "\"Date\"=:\"Date\" where Id=1";

 

15.根据日期查询数据

   DateTime date;
   if (DateTime.TryParse(txtDate.Value.Trim(), out date))
         dataPage.Where += string.Format(" and cs.{0}=to_date('{1}','yyyy-mm-dd hh24:mi:ss')", CourseSchedule.AddDate, date);

 

16.Oracle数据库迁移

    第一种：使用Oracle自带的SQL Developer，菜单-》工具—》数据库导出

         

         

         

         

         

        将“导出.sql”复制到其它安装oracle的电脑上，使用Database Configuration Assistant创建一个和“导出.sql”数据库名一样的空数据库，然后在SQL Developer使用system帐号连接空数据库，在“其他用户”里面新建一个一模一样的连接帐号("导出.sql"里面创建表都有关联的帐号的名称)，创建完毕后，在SQL Developer使用创建的帐号登录，然后把"导出.sql"文件拖进来，管理空数据库，全部选中后执行即可完成全部数据的迁移。

 

 参考

http://www.cnblogs.com/cy759648077/p/5758288.html