Oracle GURUs out there, here is a set of questions from a newbe in
this area.
I'm trying to understand the package (functions & procedure) concept.
But I still have one thing that I have not yet understood.
All the functions/procedures that I have developed so far
INSERT,
UPDATE,
DELETE rows from tables
But when it comes to SELECTING rows I have a confussion.
I can retrieve a single row with a procedure and the value is returned
through an OUT variable with a value BLA.
Same things goes for the function (either throught RETURN statement or
OUT variables), but when I want to return mutlple rows I know I need a
cursor. But how to I return a "set of rows" to a statement?
So you undertand my problem a little better, here is what I'm trying
to do:
- generate package with some procedure/ functions that perfoms
actions on tables.
- give rights to certain users to these objects
- using JSP, ASP, PHP .... execute this package.function to
retrieve mutiple rows. IE: give me all the poeple who have not payed.
Questions:
- How to do this? I know how to run a simple query for example
"SELECT * from PEOPLE WHERE payed == FALSE" & this returns to me a set
of rows. But now I want to run something like "EXECUTE
PeoplePackage.HaveNotPayed()" & this return a set of rows for me.
- The reason I'm doing this is 4 fold:
- security reasons. I know I can set
privalages to certaina packages. But Can I do the same for views? I
thnk so.
- performace reasons. According to certain
books, tutorials I have learned that packages may function faster than
certain queries. This concept still does not convince me. When
retrieving N rows from a certain table, does this change the
perfomance? What I mean is time-wise a query running in a view Vs
query running in a stored procedure.
- Further validation: I am validating certain
data in the web server before being sent to the DB. There can be
further validartion in the DB server. But I still dont know if this is
necesary? Performance wise it could cause a big effect to have double
validation. Or maybe just leave the validation in 1 of the server?
- Security 2: Not the same telling PHP to run
"SELECT * FROM ....." that telling it to run "EXECUTE PROCEDURE_BLA".
If someone hacks into the code, they will also have to hack the DB
server. More securtiy. I am right?