Daquan ORACLE internal functions as well as the difference between SQLSERVER

2010-07-30  来源:本站原创  分类:Database  人气:192 

Daquan ORACLE internal functions as well as the difference between SQLSERVER Collection

Character function

Here are the characters Oracle support functions and their Microsoft SQL Server equivalent function.

Functions Oracle Microsoft SQL Server
The character is converted to ASCII: ASCII ASCII
String connection: CONCAT --------------( expression + expression)
Convert the ASCII character CHR, CHAR
Back to the beginning of the string in characters (from left) INSTR ,--------------- CHARINDEX
The characters converted to lowercase LOWER --------------------- LOWER
The characters converted to uppercase UPPER -------------------- UPPER
Filled the left side of the string LPAD -------------------- N / A
Clear the beginning of a blank LTRIM -------------------- LTRIM
Clear tail blank RTRIM -------------------- RTRIM
String starting model (pattern) INSTR -------------------- PATINDEX
Repeated string RPAD -------------------- REPLICATE
String of voice that SOUNDEX -------------------- SOUNDEX
Repeat string RPAD -------------------- SPACE space
From the digital data is converted to character data TO_CHAR -------------------- STR
Substring SUBSTR -------------------- SUBSTRING
Replace the characters REPLACE -------------------- STUFF
The string in the first letter of each word capitalized INITCAP -------------------- N / A
Translation string TRANSLATE -------------------- N / A
String length LENGTH -------------------- DATELENGTH or LEN
List of the largest string GREATEST -------------------- N / A
List of the smallest string LEAST -------------------- N / A
If NULL then convert the string NVL -------------------- ISNULL

Date function

The following are date functions supported by Oracle and their Microsoft SQL Server equivalent function.

Functions Oracle -------------------- Microsoft SQL Server
Date add (date column + / - value) or ADD_MONTHS -------------------- DATEADD

The difference between two dates (date column + / - value) or MONTHS_BETWEEN -------------------- DATEDIFF
The current date and time SYSDATE -------------------- GETDATE ()
The last day of the month LAST_DAY -------------------- N / A
Time Zone Converter NEW_TIME -------------------- N / A
The first weekday after the date of NEXT_DAY -------------------- N / A
Represent a date string TO_CHAR -------------------- DATENAME
Represent a date integer TO_NUMBER (TO_CHAR)) -------------------- DATEPART
Date rounding ROUND -------------------- CONVERT
Date cut off TRUNC -------------------- CONVERT
String converted to date TO_DATE -------------------- CONVERT
If NULL then the conversion NVL -------------------- ISNULL

Transfer function

The following are conversion functions supported by Oracle and their Microsoft SQL Server equivalent function.

Functions Oracle -------------------- Microsoft SQL Server
Digital conversion for the character TO_CHAR -------------------- CONVERT
Character is converted to digital TO_NUMBER -------------------- CONVERT
The date is converted to character TO_CHAR -------------------- CONVERT
Characters converted to date TO_DATE CONVERT
16 hex is converted to 2 hex HEX_TO_RAW -------------------- CONVERT
2 hex convert hex RAW_TO_HEX -------------------- CONVERT 16

Other line-level function

Here is the other line-level Oracle support functions and their Microsoft SQL Server equivalent function.

Functions Oracle -------------------- Microsoft SQL Server
Back to the first non-empty expression DECODE ------------------------------------- COALESCE
The current sequence value CURRVAL -------------------- N / A
The next sequence value NEXTVAL -------------------- N / A

User login account ID number UID -------------------- SUSER_ID
User login USER -------------------- SUSER_NAME
User database ID number UID -------------------- USER_ID
User Database name USER -------------------- USER_NAME
Current user CURRENT_USER -------------------- CURRENT_USER
User environment (audit trail) USERENV -------------------- N / A
CONNECT BY clause in the level of LEVEL -------------------- N / A

Aggregate function

The following are aggregate functions supported by Oracle and their Microsoft SQL Server equivalent function.

Functions 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

Test conditions

Oracle's DECODE statements, and Microsoft SQL Server's CASE expressions are the implementation of the conditions tested.
When test_value values and subsequent match any expression when the value of the relevant returns. If you do not find any matching value, the return default_value.
If not specified, default_value, in the absence of matching the time, DECODE and CASE return a NULL. The following table shows the syntax of the statement,
DECODE command is given an example of the conversion.

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

SELECT CASE expression can support the statement with the implementation of Boolean testing, it is DECODE command is not allowed. For more information on the CASE expression,
Please refer to the SQL Server Books Online.

The value is converted to different data types

Microsoft SQL Server's CONVERT and CAST functions are multiple target transfer function. They provide similar functionality,
To convert a data type of expression to another data type of expression, and support multiple specialized data formats.

CAST (expression AS data_type)
CONVERT (data type [(length)], expression [, style])
CAST is a SQL-92 standard function. The implementation of these functions with the Oracle of the TO_CHAR, TO_NUMBER, TO_DATE, HEXTORAW and RAWTOTEXT function the same functionality.

Here, the data type of any expression will be converted into the system data types. Can not use user-defined data types. Length parameter is optional
The parameters used to char, varchar, binary and varbinary data types. Maximum allowed length is 8000.

Convert Oracle Microsoft SQL Server
Characters to a digital TO_NUMBER (ཆ ') -------------------- CONVERT (numeric, ཆ')
Figures to the character TO_CHAR (10) ---------- ---------- CONVERT (char, 10)
Character to date 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')
Date to character 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 hex to 2 decimal HEXTORAW (ƇF')-------------------- CONVERT (binary, ƇF ')
2 to 16 band band RAWTOHEX (binary_column) -------------------- CONVERT (char, binary_column)

Note how the string is converted to date. In Oracle, the default date format model is "DD-MON-YY" If you use any other format
You must provide an appropriate date format model. CONVERT function automatically converts standard date format without any format model.

From the date of conversion to string, CONVERT function, the default output is "dd mon yyyy hh: mm: ss: mmm (24h)".
Style with a digital code to format the output so that it can output for other types of date format model. For more information on CONVERT function, see SQL Server Books Online.

The following table shows the default date Microsoft SQL Server output.

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)

User-defined function

Oracle PL / SQL function can be used in the Oracle SQL statement. In Microsoft SQL Server in general can be other ways to achieve the same functionality.

SQL Server can be used in the table given in the query instead.

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.

Comparison operators

Oracle and Microsoft SQL Server comparison operators are almost the same.

Operator Oracle Microsoft SQL Server
Equal (=) (=)
Greater than (>) (>)
Less than (<) (<)
Greater than or equal to (> =) (> =)
Less than or equal to (<=) (<=)
Not equal (! =, <>, ^ =) (! =, <>, ^ =)
Not more than, not less than N / A!>,! <
In the collection of any members of the IN IN
Not set any of the members of the NOT IN NOT IN
Collection of any value ANY, SOME ANY, SOME
All values presented in the collection! = 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 pattern) LIKE LIKE
Unlike model (Not like pattern) NOT LIKE NOT LIKE
Between X and y values BETWEEN x AND y BETWEEN x AND y
Not a value between x and y NOT BETWEEN NOT BETWEEN
Value exists EXISTS EXISTS
Value does not exist NOT EXISTS NOT EXISTS
Value (is | is not) null IS NULL, IS NOT NULL Same. Also = NULL,
! = NULL for backward compatibility (not recommended).

Pattern matching

SQL Server's LIKE keyword offers useful wildcard search function, this function is not supported in Oracle RDBMS support in addition to all the (%) and (_) wildcard outside, SQL Server also supports ([]) and ([^]) wildcard.

([]) Characters used to query all in a single character within. For example, if you need to query contains a character from a to f data
You may like this: "LIKE '[af]'" or "LIKE '[abcdef]'". The validity of these additional wildcard is given the following table.

Oracle Microsoft SQL
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]%'

[^] Wildcard is used to mark those who are not within the specified range of characters. For example, if in addition to a to f all the characters other than is acceptable, you can write:
LIKE '[^ a - f]' or LIKE '[^ abcdef]'.

For more information on the LIKE keyword, see SQL Server Books Online.

In comparing the use of NULL

Despite the traditional support Microsoft SQL Server standard SQL-92 NULL, and some non-standard behavior, but it still supports the use of Oracle's NULL.

To support the distributed query, SET ANSI_NULLS must be set to ON.

When making connections, SQL Server's SQL Server ODBC driver and OLE DB provider automatically SET ANSI_NULLS set to ON.
This setting can be ODBC data source, ODBC connection attributes, or in connection to the SQL Server settings in the application before the OLE DB connection properties to configure.
From DB-Library application, connection, SET ANSI_NULLS defaults to OFF.

When the SET ANSI_DEFAULTS ON,, SET ANSI_NULLS is allowed.

For more information on the usage of NULL, see SQL Server Books Online.

Connection string

Oracle uses two pipe symbol (| |) operator as the connection string, SQL Server, use the plus sign (+). The difference in the application requires you to make small changes.

Oracle Microsoft SQL
SELECT FNAME | | '' | | LNAME AS NAME
FROM STUDENT_ADMIN.STUDENT
-----------------------------------------------
SELECT FNAME + '' + LNAME AS NAME
FROM STUDENT_ADMIN.STUDENT

Flow control (Control-of-Flow) language

Flow Control Language control of SQL statement execution flow, block, and stored procedures. PL / SQL and Transact-SQL provides the majority of the same structure, but there are some syntax differences.

Keyword

These are two RDBMS to support keyword.

Statement, Oracle PL / SQL --------------------- Microsoft SQL Server Transact-SQL

Declare variables DECLARE DECLARE
Block BEGIN ... END; BEGIN ... END
Conditions dealing with IF ... THEN,
ELSIF ... THEN,
ELSE
ENDIF;
-------------------------------------------------- ------
IF ... [BEGIN ... END]
ELSE <condition>
[BEGIN ... END]
ELSE IF <condition>
CASE expression
Unconditional end RETURN ------------ RETURN
Unconditional end to the current block behind the statement EXIT BREAK
Start of a WHILE loop N / A CONTINUE
Wait for specified interval N / A (dbms_lock.sleep) WAITFOR
Loop control WHILE LOOP ... END LOOP;
------------
LABEL ... GOTO LABEL;
FOR ... END LOOP;
LOOP ... END LOOP;
WHILE <condition>
BEGIN ... END
LABEL ... GOTO LABEL

Program comments / * ... * /, - / * ... * /, -
Printout RDBMS_OUTPUT.PUT_LINE PRINT

Trigger error (Raise program error) RAISE_APPLICATION_ERROR -------------------- RAISERROR

Implementation of the program EXECUTE ---------------------- EXECUTE
Statement terminator Semicolon (;) ------------------ N / A

Declare variables

Transact-SQL and PL / SQL variable is created with the DECLARE keyword. Transact-SQL variable with the @ mark
And as PL / SQL, as the first created, initialized with null values.

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

Transact-SQL does not support the% TYPE and% ROWTYPE variable data type definition. A Transact-SQL variables can not be initialized in the DECLARE command.
In Microsoft SQL Server data type definition can not use the Oracle of NOT NULL and CONSTANT keywords.

Like Oracle's LONG and LONG RAW data type. Text and graphics data types can not be used as variable definitions.
In addition, Transact-SQL does not support the PL / SQL style record and table definitions.

Assign the variables

Oracle and Microsoft SQL Server provides the following methods to assign the local variable.

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

Here are some syntax examples

Oracle Microsoft SQL
DECLARE VSSN CHAR (9);
VFNAME VARCHAR2 (12);
VLNAME VARCHAR2 (20);
BEGIN
VSSN: =? '
SELECT FNAME, LNAME INTO VFNAME, VLNAME FROM STUDENTS WHERE SSN = VSSN;
END;
-------------------------------------------------- ----------------------------
DECLARE @ VSSN CHAR (9),
@ VFNAME VARCHAR (12),
@ VLNAME VARCHAR (20)
SET @ VSSN =? '
SELECT @ VFNAME = FNAME, @ VLNAME = LNAME FROM STUDENTS WHERE SSN = @ VSSN

Block

Oracle PL / SQL and Microsoft SQL Server Transact-SQL support the use of the term to mark the BEGIN ... END block.
Transact-SQL does not need to use the DECLARE statement, a statement after the block.
-------------------------------------------------- -------------------------------
- If the Microsoft SQL Server
The IF statements and WHILE loop is executed more than one statement, you need to use the BEGIN ... END block.

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

Conditional processing

Microsoft SQL Server Transact-SQL statements including IF conditions and ELSE, but does not include Oracle PL / SQL in the ELSEIF statement.
Multiple nested IF statements can be used to reach the same result. Testing for a wide range of conditions, using CASE expression may be more easily and readable number.

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)
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

Repeat statement (loop)

Oracle PL / SQL provides unconditional LOOP and FOR LOOP. Transact-SQL provides a WHILE loop and the GOTO statement.

WHILE Boolean_expression
(Sql_statement | statement_block)

[BREAK] [CONTINUE]

WHILE loop tests a Boolean expression needed to determine one or more repeat statement.
As long as the given expression evaluates to true, this (these) statement has been repeated implemented. If multiple statements need to perform, these statements must be placed in a BEGIN ... END block.

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

Statement in the implementation of the internal loop control with BREAK and CONTINUE keywords. BREAK keyword causes an unconditional end of the WHILE loop,
The CONTINUE keyword to skip the back of the statement WHILE loop to restart. BREAK keyword with Oracle PL / SQL in the EXIT keyword is equivalent.
In the Oracle equivalent is not, and CONTINUE keywords

GOTO statement

Oracle and Microsoft SQL Server has a GOTO statement, but the syntax is different. Transact-SQL GOTO statement to jump to the specified label, run,
After the label specified in the GOTO statement between the statements will not be any implementation.

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

PRINT statement

Transact-SQL for PRINT statements perform the same PL / SQL for RDBMS_OUTPUT.put_line process the same operation. This statement is used to print the message given by the user.

PRINT statement to print the message with the upper limit is 8,000 characters. Is defined as char or varchar data type variable can be embedded print statements.
If you use other data types of variables, you must use the CAST or CONVERT function. Local variables, global variables can be printed. Can use single quotes or double quotes to close the text.

Returned from the stored procedure

Microsoft SQL Server and Oracle have a RETURN statement. RETURN to your program or process from the query unconditional jump. RETURN is immediate,
Complete, and can be used from the process, batch or block any part of the jump. Statement later in the REUTRN will not be executed.

Oracle Microsoft SQL
RETURN expression: RETURN [integer_expression]

Trigger error (Raising program errors)

Transact-SQL for RAISERROR to return a user-defined error message, and sets a system flag to record an error occurred.
This function with the PL / SQL for raise_application_error exception handler function is similar.

RAISERROR statement allows customers to re-acquire an entry sysmessages table, or with user-specified severity and state information dynamically build a message.
Being defined, the message is sent back to the client as a system error message.

RAISERROR ((msg_id | msg_str), severity, state
[, Argument1 [, argument2>)

[WITH options]

The conversion of your PL / SQL procedure, perhaps no need to use the RAISERROR statement. In the following sample code.
PL / SQL program uses raise_application_error exception handler, but the Transact-SQL program is nothing more to say.
Including raise_application_error exception handler is to prevent the PL / SQL returns ambiguous unhandled exception error message.
Instead, when an unforeseen problem occurs when the exception handler always returns Oracle error message.

When a Transact-SQL fails, it always returns a detailed error message to the client. Therefore, unless the need for some specific error handling,
RAISERROR statement is generally not required.

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

Implementation cursor

Oracle SELECT statement using the cursor always required, regardless of how many rows from the database request. In Microsoft SQL Server,
SELECT statement does not return the customer to attach the cursor row result set as the default. This is an application to return data to the client's effective.

SQL Server provides two functions for the cursor interfaces. When a Transact-SQL batch or stored procedure when using the cursor, SQL statement used to declare
Open, and the extract from the cursor, update and delete the same as orientation. When you use from the DB-Library, ODBC, OLEDB, or when the program cursor, SQL Server
Explicit call to the built-in server functions to more effectively deal with the cursor.

When the input from an Oracle PL / SQL procedure, first determine whether the use of the Transact-SQL cursors to achieve the same functionality. If the cursor returns a row just to the client, on the use of non cursor SELECT statement to return a default result set. If the cursor is used to get from one line to the local process of a data variable,
You must use Transact-SQL cursor.

Grammar

The following table shows the syntax for using the cursor.

Operation Oracle Microsoft SQL Server
Declare a cursor 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 a cursor OPEN cursor_name [(cursor_parameter (s))];
----------------
OPEN cursor_name
Extracted from the cursor (Fetching) FETCH cursor_name INTO variable (s)
-------------------------------------------------- -----------------------------------------
FETCH FROM] cursor_name
[INTO @ variable (s)]
Updated extraction line UPDATE table_name
SET statement (s) ...
WHERE CURRENT OF cursor_name; UPDATE table_name
SET statement (s) ...
WHERE CURRENT OF cursor_name
Remove extraction line DELETE FROM table_name
WHERE CURRENT OF cursor_name; DELETE FROM table_name
WHERE CURRENT OF cursor_name
Close the cursor CLOSE cursor_name; CLOSE cursor_name
Remove the cursor data structure N / A DEALLOCATE cursor_name

Declare a cursor

Although the Transact-SQL DECLARE CURSOR statement does not support the use of cursor parameters, but it does support local variables. When the cursor is opened,
It will use these local variables. Microsoft SQL Server in the DECLARE CURSOR provides many additional features.

INSENSITIVE option is used to define a create temporary copies of data to be used for cursor cursor. All requests by the cursor temporary table to answer. Therefore, changes to the original table will not be reflected in those who returned from the fetch data for the cursor. This type of cursor to access the data can not be modified.

Application can request a cursor type and then perform a requested server is not supported cursor types of Transact-SQL statement. SQL Server returns an error,
That the cursor type is changed, or is given a set of parameters, implicit cursor conversion. For SQL Server 7.0 to get a trigger of the implicit cursor conversion from one type to another type of a complete list of parameters, see SQL Server Books Online.

SCROLL option allows extraction in addition to the outside before, backward, absolute and relative data extraction. A scroll cursor uses a set of cursor keys model, in this model,
Submitted by any user to delete and update the table will affect the subsequent data extraction. Not only with the INSENSITIVE option in the cursor declaration, the above characteristics only play a role.

If you select the READ ONLY option, the cursor in the line of the update have been banned. This option will override the default cursor options allow the update date.

UPDATE [OF column_list] statement is used to define an updatable cursor column. If the offer [OF column_list], then only those listed in the column can be modified.
If you do not specify any column. Then all the columns can all be updated, unless the cursor is defined as READ ONLY.

Importantly, note the name of a SQL Server cursor scope is the connection itself. This is the name of the scope of local variables is different.
Can not declare a connection with the same user has the cursor on the cursor the same name, unless the cursor is released first.

Open a cursor

Transact-SQL does not support an open cursor to pass parameters, this point, and PL / SQL is not the same. When a Transact-SQL cursor is opened after
Members of the result set and order on the fixed down. Other users on the original list submitted by the cursor updates and deletes will be reflected on the definition of all outstanding options plus INSENSITIVE cursor on the data collected. An INSENSITIVE cursor, will generate a temporary table.

Data extraction

Oracle cursor can only move forward not backward or to date the capacity of the rolling. SQL Server cursors can scroll forward or backward, how specific rolling
To be given by the following table of data extraction options to decide. Only in the cursor declaration is a prerequisite SCROLL option, these options can be used.

Scroll Option Description
NEXT If this is the first extraction of the cursor, then the result set returned the first row; Otherwise, move the cursor within the combined results to the next line.
NEXT to move in the result set the basic method. NEXT is the default cursor fetch (fetch).
PRIOR result set returned the first row.
FIRST the cursor to the first row of the result set, while the first row returned.
LAST move the cursor to the last line of the result set, while return to the last row.
ABSOLUTE n result set returned the first n rows. If n is negative, then return to the penultimate line n
RELATIVE n returns the current row after extracting the first n lines, if n is negative, then return to the relative position from the cursor n from the penultimate row.

Transact-SQL in the FETCH INTO clause does not require statement. If you do not specify return variables, rows automatically, as a single result set to return to the customer. However,
If the course you have to row to the client, a non-cursor SELECT statement more effective.

In the back of each FETCH, @ @ FETCH_STATUS function is updated. This is the PL / SQL using CURSOR_NAME% FOUND and CURSOR_NAME% NOTFOUND variables are similar. @ @ FETCH_STATUS function in each after a successful data extraction is set to 0. If the data collected more than trying to read a cursor at the end of the data, it returns a value to -1.
If the requested row after opening the cursor is deleted from the table, @ @ FETCH_STATUS function returns a value to -2. SCROLL option only cursor is the definition of the situation,
-2 Value will be returned. After the data collected in each of the variables must check to ensure the validity of data.

SQL Server does not support Oracle's cursor FOR loop syntax.

CURRENT OF clause

Update and delete the CURRENT OF clause syntax and function in PL / SQL and Transact-SQL is the same. In a given cursor, the current line on the implementation of the positioning of the UPDATE and DELETE.

Close a cursor

Transact-SQL for CLOSE CURSOR statement closes the cursor, but to retain data structures to prepare for reopening. PL / SQL in the CLOSE CURSOR statement close and release all of the data structure.

Transact-SQL need DEALLOCATE CURSOR statement to remove the cursor data structure. DEALLOCATE CURSOR statement with the CLOSE CURSOR is not the same,
The latter to retain data structures to prepare for reopening. DEALLOCATE CURSOR release all data structures associated with the cursor and clear the cursor definition.

Cursor Example

The following example shows the PL / SQL and Transact-SQL equivalent cursor statements.

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 @ VSSN, @ VFNAME, @ VLNAME
END
CLOSE CUR1
DEALLOCATE CUR1

相关文章
  • Daquan ORACLE internal functions as well as the difference between SQLSERVER 2010-07-30

    Daquan ORACLE internal functions as well as the difference between SQLSERVER Collection Character function Here are the characters Oracle support functions and their Microsoft SQL Server equivalent function. Functions Oracle Microsoft SQL Server The

  • Daquan ORACLE date and time functions 2010-05-12

    Daquan ORACLE date and time functions TO_DATE format (with time :2007-11-02 13:45:25 example) Year: yy two digits show the value of two years: 07 yyy three digits show the value of three years: 007 yyyy four digits show the value of four years: 2007

  • Collection of Oracle common functions 2010-08-11

    Oracle numeric functions common feature highlights: To obtain the client IP address trim (NVL (sys_context ('userenv', 'ip_address'), '255 .255.255.255')) abs (m) m the absolute value mod (m, n) m is the remainder after n addition to power (m, n) m o

  • Summary of Practical Oracle date functions 2010-10-15

    Daquan ORACLE date and time functions TO_DATE format (time :2007 -11-02 13:45:25 example) Year: yy two digits show the value of two years: 07 yyy three digits show the value of three years: 007 yyyy four digits show the value of four years: 2007 Mont

  • Oracle Analytic Functions RANK (), ROW_NUMBER (), LAG (), etc. Use (reproduced) 2010-11-15

    Oracle Analytic Functions RANK (), ROW_NUMBER (), LAG () to use such ROW_NUMBER () OVER (PARTITION BY COL1 ORDER BY COL2) Said the group based on COL1, COL2 sort according to the group and this internal value that each number within the ordered seque

  • ORACLE - Oracle analytic functions detailed in [1] 2010-06-07

    1. Analysis of the function 1 (OVER) Contents: =============================================== About 1.Oracle analysis functions 2. Oracle simple example of analysis functions 3. Of an analytic function OVER, Oracle analysis functions Description: In

  • oracle analytic functions over (transfer) 2010-08-13

    1, Oracle analysis functions Description: In the daily production environment, we have contacted more than yes OLTP Xitong (ie Online Transaction Process), the system is characterized by Jubei Shishiyaoqiu, Or at least for the length of time Xiangyin

  • Oracle Group Functions Summary 2010-09-19

    Oracle Group Functions Summary 1.OVER (PARTITION BY ..) Cases: select a, b, c, sum (c) OVER (PARTITION BY b) sum_c The value of the b column c the same value of the cumulative row. - Retrieve the type specified number of percentage Lingshou Hu select

  • PHP's internal functions related to 2010-10-15

    PHP internal functions commonly used in string 1.strlen strlen function to get the length of a string 2.trim trim function function is to remove the spaces on both sides of the string 3.ltrim ltrim string function, the function is to remove the space

  • Oracle Analytic Functions: RANK, DENSE_RANK, FIRST and LAST 2010-12-13

    Oracle / PLSQL: Rank Function: http://techonthenet.com/oracle/functions/rank.php Oracle Rank: http://psoug.org/reference/rank.html RANK, DENSE_RANK, FIRST and LAST Analytic Functions: http://www.oracle-base.com/articles/misc/RankDenseRankFirstLastAna

  • Oracle Analytic Functions: RANK, DENSE_RANK, FIRST and LAST; PARTITION BY 2010-12-13

    Oracle / PLSQL: Rank Function: http://techonthenet.com/oracle/functions/rank.php Oracle Rank: http://psoug.org/reference/rank.html RANK, DENSE_RANK, FIRST and LAST Analytic Functions: http://www.oracle-base.com/articles/misc/RankDenseRankFirstLastAna

  • Oracle internal database training materials 2010-12-26

    Oracle internal database training materials PPT

  • oracle analytic functions over and the window function 2011-01-11

    oracle analytic functions over and the window function A: Analysis of function over Oracle began offering analysis functions from 8.1.6 to analyze the function used to calculate the aggregate value based on some kind of group, and aggregate functions

  • Js Daquan string manipulation functions 2011-05-23

    Js Daquan string manipulation functions Js Daquan string manipulation functions Js Daquan string manipulation functions / * ****************************************** Expansion of string functions ****************************************** * / / * ==

  • Oracle WMSYS Functions 2011-07-13

    Oracle WMSYS Functions Version 11.2 GENERAL Data Types CREATE OR REPLACE TYPE wm_period AS OBJECT ( validfrom TIMESTAMP WITH TIME ZONE, validtill TIMESTAMP WITH TIME ZONE); Security conn / as sysdba ALTER USER wmsys ACCOUNT UNLOCK IDENTIFIED BY wmsys

  • Oracle SQL functions and mathematical functions mathematical difference 2011-02-21

    Oracle mathematical functions and mathematical functions in SQL Server are not the same. ① absolute S: select abs (-1) value O: select abs (-1) value from dual ② rounded (large) S: select ceiling (-001) value O: select ceil (-001) value from dual ③ r

  • oracle analytic functions dense_rank over partition 2011-03-14

    oracle analysis and statements: http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/analysis.htm # DWHSG0205 http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:122801500346829407 oracle aggregate functions: http://psoug.or

  • Oracle group functions and Case Analysis 2011-04-14

    Oracle's group function, respectively: MAX () MIN () AVG () COUNT () SUM () 1. COUNT (): That function as the look-up table in the data field is not empty the total number of articles; For example: SQL> select count (empno) from emp; COUNT (EMPNO) --

  • Oracle analytic functions. Model multidimensional functions and a brief description of the function, the main newspaper for BI 2011-06-10

    The following code is tested and can be directly run Oracle analytic functions, multi-dimensional functions, and a brief description of Model functions, mainly for BI reporting statistics, not very comprehensive, but BI has done a little description

  • Layman Oracle analytic functions 2011-07-04

    Oracle development topics of: analysis of function (OVER) 1 Oracle development topics of: analysis of the function 2 (Rank, Dense_rank, row_number) 6 Oracle development topics of: analysis of the function 3 (Top / Bottom N, First / Last, NTile) 10 Or