Character functions
Below are the character functions supported by Oracle and their Microsoft SQL Server equivalent functions.
Function Oracle Microsoft SQL Server
Convert characters to ASCII:ASCII ASCII
String connection: CONCAT -------------(expression + expression)
Convert ASCII to characters CHR, CHAR
Return the start character in the string (from left) INSTR, -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Convert characters to lowercase LOWER ----------------------------------------------------------------------------------------------------------------------
Convert characters to uppercase UPPER----------------------------------------------------------------------------------------------------------------------
Fill the left side of the string LPAD -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Clear the beginning blank LTRIM--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Clear the blanks at the tail RTRIM ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Start pattern in string INSTR ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Repeat string multiple times RPAD -----------------------------------------------------------------------------------------------------------------------
Voice representation of string SOUNDEX ---------------------------------------------------------------------------------------------------------------------
String of repeated spaces RPAD -----------------------------------------------------------------------------------------------------------------------
Convert from digital data to character data TO_CHAR ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Substring SUBSTR --------------------SUBSTRING
Replace characters REPLACE -------------------------------------------------------------------------------------------------------------------------
Capitalize the initial letter of each word in the string INITCAP -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Translate string TRANSLATE ---------------------------------------------N/A
String length LENGTH------------------------------------------------------------------------------------------------------------------------
The largest string in the list GREATEST-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
The smallest string in the list LEAST ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
If NULL, convert the string NVL----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Date function
Below are Oracle supported date functions and their Microsoft SQL Server equivalent functions.
Function Oracle ------------------------------------------------------------------------------------------------------------------------------
Date addition (date column +/- value) or ADD_MONTHS --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
The difference between two dates (date column +/- value) or MONTHS_BETWEEN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Current date and time SYSDATE --------------------------------------GETDATE()
The last day of a month LAST_DAY ----------------------------------------N/A
Time zone conversion NEW_TIME --------------------------------------N/A
The first Sunday after the date NEXT_DAY ------------------------------------------------------N/A
String representing date TO_CHAR ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Integer representing date TO_NUMBER (TO_CHAR)) ------------------------------------------------------------------------------------------------------------
Date rounding ROUND ------------------------------------------------------------------------------------------------------------------------
Date truncation TRUNC --------------------------------------------------------------------------------------------------------------------
Convert string to date TO_DATE --------------------------------------------------------------------------------------------------------------------
If NULL, convert the date NVL ---------------------------------------------------------------------------------------------------------------------
Conversion function
Below are Oracle-supported conversion functions and their Microsoft SQL Server equivalent functions.
Function Oracle --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Convert numbers to characters TO_CHAR ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Convert characters to numbers TO_NUMBER ------------------------------------------------------------------------------------------------------------------
Convert date to character TO_CHAR ----------------------------------------------------------------------------------------------------------------------
Characters to date TO_DATE CONVERT
Convert hexadecimal to bin HEX_TO_RAW -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Convert binary to hexadecimal RAW_TO_HEX -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Other row-level functions
Below are other row-level functions supported by Oracle and their Microsoft SQL Server equivalent functions.
Function Oracle --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Return the first non-null expression DECODE ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Current sequence value CURRVAL ---------------------------------N/A
Next sequence value NEXTVAL --------------------------------------N/A
User login account ID number UID ---------------------------------------------SUSER_ID
User login name USER -----------------------------------SUSER_NAME
User database ID number UID -----------------------------------------------USER_ID
User database name USER -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Current user CURRENT_USER -----------------------------------------------------------------------------------------------------------------------
User environment (audit trail) USERENV -------------------------------------N/A
Level LEVEL -------------------------------------------------------------------------------------------------------------------------
Total function
Below are the total functions supported by Oracle and their Microsoft SQL Server equivalent functions.
Function Oracle --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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
Conditional testing
Both Oracle's DECODE statements and Microsoft SQL Server's CASE expressions perform conditional tests.
When the value in test_value matches any subsequent expression, the relevant value is returned. If no matching value is found, default_value is returned.
If default_value is not specified, both DECODE and CASE return a NULL when there is no match. The following table shows the syntax of this statement.
Also, an example of converting DECODE commands 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.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 expressions can support the execution of Boolean tests with SELECT statements, which is not allowed by DECODE commands. For more information about CASE expressions,
Please refer to the SQL Server online manual.
Convert values to different data types
The CONVERT and CAST functions of Microsoft SQL Server are both multi-objective conversion functions. They provide similar functionality,
Convert expressions of one data type to expressions of another data type and support multiple specialized data formats.
CAST(expression AS data_type)
CONVERT (data type[(length)], expression [, style])
CAST is a SQL-92 standard function. These functions perform the same functions as Oracle's TO_CHAR, TO_NUMBER, TO_DATE, HEXTORAW and RAWTOTEXT functions.
The data type referred to here is the system data type that any expression will be converted to. User-defined data types cannot be used. The length parameter is optional.
This parameter is used for char, varchar, binary and varbinary data types. The maximum allowable length is 8000.
Convert Oracle Microsoft SQL Server
Character to number TO_NUMBER(ཆ') ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Number to character TO_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)
Hexadecimal to binary HEXTORAW(ƇF')---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
binary to hexadecimal RAWTOHEX(binary_column) ---------------------------------------------------------------------------------------------------------
Note how strings are converted to dates. 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 converting from date to 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 be output as other types of date format models. For more information about the CONVERT function, refer to the SQL Server Online Manual.
The following table shows the default output for Microsoft SQL Server dates.
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
3 103 British/French dd/mm/yy
4 104 German
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 functions
Oracle PL/SQL functions can be used in Oracle SQL statements. In Microsoft SQL Server, the same function can generally be achieved through other methods.
In SQL Server, the query given in the table can be replaced.
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 = 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 operator
The comparison operators of Oracle and Microsoft SQL Server are almost the same.
Operator Oracle Microsoft SQL Server
Equal to (=) (=)
Greater than (>) (>)
Less than (<) (<)
greater than or equal to (>=) (>=)
Less than or equal to (<=) (<=)
Not equal to (!=, <>, ^=) (!=, <>, ^=)
Not greater than, not less than N/A !> , !<
IN IN in any member of the collection
NOT IN NOT IN NOT IN
Any value in the set ANY, SOME ANY, SOME
Submit all values in the set != 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
Value between X and y BETWEEN x AND y BETWEEN x AND y
Value not between x and y NOT BETWEEN NOT BETWEEN
Value exists EXISTS EXISTS
Value does not exist NOT EXISTS NOT EXISTS
Value {|not}NULL, IS NOT NULL Same. Also = NULL,
!= NULL for backward compatibility (not recommended).
Pattern matching
SQL Server's LIKE keyword provides useful wildcard search function, which is not supported in Oracle.
In addition to the (%) and (_) wildcards supported by all RDBMSs, SQL Server also supports ([ ]) and ([^]) wildcards.
The ([ ]) character is used to query all single characters in a range. For example, if you need to query data containing a character from a to f,
You can write this: "LIKE '[a-f]'" or "LIKE '[abcdef]'". The validity of these additional wildcards is given in the following table.
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]%';
[^] Wildcards are used to mark characters that are not within a specific range. For example, if all characters except a to f are acceptable, you can write this way:
LIKE '[^a - f]' or LIKE '[^abcdef]'.
For more information about LIKE keywords, see the SQL Server Online Manual.
Use NULL in comparison
Although Microsoft SQL Server traditionally supports SQL-92 standard and some non-standard NULL behavior, it supports the usage of NULL in Oracle.
To support distributed queries, SET ANSI_NULLS must be set to ON.
When connecting, the SQL Server ODBC driver and OLE DB provider of SQL Server automatically set SET ANSI_NULLS to ON.
This setting can be configured in the ODBC data source, the ODBC connection attribute, or the OLE DB connection attribute set in the application before connecting to SQL Server.
SET ANSI_NULLS defaults to OFF when connecting from a DB-Library application.
SET ANSI_DEFAULTS is allowed when SET ANSI_DEFAULTS is ON.
For more information on NULL usage, see the SQL Server Online Manual.
String connection
Oracle uses two pipeline symbols (||) as string concatenation operators, while SQL Server uses plus sign (+). This difference requires you to make small changes in the application.
Oracle:
SELECT FNAME||' '||LNAME AS NAME
FROM STUDENT_ADMIN.STUDENT;
/
-----------------------------------------------
Microsoft SQL:
SELECT FNAME +' '+ LNAME AS NAME
FROM STUDENT_ADMIN.STUDENT
Flow control (Control-of-Flow) language
The flow control language controls SQL statements to execute streams, statement blocks, and stored procedures. PL/SQL and Transact-SQL provide most of the same structures, but there are still some syntax differences.
Keywords
These are two RDBMS-supported keywords.
Statement Oracle PL/SQL:
Declare variable DECLARE DECLARE
Statement block BEGIN...END; BEGIN...END
Conditional processing IF…THEN,
ELSIF…THEN,
ELSE
ENDIF;
--------------------------------------------------------
Microsoft SQL Server Transact-SQL:
IF…[BEGIN…END]
ELSE <condition>
[BEGIN…END]
ELSE IF <condition>
CASE expression
Unconditional end RETURN-----------------------------------------------------------------------------------------------------------------------
Unconditionally end the statement after the current program block EXIT BREAK
Restart a WHILE loop N/A CONTINUE
Wait for the 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
Raise program error RAISE_APPLICATION_ERROR -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ExecUTE-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement terminator Semicolon (;) --------------------------N/A
Declare variables
Transact-SQL and PL/SQL variables are created with the DECLARE keyword. Transact-SQL variables are marked with @.
And just like PL/SQL, when created for the first time, initialize with a null value.
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 does not support %TYPE and %ROWTYPE variable data type definitions. A Transact-SQL variable cannot be initialized in the DECLARE command.
Oracle's NOT NULL and CONSTANT keywords cannot be used in Microsoft SQL Server data type definitions.
Like Oracle's LONG and LONG RAW data types. Text and graph data types cannot be used as variable definitions.
In addition, Transact-SQL does not support PL/SQL-style record and table definitions.
Assign values to variables
Oracle and Microsoft SQL Server provide the following methods to assign values to local variables.
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:
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
Statement block
Both Oracle PL/SQL and Microsoft SQL Server Transact-SQL support tagging statement blocks with BEGIN…END terms.
Transact-SQL does not need to use a statement block after the DECLARE statement.
---------------------------------------------------------------------------------
-If in Microsoft SQL Server
If more than one statement in the IF statement and WHILE loop are executed, you need to use the BEGIN…END statement 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 conditional statements include IF and ELSE, but do not include ELSEIF statements in Oracle PL/SQL.
You can use nested multiple IF statements to achieve the same effect. For extensive conditional testing, it may be easier and easier to read with CASE expressions.
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
Repeat execution statement (loop)
Oracle PL/SQL provides unconditional LOOP and FOR LOOP. Transact-SQL provides WHILE loops and GOTO statements.
WHILE Boolean_expression
{sql_statement | statement_block}
[BREAK] [CONTINUE]
A WHILE loop requires testing a Boolean expression to determine the repeated execution of one or more statements.
As long as the given expression is true, these statements are executed repeatedly. If there are multiple statements to be executed, they must be placed in a BEGIN…END block.
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 =
@COUNTER +1
END
The execution of statements can be controlled internally with the BREAK and CONTINUE keywords. The BREAK keyword causes the WHILE loop to end unconditionally.
The CONTINUE keyword causes the WHILE loop to skip the subsequent statement and start again. The BREAK keyword is equivalent to the EXIT keyword in Oracle PL/SQL.
There are no keywords equivalent to CONTINUE in Oracle
GOTO statement
Oracle and Microsoft SQL Server have GOTO statements, but the syntax is different. The GOTO statement causes Transact-SQL to jump to the specified label to run.
No statements that specify labels after GOTO statements will 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 procedure of PL/SQL. This statement is used to print the message given by the user.
The maximum number of messages printed with the PRINT statement is 8,000 characters. Variables defined as char or varchar data type can be embedded in print statements.
If you use variables of other data types, you must use the CONVERT or CAST function. Local variables and global variables can be printed. You can use single or double quotes to enclose text.
Return from stored procedure
Both Microsoft SQL Server and Oracle have RETURN statements. RETURN enables your program to jump out of the query or process unconditionally. RETURN is immediate,
Completely and can be used to jump out of any part of a process, batch, or statement block. Statements after REUTRN will not be executed.
Oracle Microsoft SQL
RETURN expression: RETURN [integer_expression]
Raising program errors
Transact-SQL's RAISERROR returns a user-defined error message and sets a system flag to record an error occurred.
This function is similar to the PL/SQL raise_application_error exception handler.
The RAISERROR statement allows the client to regain an entry to the sysmessages table, or to dynamically create a message using 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 need to use the RAISERROR statement. In the following example code.
PL/SQL programs use raise_application_error exception handler, but Transact-SQL programs are useless.
Including raise_application_error exception handlers is to prevent PL/SQL from returning unclear unprocessed exception error messages.
Instead, when an unforeseen problem occurs, the exception handler always returns an Oracle error message.
When a Transact-SQL fails, it always returns a detailed error message to the client. Therefore, unless some specific error handling is required,
Generally, RAISERROR statements are 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.
WHERE DEPT = @VDEPT
RETURN @@ROWCOUNT
GO
Implement cursor
Oracle always needs a cursor when using SELECT statements, regardless of how many rows are requested from the database. On Microsoft SQL Server,
The SELECT statement does not attach a cursor to the row that returns the client as the default result set. This is an effective way to return data to a customer application.
SQL Server provides two interfaces for cursor functions. When using cursors in Transact-SQL batch processing or stored procedures, SQL statements can be used to declare,
Open, and extract from cursors, just like positioning updates and deleting. SQL Server is used when using cursors from DB-Library, ODBC, or OLEDB programs
Explicitly call built-in server functions to handle cursors more efficiently.
When entering a PL/SQL procedure from Oracle, first determine whether cursors are needed in Transact-SQL to achieve the same function. If the cursor only returns one
When the row is grouped to the client program, it uses a non-cursor SELECT statement to return the default result set. If the cursor is used to obtain data from the row one at a time to the local process variable,
You have to use cursors in Transact-SQL.
grammar
The following table shows the syntax using cursors.
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
Fetching from cursor (Fetching) FETCH cursor_name INTO variable(s)
-------------------------------------------------------------------------------------------
FETCH FROM] cursor_name
[INTO @variable(s)]
Update the extract row UPDATE table_name
SET statement(s)…
WHERE CURRENT OF cursor_name; UPDATE table_name
SET statement(s)…
WHERE CURRENT OF cursor_name
Delete extract row 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
Declare 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 values of these local variables. Microsoft SQL Server provides many additional features in its DECLARE CURSOR.
The INSENSITIVE option is used to define a cursor that creates a temporary copy of the data to be used by the cursor. All requests for cursors are answered by this temporary table. therefore
Modifications to the original table will not be reflected on the data returned by fetch for the cursor. Data accessed by this type of cursor cannot be modified.
The application can request a cursor type and then execute a Transact-SQL statement that is not supported by the requested server cursor type. SQL Server returns an error.
Indicates that the cursor type has been changed, or a set of parameters is given, implicitly converting the cursor. To get a trigger SQL Server 7.0 to implicitly convert cursor from one type to
For a complete list of another type of parameters, see the SQL Server Online Manual.
The SCROLL option allows backward, absolute and relative data extraction in addition to forward extraction. A scroll cursor model using a set of keys, in which
Any user-submitted deletion and update of the table will affect subsequent data extraction. The above feature works only if the cursor is not declared with the INSENSITIVE option.
If the READ ONLY option is selected, updates to rows in the cursor are prohibited. This option overrides the default option of the cursor to allow updates.
The UPDATE [OF column_list] statement is used to define an updating column in the cursor. If [OF column_list] is provided, then only those listed columns can be modified.
If no column is specified. Then all columns can be updated unless the cursor is defined as READ ONLY.
Importantly, notice that the name range of a SQL Server cursor is to connect to itself. This is different from the name range of local variables.
A cursor with the same name as an existing cursor on the same user connection cannot be declared 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 order of the result set are fixed. The updates and deletion of cursors submitted by other users to the original table will be reflected in the definition of all unadded INSENSITIVE option.
data extraction of cursor. For an INSENSITIVE cursor, a temporary table will be generated.
Extract data
Oracle cursors can only move forward without the ability to scroll backward or relative. SQL Server cursors can be scrolled forward or backward. How to scroll specifically?
It is determined by the data extraction options given in the following table. These options can only be used if the cursor is declared with the SCROLL option.
Scroll Option Description
NEXT If this is the first extraction of the cursor, the first row of the result set is returned; otherwise, the cursor is moved to the next row within the result combination.
NEXT is the basic method of moving in a result set. NEXT is the default cursor fetch.
PRIOR Returns the previous row of the result set.
FIRST moves the cursor to the first row of the result set and returns to the first row.
LAST moves the cursor to the last row of the result set and returns the last row at the same time.
ABSOLUTE n Returns the nth row of the result set. If n is a negative number, return the n-th row to last
RELATIVE n Returns the nth row after the current extracted row. If n is a negative number, it returns the nth row to the penultimate row from the relative position of the cursor.
Transact-SQL's FETCH statement does not require an INTO clause. If no return variable is specified, the row is automatically returned to the client as a single row result set. but,
If your process has to give the line to the client, a SELECT statement without a cursor is more effective.
After each FETCH, the @@FETCH_STATUS function is updated. This is similar to using the 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 that exceeds the end of the cursor, a value of -1 is returned.
If the requested row is deleted from the table after the cursor is opened, the @@FETCH_STATUS function returns a value of -2. Only when the cursor is defined with the SCROLL option,
Only return a value of -2. This variable must be checked after each data extraction to ensure the validity of the data.
SQL Server does not support Oracle's cursor FOR loop syntax.
CURRENT OF clause
The update and deleted CURRENT OF clause syntax and function are the same in PL/SQL and Transact-SQL. In a given cursor, the positioned UPDATE and DELETE are performed on the current row.
Close a cursor
The CLOSE CURSOR statement of Transact-SQL closes the cursor, but keeps the data structure in case of reopening. The CLOSE CURSOR statement of PL/SQL closes and releases all data structures.
Transact-SQL requires the DEALLOCATE CURSOR statement to clear the cursor data structure. The DEALLOCATE CURSOR statement is different from the CLOSE CURSOR.
The latter retains the data structure for reopening. DEALLOCATE CURSOR releases all cursor-related data structures and clears the cursor definition.
Cursor example
The following example shows cursor statements equivalent to PL/SQL and 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 @VSSN, @VFNAME, @VLNAME
END
CLOSE CUR1
DEALLOCATE CUR1