I have 3 tables:
1. student: {id, name, roll}
2. subject: {id, name}
3. marks: {student_id, subject_id, marks}
我有3个表:1。学生:{id,name,roll} 2. subject:{id,name} 3. marks:{student_id,subject_id,marks}
I have a procedure which returns all the student and their marks (say getAllMarks() ), i want to create another procedure which returns marks of a given roll no (say getRollMarks(int rollno)). So there are two ways i can create the procedure:
1. write the appropriate SQL to get the result in getRollMarks procedure.
2. call getAllMarks in getRollMarks and then apply where clause on the result returned by getAllMarks.
Can anyone suggest pros and cons of above two methods? I have 2 Pros of method 2:
1.SQL queries won't be repeated across procedures.
2.If anything is changing in one table, the change will be done at one place/procedure, not in every procedure which is using that table.
PS: The tables are just for example, actually they are bigger tables with many parameters to query. The question can be simplified as should we write repeated queries in different procedures, or should we use existing procedures with required clause (where, order by, joins etc)?
我有一个程序,它返回所有学生和他们的标记(比如getAllMarks()),我想创建另一个程序,它返回给定掷骰号的标记(比如getRollMarks(int rollno))。因此,有两种方法可以创建过程:1。编写适当的SQL以获取getRollMarks过程中的结果。 2.在getRollMarks中调用getAllMarks,然后对getAllMarks返回的结果应用where子句。任何人都可以建议上述两种方法的优点和缺点吗?我有2个方法2的优点:1.SQL查询不会跨过程重复。 2.如果一个表中的任何内容发生变化,则更改将在一个位置/过程中完成,而不是在使用该表的每个过程中完成。 PS:这些表只是例如,实际上它们是更大的表,有许多参数可供查询。问题可以简化,我们应该在不同的程序中编写重复的查询,还是应该使用现有的程序和必需的子句(where,order by,joins等)?
1 个解决方案
#1
1
If you don't want to repeat the query, you should be using a view. Also performance wise it is faster.
如果您不想重复查询,则应使用视图。性能方面也更快。
#1
1
If you don't want to repeat the query, you should be using a view. Also performance wise it is faster.
如果您不想重复查询,则应使用视图。性能方面也更快。