Recursive usage of Database User-Defined Functions
First look
There is such a tree structure table,
For example: The C18 digital camera is under the C12 digital product category
While C12 digital products are in the C2 IT product category again!
C2 IT products are located at 000 (under the root node)
That is, the classification is C2. It product-C12 digital product-18 digital camera
Now, if there is such a need, use the sqlserver platform to obtain the classid of all its parent nodes for the given classid, and connect it! For example, the C18 result is c2_c12_c6 in the following table.
We can see that The place field of the digital camera is c2_c12_c6, and the place field is the identification field, which has a certain effect on requirements!
In this way, when we add a category, in addition to specifying basic information, we also need to specify its place to successfully Add a record!
At that time, there were many ways to get the place for the newly added record, but if the current requirement is to use the SQL custom recursive function to complete the Function !
How to implement it? Given the classid (this data can be obtained through the @ identity global variable obtained at the time of addition) to get the place!
See the following SQL statement /**/ /*
-------------------------------------------
Project: cmwin product library of the company
Name: f_contactallparentplacebyclassid
Function: Uses classid to generate its place prefix. Except your place
Link all its parent classid
Related table: classtree_update
Written by: Feng Yan
Time:
-------------------------------------------
*/
Create Function F_contactallparentplacebyclassid
(
@ Classid Varchar ( 15 ) -- Parameters
)
Returns Varchar ( 500 )
As
Begin
Declare @ Parentclassid Varchar ( 15 ) -- Variable parent ID
Declare @ Place Varchar ( 500 ) -- Variable place
Set @ Place = ''
-- First, obtain the parent ID and parentclassid Based on the passed classid.
Select @ Parentclassid = Parentclassid From Classtree_update
Where Classid = @ Classid
/**/ /*At this point, we get the parent ID, for example, C18. We can get the C12, but the problem is not complete yet,
C12 itself also has a parent node, that is, C2. We also need to extract C2. The parent node of C2 IS 000, that is, the root node.
So far.
Therefore, the problem can be seen that this is a recursive process, and the parent ID is obtained based on the specified classid. Again
Continue to obtain the parent ID of the parent id based on the parent ID, recursively until the parent ID is 000!
Therefore, the SQL function recursion is implemented as follows.
*/
If ( @ Parentclassid <> ' 000 ' ) -- If it is not the root node
Begin
-- Then, pass the @ parentclassid parent ID as the classid for self-calling.
Set @ Place = DBO. f_contactallparentplacebyclassid ( @ Parentclassid ) + @ Parentclassid + ' _ '
End
Return @ Place
End
Details already exist! Run to view
Note that some SQL versions report an error when running the preceding SQL statement in DBO. f_contactallparentplacebyclassid (@ parentclassid. The reason is that DBO is created. f_contactallparentplacebyclassid function, which has not been created yet, And DBO is called here. f_contactallparentplacebyclassid (@ parentclassid). Therefore, no such object is displayed. The solution is to remove DBO first. f_contactallparentplacebyclassid (@ parentclassid) is created, and then alter and modify it! I met once!
Running Effect
Given 'c18 ', you can get the place of all its parent nodes and use "_" to link them. It is very close to the requirement, but after you connect C18 to it, the requirement is met, the C18 is also known!The classid field is incremental. You can use the @ identity variable to obtain the value when adding a category.! After the connection, It is c2_c12_c6 _
At this point, the place identifier of the given classid is captured through the SQL user-defined function!
Summary:Here we will only demonstrate the usage of SQL udfs and implement a simple recursion. Of course, you do not have to use a function to implement this function. I am here only to demonstrate function usage! Sometimes it is very convenient to use SQL functions.!
