ORACLE 系统函数大全SQLSERVER系统函数的异同

字符函数

下面是Oracle支持的字符函数和它们的Microsoft SQL Server等价函数。

函数 Oracle Microsoft SQL　Server
把字符转换为ASCII :ASCII ASCII
字串连接: CONCAT ————–(expression + expression)
把ASCII转换为字符 CHR, CHAR
返回字符串中的开始字符（左起） INSTR ,—————CHARINDEX
把字符转换为小写 LOWER ———————LOWER
把字符转换为大写 UPPER——————– UPPER
填充字符串的左边 LPAD ——————–N/A
清除开始的空白 LTRIM——————–LTRIM
清除尾部的空白 RTRIM ——————–RTRIM
字符串中的起始模式（pattern） INSTR ——————–PATINDEX
多次重复字符串 RPAD ——————–REPLICATE
字符串的语音表示 SOUNDEX ——————–SOUNDEX
重复空格的字串 RPAD ——————–SPACE
从数字数据转换为字符数据 TO_CHAR ——————–STR
子串 SUBSTR ——————–SUBSTRING
替换字符 REPLACE ——————–STUFF
将字串中的每个词首字母大写 INITCAP ——————–N/A
翻译字符串 TRANSLATE ——————–N/A
字符串长度 LENGTH——————– DATELENGTH or LEN
列表中最大的字符串 GREATEST——————– N/A
列表中最小的字符串 LEAST ——————–N/A
如果为NULL则转换字串 NVL——————– ISNULL

日期函数

下面是Oracle支持的日期函数和它们的Microsoft SQL Server等价函数。

函数 Oracle ——————–Microsoft SQL　Server
日期相加 (date column +/- value) or ADD_MONTHS  ——————–DATEADD

两个日期的差 (date column +/- value) or MONTHS_BETWEEN  ——————–DATEDIFF
当前日期和时间 SYSDATE ——————–GETDATE()
一个月的最后一天 LAST_DAY  ——————–N/A
时区转换 NEW_TIME  ——————–N/A
日期后的第一个周日 NEXT_DAY  ——————–N/A
代表日期的字符串 TO_CHAR ——————–DATENAME
代表日期的整数 TO_NUMBER (TO_CHAR))  ——————–DATEPART
日期舍入 ROUND  ——————–CONVERT
日期截断 TRUNC  ——————–CONVERT
字符串转换为日期 TO_DATE  ——————–CONVERT
如果为NULL则转换日期 NVL ——————– ISNULL

转换函数

下面是Oracle支持的转换函数和它们的Microsoft SQL Server等价函数。

函数 Oracle  ——————–Microsoft SQL　Server
数字转换为字符 TO_CHAR  ——————–CONVERT
字符转换为数字 TO_NUMBER  ——————–CONVERT
日期转换为字符 TO_CHAR  ——————–CONVERT
字符转换为日期 TO_DATE CONVERT
16进制转换为2进制 HEX_TO_RAW  ——————–CONVERT
2进制转换为16进制 RAW_TO_HEX  ——————–CONVERT

其它行级别的函数

下面是Oracle支持的其它行级别的函数以及它们的Microsoft SQL Server等价函数。

函数 Oracle  ——————–Microsoft SQL　Server
返回第一个非空表达式 DECODE ————————————-COALESCE
当前序列值 CURRVAL  ——————–N/A
下一个序列值 NEXTVAL  ——————–N/A

用户登录账号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

合计函数

下面是Oracle支持的合计函数和它们的Microsoft SQL Server等价函数。

函数 Oracle  ——————–Microsoft SQL　Server
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

条件测试

Oracle的DECODE语句和Microsoft SQL Server的CASE表达式都执行条件测试。
当test_value中的值和后面的任何表达式匹配的时候，相关的值就返回。如果没有找到任何匹配的值，就返回default_value。
如果没有指定default_value，在没有匹配的时候，DECODE和CASE都返回一个NULL。下表显示了该语句的语法，
同时给出了转换DECODE命令的示例。

Oracle Microsoft SQL　
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

[ELSE else_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

CASE表达式可以支持用SELECT语句执行布尔测试，这是DECODE命令所不允许的。欲了解关于CASE表达式的详细信息，
请参阅SQL Server联机手册。

把值转换为不同的数据类型

Microsoft SQL Server的CONVERT和CAST函数都是多目标转换函数。它们提供了相似的功能，
把一种数据类型的表达式转换为另一种数据类型的表达式，并且支持多种专门数据的格式。

CAST(expression AS data_type)
CONVERT (data type[(length)], expression [, style])
CAST是一个SQL-92标准的函数。这些函数执行同Oracle的TO_CHAR、TO_NUMBER、TO_DATE、HEXTORAW以及RAWTOTEXT函数相同的功能。

这里所指的数据类型是任何表达式将被转换成为的系统数据类型。不能使用用户定义的数据类型。长度参数是可选的，
该参数用于char、varchar、binary以及varbinary数据类型。允许的最大长度是8000。

转换 Oracle Microsoft SQL　Server
字符到数字 TO_NUMBER(ཆ’)  ——————–CONVERT(numeric, ཆ’)
数字到字符 TO_CHAR(10)  ——————–CONVERT(char, 10)
字符到日期 TO_DATE(ཀ-JUL-97′)
TO_DATE(ཀ-JUL-1997′,’dd-mon-yyyy’)

TO_DATE(‘July 4, 1997’, ‘Month dd, yyyy’) ——————–CONVERT(datetime, ཀ-JUL-97′)

CONVERT(datetime, ཀ-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(ƇF’)——————– CONVERT(binary, ƇF’)
2进制到16进制 RAWTOHEX(binary_column) ——————–CONVERT(char, binary_column)

请注意字符串是怎样转换为日期的。在Oracle中，缺省的日期格式模型是”DD-MON-YY”如果你使用任何其它格式，
你必须提供一个合适的日期格式模型。CONVERT函数自动转换标准日期格式，不需要任何格式模型。

从日期转换到字符串时，CONVERT函数的缺省输出是”dd mon yyyy hh:mm:ss:mmm(24h)”。
用一个数字风格代码来格式化输出，使它能输出为其它类型的日期格式模型。欲了解CONVERT函数的详细信息，请参阅SQL Server联机手册。

下表显示了Microsoft SQL Server日期的缺省输出。

Without Century With Century Standard Output
– 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)

用户自定义函数

Oracle PL/SQL函数可以在Oracle SQL语句中使用。在Microsoft SQL Server中一般可以通过其它方式来实现同样的功能。

在SQL Server中可以用表中给出的查询来代替。

Oracle Microsoft SQL　Server
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.

比较操作符

Oracle和Microsoft SQL Server的比较操作符几乎是一样的。

算符 Oracle Microsoft SQL　Server
等于 (=) (=)
大于 (>) (>)
小于 (<) (<)
大于或等于 (>=) (>=)
小于或等于 (<=) (<=)
不等于 (!=, <>, ^=) (!=, <>, ^=)
不大于，不小于 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).

模式匹配

SQL Server的LIKE关键字提供了有用的通配符搜索功能，这个功能在Oracle中不支持
除了所有的RDBMS都支持的（%）和（_）通配符以外，SQL Server还支持（[ ]）和（[^]）通配符。

（[ ]）字符用来查询在一个范围内的所有单个字符。例如，如果你需要查询包含一个从a到f的字符的数据，
你可以这样写：“LIKE ‘[a-f]’”或者“LIKE ‘[abcdef]’”。这些附加的通配符的有效性在下表中给出。

Oracle:
SELECT * FROM STUDENT_ADMIN.STUDENT
WHERE LNAME LIKE ‘A%’
OR LNAME LIKE ‘B%’
OR LNAME LIKE ‘C%’ ;

Microsoft SQL:

SELECT * FROM STUDENT_ADMIN.STUDENT
WHERE LNAME LIKE ‘[ABC]%’;

[^]通配符用来标记那些不在特定范围内的字符。例如，如果除了a到f以外的所有字符都是可以接受的，你可以这样书写：
LIKE ‘[^a – f]’或者LIKE ‘[^abcdef]’。

欲了解关于LIKE关键字的详细信息，请参阅SQL Server联机手册。

在比较中使用NULL

尽管Microsoft SQL Server传统上支持SQL-92标准的和一些非标准的NULL行为，但是它还是支持Oracle中的NULL的用法。

为了支持分布式查询，SET ANSI_NULLS必须设定为ON。

在进行连接的时候，SQL Server的SQL Server ODBC驱动程序和OLE DB提供者自动把SET ANSI_NULLS设定为ON。
这个设置可以在ODBC数据源、ODBC连接属性、或者是在连接到SQL Server之前在应用程序中设置的OLE DB连接属性中进行配置。
在从DB-Library应用程序中连接时，SET ANSI_NULLS缺省为OFF。

当SET ANSI_DEFAULTS为ON时，SET ANSI_NULLS被允许。

欲了解关于NULL用法的详细信息，请参阅SQL Server联机手册。

字串连接

Oracle使用两个管道符号（||）来作为字串连接操作符，SQL Server则使用加号（+）。这个差别要求你在应用程序中做小小的修改。

Oracle: 　
SELECT FNAME||’ ‘||LNAME AS NAME
FROM STUDENT_ADMIN.STUDENT;

/
———————————————–

Microsoft SQL:
 SELECT FNAME +’ ‘+ LNAME AS 　　　NAME
FROM STUDENT_ADMIN.STUDENT

流控制（Control-of-Flow）语言

流控制语言控制SQL语句执行流，语句块以及存储过程。PL/SQL和Transact-SQL提供了多数相同的结构，但是还是有一些语法差别。

关键字

这是两个RDBMS支持的关键字。

语句 Oracle PL/SQL :

声明变量 DECLARE DECLARE
语句块 BEGIN…END; BEGIN…END
条件处理 IF…THEN,
ELSIF…THEN,
ELSE
ENDIF;
——————————————————–

Microsoft SQL　Server Transact-SQL:
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

声明变量

Transact-SQL和PL/SQL的变量是用DECLARE关键字创建的。T[email protected]/* */，
并且就像PL/SQL一样，在第一次创建时，用空值初始化。

Oracle : 　
DECLARE
VSSN CHAR(9);
VFNAME VARCHAR2(12);
VLNAME VARCHAR2(20);
VBIRTH_DATE DATE;

VLOAN_AMOUNT NUMBER(12,2);
—————————————–

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

Transact-SQL不支持%TYPE和%ROWTYPE变量数据类型定义。一个Transact-SQL变量不能在DECLARE命令中初始化。
在Microsoft SQL Server数据类型定义中也不能使用Oracle的NOT NULL和CONSTANT关键字。

像Oracle的LONG和LONG RAW数据类型一样。文本和图形数据类型不能被用做变量定义。
此外，Transact-SQL不支持PL/SQL风格的记录和表的定义。

给变量赋值

Oracle和Microsoft SQL Server提供了下列方法来为本地变量赋值。

Oracle Microsoft SQL　
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

这里有一些语法示例

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

/
——————————————————————————

Microsoft SQL:
DECLARE @VSSN CHAR(9),
@VFNAME VARCHAR(12),
@VLNAME VARCHAR(20)
SET @VSSN = ?’
SELECT @VFNAME=FNAME, @VLNAME=LNAME FROM STUDENTS WHERE SSN = @VSSN

语句块

Oracle PL/SQL和Microsoft SQL Server Transact-SQL都支持用BEGIN…END术语来标记语句块。
Transact-SQL不需要在DECLARE语句后使用一个语句块。
———————————————————————————
-如果在Microsoft SQL Server
中的IF语句和WHILE循环中有多于一个语句被执行，则需要使用BEGIN…END语句块。

Oracle: Microsoft SQL:
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

条件处理

Microsoft SQL Server Transact-SQL的条件语句包括IF和ELSE，但不包括Oracle PL/SQL中的ELSEIF语句。
可以用嵌套多重IF语句来到达同样的效果。对于广泛的条件测试，用CASE表达式也许更容易和可读一些。

Oracle Microsoft SQL　
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)
set @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

重复执行语句（循环）

Oracle PL/SQL提供了无条件的LOOP和FOR LOOP。Transact-SQL则提供了WHILE循环和GOTO语句。

WHILE Boolean_expression
{sql_statement | statement_block}

[BREAK] [CONTINUE]

WHILE循环需要测试一个布尔表达式来决定一个或者多个语句的重复执行。
只要给定的表达式结果为真，这个（些）语句就一直重复执行下去。如果有多个语句需要执行，则这些语句必须放在一个BEGIN…END块中。

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

/
——————————————————

Microsoft SQL:
DECLARE
@COUNTER NUMERIC
SELECT @COUNTER = 1
WHILE (@COUNTER <5)
BEGIN
　　　SELECT @COUNTER =
[email protected]/* */ +1
END

语句的执行可以在循环的内部用BREAK和CONTINUE关键字控制。BREAK关键字使WHILE循环无条件的结束，
而CONTINUE关键字使WHILE循环跳过后面的语句重新开始。BREAK关键字同Oracle PL/SQL中的EXIT关键字是等价的。
而在Oracle中没有和CONTINUE等价的关键字

GOTO语句

Oracle和Microsoft SQL Server都有GOTO语句，但是语法不同。GOTO语句使Transact-SQL跳到指定的标号处运行，
在GOTO语句后指定标号之间的任何语句都不会被执行。

Oracle Microsoft SQL　
GOTO label;
<<label name here>> GOTO label

PRINT语句

Transact-SQL的PRINT语句执行同PL/SQL的RDBMS_OUTPUT.put_line 过程同样的操作。该语句用来打印用户给定的消息。

用PRINT语句打印的消息上限是8,000个字符。定义为char或者varchar数据类型的变量可以嵌入打印语句。
如果使用其它数据类型的变量，则必须使用CONVERT或者CAST函数。本地变量、全局变量可以被打印。可以用单引号或者双引号来封闭文本。

从存储过程返回

Microsoft SQL Server和Oracle都有RETURN语句。RETURN使你的程序从查询或者过程中无条件的跳出。RETURN是立即的、
完全的、并且可以用于从过程、批处理或者语句块的任意部分跳出。在REUTRN后面的语句将不会被执行。

Oracle Microsoft SQL　
RETURN expression: RETURN [integer_expression]

引发程序错误（Raising program errors）

Transact-SQL的RAISERROR返回一个用户定义的错误消息，并且设置一个系统标志来记录发生了一个错误。
这个功能同PL/SQL的raise_application_error异常处理器的功能是相似的。

RAISERROR语句允许客户重新取得sysmessages表的一个入口，或者用用户指定的严重性和状态信息动态的建立一条消息。
在被定义后，消息被送回客户端作为系统错误消息。

RAISERROR ({msg_id | msg_str}, severity, state
[, argument1 [, argument2>)

[WITH options]

在转换你的PL/SQL程序时，也许用不着使用RAISERROR语句。在下面的示例代码中。
PL/SQL程序使用raise_application_error异常处理器，但是Transact-SQL程序则什么也没用。
包括raise_application_error异常处理器是为了防止PL/SQL返回不明确的未经处理的异常错误消息。
作为代替，当一个不可预见的问题发生的时候，异常处理器总是返回Oracle错误消息。

当一个Transact-SQL失败时，它总是返回一个详细的错误消息给客户程序。因此，除非需要某些特定的错误处理，
一般是不需要RAISERROR语句的。

Oracle Microsoft SQL　
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

实现游标

Oracle在使用SELECT语句时总是需要游标，不管从数据库中请求多少行。在Microsoft SQL Server，
SELECT语句并不把在返回客户的行上附加游标作为缺省的结果集合。这是一种返回数据给客户应用程序的有效的方法。

SQL Server为游标函数提供了两种接口。当在Transact-SQL批处理或者存储过程中使用游标的时候，SQL语句可用来声明、
打开、和从游标中抽取，就像定位更新和删除一样。当使用来自DB-Library、ODBC、或者OLEDB程序的游标时，SQL Server
显式的调用内建的服务器函数来更有效的处理游标。

当从Oracle输入一个PL/SQL过程时，首先判断是否需要在Transact-SQL中采用游标来实现同样的功能。如果游标仅仅返回一
组行给客户程序，就使用非游标的SELECT语句来返回缺省的结果集合。如果游标用来从行中一次取得一个数据给本地过程变量，
你就必须在Transact-SQL中使用游标。

语法

下表显示了使用游标的语法。

操作 Oracle Microsoft SQL　Server
声明一个游标 CURSOR cursor_name [(cursor_parameter(s))]
IS select_statement;
—————————————————-
DECLARE cursor_name CURSOR
[LOCAL | GLOBAL]
[FORWARD_ONLY | SCROLL]
[STATIC | KEYSET | DYNAMIC | FAST_FORWARD]
[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]
[TYPE_WARNING]
FOR select_statement
[FOR UPDATE [OF column_name [,…n>]
打开一个游标 OPEN cursor_name [(cursor_parameter(s))];
—————-
OPEN cursor_name
从游标中提取（Fetching） FETCH cursor_name INTO variable(s)
——————————————————————————————-
FETCH FROM] cursor_name
[INTO @variable(s)]
更新提取行 UPDATE table_name
SET statement(s)…
WHERE CURRENT OF cursor_name; UPDATE table_name
SET statement(s)…
WHERE CURRENT OF cursor_name
删除提取行 DELETE FROM table_name
WHERE CURRENT OF cursor_name; DELETE FROM table_name
WHERE CURRENT OF cursor_name
关闭游标 CLOSE cursor_name; CLOSE cursor_name
清除游标数据结构 N/A DEALLOCATE cursor_name

声明一个游标

尽管Transact-SQL DECLARE CURSOR语句不支持游标参数的使用，但它确实支持本地变量。当游标打开的时候，
它就使用这些本地变量的值。Microsoft SQL Server在其DECLARE CURSOR中提供了许多附加的功能。

INSENSITIVE选项用来定义一个创建数据的临时拷贝以被游标使用的游标。游标的所有请求都由这个临时表来应答。因此
对原表的修改不会反映到那些由fetch返回的用于该游标的数据上。这种类型的游标访问的数据是不能被修改的。

应用程序可以请求一个游标类型然后执行一个不被所请求的服务器游标类型支持的Transact-SQL语句。SQL Server返回一个错误，
指出该游标类型被改变了，或者给出一组参数，隐式的转换游标。欲取得一个触发SQL Server 7.0隐式的把游标从一种类型转换为
另一种类型的参数的完整列表，请参阅SQL Server联机手册。

SCROLL选项允许除了前向的抽取以外，向后的、绝对的和相对的数据抽取。一个滚动游标使用一种键集合的游标模型，在该模型中，
任何用户提交的对表的删除和更新都将影响后来的数据抽取。只有在游标没有用INSENSITIVE选项声明时，上面的特性才起作用。

如果选择了READ ONLY选项，对游标中的行的更新就被禁止。该选项将覆盖游标的缺省选项棗允许更新。

UPDATE [OF column_list]语句用来在游标中定义一个可更新的列。如果提供了[OF column_list]，那么仅仅是那些列出的列可以被修改。
如果没有指定任何列。则所有的列都是可以更新的，除非游标被定义为READ ONLY。

重要的是，注意到一个SQL Server游标的名字范围就是连接自己。这和本地变量的名字范围是不同的。
不能声明一个与同一个用户连接上的已有的游标相同名字的游标，除非第一个游标被释放。

打开一个游标

Transact-SQL不支持向一个打开的游标传递参数，这一点和PL/SQL是不一样的。当一个Transact-SQL游标被打开以后，
结果集的成员和顺序就固定下来了。其它用户提交的对原表的游标的更新和删除将反映到对所有未加INSENSITIVE选项定义
的游标的数据抽取上。对一个INSENSITIVE游标，将生成一个临时表。

抽取数据

Oracle游标只能向前移动棗没有向后或者相对滚动的能力。SQL Server游标可以向前或者向后滚动，具体怎么滚动，
要由下表给出的数据抽取选项来决定。只有在游标是用SCROLL选项声明的前提下，这些选项才能使用。

卷动选项 描述
NEXT 如果这是对游标的第一次提取，则返回结果集合的第一行；否则，在结果结合内移动游标到下一行。
NEXT是在结果集合中移动的基本方法 。NEXT是缺省的游标提取（fetch）。
PRIOR 返回结果集合的前一行。
FIRST 把游标移动到结果集合的第一行，同时返回第一行。
LAST 把游标移动到结果集合的最后一行，同时返回最后一行。
ABSOLUTE n 返回结果集合的第n行。如果n为负数，则返回倒数第n行
RELATIVE n 返回当前提取行后的第n行，如果n是负数，则返回从游标相对位置起的倒数第n行。

Transact-SQL的FETCH语句不需要INTO子句。如果没有指定返回变量，行就自动作为一个单行结果集合返回给客户。但是，
如果你的过程必须把行给客户，一个不带游标的SELECT语句更有效一些。

在每一个FETCH后面，@@FETCH_STATUS函数被更新。这和在PL/SQL中使用CURSOR_NAME%FOUND和CURSOR_NAME%NOTFOUND变量是相似的
。@@FETCH_STATUS函数在每一次成功的数据抽取以后被设定为0。如果数据抽取试图读取一个超过游标末尾的数据，则返回一个为-1的值。
如果请求的行在游标打开以后从表上被删除了，@@FETCH_STATUS函数就返回一个为-2的值。只有游标是用SCROLL选项定义的情况下，
才会返回-2值。在每一次数据抽取之后都必须检查该变量，以确保数据的有效性。

SQL Server不支持Oracle的游标FOR循环语法。

CURRENT OF子句

更新和删除的CURRENT OF子句语法和函数在PL/SQL和Transact-SQL中是一样的。在给定游标中，在当前行上执行定位的UPDATE和DELETE。

关闭一个游标

Transact-SQL的CLOSE CURSOR语句关闭游标，但是保留数据结构以备重新打开。PL/SQL 的CLOSE CURSOR语句关闭并且释放所有的数据结构。

Transact-SQL需要用DEALLOCATE CURSOR语句来清除游标数据结构。DEALLOCATE CURSOR语句同CLOSE CURSOR是不一样的，
后者保留数据结构以备重新打开。DEALLOCATE CURSOR释放所有与游标相关的数据结构并且清除游标的定义。

游标示例

下面的例子显示了在PL/SQL和Transact-SQL等价的游标语句。

Oracle Microsoft SQL　
———————————————————————————————————–
DECLARE
VSSN CHAR(9);
VFNAME VARCHAR(12);
VLNAME VARCHAR(20);
———————————————————————————————————–
DECLARE
@VSSN CHAR(9),
@VFNAME VARCHAR(12),
@VLNAME VARCHAR(20)
CURSOR CUR1
IS
SELECT SSN, FNAME, LNAME
FROM STUDENT ORDER BY LNAME;

BEGIN
　　　　OPEN CUR1;
　　　　FETCH CUR1 INTO VSSN, 　　　　VFNAME, VLNAME;
　　　　WHILE (CUR1%FOUND) LOOP
　　　　FETCH CUR1 INTO VSSN, 　　　　VFNAME, VLNAME;
END LOOP;
CLOSE CUR1;
END;
———————————————————————————————————–
DECLARE curl CURSOR FOR
　　　SELECT SSN, FNAME, LNAME
　　　FROM STUDENT ORDER BY SSN
OPEN CUR1
FETCH NEXT FROM CUR1
　　　INTO @VSSN, @VFNAME, @VLNAME
WHILE (@@FETCH_STATUS <> -1)
　　　BEGIN
　　FETCH NEXT FROM CUR1 INTO [email protected]/* */, @VFNAME, @VLNAME
　　　END
CLOSE CUR1
DEALLOCATE CUR1

以上就是【ORACLE 系统函数大全SQLSERVER系统函数的异同】的全部内容了,欢迎留言评论进行交流!