Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
How to convert Oracle procedures(with CURSORS) to SQL?
- Thread starter HiBoo
- Start date
HiBoo,
Have a look in SQL Server Books on line and search for ORACLE. It gives the syntax of most statements and how to convert them. An extract for Cursors is as follows:
Migrating Cursors from Oracle to SQL Server
Oracle requires using cursors with SELECT statements, regardless of the number of rows requested from the database. In Microsoft® SQL Server™, a SELECT statement that is not disclosed on a cursor returns rows to the client as a default result set. This is an efficient way to return data to a client application.
SQL Server provides two interfaces for cursor functions. When using cursors in Transact-SQL batches or stored procedures, SQL-92 standard SQL syntax has been added for declaring, opening, and fetching from cursors as well as positioned updates and deletes. When using cursors from a DB-Library or ODBC program, the SQL Server client libraries transparently call built-in server functions to handle cursors more efficiently.
When porting a PL/SQL procedure from Oracle, first determine whether cursors are needed to do the same function in Transact-SQL. If the cursor is used only to return a set of rows to the client application, use a noncursor SELECT statement in Transact-SQL to a return default result set. If the cursor is used to load data a row at a time into local procedure variables, you must use cursors in Transact-SQL.
The following table shows the syntax for using cursors.
Operation Oracle SQL Server
Declaring a cursor CURSOR cursor_name [(cursor_parameter(s))]
IS select_statement; DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR FOR select_statement
[FOR {READ ONLY | UPDATE [OF column_list]}]
Opening a cursor OPEN cursor_name [(cursor_parameter(s))]; OPEN cursor_name
Fetching from cursor FETCH cursor_name INTO variable(s) FETCH [[NEXT | PRIOR | FIRST | LAST | ABSOLUTE {n | @nvar} | RELATIVE {n | @nvar}]
FROM] cursor_name
[INTO @variable(s)]
Update fetched row UPDATE table_name
SET statement(s)...
WHERE CURRENT OF cursor_name; UPDATE table_name
SET statement(s)...
WHERE CURRENT OF cursor_name
Delete fetched row DELETE FROM table_name
WHERE CURRENT OF cursor_name; DELETE FROM table_name
WHERE CURRENT OF cursor_name
Closing cursor CLOSE cursor_name; CLOSE cursor_name
Remove cursor data structures N/A DEALLOCATE cursor_name
Sorry about the formatting?
There is also a some very good articles in TECHNET if you have access to it, describing how to convert from Oracle to SQLServer
hope this helps,
Chris Dukes
Have a look in SQL Server Books on line and search for ORACLE. It gives the syntax of most statements and how to convert them. An extract for Cursors is as follows:
Migrating Cursors from Oracle to SQL Server
Oracle requires using cursors with SELECT statements, regardless of the number of rows requested from the database. In Microsoft® SQL Server™, a SELECT statement that is not disclosed on a cursor returns rows to the client as a default result set. This is an efficient way to return data to a client application.
SQL Server provides two interfaces for cursor functions. When using cursors in Transact-SQL batches or stored procedures, SQL-92 standard SQL syntax has been added for declaring, opening, and fetching from cursors as well as positioned updates and deletes. When using cursors from a DB-Library or ODBC program, the SQL Server client libraries transparently call built-in server functions to handle cursors more efficiently.
When porting a PL/SQL procedure from Oracle, first determine whether cursors are needed to do the same function in Transact-SQL. If the cursor is used only to return a set of rows to the client application, use a noncursor SELECT statement in Transact-SQL to a return default result set. If the cursor is used to load data a row at a time into local procedure variables, you must use cursors in Transact-SQL.
The following table shows the syntax for using cursors.
Operation Oracle SQL Server
Declaring a cursor CURSOR cursor_name [(cursor_parameter(s))]
IS select_statement; DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR FOR select_statement
[FOR {READ ONLY | UPDATE [OF column_list]}]
Opening a cursor OPEN cursor_name [(cursor_parameter(s))]; OPEN cursor_name
Fetching from cursor FETCH cursor_name INTO variable(s) FETCH [[NEXT | PRIOR | FIRST | LAST | ABSOLUTE {n | @nvar} | RELATIVE {n | @nvar}]
FROM] cursor_name
[INTO @variable(s)]
Update fetched row UPDATE table_name
SET statement(s)...
WHERE CURRENT OF cursor_name; UPDATE table_name
SET statement(s)...
WHERE CURRENT OF cursor_name
Delete fetched row DELETE FROM table_name
WHERE CURRENT OF cursor_name; DELETE FROM table_name
WHERE CURRENT OF cursor_name
Closing cursor CLOSE cursor_name; CLOSE cursor_name
Remove cursor data structures N/A DEALLOCATE cursor_name
Sorry about the formatting?
There is also a some very good articles in TECHNET if you have access to it, describing how to convert from Oracle to SQLServer
hope this helps,
Chris Dukes
- Status
Similar threads
- Locked
- Question
- Locked
- Question