DEPTNO DNAME EMPS
------- ----------- ------------------------------------------
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] 没有相关教程
|