sql--system functions

1) Case

There are two types of use case: one is simple, the other is a case function, and the other is the search type.

[1] Simple case function

Format:

Case input_expression

When when_expression then result_expression

[... n]

[ELSE else_result_expression]

END

Attention: The data types of the parameters input_expression and when_expression must be the same or can be converted invisibly.

EG1:

[2] Case Search Function:

Function: Evaluates the boolean_expression expression for each when clause in the order specified, returning the value of the result_expression expression corresponding to the first boolean_expression expression that evaluates to True If no value is true boolean_expression expression, returns the value of else_result_expression when the ELSE clause is specified, or null if no ELSE clause is specified.

Format:

Case

When Boolean_expression then result_expression

[... n]

[ELSE else_result_expression]

END

EG1:

2) CAST and CONVERT

Common types of conversions are in the following situations:

Date type, character type: Converts datetime or smalldatetime data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types).

Character Type-date type: Converts character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types) to datetime or smalldatetime data.

Numeric type: If you convert float, real, money, or smallmoney data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types).

Format:

CAST (expression as data_type)

CONVERT (data_type[(length)], expression [style])

Attention:data_type cannot be a user-defined data type. The commonly used values of style and their effects are shown in the following table:

The common values of style and its function in date and character conversions

Style takes value Not with the century digits	Style takes value With century digits	Standard	Input/Output
	0 or 100	Default value	Mon dd yyyy hh:miam (or PM)
1	101	United States	Mm/dd/yyyy
2	102	Ansi	Yy.mm.dd
	9 or 109	Default value + milliseconds	Mon dd yyyy hh:mi:ss:mmmAM (or PM)
10	110	United States	Mm-dd-yy
12	112	Iso	Yymmdd

eg

Value of style when float or real is converted to character data

style;	output
style= font-size:14px ">0" (default value)
1	use scientific notation, length 8.
2	use scientific notation, length 16.

eg

The value of the style when converting from money or smallmoney to character data

Value	Output
0 (default value)	Every three digits to the left of the decimal point are not separated by commas, and two digits to the right of the decimal point, for example 4236.98.
1	A comma separates each three digits to the left of the decimal point and two digits to the right of the decimal point, such as 3,610.92.
2	Every three digits to the left of the decimal point are not separated by commas, and four digits to the right of the decimal point, for example 4236.9819.

eg

Eg: The following procedure will retrieve the student's name in total credits 40~49 and convert the total credits to char (20).

The following examples use CAST and CONVERT respectively

3) Coalesce

Function: Returns the value of the first non-empty expression in a parameter expression, or coalesce returns null if all arguments are null.

Format:coalesce (expression[... n])

EG1:

EG2:

6) Date-time function

The date function can be used in the select list of the SELECT statement or in the WHERE clause of the query, where the GETDATE () function is described.

Function: Returns the current date and time. return value type: datetime

Format:getdate ()

eg

7) Cursor function

The cursor function is used to return information about the cursor. The main function is as downstream standard functions:

Function: Returns the number of rows that currently exist in the last-opened cursor that satisfy the condition. The return value type is: integer

format:@ @CURSOR_ROWS

eg

The following example declares a cursor and uses SELECT to display the value of the @ @CURSOR_ROWS.

[2] Cursor_status

Function: Returns the cursor state value, whether it is open or closed

Format:

Cursor_status

({' Local ', ' cursor_name '}/* indicates that the data source is a local cursor */

| {' Global ', ' cursor_name '}/* indicates that the data source is a global cursor */

| {' variable ', ' cursor_variable '}/* indicates that the data source is a cursor variable */

)

The return value of the Cursor_status () function is shown in the following table:

return value	Cursor name or cursor variable
1	At least one row of the cursor's result set.
0	The result set of the cursor is empty. *
-1	The cursor is closed.
-2	The cursor is not available.
-3	The specified cursor does not exist.

eg

1 SELECT @ @CURSOR_ROWS  asa2 DECLAREStudent_cursorCURSOR  for3            SELECTSname fromXS4     OPENStudent_cursor5     FETCH NEXT  fromStudent_cursor6     SELECT @ @CURSOR_ROWS  asb7     Select Cursor_status('Global','Student_cursor') asC8     CLOSEStudent_cursor9     deallocateStudent_cursorTen Go

View Code

Result:

[3]@ @FETCH_STATUS

format:@ @FETCH_STATUS

Function: Returns the state of the cursor after the FETCH statement executes. Return value type: integer,@ @FETCH_STATUS The return value is shown in the following table.

return value	Description
0	The FETCH statement was executed successfully.
-1	FETCH statement execution failed.
-2	The record being read does not exist.

eg

1  1 /*use @ @FETCH_STATUS to control cursor activity in a while loop. */2  2  UseXSCJ3  3 Declare @name Char(Ten),@st_id Char(6)4  4 DeclareStudent_cursorCURSOR5  5      for    SelectSname,sno fromXSCJ.dbo.XS6  6     OpenStudent_cursor7  7     FETCH NEXT  fromStudent_cursor into @name,@st_id8  8     Select @name,@st_id9  9      while @ @FETCH_STATUS = 0Ten Ten     begin One  One         Fetch Next  fromStudent_cursor into @name,@st_id A  A         Select @name,@st_id -  -     End -  - CloseStudent_cursor the  the deallocateStudent_cursor

View Code

Result:

[4] Meta data functions

Metadata is used to describe the database and database objects. Metadata functions are used to return information about databases and database objects.

1) db_id

FORMAT:DB_ID ([' database_name '])

Function: When the system creates a database, it automatically creates an identification number for it, the function db_id returns its database identification number (ID) based on the database name specified by database_name, and returns the current database ID if the parameter database_name is not specified. The return value type is smallint.

2) db_name function

Format:db_name (database_id)

Function: Returns its database name (name).

sql--system functions