Shulou(Shulou.com)05/31 Report--

This article introduces what is the difference between ORACLE system function and SQLSERVER system function, the content is very detailed, interested friends can refer to, hope to be helpful to you.

Character function

Function Oracle Microsoft SQL Server

Convert characters to ASCII: ASCII ASCII

String connection: CONCAT-(expression + expression)

Convert ASCII to characters CHR, CHAR

Returns the start character (from left) INSTR,-CHARINDEX in the string

Convert characters to lowercase LOWER-LOWER

Convert characters to uppercase UPPER- UPPER

Fill the left side of the string with LPAD-Ndeband A

Clear the initial blank LTRIM-LTRIM

Clear the blank tail RTRIM-RTRIM

Start mode in a string (pattern) INSTR-PATINDEX

Repeat the string RPAD-REPLICATE multiple times

The phonetic representation of string SOUNDEX-SOUNDEX

The string RPAD-SPACE that repeats spaces

Conversion from numeric data to character data TO_CHAR-STR

Substring SUBSTR-SUBSTRING

Replacement character REPLACE-STUFF

Capitalize the first letter of each word in the string INITCAP-N _ A

Translate the string TRANSLATE-NumberA

String length LENGTH- DATELENGTH or LEN

The largest string in the list, GREATEST- NumberA

The smallest string in the list, LEAST-NCMA

Convert the string NVL- ISNULL if it is NULL

Date function

Here are the date functions supported by Oracle and their Microsoft SQL Server equivalents.

Function Oracle-Microsoft SQL Server

Date addition (date column + /-value) or ADD_MONTHS-DATEADD

The difference between two dates (date column + /-value) or MONTHS_BETWEEN-DATEDIFF

Current date and time SYSDATE-GETDATE ()

The last day of the month LAST_DAY-N _ A

Time zone conversion NEW_TIME-NumberA

The first Sunday after the date NEXT_DAY-N _ big A

The string TO_CHAR-DATENAME that represents the date

Integer TO_NUMBER (TO_CHAR) representing the date)-DATEPART

Date rounding ROUND-CONVERT

Date truncation TRUNC-CONVERT

Convert a string to a date TO_DATE-CONVERT

If NULL, convert the date NVL-ISNULL

Conversion function

Here are the conversion functions supported by Oracle and their Microsoft SQL Server equivalents.

Function Oracle-Microsoft SQL Server

Convert numbers to characters TO_CHAR-CONVERT

Convert characters to numbers TO_NUMBER-CONVERT

Date is converted to character TO_CHAR-CONVERT

Character conversion to date TO_DATE CONVERT

Convert hexadecimal to binary HEX_TO_RAW-CONVERT

Convert binary to hexadecimal RAW_TO_HEX-CONVERT

Functions at other line levels

Here are the other line-level functions supported by Oracle and their Microsoft SQL Server equivalents.

Function Oracle-Microsoft SQL Server

Returns the first non-empty expression DECODE-- COALESCE

Current sequence value CURRVAL-NumberA

The next sequence value is NEXTVAL-NumberA

User login account ID digital UID-SUSER_ID

User login name USER-SUSER_NAME

User database ID digital UID-USER_ID

User database name USER-USER_NAME

Current user CURRENT_USER-CURRENT_USER

User environment (audit trail) USERENV-NAMA

Level LEVEL-N LEVEL An in the CONNECT BY clause

Aggregate function

Here are the aggregate functions supported by Oracle and their Microsoft SQL Server equivalents.

Function 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

Conditional test

Both Oracle's DECODE statement and Microsoft SQL Server's CASE expression perform conditional tests.

When the value in test_value matches any subsequent expression, the related value is returned. If no matching values are found, default_value is returned.

If default_value is not specified, both DECODE and CASE return a NULL if there is no match. The following table shows the syntax of the statement

At the same time, an example of converting DECODE command is given.

Oracle Microsoft SQL

DECODE (test_value

Expression1, value1

[, default_value]

)

CREATE VIEW STUDENT_ADMIN.STUDENT_GPA

(SSN, GPA)

AS SELECT SSN, ROUND (AVG (DECODE (grade)

, 'Aunt, 4

, 'Asians, 4.3

, 'Amura, 3.7

, 'Bamboo, 3

, 'Beverly, 3.3

, 'BMurray, 2.7

, 'Che, 2

, 'Clearing, 2.3

, 'CMurray, 1.7

, 'Downs, 1

, 'Dracula, 1.3

, 'Dmurf, 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 'Aids' THEN 4.3

WHEN 'Amura' THEN 3.7

WHEN 'B'THEN 3

WHEN 'backed' THEN 3.3

WHEN 'Bmuri' THEN 2.7

WHEN 'C'THEN 2

WHEN 'Clearing' THEN 2.3

WHEN 'Cmuri' THEN 1.7

WHEN 'D'THEN 1

WHEN'Delift 'THEN 1.3

WHEN 'Dmuri' THEN 0.7

ELSE 0

END), 2)

FROM STUDENT_ADMIN.GRADE

GROUP BY SSN

CASE expressions can support Boolean tests with select statements, which is not allowed by the DECODE command. For more information about CASE expressions

Refer to the SQL Server online manual.

Convert values to different data types

The CONVERT and CAST functions of Microsoft SQL Server are multi-objective conversion functions. They provide similar functions.

Converts an expression of one data type to an expression of another data type and supports a variety of specialized data formats.

CAST (expression AS data_type)

CONVERT (data type [(length)], expression [, style])

CAST is a standard SQL-92 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 to which any expression will be converted. 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 length allowed is 8000.

Convert Oracle Microsoft SQL Server

Character to number TO_NUMBER (characters')-CONVERT (numeric, characters')

Digit to character TO_CHAR (10)-CONVERT (char, 10)

Character to date TO_DATE (character-JUL-97')

TO_DATE (recently-JUL-1997','dd-mon-yyyy')

TO_DATE ('July 4, 1997,' Month dd, yyyy')-CONVERT (datetime, September-JUL-97')

CONVERT (datetime, floor-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 ())

Hexadecimal to binary HEXTORAW (binary F')-CONVERT (binary, binary F')

2 to hexadecimal RAWTOHEX (binary_column)-CONVERT (char, binary_column)

Notice how the string is converted to a 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. The CONVERT function automatically converts the standard date format without any format model.

When converting from a date to a string, the default output of the CONVERT function is "dd mon yyyy hh:mm:ss:mmm (24 hours)".

Format the output with a numeric style code so that it can be output to 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 yy.mm.dd

3 103 British/French dd/mm/yy

4 104 German dd.mm.yy

5 105 Italian dd-mm-yy

6106-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 (24 hours)

14 114-hh:mi:ss:mmm (24 h)

User-defined function

The Oracle PL/SQL function can be used in Oracle SQL statements. The same function can generally be achieved in other ways in Microsoft SQL Server.

In SQL Server, you can use the query given in the table 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 operator

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

Operator Oracle Microsoft SQL Server

Equal to (=)

Greater than (>)

Less than (=)

Less than or equal to (ALL, = ALL,! = SOME, SOME

SOME

= SOME! = ALL, ALL

< ALL, >

ALL, = ALL,! = SOME, SOME

SOME

= SOME

Image mode (Like pattern) LIKE LIKE

Unlike Not like pattern NOT LIKE NOT LIKE

The value BETWEEN x AND y BETWEEN x AND y between X and y

Value NOT BETWEEN NOT BETWEEN that is not between x and y

Value exists EXISTS EXISTS

Value does not exist NOT EXISTS NOT EXISTS

The value {is | not} empty IS NULL, IS NOT NULL Same. Also = NULL

! = NULL for backward compatibility (not recommended)

Pattern matching

SQL Server's LIKE keyword provides a useful wildcard search feature, which is not supported in Oracle

In addition to the (%) and (_) wildcards supported by all RDBMS, SQL Server also supports ([]) and ([^]) wildcards.

The ([]) character is used to query all individual characters in a range. For example, if you need to query data that contains a character from a to f

You can write: "LIKE'[a murf]'" 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 like this:

LIKE'[^ a-f]'or LIKE'[^ abcdef]'.

For more information about the LIKE keyword, refer to the SQL Server online manual.

Using NULL in comparison

Although Microsoft SQL Server traditionally supports SQL-92 standard and some non-standard NULL behavior, it still supports the use of NULL in Oracle.

In order to support distributed queries, SET ANSI_NULLS must be set to ON.

SQL Server's SQL Server ODBC driver and OLE DB provider automatically set SET ANSI_NULLS to ON when making a connection.

This setting can be configured in the ODBC data source, the ODBC connection property, or in the OLE DB connection property set in the application before connecting to the SQL Server.

When connecting from a DB-Library application, SET ANSI_NULLS defaults to OFF.

When SET ANSI_DEFAULTS is ON, SET ANSI_NULLS is allowed.

For more information about the use of NULL, please refer to the SQL Server online manual.

String connection

Oracle uses two pipe symbols (| |) as string concatenation operators, and SQL Server uses the 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 the flow of SQL statement execution, statement blocks, and stored procedures. PL/SQL and Transact-SQL provide most of the same structure, but there are some grammatical differences.

Keyword

These are two keywords supported by RDBMS.

Statement Oracle PL/SQL:

Declare the 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

[BEGIN... END]

ELSE IF

CASE expression

Unconditionally end RETURN- RETURN

Unconditionally end the statement EXIT BREAK after the current block

Restart a WHILE loop Namp A CONTINUE

Wait for the specified interval NCMA (dbms_lock.sleep) WAITFOR

Cycle control WHILE LOOP... END LOOP

-

LABEL... GOTO LABEL

FOR... END LOOP

LOOP... END LOOP

WHILE

BEGIN... END

LABEL... GOTO LABEL

Program comments / *... * /,-/ *... * /,-

Printout RDBMS_OUTPUT.PUT_LINE PRINT

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

Executor EXECUTE--EXECUTE

Statement Terminator Semicolon (;)-NCMA

Declare variable

The variables for Transact-SQL and PL/SQL are created with the DECLARE keyword. The Transact-SQL variable is marked with @

And just like PL/SQL, it is initialized with a null value when it is first created.

Oracle:

DECLARE

VSSN CHAR (9)

VFNAME VARCHAR2 (12)

VLNAME VARCHAR2 (20)

VBIRTH_DATE DATE

VLOAN_AMOUNT NUMBER (12pr 2)

-

Microsoft SQL:

DECLARE

@ VSSN CHAR (9)

@ VFNAME VARCHAR2 (12)

@ VLNAME VARCHAR2 (20)

@ VBIRTH_DATE DATETIME

@ VLOAN_AMOUNT NUMERIC (12pr 2)

Transact-SQL does not support TYPE and ROWTYPE variable data type definitions. A Transact-SQL variable cannot be initialized in the DECLARE command.

The NOT NULL and CONSTANT keywords of Oracle cannot also be used in Microsoft SQL Server data type definitions.

Like the LONG and LONG RAW data types of Oracle. Text and graphic data types cannot be used as variable definitions.

In addition, Transact-SQL does not support PL/SQL-style definition of records and tables.

Assign a value to a variable

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 examples of syntax

Oracle:

DECLARE VSSN CHAR (9)

VFNAME VARCHAR2 (12)

VLNAME VARCHAR2 (20)

BEGIN

VSSN: =?'

SELECT FNAME, LNAME INTO VFNAME, VLNAME FROM STUDENTS WHERE SSN=VSSN

END

/

Microsoft SQL:

DECLARE @ VSSN CHAR (9)

@ VFNAME VARCHAR (12)

@ VLNAME VARCHAR (20)

SET @ VSSN =?'

SELECT @ VFNAME=FNAME, @ VLNAME=LNAME FROM STUDENTS WHERE SSN = @ VSSN

Sentence block

Both Oracle PL/SQL and Microsoft SQL Server Transact-SQL support BEGIN. An END term to mark a block of statements.

Transact-SQL does not need to use a statement block after the DECLARE statement.

-if you are in Microsoft SQL Server

If more than one statement is executed in the IF statement and the WHILE loop, you need to use 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 treatment

Microsoft SQL Server Transact-SQL 's 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, using CASE expressions may be easier and more readable.

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 the 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]

The WHILE loop needs to test a Boolean expression to determine the repeated execution of one or more statements.

This statement is repeated as long as the given expression turns out to be true. If there are multiple statements to execute, they must be placed in a BEGIN. In the END block.

Oracle:

DECLARE

COUNTER NUMBER

BEGIN

COUNTER: = 0

WHILE (COUNTER)

Open a cursor OPEN cursor_name [(cursor_parameter (s))]

-

OPEN cursor_name

Extract (Fetching) FETCH cursor_name INTO variable (s) from the cursor

-

FETCH FROM] cursor_name

[INTO @ variable (s)]

Update extraction line UPDATE table_name

SET statement (s)...

WHERE CURRENT OF cursor_name; UPDATE table_name

SET statement (s)...

WHERE CURRENT OF cursor_name

Delete 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

Clears the cursor data structure NumberA 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 opens

It uses the values of these local variables. Microsoft SQL Server provides many additional features in its DECLARE CURSOR.

The INSENSITIVE option defines 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

Changes to the original table are not reflected in the data returned by fetch for the cursor. The 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 returned an error

Indicates that the cursor type has been changed, or gives a set of parameters to implicitly convert the cursor. To get a trigger SQL Server 7. 0 implicitly converts cursors from a type to

For a complete list of parameters of another type, see the SQL Server online manual.

The SCROLL option allows backward, absolute, and relative data extraction in addition to forward extraction. A scroll cursor uses a cursor model of a set of keys, in which

Any deletions and updates to the table submitted by the user will affect subsequent data extraction. The above features work 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 disabled. This option overrides the default option for cursors, allow updates.

The UPDATE [OF column_list] statement is used to define an updatable column in the cursor. If [OF column_list] is provided, then only those listed columns can be modified.

If no columns are specified. All columns are updatable unless the cursor is defined as READ ONLY.

It is important to note that the name range of a SQL Server cursor is to connect to yourself. This is different from the name range of local variables.

You cannot declare a cursor with the same name as an existing cursor on the same user connection unless the first cursor is released.

Open a cursor

Unlike PL/SQL, Transact-SQL does not support passing parameters to an open cursor. When a Transact-SQL cursor is opened

The membership and order of the result set are fixed. Updates and deletions to the cursor of the original table submitted by other users will be reflected in the definition of all unINSENSITIVE options

On the data extraction of the cursor. For an INSENSITIVE cursor, a temporary table is generated.

Extract data

Oracle cursors can only move forward without the ability to scroll backward or relatively. SQL Server cursors can scroll forward or backward, exactly how to scroll

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 returns the first row of the result set if this is the first extraction of the cursor; otherwise, move the cursor to the next row within the result union.

NEXT is the basic method of moving through the result set. NEXT is the default cursor extraction (fetch).

PRIOR returns the previous row of the result set.

FIRST moves the cursor to the first row of the result set and returns the first row.

LAST moves the cursor to the last row of the result set and returns the last row.

ABSOLUTE n returns the nth row of the result set. If n is negative, the penultimate line is returned

RELATIVE n returns the nth row after the current fetched row, or the penultimate nth row from the relative position of the cursor if n is negative.

The FETCH statement of Transact-SQL does not require an INTO clause. If no return variable is specified, the row is automatically returned to the customer as a single-row result set. But,

If your process has to give the line to the customer, a SELECT statement without a cursor is more effective.

After 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 data beyond 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 if the cursor is defined with the SCROLL option

A value of-2 is returned. 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 syntax and functions of the updated and deleted CURRENT OF clauses are the same in PL/SQL and Transact-SQL. UPDATE and DELETE that are positioned on the current line in a given cursor.

Close a cursor

The CLOSE CURSOR statement of Transact-SQL closes the cursor, but retains the data structure for reopening. The CLOSE CURSOR statement of PL/SQL closes and frees all data structures.

Transact-SQL requires a DEALLOCATE CURSOR statement to clear the cursor data structure. The DEALLOCATE CURSOR statement is different from CLOSE CURSOR.

The latter retains the data structure for reopening. DEALLOCATE CURSOR releases all data structures associated with cursors and clears the definition of cursors.

Cursor exampl

The following example shows the equivalent cursor statements in 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

About ORACLE system function and SQLSERVER system function what is the difference is shared here, hope that the above content can have some help to everyone, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.