SELECT NEXTVAL('A_TABLE_SEQUENCE')
FROM
DUAL |
| 3 |
ROWNUM |
①SELECT *
FROM AGE_TYPE
WHERE ROWNUM<=5 |
①SELECT *
FROM AGE_TYPE
LIMIT 5 OFFSET 0 |
②SELECT *
FROM AGE_TYPE
WHERE CODE IS NOT NULL
AND ROWNUM<=5
ORDER BY CODE DESC |
②SELECT
*
FROM
AGE_TYPE
WHERE CODE IS NOT NULL
ORDER BY CODE DESC
LIMIT 5 OFFSET 0 |
| 4 |
(+) |
①SELECT *
FROM A_TABLE A , B_TABLE B
WHERE A.ID(+)=B.ID |
①SELECT *
FROM A_TABLE A
RIGHT OUTER JOIN
B_TABLE B
ON A.ID=B.ID |
②SELECT *
FROM A_TABLE A , B_TABLE B
WHERE A.ID(+)=B.ID
AND A.COL1='COL1_VALUE' |
②SELECT *
FROM A_TABLE A
RIGHT OUTER JOIN B_TABLE B
ON A.ID=B.ID AND A.COL1='COL1_VALUE' |
③SELECT *
FROM A_TABLE A, B_TABLE B,C_TABLE C,D_TABLE D
WHERE
A.ID=B.ID(+) AND
A.ID=C.ID(+) AND
A.COL1=D.COL1 |
③SELECT *
FROM (A_TABLE A
LEFT OUTER JOIN B_TABLE B
ON A.ID=B.ID)
LEFT OUTER JOIN C_TABLE C
ON A.ID=C.ID,D_TABLE D
WHERE A.COL1=D.COL1 |
④!!!
SELECT *
FROM A_TABLE A
WHERE A.COL1(+)=0 AND
A.COL2(+)
='A_VALUE2' |
④!!!
SELECT *
FROM A_TABLE A
WHERE A.COL1=0 AND
A.COL2='A_VALUE2'
WHERE
(A.COL1=0 OR A.COL1 IS NULL) AND
(A.COL2='A_VALUE2' OR
A.COL2 IS NULL) |
| 5 |
AS |
SELECT A.COL1
A_COL1,
A.COL2
A_COL2
FROM A_TABLE A |
SELECT A.COL1 AS A_COL1,
A.COL2 AS
A_COL2
FROM A_TABLE A |
| 6 |
NVL |
SELECT NVL(SUM(VALUE11),0) FS_VALUE1,
NVL(SUM(VALUE21),0) FS_VALUE2
FROM FIELD_SUM
|
SELECT COALESCE(SUM(VALUE11),0) AS
FS_VALUE1,
COALESCE(SUM(VALUE21),0) AS
FS_VALUE2
FROM FIELD_SUM
|
| 7 |
TO_
NUMBER |
SELECT COL1
FROM A_TABLE
ORDER BY TO_NUMBER(COL1) |
SELECT COL1
FROM A_TABLE
ORDER BY TO_NUMBER(COL1,999999)
[注：'999999' ---- 6位数 为
COL1字段的
长
度] |
| 8 |
DECODE |
SELECT DECODE(ENDFLAG,'1','A','B')
ENDFLAG
FROM TEST |
SELECT
(CASE ENDFLAG
WHEN '1' THEN 'A'
ELSE ' B
' END) AS ENDFLAG
FROM TEST |
| 9 |
时间
问题 |
UPDATE A_TABLE
SET ENTREDATE=
SYSDATE |
UPDATE A_TABLE
SET ENTREDATE=TO_TIMESTAMP(CURRENT_TIMESTAMP,'YYYY-MM-DD
HH24:MI:SS')
或者
UPDATE A_TABLE
SET ENTREDATE=CURRENT_TIMESTAMP |
SELECT
TO_DATE('20010203','YYYY-MM-DD') AS DAY
FROM DUAL |
SELECT TO_DATE('20010203','YYYYMMDD') AS
DAY
FROM DUAL
SELECT TO_DATE('20010203','YYYY-MM-DD') AS DAY
FROM DUAL |
SELECT
TO_DATE(SYSDATE ,'YYYY-MM-DD')
AS DAY
FROM DUAL |
SELECT TO_DATE(CURRENT_DATE,'YYYY-MM-DD') AS
DAY
FROM DUAL |
SELECT
TO_DATE(SYSDATE ,'YYYY/MM/DD')
AS DAY
FROM DUAL |
SELECT TO_DATE(CURRENT_DATE,'YYYY/MM/DD') AS
DAY
FROM DUAL |
| 10 |
|| |
SELECT NULL||'-'||NULL AS VALUES1
FROM DUAL
|
SELECT
COALESCE(NULL,'')||'-'||COALESCE(NULL,'') AS VALUES1
FROM DUAL
SELECT NULL||'-' ||NULL AS
VALUES1
FROM DUAL |
| 11 |
aggregate |
SELECT ROUND(AVG(SUM(BASICCNT1)))
BASICCNT
FROM
ACCESS_INFO_SUM1_V
WHERE YEARCODE BETWEEN '200305' AND '200505'
GROUP BY SCCODE |
SELECT
ROUND(AVG(AIV.BASICCNT)) AS
BASICCNT
FROM
(SELECT
SUM(BASICCNT1)
AS BASICCNT
FROM
ACCESS_INFO_SUM1_V
WHERE
YEARCODE BETWEEN '200305' AND '200505'
GROUP BY
sccode
)
AIV |
| 12 |
「'」 |
①SELECT LENGTH('') AS VALUE1 FROM DUAL
[Result]VALUE1=NULL |
①SELECT LENGTH('') AS VALUE1 FROM DUAL
[Result]VALUE1=0 |
②SELECT TO_DATE('','YYYYMMDD') AS
VALUE2
FROM DUAL
[Result]VALUE2=NULL |
②SELECT TO_DATE('','YYYYMMDD') AS VALUE2
FROM DUAL
[Result]VALUE2=0001-01-01 BC |
③SELECT TO_NUMBER('',1) AS VALUE3
FROM DUAL
[Result]VALUE3=NULL |
③SELECT TO_NUMBER('',1) AS VALUE3 FROM
DUAL
[Result]不能 执
行 |
④INSERT INTO
TEST(VALUE4)VALUES('')
[Result]VALUE4=NULL (注：VALUE3字段
为
数
值类
型) |
④INSERT INTO TEST(VALUE4)VALUES('')
[Result]VALUE4=0
(注：VALUE4字段
为
数
值
类
型) |
⑤INSERT INTO
TEST(VALUE5)VALUES('')
[Result]VALUE5=NULL (注：VALUE5字段
为
字符
类型) |
⑤INSERT INTO TEST(VALUE5)VALUES('')
[Result]VALUE5=''
(注：VALUE5字段
为
字符
类
型,
结果为长度为零的字符串
) |
⑥INSERT INTO
TEST(VALUE6)VALUES(TO_DATE('','YYYYMMDD'))
[Result]VALUE6=NULL (注：VALUE6字段
为时间类型) |
⑥INSERT INTO
TEST(VALUE6)VALUES(TO_DATE('','YYYYMMDD'))
[Result]VALUE6=0001-01-01 BC
(注：VALUE7字段
为时间类
型) |
| 13 |
CEIL |
SELECT CEIL(SYSDATE
- TO_DATE('20051027 14:56:10','YYYYMMDD
HH24:MI:SS')) AS DAYS
FROM DUAL |
SELECT
EXTRACT(DAY FROM
(TO_TIMESTAMP(CURRENT_TIMESTAMP,'YYYY-MM-DD-HH24-MI-SS')
-TO_TIMESTAMP('2005-10-27 14:56:10','YYYY-MM-DD-HH24-MI-SS') ))+1
AS DAYS
FROM DUAL |
| 14 |
NULLIF |
无 NULLIF函数 |
SELECT NULLIF(VALUE1,VALUE2) AS COL1 FROM
DUAL
[注]当 VALUE1=VALUE2
时，COL1=NULL |
| 15 |
CONCAT |
CONCAT(CHAR,CHAR) |
创建函数来解决
CREATE FUNCTION CONCAT(CHAR,CHAR)
RETURNS CHAR AS
'SELECT $1 || $2' LANGUAGE
'sql'; |
| 16 |
ADD_
MONTHS |
add_months(date, int) |
创
建函数来解决
CREATE FUNCTION add_months(date, int)
RETURNS date AS
'SELECT ($1 + ( $2::text || ''months'')::interval)::date;'
LANGUAGE 'sql' |
| 17 |
LAST
_DAY |
LAST_DAY(DATE) |
创
建函数来解决
CREATE FUNCTION LAST_DAY(DATE)
RETURNS DATE AS
'SELECT date(substr(text($1 +
interval(''1
month'')),1,7)||''-01'')-1'
LANGUAGE 'sql'; |
| 18 |
MONTHS
_BETWEEN |
MONTH_BETWEEN(DATA,DATA) |
创
建函数来解决
CREATE FUNCTION MONTH_BETWEEN(DATA,DATA)
RETURNS NUMERIC AS
'SELECT to_number((date($1)-
date($2)),''999999999'')/31'
LANGUAGE 'sql'; |
| 19 |
GRE~
ATEST |
GREATEST (LEAST) |
创
建函数来解决
CREATE OR REPLACE FUNCTION
GREATEST(TEXT[])
RETURNS TEXT AS '
DECLARE
ARRY ALIAS FOR
$1;
GREATEST
TEXT;
BEGIN
GREATEST :=
ARRY[1];
FOR I IN 1 ..
ARRAY_UPPER(ARRY,1) LOOP
IF ARRY[I] > GREATEST THEN
GREATEST := ARRY[I];
END IF;
END LOOP;
RETURN
GREATEST;
END;
' LANGUAGE 'PLPGSQL';
SELECT GREATEST( ARRAY['HARRY','HARRIOT','HAROLD'])
AS 'Greatest'; |
| 20 |
BITAND |
BITAND(int,int) |
SELECT 値 & 値; |
| 21 |
子条件 |
|
在FROM子条件中字段
须有列名，
处理方法用AS +别名 |
| 22 |
MINUS |
MINUS |
以EXCEPT来替代 |
| 23 |
BIN_
TO_
NUM |
SELECT BIN_TO_NUM(1,0,1,0) AS VALUE1 FROM
DUAL |
