There is a lot of confusion when it comes to MySQL and views versus stored procedures. Many people often wonder what the difference is between the two of them?
Views
It is all in the name. Views are essentially virtual tables comprised of pre-written SQL statements. You might have a view for displaying users who have joined in the last couple of days. Think of views as a means of getting data from your database without having to write or call queries constantly.
A view cannot accept parameters and also is limited to just one SELECT query as well as not being able to perform modifications to any table. They’re very much used for simplifying data queries.
You can query a view like you would any table, it is a virtual table after all and thus can be used in a query alongside other views to form a query. To select data from a view called “popularUsers” you can simply go: SELECT * FROM popularUsers
Stored Procedures
One of the definitions of the word “procedure” is: a series of actions conducted in a certain order or manner. A stored procedure is a set of one or more queries that allow for dynamic parameters contained within something resembling that of a function, only called a stored procedure.
A stored procedure can contain loops, if/else statements and other logic you might usually see within a function if you’ve worked with any programming language before. It means you can create functions for getting popular users for example and allow date ranges to be specified to the procedure which will affect the results.
From a performance perspective, a stored procedure is often easier to tune to be more effective in comparison to a view and you should always use a stored procedure where you can. They also neatly encapsulate logic keeping things cleaner, they can be used to abstract your schema for security reasons and they’re easier to test.
Both have their purposes and can achieve relatively the same result. A stored procedure offers you greater modularity, can easily be optimised and customised and allow for cool things like loops, if/else statements and other things views cannot. If performance is important to you and your working with a lot of tables and rows, a stored procedure might just stop you from tearing out your hair and smashing your head against your desk violently.
