William Jiang

JavaScript,PHP,Node,Perl,LAMP Web Developer – http://williamjxj.com; https://github.com/williamjxj?tab=repositories

MySQL: stored procedure and function

While writing MySQL functions, I found a helpful article from web and extracted it, put here for reference.

I always find it amusing that when people talk about database logic they are very focused on stored procedures and its almost like nothing else exists. Makes me wonder if these people have ever worked with modern databases. Stored procedures are one of the oldest methods of encapsulating database logic, but they are not the only method available. Many relational databases nowadays have views, constraints, referential integrity with cascading update, delete, stored functions, triggers and the like. These are extremely powerful tools when used appropriately.

In the next couple of sections we’ll cover stored procedures and these other kinds of database objects and detail the strengths and weaknesses of each for encapsulating logic. We will give a rating of 0-5 for each feature 0 meaning the feature is non-existent, 5 meaning this object is one of the best suited objects for implementing this kind of task.

Stored Procedures

Stored procedures are one of numerous mechanisms of encapsulating database logic in the database. They are similar to regular programming language procedures in that they take arguments, do something, and sometimes return results and sometimes even change the values of the arguments they take when arguments are declared as output parameters. You will find that they are very similar to stored functions in that they can return data; however stored procedures can not be used in queries. Since stored procedures have the mechanism of taking arguments declared as OUTPUT they can in theory return more than one output.

Feature Rating
Works in various kinds of databases 3 (many  databases such as DB II, Oracle, SQL Server, MySQL 5, PostGreSQL,
FireBird support them).  There are also a lot that don’t e.g.
MySQL < 5.0, MS Access (although parameterized queries serve a similar role)
Can be called by multiple applications and interfaces 4 (generally they can be called, but the use of OUTPUT arguments  is not
always usable)
Can take an undefined number of arguments 2 (note most databases allow to define optional arguments, but this can
become very unwieldy to maintain if there are a lot  because you end up
duplicating logic even within the stored procedure so is generally avoided)
Reusability within the database 3 (you can not reuse them in views, rarely in stored functions and other
stored procedures unless the stored procedure using it does not require a
return value or result query).  This varies slightly from DBMS to DBMS.
Can be used to change data in a table without giving rights to a user to
change table directly
4  In general true for most DBMSs
that support them.
Can return varying number of fields given different arguments. 3 –again in theory it can, but very hard to
maintain since you would often be duplicating logic to say return one field
in one situation and other set of fields in another situation or update a
field when the field is passed in as an argument. Note that in many databases such as for example SQL Server and Oracle, one can return multiple result sets with a stored procedure, but the receiving end needs to be able to do a next result set call and know the sequence in which the result sets are being sent.
Long stretches of SQL easy to read and maintain 5 (one of the great strengths of stored procedures is that you can have long transactions of sql statements and conditional loops which can be all committed at once or rolled back as a unit. This also saves on network traffic.

Stored Functions

Stored Functions are very similar to stored procedures except in 3 major
ways.

  1. Unlike
    stored procedures, they can be used in views, stored procedures, and other
    stored functions.
  2. In
    many databases they are prohibited from changing
    data or have ddl/dml limitations.  Note for databases such as PostGreSQL
    this is not true since the line between a stored function and a stored
    procedure is very greyed
  3. They
    generally can not take output arguments (placeholders) that are then passed back out with changed values.
Feature Rating
Works in various kinds of databases 3 (many  databases such as DB II, Oracle, SQL Server support them, MySQL 5, PostGreSQL).  There are also a lot that don’t e.g.
MySQL < 5.0, MS Access
Can be called by multiple applications and interfaces 4 (generally they can be called, but the use of OUTPUT arguments  is not
always usable)
Can take an undefined number of arguments 2 (note most databases allow to define optional arguments, but this can
become very unwieldy to maintain if there are a lot  because you end up
duplicating logic even within the stored function so is generally avoided)
Reusability within the database 5 (you can reuse them in views, in other stored functions and
stored procedures).  This varies slightly from DBMS to DBMS.
Can be used to change data in a table without giving rights to a user to
change table directly
3  Many databases do not allow changing of data in stored functions except temp table data, but those that do in general support this.
Can return varying number of fields given different arguments. 4 –For databases such as SQL Server, PostgreSQL, DB 2, Oracle that allow return tables and sets, you can selectively pick fields you want from within a query. So although the function always outputs the same number of fields, you can selectively use only some similar to what you can do with views. This is not true for scalar functions (MySQL 5.1- only supports scalar functions).
Long stretches of SQL easy to read 5 – yes – you can do fairly intensive multi-line processing which in the end returns one value or table to the user.

Views

Views are one of the greatest things invented since sliced bread.
The main beauty of a view is that it can be used like a table in most situations, but unlike a table, it can encapsulate very complex calculations and commonly used joins. It can also use pretty much any object in the db except for stored procedures. Views are most useful when you always need to join the same set of tables say an Order with an Order Detail to get summary calculation fields etc.

Feature Rating
Works in various kinds of databases 4 (many  databases such as DB II, Oracle, SQL Server support them, MySQL 5, PostGreSQL, SQLite, MSAccess (calls it a query)).  There are also some that don’t e.g.
MySQL < 5.0
Can be called by multiple applications and interfaces 5 (generally they can be called anywhere where you can call a table which is pretty much everywhere)
Can take an undefined number of arguments 5 (you can select subsets of columns and rows from a view just like you can from a table)
Reusability within the database 5 (you can reuse them in other views, in stored functions and
stored procedures).
Can be used to change data in a table without giving rights to a user to
change table directly.
3  In many databases Views are read-only and complex views are rarely updateable. Note that for example some databases such as Oracle,PostgreSQL, MS SQL Server , SQLite
will allow you to update even a complex view by using an instead of trigger or rule against the view. MySQL 5, MS SQL Server and some others automatically make one table views updateable. MS Access has a fairly sophisticated update algorithm for queries in that it automatically makes one table and even multi-table queries updateable and allows deletes if you define primary keys appropriately. Also depending on the field a column comes from it will update that and also automatically create child records in child tables if you try to update a child column when there is no record.
Can return varying number of fields given different arguments. 4 – (you can select subsets of columns and rows from a view just like you can from a table). However you can’t change the underlying structure e.g. return records from a different set of tables like you can with a stored procedure or function.
Long stretches of SQL easy to read. 3 (A view can often be defined with an administrative designer or using a color coded sql editor so is fairly easy to read) – in general though it is limited to only one select statement or unioned select statements.

Triggers And Rules

Triggers are objects generally tied to a table or view that run code based on certain events such as inserting data, before inserting data, updating/deleting data and before these events happen.

Triggers can be very great things and very dangerous things. Dangerous in the sense that they are tricky to debug, but powerful because no update to a table with a trigger can easily escape the trigger.

They are useful for making sure certain events always happen when data is inserted or updated – e.g. set complex default values of fields, inserting logging records into other tables.

Triggers are especially useful for one particular situation and that is for implementing instead of logic. For example as we said earlier, many views involving more than one table are not updateable. However in DBMS such as PostgreSQL, you can define a rule on a view that occurs when someone tries to update or insert into the view and will occur instead of the insert. The rule can be fairly complex and can layout how the tables should be updated in such a situation. MS SQL Server and SQLite let you do something similar with INSTEAD OF triggers.
Note the term Rule is a little confusing in DBMS because they mean quite different things. In Microsoft SQL Server for example a Rule is an obsolete construct that was used to define constraints on tables. In PostgreSQL a Rule is very similar to a trigger except that it does not get triggered per row event and is defined without need of a handling function.

Feature Rating
Works in various kinds of databases 2 (many  databases such as DB II, Oracle, SQL Server support them, MySQL 5, PostGreSQL,).  There are lots that don’t e.g.
MySQL < 5.0, MySQL 5 limited, MS Access
Can be called by multiple applications and interfaces 5 (it just happens behind the scenes. No application can escape them)
Can take an undefined number of arguments 0 ( strictly for updating data and against a table or view )
Reusability within the database 0 – No
Can be used to change data in a table without giving rights to a user to
change table directly.
4  In general yes for databases that support them
Can return varying number of fields given different arguments. 0 –Triggers are strictly for updating data
Long stretches of SQL easy to read. A trigger can often be defined with an administrative designer or using a color coded sql editor so is fairly easy to read 5

Foreign Key Constraints, Primary Key Constraints, Referential Integrity, Cascade Update/Delete

No true database should be without Constraints, Referential Integrity, and Cascade Update/Delete. You can define them with SQL DDL statements or using a relational designer. The use of these tools are limited but the purpose they serve can not be easily replicated with other database objects.
These tools serve 2 main purposes.

  • Provide a declarative model of the database and how the data is related to each other. A self-documenting tool.
  • Insure that you don’t mistakenly do something stupid in your coding to violate your model. If your model is sound, errors in your code signal a flaw in your programming logic. If you get errors in your programming logic, then verify that your model is sound.
Feature Rating
Works in various kinds of databases 3 (many  databases such as DB II, Oracle, SQL Server support them, MySQL 4+ (4 is limited, 3 very limited and varies depending on storage engine (InnoDB vs MyISAM)), PostGreSQL, MS Access).  There are lots that don’t e.g.
MySQL < 5.0
Can be called by multiple applications and interfaces 5 (it just happens behind the scenes. No application can escape them)
Can take an undefined number of arguments 0 (they take no arguments )
Reusability within the database 5 – Yes – no matter where your updates, inserts occur, they can not escape the iron-fist of the Key Constraints and Cascade Update/Delete rules next to disabling them.
Can be used to change data in a table without giving rights to a user to
change table directly.
4  Really only applies to cascade update/delete rules Yes – cascade update/delete rules are a special kind of trigger so to speak that kick in when a potential referential integrity constraint can be violated. Example if you update a key field, a cascade update on the foreign keys will force an update on the foreign key field to correct the situation so you don’t end up with orphan data.
Can return varying number of fields given different arguments. 0 –not relevant
Long stretches of SQL easy to read. A trigger can often be defined with an administrative designer or using a color coded sql editor so is fairly easy to read 0 – not relevant

Dynamically Generated SQL

In this section we discuss the pros and cons of using dynamically generated sql vs. only using stored procedures, view, etc.

Feature Rating
Works in various kinds of databases 4 (guaranteed to work for any database, although the syntax may vary from database.)
Can be called by multiple applications and interfaces 3 (Will only work in the application where the dynamic sql is defined or if the application is wrapped as a shared library. The use of shared library is often limited as to where you can use it. However your logic can be applied to multiple databases without needing to alter the db by adding stored procs etc. if you are always using the same app to access these databases.)
Can take an undefined number of arguments 5 (you can select subsets of columns and rows from tables, change tables you are reading from etc.)
Reusability within the database 2 (non-existent except in some very rare situations and databases that allow you to load shared libraries).
Can be used to change data in a table without giving rights to a user to
change table directly.
4  You can have the application control the security so in that sense the application only needs to have rights
Can return varying number of fields given different arguments. 5 – (you can select subsets of columns and rows, do complex joins ). You can change the underlying structure e.g. return records from a different set of tables.
Long stretches of SQL easy to read. 2 – SQL often sits interspersed with other programming logic which makes it somewhat difficult to read depending on the application language and also difficult to write complex transactional sql.

What about Security?

In the above criteria, we didn’t put Security in as a feature/benefit even though many people will argue that dynamic SQL and the like are not secure.
This actually varies depending on how you implement Dynamic SQL, e.g. if you are using an O/R Mapper or built-in dataadapters (supported in .NET),prepared statements, or are very careful to sanitize inputs you are using in your sql then
your Dynamic SQL will probably be just as secure or sometimes more so than using stored procedures since some people are of the mindset that stored procedures are a magic bullet for protecting against SQL Injection and other attacks (such as script injection) regardless of how they are implemented and called and thus neglect other necessary precautions.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: