relational database SQL programmability functions (user-defined functions)

In addition to the previous basic database and data table operations in the relational database, programmability functions, stored procedures, transactions, triggers, and cursors are provided.

This article describes the functions.

The functions are divided into two types:

1. System functions

2. User-defined Functions

Preparatory work

Here is an example of a bank deposit and withdrawal.

1. Create a data table

--Create Account information tableCreateTable AccountInfo (
--Account ID CustIDIntIdentity1,1) PrimaryKey
--Account name CustNamevarchar20)NotNull
--ID Number Idcardvarchar18),
--Telephone telephonevarchar13)NotNull
--Addressvarchar50)Default' Address unknown '))
Go

--Create Card information tableCreateTable Cardinfo (
--bank card number CardIDvarcharPrimary)Key
--Bank card password Cardpasswordvarchar6)NotNullDefault' 888888 '),
--ID Number CustIDIntReferences AccountInfo (CustID),
--Deposit type Savetypevarchar10)NotNull
--Opening date opendate datetimeNotNullDefaultGETDATE ()),
--Account Amount Openmoney moneyNotNullCheck (openmoney>1),
--Available Balance Leftmoney moneyNotNullCheck (leftmoney>1),
--whether to report the loss Islostvarchar2)NotNullDefault' No '))
Go

--Transaction Information tableCreateTable Transinfo (
--Transaction Number TransidIntIdentity PrimaryKey
--bank card number CardIDvarchar19)NotNull
--Transaction type Transtypevarchar4)NotNull
--Transaction amount Transmoney MoneyNotNull
--Trading Time TransDate datetimeDefaultGETDATE ()))
Go

/*1. Creating a foreign key for the bank card number (CardID) field of the Transaction information table (Transinfo) using the T-SQL statement */

--Foreign KEY (bank card number Cardid)

AlterTable [dbo]. TransinfoWithCheckAddconstraint [Fk_cardinfo_transinfo] ForeignKey (CardID)References [dbo]. [Cardinfo] (CardID)
Go

/*2. Using T-SQL statements to create a unique constraint for the bank card number (Idcard) field of the Account Information table (accountinfo) */

Alter table [dbo]. [AccountInfo]  add  Unique (idcard  ASC) 
  go 
 
 /*3. Create constraints using T-SQL statements. The Bank card Information table (cardinfo) field value can only be 19 bits long */
 
  alter  table [dbo].[ Cardinfo]  add  constraint ck_cardid  check ( len (CardID) = ) 
  go 
 
 /* 4. Use T-SQL statements to make the transaction type (transtype) field of the Transaction information table (transinfo) only select ' Deposit ' and ' withdraw ',     bank card information table (Cardinfo) deposit type (savetype) field can only select ' Periodic ' and ' current ' */
 
  alter  table [dbo]. Transinfo  add  constraint ck_transtype  check (transtype =  ' deposit '  or transtype =  ' withdrawal ') 
  go 
 
  alter  table [dbo]. Cardinfo  add  constraint ck_savetype  check (savetype =  ' periodic '  or Savetype =  ' demand ') 
 Span>go                

2. Inserting basic data

--Insert three account information

InsertInto AccountInfoVALUES (' Monkey King ',' 422322001502110017 ', ' 027-88888888 ',  ' Huaguoshan '),         ( ' Tang priest ',  ' 420322001902140019 ',  ' 027-85368962 ',  ' Datang '),         
           ( ' Sand monk ',  ' 410340001572144714 ',  ' 13295654665 ',  ' through Tianhe ') 
  insert  into Cardinfo  values         ( ' 1027 3526 1536 1135 ',  ' 888888 ',  1,  ' regular ',  default,  $,  500,  ' no '),         ( ' 1029 3326 1536 1235 ',  ' 888888 ',  2,  ' current ',  default,< span>1500, ,  ' no '),         ( ' 1324 3626 7532 1935 ',  ' 888888 ',  1,  ' current ',  default,  4500,  4500,  ' no ')           

System functions

Database system-defined functions, which are built-in functions.

Built-in function calls

FROM <表名>;

function Column alias	Description
Aggregation functions	The action performed is to combine multiple values into a single value. For example, COUNT, SUM, MIN, and Max.
Configuration functions	is a scalar function that returns information about the configuration settings.
Cryptographic functions	Supports encryption, decryption, digital signature, and digital signature verification.
Cursor functions	Returns information about the state of the cursor.
Date and Time functions	You can change the value of a date and time.
Mathematical functions	Perform trigonometric, geometric, and other numeric operations.
Meta-data functions	Returns property information for database and database objects.
Ranking function	is a non-deterministic function that returns the rank value of each row in a partition.
Rowset functions	Returns a rowset that can be used in the location of a table reference in a Transact-SQL statement.
Security functions	Returns information about users and roles.
String functions	You can change the values of char, varchar, nchar, nvarchar, binary, and varbinary.
System functions	Manipulate or report on various options and objects at the system level.
System statistics functions	Returns information about the performance of SQL Server.
Text and image functions	You can change the value of text and image.

If the specific function is not clear, please search by yourself, this article focuses on user-defined functions.

User-defined Functions

In addition to the system-provided functions, users can customize functions according to their own requirements;
User-defined function, as the name implies, is a number of user-defined functions;
User-defined functions are divided into two categories: table-valued functions and scalar-valued functions;
There are two types of table-valued functions: inline table-valued functions and multi-statement table-valued functions.

Custom Function calls

SELECT 字段列表/* FROM [函数所有者.]<函数名称>([参数列表]);

In a custom function, the 函数所有者 general default is dbo;
In the custom function, 标量值函数 must add 函数所有者 , otherwise will error: prompt function non-built function;

Table-Valued functions

A table-valued function returns a table type that is equivalent to a virtual table stored in memory.

Inline table-valued function syntax

CREATE FUNCTION [函数所有者.]<函数名称> (        -- 添加函数所需的参数，可以没有参数    [<@param1> <参数类型>]    [,<@param1> <参数类型>]…)
RETURNS TABLE 
AS

RETURN 
(    
      -- 查询返回的SQL语句    SELECT查询语句)

Example

/** Create an inline table-valued function to query the account holder's personal information in total transactions greater than 1W * *

Createfunction Getcustinfo ()
Returns @CustInfoTable--Return table type

(
--Account ID CustIDInt
--Account name CustNamevarchar20)NotNull
--ID Number Idcardvarchar18),
--Telephone telephonevarchar13)NotNull
-Address varchar ( ' address unknown ')
As

Begin -Assign values to table tables insert INTO @CustInfo
Select custid,custname,idcard,telephone,address from accountinfo where CustID in (select CustID fro M cardinfo
where CardID in (select CardID from transinfo group by Cardid,transid,transtype,transmoney, TransDate having sum (Transmoney) >10000))
return

End

Go

--Call an inline table-valued function

SELECT * from getcustinfo ()
go                   

Multi-statement table-valued function syntax

CREATE FUNCTION [函数所有者.]<函数名称> (        -- 添加函数所需的参数，可以没有参数    [<@param1> <参数类型>]    [,<@param1> <参数类型>]…)RETURNS <@定义的表名> TABLE (    -- 添加返回数据表的列    <列名1> <数据类型1>,    <列名2> <数据类型2>,    ……    <列名n> <数据类型n>])AS

BEGIN    --为<@定义的表名>赋值的SQL语句    RETURN 
END

Example

/** Create a multi-statement table-valued function that can query the name of the user who has the transaction within one months, contact number, ID, bank card number and account balance */

Createfunction Getcustinfomonth ()
Returns @CustInfoTable--Return table type

(
--Account name CustNamevarchar20)NotNull
--Telephone telephonevarchar13)NotNull
--ID Number Idcardvarchar18),
--bank card number CardIDvarchar19)NotNull
--Available Balance Leftmoney moneyNotNullCheck (leftmoney>1))AsBegin--Assigning Values to table tablesInsertInto @CustInfo Select A.custname, A.telephone, A.idcard,T.cardid,C.leftmoney
From AccountInfoAs AInner join Cardinfo  as  C  on a.custid =  c.custid          Inner  Join Transinfo  as  T  on  c.cardid =  t.cardid         
       & nbsp      where  exists (SELECT *  from transinfo         
       & nbsp   &NBSP  group  by Cardid,transdate,transid,transtype,transmoney         
              having  DateDiff ( month,transdate,  GETDATE ()) =  0)   &NBSP 
              return 
 
  end 
 
  go 
 
 -- Call a multi-statement table-valued Function 
 
 Select *  from Dbo.getcustinfo () 
  go        

Scalar-valued functions

Returns a scalar value

Grammar

CREATE FUNCTION [函数所有者.]<函数名称> (    -- 添加函数所需的参数，可以没有参数    [<@param1> <参数类型>]    [,<@param1> <参数类型>]…)
RETURNS <函数返回数据类型>
AS

BEGIN    -- 定义返回数据变量    DECLARE @变量名 数据类型    
      -- 通过SQL语句为返回变量赋值    SELECT @变量名 = SQL语句    
      -- 返回结果    RETURN @变量名
END

Example

  /*7. Create a scalar value function that obtains the number of transactions for the card based on the user's incoming card number */
 
   Create  function Gettranscount (     @CardID  varchar ()   --Parameter bank card number) 
 
  returns  int   -- Returns the int type 
 
  as 
 
  begin      declare @  count  int     
      select @  count =  count (*)  from transinfo  where CardID = @CardID     
 &nbs P   &NBSP  return @  count 
 
  end 
 
  go 
 
 --Call scalar value function 
 
 --' Scalar value function ' must add ' function owner ', otherwise error: Prompt function non-built function 
 
 Select Dbo.gettranscount ( ' 1027 3526 1536 1135 ')  as number of times 
  go                 

Prompt again:
In a custom function, the 函数所有者 general default is dbo;
In the custom function, 标量值函数 must add 函数所有者 , otherwise will error: prompt function non-built function;

Custom Function Delete

-- 自定义函数删除语法

DROP FUNCTION [函数所有者.]<函数名称>;

-- 示例、

-- 删除查询交易总额大于1W的开户人个人信息内联表值函数

DROP FUNCTION getCustInfo

relational database SQL programmability functions (user-defined functions)