|
技术：向Tom咨询
关于格式、负0（零）和时间后移等问题
作者：Tom Kyte
我们的技术专家告诉你如何修改格式来让0显示0，以及如何避免时间后移。
日期和时间格式化
我在格式化日期时，在小时前面有一些前导符0（如09:30 a.m.）。 我怎么才能去掉它们呢？
这里有一个非常好的格式模型修改器--填充模式（FM），你可以把它用于日期和数字的修改。 它禁止出现空白及其他填充符字符（像这个前导符0）。
例如：
select to_char(dt,'hh:mi am'),
to_char(dt,'fmhh:mi am'),
to_char(dt,'fmhhfm:mi am')
from (
select to_date('09:01 am','hh:mi am') dt
from dual
)
/
TO_CHAR( TO_CHAR( TO_CHAR(
-------- -------- --------
09:01 am 9:1 am 9:01 am
请注意这三种不同的输出结果。 第一列显示的可能就是你现在得到的： 需要的时候时间成分用前导符0填充为两个字符。
第二列显示出FM的作用： 前导符0被取消了。 但是那可能不是你想要的，因为值9:1的含义不太清楚。 第三列大概是你真正想得到的： 小时前面的前导符0不显示，但时间的其余成分的前导0不取消。
FM的作用类似于一个切换开关--第一次出现空白填充符时将其关闭（空格），再次出现时又打开让其显示。 在修改日期和时间格式时它非常方便。 考虑下面两种输出的区别：
select to_char(sysdate,'Day Month dd'),
to_char(sysdate,'fmDay Month dd')
from dual;
NO_FM
-------------------------------------
Sunday November 02
FM
-------------------------------------
Sunday November 2
第二种输出使用了FM，从进行了精心格式化的日期中得到了希望的输出结果。 第一种输出没有使用FM，用空格填充了日期和月份的名字，以便得到统一格式的输出（一个固定长度的字符串）。
为什么是负0？
我在下面的查询中为什么得到负0，我感到迷惑：
select c1, c2, c1 -
(trunc(to_date(c2,'YYYYMMDDHH24MISS') + 1) - to_date(c2,'YYYYMMDDHH24MISS')) * (24*3600) result
from t where c3 != 'N05416776X0001'
C1 C2 RESULT
---- -------------- ------
...
1229 20030731235942 1211
82 20030731235838 -0
我执行你的查询时得到了不同的结果。 但是，我能用SQL*Plus的命令set
numformat重现你的问题，我设想你肯定用它格式化了输出结果。 问题出在结果列实际上不是0；它是一个非常小的数，而你的格式化命令掩盖了这一事实。请看下面的代码：
select c1,
c2,
c1-(trunc(to_date(c2,
'YYYYMMDDHH24MISS')+1)
-to_date(c2,
'YYYYMMDDHH24MISS'))
* (24*3600) result
from (
select 82 c1, 20030731235838 c2
from dual
)
/
C1 C2 RESULT
----------- ---------- ----------
82 2.0031E+13 -2.240E-36
请注意默认情况下SQL*Plus是如何用指数计数法格式化数字的（它最适合于这种格式）。
你过去用了固定格式的显示方式，因此在显示结果中没有小数位：
SQL> set numformat 99999999999999
SQL> /
C1 C2 RESULT
-------- --------------- ----------
82 20030731235838 -0
所以，结果其实不是负0，而是一个非常小的负数。 用格式时要十分小心；它们也许能很好地格式化信息，但它们也可能对你隐藏信息。
作业后移
我注意到DBA_JOBS.NEXT_DATE的值每天后移一点儿。 我如何将这种机制用于业务关键的操作呢？
我有一个服务器，现在的时间已经后移了10多分钟。 这种情况下是不是有什么出了问题？
这是预料中的、确定的行为。 很容易就可以让DBA_JOBS.NEXT_DATE值不"后移"。
在数据库中，作业是"按照或大致按照预定时间"运行的。 当作业运行时，数据库只是简单地把你指定的字符串作为时间间隔并计算它的值（例如，从DUAL中选择它）。
例如，假设你安排的一项作业的时间是从今天晚上午夜开始，间隔是SYSDATE+1。另外，假设安排在午夜开始的作业实际上是在12:00:20 a.m.（午夜后20秒，虽然它离午夜时间很近，但按秒计算还是晚了一些的）开始运行的。
作业队列工具将接受字符串SYSDATE+1并计算它的值。 安排的下一次开始时间因此将变为明天的12:00:20 a.m.！ 假设该过程自己不断重复（总是差20秒）。
两周之内，作业将在大约12:05 a.m.运行。一个月之内就是12时过10分钟，如此等等。
解决的办法就是使用日期函数，它计算将来的一个固定时间。 例如，假设你想让作业在每天晚上1 a.m.运行。
时间间隔应该是：
trunc(sysdate)+1+1/24
不管你什么时候计算它，这个日期函数都会计算出明天1 a.m.。
设置模式
我有一个Oracle8i第3版（8.1.7）数据库的模式（schema），借助它我使用Oracle
Forms 6i作为用户界面。 当时，它只有一个可以用来创建表或其他东西的用户模式。
我不知道哪种方法是最好、最有效的方法，它使以不同身份登陆的用户能够通过Oracle数据库的内置程序来使用我的应用程序。
有人告诉我说，如果要使多个登陆用户能够自己运行这个应用程序，那么我必须使用同义词，因为他们必须有<schema>.<procedure>权限来执行一个过程。
这是真的吗？
好的，如果你问我，那么我说你的应用程序使?lt;schema>.<procedure>绝对没错。
事实上，我认为这是一条好的经验。 但是，如果你没这么做，而且也不想或不允许这么做，那么你就可以在你的代码中添加以下代码：
alter session set
current_schema=schema_name;
例如，考虑用户以SCOTT以外的身份登陆后的情况：
SQL> desc dept
ERROR:
ORA-04043: object dept does not exist
SQL> alter session set
2 current_schema=scott;
Session altered.
SQL> desc dept
Name Null? Type
------ -------- --------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
该会话的CURRENT_SCHEMA设置用于提供会话的默认模式名。 因此，无论数据库何时要把你的当前用户名放到查询中，它都将放入SCOTT（或者设置给它的什么模式）。
请注意这不会以任何方式和形式影响安全性；它只影响所使用的默认模式名。 如果我没有访问SCOTT.DEPT，则上面的说法就没什么用。
限定对象的数目
我很想知道对于某一特定用户，Oracle数据库是否可以限定所创建的对象的数目（例如，限定为10）。
是否有可能实现这样的要求？
系统事件触发器是用于实现这些独特的定制规则的优秀工具。 你可以使用BEFORE CREATE触发器统计模式中已有对象的数目，如果对象数超过你设定的最大值则使创建对象的过程失败。代码清单1
显示出了这样一个触发器的例子。
另一种方法是你可以把触发器放到想要实现这一要求的每个模式中，而不是在数据库一级上。 为了在运行过程中看到它，我简单地建立一个测试账户：
SQL> drop user a cascade;
User dropped.
SQL> create user a identified by a;
User created.
SQL> grant create session,
2 create table to a;
Grant succeeded.
现在我以这个新用户的身份连接到数据库并创建10个对象：
SQL> connect a/a
SQL> begin
2 for i in 1 .. 10
3 loop
4 execute immediate
'create table t' || i ||
' (x int)';
5 end loop;
6 end;
7 /
PL/SQL过程已成功完成。
到目前为止一切正常，但当我试着创建第11个对象时，将看到：
SQL> create table t11 ( x int );
create table t11 ( x int )
*
ERROR at line 1:
ORA-00604: error occurred at
recursive SQL level 1
ORA-20001: You can only do 10 things!
ORA-06512: at line 13
你自己可能会问，为什么没有一条刚好能做这件事的命令呢？ 事实是数据库不可能预见到数据库之外的每一项独特的定制或要求，但它能向我们提供必需的工具来自己实现定制要求。
例如，假设有一组像下面这样的客户要求，而不是一条简单的"限定到10"的规则：
1. "我希望Oracle数据库使我能够将'alter these 20 users only'权限授予某个用户。"
2. "正常情况下，ALTER USER权限将允许某人可以修改其他任何人的密码，包括DBA的密码。
我希望有一张列表，其中说明了哪个用户可以更改其他哪些用户的密码，这样就可以使帮助台的分析员只更改这5个用户而不是任何用户的密码。 因此我希望得到授权能真正检查这张表，看看George是否能更改Mary的密码。"
3. "我希望Oracle数据库能使这个用户可以更改任何以APP_开头的账户。"
这些都是基于帮助台的正当要求（允许权限相对较低的帮助台技术人员重新设置密码，但不是DBA的密码！），但是这些要求的独特性和定制性太强，以至于我们不能期望实现所有这些要求。
但是鉴于数据库存储了用定义者权限可以运行的过程，那么我就可以轻松执行世界上大多数定制的、独特的安全性协议。 例如，为了满足第一个客户要求，我可以创建一个过程，如代码清单2所示。
因为该过程用定义者的基本权限来执行（因此该过程的拥有者，不是GEORGE，必须有ALTER USER权限），所以我可以在几秒钟之内实现第一个客户要求！
满足客户的第二个和第三个要求同样简单。 事实上，用这种方法任何一种变化都是有可能的。 而且它们和你想要的一样安全和可审计。
RR格式
我正在尝试使用Oracle9i支持的RR格式。 按照RR的格式，如果当前年份和指定的年份都在0-49的范围内，那么日期应该是在当前世纪内的。
假定当前是2003年，指定的日期是27-oct-17。如果用RR格式那么它是不是代表2017年，而如果用YYYY格式，那是不是1917年？
对于同一个日期是不是会显示两个不同的值？
在这种情况下，答案应该是用RR格式和用YY格式都是2017年。 用YYYY格式则是17年。来看下面的代码：
alter session set
nls_date_format='yyyy';
select
to_date('27-oct-17','dd-mon-yy') c1,
to_date('27-oct-17','dd-mon-rr') c2,
to_date('27-oct-17','dd-mon-yyyy') c3
from dual
/
C1 C2 C3
---- ---- ----
2017 2017 0017
把它在旁边先放一会儿，我要指明非常重要的一点。 要做的惟一一件正确的、适当的、专业的、巧妙的、智能的事情是1999年我们学会的。
用4个字符正确地输入年份。就是这样。用户接口会正确接收4位数字--不多不少。 你不应该用2位数字去表示需要4位数字的数据。 含义太不清楚了。
当看到27-oct-17时，你不知道日期到底是什么。
但在回答你的问题时，如果你把数据拆开，数据就会丢失。 如果你要求使用2个字符来显示最少需要4个字符的内容，你将丢失数据；有时你还会误解它。
使用YYYY格式，你绝对不用担心这一点！
对求救问题的分析（再一次）
我有这样的一些记录：
Time Amount
------------------- ------
11/22/2003 12:22:01 100
11/22/2003 12:22:03 200
11/22/2003 12:22:04 300
11/22/2003 12:22:06 200
11/22/2003 12:22:45 100
11/22/2003 12:22:46 200
11/22/2003 12:23:12 100
11/22/2003 12:23:12 200
我需要得到那些与其前一条记录或后一条记录的时间值之差在3秒之内的记录的数量之和。
根据上面的输入，我预期会得到3行： 前4条记录应该放在一起，然后是接下来的2条，最后是最后的2条记录。 我希望得到这样几个"实例"，即一个实例内的各个记录的时间值前后相差不超过3秒。
我认为应该使用LAG 和LEAD函数。
这是一个有趣的问题，你是对的--使用分析函数是正确的方法。 我把我的方法写成一个任务列表：
- 取1行，如果与前1行的时间差在3秒之内，则把它放在这组中。 如超过3秒那我就开始建立一个新组。 我可以使用LAG向回看1行，并得到当前行与前1行的时间差。
- 一旦我给出现时间差值超过3秒的记录做上标记，我就必须沿用这个组标识。
- 然后我就可以对有这个组标识的数据求和了。
我将逐个进行每一步，看看会出现什么情况。 我把你的数据加载到一个小表T中，并且为了节省空间我将使用hh24:mi:ss
这样的NLS_DATE_FORMAT格式，这样我就可以只看到日期的时间部分了。 先解决第一个要求，我给每个新组（也就是你所说的"实例"）的开始部分做上标记：
select time, amount,
case
when time-ltime > 3/24/60/60
or ltime is null
then row_num
end rn
from (
select time, amount,
lag(time)
over (order by time) ltime,
row_number()
over (order by time) row_num
from t
)
order by 1
/
TIME AMOUNT RN
-------- ------ --
12:22:01 100 1
12:22:03 200
12:22:04 300
12:22:06 200
12:22:45 100 5
12:22:46 200
12:23:12 100 7
12:23:12 200
8 rows selected.
在每个组的一开始，我输出ROW_NUMBER()分析函数的返回值。 通过从当前行的时间值中减去前1行的时间值（用LAG()得到），就可以给每一组的第1行做标记了。
如果结果值是NULL--说明这是结果集的第1行--或大于3秒，我就给这行做标记。 现在我必须沿用这些独特的值，从而使所有行都在同一个组中。
我可以使用分析函数MAX()完成这件事。 我利用上面的查询(QUERY-FROM-ABOVE)--去掉ORDER BY 1子句--并执行该查询：
select time, amount,
max(rn)
over(order by time) max_rn
from (QUERY-FROM-ABOVE)
order by 1
/
TIME AMOUNT MAX_RN
-------- ------ ------
12:22:01 100 1
12:22:03 200 1
12:22:04 300 1
12:22:06 200 1
12:22:45 100 5
12:22:46 200 5
12:23:12 100 7
12:23:12 200 7
8 rows selected.
它沿用了组标识符。之所以能做到这一点是因为MAX(RN) OVER (ORDER
BY TIME)使用了"当前行与之前所有行之间"这样一个隐含范围窗口。
这就意味着计算第一行的
MAX(RN)时，所考虑的行只是第一行以及"按照时间排序后在其前边的那些行"，而在此情况下根本就没有任何一行了。
对于结果集中的第2行，从当前行和前一行中选择MAX(RN)，因此，在这种情况下值1被沿用下去。 同样对于第3行和第4行，再次从当前行和前一行中选择MAX(RN)。
到第5行时，考虑新的当前行和前面的所有行，当然现在MAX(RN)的值是5，而且在遇到下一个非空MAX(RN)之前它一直是MAX(RN)的值。
现在要做的最后一件事是完成总计。 我已经给所有组加了标记；进行总计非常简单。 使用上面的查询（不带ORDER
BY），我可以完成：
select min(time),
max(time),
sum(amount)
from (QUERY-FROM-ABOVE)
group by max_rn
order by 1
/
MIN(TIME MAX(TIME SUM(AMOUNT)
-------- -------- -----------
12:22:01 12:22:06 800
12:22:45 12:22:46 300
12:23:12 12:23:12 300
3 rows selected.
你完全得到了你想要的。 一旦你开始使用分析函数，就会发现它的功能极为强大。
Tom Kyte （thomas.kyte@oracle.com）从1993年起一直为Oracle工作。Kyte是Oracle
Government、Education和Healthcare集团的副总裁，是"Effective Oracle by Design（高超的设计造就了高效的Oracle产品）"（Oracle
Press出版）和"Expert One-on-One（一对一的专家）： Oracle（Apress出版，2003年）两书的作者。 |
