ETL基础及常用技术培训PPT课件

ETL基础及常用技术,主要内容,ETL基本概念ETL常用逻辑架构ETL实施过程ETL常用技术（shell,oracle,datastage）,ETL基本概念,ETL(Extract-Transform-Load)即数据的抽取、转换与加载。ETL是从各种原始的业务系统(异构多源)中提取数据，按照预先设计好的规则将抽取到的数据进行转换，最后将转换完的数据按计划增量或全部导人到目标数据库，成为联机分析处理、数据挖掘的基础。ETL为BI/DW的核心和灵魂.ETL就是一个批量数据加工的过程.,ETL常用逻辑架构,数据抽取数据清洗与转换数据加载,ETL实施过程,数据抽取确定所有数据源来源于哪些源系统，核心系统，信贷，信用卡等定义数据接口对每个源文件及接口的每个字段进行详细说明数据抽取方法主动抽取还是源系统提供文件，增量还是全量,每日还是每月,ETL实施过程,1.代码标准化2.数据粒度的转换3.根据业务规则计算确定数据清洗和转换规则后细化mapping,编码实现,数据清洗与转换数据清洗1.不完整的数据2.错误的数据3.重复的数据数据转换,ETL实施过程,数据加载将数据缓冲区的数据直接加载到数据库对应的表中，如果是全量采用load方式，如果是增量则根据业务规则merge进数据库,ETL实施过程,ETL常用技术,SHELL(unix基本操作)SQLPL/SQLPROCDATASTAGE,shell基础,Linux中有好多种不同的shell，如bsh，csh，同其他语言一样，可以通过我们使用任意一种文字编辑器，比如vi等来编写我们的shell程序。程序必须以下面的行开始（必须放在文件的第一行）：#!/bin/sh符号#!用来告诉系统它后面的参数是用来执行该文件的程序。在这个程序中我们使用/bin/sh来执行程序。当编辑好脚本时，如果要执行该脚本，还必须使其可执行。要使脚本可执行：chmod+xfilename然后，可以通过输入：./filename来执行脚本。,.,shell基础,Vi（一）、进入vi在系统提示字符(如$、#)下敲入vi档案名称，vi可以自动帮你载入所要编辑的文件或是开启一个新文件（如果该文件不存在或缺少文件名）。进入vi后萤幕左方会出现波浪符号，凡是列首有该符号就代表此列目前是空的。(二）、两种模式如上所述，vi存在两种模式：指令模式和输入模式。在指令模式下输入的按键将做为指令来处理：如输入a，vi即认为是在当前位置插入字符。而在输入模式下，vi则把输入的按键当作插入的字符来处理。指令模式切换到输入模式只需键入相应的输入命令即可（如a,A），而要从输入模式切换到指令模式，则需在输入模式下键入ESC键，如果不晓得现在是处於什麽模式，可以多按几次ESC，系统如发出哔哔声就表示已处于指令模式下了。,.,shell基础,Vi由指令模式进入输入模式的指令：新增(append)a：从光标所在位置後面开始新增资料，光标後的资料随新增资料向後移动。A：从光标所在列最後面的地方开始新增资料。插入(insert)i：从光标所在位置前面开始插入资料，光标後的资料随新增资料向後移动。I：从光标所在列的第一个非空白字元前面开始插入资料。开始(open)o：在光标所在列下新增一列并进入输入模式。O:在光标所在列上方新增一列并进入输入模式。,.,shell基础,Vi（三）、基本编辑配合一般键盘上的功能键，像是方向键、Insert、Delete等等，现在你应该已经可以利用vi来编辑文件了。当然vi还提供其他许许多多功能让文字的处理更为方便。何谓编辑？一般认为是文字的新增、修改以及删除，甚至包括文字区块的搬移、复制等等。先这里介绍vi的如何做删除与修改。删除与修改文件的命令：x：删除光标所在字符。dd：删除光标所在的列。r：修改光标所在字元，r後接著要修正的字符。R：进入取替换状态，新增文字会覆盖原先文字，直到按ESC回到指令模式下为止。s：删除光标所在字元，并进入输入模式。S：删除光标所在的列，并进入输入模式。在指令模式下移动光标的基本指令是h,j,k,l。,.,shell基础,Vi（四）、退出vi在指令模式下键入:q,:q!,:wq或:x(注意:号），就会退出vi。其中:wq和:x是存盘退出，而:q是直接退出，如果文件已有新的变化，vi会提示你保存文件而:q命令也会失效，这时你可以用:w命令保存文件后再用:q退出，或用:wq或:x命令退出，如果你不想保存改变后的文件，你就需要用:q!命令，这个命令将不保存文件而直接退出vi。,shell基础,注释#变量在shell编程中，所有的变量都由字符串组成，并且不需要对变量进行声明。要赋值给一个变量，可以这样写：变量名=值取出变量值可以加一个美元符号（$）在变量前面shell脚本中的三类命令1）Unix命令2）管道,重定向和backtick3）流程控制,shell基础unix命令,在shell脚本中可以使用任意的unix命令，这些命令通常是用来进行文件和文字操作的。常用命令语法及功能:echosometext:将文字内容打印在屏幕上ls:文件列表wclfilewc-wfilewc-cfile:计算文件行数计算文件中的单词数计算文件中的字符数cpsourcefiledestfile:文件拷贝mvoldnamenewname:重命名文件或移动文件rmfile:删除文件greppatternfile:在文件内搜索字符串比如：grepsearchstringfile.txtcut-bcolnumfile:指定欲显示的文件内容范围，并将它们输出到标准输出设备比如：输出每行第5个到第9个字符cut-b5-9file.txt千万不要和cat命令混淆，这是两个完全不同的命令,.,shell基础unix命令,ftp:远程传输文件ftpip然后输入用户名密码，put，getcatfile.txt:输出文件内容到标准输出设备（屏幕）上filesomefile:得到文件类型readvar:提示用户输入，并将输入赋值给变量sortfile.txt:对file.txt文件中的行进行排序uniq:删除文本文件中出现的行列比如：sortfile.txt|uniqexpr:进行数学运算Example:add2and3expr2+3find:搜索文件比如：根据文件名搜索find.-namefilename-printtee:将数据输出到标准输出设备(屏幕)和文件比如：somecommand|teeoutfilebasenamefile:返回不包含路径的文件名比如：basename/bin/tux将返回tux,.,shell基础unix命令,dirnamefile:返回文件所在路径比如：dirname/bin/tux将返回/binheadfile:打印文本文件开头几行tailfile:打印文本文件末尾几行sed:Sed是一个基本的查找替换程序。可以从标准输入（比如命令管道）读入文本，并将结果输出到标准输出（屏幕）。该命令采用正则表达式（见参考）进行搜索。不要和shell中的通配符相混淆。比如：将linuxfocus替换为LinuxFocus：cattext.file|seds/linuxfocus/LinuxFocus/newtext.fileawk:awk用来从文本文件中提取字段。缺省地，字段分割符是空格，可以使用-F指定其他分割符。catfile.txt|awk-F,print$1,$3这里我们使用，作为字段分割符，同时打印第一个和第三个字段。如果该文件内容如下：AdamBor,34,IndiaKerryMiller,22,USA命令输出结果为：AdamBor,IndiaKerryMiller,USAtar:将文件或文件夹打包解包，tarcvfa.tarabctarxvfa.tar,shell基础管道、重定向和backtick命令,管道(|)将一个命令的输出作为另外一个命令的输入。grephellofile.txt|wc-l在file.txt中搜索包含有”hello”的行并计算其行数。在这里grep命令的输出作为wc命令的输入重定向将命令的结果输出到文件，而不是标准输出（屏幕）写入文件并覆盖旧文件加到文件的尾部，保留旧文件内容。反短斜线（）使用反短斜线可以将一个命令的输出作为另外一个命令的一个命令行参数。命令：V_date=date+%F,.,shell基础流程控制命令(if),ifThen.elifThen.Else.fi大多数情况下，可以使用测试命令来对条件进行测试。比如可以比较字符串、判断文件是否存在及是否可读等等通常用来表示条件测试。注意这里的空格很重要。要确保方括号的空格。-fsomefile：判断是否是一个文件-x/bin/ls：判断/bin/ls是否存在并有可执行权限-n$var：判断$var变量是否有值$a=$b：判断$a和$b是否相等,.,shell基础流程控制命令(if),字符串比较:string1=string2如果相等则为真string1!=string2如果不等则为真-nstring如果不空则为真-zstring如果为空则为真算术比较:expression1-eqexpression2如果相等则为真expression1-neexpression2如果不等则为真expression1-gtexpression2如果大于则为真expression1-geexpression2大于等于则为真expression1-ltexpression2如果小于则为真expression1-leexpression2小于等于则为真,.,shell基础流程控制命令(if),文件:-dfile如果为目录则为真-efile如果存在则为真(在这里要注意的是,由于历史原因,-e选项并不可移植,所以常用的是-f选项-ffile如果为常规文件则为真-gfile如果设置了组ID则为真-rfile如果文件可读则为真-sfile如果文件大小不为零则为真-ufile如果设置了用户ID则为真-wfile如果文件可写则为真-xfile如果文件可执行则为真,.,shell基础流程控制命令,1)while.Do.Donewhile-loop将运行直到表达式测试为真。willrunwhiletheexpressionthatwetestforistrue.关键字break用来跳出循环。而关键字”continue”用来不执行余下的部分而直接跳到下一个循环。2)forvarin.do.Donefor-loop表达式查看一个字符串列表(字符串用空格分隔)然后将其赋给一个变量：3)case.in.).;Esaccase表达式可以用来匹配一个给定的字符串,.,oracle基础数据库安装,Windows环境下:网上下载安装包点击安装全选默认配置即可。其它机器上已有server端，可只安client端。PLSQL软件是一个优秀的oracle工具，建议安装unix环境下:建oracle组和用户在oracle用户下安装授予其它用户执行oracle命令的权限,.,oracle基础数据库的连接,Client端连接配置到oracle安装目录下搜索tnsnames.ora增加修改相应连接内容ORCL=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=cg)(PORT=1521)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)mdb=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=1)(PORT=1521)(CONNECT_DATA=(SERVER=DEDICATED)(SID=mdb),.,oracle基础数据库的连接,Unix环境先设置编辑器exportEDITOR=viselectparameter,valuefromnls_database_parameterswhereparameter=NLS_CHARACTERSET;-显示数据库字符集NLS_LANG应与数据库字符集一致exportNLS_LANG=“SIMPLIFIEDCHINESE.ZHS16GBK”exportNLS_DATE_FORMAT=YYYY-MM-DD-HH24.MI.SS命令行：sqlplususername/passwordserverassysdbaassysopersqlplus/assysdba-管理员进入sqlplusetl/etlmdb-普通用户进入SQLsetlinesize300;-设置行长度SQLsetpagesize1;-设置页长度SQLcolnameformata30;-设置列格式SQLselectsysdatefromdual;-运行语句,.,oracle基础数据库的连接,Plsql是一款优秀的操作oracle图形界面软件Sqlwindow可单条执行sqlCommandwindow可批量执行多条sql,.,oracle基础表空间,ORACLE数据库被划分成称作为表空间的逻辑区域形成ORACLE数据库的逻辑结构。一个ORACLE数据库能够有一个或多个表空间,而一个表空间则对应着一个或多个物理的数据库文件。表空间是ORACLE数据库恢复的最小单位,容纳着许多数据库实体,如表、视图、索引、聚簇、回退段和临时段等。每个ORACLE数据库均有SYSTEM表空间,这是数据库创建时自动创建的。SYSTEM表空间必须总要保持联机,因为其包含着数据库运行所要求的基本信息(关于整个数据库的数据字典、联机求助机制、所有回退段、临时段和自举段、所有的用户数据库实体、其它ORACLE软件产品要求的表)。一个小型应用的ORACLE数据库通常仅包括SYSTEM表空间,然而一个稍大型应用的ORACLE数据库采用多个表空间会对数据库的使用带来更大的方便。,.,oracle基础表空间,建立本地管理表空间CREATETABLESPACEtablespace_nameDATAFILEfile_nameSIZEfile_sizeUNIFORMSIZEextent_size|AUTOALLOCATECREATETABLESPACEDATATBS01_32KDATAFILE/home/mdb/oracle/oradata/mdb/datatbs01_01.dbfSIZE100G,/home/mdb/oracle/oradata/mdb/datatbs01_02.dbfSIZE100G,/home/mdb/oracle/oradata/mdb/datatbs01_03.dbfSIZE100G,EXTENTMANAGEMENTLOCALUNIFORMSIZE32MSEGMENTSPACEMANAGEMENTAUTOblocksize32k,.,oracle基础表空间,建立UNDO表空间CREATEUNDOTABLESPACEtablespace_nameDATAFILEfile_nameSIZEfile_sizeUNIFORMSIZEextent_size|AUTOALLOCATE建立临时表空间:存放查询、排序、分组等生成的临时数据CREATETEMPORARYTABLESPACEtablespace_nameTEMPFILEfile_nameSIZEfile_sizeUNIFORMSIZEextent_size|AUTOALLOCATE,.,oracle基础表空间,改变表空间可用性ALTERTABLESPACEtablespace_nameonline|offline改变表空间读写状态ALTERTABLESPACEtablespace_nameREADWRITE|READONLY改变表空间名称ALTERTABLESPACEtablespace_nameRENAMETOnew_tbs_name设置默认表空间ALTERDATABASEDEFAULTTABLESPACEtablespace_name-数据库默认表空间ALTERDATABASEDEFAULTTEMPORARYtablespace_name-数据库默认临时表空间删除表空间DROPTABLESPACEtablespace_nameINCLUDINGCONTENTSANDDATAFILES;,.,oracle基础用户管理,建立用户createuserETLidentifiedbydefaulttablespaceETLDATA01temporarytablespaceTEMP1profileDEFAULT;给用户授权grantconnecttoetl;grantselect_catalog_roletoetl;grantselectanytabletoetl;grantcreatesessiontoetl;grantaltersessiontoetl;grantcreatetabletoetl;grantcreateviewtoetl;grantcreatetypetoetl;grantcreatesynonymtoetl;grantcreatesequencetoetl;grantcreateproceduretoetl;,.,oracle基础用户管理,修改口令ALTERUSERuser_nameIDENTIFIEDBYnew_password;修改默认表空间ALTERUSERuser_nameDEFAULTTABLESPACEnew_tablespace_name;锁定用户ALTERUSERetlACCOUNTLOCK;解锁用户ALTERUSERetlACCOUNTUNLOCK;删除用户DROPUSERusernameCASCADE;,.,oracle基础表建立及管理,普通建表CREATETABLEschema.table_name(column_namedatatypeDEFAULTexpr,)TABLESPACEtablespace_name;使用子查询建表CREATETABLEschema.table_name(column_name,)TABLESPACEtablespace_nameASsubquery;建临时表-事务临时表当前事务内有效CREATEGLOBALTEMPORARYTABLEtable_name(column_name,)ONCOMMITDELETEROWS;-会话临时表当前会话内有效CREATEGLOBALTEMPORARYTABLEtable_name(column_name,)ONCOMMITPRESERVEROWS;,.,oracle基础表建立及管理,增加列ALTERTABLEtable_nameADD(columndatatypeDEFAULTexpr);修改列定义ALTERTABLEtable_nameMODIFY(columndatatypeDEFAULTexpr);删除列ALTERTABLEtable_nameDROP(column);修改列名ALTERTABLEtable_nameRENAMECOLUMNcolumn_nametonew_column_name;增加注释COMMENTONTABLEtable_nameIStext;COMMENTONCOLUMNtable_name.column_nameIStext;迁移非分区表的表空间ALTERTABLEtable_nameMOVEnew_tablespace_name;-表对应索引需重建,.,oracle基础表建立及管理,截断表-快速删除数据释放空间保留表结构-是DDL语句不可回退TRUNCATETABLEtable_name;-delete是DML语句可回退删除表-PURGE是oracle10g新特性表永久删除DROPTABLEtable_nameCASCADECONSTRAINTSPURGE;从回收站Recyclebin恢复被删除表FLASHBACKTABLEtable_nameTOBEFOREDROP;清空回收站PURGERECYCLEBIN;查看回收站SELECT*FROMRECYCLEBIN;,.,oracle基础表建立及管理,表SELECT*FROMUSER_TABLES;对象SELECT*FROMUSER_OBJECTS;表占用的空间selectsum(bytes)fromuser_segmentswheresegment_name=table_name;清空回收站PURGERECYCLEBIN;查看回收站SELECT*FROMRECYCLEBIN;查看表的列SELECT*FROMUSER_TAB_COLUMNS;查看注释SELECT*FROMUSER_TAB_COMMENTS;SELECT*FROMUSER_COL_COMMENTS;查看建表语句SELECTdbms_metadata.get_ddl(TABLE,)FROMdual;SELECTdbms_metadata.get_ddl(TABLE,upper(tmpcg_ctl_paravalue),upper(etl)FROMdual;,.,oracle基础索引,建索引CREATEINDEXUNIQUEindex_nameONtable_name(column_name,.)TABLESPACEtablespace_name;-不设表空间则建在用户默认表空间上维护索引ALTERINDEXindex_nameREBUILDTABLESPACEtablespace_name;-可改变索引表空间显示索引信息SELECT*FROMUSER_INDEXES;SELECT*FROMUSER_IND_COULMNS;-查找失效的非分区索引SELECT*FROMUSER_INDEXESWHERESTATUS=UNUSABLE;查看建索引语句SELECTdbms_metadata.get_ddl(INDEX,)FROMdual;SELECTdbms_metadata.get_ddl(INDEX,upper(idx_tmpcg_ctl_paravalue),upper(etl)FROMdual;,.,oracle基础序列,序列（Sequence）是用于生成唯一数字的数据库对象，自动生成顺序递增的序列号，可提供唯一的主键值，事务回滚值不会减少建序列CREATESEQUENCEseq_nameINCREMENTBYnSTARTWITHnMAXVALUEn|NOMAXVALUE;MINVALUEn|NOMINVALUECYCLE|NOCYCLECACHEn|NOCACHE;使用序列INSERTINTOt1(id)VALUES(t1id_seq.NEXTVAL);SELECTt1id_seq.CURRVALFROMdual;删除序列DROPSEQUENCEseq_name;显示序列信息SELECT*FROMSEQ;查看建序列语句SELECTdbms_metadata.get_ddl(SEQUENCE,)FROMdual;,.,oracle基础分区表,ORACLE的分区(PartitioningOption)是一种处理超大型表的技术。分区是一种“分而治之”的技术，通过将大表和索引分成可以管理的小块，从而避免了对每个表作为一个大的、单独的对象进行管理，为大量数据提供了可伸缩的性能。分区通过将操作分配给更小的存储单元，减少了需要进行管理操作的时间，并通过增强的并行处理提高了性能，通过屏蔽故障数据的分区，还增加了可用性。ORACLE的分区表的划分方法包括：按字段值进行划分的范围分区；按字段的HASH函数值进行的划分HASH分区；先按范围划分，再按HASH划分的复合分区；在ORACLE9i中又增强了按字段值列表进行划分的列表(Listing)分区方法。管理员可以指定每个分区的存储属性，分区在宿主文件系统中的放置情况，这样便增加了对超大型数据库的控制粒度(granularity)。分区可以被单独地删除、卸出或装入、备份、恢复，因此减少了需要进行管理操作的时间。还可以为表分区创建单独的索引分区，从而减少了需要进行索引维护操作的时间。此外，还提供了种类繁多的局部和全局的索引技术。分区操作也可以被并行执行。分区技术还提高了数据的可用性。当部分数据由于故障或其它原因不可用时，其它分区内的数据可不受影响继续使用。分区对应用是透明的，可以通过标准的SQL语句对分区表进行操作。Oracle的优化器在访问数据时会分析数据的分区情况，在进行查询时，那些不包含任何查询数据的分区将被忽略，从而大大提高系统的性能。总之，Oracle的分区表技术能满足高性能、高可用性、数据易管理性等综合需求。,.,oracle基础分区表设计原则,表的大小对于大表进行分区，将有益于大表操作的性能和大表的数据维护。通常当表的大小超过1.5GB2GB，或对于OLTP系统，表的记录超过1000万，都应考虑对表进行分区。数据访问特性基于表的大部分查询应用，只访问表中少量的数据。对于这样表进行分区，可充分利用分区排除无关数据查询的特性。数据维护某些表的数据维护，经常按时间段删除成批的数据，例如按月删除历史数据。对于这样的表需要考虑进行分区，以满足维护的需要。因为删除（Delete）大量的数据，对系统开销很大，有时甚至是不可接受的。只读数据如果一个表中大部分数据都是只读数据，通过对表进行分区，可将只读数据存储在只读表空间中，对于数据库的备份是非常有益的。并行数据操作（ParallelDML）对于经常执行并行操作（如ParallelInsert,ParallelUpdate等）的表应考虑进行分区。表的可用性当对表的部分数据可用性要求很高时，应考虑进行表分区。,.,oracle基础分区表,范围分区表创建脚本CREATETABLEHIS_EVT_DCC_FCMAUDI(cm_rec_llinteger,cm_tx_dtvarchar2(8),)PARTITIONBYRANGE(CM_TX_DT)(PARTITIONETL_LOAD_DATE_0508VALUESLESSTHAN(20050901)TABLESPACEetl0_r_jyrq_data_200508,PARTITIONETL_LOAD_DATE_0509VALUESLESSTHAN(20051001)TABLESPACEetl0_r_jyrq_data_200509,PARTITIONETL_LOAD_DATE_0510VALUESLESSTHAN(20051101)TABLESPACEetl0_r_jyrq_data_200510,PARTITIONETL_LOAD_DATE_0511VALUESLESSTHAN(20051201)TABLESPACEetl0_r_jyrq_data_200511,PARTITIONETL_LOAD_DATE_0512VALUESLESSTHAN(20060101)TABLESPACEetl0_r_jyrq_data_200512);CREATEUNIQUEINDEXHIS_EVT_DCC_FCMAUDI_UIDXONHIS_EVT_DCC_FCMAUDI(CM_TX_DTASC,CM_TX_LOG_NOASC,CM_TX_CODASC,CM_EC_FLGASC)LOCAL(PARTITIONETL_LOAD_DATE_0508TABLESPACEetl0_r_jyrq_idx_200508,PARTITIONETL_LOAD_DATE_0509TABLESPACEetl0_r_jyrq_idx_200509,PARTITIONETL_LOAD_DATE_0510TABLESPACEetl0_r_jyrq_idx_200510,PARTITIONETL_LOAD_DATE_0511TABLESPACEetl0_r_jyrq_idx_200511,PARTITIONETL_LOAD_DATE_0512TABLESPACEetl0_r_jyrq_idx_200512,);,.,oracle基础分区表,HASH分区表创建脚本CREATETABLEPERSONAL_CUSTOMER_INFO（）PARTITIONBYHASH(CI-CUST-NO)PARTITIONS64TABLESPACETS_ODS_DATA;复合分区表创建脚本CREATETABLEFACT_DCC_SAACNACN_PERSONAL(txn_date,sa-acct-no)PARTITIONBYRANGE(时间字段)SUBPARTITIONBYHASH(SA-ACCT-NO)SUBPARTITIONTEMPLATE(SUBPARTITIONs1,SUBPARTITIONs2,SUBPARTITIONs3,);,.,oracle基础分区表,查询SELECT*FROMhis_evt_dcc_fcmaudiPARTITION(ETL_LOAD_DATE_0508);清分区数据ALTERTABLEhis_evt_dcc_fcmaudiTRUNCATEPARTITION(ETL_LOAD_DATE_0508);新增分区ALTERTABLEhis_evt_dcc_fcmaudiADDPARTITIONETL_LOAD_DATE_0601VALUESLESSTHAN(20060201)TABLESPACEetl0_r_jyrq_data_200612;删除分区ALTERTABLEhis_evt_dcc_fcmaudiDROPPARTITIONETL_LOAD_DATE_0508;,.,oracle基础分区表,分区信息查询SELECT*FROMUSER_TAB_PARTITIONS;SELECT*FROMUSER_TAB_SUBPARTITIONS;SELECT*FROMUSER_IND_PARTITIONS;加上WHERESTATUS=UNUSABLE可查询失效的分区索引SELECT*FROMUSER_IND_SUBPARTITIONS;加上WHERESTATUS=UNUSABLE可查询失效的子分区索引SELECT*FROMUSER_SEGMENTS;,.,oracle基础sqlldr,用法:SQLLDRkeyword=value,keyword=value,.sqlldr$1control=tb.ctlerrors=10000rows=100000bindsize=8192000readsize=8192000log=tbldr.logbad=tbldr.baddirect=true有效的关键字:userid-ORACLE用户名/口令control-控制文件名log-日志文件名bad-错误文件名data-数据文件名discard-废弃文件名discardmax-允许废弃的文件的数目(全部默认)skip-要跳过的逻辑记录的数目(默认0)load-要加载的逻辑记录的数目(全部默认)errors-允许的错误的数目(默认50)rows-常规路径绑定数组中或直接路径保存数据间的行数(默认:常规路径64,所有直接路径)bindsize-常规路径绑定数组的大小(以字节计)(默认256000)silent-运行过程中隐藏消息(标题,反馈,错误,废弃,分区)direct-使用直接路径(默认FALSE)parfile-参数文件:包含参数说明的文件的名称parallel-执行并行加载(默认FALSE)file-要从以下对象中分配区的文件,.,oracle基础sqlldr,控制文件LOADDATAINFILEtest.txtINTOTABLEDEPTREPLACEFIELDSTERMINATEDBY,OPTIONALLYENCLOSEDBY(DEPTNO,DNAME,LOC),.,oracle基础文本数据导出,Oracle没有提供将数据导出到一个文件的工具。但是我们可以用SQL*Plus的select及format数据来输出到一个文件：setechooffnewpage0space0pagesize0feedoffheadofftrimspoolonspooloradata.txtselectcol1|,|col2|,|col3fromtab1wherecol2=XYZ;spooloff,.,oracle基础数据库备份导入导出,EXP:exphelp=y有三种主要的方式（完全、用户、表）1、完全：EXPSYSTEM/MANAGERBUFFER=64000FILE=C:FULL.DMPFULL=Y如果要执行完全导出，必须具有特殊的权限2、用户模式：EXPSONIC/SONICBUFFER=64000FILE=C:SONIC.DMPOWNER=SONIC这样用户SONIC的所有对象被输出到文件中。3、表模式：EXPSONIC/SONICBUFFER=64000FILE=C:SONIC.DMPOWNER=SONICTABLES=(SONIC)这样用户SONIC的表SONIC就被导出,.,oracle基础数据库备份导入导出,IMP:imphelp=y具有三种模式（完全、用户、表）1、完全：IMPsys/sys123assysdbaBUFFER=64000FILE=C:FULL.DMPFULL=Y2、用户模式：IMPSONIC/SONICBUFFER=64000FILE=C:SONIC.DMPFROMUSER=SONICTOUSER=SONIC这样用户SONIC的所有对象被导入到文件中。必须指定FROMUSER、TOUSER参数，这样才能导入数据。3、表模式：impSONIC/SONICBUFFER=64000FILE=C:SONIC.DMPOWNER=SONICTABLES=(SONIC)这样用户SONIC的表SONIC就被导入。,.,oracle基础查看执行计划,若有PLSQL等windows界面的工具，可点执行计划菜单查看命令行查看执行计划SQLexplainplanforsql语句；-并不真正执行，只是生成执行计划例：SQLexplainplanforselect*frometlcusorgwhereecif_cus_num=12345;已解释。SQL?/rdbms/admin/utlxplp-查看PLAN_TABLE_OUTPUT-|Id|Operation|Name|Rows|Bytes|Cost|-|0|SELECTSTATEMENT|1|279|3|1|TABLEACCESSBYINDEXROWID|ETLCUSORG|1|279|3|2|INDEXUNIQUESCAN|SYS_C007050|1|2|-,.,oracle基础常用函数,数值型函数ABS(x)【功能】返回x的绝对值mod(x,y)【功能】返回x除以y的余数power(x,y)【功能】返回x的y次幂round(x,y)【功能】返回四舍五入后的值【参数】x,y，数字型表达式,如果y不为整数则截取y整数部分，如果y0则四舍五入为y位小数，如果y小于0则四舍五入到小数点向左第y位。trunc(x,y)【功能】返回x按精度y截取后的值【参数】x,y，数字型表达式,如果y不为整数则截取y整数部分，如果y0则截取到y位小数，如果y小于0则截取到小数点向左第y位，小数前其它数据用0表示,.,oracle基础常用函数,字符型函数CONCAT(c1,c2)【功能】连接两个字符串同|LOWER(c1)【功能】：将字符串全部转为小写UPPER(c1)【功能】将字符串全部转为大写LENGTH(c1)【功能】返回字符串的长度;【说明】多字节符(汉字、全角符等)，按1个字符计算LENGTHB(c1)【功能】返回字符串的长度;【说明】多字节符(汉字、全角符等)，按2个字符计算LPAD(c1,n,c2)【功能】在字符串c1的左边用字符串c2填充，直到长度为n时为止RPAD(c1,n,c2)【功能】在字符串c1的右边用字符串c2填充，直到长度为n时为止,.,oracle基础常用函数,字符型函数LTRIM(c1,c2)【功能】删除左边出现的字符串默认为空格RTRIM(c1,c2)【功能】删除右边出现的字符串TRIM(c1)【功能】删除左边和右边出现的空格SUBSTR(c1,n1,n2)【功能】取子字符串【说明】多字节符(汉字、全角符等)，按1个字符计算【参数】在字符表达式c1里，从n1开始取n2个字符;若不指定n2,则从第y个字符直到结束的字串.,.,oracle基础常用函数,日期函数sysdate【功能】：返回当前日期add_months(d1,n1)【功能】：返回在日期d1基础上再加n1个月后新的日期。last_day(d1)【功能】：返回日期d1所在月份最后一天的日期。round(d1,c1)【功能】：给出日期d1按期间(参数c1)四舍五入后的期间的第一天日期（与数值四舍五入意思相近）【参数】：d1日期型,c1为字符型(参数)，c1默认为j（即最近0点日期)【参数表】：c1对应的参数表:最近0点日期:取消参数c1或j最近的星期日：day或dy或d最近月初日期：month或mon或mm或rm最近季日期：q最近年初日期：syear或year或yyyy或yyy或yy或y(多个y表示精度)最近世纪初日期：cc或scctrunc(d1,c1)【功能】：返回日期d1所在期间(参数c1)的第一天日期,.,oracle基础常用函数,转换函数TO_CHAR(x,c2,C3)【功能】将日期或数据转换为char数据类型【参数】x是一个date或number数据类型。c2为格式参数c3为NLS设置参数如果x为日期nlsparm=NLS_DATE_LANGUAGE控制返回的月份和日份所使用的语言。如果x为数字nlsparm=NLS_NUMERIC_CHARACTERS用来指定小数位和千分位的分隔符，以及货币符号。NLS_NUMERIC_CHARACTERS=dg,NLS_CURRENCY=string,.,oracle基础常用函数,转换函数TO_DATE(X,c2,c3)【功能】将字符串X转化为日期型【参数】c2,c3,字符型，参照to_char()TO_NUMBER(X,c2,c3)【功能】将字符串X转化为数字型【参数】c2,c3,字符型，参照to_char(),.,oracle基础常用函数,聚组函数AVG(distinct|allx)【功能】统计数据表选中行x列的平均值。SUM(distinct|allx)【功能】统计数据表选中行x列的合计值。count(*|distinct|allx)【功能】统计数据表选中行x列的合计值。【参数】*表示对满足条件的所有行统计，不管其是否重复或有空值(NULL)all表示对所有的值统计,默认为alldistinct只对不同的值统计，如果有参数distinct或all，需有空格与x(列)隔开，均忽略空值(NULL)。MAX(distinct|allx)【功能】统计数据表选中行x列的最大值。MIN(distinct|allx)【功能】统计数据表选中行x列的最大值。,.,oracle基础常用函数,分析函数RANK()dense_rank()【语法】RANK()OVER(query_partition_clauseorder_by_clause)dense_RANK()OVER(query_partition_clauseorder_by_clause)【功能】聚合函数RANK和dense_rank主要的功能是计算一组数值中的排序值。【参数】dense_rank与rank()用法相当，【区别】dence_rank在并列关系是，相关等级不会跳过。rank则跳过rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）dense_rank()l是连续排序，有两个第二名时仍然跟着第三名。,.,oracle基础常用函数,其他函数NVL(expr1,expr2)【功能】若expr1为NULL，返回expr2；expr1不为NULL，返回expr1。注意两者的类型要一致decode(条件,值1,翻译值1,值2,翻译值2,.值n,翻译值n,缺省值)【功能】根据条件返回相应值【参数】c1,c2,.,cn,字符型/数值型/日期型，必须类型相同或null注：值1n不能为条件表达式,这种情况只能用casewhenthenend解决含义解释：decode(条件,值1,翻译值1,值2,翻译值2,.值n,翻译值n,缺省值)该函数的含义如下：IF条件=值1THENRETURN(翻译值1)ELSIF条件=值2THENRETURN(翻译值2).ELSIF条件=值nTHENRETURN(翻译值n)ELSERETURN(缺省值)ENDIF,.,oracle基础常用函数,其他函数casewhenthenwhenthenelseend【功能】当：时，返回对应当不为条件表达式时，与函数decode()相同，decode(,)【参数】默认为true(逻辑型)类型要与类型一致，若为字符型，则也要为字符型【注意点】1、以CASE开头，以END结尾2、分支中WHEN后跟条件，THEN为显示结果3、ELSE为除此之外