Updating relational databases through views

19-Jan-2020 03:12 by 7 Comments

Updating relational databases through views

In this section, we will talk about considerations on a very elemental functional level rather than a more macro application level.

In the next couple of sections we'll outline what are the important questions to consider when choosing one approach over another.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) 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). 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.Just follow Best Practices outlined by an authority in the subject matter and things will magically fall into place.Rather than just focus on Best Practices, I would like to propose that one should think about what they are trying to achieve and why one approach is better than another to get there.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.

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.

A lot of people in the database and programming professions are vehemently in favor or opposed to the use of stored procedures and the like in databases.

They will argue that all access to the database should go thru stored procedures because it is more secure and shields applications from changing logic.

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.