Character function
Below is the character functions supported by Oracle and their Microsoft SQL Server equivalents.
Function Oracle Microsoft SQL Server Convert Characters to ASCII ASCII ASCII Strings Connect Concat (Expression Expression) Converting ASCII to Character Chr Char Returns Start Character (Left Stand) Instr Charindex Transfer Characters to Small / Writing Lower Lower Converting the left LTRIM LTRIM Clear Started Blank LTRIM RTRIM String of the RTRIM RTRIM String, the Pattern INSTER RTRIM RTRIM String Multi-repeated String RPAD Replicate string Soundex SoundEx Repeat String RPAD Space Convert SPACE from Digital Data To Character Data To_CHAR STR Skew Substr Substring Replace Character Replace Stuff Translate Initcap N / A Translation String Translate N / A String of Strings Length Length DateLength Or LEN list The smallest string of LEAST N / A in the list of LENGTST N / A LEAST N / A is converted to the string NVL ISNULL
Date function
Below is the date function supported by Oracle and their Microsoft SQL Server equivalent functions.
Function Oracle Microsoft SQL Server Date Added (Date Column /- Value) ORADD_MONTHS DATEADD two Date COLUMN /- Value) Ormonths_between dateDiff Current date and time sysdate getdate () last day last day Last_day N / A The first Sunday N / A representative date after the time zone conversion new_time n / a Date String To_Char Datename Represents Date TO_NUMBER (TO_CHAR)) DatePart Date Round Convert Date Trunc Convert String Translation To Date TO_DATE Convert If null, conversion date NVL ISNULL
Conversion function
Below is the conversion function supported by Oracle and their Microsoft SQL Server equivalent functions.
Function Oracle Microsoft SQL Server Digital Convert to Character TO_CHAR Convert Characters Convert to Number TO_NUMBER CONVERT Date Convert to Character TO_CHAR Convert Characters Convert to Date TO_DATE CONVERT 16 Enter to 2 Enter Hex_TO_RAW Convert 2 Enter to 16 Enter Raw_TO_HEX Convert
Other line-level functions
Below is Oracle's other row level functions and their Microsoft SQL Server equivalent functions.
Function Oracle Microsoft SQL Server Returns the first non-empty expression decode coalesce Current sequence value CURRVAL N / A Next sequence value NextVal N / A If exp1 = exp2, return NULL DECODE NULLIF user login account ID Number UID SUSER_ID User login User SUSER_NAME User Database ID Digital UID User_ID User Database Name User_Name Current User Current_User Current_user User Environment (Audit Trail) Level N / A in the Connect By clause
Total function
Below are the total functions supported by Oracle and their Microsoft SQL Server equivalents. Function Oracle Microsoft SQL Server Average Avg Count Count Count Maximum Max Max MINMUM Min MIN Stdeard Deviation Sum Sum Variance Variance Var Or Varp
Condition test
The Case expression of Oracle's Decode statement and Microsoft SQL Server performs condition tests. When the value in Test_Value and any expression of the following match, the associated value is returned. If you do not find any matching values, return DEFAULT_VALUE. If DEFAULT_VALUE is not specified, Decode and Case return a NULL when there is no match. The table below shows the syntax of the statement, and an example of the conversion DECODE command is given.
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.gradegroup by ssncase infut_expressionwhen when_expression the result_expression
[ELSE else_result_expression] END CREATE VIEW STUDENT_ADMIN.STUDENT_GPA (SSN, GPA) AS SELECT SSN, ROUND (AVG (CASE gradeWHEN 'A' THEN 4WHEN 'A ' THEN 4.3WHEN 'A-' THEN 3.7WHEN 'B' THEN 3WHEN 'B 'TEN 3.3WHEN' B- 'THEN 2.7WHEN' C 'THEN' C- 'THEN' DHEN 'D' THEN 'DHEN' D 'TEN 1.3when' D- 'TEN 0.7ELSE 0nd, 2) from student_admin.gradegroup by SSN
The CASE expression can support Boolean tests with the SELECT statement, which is not allowed by the decode command. For more information on CASE expressions, see the SQL Server online manual.
Convert value into different data types
The CONVERT and CAST functions for Microsoft SQL Server are multi-objective conversion functions. They provide similar functions that convert an expression of a data type to another data type, and support a variety of special data formats.
Cast (expression as data_type) Convert (Data Type ", Expression [, style]) CAST is a function of SQL-92 standard. These functions perform the same features as Oracle's To_CHAR, TO_NUMBER, TO_DATE, HEXTORAW, and RAWTTEXT functions. The data type referred to herein is a system data type that will be converted to be converted. You cannot use user-defined data types. The length parameters are optional, which is used for char, varchar, binary, and varbinary data types. The maximum allowed length is 8000.
Convert Oracle Microsoft SQL Server Character to Digital To_Number (ཆ ') Convert (Numeric, ཆ') Number to 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 ()) Convert CHAR, GETDATE (), 106) Convert (char, getdate (), 101) 16 Enter 2-Binary Hextoraw (ƈF ') Convert (binary, ƈF') 2 Enter to 16 Enter RawtoHEX (Binary_Column) Convert (Binary_Column) Convert Char, binary_column)
Please 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 a suitable date format model. The CONVERT function automatically converts the standard date format and does not require any format model.
When the date is converted to the string, the default output of the Convert function is "DD MON YYYY HH: MM: SS: MMM (24H)". Format the output with a digital style code so that it can output a date format model for other types. For more information on the CONVERT function, see the SQL Server online manual.
The following table shows the default output of the Microsoft SQL Server date.
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 YYYYY 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 definition function
The Oracle PL / SQL function can be used in the Oracle SQL statement. The same functions can be achieved in Microsoft SQL Server. Instruments you can use in the SQL Server instead.
Oracle Microsoft SQL Server SELECT SSN, FNAME, LNAME,) TUITION_PAID, TUITION_PAID / GET_SUM_ MAJOR (MAJOR) AS PERCENT_MAJORFROM STUDENT_ADMIN.STUDENT SELECT SSN, FNAME, LNAME, TUITION_PAID, TUITION_PAID / SUM_MAJOR AS PERCENT_MAJORFROM STUDENT_ADMIN.STUDENT, (SELECT MAJOR, SUM ( TUITION_PAID) SUM_MAJOR FROM STUDENT_ADMIN.STUDENT GROUP BY MAJOR) SUM_STUDENTWHERE STUDENT.MAJOR = SUM_STUDENT.MAJOR CREATE OR REPLACE FUNCTION GET_SUM_MAJOR (INMAJOR VARCHAR2) RETURN NUMBERAS SUM_PAID NUMBER; BEGINSELECT SUM (TUITION_PAID) INTO SUM_PAIDFROM STUDENT_ADMIN.STUDENTWHERE MAJOR = INMAJOR; RETURN (SUM_PAID ); End get_sum_major; no create function syntax is required; use create procedure Syntax.
Compare operator
The comparison operator of Oracle and Microsoft SQL Server is almost the same.
Octa Microsoft SQL Server is equal to (=) (=) greater than (>) (<) (<) (<) is greater than or equal to (> =) (> =) less than or equal to (<=) (<=) Is equal to (! =, <>, ^ =) (! =, <>, ^ =) Is not greater than, not less than n / a!>,!
SQL Server's LIKE keyword provides a useful wildcard search feature, which is not supported in Oracle. SQL Server also supports ([]) and ([^]) wildcards except for all RDBMS supported.
([]) Characters are used to query all individual characters in a range. For example, if you need to query the data containing a character from A to F, you can write: "Like '[ABCDEF]'". The validity of these additional wildcards is given in the table below.
Oracle Microsoft SQL Select * from student_admin.studentwhere Lname Like 'a%' or lname like 'b%' or lname like 'c%' select * from student_admin.studentwhere Lname Like '[ABC]%'
[^] Wildcard is used to mark those characters that are not within a specific range. For example, if all characters other than A to F are acceptable, you can write: Like '[^ a - f]' or Like '[^ Abcdef]'.
For more information about the LIKE keyword, see the SQL Server online manual.
Use null in comparison
Although Microsoft SQL Server traditionally supports SQL-92 standards and some non-standard NULL behaviors, it also supports NULL in Oracle. To support distributed queries, SET ANSI_NULLS must be set to ON.
When connecting, SQL Server's SQL Server ODBC drivers and OLE DB providers are automatically set to ON. This setting can be configured in an ODBC data source, an ODBC connection property, or an OLE DB connection attribute set in the application before connecting to SQL Server. When connected to the DB-Library application, set ANSI_NULLS is default to OFF.
When SET ANSI_DEFAULTS is ON, SET ANSI_NULLS is allowed.
For more information about NULL usage, see the SQL Server online manual.
String connection
Oracle uses two pipe symbols (||) as a string connection operator, SQL Server uses a plus sign ( ). This difference requires you to make small modifications in your app.
Oracle Microsoft SQL SELECT FNAME || '' || Lname As Namefrom Student_admin.student SELECT FNAME ' LNAME As NameFrom Student_admin.student
CONTOL-OF-FLOW
Stream control language controls the SQL statement to execute flow, statement blocks, and stored procedures. PL / SQL and Transact-SQL provide most identical structures, but there are still some syntax differences.
Keyword
This is a keyword supported by two RDBMS.
Statement Oracle PL / SQL Microsoft SQL ServerTransAct-SQL Declared Variable Declare Declare Statement Block Services ... End; Begin ... END Condition Processing IF ... Then, Elsif ... Then, ElseEndif; if ... [begin ... END] Else
Label ... goto label; for ... end loop; loop ... end loop; while
Program Note / * ... * /, - / * ... * /, - Print output RDBMS_OUTPUT.PUT_LINE PROGRAROR ERROR RAISE_APPLICATION_ERROR RAISERROR execute program EXECUTE EXECUTE statement terminator SEMICOLON (;) N / A
Declaration variable
Transact-SQL and PL / SQL variables are created with Declare keywords. TRANSACT-SQL variables are used in @ tags, just like PL / SQL, initialization with null value when creating.
Oracle Microsoft SQL Declarevssn Char (9); VfName Varchar2 (12); Vlname Varchar2 (20); Vbpter_Date Date; Vloan_AMount Number (12, 2); Declare @ vssn char (9), @ vfname varcha2 (12), @ VLNAME VARCHAR2 (20), @ vbirth_date datetime, @ VLOAN_AMOUNT NUMERIC (12, 2) Transact-SQL does not support% type and% rowtype variable data type definition. A Transact-SQL variable cannot be initialized in the declare command. Oracle's NOT NULL and CONSTANT keywords cannot be used in the Microsoft SQL Server data type definition.
Like Oracle's long and long RAW data types. Text and graphical data types cannot be used to define variables. In addition, Transact-SQL does not support the definition of records and tables for PL / SQL style.
Assign value for variables
Oracle and Microsoft SQL Server provide the following methods to assign a 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
There are some syntax examples here.
Oracle Microsoft SQL DECLARE VSSN CHAR (9); VFNAME VARCHAR2 (12); VLNAME VARCHAR2 (20); BEGINVSSN:? = '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
Statement block
Both Oracle PL / SQL and Microsoft SQL Server Transact-SQL support to mark the phrase block with begin ... END terms. Transact-SQL does not need to use a statement after the DECLARE statement. If there is more statements in the IF statement and the While loop in Microsoft SQL Server, you need to use the Begin ... END statement block.
Oracle Microsoft SQL DECLAREDECLARE VARIABLES ... BEGIN - THIS IS REQUIRED SYNTAXPROGRAM_STATEMENTS ... IF ... THENSTATEMENT1; STATEMENT2; STATEMENTN; END IF; WHILE ... LOOPSTATEMENT1; STATEMENT2; STATEMENTN; END LOOP; END; - THIS IS REQUIRED SYNTAX DECLAREDECLARE VARIABLES ... BEGIN - THIS IS OPTIONAL SYNTAXPROGRAM_STATEMENTS ... IF ... BEGIN STATEMENT1STATEMENT2STATEMENTNEND WHILE ... BEGINSTATEMENT1STATEMENT2STATEMENTNEND END - THIS IS REQUIRED SYNTAX processing conditions
The conditional statements of the Microsoft SQL Server Transact-SQL include IF and ELSE, but do not include the elseiF statement in Oracle PL / SQL. You can use a nest multi-IF statement to reach the same effect. For a wide range of conditions, it is easier and readable with a Case expression.
Oracle Microsoft SQL DECLAREVDEGREE_PROGRAM CHAR (1); VDEGREE_PROGRAM_NAME VARCHAR2 (20); BEGINVDEGREE_PROGRAM: = 'U'IF VDEGREE_PROGRAM =' U 'THENVDEGREE_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' TEN 'Undergraduate' When 'm' Ten 'Masters' When' p 'Ten' PHD '. Else' Unknown'END
Repeat the statement (loop)
Oracle PL / SQL provides unconditional loop and for loop. Transact-SQL provides a While loop and goto statement.
While Boolean_Expression {SQL_Statement | Statement_Block}
[BREAK] [Continue]
The While loop needs to test a Boolean expression to determine the repeated execution of one or more statements. As long as the given expression results are true, this (some) statements have been repeated. If there are multiple statements that need to be executed, these statements must be placed in a BEGIN ... END block. Oracle Microsoft SQL DECLARECOUNTER NUMBER; BEGINCOUNTER: = 0WHILE (COUNTER <5) LOOPCOUNTER: = COUNTER 1; END LOOP; END; DECLARE @ COUNTER NUMERICSELECT @ COUNTER = 1WHILE (@COUNTER <5) BEGIN SELECT @COUNTER = @COUNTER 1END
The execution of the statement can be controlled by Break and Continue keywords within the cycles. The Break keyword makes the WHILE cycle unconditionally, and the Continue keyword restarts the statement after the WHILE runs. The Break keyword is equivalent to the EXIT key in Oracle PL / SQL. And keywords in Oracle and Continue equivalents
GOTO statement
Oracle and Microsoft SQL Server have a goto statement, but the syntax is different. The goto statement runs Transact-SQL to run at the specified label, and any statement between the specified label after the goto statement will not be executed.
Oracle Microsoft SQL Goto Label; << Label Name Here >> Goto Label
PRINT statement
The Print statement of Transact-SQL performs the same operation as the RDBMS_Output.put_line process with the PL / SQL. This statement is used to print the information given by the user.
The message cap of printing with a Print statement is 8,000 characters. Variables defined as char or varchar data types can be embedded in the print statement. If the variables of other data types are used, the CONVERT or CAST function must be used. Local variables, global variables can be printed. You can close the text with single quotes or dual quotes.
Return from the stored procedure
All Microsoft SQL Server and Oracle have Return statements. Return makes your program from the query or unconditional jump out of the process. Return is immediate, complete, and can be used to jump out of any part of the process, batch or speech block. The statement behind Reutr will not be executed.
Oracle Microsoft SQL RETURN ExpRerge: Return [Integer_EXPISSION]
Raising Program Errors
Transact-SQL's raiserror returns a user-defined error message and sets a system flag to record an error. This feature is similar to the functionality of the PL / SQL Raise_Application_ERROR exception processor.
The RAISError statement allows customers to re-acquire an entry of the SysMessages table, or create a message with the severity and status information specified by the user. After 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]
When converting your PL / SQL program, you may not use the RaisError statement. In the sample code below. The PL / SQL program uses the Raise_Application_ERROR exception processor, but the Transact-SQL program is useless. Include the RAISE_APPLICATION_ERROR exception processor is to prevent PL / SQL from returning an unclear unprocessed exception error message. Instead, when an unforeseen problem occurs, the abnormal processor always returns Oracle error message. When a TRANSACT-SQL fails, it always returns a detailed error message to the client. Therefore, unless certain specific error processing is required, it is generally not required to be the raiserror statement.
Oracle Microsoft SQL CREATE OR REPLACE FUNCTIONDEPT_ADMIN.DELETE_DEPT (VDEPT IN VARCHAR2) RETURN NUMBER ASBEGINDELETE FROM DEPT_ADMIN.DEPTWHERE DEPT = VDEPT; RETURN (SQL% ROWCOUNT); EXCEPTIONWHEN OTHER THENRAISE_APPLICATION_ERROR (-20001, SQLERRM); END DELETE_DEPT; / CREATE PROCEDUREDEPT_ADMIN.DELETE_DEPT @Vdept varchar (4) asdelete from dept_db.dbo.deptwhere dept = @vdeptreturn @@ rowcountgo
Realize a cursor
Oracle always requires a cursor when using the SELECT statement, regardless of how much rows are requested from the database. In Microsoft SQL Server, the SELECT statement does not attach the cursor on the row of customers as the default result collection. This is an effective way to return data to the client application.
SQL Server provides two interfaces for the cursor function. When using a cursor during the Transact-SQL batch or stored procedure, the SQL statement can be used to declare, open, and extract from the cursor, just like positioning updates and deletion. When using cursors from DB-Library, ODBC, or OLEDB programs, SQL Server explicitly calls built-in server functions to process cursors more efficient.
When entering a PL / SQL process from Oracle, first determine if a cursor is needed in Transact-SQL to implement the same function. If the cursor only returns a row to the client, use the non-target SELECT statement to return to the default result collection. If the cursor is used to get a data from the row to the local process variable, you must use a cursor in Transact-SQL.
grammar
The following table shows the syntax 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)] update fetched row uPDATE table_nameSET statement (s) ... WHERE CURRENT OF cursor_name; uPDATE table_nameSET statement (s) ... WHERE CURRENT OF cursor_name delete rows fetched dELETE FROM table_name WHERE CURRENT OF cursor_name; dELETE FROM table_name WHERE CURRENT OF CURSOR_NAME Close Cursor Close Cursor_name; Close Cursor_name Clear Cursor Data Structure N / A Deallocate Cursor_name declares a cursor
Although the Transact-SQL Declare Cursor statement does not support the use of cursor parameters, it does support local variables. When the cursor is opened, it uses the value of these local variables. Microsoft SQL Server provides a number of additional features in its Declare Cursor.
The INSENSITIVE option is used to define a temporary copy to create data to be used by the cursor. All requests for the cursor are replied by this temporary table. Therefore, modifications to the original table do not reflect those data returned by FETCH to the cursor. This type of cursor access is not modified.
The application can request a cursor type and perform a Transact-SQL statement that is not supported by the requested server cursor type. SQL Server returns an error, indicating that the cursor type is changed, or a set of parameters, implicit conversion cursors. To get a complete list of the Trigger SQL Server 7.0 implicitly implicit cursors from one type to another type of parameters, see the SQL Server online manual.
The Scroll option allows for later, backward, absolute, and relative data extraction in addition to the forward extraction. A scrolling cursor uses a cursor model of a key collection, in which the deletion and update of any user submitted to the table will affect the subsequent data. The above features only work only when the cursor is declared with the InSensitive option declaration.
If the read only option is selected, the update of the rows in the cursor is disabled. This option allows you to update your cursor.
Update [of column_list] The statement is used to define an updated column in the cursor. If [of column_list] is provided, it is just those columns that are listed can be modified. If no column is specified. All columns can be updated unless the cursor is defined as read only.
It is important to note that the name of the SQL Server cursor is to connect to themselves. This is different from the name range of local variables. You cannot declare a cursor of the same name as the existing cursor on the same user, unless the first cursor is released.
Open a cursor
Transact-SQL does not support passing parameters to an open cursor, which is different from PL / SQL. When a TRANSACT-SQL cursor is opened, the members and the order of the result set will be fixed. Updates and deletes of other users' cursors of the original table will be reflected on the data extraction of all cursors defined by the INSENSITIVE option. For an Insensitive cursor, a temporary table will be generated. Extract data
The Oracle cursor can only move the dates backward or relatively rolling capabilities. The SQL Server cursor can be scrolled forward or backwards, and how to scroll, it is determined by the data extraction options given by the following table. These options can only be used only if the cursor is declared with the Scroll option.
Rolling Option Description Next If this is the first time for the cursor, the first line of the result set is returned; otherwise, the cursor is moving the cursor to the next line in the result. Next is the basic method of moving in the result collection. Next is the default cursor extraction (FETCH). PRIOR returns the front line of the result collection. First move the cursor to the first line of the result collection, and return the first line. Last moves the cursor to the last line of the result collection, and return to the last line. Absolute n Returns the Nth line of the result collection. If n is a negative number, then the row, RELATIVE N, returns the Nth line of the currently mentioned row, if n is a negative number, returns a countdown nth line from the relative position of the cursor.
Transact-SQL's FETCH statement does not require an INTO clause. If you do not specify a returned variable, the line automatically returns to the customer as a single line result collection. However, if your process must give the customer, a SELECT statement that does not have a cursor is more effective.
Before each fetch, the @@ fetch_status function is updated. This is similar to using Cursor_name% Found and Cursor_name% notfound variables in PL / SQL. The @@ fetch_status function is set to 0 after each successful data extraction. If the data extraction attempts to read a data at the end of the cursor, the value of -1 is returned. If the requested row is deleted from the table after the cursor is open, the @@ fetch_status function returns a value of -2. Only the cursor is defined with the scroll option, the -2 value will be returned. This variable must be checked after each data extraction to ensure data validity.
SQL Server does not support Oracle's cursor for loop syntax.
Current of clause
The update and deletion of Current OF classes syntax and functions are the same in PL / SQL and Transact-SQL. In a given cursor, the positioned Update and Delete are performed on the current line.
Close a cursor
TRANSACT-SQL's Close Cursor statement off the cursor, but reserves the data structure for reopen. PL / SQL's Close Cursor statement is turned off and all data structures are released.
Transact-SQL needs to use a DEALLOCATE CURSOR statement to clear the cursor data structure. The DEAALLOCATE CURSOR statement is different from the Close Cursor, the latter reserves the data structure for reopen. DEAALLOCATE CURSOR releases all the data structures related to the cursor and clear the definition of the cursor.
Cursor example
The following example shows a cursor statement in PL / SQL and Transact-SQL equivalents.
