ORACLE常用傻瓜1000(之三)

?

?? 没有限制.

?

111. 返回大于等于N的最小整值?

????SELECT CEIL(N)??FROM DUAL;

?

112. 返回小于等于N的最小整值?

????SELECT FLOOR(N)??FROM DUAL;

?

113. 返回前月的最后一天?

?????SELECT LAST_DAY(SYSDATE)??FROM DUAL;

?

114. 如何不同用入?

??IMP??SYSTEM/MANAGER??FILE=AA.DMP??FROMUSER=USER_OLD???TOUSER=USER_NEW??ROWS=Y??INDEXES=Y ;

?

115.?如何找数据库表的主键字段的名称?

???SQL>SELECT * FROM user_constraints WHERE CONSTRAINT_TYPE='P' and?? table_name='TABLE_NAME';
?

116. 果集互加的函?

???SQL>SELECT * FROM BSEMPMS_OLD INTERSECT SELECT * FROM BSEMPMS_NEW;
? SQL>SELECT * FROM BSEMPMS_OLD UNION SELECT * FROM BSEMPMS_NEW;
? SQL>SELECT * FROM BSEMPMS_OLD UNION ALL SELECT * FROM BSEMPMS_NEW;
?

117. 果集互的函?
?SQL>SELECT * FROM BSEMPMS_OLD MINUS SELECT * FROM BSEMPMS_NEW;
?

118. 如何配置Sequence?

????建sequence seq_custid?
? create sequence seq_custid start 1 incrememt by 1;?
? 建表时:?
? create table cust?
? { cust_id smallint not null,?
? ...}?
? insert 时:?
? insert into table cust?
? values( seq_cust.nextval, ...)?
?

日期的各部分的常用的的法

119>.取的年份的法:
???? SELECT TO_CHAR(SYSDATE,'YYYY') FROM DUAL;

?

120>.取的月份的法:
???? SELECT TO_CHAR(SYSDATE,'MM') FROM DUAL;

?

121>.取的日的法:
???? SELECT TO_CHAR(SYSDATE,'DD') FROM DUAL;

?

122>.取的的法:
???? SELECT TO_CHAR(SYSDATE,'HH24') FROM DUAL;

?

123>.取的分的法:
???? SELECT TO_CHAR(SYSDATE,'MI') FROM DUAL;

?

124>.取的秒的法:
???? SELECT TO_CHAR(SYSDATE,'SS') FROM DUAL;

?

125>.取的日期的法:
???? SELECT TRUNC(SYSDATE) FROM DUAL;

?

126>.取的的法:
???? SELECT TO_CHAR(SYSDATE,'HH24:MI:SS') FROM DUAL;

?

127>.日期,形字符形
???? SELECT TO_CHAR(SYSDATE) FROM DUAL;

?

128>.字符串成日期或形:
???? SELECT TO_DATE('2003/08/01') FROM DUAL;

?

129>.返回的星期几的法:
???? SELECT TO_CHAR(SYSDATE,'D') FROM DUAL;

?

130>.返回一年中的第几天的法:
???? SELECT TO_CHAR(SYSDATE,'DDD') FROM DUAL;

?

131>.返回午夜和中指定的值之的秒的法:
???? SELECT TO_CHAR(SYSDATE,'SSSSS') FROM DUAL;

?

132>.返回中一年的第几周的法:
???? SELECT TO_CHAR(SYSDATE,'WW') FROM DUAL;
???
? 如有迎大家一起探

?

?????待……