1) What is difference between a PROCEDURE & FUNCTION ?
· A function always return a values while procedure can return one or more values through Parameters.A function can call directly by sql statement like select func_name from dual while procedure cannot.
·
· We can't have any DDL DML and TLC command inside a function if that function is called from aquery.But if the function is not called from query then we can have all transactional statement(DDL DML and TLC ) inside a function.
· procedure is to be compile only one time.Execution of procedure is faster then function
· SP Accepts both i/p and o/p paramateres where function also accespts i/p and o/p paramateres fuction can be used in select stmt as well as function can be used as user defined datatype
2) Which is the best to user for code and when is it best to use a package rather than a procedure?
· Always use a package.
· Never use a standalone procedure except for demos, tests and standalone utilities (that call nothing and are called by nothing)
Can you please justify your statement?
· break the dependency chain (no cascading invalidations when you install a new package body -- if you have procedures that call procedures -- compiling one will invalidate your database) http://asktom.oracle.com/pls/asktom/f?p=100:11:1669015774188148::::P11_QUESTION_ID:286816015990
· support encapsulation -- I will be allowed to write MODULAR, easy to understand code -- rather then MONOLITHIC, non-understandable procedures
· o increase my namespace measurably. package names have to be unique in a schema, but I can have many procedures across packages with the same name without colliding
· support overloading
· support session variables when you need them
· promote overall good coding techniques, stuff that lets you write code that is modular, understandable, logically grouped together....
0 comments:
Post a Comment