Especially if you're moving things from one table to another i. There are drawbacks of course in that you'll have to keep a separate log of changes to them you could in theory make a table that just holds changes to the stored procedures that the DBA's update.
If you have many different applications interfacing with the database, especially if say you have a desktop program written in C and a web program in PHP, it might be more beneficial to have some of your procedures stored in the database as they are platform independent.
Try to update ,, records on a live system from a framework, and let me know how it goes. For small apps, SPs are not a must, but for large serious systems, they are a real asset. Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. Asked 10 years, 5 months ago. Active 2 years, 6 months ago. Viewed 27k times. Improve this question. Add a comment. Active Oldest Votes. Sometimes different versions of the same database are incompatible - I've seen it!
Improve this answer. It is an illusion to believe it is "good design" or it will "make your code cleaner". Although it seems like a good idea, it is not. If you want to encapsulate that action, create a PHP function that does it and call that. Believe me, you will regret it if you go down the stored procedure road if you don't absolutely need to. Two years from the begining of the project. Finally, I have realised the bad decision we made encapsulating a little data base functionallity in the store procedures.
Never again It's not as bad to change it becouse is very little, but in a future project the database strictly will save the data. I mark now this answer as the good one for future readers. I can say that after 3 years I'm even more in love with one of my SP based systems.
I'd add some more reasons not to use stored procedures: The are not easily deployed using standard deployment tools and are als not easily managed by most version control systems.
It's good for small apps and fast development, yet DB-independent persistence frameworks only offer a small subset of the features the database offer. You have zero flexibility to tune the queries. Show 7 more comments. Unlike programming code, they: render SQL injection attacks almost impossible unless you are are constructing and executing dynamic SQL from within your procedures require far less data to be sent over the IPC as part of the callout enable the database to far better cache plans and result sets this is admittedly not so effective with MySQL due to its internal caching structures are easily testable in isolation i.
Pacerier DB call in difference. Not "far less" in my mind where we use broadband. Yes, I mean the callout, not what's returned: typically you would issue a call like "call myProc x,y,z " which can be far less data than an intricate SQL query. And depending on your mileage that can add up. No, SQL injection attacks are not impossible, because in practice developers often use dynamically prepared queries. Parameterised queries can be used without Sprocs. Trivially perhaps, but hardly any difference in practice.
MySQL does not precompile or cache plans in sprocs. YES they are testable in isolation, but if you factored your queries into a testable unit on the client, they would be too.
Other reasons -to- use stored procedures include strong typing, and input size limitations on input variables. If you have an integer and a date parameter on a procedure, and someone tries to inject SQL using either input variable it will fail out of hand. As well if your web server gets compromised in some way, the attacker can't run ad hoc queries if the web server db user only has execute permissions.
There are some pretty lame arguments in this page against stored procs, the worst of which is "because it's hard". Toss performance out the window and there still good reasons to use them. Two things, MarkR. Davek said "almost impossible". The "trivial" difference in the amount of data across the wire depends on size of the queries and amount of data returned. Show 2 more comments. This is pretty odd but I wanted to signal it anyway Task force: yes, creating efficient SQL SPs requires some specific resources, sometimes more expensive.
Quickly testable: one command line under your shell and your code is tested. Sebas Sebas Regarding "Independance from the client: if tomorrow you'd like to switch from php to something else, no problem. Stored procedures are the poor man's microservice architecture. Having multiple applications directly access the same database is the real root problem, equally solved by stored procedures or a middle tier access layer.
If you accept that, then just ask yourself which you'd rather build and maintain - and I'll refer you back to the accepted answer. Abhay Abhay 6, 2 2 gold badges 20 20 silver badges 17 17 bronze badges. I disagree: 1. You can keep your queries organised without stored procs.
You can perform a query batch without stored procedures; 3. Stored procedures are not pre-compiled in MySQL. You can performa a query batch without stored procedures again.
MarkR: I agree but I didn't mean to say that there are no ways other than stored procedure to organize your queries or run batch queries. I think I didn't know that MySQL stored procedures are not pre-compiled, that's weird but thanks for that bit.
Abhay, Shouldn't you remove that misleading information regarding the precompilation then? What you have in order to get it. Time for get it. No secrets about that. Change Management. Cost How much will cost to implement that solution using one or another strategy?
The restrictions for stored procedures also apply to the DO clause of Event Scheduler event definitions. There are also some restrictions specific to events.
Stored routines cannot contain arbitrary SQL statements. The following statements are not permitted:. Thus, stored functions and triggers cannot use dynamic SQL where you construct statements as strings and then execute them.
Generally, statements not permitted in SQL prepared statements are also not permitted in stored programs. For a list of statements supported as prepared statements, see Prepared Statements. Because local variables are in scope only during stored program execution, references to them are not permitted in prepared statements created within a stored program.
Prepared statement scope is the current session, not the stored program, so the statement could be executed after the program ends, at which point the variables would no longer be in scope. This restriction also applies to stored procedure and function parameters. END block. The following additional statements or operations are not permitted within stored functions. They are permitted within stored procedures, except stored procedures that are invoked from within a stored function or trigger.
For example, if you use FLUSH in a stored procedure, that stored procedure cannot be called from a stored function or trigger. Statements that perform explicit or implicit commit or rollback. Support for these statements is not required by the SQL standard, which states that each DBMS vendor may decide whether to permit them.
Statements that return a result set. FLUSH statements. A stored function or trigger cannot modify a table that is already being used for reading or writing by the statement that invoked the function or trigger.
READ statements that invoke stored functions can cause replication errors and are disallowed. When using row-based replication, triggers on the replica are not activated by statements originating on the source. The triggers on the replica are activated when using statement-based replication. For more information, see Replication and Triggers. Triggers are not permitted on tables in the mysql database.
Those tables are actually views and triggers are not permitted on views. The trigger cache does not detect when metadata of the underlying objects has changed. If a trigger uses a table and the table has changed since the trigger was loaded into the cache, the trigger operates using the outdated metadata. The same identifier might be used for a routine parameter, a local variable, and a table column. Also, the same local variable name can be used in nested blocks. For example:.
In such cases, the identifier is ambiguous and the following precedence rules apply:. A local variable takes precedence over a routine parameter or table column. To store the value returned by the procedure, pass a session variable named PGRatingMovies. Suppose we want to get the total count of movies based on the rating. It contains the list of the stored procedure and stored functions created on the database.
To view the list of the stored procedure created in a sakila database, run the following query. Moreover, it also provides the owner , created date, security type, and SQL data access to the stored procedures.
To drop the stored procedure, you can use the drop procedure command. The syntax is following. In the syntax, the name of the stored procedure must be followed by the Drop Procedure keyword. When you try to drop the procedure that does not exist on a database, the query shows an error:. Query OK, 0 rows affected, 1 warning 0. You can use the MySQL workbench wizard to drop the procedure. A dialog box opens. You can choose to review the procedure before dropping it, or you can drop it without reviewing it.
It is good practice to review the database object before dropping it, so choose Review SQL.
0コメント