当前位置：文档库 › Oracle数据库SqlLoad常用技巧总结

Oracle数据库SqlLoad常用技巧总结

1、控制文件中注释用“--”。

2、为防止导入出现中文乱码，在控制文件中加入字符集控制

1.LOAD DATA

2.CHARACTERSET ZHS16GBK

3、让某一列成为行号，用RECNUM关键字

1.load data

2.infile *

3.into table t

4.replace

5.( seqno RECNUM //载入每行的行号

6.text Position(1:1024))

7.BEGINDATA

8.fsdfasj

4、过滤某一列，用FILLER关键字

1.LOAD DATA

2.TRUNCATE INTO TABLE T1

3.FIELDS TERMINATED BY ','

4.( field1,

5.field2 FILLER,

6.field3

7.)

5、过滤行

在INTO TABLE table_name后加WHEN过滤条件，但功能有限，如果以竖线分隔符的文件，不能实现字段级的过滤，定长的还好。

1.LOAD DATA

2.INFILE 'mydata.dat'

3.BADFILE 'mydata.bad'

4.DISCARDFILE 'mydata.dis'

5.APPEND

6.INTO TABLE my_selective_table

7.WHEN (01) <> 'H' and (01) <> 'T' and (30:37) = '20031217'

8.(

9.region CONSTANT '31',

10.service_key POSITION(01:11) INTEGER EXTERNAL,

11.call_b_no POSITION(12:29) CHAR

12.)

6、过滤首行，用OPTIONS (SKIP 1)选项，也可以写在命令行中，如：

sqlldr sms/admin control=test.ctl skip=1

7、TRAILING NULLCOLS的使用，作用是表的字段没有对应的值时允许为空

如：

1.LOAD DATA

2.INFILE *

3.INTO TABLE DEPT

4.REPLACE

5.FIELDS TERMINATED BY ','

6.TRAILING NULLCOLS // 其实下面的ENTIRE_LINE在BEGINDATA后面的数据中是没有直接对应的列的值的如果第一行改为 10,Sales,Virginia,1/5/2000,, 就不用TRAILING NULLCOLS

了

7.(DEPTNO,

8.DNAME "upper(:dname)", // 使用函数

9.LOC "upper(:loc)",

https://www.wendangku.net/doc/d83297079.html,ST_UPDATED date 'dd/mm/yyyy', // 日期的一种表达方式还有'dd-mon-yyyy' 等

11.ENTIRE_LINE ":deptno||:dname||:loc||:last_updated"

12.)

13.BEGINDATA

14.10,Sales,Virginia,1/5/2000

15.20,Accounting,Virginia,21/6/1999

16.30,Consulting,Virginia,5/1/2000

17.40,Finance,Virginia,15/3/2001

8、添加、修改数据

（1）、

1.LOAD DATA

2.INFILE *

3.INTO TABLE tmp_test

4.( rec_no "my_db_sequence.nextval",

5.region CONSTANT '31',

6.time_loaded "to_char(SYSDATE, 'HH24:MI')",

7.data1 POSITION(1:5) ":data1/100",

8.data2 POSITION(6:15) "upper(:data2)",

9.data3 POSITION(16:22)"to_date(:data3, 'YYMMDD')"

10.)

11.BEGINDATA

12.11111AAAAAAAAAA991201

13.22222BBBBBBBBBB990112

（2）、

1.LOAD DATA

2.INFILE 'mail_orders.txt'

3.BADFILE 'bad_orders.txt'

4.APPEND

5.INTO TABLE mailing_list

6.FIELDS TERMINATED BY ","

7.( addr,

8.city,

9.state,

10.zipcode,

11.mailing_addr "decode(:mailing_addr, null, :addr, :mailing_addr)",

12.mailing_city "decode(:mailing_city, null, :city, :mailing_city)",

13.mailing_state

14.)

9、合并多行记录为一行记录

通过关键字concatenate 把几行的记录看成一行记录：

1.LOAD DATA

2.INFILE *

3.concatenate 3 // 通过关键字concatenate 把几行的记录看成一行记录

4.INTO TABLE DEPT

5.replace

6.FIELDS TERMINATED BY ','

7.(DEPTNO,

8.DNAME "upper(:dname)",

9.LOC "upper(:loc)",

https://www.wendangku.net/doc/d83297079.html,ST_UPDATED date 'dd/mm/yyyy'

11.)

12.BEGINDATA

13.10,Sales, // 其实这3行看成一行 10,Sales,Virginia,1/5/2000

14.Virginia,

15.1/5/2000

10、用”|+|”分隔符，避免数据混淆：fields terminated by "|+|"

11、如果数据文件包含在控制文件中，用INFILE *

如下：

1.LOAD DATA

2.INFILE *

3.append

4.INTO TABLE tmp_test

5.FIELDS TERMINATED BY ","

6.OPTIONALLY ENCLOSED BY '"'

7.TRAILING NULLCOLS

8.( data1,

9.data2

10.)

11.BEGINDATA

12.11111,AAAAAAAAAA

13.22222,"A,B,C,D,"

12、一次导入多个文件到同一个表

1.LOAD DATA

2.INFILE file1.dat

3.INFILE file2.dat

4.INFILE file3.dat

5.APPEND

6.INTO TABLE emp

7.( empno POSITION(1:4) INTEGER EXTERNAL,

8.ename POSITION(6:15) CHAR,

9.deptno POSITION(17:18) CHAR,

10.mgr POSITION(20:23) INTEGER EXTERNAL

11.)

13、将一个文件导入到不同的表

（1）、

1.LOAD DATA

2.INFILE *

3.INTO TABLE tab1 WHEN tab = 'tab1'

4.( tab FILLER CHAR(4),

5.col1 INTEGER

6.)

7.INTO TABLE tab2 WHEN tab = 'tab2'

8.( tab FILLER POSITION(1:4),

9.col1 INTEGER

10.)

11.BEGINDATA

12.tab1|1

13.tab1|2

14.tab2|2

15.tab3|3

16.==============

（2）、

1.LOAD DATA

2.INFILE 'mydata.dat'

3.REPLACE

4.INTO TABLE emp

5.WHEN empno != ' '

6.( empno POSITION(1:4) INTEGER EXTERNAL,

7.ename POSITION(6:15) CHAR,

8.deptno POSITION(17:18) CHAR,

9.mgr POSITION(20:23) INTEGER EXTERNAL

10.)

11.INTO TABLE proj

12.WHEN projno != ' '

13.( projno POSITION(25:27) INTEGER EXTERNAL,

14.empno POSITION(1:4) INTEGER EXTERNAL

15.)

14、过滤掉的数据文件路径指定

1./opt/app/oracle/product/10.

2.0/bin/sqlldr APS/APS control=/home/oracle/APS_LOAD/ctl/AP_CONTRACT.CTL LOG=/home/oracle/APS_LOAD/log/$yesterday/AP_CONT

RACT_$yesterday.log bad=/home/oracle/APS_LOAD/bad/DUE_BILL_$yesterday.bad rows=10000readsize=20000000bindsize=20000000DISCARD=/home/oracle/APS_LOAD/bad /discard_ts.dis

15、附：测试用控制文件

1.LOAD DATA

2.INFILE '/home/oracle/APS_LOAD/dat/APS_AP_CONTRACT.dat'

3.TRUNCATE

4.INTO TABLE AP_CONTRACT

5.WHEN (01)<>'1'

6.FIELDS TERMINATED BY "|"

7.TRAILING NULLCOLS

8.(

9.AGMT_NO "(TRIM(:AGMT_NO ))",

10.CONTRACT_NO FILLER, -- "(TRIM(:CONTRACT_NO ))",

11.LOAN_AMT "(TRIM(:LOAN_AMT ))",

12.AGMT_HOLDER "(TRIM(:AGMT_HOLDER ))",

13.LOAN_TYPE_CD "(TRIM(:LOAN_TYPE_CD ))",

14.CURR_CD "(TRIM(:CURR_CD ))",

15.BALANCE "(TRIM(:BALANCE ))",

16.LOAN_DIRC_CD "(TRIM(:LOAN_DIRC_CD ))",

17.AGMT_START_DATE "(TRIM(:AGMT_START_DATE ))",

18.AGMT_END_DATE "(TRIM(:AGMT_END_DATE ))",

19.AGMT_BELONG_ORG_NO "(TRIM(:AGMT_BELONG_ORG_NO ))",

20.MANAGER_NO "(TRIM(:MANAGER_NO ))",

21.PROCESS_RATE "(TRIM(:PROCESS_RATE ))",

22.INSURE_METH_TYPE_CD "(TRIM(:INSURE_METH_TYPE_CD ))",

23.AGMT_SIGN_DATE "(TRIM(:AGMT_SIGN_DATE ))",

24.LOAN_PROP_CD "(TRIM(:LOAN_PROP_CD ))",

25.LOAN_USE_TYPE "(TRIM(:LOAN_USE_TYPE ))",

26.ENTRUST_LOAN_FLAG "(TRIM(:ENTRUST_LOAN_FLAG ))",

27.ENTRUST_NAME "(TRIM(:ENTRUST_NAME ))",

28.FARM_LOAN_FLAG "(TRIM(:FARM_LOAN_FLAG ))",

29.FARM_LOAN_TYPE_CD "(TRIM(:FARM_LOAN_TYPE_CD ))",

30.LOAN_BIZ_TYPE_CD "(TRIM(:LOAN_BIZ_TYPE_CD ))",

31.ID_TEST RECNUM ,

32.CHAR_TEST CONSTANT '31',

33.SQ "sqlldr.nextval",

34.TEST_4 "TO_CHAR(SYSDATE,'YYYYMMDD HH24:MI:SS')",

35.TEST_5 "(TRIM(:LOAN_BIZ_TYPE_CD)||'---'||TRIM(:AGMT_NO))"

36.)

来源：网络编辑：联动北方技术论坛

oracle常用命令及格式

oracle常用命令及格式一：关于日志管理的 1.切换日志：sql> alter system switch logfile; 2.切换checkpoints：sql> alter system checkpoint; 3.增加日志组：sql> alter database add logfile [group 4] sql> ('/disk3/log4a.rdo','/disk4/log4b.rdo') size1m; 4.增加日志成员 sql> alter database add logfile member sql> '/disk3/log1b.rdo' to group 1, sql> '/disk4/log2b.rdo' to group 2; 5.改变日志文件名字或路径 sql> alter database rename file 'c:/oracle/oradata/oradb/re do01.log' sql> to 'c:/oracle/oradata/redo01.log'; （此处注意，那个文件路径的输入格式） 6.删除日志文件组：sql> alter database drop logfile group 3;

7.删除日志文件成员 sql> alter database drop logfile member 'c:/oracle/oradata/ redo01.log'; 8.清除日志文件内容 sql> alter database clear [unarchived] logfile 'c:/oracle/l og2a.rdo'; 二、关于表空间管理的 1.创建表空间 sql> create tablespace tablespace_name datafile 'c:\oracle\ oradata\file1.dbf' size100m, sql> 'c:\oracle\oradata\file2.dbf' size100mminimum extent 5 50k [logging/nologging] sql> default storage (initial 500k next 500k maxextents 500 pctinccease 0) sql> [online/offline] [permanent/temporary] [extent_managem ent_clause]

(Oracle数据库管理)玩转实战教程学习笔记最全版

（O管理）玩转实战教程(韩顺平)学习笔记

Oracle SQLPlus 常用命令及解释

Oracle SQLPlus 常用命令及解释 1.@ 执行位于指定脚本中的SQLPlus语句。可以从本地文件系统或Web服务器中调用脚本。可以为脚本中的变量传递值。在iSQL*Plus中只能从Web服务器中调用脚本。 2.@@ 执行位于指定脚本中的SQL*Plus语句。这个命令和@（“at”符号）命令功能差不多。在执行嵌套的命令文件时它很有用，因为它会在与调用它的命令文件相同的路径或url中查找指定的命令文件。在iSQL*Plus中只支持url形式。 3./ 执行保存在SQL缓冲区中的最近执行的SQL命令或PL/SQL块。在SQL*Plus命令行中，可在命令提示符或行号提示符使用斜线（/）。也可在iSQL*Plus的输入区中使用斜线（/）。斜线不会列出要执行的命令。 4.ACCEPT 可以修改既有变量，也可定义一个新变量并等待用户输入初始值,读取一行输入并保存到给出的用户变量中。ACCEPT在iSQL*Plus中不可用。 5.APPEND 把指定文本添加到SQL缓冲区中当前行的后面。如果text的最前面包含一个空格可在APPEND和text间输入两个空格。如果text的最后是一个分号，可在命令结尾输入两个分号（SQL*Plus会把单个的分号解释为一个命令结束符）。APPEND 在iSQL*Plus中不可用。 6.ARCHIVE LOG 查看和管理归档信息。启动或停止自动归档联机重做日志，手工（显示地）归档指定的重做日志，或者显示重做日志文件的信息。 7.ATTRIBUTE 为对象类型列的给定属性指定其显示特性，或者列出单个属性或所有属性的当前显示特性。 8.BREAK 分开重复列。指定报表中格式发生更改的位置和要执行的格式化动作（例如，在列值每次发生变化时跳过一行）。只输入BREAK而不包含任何子句可列出当前的BREAK定义。 9.BTITLE 在每个报表页的底部放置一个标题并对其格式化，或者列出当前BTITLE定义。

oracle笔记(3)

1.用insert语句进行行数据的迁移 Insert into 表名(列，列) select 列，列from emp where 条件 2.用update更新数据(使用子查询)：希望员工scott的岗位，工资，补助与smith员工一样？ update emp set (job,sal,comm)=(select job,sal,comm. From emp where ename=?SMITH?) where ename=?SCOTT?; 3.事务 (1)锁 (2)提交事务 (3)回退事务（一开始就做保存点(savepoint)） (4)事务的几个重要操作 1)设置保存点:savepoint a 2)取消部分事务:rollback to a 3)取消全部事务:rollback (5)只读事务:set transaction read only（当前用户能看到之前的操作，若其他用户进行更新，该用户之后的操作都不能看到更新） (6)字符函数 (1)lower(char):将字符串转化为小写的格式 (2)upper(char): 将字符串转化为大写的格式 (3)length(char):返回字符串的长度 (4)substr(char,m,n):取字符串的子串，从m开始取，取n个 (5)例：以首字符大写的方式显示所有的员工的姓名: (1)完成首字母大写：select upper(substr(ename,1,1)) from emp; (2)完成后面字母小写：select lower(substr(ename,2,length(ename)-1)) from emp; (3)合并:select upper(substr(ename,1,1)) || lower(substr(ename,2,length(ename)-1)) from emp; (6)replace(char1,search_string,replace_string) (7)instr(char1,char2,[,n[,m]])—取子串在字符串的位置 (8)例：显示所有员工的姓名，用”我是A”替换所有”A”:select replace(ename,?A?,? 我是A?) from emp; (7)数学函数 (1)round(n,[m]):用于四舍五入 (2)trunc(n,[m]):用于截取数字 (3)mod(m,n)：取模 (4)floor(n)：返回<=n的最大整数 (5)ceil(n):返回>=n的最小整数 (6)例：显示在一个月为30天的情况所有员工的日薪金，忽略余数:select floor(sal/30),ename from emp; (8)日期函数 (1)sysdate (2)add_months(d,n) (3)last_day(d):返回指定日期所在月份的最后一天例：返回在八个月前雇佣的员工(已经入职8个月多的员工):select * from emp where sysdate>add_months(hiredate,300); 例：对于每个员工，显示其加入公司的天数:select trunk(sysdate-hiredate) “入职天

ORACLE笔记

1.Oracle 工具:sqlplus Sqlplus / as sysdba Shutdown immediate(关闭数据库) Startup(启动数据库) 注意：数据库开启才可以进行操作 Select username,account_status from dba_users;(查询数据库中所有的用户名称与用户状态) Alter user scott identified by tiger account unlock;(scott用户解锁) Sqlplus scott/tiger(使用SCOTT 密码为tiger登陆ORACLE数据库) Connect scott/tiger (在SQL>中直接使用SCOTT用户连接数据库) Show all;(看所有变量) Set sqlprompt “_user>”(设置sqlplus环境下面的提示符用自身用户显示) 注意：如要变量下次重启生效必须把变量写入 /u01/app/oracle/product/10.2.0/db_1/sqlplus/admin/glogin.sql中 Host(回到系统) Help index（查看所有命令） Help+命令(类似于LINUX中的MAN,查看命令的格式与用法) Disconnect(关闭SQLPLUS工具但是不离开数据库) Describe emp(查看EMP表) Define(定义变量,常用与写脚本用) 如：DEFINE _EDITOR =”vi” (CHAR) (定义VI编辑器用于保存最后一条执行的SQL语句) Save /u01/app/oracle/aa.sql (保存SQL语句) Get /u01/app/oracle/aa.sql (调用保存的SQL语句) 2.select（select 查询语句是ORACLE中最常用的语句） DML语句包括（insert 写入，update改变，delect删除，merge两张表同步）注意：merge常用在ETL（数据仓库）底下 DDL数据定义语句包括（create创建对象，alter修改，drop删除对象，rename改名，truncate 删除整个表） TCL事务处理语句（commit提交立刻生效，rollback回滚，savepoint保存点，savepoint(保存点) DCL权限语句（grant赋予权限，revoke(收回权限) ORACLE的表称之为堆表（keep table） SELECT (1) writing basic SQL select statemanes 1. selecting all columns SCOTT>select * from dept; SCOTT>select dname,deptno,loc from dept; Basic SELECT Statement SELECT *|{[DISTINCT] column|expression [alias],...} FROM table;

oracle数据库索引的理解与总结

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。例如这样一个查询：SELECT * FROM TABLE1 WHERE ID = 44。如果没有索引，必须遍历整个表，直到ID等于44的这一行被找到为止；有了索引之后(必须是在ID这一列上建立的索引)，直接在索引里面找44（也就是在ID这一列找），就可以得知这一行的位置，也就是找到了这一行。可见，索引是用来定位的。建立索引的目的是加快对表中记录的查找或排序。为表设置索引要付出代价的：一是增加了数据库的存储空间，二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。虽然建立索引能加快对表中记录的查询或者排序速度，但是并不是索引建得越多越好，这就需要我们了解使用索引过程中，索引的一些优点以及缺陷：使用索引的好处：创建索引可以大大提高系统的性能：第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。使用索引的一些不足：第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。索引是建立在数据库表中的某些列的上面。在创建索引的时候，应该考虑在哪些列上可以创建索引，在哪些列上不能创建索引。一般来说，应该在这些列上创建索引： ?在经常需要搜索的列上，可以加快搜索的速度；在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构； ?在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度； ?在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的； ?在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间； ?在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。同样，对于有些列不应该创建索引。一般来说，不应该创建索引的的这些列具有下列特点： ?对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。 ?对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了

Oracle基本语法

Oracle的历史 ?Oracle 公司( 甲骨文) 创始人: Larry Ellison 32岁,公司提供数据库服务. ?公司成立于1977 年, 83 年公司更名为Oracle ,原名为”软件开发实验室”. ?Oracle 数据库适用于大型企业 ?竞争对手 –微软的SQLServer –IBM 的DB2 ?目前的版本 – 2.0~7.0 , 8.0 , 8i , 9i , 10g Oracle的服务：我的电脑右键选择管理--服务和应用程序—服务 -----是数据库或例程的系统标识符 ------是Oracle主目录名称这几个服务之间的关系：启动顺序：1、OracleTNSListener必须启动 2、OracleServer必须启动 3、OracleDBConsole启动依赖于OracleServer SqlPlus SqlPlus是Oracle任何版本都自带的数据库操作工具，使用它可以完成大部分的数据库操作。 SqlPlus可以“开始→程序→Oracle”启动，也可以命令行启动（互动） 1.命令行启动sqlPlus sqlplus 用户名/密码@orcl

或 sqlplus 用户名@orcl 如果用户是管理员要在sqlplus 用户名/密码@主机字符串as sysdba “/”是用户名和密码分隔符号 “@”是密码和数据库的分隔符号 “orcl”是数据库的名称，在安装时指定常用命令（互动） connect 切换用户 show user 显示当前用户 set linesize 1000 设置行显示长度 set pagesize 1000 设置分页长度 desc dept 查看表结构 select table_name from user_tables 查询当前用户的表 / 运行上一条SQL语句 clear screen 清除屏幕 edit 编辑 spool d:/a 保存输出结果到某个位置 spool off 保存结束 quit 退出 list 查看最后一条语句 @ 文件名.sql 运行外部文件中的SQL语句

学习oracle数据库的总结(图文整理)

1、在开发环境中连接到数据库，进行基本的select查询操作； 2、熟悉plsql的使用； 3、熟悉sqlplus相关命令（登录、查询、导入导出等）登录sqlplus：第一：使用dos窗口登录sqlplus Sqlplus 用户名/密码@数据库实例名 Sqlplus system/密码@数据库实例名as sysdba 第二：使用oracle自带的一个sqlplus登录，提供界面，显得更简单一些。显示当前用户名：show user; 创建一个用户：create user 用户名identified by 密码；给用户赋予登录的权限：grant connect to 用户名；（此时才可以使用这个用户来登录这个数据库。）给其赋予dba的权限。修改用户的密码：alter user 用户名identified by 新密码；查询： Select * from t_user; Select id from t_user; Select name from t_user; Select birthday from t_user; Select id,name from t_user; 导入导出：导出表: （注意，导出表的exp命令不是在sqlplus下使用的，是在dos窗口下使用的命令。） exp userid=test/sa@test tables=(qx) file=d:\e1223.dmp exp userid=test/sa@test tables=(t_user,qx) file=f:\test.dmp 导出方案： Exp userid=test/sa@test owner=test file=f:\test2.dmp 导出数据库： Exp userid=test/sa@test full=y inctype=complete file=f:\all.dmp 导入表：下面以一个例子来说明：看下面的图，我的用户名test，密码sa，数据库实例名test，所有的表都在这里现在我执行导出表JD的操作：exp userid=test/sa@test tables=(jd) file=f:\jd.dmp 在我的f盘下就出现了这么一个.dmp文件

Oracle数据库试题

constraint pk_spj primary key (sno,pno,jno), constraint fk_spj_sno foreign key (sno) references s(sno), constraint fk_spj_pno foreign key (pno) references p(pno), constraint fk_spj_jno foreign key (jno) references j(jno) 实验二游标和函数 1、定义一个游标完成显示所有供应商名。 declare v_sname s.sname%type; cursor cursor_sname is select sname from s; begin for curso in cursor_sname loop dbms_output.put_line(curso.sname); end loop; end; 2、定义、调用一个简单函数：查询返回指定供应商编号的供应商名及其供应零件总数量。create or replace function fun(f_sno in s.sno%type,f_sname out s.sname%type) return number as f_qty number; begin SELECT s.sname,sum(qty) into f_sname,f_qty from s,spj WHERE s.sno=spj.sno GROUP BY s.sname,spj.sno having spj.sno=f_sno; return f_qty; end; declare v_sno s.sno%type:='&sno'; v_sname s.sname%type; v_qty spj.qty%type; begin v_qty:=fun(v_sno,v_sname); dbms_output.put_line(v_sname||v_qty); end; 3、定义一个函数：对于给定的供应商号，判断是否存在，若存在返回0，否则返回-1。写一段程序调用此函数，若供应商号存在则在spj插入一元组。

oracle中常用函数大全

oracle中常用函数大全 1、数值型常用函数函数返回值样例显示 ceil(n) 大于或等于数值n的最小整数select ceil(10.6) from dual; 11 floor(n) 小于等于数值n的最大整数select ceil(10.6) from dual; 10 mod(m,n) m除以n的余数,若n=0,则返回m select mod(7,5) from dual; 2 power(m,n) m的n次方select power(3,2) from dual; 9 round(n,m) 将n四舍五入,保留小数点后m位select round(1234.5678,2) from dual; 1234.57 sign(n) 若n=0,则返回0,否则,n>0,则返回1,n<0,则返回-1 select sign(12) from dual; 1 sqrt(n) n的平方根select sqrt(25) from dual ; 5 2、常用字符函数 initcap(char) 把每个字符串的第一个字符换成大写select initicap('mr.ecop') from dual; Mr.Ecop lower(char) 整个字符串换成小写select lower('MR.ecop') from dual; mr.ecop replace(char,str1,str2) 字符串中所有str1换成str2 select replace('Scott','s','Boy') from dual; Boycott substr(char,m,n) 取出从m字符开始的n个字符的子串select substr('ABCDEF',2,2) from dual; CD length(char) 求字符串的长度select length('ACD') from dual; 3 || 并置运算符select 'ABCD'||'EFGH' from dual; ABCDEFGH 3、日期型函数 sysdate当前日期和时间select sysdate from dual;

oracle学习笔记

●PLSQL控制台输出语句 SET serveroutput ON; --打开控制台输出服务 dbms_output.put_line('values2='||var_val); --输出语句 ●PLSQL动态变量 var_str := '&input'; ●创建表空间和用户 --创建表空间 CREATE TABLESPACE "BCPBS" LOGGING DATAFILE 'D:\app\E430\oradata\orcl\BCPBS_01.ora' SIZE 2048M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED, 'D:\app\E430\oradata\orcl\BCPBS_02.ora' SIZE 2048M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ; --建立用户 CREATE USER "BCPBS" PROFILE "DEFAULT" IDENTIFIED BY "bcpbs123" DEFAULT TABLESPACE "BCPBS" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK; GRANT "CONNECT" TO "BCPBS"; GRANT "DBA" TO "BCPBS"; GRANT "RESOURCE" TO "BCPBS"; ●删除表空间和用户 drop user bcpbs cascade ; drop tablespace BCPBS including contents and datafiles cascade constraints ; ●自定义函数 CREATE OR REPLACE FUNCTION fun_level_value(level_value number) RETURN number IS return_value number:=null; BEGIN CASE level_value WHEN 0 THEN return_value:='0'; WHEN 1 THEN return_value:='1'; WHEN 2 THEN return_value:='2'; WHEN 3 THEN return_value:='3'; WHEN 6 THEN return_value:='8'; ELSE

oracle学习心得体会

oracle学习心得一、sqlserver的理解 sqlserver服务器就像一栋大楼，大楼里的机房就像服务器的数据库，机房里的电脑如同数据库里的表 1、登录用户可以登录服务器——可以进大楼 2、登录用户成为数据库用户才能进指定的数据库——进入大楼的人给了某个机房的钥匙才能进入机房 3、登录用户有权限使用表——进入机房的人有电脑的密码才能使用电脑二、oracle的理解 oracle服务器（全局数据库）就像一个商场，商场的每一家公司是表空间，公司的业务是表 1、数据库由多个表空间组成——商场里有多家公司组成 2、表空间由段组成——公司要有自己的经营业务，可以只有一个业务，就是一个表空间中只有一个段，可以有多个业务，就是一个表空间有多个段 3、段由区组成——单个业务的细分类别。例如有家公司经营三个业务，卖书，卖家电，

卖衣服，则每个业务就是一个段。而每个业务又有细分，比如卖书的话要进行分类了。计算机区，人文区，小说区等，每一区都要放上书架存放书籍，则书架就是oracle块，存放数据的三、数据库，表空间，用户（指定默认表空间），表统统由管理员管理四、在oem中管理数据库的步骤 1、创建 1)存储——表空间——创建表空间(tomspace)（类似于在sql中创建数据库，通常可以省略，使用默认表空间为users,,临时表空间为temp） 2)首先展开安全性——创建新用户（tom，指定表空间）（类似于在sql中指定数据库用户） 3)创建表——指定方案（用户）和表空间（列名不要带<>） 4)设置约束 5)输入信息：方案——用户名——表——右击——查看/编辑目录… 2、修改 1）方案——用户名——表 2）修改表结构，添加约束

ORACLE数据库学习心得

ORACLE数据库结课论文一个好的程序，必然联系着一个庞大的数据库网路... 今年我们学习了oracle数据库这门课程，起初的我，对这个字眼是要多陌生有多陌生，后来上课的时候听一会老师讲课，偶尔再跟上上机课，渐渐的学会了不少东西，但我感觉，我学到的仍是一些皮毛而已，怀着疑惑和求知的心态，我在网上搜索了关于oracle数据库的一些知识。 1.ORACLE的特点: 可移植性ORACLE采用C语言开发而成，故产品与硬件和操作系统具有很强的独立性。从大型机到微机上都可运行ORACLE的产品。可在UNIX、DOS、Windows等操作系统上运行。可兼容性由于采用了国际标准的数据查询语言SQL，与IBM的SQL/DS、DB2等均兼容。并提供读取其它数据库文件的间接方法。可联结性对于不同通信协议，不同机型及不同操作系统组成的网络也可以运行ORAˉCLE数据库产品。 2.ORACLE的总体结构（1）ORACLE的文件结构一个ORACLE数据库系统包括以下5类文件:ORACLE RDBMS的代码文件。数据文件一个数据库可有一个或多个数据文件，每个数据文件可以存有一个或多个表、视图、索引等信息。日志文件须有两个或两个以上，用来记录所有数据库的变化，

用于数据库的恢复。控制文件可以有备份，采用多个备份控制文件是为了防止控制文件的损坏。参数文件含有数据库例程起时所需的配置参数。（2）ORACLE的内存结构一个ORACLE例程拥有一个系统全程区（SGA）和一组程序全程区（PGA）。 SGA（System Global Area）包括数据库缓冲区、日志缓冲区及共享区域。 PGA（Program Global Area）是每一个Server进程有一个。一个Server进程起动时，就为其分配一个PGA区，以存放数据及控制信息。（3）ORACLE的进程结构ORACLE包括三类进程: ①用户进程用来执行用户应用程序的。 ②服务进程处理与之相连的一组用户进程的请求。 ③后台进程ORACLE为每一个数据库例程创建一组后台进程，它为所有的用户进程服务，其中包括: DBWR（Database Writer）进程，负责把已修改的数据块从数据库缓冲区写到数据库中。LGWR（Log Writer）进程，负责把日志从SGA中的缓冲区中写到日志文件中。 SMON（System Moniter）进程，该进程有规律地扫描SAG进程信息，注销失败的数据库例程，回收不再使用的内存空间。PMON （Process Moniter）进程，当一用户进程异常结束时，该进程负责恢复未完成的事务，注销失败的用户进程，释放用户进程占用的资源。

Oracle学习笔记

Oracle的四个主要用户 1.超级管理员sys/change_on_install 2.普通管理员system/manager 3.普通用户scott/tiger 4.大数据用户sh/sh 简单查询 1.查询表结构 DESC 表名 2.简单查询SELECT [DISTINCT] *|[字段 [别名]] [,字段 [别名]] … FROM 表名 [别名] （1）查询时也可以使用四则运算，如：SELECT (字段*x)+y FROM 表名（2）可以使用“||”连接字段与字符串，如： select '工作与工资：' || job || sal 工作工资 from emp 执行结果： 3.限定查询SELECT [DISTINCT] *|[字段 [别名]] [,字段 [别名]] … FROM 表名 [别名] [WHERE 条件(s)] （1）Where字句后可以增加多个条件，最常见的条件就是最基本关系运算：>、<、>=、<=、!=(<>)、BE TWEEN…AND、LIKE、IN(指定范围)、IS NULL(为空)、AND(且)、OR(或)、NOT(非)。（2）多个条件可以使用AND与OR连接。例如：select * from emp where sal>1300 and sal<4000 （3）范围判断BE TWEEN…AND：BETWEEN 最小值 AND 最大值（大于等于最小值，小于等于最大值），BE TWEEN…AND 对数字、日期都可以使用！！！例如：select * from emp where sal between 1300 and 4000 例如：select * from emp where hiredate between '01-1月-1981' and '31-12月-1981' （4）判断为空IS (NOT) NULL 例如：select * from emp where comm is null 例如：select * from emp where comm is not null 例如：select * from emp where not comm is null （5）指定范围的判断(NOT) IN 例如：select * from emp where empno in(7369,7566,7799)

Oracle数据库期末复习知识点整理

基础知识表3.2 Oracle数据类型

表3.3 XSB的表结构

操作表创建表 CREATE TABLE [schema.] table_name ( column_namedatatype [DEFAULT expression] [column_constraint][,…n] [,…n] ) [PCTFREE integer] [PCTUSED integer] [INITRANS integer] [MAXTRANS integer] [TABLESPACE tablespace_name] [STORGE storage_clause] [CLUSTER cluster_name(cluster_column,…n)] [ENABLE | DISABLE ] [AS subquery] 【例】使用CRETE TABLE命令为XSCJ数据库建立表XSB，表结构参照表3.3。打开SQL*Plus工具，以system方案连接数据库，输入以下语句： CREATE TABLE XSB ( XH char(6) NOT NULL PRIMARY KEY, XM char(8) NOT NULL, XB char(2) DEFAULT '1' NOT NULL, CSSJ date NOT NULL, ZY char(12) NULL, ZXF number(2) NULL, BZ varchar2(200) NULL ); 修改表 ALTER TABLE [schema.] table_name [ ADD(column_namedatatype [DEFAULT expression][column_constraint],…n) ] /*增加新列*/ [ MODIFY([ datatype ] [ DEFAULT expression ] [column_constraint],…n) ] /*修改已有列的属性*/ [ STORAGE storage_clause ] *修改存储特征*/ [ DROP drop_clause ] /*删除列或约束条件*/ 【例】使用ALTER TABLE语句修改XSCJ数据库中的表。

PL_SQL命令的使用大全

SQL*PLUS命令的使用大全[zt] Oracle的sql*plus是与oracle进行交互的客户端工具。在sql*plus中，可以运行sql*plus命令与sql*plus语句。我们通常所说的DML、DDL、DCL语句都是sql*plus语句，它们执行完后，都可以保存在一个被称为sql buffer的内存区域中，并且只能保存一条最近执行的sql语句，我们可以对保存在sql buffer中的sql 语句进行修改，然后再次执行，sql*plus一般都与数据库打交道。除了sql*plus语句，在sql*plus中执行的其它语句我们称之为sql*plus命令。它们执行完后，不保存在sql buffer的内存区域中，它们一般用来对输出的结果进行格式化显示，以便于制作报表。下面就介绍一下一些常用的sql*plus命令： 1. 执行一个SQL脚本文件 SQL>start file_name SQL>@ file_name 我们可以将多条sql语句保存在一个文本文件中，这样当要执行这个文件中的所有的sql语句时，用上面的任一命令即可，这类似于dos中的批处理。 2. 对当前的输入进行编辑 SQL>edit 3. 重新运行上一次运行的sql语句 SQL>/ 4. 将显示的内容输出到指定文件 SQL> SPOOL file_name 在屏幕上的所有内容都包含在该文件中，包括你输入的sql语句。 5. 关闭spool输出 SQL> SPOOL OFF 只有关闭spool输出，才会在输出文件中看到输出的内容。 6．显示一个表的结构 SQL> desc table_name 7. COL命令：主要格式化列的显示形式。该命令有许多选项，具体如下： COL[UMN] [{ column|expr} [ option ...]] Option选项可以是如下的子句: ALI[AS] alias CLE[AR] FOLD_A[FTER] FOLD_B[EFORE] FOR[MA T] format HEA[DING] text JUS[TIFY] {L[EFT]|C[ENTER]|C[ENTRE]|R[IGHT]} LIKE { expr|alias} NEWL[INE] NEW_V[ALUE] variable NOPRI[NT]|PRI[NT] NUL[L] text OLD_V[ALUE] variable ON|OFF WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED] 1). 改变缺省的列标题

oracle数据库学习笔记心得

select* |字段表名 from 表名 where 布尔表达式【条件】 externalcandidate 职员相关信息 contractrecruiter 猎头公司查看表里所有字段 describe[描述] contractrecruiter select cname,nperecentagecharge from contractrecruiter 不显示原来名字，显示现在名字【用AS】 select cname as "Recruiter Name",nperecentagecharge as "Hire Fees" from contractrecruiter 不显示原来名字，显示现在名字【可不用AS】 select cname "Recruiter Name",nperecentagecharge "Hire Fees" from contractrecruiter 【双引号是否写】双引号是为了区分大小写职位表 position select *from position 缺额运算:[想减] select VDESCRIPTION , nbudgetedstrength, NBUDGETEDSTRENGTH -NCURRENTSTRENGTH , NYEAR from position

select VDESCRIPTION "Potion", nbudgetedstrength "Budgeted Strength" , NBUDGETEDSTRENGTH -NCURRENTSTRENGTH "Vacancies", NYEAR "Year" from position 显示非重复运行查询来源地的人数 describe externalcandidate 查看职员城市名字 select ccity from externalcandidate 查看职员城市名字【名字不重复，插入一个关键字：distinct】select distinct ccity,cstate from externalcandidate 运算符：两列 select vfirstname,vlastname from externalcandidate 字段拼接 select vfirstname||vlastname from externalcandidate select vfirstname||' '||vlastname from externalcandidate 学校 describe college 查看所有学校 select *from college 查看只是加利福尼亚的学校 select * from college where cstate='California' 一个条件 select vfirstname,vlastname,dbirthdate,ntestscore from externalcandidate where dbirthdate>='01-1月-70'

oracle常用命令大全和环境变量路径

Oracle 命令大全底部为环境变量配置路径。 1 运行SQLPLUS工具 sqlplus 2 以OS的默认身份连接 / as sysdba 3 显示当前用户名 show user 4 直接进入SQLPLUS命令提示符 sqlplus /nolog 5 在命令提示符以OS身份连接 connect / as sysdba 6 以SYSTEM的身份连接 connect system/xxxxxxx@服务名 7 显示当然用户有哪些表 select * from tab; 8 显示有用户名和帐户的状态 select username,account_status from dba_users; 9 将SCOTT帐号解锁(加锁) alter user scott account unlock(lock); 10 以SCOTT的身份连接并且查看所属表 connect scott/tiger select * from tab; 11 查看EMP的表结构及记录内容 desc emp select empno,ename from emp; 12 以OS的身份登看SGA,共享池,CACHE的信息 connect / as sysdba show sga select name,value/1024/1024 from v$sga; show parameter shared_pool_size select value/1024/1024 from v$parameter where name ='shared_pool_size';

show parameter db_cache_size select value/1024/1024 from v$parameter where name ='db_cache_size'; 13 查看所有含有SIZE的信息 show parameter size bitmap_merge_area_size integer 1048576 create_bitmap_area_size integer 8388608 db_16k_cache_size big integer 0 db_2k_cache_size big integer 0 db_32k_cache_size big integer 0 db_4k_cache_size big integer 0 db_8k_cache_size big integer 0 db_block_size integer 4096 db_cache_size big integer 33554432 db_keep_cache_size big integer 0 db_recycle_cache_size big integer 0 NAME TYPE V ALUE ------------------------------------ ----------- ------------- global_context_pool_size string hash_area_size integer 1048576 java_max_sessionspace_size integer 0 java_pool_size big integer 33554432 large_pool_size big integer 8388608 max_dump_file_size string UNLIMITED object_cache_max_size_percent integer 10 object_cache_optimal_size integer 102400 olap_page_pool_size integer 33554432 oracle_trace_collection_size integer 5242880 parallel_execution_message_size integer 2148 NAME TYPE V ALUE ------------------------------------ ----------- ------------- sga_max_size big integer 143727516 shared_pool_reserved_size big integer 2516582 shared_pool_size big integer 50331648 sort_area_retained_size integer 0 sort_area_size integer 524288 workarea_size_policy string AUTO 14 显示SGA的信息 select * from v$sgastat; POOL NAME BYTES