oracle数据库使用笔记一

　　最近做个一个项目是使用oracle数据库的，在使用过程中会用到各种知识点，所以记下来方便自己查阅，同时跟大家分享。

1、字符集乱码问题

　　字符乱码一般都是服务器端跟客户端字符编码不一致导致，首先查询服务器端字符编码：

　　SQL> select * from V$NLS_PARAMETERS;

　　　　　PARAMETER   　　　　　　　　　　　　VALUE

　　　　　--------------------- 　　　　　　-------------------------------
　　　　　NLS_LANGUAGE 　　　　　　 CHINESE_CHINA.ZHS16GBK

　　也可以通过这种方式获取服务器端的字符编码：

　　SQL>select userenv('language') from dual;

　　然后根据服务器端字符编码更改客户端字符编码，保持一致：

　　[oracle@localhost ~]$ export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"

2、导入导出命令的使用

　　导入工具imp.exe,导出工具exp.exe,这两个程序分别在oracle的安装目录的bin目录下，有多种写法其格式如下：

　　--导出单表

　　exp system/password@orcl file=/home/oracle/tablename.dmp log=/home/oracle/tablename.log tables=tablename

　　--有查询条件的导出

　　exp system/password@orcl file=/home/oracle/tablename.dmp log=/home/oracle/tablename.log tables=tablename query=\" where colname = value \"

　　--导出多表到一个文件

　　exp system/password@orcl file=/home/oracle/tablename.dmp log=/home/oracle/tablename.log tables=(tablename1,tablename2,...)

　　--导出指定用户下的所有表

　　exp system/password@orcl file=/home/oracle/tablename.dmp log=/home/oracle/tablename.log owner=(user1,user2,...)

　　--导入

　　imp system/password@orcl file=/home/orace/tablename.dmp full=y

3、使用sqlldr工具装载数据

　　sqlldr是通过控制文件来说明要导入的数据文件的格式，包括分隔符，字段格式。

　　首先编辑控制文件tabname.ctl：

　　　　　　options (errors=99999,rows=50000,bindsize=8194304)      --选项允许错误数，一次提交行数，每次提交记录的缓冲区的最大值

　　　　　　load data characterset ZHS16GBK　　　　　　　　　　　　　--字符集

　　　　　　infile '/home/oracle/tabname.dat'　　　　　　　　　　　　　 --需要导入的数据文件

　　　　　　FIELDS TERMINATED BY x'03'　　　　　　　　　　　　　　  --列分隔符(两个列通过^C分隔)

　　　　　　OPTIONALLY ENCLOSED BY '"'　　　　　　　　　　　　　　  --字段修饰符(字段通过""括起来)

　　　　　　truncate into tabname　　　　　　　　　　　　　　　　　　  --目标表名(选项有truncate,insert,append,replace)

 　　　　　  trailing nullcols　　　　　　　　　　　　　　　　　　　　　　 --表的字段没有对应的值时允许为空

　　　　　　(

　　　　　　　　colname1 char(255),　　　　　　　　　　　　　　　　　　--如何单个列长度超过255个字节时，需要标注出来

　　　　　　　　colname2 "trim(:colname2)",　　　　　　　　　　　　　　--去除该字段前后空格(可以用函数处理字段值)

　　　　　　　　colname3 date "YYYY-MM-DD HH24:MI:SS",　　　　　　--日期字段声明日期格式

　　　　　　　　colname4 "substr(:colname1,1,5)"　　　　　　　　　　　 --取字段1的一到五位(可以使用其他列的值填充本列)

　　　　　　)

 　　　　装载命令：

　　　　　sqlldr userid=system/password data=tabname.del control=tabname.ctl direct=true log=tabname.log

　　　　　--如果data指定的文件跟控制文件中infile指定的文件不一致时以data指定文件装载，direct选项指定以直接路径方式装载，相当于insert /*+append*/ into select语法

 4、在linux环境下执行sql脚本

　　首先编辑sql文件 dept.sql：

　　　　connect system/password
　　　　spool ./dept.lst --输出日志
　　　　set linesize 100 pagesize 80
　　　　create table test_sql(id int not null primary key,name varchar2(10));
　　　　insert into test_sql values(1,'aaa');
　　　　insert into test_sql values(2,'bbb');
　　　　commit;
　　　　select * from test_sql;
　　　　spool off;
　　　　exit;

　　执行sql脚本：

 

 　　[oracle@localhost ~]$sqlplus -S /nolog @/home/oracle/dept.sql ---S 表示以静默方式执行

5、嵌入sql语句到shell脚本

　　(1)直接将sql语句嵌入到shell脚本 get_dept_2.sh

　　　　sqlplus -S system/password@orcl<<EOF
　　　　set echo off;
　　　　set heading off;
　　　　set pagesize 0;
　　　　set termout off;
　　　　set trimout on;
　　　　set trimspool on;
　　　　SET SPACE 0;
　　　　set term off;
　　　　set linesize 1000;
　　　　SET NEWPAGE NONE;
　　　　SET VERIFY OFF;
　　　　set feedback off;
　　　　spool test_sql.del
　　　　select id||','||name from test_sql;
　　　　spool off
　　　　exit;
　　　　EOF

　　(2)使用管道符号>代替spool来输出日志 get_dept_2.sh
　　　　sqlplus -S /nolog 1>/home/oracle/dept.log 2>&1 <<EOF
　　　　connect system/password
　　　　set linesize 80 pagesize 80
　　　　select * from test_sql;
　　　　exit;
　　　　EOF
　　　　cat /home/oracle/dept.log
　　　　exit