返回第一个非空表达式

DECODE

COALESCE

当前序列值

CURRVAL

N/A

下一个序列值

NEXTVAL

N/A

如果exp1 = exp2, 返回null

DECODE

NULLIF

用户登录账号ID数字

UID

SUSER_ID

用户登录名

USER

SUSER_NAME

用户数据库ID数字

UID

USER_ID

用户数据库名

USER

USER_NAME

当前用户

CURRENT_USER

CURRENT_USER

用户环境(audit trail)

USERENV

N/A

在CONNECT BY子句中的级别

LEVEL

N/A

Average

AVG

AVG

Count

COUNT

COUNT

Maximum

MAX

MAX

Minimum

MIN

MIN

Standard deviation

STDDEV

STDEV or STDEVP

Summation

SUM

SUM

Variance

VARIANCE

VAR or VARP

DECODE (test_value,
expression1, value1
[[,expression2, value2] […]]
[,default_value]
)

CREATE VIEW STUDENT_ADMIN.STUDENT_GPA
(SSN, GPA)
AS SELECT SSN, ROUND(AVG(DECODE(grade
,'A', 4
,'A+', 4.3
,'A-', 3.7
,'B', 3
,'B+', 3.3
,'B-', 2.7
,'C', 2
,'C+', 2.3
,'C-', 1.7
,'D', 1
,'D+', 1.3
,'D-', 0.7
,0)),2)
FROM STUDENT_ADMIN.GRADE
GROUP BY SSN

CASE input_expression
WHEN when_expression THEN    result_expression
[[WHENwhen_expressionTHEN    result_expression] [...]]
[ELSEelse_result_expression]
END

CREATE VIEW STUDENT_ADMIN.STUDENT_GPA
(SSN, GPA)
AS SELECT SSN, ROUND(AVG(CASE grade
WHEN 'A' THEN 4
WHEN 'A+' THEN 4.3
WHEN 'A-' THEN 3.7
WHEN 'B' THEN 3
WHEN 'B+' THEN 3.3
WHEN 'B-' THEN 2.7
WHEN 'C' THEN 2
WHEN 'C+' THEN 2.3
WHEN 'C-' THEN 1.7
WHEN 'D' THEN 1
WHEN 'D+' THEN 1.3
WHEN 'D-' THEN 0.7
ELSE 0
END),2)
FROM STUDENT_ADMIN.GRADE
GROUP BY SSN

字符到数字

TO_NUMBER('10')

CONVERT(numeric, '10')

数字到字符

TO_CHAR(10)

CONVERT(char, 10)

字符到日期

TO_DATE('04-JUL-97')
TO_DATE('04-JUL-1997',
'dd-mon-yyyy')
TO_DATE('July 4, 1997',
'Month dd, yyyy')

CONVERT(datetime, '04-JUL-97')
CONVERT(datetime, '04-JUL-1997')
CONVERT(datetime, 'July 4, 1997')

日期到字符

TO_CHAR(sysdate)
TO_CHAR(sysdate, 'dd mon yyyy')
TO_CHAR(sysdate, 'mm/dd/yyyy')

CONVERT(char, GETDATE())
CONVERT(char, GETDATE(), 106)
CONVERT(char, GETDATE(), 101)

16进制到2进制

HEXTORAW('1F')

CONVERT(binary, '1F')

2进制到16进制

RAWTOHEX
(binary_column)

CONVERT(char, binary_column)

-

0 or 100 (*)

Default

mon dd yyyy hh:miAM (or PM)

1

101

USA

mm/dd/yy

2

102

ANSI

yy.mm.dd

3

103

British/French

dd/mm/yy

4

104

German

dd.mm.yy

5

105

Italian

dd-mm-yy

6

106

-

dd mon yy

7

107

-

mon dd, yy

8

108

-

hh:mm:ss

-

9 or 109 (*)

Default milliseconds

mon dd yyyy hh:mi:ss:mmm (AM or PM)

10

110

USA

mm-dd-yy

11

111

Japan

yy/mm/dd

12

112

ISO

yymmdd

-

13 or 113 (*)

Europe default

dd mon yyyy hh:mm:ss:mmm(24h)

14

114

-

hh:mi:ss:mmm(24h)

SELECT SSN, FNAME, LNAME, )    TUITION_PAID,
   TUITION_PAID/GET_SUM_
   MAJOR(MAJOR)
   AS PERCENT_MAJOR
FROM STUDENT_ADMIN.STUDENT

SELECT SSN, FNAME, LNAME, TUITION_PAID, TUITION_PAID/SUM_MAJOR AS PERCENT_MAJOR
FROM STUDENT_ADMIN.STUDENT,
  (SELECT MAJOR,      SUM(TUITION_PAID) SUM_MAJOR
  FROM STUDENT_ADMIN.STUDENT
  GROUP BY MAJOR) SUM_STUDENT
WHERE STUDENT.MAJOR =      SUM_STUDENT.MAJOR

CREATE OR REPLACE FUNCTION GET_SUM_MAJOR
(INMAJOR VARCHAR2) RETURN NUMBER
AS SUM_PAID NUMBER;
BEGIN
SELECT SUM(TUITION_PAID) INTO    SUM_PAID
FROM STUDENT_ADMIN.STUDENT
WHERE MAJOR = INMAJOR;
RETURN(SUM_PAID);
END GET_SUM_MAJOR;

No CREATE FUNCTION syntax is required; use CREATE PROCEDURE syntax.

等于

(=)

(=)

大于

(>)

(>)

小于

(<)

(<)

大于或等于

(>=)

(>=)

小于或等于

(<=)

(<=)

不等于

(!=, <>, ^=)

(!=, <>, ^=)

不大于，不小于

N/A

!> , !<

在集合中任意成员中

IN

IN

不在集合中的任何成员中

NOT IN

NOT IN

集合中的任意值

ANY, SOME

ANY, SOME

提交集合中的所有值

!= ALL, <> ALL, < ALL,
> ALL, <= ALL, >= ALL, != SOME, <> SOME,
< SOME, > SOME,
<= SOME, >= SOME

!= ALL, <> ALL, < ALL,
> ALL, <= ALL, >= ALL, != SOME, <> SOME,
< SOME, > SOME,
<= SOME, >= SOME

像模式（Like pattern）

LIKE

LIKE

不像模式（Not like pattern）

NOT LIKE

NOT LIKE

X和y之间的值

BETWEEN x AND y

BETWEEN x AND y

不在x和y之间的值

NOT BETWEEN

NOT BETWEEN

值存在

EXISTS

EXISTS

值不存在

NOT EXISTS

NOT EXISTS

值{为|不为}空

IS NULL, IS NOT NULL

Same. Also = NULL,
!= NULL for backward compatibility (not recommended).

SELECT * FROM STUDENT_ADMIN.STUDENT
WHERE LNAME LIKE 'A%'
OR LNAME LIKE 'B%'
OR LNAME LIKE 'C%'

SELECT * FROM STUDENT_ADMIN.STUDENT
WHERE LNAME LIKE '[ABC]%'

SELECT FNAME||' '||LNAME AS NAME
FROM STUDENT_ADMIN.STUDENT

SELECT FNAME +' '+ LNAME AS    NAME
FROM STUDENT_ADMIN.STUDENT

声明变量

DECLARE

DECLARE

语句块

BEGIN...END;

BEGIN...END

条件处理

IF…THEN,
ELSIF…THEN,
ELSE
ENDIF;

IF…[BEGIN…END]
ELSE <condition>
[BEGIN…END]
ELSE IF <condition>
CASE expression

无条件结束

RETURN

RETURN

无条件结束当前程序块后面的语句

EXIT

BREAK

重新开始一个WHILE循环

N/A

CONTINUE

等待指定间隔

N/A (dbms_lock.sleep)

WAITFOR

循环控制

WHILE LOOP…END LOOP;

LABEL…GOTO LABEL;
FOR…END LOOP;
LOOP…END LOOP;

WHILE <condition>
BEGIN… END

LABEL…GOTO LABEL

程序注释

/* … */, --

/* … */, --

打印输出

RDBMS_OUTPUT.PUT_
LINE

PRINT

引发程序错误（Raise program error）

RAISE_APPLICATION_
ERROR

RAISERROR

执行程序

EXECUTE

EXECUTE

语句终止符

Semicolon (;)

N/A

DECLARE
VSSN CHAR(9);
VFNAME VARCHAR2(12);
VLNAME VARCHAR2(20);
VBIRTH_DATE DATE;
VLOAN_AMOUNT NUMBER(12,2);

DECLARE
@VSSN CHAR(9),
@VFNAME VARCHAR2(12),
@VLNAME VARCHAR2(20),
@VBIRTH_DATE DATETIME,
@VLOAN_AMOUNT NUMERIC(12,2)

Assignment operator (:=)

SET @local_variable = value

SELECT...INTO syntax for selecting column values from a single row

SELECT @local_variable = expression [FROM…] for assigning a literal value, an expression involving other local variables, or a column value from a single row

FETCH…INTO syntax

FETCH…INTO syntax

DECLARE VSSN CHAR(9);
VFNAME VARCHAR2(12);
VLNAME VARCHAR2(20);
BEGIN
VSSN := '123448887';
SELECT FNAME, LNAME INTO VFNAME, VLNAME FROM STUDENTS WHERE SSN=VSSN;
END;

DECLARE @VSSN CHAR(9),
@VFNAME VARCHAR(12),
@VLNAME VARCHAR(20)
SET @VSSN = '12355887'
SELECT @VFNAME=FNAME, @VLNAME=LNAME FROM STUDENTS WHERE SSN = @VSSN

DECLARE
DECLARE VARIABLES ...
BEGIN -- THIS IS REQUIRED SYNTAX
PROGRAM_STATEMENTS ...
IF ...THEN
STATEMENT1;
STATEMENT2;
STATEMENTN;
END IF;
WHILE ... LOOP
STATEMENT1;
STATEMENT2;
STATEMENTN;
END LOOP;
END; -- THIS IS REQUIRED SYNTAX

DECLARE
DECLARE VARIABLES ...
BEGIN -- THIS IS OPTIONAL SYNTAX
PROGRAM_STATEMENTS ...
IF ...
BEGIN
STATEMENT1
STATEMENT2
STATEMENTN
END
WHILE ...
BEGIN
STATEMENT1
STATEMENT2
STATEMENTN
END
END -- THIS IS REQUIRED SYNTAX

DECLARE
VDEGREE_PROGRAM CHAR(1);
VDEGREE_PROGRAM_NAME VARCHAR2(20);
BEGIN
VDEGREE_PROGRAM := 'U';
IF VDEGREE_PROGRAM = 'U' THEN
VDEGREE_PROGRAM_NAME :=
'Undergraduate';
ELSIF VDEGREE_PROGRAM = 'M'  THEN VDEGREE_PROGRAM_
    NAME := 'Masters';
ELSIF VDEGREE_PROGRAM = 'P'     THEN VDEGREE_PROGRAM_
NAME := 'PhD';
ELSE VDEGREE_PROGRAM_
NAME := 'Unknown';
END IF;
END;

DECLARE
@VDEGREE_PROGRAM CHAR(1),
@VDEGREE_PROGRAM_NAME VARCHAR(20)
SELECT @VDEGREE_PROGRAM = 'U'
SELECT @VDEGREE_PROGRAM_
   NAME = CASE @VDEGREE_PROGRAM
   WHEN 'U' THEN 'Undergraduate'
   WHEN 'M' THEN 'Masters'
   WHEN 'P' THEN 'PhD'.
   ELSE 'Unknown'
END

WHILE Boolean_expression

    {sql_statement | statement_block}

    [BREAK] [CONTINUE]

DECLARE
COUNTER NUMBER;
BEGIN
COUNTER := 0
WHILE (COUNTER <5) LOOP
COUNTER := COUNTER + 1;
END LOOP;
END;

DECLARE
@COUNTER NUMERIC
SELECT@COUNTER = 1
WHILE (@COUNTER <5)
BEGIN
   SELECT @COUNTER =
   @COUNTER +1
END

GOTO label;
<<label name here>>

GOTO label

RETURN expression:

RETURN [integer_expression]

RAISERROR ({msg_id | msg_str}, severity, state

    [, argument1 [, argument2]])

    [WITH options]

CREATE OR REPLACE FUNCTION
DEPT_ADMIN.DELETE_DEPT
(VDEPT IN VARCHAR2) RETURN NUMBER AS
BEGIN
DELETE FROM DEPT_ADMIN.DEPT
WHERE DEPT = VDEPT;
RETURN(SQL%ROWCOUNT);
EXCEPTION
WHEN OTHER THEN
RAISE_APPLICATION_ERROR
(-20001,SQLERRM);
END DELETE_DEPT;
/

CREATE PROCEDURE
DEPT_ADMIN.DELETE_DEPT
@VDEPT VARCHAR(4) AS
DELETE FROM DEPT_DB.DBO.DEPT
WHERE DEPT = @VDEPT
RETURN @@ROWCOUNT
GO