一个关于Oracle更新语句引发的...-Oracle Sql基础-oracle导出数据到文本、从文本导入数据

当前位置: 数据库>oracle

本页文章导读:

▪一个关于Oracle更新语句引发的时间字段类型的问题想当初我在做一个ASP.NET网站的时候，用的是Oracle数据库更新数据。表里面的字段有int类型的，string类型的，还有date类型的，在和asp.NET前台控件绑定更新的时候总是出现问题，我当时上网查了.........

▪Oracle Sql基础 1、nvl('A','B') 如果a为null，则显示B2、DECODE(value, if1, then1, if2,then2, if3,then3, . . . else )3、grouping函数用法，带一个参数，参数为字段名，结果是根据该字段得出来的就返回1，反之返回04、Overover不.........

▪oracle导出数据到文本、从文本导入数据经常有需求向表中导入大量的数据，使用insert不靠谱，太慢oracle提供了sqlldr的工具也有时需要讲数据导入到文本，oracle的spool可以轻松实现方便的实现oracle导出数据到txt、txt导入数据到oracle一.........

[1]一个关于Oracle更新语句引发的时间字段类型的问题

来源: 发布时间: 2013-10-29

想当初我在做一个ASP.NET网站的时候，用的是Oracle数据库更新数据。表里面的字段有int类型的，string类型的，还有date类型的，在和asp.NET前台控件绑定更新的时候总是出现问题，我当时上网查了好多东西，总是看的模棱两可，毕竟也是头一次接触操作Oracle。我把我最初写的更新贴出来（现在这个问题已经解决了），你们看看：

更新语句如下：
sql = string.Format("update xxt_registration r set r.name='{0}',r.ip='{1}',r.port='{2}',r.time='{3}',r.location='{4}',r.installtime='{5}',r.type=(select d.id from xxt_devicetype d where d.name='{6}') where r.id='{7}'",

this.txtname.Text.Trim(), this.txtip.Text.Trim(), this.txtport.Text.Trim(), this.txttime.Text.Trim(), this.txtlocation.Text.Trim(), this.txtinstalltime.Text.Trim(), this.DropDownList_tname.Text.Trim(), this.txtid.Text.Trim());

首先，出现的问题就是“类型”与“单引号”的问题，报的错误是，如下图：

这个错误是，ORA-01861: 文字与格式字符串不匹配。

可能是因为你那个表里面的time这一列，数据类型是date类型的。

你传递一个字符格式的内容进去以后，数据库不知道如何格式化这个字符信息为日期。

下面是一个网友提供的，错误重现的例子与解决的办法。在这里非常感谢他的热心帮助（链接地址是http://zhidao.baidu.com/question/551220541?quesup2&oldq=1）

SQL> create table test_time( time   date );
表已创建。
SQL> insert into test_time values('2013-05-17 16:55');
insert into test_time values('2013-05-17 16:55')
                             *
ERROR 位于第 1 行:
ORA-01861: 文字与格式字符串不匹配
SQL> insert into test_time values('2013-05-17 16:55:30');
insert into test_time values('2013-05-17 16:55:30')
                             *
ERROR 位于第 1 行:
ORA-01861: 文字与格式字符串不匹配
SQL> insert into test_time values(  TO_DATE('2013-05-17 16:55:30', 'YYYY-MM-DD HH24:MI:SS') );
已创建 1 行。

对于上面的更新语句，我总结如下：

string类型的可以加单引号，像r.name='{0}',r.ip='{1}',r.port='{2}'等等都是可以的;

int类型的在Oracle数据库更新语句中是不能加单引号的，像id='{7}'是不行的，应该去掉单引号改为id={7};

date类型可以加单引号，但不是像r.time='{3}'这样的，应该应用TO_DATE()来转换格式，如：r.time=to_date('{3}','yyyy-mm-dd')。asp.net前台控件中输入的是字符串类型的数据，更新的时候当然要转换成时间类型格式的，修改后的更新语句如下：

sql = string.Format("update xxt_registration r set r.name='{0}',r.ip='{1}',r.port='{2}',r.location='{3}',r.installtime=to_date('{4}','yyyy-mm-dd'),r.type=(select d.id from xxt_devicetype d where d.name='{5}') where r.id={6}",
                    this.txtname.Text.Trim(), this.txtip.Text.Trim(), this.txtport.Text.Trim(), this.txtlocation.Text.Trim(), this.Calendarinstalltime.SelectedDate.ToString("yyyy-MM-dd"), this.DropDownList_tname.Text.Trim(), this.txtid.Text.Trim());

最后，如果您还需要Oracle中TO_DATE格式的其它解答，下面的链接地址有详细的解说，相信一定能帮助您的。

http://www.cnblogs.com/ajian/archive/2009/03/25/1421063.html

本文链接

[2]Oracle Sql基础

来源: 发布时间: 2013-10-29

1、nvl('A','B') 如果a为null，则显示B

2、DECODE(value, if1, then1, if2,then2, if3,then3, . . . else )

3、grouping函数用法，带一个参数，参数为字段名，结果是根据该字段得出来的就返回1，反之返回0

4、Over

over不能单独使用，要和分析函数：rank(),dense_rank(),row_number()等一起使用。
其参数：over（partition by col1 order by col2）
含义：按col1指定的字段进行分组，然后再按照col2进行排序

4、开窗函数：rank() over ( partition by [col1],[col2]... by [col3]desc)

rank,dense_rank,row_number的区别：
结果集中如果出现两个相同的数据，那么rank会进行跳跃式的排名，
比如两个第二，那么没有第三接下来就是第四；
但是dense_rank不会跳跃式的排名，两个第二接下来还是第三；
row_number最牛，即使两个数据相同，排名也不一样。

5、distinct 去重复

6、Lag和Lead函数可以在一次查询中取出某个字段的前N行和后N行的数据（可以是其他字段的数据，比如根据字段甲查询上一行或下两行的字段乙），原来没有分析函数的时候采用子查询方法，但是比较麻烦，惭愧，我用子查询有的还查不出来呢。

语法如下：
lag(value_expression [,offset] [,default]) over ([query_partition_clase] order_by_clause)；
lead(value_expression [,offset] [,default]) over ([query_partition_clase] order_by_clause)；
其中：
value_expression：可以是一个字段或一个内建函数。
offset是正整数，默认为1，指往前或往后几点记录．因组内第一个条记录没有之前的行，最后一行没有之后的行，
default就是用于处理这样的信息，默认为空。

求出每个打工者上个月和下个月有没有赚钱(personincome大于零即为赚钱)

select distinct earnmonth,sname,
       lag(decode(nvl(personincome,0),0,'No','Yes'),1,0) over(partition by sname order by earnmonth) PerMonth,
       lead(decode(nvl(personincome,0),0,'No','Yes'),1,0) over(partition by sname order by earnmonth) NextMonth
from earnings;

详细内容：http://love-flying-snow.iteye.com/blog/573083

本文链接

[3]oracle导出数据到文本、从文本导入数据

来源: 发布时间: 2013-10-29

经常有需求向表中导入大量的数据，使用insert不靠谱，太慢
oracle提供了sqlldr的工具

也有时需要讲数据导入到文本，oracle的spool可以轻松实现

方便的实现oracle导出数据到txt、txt导入数据到oracle

一、导出数据到txt

这里用all_objects表做测试

SQL> desc all_objects;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 OBJECT_NAME                               NOT NULL VARCHAR2(30)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                 NOT NULL NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                   NOT NULL DATE
 LAST_DDL_TIME                             NOT NULL DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)

拿object_id,object_name做导出、导入测试
这里需要一些设置满足数据导出的样式

vi exp_table.sql

set line 1000         --设置行的长度
set pagesize 0        --输出不换页
set feedback off      --默认的当一条sql发出的时候，oracle会给一个反馈，比如说创建表的时候，如果成功命令行会返回类似：Table created的反馈,off后不显示反馈
set heading off       --不显示表头信息
set trimspool on      --如果trimspool设置为on，将移除spool文件中的尾部空
set trims on          --去掉空字符
set echo off;　　　　   --显示start启动的脚本中的每个sql命令，缺省为on
set colsep '|'         --设置分隔符
set termout off        --不在屏幕上显示结果
spool db1.txt          --记录数据到db1.txt
select object_id,object_name from all_objects;  --导出数据语句
spool off              --收集完毕
exit

一切就绪后导出数据

[oracle@centos5 ~]$ sqlplus test/test @exp_table.sql 

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jun 13 16:35:14 2013

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@centos5 ~]$ sed -i 's/ //g' db1.txt  --可选，去除每行开头部分的空格
[oracle@centos5 ~