使用存储过程的优点。Improved performance – The amount of information that an application must send over a network is small compared with issuing individual SQL statements or sending the text of an entire PL/SQL block to Oracle Database, because the information is sent only once and thereafter invoked when it is used. – The compiled form of a procedure is readily available in the database, so no compilation is required at execution time. – If the procedure is already present in the shared pool of the SGA, then the database need not retrieve it from disk and can begin execution immediately. ■ Memory allocation Because stored procedures take advantage of the shared memory capabilities of Oracle Database, it must load only a single copy of the procedure into memory for execution by multiple users. Sharing code among users results in a substantial reduction in database memory requirements for applications. ■ Improved productivity Stored procedures increase development productivity. By designing applications around a common set of procedures, you can avoid redundant coding. For example, you can write procedures to manipulate rows in the employees table. Any application can call these procedures without requiring SQL statements to be rewritten. If the methods of data management change, then only the procedures must be modified, not the applications that use the procedures. See Also: ■ "Tools for Database Developers" on page 19-1 ■ Oracle Database PL/SQL Language Reference for complete information about PL/SQL, including packages See Also: ■ Pro*C/C++ Programmer's Guide and Pro*COBOL Programmer's Guide to learn about stored procedures in these languages ■ Oracle Database PL/SQL Language Reference Overview of PL/SQL 8-4 Oracle Database Concepts Stored procedures are perhaps the best way to achieve code reuse. Because any client application written in any language that connects to the database can invoke stored procedures, they provide maximum code reuse in all environments. ■ Integrity Stored procedures improve the integrity and consistency of your applications. By developing applications around a common group of procedures, you reduce the likelihood of coding errors. For example, you can test a subprogram to guarantee that it returns an accurate result and, after it is verified, reuse it in any number of applications without retesting. If the data structures referenced by the procedure are altered, then you must only recompile the procedure. Applications that call the procedure do not necessarily require modifications. ■ Security with definer's rights procedures Stored procedures can help enforce data security (see "Overview of Database Security" on page 17-1). A definer's rights procedure executes with the privileges of its owner, not its current user. Thus, you can can restrict the database operations that users perform by allowing them to access data only through procedures and functions that run with the definer's privileges. For example, you can grant users access to a procedure that updates a table but not grant access to the table itself. When a user invokes the procedure, it runs with the privileges of its owner. Users who have only the privilege to run the procedure (but not privileges to query, update, or delete from the underlying tables) can invoke the procedure but not manipulate table data in any other way. ■ Inherited privileges and schema context with invoker's rights procedures An invoker's rights procedure executes in the current user's schema with the current user's privileges. In other words, an invoker's rights procedure is not tied to a particular user or schema. Invoker's rights procedures make it easy for application developers to centralize application logic, even when the underlying data is divided among user schemas. For example, an hr_manager user who runs an update procedure on the hr.employees table can update salaries, whereas an hr_clerk who runs the same procedure is restricted to updating address data.
打个比方， $query = "select id,name from A";//处理数据$query = "insert into A(id,name)values(2,'sss')";//处理数据$qeury = "update B set ... ";//处理数据，并返回值 如上， 以前是把处理数据部份都写在存储过程里的， 用一键调用好方便，但现在又发现要修改处理数据那部份内容时不灵活 . . . 所时不知如何决择好
– The amount of information that an application must send over a network is
small compared with issuing individual SQL statements or sending the text of
an entire PL/SQL block to Oracle Database, because the information is sent
only once and thereafter invoked when it is used.
– The compiled form of a procedure is readily available in the database, so no
compilation is required at execution time.
– If the procedure is already present in the shared pool of the SGA, then the
database need not retrieve it from disk and can begin execution immediately.
■ Memory allocation
Because stored procedures take advantage of the shared memory capabilities of
Oracle Database, it must load only a single copy of the procedure into memory for
execution by multiple users. Sharing code among users results in a substantial
reduction in database memory requirements for applications.
■ Improved productivity
Stored procedures increase development productivity. By designing applications
around a common set of procedures, you can avoid redundant coding. For
example, you can write procedures to manipulate rows in the employees table.
Any application can call these procedures without requiring SQL statements to be
rewritten. If the methods of data management change, then only the procedures
must be modified, not the applications that use the procedures.
See Also:
■ "Tools for Database Developers" on page 19-1
■ Oracle Database PL/SQL Language Reference for complete
information about PL/SQL, including packages
See Also:
■ Pro*C/C++ Programmer's Guide and Pro*COBOL Programmer's
Guide to learn about stored procedures in these languages
■ Oracle Database PL/SQL Language Reference
Overview of PL/SQL
8-4 Oracle Database Concepts
Stored procedures are perhaps the best way to achieve code reuse. Because any
client application written in any language that connects to the database can invoke
stored procedures, they provide maximum code reuse in all environments.
■ Integrity
Stored procedures improve the integrity and consistency of your applications. By
developing applications around a common group of procedures, you reduce the
likelihood of coding errors.
For example, you can test a subprogram to guarantee that it returns an accurate
result and, after it is verified, reuse it in any number of applications without
retesting. If the data structures referenced by the procedure are altered, then you
must only recompile the procedure. Applications that call the procedure do not
necessarily require modifications.
■ Security with definer's rights procedures
Stored procedures can help enforce data security (see "Overview of Database
Security" on page 17-1). A definer's rights procedure executes with the privileges
of its owner, not its current user. Thus, you can can restrict the database operations
that users perform by allowing them to access data only through procedures and
functions that run with the definer's privileges.
For example, you can grant users access to a procedure that updates a table but
not grant access to the table itself. When a user invokes the procedure, it runs with
the privileges of its owner. Users who have only the privilege to run the procedure
(but not privileges to query, update, or delete from the underlying tables) can
invoke the procedure but not manipulate table data in any other way.
■ Inherited privileges and schema context with invoker's rights procedures
An invoker's rights procedure executes in the current user's schema with the
current user's privileges. In other words, an invoker's rights procedure is not tied
to a particular user or schema. Invoker's rights procedures make it easy for
application developers to centralize application logic, even when the underlying
data is divided among user schemas.
For example, an hr_manager user who runs an update procedure on the
hr.employees table can update salaries, whereas an hr_clerk who runs the
same procedure is restricted to updating address data.
$query = "select id,name from A";//处理数据$query = "insert into A(id,name)values(2,'sss')";//处理数据$qeury = "update B set ... ";//处理数据，并返回值
如上， 以前是把处理数据部份都写在存储过程里的， 用一键调用好方便，但现在又发现要修改处理数据那部份内容时不灵活 . . . 所时不知如何决择好
2. 不要和数据库打交道的时候，动不动就写存储过程（言必称希腊），很多时候用Where/Having/子查询之类就可以搞定的，非要在存储过程中写fetch+if，看了就恶心
3. 如果项目规模不大，主要看开发团队习惯及喜好
4. 以个人经验，按照现在的趋势，如果项目数据规模比较大，为了性能考虑，今后可能会进行分割，或者，上来就投入了很多服务器，分层就分的很清楚，应用集群、分压、缓存都考虑到了，就应考虑避免存储过程。IMHO，在应用层提高效率，速度的手段相对比较多，也灵活一点。
5. 也是本人经验，招Java/.NET，会基本SQL的程序员相对来说比在此基础上增加存储过程的容易一些，另外，好的数据库结构设计，通常也削减了存储过程存在的必要性。当然，不管选哪种，永远应当记得：
1. 避免在应用与数据库之间传递大量非必要信息
2. 尽量避免数据库中的计算，比如将经常用的统计的数据单独做一个字段或者设计表，避免不必要的计算量