Oracle 学习笔记
2004-06-28lsnrctl start LISTENER 手工启动监听器(Listener)Oracle安装的默认HTTP 服务器可以通过以下 URL 进行访问:http://localhost:7778https://localhost:444318:27 2004-08-14用 select userenv('LANGUAGE') from dual;得到客户端环境的字符集 14:32 2004-9-2A表和B表是N..N的关系,中间有一个关系表R, A表中的部分数据有可能不在R表和B表中有对应的记录, 现在需要把A表中的数据取出,如果有的话,同时包含B和R的数据,否则B和R的列置为空.select...
2004-06-28
lsnrctl start LISTENER 手工启动监听器(Listener)
Oracle安装的默认HTTP 服务器可以通过以下 URL 进行访问:
http://localhost:7778
https://localhost:4443
18:27 2004-08-14
用 select userenv('LANGUAGE') from dual;得到客户端环境的字符集
14:32 2004-9-2
A表和B表是N..N的关系,中间有一个关系表R, A表中的部分数据有可能不在R表和B表中有对应的记录, 现在需要把A表中的数据取出,如果有的话,同时包含B和R的数据,否则B和R的列置为空.
select *
from a
left join r
on a.id = r.id_a
left join b
on b.id = r.id_b
注意: sql中join各表的顺序很重要,当使用基本表编写sql时,一般很少犯错误,因为顺序搞错很可能编译不通过,但在使用拉平的视图编写sql时就可能导致潜在的问题,比如可能把join r和b的顺序弄反.
select *
from a
left join b
on ...
left join r
on r.id_a = a.id
and r.id_b = b.id
这样编写,对于r表中没有对应记录的数据就会产生笛卡儿乘积.
11:22 2004-9-28
取得一个给定日期的星期数,并使用指定的格式返回:
to_char( sysdate, 'day', 'nls_date_language=American' )
若需要锁定记录可是用:
select * from table for update 语句,当前session会获得一个锁,在提交之前或者回滚之前,其他session不能修改此表.
14:27 2004-9-29
用同义词方法,使用户可以像另一个对象的拥有者一样操作这个对象,不用加用户名前缀 user.table1
SQL>CREATE PUBLIC SYNONYM user1.DEPT FOR SCOTT.DEPT;
SQL>connect user1/pass1;
SQL>select * from dept; <- 引用 scott.dept 表
能不能更进一步？>
可以
<我的表很多，每个表都要建一次同义词？>
没有必要
<能不能对用户，而不是具体的表，建立同义词？>
不行
更简单的办法：
用户B登录，先执行：
alter session set current_schema=a
select * from table1 就是 select * from a.table1
15:25 2004-9-29
Oracle 8i 文档中对临时表的描述:
Temporary Tables
In addition to permanent tables, Oracle can create temporary tables to hold session-private data that exists only for the duration of a transaction or session.
The CREATE GLOBAL TEMPORARY TABLE command creates a temporary table which can be transaction specific or session specific. For transaction-specific temporary tables, data exists for the duration of the transaction while for session-specific temporary tables, data exists for the duration of the session. Data in a temporary table is private to the session. Each session can only see and modify its own data. DML locks are not acquired on the data of the temporary tables. The LOCK command has no effect on a temporary table as each session has its own private data.
A TRUNCATE statement issued on a session-specific temporary table truncates data in its own session; it does not truncate the data of other sessions that are using the same table.
DML statements on temporary tables do not generate redo logs for the data changes. However, undo logs for the data and redo logs for the undo logs are generated. Data from the temporary table is automatically dropped in the case of session termination, either when the user logs off or when the session terminates abnormally such as during a session or instance crash.
You can create indexes for temporary tables using the CREATE INDEX command. Indexes created on temporary tables are also temporary and the data in the index has the same session or transaction scope as the data in the temporary table.
You can create views that access both temporary and permanent tables. You can also create triggers on temporary tables.
The EXPORT and IMPORT utilities can export and import the definition of a temporary table. However, no data rows are exported even if you use the ROWS option. Similarly, you can replicate the definition of a temporary table but you cannot replicate its data.
GLOBAL TEMPORARY关键字:
GLOBAL TEMPORARY
specifies that the table is temporary and that its definition is visible to all sessions. The data in a temporary table is visible only to the session that inserts the data into the table.
A temporary table has a definition that persists the same as the definitions of regular tables, but it contains either session-specific or transaction-specific data. You specify whether the data is session- or transaction-specific with the ON COMMIT keywords (below).
For more information on temporary tables, please refer to Oracle8i Concepts.
Restrictions:
* Temporary tables cannot be partitioned, index-organized, or clustered.
* You cannot specify any referential integrity (foreign key) constraints on temporary tables.
* Temporary tables cannot contain columns of nested table or varray type.
* You cannot specify the following clauses of the LOB_storage_clause: TABLESPACE, storage_clause, LOGGING or NOLOGGING, MONITORING or NOMONITORING, or LOB_index_clause.
* Parallel DML and parallel queries are not supported for temporary tables. (Parallel hints are ignored. Specification of the parallel_clause returns an error.)
* You cannot specify the segment_attributes_clause, nested_table_storage_clause, or parallel_clause.
* Distributed transactions are not supported for temporary tables.
on commit 关键字:
ON COMMIT
can be specified only if you are creating a temporary table. This clause specifies whether the data in the temporary table persists for the duration of a transaction or a session.
DELETE ROWS
specifies that the temporary table is transaction specific (this is the default). Oracle will truncate the table (delete all its rows) after each commit.
PRESERVE ROWS
specifies that the temporary table is session specific. Oracle will truncate the table (delete all its rows) when you terminate the session.
12:56 2004-10-8
plsql中可以使用for c in (select...) loop 语句，select后面可以跟一个或多个列名，并会把值自动付给变量c，变量c的类型为rowtype，
在循环体内可以通过如下方式引用：
for c in (select colno,cname from sys.col) loop
dbms_output.put_line( c.colno || ': ' || c.cname );
end loop;
14:05 2004-10-13
oracle 存储过程中，当在返回类型的cursor中使用临时表，可以不用指定on commit关键字，外部程序通过ado访问都可以取到记录集。
17:15 2004-10-14
vc程序通过ado访问数据库，调用存储过程，如果在程序中使用了odbc数据源（dsn）则会有一些限制，比如：调用即含有cursor又含有返回值的存储过程会发生返回值无法取出。应直接使用connection对象获得数据库连接，可以避免此问题。
14:54 2004-10-15
to_char(sysdate, 'hh:mi:ss.ssss')
to_char(sysdate, 'hh:mi:ss.sssss')
是有效的，四个以下或者5个以上的's'都是非法的。
18:38 2004-10-18
可以在cursor的sql中使用order by子句，但如果sql当中含有 sequence的调用，则不允许使用order by子句。
21:24 2004-10-19
min()与max() 函数将会把重复的值合并掉,比如数据库中有多条记录col列的最小值为1,则min(col)会返回:1,与min(distinct col)返回结果相同.
17:02 2004-10-20
plsql 的记录集操作符:
UNION All rows selected by either query.
两个合并重复记录的结果集,其交叉部分只出现一次.
UNION ALL All rows selected by either query, including all duplicates.
两个未合并重复记录的结果集,不合并交叉的部分.(不合并任何部分)
INTERSECT All distinct rows selected by both queries.
两个合并重复记录的结果集,只返回交叉部分.
MINUS All distinct rows selected by the first query but not the second.
两个合并重复记录的结果集,从第一个结果集中去除交叉的部分.
select * from test t1 where b = (select max(b) from test t2 where t1.a = t2.a);
select a,max(b) from test t1 group by a;
16:14 2004-10-21
round(d, fmt) 函数返回一个日期以给定最小单位的最近时间.比如:
round(sysdate, 'dd') 返回下一日的日期
round(sysdate, 'yyyy') 返回下一年开始的日期
round(sysdate, 'mi') 返回下一分钟的时间
后面的fmt 可以为: yyyy, mm, dd, hh, mi, 不可以为秒ss.
oracle 中的时间分为12小时制和24小时制，当为二十四小时制时，00点00分为一天的起始。23点59分..为一天的结束，不允许出现24点的格式。
在12小时制当中，当然不允许出现大于12的小时数，比如13点。同时也不允许出现00点。24小时制中00点的时间被换算为12点，同时标注上午。即12小时制当中，上午12点00分是一天的开始，下午12点59分...是一天的结束。
14:47 2004-10-25
一个查询统计sql语句。表test中有，a,b,c三列，a为主键列，b为区分1，c为区分2，根据b，c建立一个二维表，各个块上的值为满足此两种区分条件的记录数目。
select t1.c,
count(t2.a) n1,
count(t3.a) n2
from test t1
left join test t2
on t2.a = t1.a
and t2.b = 1
left join test t3
on t3.a = t1.a
and t3.b = 2
group by (t1.c)
在存储过程中使用select 语句从临时表（commit on delete）中向应用程序返回cursor前，不能在存储过程内部使用commit语句清除临时表的数据，否则，应用程序得不到有效的cursor。
15:38 2004-10-26
使用 select ... for update 进行排他。
select ... for update;
update ...
commit;
20:26 2004-11-1
select id, num from t1 group by rollup(id);
可以在最后一行生成一个num的合计。
17:10 2004-11-2
如果采用 select id, count（decode(c1,'a', 1), count(decode(c2, 'b', 1), sum(1) from t1 group be rollup(id);
当decode中的等式右边的值都互斥时,sum(1)可以起到对列进行汇总的作用.
count（decode(c1,'a', 1) + count(decode(c2, 'b', 1) 最终会等于sum(1)
原理就是count(1)只对非空列进行计数,而sum(1)对每一列都产生了一个计数,只要第一个count与第二个count之间不产生重复计数,则上述等式成立.
如果把sum(1)变为sum(2)或者sum(0.5),则还会可以使总计的值是实际值的某个倍数.
但这样的语句就不会成立：
select dummy,1,2,3,4,sum(1) from dual group by rollup(dummy);
DUMMY 1 2 3 4 SUM(1)
----- ---------- ---------- ---------- ---------- ----------
X 1 2 3 4 1
1 2 3 4 1
14:49 2004-11-5
在plsql的trigger中,可以使用when子句指定在何种条件下才触发trigger,但必须用在行级别的trigger(使用for each row),不能用在表级别的trigger.
而且不能够在when子句中调用用户自定义的函数. 在when子句中引用new 或者old不需要加":"前缀.
The expression in a WHEN clause must be a SQL expression, and it cannot include a subquery. You cannot use a PL/SQL expression (including user-defined functions) in the WHEN clause.
使用语句判断当前触发trigger的是哪类事件:
IF DELETING THEN
IF UPDATING THEN
IF INSERTING THEN
或者IF UPDATING( 'mycol')来判断是否更新了某列。
或者： after update of mycol on table
如果一个表名是new或者old,则可以在定义trigger时使用以下子句,避免冲突
BEFORE UPDATE ON new
REFERENCING new AS Newest
在代码中可以使用:Newest引用表new.
19:01 2004-11-10
使用utl_file包写入超过1024个字符数据时，需要在fopen方法中增加参数声明最大的size，如允许最大5000个字符：
utl_file.fopen( 'd:', 'ora.log', 'a', 5000 );
lsnrctl start LISTENER 手工启动监听器(Listener)
Oracle安装的默认HTTP 服务器可以通过以下 URL 进行访问:
http://localhost:7778
https://localhost:4443
18:27 2004-08-14
用 select userenv('LANGUAGE') from dual;得到客户端环境的字符集
14:32 2004-9-2
A表和B表是N..N的关系,中间有一个关系表R, A表中的部分数据有可能不在R表和B表中有对应的记录, 现在需要把A表中的数据取出,如果有的话,同时包含B和R的数据,否则B和R的列置为空.
select *
from a
left join r
on a.id = r.id_a
left join b
on b.id = r.id_b
注意: sql中join各表的顺序很重要,当使用基本表编写sql时,一般很少犯错误,因为顺序搞错很可能编译不通过,但在使用拉平的视图编写sql时就可能导致潜在的问题,比如可能把join r和b的顺序弄反.
select *
from a
left join b
on ...
left join r
on r.id_a = a.id
and r.id_b = b.id
这样编写,对于r表中没有对应记录的数据就会产生笛卡儿乘积.
11:22 2004-9-28
取得一个给定日期的星期数,并使用指定的格式返回:
to_char( sysdate, 'day', 'nls_date_language=American' )
若需要锁定记录可是用:
select * from table for update 语句,当前session会获得一个锁,在提交之前或者回滚之前,其他session不能修改此表.
14:27 2004-9-29
用同义词方法,使用户可以像另一个对象的拥有者一样操作这个对象,不用加用户名前缀 user.table1
SQL>CREATE PUBLIC SYNONYM user1.DEPT FOR SCOTT.DEPT;
SQL>connect user1/pass1;
SQL>select * from dept; <- 引用 scott.dept 表
能不能更进一步？>
可以
<我的表很多，每个表都要建一次同义词？>
没有必要
<能不能对用户，而不是具体的表，建立同义词？>
不行
更简单的办法：
用户B登录，先执行：
alter session set current_schema=a
select * from table1 就是 select * from a.table1
15:25 2004-9-29
Oracle 8i 文档中对临时表的描述:
Temporary Tables
In addition to permanent tables, Oracle can create temporary tables to hold session-private data that exists only for the duration of a transaction or session.
The CREATE GLOBAL TEMPORARY TABLE command creates a temporary table which can be transaction specific or session specific. For transaction-specific temporary tables, data exists for the duration of the transaction while for session-specific temporary tables, data exists for the duration of the session. Data in a temporary table is private to the session. Each session can only see and modify its own data. DML locks are not acquired on the data of the temporary tables. The LOCK command has no effect on a temporary table as each session has its own private data.
A TRUNCATE statement issued on a session-specific temporary table truncates data in its own session; it does not truncate the data of other sessions that are using the same table.
DML statements on temporary tables do not generate redo logs for the data changes. However, undo logs for the data and redo logs for the undo logs are generated. Data from the temporary table is automatically dropped in the case of session termination, either when the user logs off or when the session terminates abnormally such as during a session or instance crash.
You can create indexes for temporary tables using the CREATE INDEX command. Indexes created on temporary tables are also temporary and the data in the index has the same session or transaction scope as the data in the temporary table.
You can create views that access both temporary and permanent tables. You can also create triggers on temporary tables.
The EXPORT and IMPORT utilities can export and import the definition of a temporary table. However, no data rows are exported even if you use the ROWS option. Similarly, you can replicate the definition of a temporary table but you cannot replicate its data.
GLOBAL TEMPORARY关键字:
GLOBAL TEMPORARY
specifies that the table is temporary and that its definition is visible to all sessions. The data in a temporary table is visible only to the session that inserts the data into the table.
A temporary table has a definition that persists the same as the definitions of regular tables, but it contains either session-specific or transaction-specific data. You specify whether the data is session- or transaction-specific with the ON COMMIT keywords (below).
For more information on temporary tables, please refer to Oracle8i Concepts.
Restrictions:
* Temporary tables cannot be partitioned, index-organized, or clustered.
* You cannot specify any referential integrity (foreign key) constraints on temporary tables.
* Temporary tables cannot contain columns of nested table or varray type.
* You cannot specify the following clauses of the LOB_storage_clause: TABLESPACE, storage_clause, LOGGING or NOLOGGING, MONITORING or NOMONITORING, or LOB_index_clause.
* Parallel DML and parallel queries are not supported for temporary tables. (Parallel hints are ignored. Specification of the parallel_clause returns an error.)
* You cannot specify the segment_attributes_clause, nested_table_storage_clause, or parallel_clause.
* Distributed transactions are not supported for temporary tables.
on commit 关键字:
ON COMMIT
can be specified only if you are creating a temporary table. This clause specifies whether the data in the temporary table persists for the duration of a transaction or a session.
DELETE ROWS
specifies that the temporary table is transaction specific (this is the default). Oracle will truncate the table (delete all its rows) after each commit.
PRESERVE ROWS
specifies that the temporary table is session specific. Oracle will truncate the table (delete all its rows) when you terminate the session.
12:56 2004-10-8
plsql中可以使用for c in (select...) loop 语句，select后面可以跟一个或多个列名，并会把值自动付给变量c，变量c的类型为rowtype，
在循环体内可以通过如下方式引用：
for c in (select colno,cname from sys.col) loop
dbms_output.put_line( c.colno || ': ' || c.cname );
end loop;
14:05 2004-10-13
oracle 存储过程中，当在返回类型的cursor中使用临时表，可以不用指定on commit关键字，外部程序通过ado访问都可以取到记录集。
17:15 2004-10-14
vc程序通过ado访问数据库，调用存储过程，如果在程序中使用了odbc数据源（dsn）则会有一些限制，比如：调用即含有cursor又含有返回值的存储过程会发生返回值无法取出。应直接使用connection对象获得数据库连接，可以避免此问题。
14:54 2004-10-15
to_char(sysdate, 'hh:mi:ss.ssss')
to_char(sysdate, 'hh:mi:ss.sssss')
是有效的，四个以下或者5个以上的's'都是非法的。
18:38 2004-10-18
可以在cursor的sql中使用order by子句，但如果sql当中含有 sequence的调用，则不允许使用order by子句。
21:24 2004-10-19
min()与max() 函数将会把重复的值合并掉,比如数据库中有多条记录col列的最小值为1,则min(col)会返回:1,与min(distinct col)返回结果相同.
17:02 2004-10-20
plsql 的记录集操作符:
UNION All rows selected by either query.
两个合并重复记录的结果集,其交叉部分只出现一次.
UNION ALL All rows selected by either query, including all duplicates.
两个未合并重复记录的结果集,不合并交叉的部分.(不合并任何部分)
INTERSECT All distinct rows selected by both queries.
两个合并重复记录的结果集,只返回交叉部分.
MINUS All distinct rows selected by the first query but not the second.
两个合并重复记录的结果集,从第一个结果集中去除交叉的部分.
select * from test t1 where b = (select max(b) from test t2 where t1.a = t2.a);
select a,max(b) from test t1 group by a;
16:14 2004-10-21
round(d, fmt) 函数返回一个日期以给定最小单位的最近时间.比如:
round(sysdate, 'dd') 返回下一日的日期
round(sysdate, 'yyyy') 返回下一年开始的日期
round(sysdate, 'mi') 返回下一分钟的时间
后面的fmt 可以为: yyyy, mm, dd, hh, mi, 不可以为秒ss.
oracle 中的时间分为12小时制和24小时制，当为二十四小时制时，00点00分为一天的起始。23点59分..为一天的结束，不允许出现24点的格式。
在12小时制当中，当然不允许出现大于12的小时数，比如13点。同时也不允许出现00点。24小时制中00点的时间被换算为12点，同时标注上午。即12小时制当中，上午12点00分是一天的开始，下午12点59分...是一天的结束。
14:47 2004-10-25
一个查询统计sql语句。表test中有，a,b,c三列，a为主键列，b为区分1，c为区分2，根据b，c建立一个二维表，各个块上的值为满足此两种区分条件的记录数目。
select t1.c,
count(t2.a) n1,
count(t3.a) n2
from test t1
left join test t2
on t2.a = t1.a
and t2.b = 1
left join test t3
on t3.a = t1.a
and t3.b = 2
group by (t1.c)
在存储过程中使用select 语句从临时表（commit on delete）中向应用程序返回cursor前，不能在存储过程内部使用commit语句清除临时表的数据，否则，应用程序得不到有效的cursor。
15:38 2004-10-26
使用 select ... for update 进行排他。
select ... for update;
update ...
commit;
20:26 2004-11-1
select id, num from t1 group by rollup(id);
可以在最后一行生成一个num的合计。
17:10 2004-11-2
如果采用 select id, count（decode(c1,'a', 1), count(decode(c2, 'b', 1), sum(1) from t1 group be rollup(id);
当decode中的等式右边的值都互斥时,sum(1)可以起到对列进行汇总的作用.
count（decode(c1,'a', 1) + count(decode(c2, 'b', 1) 最终会等于sum(1)
原理就是count(1)只对非空列进行计数,而sum(1)对每一列都产生了一个计数,只要第一个count与第二个count之间不产生重复计数,则上述等式成立.
如果把sum(1)变为sum(2)或者sum(0.5),则还会可以使总计的值是实际值的某个倍数.
但这样的语句就不会成立：
select dummy,1,2,3,4,sum(1) from dual group by rollup(dummy);
DUMMY 1 2 3 4 SUM(1)
----- ---------- ---------- ---------- ---------- ----------
X 1 2 3 4 1
1 2 3 4 1
14:49 2004-11-5
在plsql的trigger中,可以使用when子句指定在何种条件下才触发trigger,但必须用在行级别的trigger(使用for each row),不能用在表级别的trigger.
而且不能够在when子句中调用用户自定义的函数. 在when子句中引用new 或者old不需要加":"前缀.
The expression in a WHEN clause must be a SQL expression, and it cannot include a subquery. You cannot use a PL/SQL expression (including user-defined functions) in the WHEN clause.
使用语句判断当前触发trigger的是哪类事件:
IF DELETING THEN
IF UPDATING THEN
IF INSERTING THEN
或者IF UPDATING( 'mycol')来判断是否更新了某列。
或者： after update of mycol on table
如果一个表名是new或者old,则可以在定义trigger时使用以下子句,避免冲突
BEFORE UPDATE ON new
REFERENCING new AS Newest
在代码中可以使用:Newest引用表new.
19:01 2004-11-10
使用utl_file包写入超过1024个字符数据时，需要在fopen方法中增加参数声明最大的size，如允许最大5000个字符：
utl_file.fopen( 'd:', 'ora.log', 'a', 5000 );
