A choice before chatty SQL and function: Who is the more efficient SQL?

Reprint please indicate the source: reprint Please specify source:Jiq Technical Blog

In particular, the ORACLE11G database is discussed here, not necessarily for other databases such as SQL Server,mysql.

1, problem description

The current system has two tables, one is the product table, and one is the attachment table.

PRODUCT (Productid,productname,productprice,picture)

ATTACHMENT (ATTACHMENTID,FILEPATH,BUSINESSTYPE,BUSINESSID)

The Product table contains the product ID, product name, product price, image and other fields, attachment table contains attachment ID, attachment full path, the attachment belongs to the module number (such as the product module is 105), the attachment belongs to the record ID (here is the Product ID).

The requirement is to display a list of all products in the query all product interface, and a "if video" field is required after each record to indicate whether the current product record contains video. In addition, in the above query criteria area can be in accordance with whether or not video product query.

2, solve the idea

Concepts related to left links, regular expressions, functions, de-weight, rownum, etc.

Mode 1: A chatty SQL statement

When I do this, the first thing I want to do is to use a more complex SQL statement to spell out an SQL statement to find out all the product records that are going to be displayed.

So first think of the left link, because the need to ensure that all the records on the left side of the product table can be displayed, there is no omission, so write an SQL statement:

Select T.*,a.businesstypefrom PRODUCT T left join ATTACHMENT Aont.productid=a.businessid and Regexp_like (A.filepath, ' ^. *. (Rmvb|avi|mtv|3gp|flv|mpeg|mov) ')

The Product table and attachment table are left-linked, and the attachment table on the right is connected to the left table record only if the Businessid equals the left product ID, and the attachment is a video file.

Note: The Regexp_like function is used to judge the regular expression in the Where condition.

But when a product has two video files, the problem comes out, such as the Product ID 331 has two video attachments, then the attachment table has two attachment records to meet:

T.productid=a.businessid and Regexp_like (A.filepath, ' ^.*. ( Rmvb|avi|mtv|3gp|flv|mpeg|mov) ')

Where condition, then the two attachment records will be connected to the left table, because the left link is not said to left table as the benchmark, the left table has how many bars, connected out how many, in this case the number of connected records will be more than the original left table record number. This leads to the result of the connection:

PRODUCTID ... Businessid Businesstype

331 331 105

331 331 105

As you may have thought, we can go over the process and the following statement appears:

SELECT distinct T.*,a.businesstypefrom PRODUCT T left join ATTACHMENT Aont.productid=a.businessid and Regexp_like (A. FILEPATH, ' ^.*. (Rmvb|avi|mtv|3gp|flv|mpeg|mov) ')

Because it looks like the duplicate records above are identical, use the DISTINCT keyword to record and redo the records.

Unfortunately, this statement compiled an error, I started to think that a product has more than two video, the concatenated duplicate record is identical.

Unfortunately in my case, it is not the same, because there is a picture field in the Product table, is a BLOB type, storing the image of binary data, not allowed to use the DISTINCT keyword to go heavy.

The following methods can be used to remove the weight:

SELECT * FROM (SELECT DISTINCT T.*,a.businesstype,row_number () up (PARTITION by T.productid Order by T.productname) Rkfro M PRODUCT T left join ATTACHMENT Aont.productid=a.businessid and Regexp_like (A.filepath, ' ^.*. ( Rmvb|avi|mtv|3gp|flv|mpeg|mov) ') where rk=1

That is, based on repeated ProductID, only the first record is taken as long as the repetition.

Just such a satisfying condition of SQL has been constructed, the result of the query is the product table all records have and listed only once, each product record followed by a field businesstype, if this field has a value, identify this product record has a video attachment, if no value identifies no video attachment.

As for the query, with or without video as the query condition, only need to be in the above SQL outside a layer, plus where businesstype= ' 105 ' This condition can be.

Mode 2: Solve with advanced features such as functions

By communicating the above approach to more experienced colleagues in the project team, it is not graceful enough to find yourself in a more elegant way, and there are plenty of elegant ways to solve this problem.

My solution above is first too complex, followed by the record after the connection Businesstype field has no value, is not 105来 to determine whether the product has video.

More often than not, we may not just need to preserve the value of a field in the right table after the connection, but more likely to be a conversion of values for some columns!!!

For example, we would like to add a column after the product table, the value of this column if it is 0 ID no video attachment, if it is 1 identifies a video attachment. Another scene we want to add a column after the product table, the value of this column indicates the number of attachments that the product has, where the information is definitely from the attachment table, but can be done by the link of two tables?

This time we are going to use functions to implement:

SELECT DISTINCT t.*,func_hasattachment (t.productid) from PRODUCT T

look at this way more elegant, more extensibility, you can add more functions, you want to know what information can be, theoretical functions can be achieved.

Here's how this function returns an attachment based on the value of the product ID (0-no video attachment, 1-video attachment)

Create OR REPLACE function func_hasattachment (ProductID in decimal) return numberisnum number;result number; Beginresult:=0;select count (attachmentid) into Num from ATTACHMENT where businesstype= ' "" and Businessid=productid and Regexp_like (A.filepath, ' ^.*. Rmvb|avi|mtv|3gp|flv|mpeg|mov), if (num>0) then result:=1;else result:=0;endif;return result; END;

You see we only need to pass in the product ID, according to this ID in the attachment table to do a little to get out of this product has no video accessories.

It seems that we consider a database operation, and feel a little bit of trouble, we should try to jump out of the "chatty SQL" this vicious circle, and try to consider the process/function, triggers, views and other advanced features to help solve!

The following is an introduction to the function:

CREATE [OR REPLACE] FUNCTION function_name (arg1 [{in | Out | In off}] type1 [DEFAULT value1], [arg2 [{in | Out | In Out}] type2 [DEFAULT value1]], ... [Argn [{in | Out | In Out}] Typen [DEFAULT Valuen]] [AUTHID definer | Current_User]return Return_type is  |    as < type. The declaration part of the variable > BEGIN    executes part    RETURN expressionexception    exception handling section end Function_name;

For more details, refer to: http://blog.csdn.net/jiyiqinlovexx/article/details/13417455

A choice before chatty SQL and function: Who is the more efficient SQL?