存储过程是在大型数据库系统中,一组为了完成特定功能的SQL语句集,它存储在数据库中,一次编译后永久有效,用户通过指定存储过程的名字并给出参数,来执行它,存储过程是数据库中的一个重要对象,在数据量特别庞大的情况下利用存储过程能达到倍速的效果提升。
一、 存储过程的好处
-
由于数据库执行动作时,是现编译后执行的,然而存储过程是一个编译过的代码块,所以执行速率要比SQL语句高。
-
一个存储过程在程序在网络中交互时可以替代大堆的SQL语句,所以也能降低网络的通信量,提高通信速率。
-
通过存储过程能够使没有权限的用户在控制之下间接的存取数据库,从而确保数据的安全。
二、 定义简单的存储过程
create or replace procedure p_emp is
BEGIN
-- 存储过程中至少要有一行有效代码
dbms_output.put_line('hello world');
end p_emp;
三、 带参数的存储过程
带参数的存储过程在定义参数时要指定参数名称 参数类型in/out入参还是出参,in可以不写,默认是入参,参数的数据类型
create or replace procedure p_emp(v_deptno IN emp.deptno%TYPE) is
CURSOR emp_c IS SELECT * FROM emp WHERE deptno=v_deptno;
v_emp_row emp%ROWTYPE;
BEGIN
FOR v_emp_row IN emp_c LOOP
dbms_output.put_line(v_emp_row.empno);
END LOOP;
end p_emp;
四、 带出参的存储过程
create or replace procedure p_emp(
v_deptno IN emp.deptno%TYPE,
v_num OUT INTEGER) is
BEGIN
SELECT COUNT(*) INTO v_num FROM emp WHERE deptno=v_deptno;
end p_emp;
黄色部分的参数是存储过程的出参,通过这个出参可以看到存储过程执行的结果,存储过程中不建议处理事务,提交和回滚都不建议操作,如果存储过程有嵌套调用的话,让最外层程序来决定是否提交事务。
五、 例题
给一个字符串,'7788,5566,2233'员工编号,传入存储过程,删除这些员工 记录,并给出这些员工删除的结果'true,false,true'删除成功true,否则false,以字符串形式返回。
CREATE OR REPLACE PROCEDURE p_delete(ids VARCHAR,-- 要删除的ids
rs OUT VARCHAR) -- 删除的结果
IS
v_ids VARCHAR(50) :=ids;-- 赋值给变量,因为入参无法改变
v_id VARCHAR(5);-- 截取出的id
n NUMBER;-- 逗号的位置
BEGIN
LOOP
EXIT WHEN v_ids IS NULL;-- ids为null时退出循环
n:=INSTR(v_ids,',');-- 找逗号的位置
IF n=0 THEN -- 没有逗号
v_id:=v_ids;
v_ids:=NULL;-- ids变为null,标识着准备退出循环
ELSE
v_id:=SUBSTR(v_ids,1,n-1);-- 找逗号前的id
v_ids:=SUBSTR(v_ids,n+1);-- ids更改为剩下的id信息
END IF;
DELETE FROM emp WHERE empno=v_id;-- 删除记录
IF SQL%ROWCOUNT=1 THEN -- 判断结果
IF rs IS NULL THEN-- 拼接结果
rs:='true';
ELSE
rs:=rs||',true';
END IF;
ELSE
IF rs IS NULL THEN
rs:='false';
ELSE
rs:=rs||',false';
END IF;
END IF;
END LOOP;
END;
-- 调用存储过程
declare
v_result VARCHAR2(50);
BEGIN
p_delete('7788,5566,2233',v_result);
COMMIT;
dbms_output.put_line('execute result:'||v_result);
-- execute result:true,false,false
end;