Oracle存儲过程总结(基本应用).doc

建立存储过程或函数 存储过程或函数是存储ORACLE数据库中的PL/SQL程序，可由用户直接或间接调用。使用存储过程和函数主要优越性是: 1、提高了效:在客户/服务器体系结构中，客户机应用向数据库服务器提出对SQL的需求。随着用户数量的增加，SQL 请求也就不断地增加，使网络很快就成为运行的瓶颈。使用存储过程可使运行性能得到显著的改进，因为对储过程的一个调用，即调用了在服务器中执行的多个SQL语句，从而减少了网络的拥挤。 2、可重用性:一个PL/SQL程序只需编写一次，即可用于各种地方. 3、可移植性：可在任何ORACLE数据库中使用存储过程，而不用考虑平台问题。 4、可维护性:一个存储过程用于完成一个特定的任务，如数据库触发器等需要调用该过程的地方均调用同一个存储过程，这样可降低软件维护的成本。 一、 存储过程 1、 建立存储过程的语法。 CREATE [OR REPLACE] PROCEDURE 程储过程名 [（参数1,……参加n）] IS [局部变量声明部分] BEGIN 可执行部分 [例外处理部分] END; 说明：OR REPLACE 选项是当此存储过程存在时覆盖此程储过程。 参数部分和过程定义的语法相同。 例:定义一个存储过程用于删除students表中按学号指定的学生记录。 CREATE OR REPLACE PROCEDURE DELE (STUID VARCHAR2) IS BEGIN DELETE FROM STUDENTS WHERE STU_ID =STUID; END; 2、 调用存储过程 方法: EXECUTE 存储过程名(参数1,参数n); 说明:参数1到参数n的类型与存储过程定义的类型必须一致，且参数的个数必须相同。 例:调用DELE存储过程删除学号为1的学生记录。 EXECUTE DELE(‘1’); 例:建立一个存储过程，在emp表中给按雇员号指定的人员增加工资,如果工资大于2000则增加50，否则如果工资大于1000则增加100，否则增加150。 CREATE OR REPLACE PROCEDURE ADDSAL (EMPLOYNO EMP.EMPNO%TYPE) IS INCREMENT NUMBER; SALARY EMP.SAL%TYPE; BEGIN SELECT SAL INTO SALARY FROM EMP WHERE EMPNO=EMPLOYNO; IF SALARY=2000 THEN INCREMENT:=50; ELSIF SALARY=1000 THEN INCREMENT:=100; ELSE INCREMENT:=150; END IF; UPDATE EMP SET SAL=SAL+INCREMENT WHERE EMPNO=EMPLOYNO; END; 调用此存储过程,给雇员号为7369的记录增加相应的工资。 Execute addsal(‘7369’); 二、 存储函数 1、 语法 CREATE [OR REPLACE] FUNCTION 函数名 [参数1,……参数n] RETURN 函数数据类型 IS [ 局部变量说明] BEGIN 可执行部分 [例外处理部分] RETURN 函数的值 END; 说明:函数数据类型是函数返回值的数据类型； 函数的值是返回给调用程序的数值。 例：建立一个存储函数，统计指定部门的人数。 CREATE OR REPLACE FUNCTION COUNTNUM (DEPNO NUMBER) RETURN NUMBER IS SUMA NUMBER; BEGIN SELECT COUNT(*) INTO SUMA FROM EMP WHERE DEPTNO=DEPNO RETURN SUMA; END; 调用此函数时注意不能把函数单独的写成一行。可写在PL/SQL赋值语句的右端。或写在SELECT语句中等等。 例：写一PL/SQL块统计10号部门和30号部门人数之和。 Declare A number; Begin A:=countnum(10)+countnum(30); Dbms_output.put_line(a); End; 例：建立一个存储过程，将STUDENTS表中按学号指定的学生记录移至HISTORY表中。并在history表中增加移入日期 (删除STUDENTS表中的记录，同时录入到HISTORY表中)。 Create or replace procedure move( stuno varchar) is Begin /*将students中学号为stuno的记录插入到history表中*/ Insert into history(stu_id,name,sex,lda