SQL Server user-defined Function usage, sqlserverfunction

SQL Server user-defined Function usage, sqlserverfunction

I. FUNCTION:
In sqlserver2008, there are 3 custom functions: scalar functions, inline table valued functions, and multi-statement table valued functions. First, summarize the similarities and differences of their syntax:

Similarities:
1. the creation definition is the same: a, create function F_NAME (type of input parameter name) B, RETURNS return value type c, AS difference: 1. a scalar function returns a data type value. an inline table value function returns a table, while multiple statements return a table variable (similar to the combination of the first two );
2. syntax structure: scalar functions and multi-statement functions must have begin, end, and inline Table value functions;
3. Call: the scalar function must be written in dbo, function_name;

The scalar function uses the data table written in the previous article to query the employee ID of Li Yifeng in the [T _ employee information] table:

USE SQL _SYSTEM GOCREATE FUNCTION F_GONGHAO (@ XINGMING NVARCHAR (5) RETURNS INT ASBEGINDECLARE @ GONGHAO INTSET @ GONGHAO = (SELECT Y. employee id from [T _ employee information] as y where y. name = @ XINGMING) RETURN @ GONGHAOENDGO/* The above is a simple example of a scalar function, which is called below. Note that it is in dbo. next */SELECT [name] FROM [T _ employee information] WHERE [name] = 'Li yifeng'/** query for comparison * // * Please observe, and Comparison */SELECT [name], DBO. f_GONGHAO ('Li yifeng') AS employee id from [T _ employee information] WHERE [name] = 'Li yifeng' GO

F5:

Inline table value function: This function returns a table. For example, filter the logging records of all employees on January 28:

USE SQL _SYSTEM GOCREATE FUNCTION F_DAKAJILU (@ RIQI DATE) RETURNS TABLEASRETURN (SELECT * FROM [T _ attendance] AS K WHERE K. date = @ RIQI) GO/* It should be noted that there is no begin + end * // * in the writing method, the following is to bring date into the function */SELECT * FROM [F_DAKAJILU] ('2017/28') GO

F5:

The multi-statement Table value function is the integrated version of the table Value Function + inline Table value function: Write a response to the on-day logging and personal information related to employees:

USE SQL _SYSTEM GOCREATE FUNCTION F_D_DAKAJILU (@ RIQI DATE) RETURNS @ TEMP_TABLE TABLE (/* Note that since multiple statements are used, then you need to tell the computer which columns you want to query */[name] NVARCHAR (5) not null, [employee ID] int not null, [position] NVARCHAR (10) not null, [Department] NVARCHAR (5) not null, [night shift or NOT] NCHAR (1) not null, [DATE] date not null, [working day] FLOAT (1) not null) /* the above is the basic information column you want to tell the computer */asbegininsert into @ TEMP_TABLE/*, which means to insert the following query results INTO the @ TEMP_TABLE variable * /select y. name, Y. employee ID, Y. position, Y. department, K. night shift, K. date, K. FROM [T _ employee information] as y cross join [T _ attendance] as k/* I am using a CROSS connection */WHERE Y. employee ID = K. employee id and k. date = @ riqi return endgoselect * FROM [F_D_DAKAJILU] ('2017/28') GO

F5:

Ii. CURSORYou can use it to retrieve each piece of complex data. Similar to the C language pointer, it can experience each piece of data and update in your data table. 1. Write a new cursor experience data table (T _ employee information) Data Process: 1. 1. Declare a read-only cursor:

USE SQL _SYSTEM SET TRANSACTION ISOLATION LEVEL REPEATABLE READBEGIN TRANSACTION CURSOR_READ_T _ employee information DECLARE CUR_T _ employee information SCROLL CURSOR FORSELECT Y. name, Y. employee ID, Y. position, Y. department FROM [T _ employee information] as y order by y. id asc commit transaction CURSOR_READ_T _ employee information GO

1. 2. open:

Open global CUR_T _ employee info GO

1. 3. Experience each piece of data:

/* DECLARE four variables to receive data from the temporary cursor */DECLARE @ xingming nvarchar (3), @ gonghao int, @ zhiwei nvarchar (10), @ bumen nvarchar (8) /* use global variables to obtain the number of data rows in the current data table and */PRINT 'the current data table contains' + CAST (@ CURSOR_ROWS as nvarchar (6) + 'row data. '/* Read the FIRST data and save it to the saved variable */fetch first from [CUR_T _ employee information] INTO @ XINGMING, @ GONGHAO, @ ZHIWEI, @ BUMEN/* use the value returned by @ FETCH_STATUS to determine whether the cursor reads data in the data table = 0 indicates that fetch is successfully executed */WHILE (@ FETCH_STATUS = 0) BEGINPRINT 'Name: '+ @ XINGMING +' employee ID: '+ CONVERT (VARCHAR (3), @ GONGHAO) +' position: '+ @ ZHIWEI +' department: '+ @ bumenfetch next from [CUR_T _ employee information] INTO @ XINGMING, @ GONGHAO, @ ZHIWEI, @ BUMENENDGO

F5:

. Close and release after use:

/* CLOSE */close global CUR_T _ employee information/* release memory */deallocate global CUR_T _ employee information GO

In this way, the function of reading each record is realized, and then it can be used to update related data records (all employee numbers + 100 ):

USE SQL _SYSTEM/* to DECLARE and enable update -- cursor */SET TRANSACTION ISOLATION LEVEL REPEATABLE READBEGIN TRANSACTION TR_UPDATE_YG DECLARE CUR_T_YG CURSOR FORSELECT YG. name, YG. employee ID, YG. position, YG. department FROM [T_yuangongxinxi] as yg for update open global CUR_T_YGCOMMIT TRANSACTION TR_UPDATE_YG

UPDATE:

PRINT 'currently '+ CONVERT (VARCHAR (3), @ CURSOR_ROWS) +' data rows. 'Fetch next from [CUR_T_YG] WHILE (@ FETCH_STATUS = 0) BEGINUPDATE [T_yuangongxinxi] SET [employee ID] = [employee ID] + 100 where current of CUR_T_YGFETCH next from [CUR_T_YG] END

SELECT:

Close release:

CLOSE GLOBAL CUR_T_YGDEALLOCATE GLOBAL CUR_T_YG

3. PROCEDURE: a stored PROCEDURE is a program written in the Transact-SQL language provided by SQL Server. It can also be called in advanced languages. 3. 1. No parameter: indicates a pro that calls the query data table:

USE SQL_SYSTEM GOCREATE PROCEDURE PRO_SELECT_TWITH RECOMPILE ASSELECT* FROM[T_yuangongxinxi] GOEXECUTE:EXECUTE PRO_SELECT_T

F5:

3.1.2.EXECUTE PRO_SELECT_T is actually a view here. You can also insert the execute call result to the new table:

USE SQL _SYSTEM SELECT * INTO AA FROM [T_yuangongxinxi] GOTRUNCATE TABLE AA -- the difference between TRUNCATE and DELETE is that TR is more efficient than DE, and will leave a recovery record GOINSERT INTO AAEXECUTE PRO_SELECT_TGO in the log

The result cannot be reached. 3. 2. with the parameter pro, write a modified message and output the modified information. For example, if the company's employees often change their positions, write this information, after the employee ID is changed, you can modify the employee's position and then feedback the modification operation:

USE SQL _SYSTEM GOCREATE PROCEDURE PRO_DAUP_ZHIWEI @ GONGHAO INT, @ ZHIWEI NVARCHAR (10), @ RETURNS NVARCHAR (50) OUTPUTASBEGIN/* get the job information before update */DECLARE @ QIAN_RETURN_ZHIWEI NVARCHAR (10), @ xingming nvarchar (3) SELECT @ QIAN_RETURN_ZHIWEI = AA. position, @ XINGMING = AA. name FROM [AA] where aa. employee ID = @ GONGHAO/* UPDATE */UPDATE [AA] SET [position] = @ zhiwei where aa. employee ID = @ gonghao set @ RETURNS = 'the employee ID has been successfully SET to:' + CONVERT (VARCHAR (3), @ GONGHAO) + '. The name is: ['+ @ XINGMING +'], position ['+ @ QIAN_RETURN_ZHIWEI +'] updated to: ['+ @ ZHIWEIEND

EXECUTE:

DECLARE @ printf nvarchar (50); EXECUTE PRO_DAUP_ZHIWEI 101, 'SQL engineer', @ PRINTF OUTPUT SELECT @ PRINTF AS 'Update message' GO

F5:

Here is PRO;
4. TRIGGERLike a mine, if you break his requirements, he will start to work. He can also use this function to maintain or block some unwanted error operations, such: do not delete a [AA] table:

USE SQL _SYSTEM gocreate trigger DROP_TON database for DROP_TABLE asbeginrollback transaction print cannot be deleted because I have added the TRIGGER protection! 'End

Drop:

DROP TABLE AA 

F5:

DML is used to solve the problem of data at the lowest layer: There are two temporary tables: deleted and inserted logical (concept) tables. To understand this, you must know how trigger works: insert operation:

Delete operation:

Update operation:

For example, the update operation focuses on the inserted and deleted tables:

USE [SQL _SYSTEM] GO/****** Object: Trigger [dbo]. [UPDATE_T] Script Date: 03/04/2014 16:04:21 *****/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ongocreate trigger [dbo]. [UPDATE_T] ON [dbo]. [AA] instead of updateasbegininsert into T_UPDATE_HOUSELECT * FROM [inserted]/* INSERT new data INTO [T_UPDATE_HOU] */insert into T_UPDATE_QIANSELECT * FROM [deleted]/* update the previous data is inserted into T_UPDATE_QIAN */PRINT. The update is complete, the data before the update has been written to [T_UPDATE_QIAN ], The updated data is inserted into [T_UPDATE_HOU ]. 'End

Update:

USE SQL _SYSTEM GOUPDATE [AA] SET AA. position = 'SQL Senior Engineer' WHERE AA. the employee ID = 101/* is a simple update operation, used to trigger update */SELECT * FROM [T_UPDATE_HOU] -- SELECT * FROM [T_UPDATE_QIAN] -- data to be modified GO

F5:

Last year, I wrote an update operation to the view connected to multiple tables using the trigger:

Http://www.bkjia.com/article/95473.htm