DB2

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 );

正文

Oracle 在PLSQL中提供的强大特性使得数据库开发人员可以在数据库端完成功能足够复杂的任务, 本文将结合Oracle提供的相关程序包(package)以及一个非常优秀的第三方开发工具来介绍在PLSQL中开发及调试存储过程的方法，当然也适用于函数。

本文所采用的软件版本和环境：

• 服务器：Oracle 8.1.2 for Solaris 8
• 开发工具：PL/SQL Developer 4.5

准备工作

在开始之前, 假设您已经安装好了Oracle的数据库服务, 并已经建立数据库, 设置好监听程序, 以允许客户端进行连接; 同时您已经拥有了一台设置好本地Net服务名的开发客户机, 并已经安装好PL/SQL Developer开发工具的以上版本或者更新.

在下面的示例代码中,我们使用Oracle数据库默认提供的示例表 scott.dept 和 scott.emp. 建表的语句如下：

create table SCOTT.DEPT
(
DEPTNO NUMBER(2) not null,
DNAME VARCHAR2(14),
LOC VARCHAR2(13)
)
create table SCOTT.EMP
(
EMPNO NUMBER(4) not null,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
)

从一个最简单的存储过程开始

我们现在需要编写一个存储过程, 输入一个部门的编号, 要求取得属于这个部门的所有员工信息, 包括员工编号和姓名. 员工的信息通过一个cursor返回给应用程序.

create or replace procedure usp_getEmpByDept(
in_deptNo in number,
out_curEmp out pkg_const.REF_CURSOR 
) as
begin
open curEmp for 
select empno,
ename
from scott.emp
where deptno = in_deptNo;
end usp_getEmpByDept;

上面我们定义了两个参数, 其中第二个参数需要利用cursor返回员工信息, PLSQL中提供了REF CURSOR的数据类型, 可以采用两种方式进行定义, 一种是强类型,一种是弱类型, 前者在定义时指定cursor返回的数据类型, 后者可以不指定, 由数据库根据查询语句进行动态绑定.

在使用前必须首先使用TYPE关键字进行定义, 我们把数据类型REF_CURSOR定义在自定义的程序包中: pkg_const

create or replace package pkg_const as
type REF_CURSOR is ref cursor;
end pkg_const;

注意: 这个包需要在创建上面的存储过程之前被编译, 因为存储过程用到了包中定义的数据类型.

调试存储过程

使用PL/SQL Developer 登录数据库, 用户名scott, 密码默认为: tiger. 将包和存储过程分别编译, 然后在左侧浏览器的procedure栏目下找到新建的存储过程, 点击右键, 选择"Test"/"测试", 在下面添好需要输入的参数值, 按快捷键F8直接运行存储过程, 执行完成之后, 可以点开返回参数旁边的按钮查看结果集.

如果存储过程内部语句较复杂, 可以按F9进入存储过程进行跟踪调试. PL/SQL Developer提供与通用开发工具类似的跟踪调试功能, 分为step、step over、step out 等多种方式， 对于变量也可进行trace或者手动赋值。

在存储过程中写日志文件

以上方法可以在开发阶段对编写和调试存储过程提供最大限度的方便，但为了在系统测试或者生产环境中确认我们的代码是否正常工作时，就需要记录log。

PLSQL提供了一个UTL_FILE包，通过定义UTL_FILE包中的FILE_TYPE类型，可以获得一个文件句柄，通过此句柄可以实现一般的文件操作功能。但默认的数据库参数是不允许使用UTL_FILE包的，需要手动进行配置，使用GUI的管理工具或者手工编辑 INIT.ORA文件，找到 "utl_file_dir" 参数，如果没有，则添加一行，修改成如下：

utl_file_dir='/usr/tmp'

或者

utl_file_dir=*

第一种方式限定了在UTL_FILE包中可以存取的目录，第二种方式则不进行限定。无论哪种方式，都要保证运行数据库实例的用户，一般是oracle，拥有此目录的存取权限，否则在使用包的过程中会报出错误信息。

注意等号左右不要留空格，可能会引起解析错误，导致设置无效。

下面在上面的存储过程中加入记录log的代码：

create or replace procedure usp_getEmpByDept(
in_deptNo in number,
out_curEmp out pkg_const.REF_CURSOR 
) as
fi utl_file.file_type;
begin
if( pkg_const.DEBUG ) then 
fi := utl_file.fopen( pkg_const.LOG_PATH, to_char( sysdate, 'yyyymmdd' ) || '.log', 'a' );
utl_file.put_line( fi, ' ****** calling usp_getEmpByDept begin at ' || to_char( sysdate, 'hh24:mi:ss mm-dd-yyyy' ) || ' ******' );
utl_file.put_line( fi, ' INPUT:' );
utl_file.put_line( fi, ' in_chID => ' || in_chID );
end if;
open curEmp for 
select empno,
ename
from scott.emp
where deptno = in_deptNo;
if( pkg_const.DEBUG ) then 
utl_file.put_line( fi, ' RETURN:' );
utl_file.put_line( fi, ' out_curEmp: unknown' );
utl_file.put_line( fi, ' ****** usp_getEmpByDept end at ' || to_char( sysdate, 'hh24:mi:ss mm-dd-yyyy' ) || ' ******' );
utl_file.new_line( fi, 1 );
utl_file.fflush( fi );
utl_file.fclose( fi );
end if;
exception
when others then
if( pkg_const.DEBUG ) then 
if( utl_file.is_open( fi )) then
utl_file.put_line( fi, ' ERROR:' );
utl_file.put_line( fi, ' sqlcode = ' || sqlcode );
utl_file.put_line( fi, ' sqlerrm = ' || sqlerrm );
utl_file.put_line( fi, ' ****** usp_getEmpByDept end at ' || to_char( sysdate, 'hh24:mi:ss mm-dd-yyyy' ) || ' ******' );
utl_file.new_line( fi, 1 );
utl_file.fflush( fi );
utl_file.fclose( fi );
end if;
end if;
/* Raise the exception for caller. */
raise_application_error( -20001, sqlcode || '|' || sqlerrm );
end usp_getEmpByDept;

在上面的代码中，我们又引用了两个新的常量：

DEBUG
LOG_PATH

分别定义了调试开关参数和文件路径参数，对此，我们需要修改我们前面定义的程序包：

create or replace package pkg_const as
type REF_CURSOR is ref cursor;
DEBUG constant boolean := true;
LOG_PATH constant varchar2(256) := '/usr/tmp/db';
end pkg_const;

在代码块的起始处，将输入参数的名称与值成对的记入log文件，在代码块的正常退出部分，将输出参数的名称和数值也成对的记录下来，如果程序非正常退出，则在exception 的处理部分，把错误代码及错误信息写入log文件。一般使用这些信息就可以较迅速的找出程序运行中出现的大部分错误。

注意：如果返回参数的类型是cursor，是无法在存储过程内部将返回的结果集一条一条写入log文件的，此时应当结合在调用程序中记录的log信息，下面具体分析一下上述代码：

fopen() 函数使用给定的路径和文件名，新建文件或者打开已有的文件，这取决于最后一个参数, 当使用'a'作为参数时，如果给定的文件不存在，则以此文件名新建文件，并以写'w'方式打开，返回一个文件句柄。

上面代码以天为单位建立日志文件，并且，不同存储过程之间共享log文件，这种方式的优点是可能通过查看log文件追溯出程序的调用顺序和逻辑。实际应用中，应根据不同的需求，具体分析，可以使用更复杂的log文件生成策略。

put_line() 函数用于写入字符到文件，并在字符串的结尾加入换行符，若不想换行，使用put()函数。

new_line() 函数用于生成指定数目的空行，上面对文件的修改写在一个缓冲区内，执行fflush() 将立即将buffer中的内容写入文件，当你希望在文件还未关闭之前就需要读取已经作出的改变时，调用此函数。

is_open() 函数用于判断一个文件句柄的状态，最后用完一定记得把打开的文件关闭，调用fclose() 函数，并且应把这个语句加入exception的处理中，防止过程非正常退出时留下未关闭的文件句柄。

捕获违例

在PLSQL中，你可以通过两个内建的函数sqlcode 和sqlerrm 来找出发生了哪类错误并且获得详细的message信息，在内部违例发生时，sqlcode返回从-1至－20000之间的一个错误号，但有一个例外，仅当内部违例no_data_found 发生时，才会返回一个正数 100。当用户自定义的违例发生时，sqlcode返回＋1，除非用户使用 pragma EXCEPTION_INIT 将自定义违例绑定一个自定义的错误号。当没有任何违例抛出时，sqlcode返回0。

下面是一个简单的捕获违例的例子：

declare
i number(3);
begin
select 100/0 into i from dual;
exception
when zero_divide then
...
end;

在上面的exception 中我们使用others 关键字捕获所有未明确指定的违例，并进行记录log处理，同时我们必须在做完这些处理之后，把违例再次抛出给调用程序，调用函数：
raise_application_error ()，此函数向调用程序返回一个用户自定义的错误号码和错误信息，第一个参数指定一个错误号码，由用户自行定义，但必须限定在-20000至－20999 之间，避免与Oracle内部定义exception的错误号码冲突，第二个参数需要返回一个字符串，这里我们使用它返回我们上面捕获的错误号码和错误描述。

注意：通过raise_application_error()函数抛出的违例已经不是开始在程序块内部捕获的内部违例，而是由用户自己定义的。