存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL 语句集,它存储在数据库中,一次编译后永久有效,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是数据库中的一个重要对象。在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升。(百度百科)
优点:
-
重复使用。
-
减少网络流量。
-
安全性。可以防止SQL注入式攻击等。
定义
定义简单存储过程:
-- 案例1
CREATE OR REPLACE PROCEDURE demo AS/IS
变量2 DATE;
变量3 NUMBER;
BEGIN
--要处理的业务逻辑
EXCEPTION --存储过程异常
END
-- 案例2
CREATE OR REPLACE PROCEDURE 存储过程名称(
s_no in varchar,
s_name out varchar,
s_age number) AS
total NUMBER := 0;
BEGIN
SELECT COUNT(1) INTO total FROM student s WHERE s.age=s_age;
dbms_output.put_line('符合该年龄的学生有'||total||'人');
EXCEPTION
WHEN too_many_rows THEN
DBMS_OUTPUT.PUT_LINE('返回值多于1行');
END
-- 案例3
CREATE OR REPLACE PROCEDURE updateContractDept(
p_code in varchar,
p_dept_name in varchar,
p_dept_id in varchar) AS
dept_name_old varchar(50);
dept_id_old varchar(50);
dept_name_now varchar(50);
dept_id_now varchar(50);
BEGIN
SELECT dept_name,dept_id INTO dept_name_old,dept_id_old FROM contract WHERE code = p_code;
update contract set dept_name = p_dept_name,dept_id = p_dept_id where code = p_code
and exists ( select 1 from s_dept where dept_id = p_dept_id );
SELECT dept_name,dept_id INTO dept_name_now,dept_id_now FROM contract WHERE code = p_code;
dbms_output.put_line('合同【'||p_code||'】的实施部门已由【'||dept_name_old||dept_id_old||'】修改为【'||dept_name_now||dept_id_now||'】!');
EXCEPTION
WHEN others THEN
dbms_output.put_line('修改失败!');
END;
定义稍复杂一点的存储过程:
-- 更新部门(名称、id)
create or replace procedure updateDept(
-- 接收的参数
p_table_name in varchar,
p_key in varchar,
p_value in varchar,
p_dept_name in varchar,
p_dept_id in varchar)
AS
-- 变量
dept_name_old varchar(50);
dept_id_old varchar(50);
dept_name_now varchar(50);
dept_id_now varchar(50);
sql_getdept varchar2(2000);
sql_update varchar2(2000);
begin
-- 拼接sql
sql_getdept := 'SELECT dept_name,dept_id FROM '||p_table_name||' WHERE '||p_key||' = '''||p_value||'''';
sql_update := 'update '||p_table_name||' set dept_name = '''||p_dept_name||''',dept_id = '''||p_dept_id||''' where '||p_key||' = '''||p_value||''' and exists ( select 1 from s_dept where dept_id = '''||p_dept_id||''' )';
-- 执行前边拼接好的sql,并将查询结果保存在变量中
execute immediate sql_getdept into dept_name_old,dept_id_old;
execute immediate sql_update;
execute immediate sql_getdept into dept_name_now,dept_id_now;
-- 输出语句
dbms_output.put_line('部门已由【'||dept_name_old||dept_id_old||'】修改为【'||dept_name_now||dept_id_now||'】!');
-- 异常处理
EXCEPTION
-- 使用others,只要出现异常就会打印下边的语句
WHEN others THEN
dbms_output.put_line('修改失败!');
end;
调用
#执行存储过程
call 存储过程名()
删除
#删除存储过程
drop procedure 存储过程名;