Oracle存储过程总结(一、基本应用)（Summary of Oracle stored procedure (i. basic application)）.docVIP

Oracle存储过程总结(一、基本应用)（Summary of Oracle stored procedure (i. basic application)） Establish stored procedures or functions A stored procedure or function is a PL/SQL program in an ORACLE database that can be called directly or indirectly by the user. The main advantages of using stored procedures and functions are: 1. Effectiveness: in the client/server architecture, the client applies the requirement for SQL to the database server. As the number of users increases, the SQL request increases continuously, making the network quickly become a running bottleneck. Use stored procedures can make the running performance is a significant improvement, because of a call to the process of storage, which invokes the execution of multiple SQL statements in the server, thus reducing the network congestion. Reusability: a PL/SQL program is written only once and can be used everywhere. Portability: you can use stored procedures in any ORACLE database without considering platform issues. 4, maintainability: a stored procedure is used to complete a specific task, such as database trigger need to invoke the process are the same stored procedure, which can reduce the cost of software maintenance. I. stored procedures 1. Establish the syntax of stored procedures. CREATE [OR REPLACE] PROCEDURE name (parameter 1,... participate n)] IS [local variable declaration section] The BEGIN Executable part [exception handling section] The END; Note: the OR REPLACE option is to override this process when this stored procedure exists. The parameter part and the procedure definition are the same. Example: define a stored procedure to delete the student record specified by the student table in the students table. CREATE OR REPLACE PROCEDURE DELE (STUID VARCHAR2) IS The BEGIN The DELETE FROM STUDENTS WHERE STU_ID = STUID; The END; 2. Call the stored procedure Method: EXECUTE stored procedure name (parameter 1, parameter n); Note: the type of parameter 1 to the parameter n must be consistent with the type