Unanswered: when to use view, function and when to code in the SQL query?
what are the advantages/disadvantages of using Oracle views or function as opposed to simply putting the SQL query for the action you want to perform in your code? let me clarify with an example: i am working on a VB.NET application and i need to get a count on a field from one of my Oracle 8.1.7 database tables. Now, i could simply put the SQL query in my VB.NET code or alternatively i could create an Oracle View or a Function to get the same result. The query is kind of big, it pulls from 4 tables and has a bunch of ANDs. Any tips? Thanks.
I agree in principle, but how do you know when an SQL statement is too big to be embedded in the VB code? For an example, it would be wasteful to write an Oracle function for something like this: "SELECT TEXT FROM MYTABLE WHERE COLOR = RED".
I think that as long as you can do what you want to do in a single SQL query, and provided that query is shorter than..say 10 lines, it'd probably be a good idea to embed it in the VB code. What do you think?
Well I guess it depends on how "serious" your system is. If you are just "knocking up" a simple database application, then maybe embedding SQL code in your VB is OK. But if this is a system on a medium to large scale (like more than 1 developer involved), I would definitely go for a clear separation between database code (in packages) and application code (in VB) - no question.