Mysql – Stored Procedure – MySQL

MySQLstored-procedures

I am developing a database for an application. I have to create stored procedure to tables, so that programmers can simply call the stored procedure to implement the functions. I have a doubt to implement the following logic,

For example:
Business Type Table

Stored Procedure

Business Type Stored Procedure

As per my knowledge I thought, this procedure will work fine. While I call the procedure name as follows,

Call businesstype('insert', 1, 'Online Marketing', datetime(),'raj', datetime(), 'raj', 1);
Every time needs to call this procedure with all its parameters, but developers need to call the procedure with required fields, for example they need to call only the ID (PK) to perform delete function, but it required some values (NULL) for remaining parameters.
How to perform this action? I am just a starter, I am expecting some best practices to implement this, also I need to know whether the given table design and procedure will meet the industry expectations? is it good to implement in this way?

Thanks in advance.

Best Answer

In my opinion, a procedure should do one thing and one thing only. The logic should be performed in the application code. Secondly, a procedure should not return a result set. It should do work and then possibly return a status e.g. success, failure or something similar. Also, the name of your procedure is meaningless. Choose a descriptive name.

So, break it up into three procedures (insert, update and delete). Move the select statement into a view rather than a procedure. Let your programmers add the logic in their code to determine which one to call. Make sure you put index on the table.

Never do a select *. Only select the columns you need. It may be that you add columns later on but you will still SELECT *. Your SELECT * will then store all (incl unnecessary columns) columns in memory and, in worst case, run out of memory and be swapping to disk.

Related Solutions

Finding parameter names for binding stored procedure parameters

I did a little bit more research and found the answer to the problem.

Even though the first parameter for DBMS_OUTPUT.PUT_LINE is listed as item in the 10g documentation for DBMS_OUTPUT. The actual name for the parameter is A. You need to bind the name using DBMS_OUTPUT.PUT_LINE(A => 'random text').

The actual parameter names for stored procedures can be discovered using the following query:

SELECT 
    argument_name, 
    in_out, 
    data_type 
FROM 
    all_arguments 
WHERE 
    owner = 'SYS' --owner for said procedure
    AND package_name = 'DBMS_OUTPUT' --if procedure is in a package that name, null otherwise
    AND object_name = 'PUT_LINE' --procedure name

The values SYS, DBMS_OUTPUT, and PUT_LINE can be replaced with the values of the the stored procedure that you would like to look up.

MySQL overhead of calling a Stored Procedure

Percona wrote up a nice article MySQL Prepared Statements explaining pros and cons

So there are good reasons to use prepared statements:

Save on query parsing

Save on data conversion and copying

Avoid SQL Injection

Save memory on handling blobs

There are also drawbacks and chewats of using prepared statements:

Query cache does not work

Extra server round trip required if statement used only once

Not all statements can be prepared. So you can’t use prepared API exclusively you’ll need to fall back to normal API for some statements

Newer and sometimes buggy code. I had a lot of problems with PHP prepared statements. It is getting better but still it is less mature than standard API

You can’t use placeholders in place of all identifiers. For example you can’t use them for table name. In certain version it even does not work for LIMIT boundaries

Inconvenient list handling. Unlike in for example PEAR emulated prepared statements there is no nice way to pass list of values to IN

Harder tracing. Logs were now fixed to include full statement text not only “Execute” but in SHOW INNODB STATUS you would still see statements without actual values – quite inconvenient for analyses

Since what you are experiencing is the expected behavior, you should not use Prepare Statements. You are making many calls to execute a single command. If you simply ran this

stmt = conn.createStatement();
rs = stmt.executeQuery("call sp_one('a','b')");

the complete parsing of the SQL, preparing for execution, and the parameters already being passed in through the SQL statement are all done on the server side with one call.

Please read that Percona blog for more details.

Best Answer

Related Solutions

Finding parameter names for binding stored procedure parameters

MySQL overhead of calling a Stored Procedure

Related Question