------- ----------- ------------------------------------------

10 ACCOUNTING CLARK, KING, MILLER

20 RESEARCH ADAMS, FORD, JONES, ROONEY, SCOTT, SMITH

30 SALES ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD

16．在Oracle中建一个编号会自动增加的字段,以利于查询

1、建立序列：

CREATE SEQUENCE checkup_no_seq

NOCYCLE

MAXVALUE 9999999999

START WITH 2;

2、建立触发器：

CREATE OR REPLACE TRIGGER set_checkup_no

BEFORE INSERT ON checkup_history

FOR EACH ROW

DECLARE

next_checkup_no NUMBER;

BEGIN

--Get the next checkup number from the sequence

SELECT checkup_no_seq.NEXTVAL

INTO next_checkup_no

FROM dual;

--use the sequence number as the primary key

--for the record being inserted

:new.checkup_no := next_checkup_no;

END;

17．查看对象的依赖关系(比如视图与表的引用)

查看视图：dba_dependencies 记录了相关的依赖关系

查东西不知道要查看哪个视图时，可以在DBA_Objects里看，

select object_name from dba_objects where object_name like ''''%ROLE%''''(假如查看ROLE相关)

然后DESC一下就大体上知道了。

18．要找到某月中所有周五的具体日期

select to_char(t.d,''''YY-MM-DD'''') from (

select trunc(sysdate, ''''MM'''')+rownum-1 as d

from dba_objects

where rownum < 32) t

where to_char(t.d, ''''MM'''') = to_char(sysdate, ''''MM'''') --找出当前月份的周五的日期

and trim(to_char(t.d, ''''Day'''')) = ''''星期五''''

--------

03-05-02

03-05-09

03-05-16

03-05-23

03-05-30

如果把where to_char(t.d, ''''MM'''') = to_char(sysdate, ''''MM'''')改成sysdate-90，即为查找当前

月份的前三个月中的每周五的日期。

上一页  [1] [2] [3] [4]