牛数据库啊，虽然我觉得不如sql server好用
红楼梦魇
Oracle study
基础
oracle的命令语言叫做sql plus
oracle对象（过程，函数等）的开发语言叫做pl/sql
例子为
-- available online in file ’examp1’
DECLARE
qty_on_hand NUMBER(5);
BEGIN
SELECT quantity INTO qty_on_hand FROM inventory
WHERE product = ’TENNIS RACKET’
FOR UPDATE OF quantity;
IF qty_on_hand > 0 THEN -- check quantity
UPDATE inventory SET quantity = quantity - 1
WHERE product = ’TENNIS RACKET’;
INSERT INTO purchase_record
VALUES (’Tennis racket purchased’, SYSDATE);
ELSE
INSERT INTO purchase_record
VALUES (’Out of tennis rackets’, SYSDATE);
END IF;
COMMIT;
END;
如何获取帮助
据我所知，安装了oracle客户端是难以获得完备的联机文档，我干脆就直接看pdf的文档吧，很多有plsql的，sql，jdbc的等等
对于sql plus，可以看SQLPLUS User's Guide and Reference.pdf
对于pl/sql，可以看plsql User’s Guide and Reference.pdf
如何找到pl/sql的内建函数的帮助呢，plsql User’s Guide and Reference.pdf中有函数的简单列表，函数的详细说明在Oracle9i SQL Reference中。
概念
以下内容是曾庆峰的oracle讲座笔记
数据库
由文件组成，数据文件，重做日志文件，控制文件组成
数据库的名称
三种文件的统称
实例
是oracle的内存结构，包括各种进程，共享池，各种buffer和cache，比如redo buffer等，有的cache是为了提高效率。实例是访问数据库的手段
模式，方案schema
创建用户的时候，为用户建了一个模式，模式是模式对象的容器
dbms oracle server
＝数据库＋实例
连接
是用户和server的通讯通道
会话
认证通过了，建立会话
归档日志
用来恢复数据库。如果日志完整，可以恢复到故障点，必须在归档状态运行才可以保证日志完整。
逻辑结构
逻辑结构也就是数据结构。
表空间
段的容器，默认的表空间是system
段
表，索引，IOT（索引组织表），束，段可以跨文件
区
扩展，在一个文件，区有若干个块
数据块
对应若干个IO的数据块
temp临时表空间
用于排序
分离应用的数据表空间
静态表空间和动态表空间应该分离
大字段应该有单独的表空间
sga
在实例启动的时候产生。server globle area。包含共享池（shared pool），重做日志buffter，data buffer cache。其中共享池包含了library pool和数据字典，数据字典记录了对数据库对象的描述。
pga
在server process启动的时候产生。process globle area。包含server，user通过server访问数据库。后台有database writer，log writer，system monitor，process monitor，checkpoint，archiver
index
就是目录。从逻辑上分有单列索引，组合索引。有唯一索引，和非唯一索引。唯一索引是列的容器唯一。从物理上分有分区索引，非分区索引，b树索引，逆序索引
基本管理
如何快速复制表（表数据也复制）
create table new_table as (select * from old_table)
如何执行一个保存在文件中的脚本
@c:/test_sql.txt（其中c:/是文件所在路径）
如何在脚本中执行多条语句（某些时候）
用 / 隔开
例如：
INSERT INTO "DYNEWCAT"."SCP_TEMP"("NAME" ,"APPNAME" ,"VARIABLEVALUE" )
VALUES ('ATTACHMENT_TYPE_LWID' ,'XCATALOG' ,'附件类型' )
/
INSERT INTO "DYNEWCAT"."SCP_TEMP" ("NAME" ,"APPNAME" ,"VARIABLEVALUE" )
VALUES ('BC_COLOR_FORMAT' ,'DYBC' ,'512' )
/
如果不用 / 隔开，则报“SQL命令尚未结束”。
如何快速清空一个大表
truncate table table_name
如何将select的结果导入文本
SPOOL C:/ABCD.TXT;
select * from test0;
spool off;
如何修改表名
alter table old_table_name rename to new_table_name
移植
数据库的导入导出
导出数据库
C:/>exp dywfdb_20/dywfdb_20@oradb
Export: Release 9.2.0.1.0 - Production on 星期二 4月 27 12:58:30 20
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
连接到: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
输入数组提取缓冲区大小: 4096 > 9999999
导出文件: EXPDAT.DMP> dywfdb_20.dmp
(1)E(完整的数据库)，(2)U(用户) 或 (3)T(表): (2)U >
导出权限 (yes/no): yes >
导出表数据 (yes/no): yes >
压缩区 (yes/no): yes >
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
即将导出指定的用户...
要导出的用户: (RETURN 以退出) > dywfdb_20
要导出的用户: (RETURN 以退出) >
. 正在导出 pre-schema 过程对象和操作
. 正在导出用户 DYWFDB_20 的外部函数库名称
. 导出 PUBLIC 类型同义词
. 导出私有类型同义词
. 正在导出用户 DYWFDB_20 的对象类型定义
即将导出 DYWFDB_20 的对象 ...
. 正在导出数据库链接
. 正在导出序号
. 正在导出群集定义
. 即将导出 DYWFDB_20 的表通过常规路径 ...
. . 正在导出表 WF_ACTINSTANCE 5215 行被导出
. . 正在导出表 WF_ACTIVITY 58 行被导出
. . 正在导出表 WF_ACTIVITY_DATA 5 行被导出
. . 正在导出表 WF_GROUP_USER 85 行被导出
. . 正在导出表 WF_PROCESS 7 行被导出
. . 正在导出表 WF_PROCESSDATA 139 行被导出
. . 正在导出表 WF_PROCESSINSTANCE 923 行被导出
. . 正在导出表 WF_SETUP 1 行被导出
. . 正在导出表 WF_TRANSITION 76 行被导出
. . 正在导出表 WF_USER 50 行被导出
. . 正在导出表 WF_USERGROUP 28 行被导出
. . 正在导出表 WF_WORKITEM 2616 行被导出
. . 正在导出表 WF_WORKITEMDATA 0 行被导出
. 正在导出同义词
. 正在导出视图
. 正在导出存储的过程
. 正在导出运算符
. 正在导出引用完整性约束条件
. 正在导出触发器
. 正在导出索引类型
. 正在导出位图, 功能性索引和可扩展索引
. 正在导出后期表活动
. 正在导出实体化视图
. 正在导出快照日志
. 正在导出作业队列
. 正在导出刷新组和子组
. 正在导出维
. 正在导出 post-schema 过程对象和操作
. 正在导出统计
在没有警告的情况下成功终止导出。
C:/>
导入数据库
1. 必须将dywfdb_20.dmp文件放入到c:根目录下
2. 用DOS环境进入到c:根目录下
3．按下列提示执行
C:/>imp wf_inter/wf_inter@ora2 回车 注： 红色部分根据具体环境而定
分别为用户、密码以及oracle的SID
Import: Release 9.2.0.1.0 - Production on 星期五 4月 1 13:36:22 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
连接到: Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
导入文件: EXPDAT.DMP> ini_dywfdb_20.dmp 回车
输入插入缓冲区大小（最小为 8192 ) 30720> 回车
经由常规路径导出由EXPORT:V09.02.00创建的文件
警告: 此对象由 DYWFDB_20 导出, 而不是当前用户
已经完成ZHS16GBK字符集和UTF8 NCHAR 字符集中的导入
只列出导入文件的内容 (yes/no): no > 回车
由于对象已存在, 忽略创建错误 (yes/no): no > 回车
导入权限 (yes/no): yes > 回车
导入表数据 (yes/no): yes > 回车
导入整个导出文件 (yes/no): no > yes 回车
. 正在将DYWFDB_20的对象导入到 WF_INTER
. . 正在导入表 "WF_ACTINSTANCE" 2748行被导入
. . 正在导入表 "WF_ACTIVITY" 58行被导入
. . 正在导入表 "WF_ACTIVITY_DATA" 5行被导入
. . 正在导入表 "WF_GROUP_USER" 85行被导入
. . 正在导入表 "WF_PROCESS" 7行被导入
. . 正在导入表 "WF_PROCESSDATA" 139行被导入
. . 正在导入表 "WF_PROCESSINSTANCE" 414行被导入
. . 正在导入表 "WF_PROINSTANCEDATA" 5252行被导入
. . 正在导入表 "WF_SETUP" 1行被导入
. . 正在导入表 "WF_TRANSITION" 76行被导入
. . 正在导入表 "WF_USER" 49行被导入
. . 正在导入表 "WF_USERGROUP" 28行被导入
. . 正在导入表 "WF_WORKITEM" 1347行被导入
. . 正在导入表 "WF_WORKITEMDATA" 0行被导入
成功终止导入，但出现警告。
C:/>
服务器端开发
开发工具Pl/sql developer
这个工具做为oracle9i的对象开发工具很不错，它可以调试。
还有一个很有用的功能，就是它提供了模板功能，可以将不少重复的劳动交给模板来做。
基本编程：变量的使用
声明变量的语法如下：Variable_name [CONSTANT] databyte [NOT NULL][:=|DEFAULT expression]
其中databyte可以为表名或列名如：com_basicinfo%rowtype,com_basicinfo.ccid%type;
可以通过SQL SELECT INTO 或FETCH INTO给变量赋值
存储过程和函数
oracle的存储过程和函数等的开发可以使用pl/sql developer，使用这个工具可以调试，还是很好用的，下面是特意做的一个函数的例子：
--this is a example,is show how to
-- .create a function whith in and out param
-- .use cursor
-- .use array type
-- .string operate
-- this function select objectid from the table com_basicinfo and use a cursor
-- to store the objectid into a array, at last,scrabbe all the objectid to a string
create or replace function TestFunction (nObjectType in integer, ccid in com_basicinfo.ccid%type)
return varchar2 is Result varchar2(1024);
--declare cursor variable
cur_test sys_refcursor;
--declare user type
type var_array is
table of varchar2(256)
index by Binary_Integer;
ary_objectid var_array;
temp_objectid com_basicinfo.objectid%type;
loopFlag integer := 0;
nLen integer := 0;
begin
open cur_test for
select objectid from com_basicinfo where objecttype = nObjectType;
--store objectid to array
loop
fetch cur_test into temp_objectid;
exit when cur_test%NOTFOUND;
ary_objectid(loopflag) := temp_objectid;
loopflag := loopflag + 1;
end loop;
--close cursor
close cur_test;
--string operate
for i in 0..loopflag loop
EXIT WHEN nLen >= 900;
Result := Result || ary_objectid(i) || '||chr(13)||';
nLen := LENGTH( Result );
end loop;
return(Result);
end TestFunction;
Oracle对象中事务的处理
在oracle对象的开发中，可能需要用到事务，比如在存储过程中，如何使用事务。
在oracle中使用事务，需要使用commit，rollback，exception关键字，具体见pl/sql参考手册。例子如下：
DECLARE
emp_id INTEGER;
...
BEGIN
SELECT empno, ... INTO emp_id, ... FROM new_emp WHERE ...
...
INSERT INTO emp VALUES (emp_id, ...);
INSERT INTO tax VALUES (emp_id, ...);
INSERT INTO pay VALUES (emp_id, ...);
...
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
ROLLBACK;
...
END;
Trigger
Oracle的trigger开发用pl/sql developer做很简单，它提供了简单的向导功能。对出发的表的引用使用关键帧OLD，下面是个简单的例子。
create or replace trigger Test
after delete on com_basicinfo
for each row
declare
-- local variables here
begin
DELETE FROM dycat.com_fulltext WHERE objectid = :OLD.objectid;
end Test;
（董章震）
job的开发
job类似于sql server的作业，也是为了实现定时的执行某个用户指定的任务
job的开发也很简单，可以用pl/sql developer
next_date参数指示何时此工作将被执行。
interval参数指示一个工作重执行的频度。
例子1，让一个任务每隔20分钟执行一次
dbms_job.isubmit(1,'sp_truncate_table;',
sysdate,
'trunc(sysdate)+44/24');
sysdate第一次执行时间，无所谓，
(sysdate)+(20/60)/24才是真正的以后执行时间，表示间隔是当前
0点+(20/60)个小时，即隔20分钟
例子2，让一个任务每天0点执行
dbms_job.isubmit(1,'sp_truncate_table;',
trunc(sysdate)+ 20/24,
'trunc(sysdate)+1');
任务将是在每天0点执行
pl/sql中一个job的例子，它的间隔是600秒，如图：
对象如何互相调用
很简单，比如函数func1中要调用函数func2，后者有两个参数param1 varchar2(40)和 param2 integer，返回值为Result integer，只需要加入代码
Result := func2(str1, n)
如何加入特殊字符
使用chr函数，例如回车符是chr(13)
oracle的连字符是||
日期的处理
oracle中进行日期的处理可能会用到两个函数to_char和to_date
to_char(datevalue, format)
to_date(dateStirng, formate)
注意，这两个函数的日期格式要一致。
例子
where lastmodify > to_date('2004-10-10','yyyy-mm-dd')
where lastmodify > to_date('2004-10-10 1:11:11','yyyy-mm-dd hh24:mi:ss')
如何将Select出来的char字段每行相加连成字符串
在存储过程中
declare @n nvarchar(4000)
select @n=''
select @n=@n+field_name from table_name
select @n
Oracle处理=条件的时候，区分大小写
Oracle在处理＝条件的时候，是区分大小写的，这一点和SQLServer不一样，和db2一样。所以如果程序在SQLServer上可以查出结果，但在Oracle上不能查出，可能是这个原因，请注意。
游标的使用
游标大家都会用啦，不会还谈什么开发，但是动态的游标怎么用呢
比如在一个存储过程中，我想遍历一个表中的内容，但是表名是从其它的表中取出来的，也就是说是动态的，怎么办
To do a dynamic cursor you will have to use REF Cursors. Here is an example:
DECLARE
TYPE curvar_type IS REF CURSOR;
curvar curvar_type;
column_1 VARCHAR2 (100);
column_2 VARCHAR2 (100);
myQuery VARCHAR2 (1000);
BEGIN
myQuery := 'SELECT col1, col2 FROM table1';
OPEN curvar FOR myquery;
WHILE TRUE LOOP
FETCH curvar INTO column_1, column_2;
EXIT WHEN curvar%NOTFOUND;
END LOOP;
CLOSE curvar;
END;
（董章震）
oracle中，空串’’就是null
update wf_user t set password='' where userid='cz'
然后执行select * from wf_user where password =''就找不到了
用select * from wf_user where password is null才可以
也就是说用条件field=’’永远找不到记录。
动态执行
在PL/SQL运行DDL语句
l_sql := ‘select :1 from :2 where number = :3’;
execute immediate l_sql using name,student,7521;
给动态语句传值(USING 子句)
declare
l_depnam varchar2(20) := 'testing';
l_loc varchar2(10) := 'Dubai';
begin
execute immediate 'insert into dept values (:1, :2, :3)'
using 50, l_depnam, l_loc;
commit;
end;
从动态语句检索值(INTO子句)
declare
l_cnt varchar2(20);
begin
execute immediate 'select count(1) from emp'
into l_cnt;
dbms_output.put_line(l_cnt); --输出信息
end;
也可以动态调用例程，例程中用到的绑定变量参数必须指定参数类型.黓认为IN类型,其它类型必须显式指定
对于处理动态语句,EXECUTE IMMEDIATE比以前可能用到的更容易并且更高效.当意图执行动态语句时，适当地处理异常更加重要.应该关注于捕获所有可能的异常
大字段CLOB的写入与读出
经常在开发中需要对lob数据类型进行操作，Oracle中提供dbms_log包对这类数据进行操作，具体方法如下：
--向lob字段写入数据
--初始化要写入的数据
buffer := 'This is a writing example';
amount := length(buffer);
SELECT document INTO lobloc -- 获取定位器并锁定行
FROM view_sites_info
WHERE site_id = 100 FOR UPDATE;
dbms_lob.write(lobloc,amount,1,buffer);
其中write的参数含义：
lob_loc：要写入的LOB定位器
amount：写入LOB中的字节数
offset：指定开始操作的偏移量
buffer： 指定写操作的缓冲区
也可以用动态SQl锁定行
l_sql := 'select content from com_fulltext where objectid = 1234 for update';
execute immediate l_sql into lobloc;
在调用写过程前一定要使用SELECT语句检索到定位器且用 FOR UPDATE 子句锁定行，否则不能更新LOB；
写过程从offset指定的位置开始，向LOB中写入长度为amount的数据，原LOB中在这个范围内的任何数据都将被覆盖。
缓冲区的最大容量为32767字节，因此在写入大量数据时需多次调用该过程。
读数据过程和写过程类似，这里只给出dbms_lob.read()的参数说明：
lob_loc：要读取的LOB定位器
amount：要读取的字节数
offset：开始读取操作的偏移量
buffer： 存储读操作结果的缓冲区
影响oracle查询效率的几个因素
在开发的过程中有几个地方有感性的认识，如下：
对一个表的某个字段进行查询，如果用like的话，比用＝慢得多，特别是如果那个字段建了索引，用like的话索引也不起作用
对一个表进行查询比对一个视图查询要快得多，几万条数据的时候大约快一倍以上，（表8秒，视图3秒）这个是我以前没有想到的
FAQ
Q如何知道oracle错误代码的详细说明
A 查看oracle9i ErrorMsg.pdf文档。还有google
Q:有关varchar2和nvarchar2的用法，有时候用nvarchar2的时候无法在调试的时候显示出字符串的内容，且无法将函数返回值赋给另一个nvarchar2变量。
A:这是pl/sql developer的问题，它对unicode不支持。Pl/sql developer新大的版本支持unicode。
Q:在用动态执行execute immediate()时，在字符串中指定:id,:char等变量后，用USING子句给它们赋值的时候会出现SQL语句执行不正确的情况，具体的Using子句应该如何使用?有没有什么限制？
Q:job如何监测，不知道日志在什么地方
ORA-01591: lock held by in-doubt distributed transaction ID
In this case, the SQL statement is rolled back immediately. The user who executed the statement can try to re-execute the statement later. If the lock persists, the user should contact an administrator to report the problem, including the ID of the in-doubt distributed transaction.
The chances of the above situations occurring are very rare, considering the low probability of failures during the critical portions of the two-phase commit. Even if such a failure occurs and assuming quick recovery from a network or system failure, problems are automatically resolved without manual intervention. Thus problems usually resolve before they can be detected by users or database administrators.
进入sqlplus >rollback force OK
ORA-01795: 列表中的最大表达式数为 1000
这个的原因是in子句中的个数太多造成的，oracle有这个限制
oracle上取前XX条和排序的问题
以前我有个错误的认识，那就是Oracle上实现分页用如下的语句
SELECT * from TSK_MIGRATE where rownum <= 100 order by created
实际上这样返回的是先取oracle物理上存放的前100条记录再按created排序，而不是先按created排序再取前100条
正确的SQL语句应该这么写
SELECT * from (SELECT * FROM TSK_MIGRATE order by created) where rownum <= 100
（海滨）
oracle表名称长度为30的限制
别取那么长的名字呗
鸣谢
王佳
邹娟
曾庆峰
海滨
8286
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
