Oracle 存储过程
存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL 语句集,存储在数据库中,经过第一次编译后再次调用不需要再次编译,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是数据库中的一个重要对象。
- 创建一个简单的存储过程
CREATE OR REPLACE PROCEDURE p_test --创建一个p_test 存储过程, 如果存在就覆盖它; IS --表明后面是一个pl/sql体 BEGIN --pl/sql体开始 NULL; END; --pl/sql体结束
- 执行储存过程
EXECUTE p_test;
- 执行储存过程
BEGIN p_test; END;
控制台输出显示
SET SERVEROUTPUT ON;
创建一个带输入参数的存储过程,把查询结果显示出来
CREATE OR REPLACE PROCEDURE p_test(in_id IN Integer) IS p_ename Varchar2(20); Error_Message Varchar2(100); BEGIN select ename into p_ename from emp where empno = in_id; Exception When Others Then Error_Message := Sqlerrm; Dbms_Output.Put_Line (Error_Message); Raise; Dbms_Output.Put_Line(p_ename); END;
创建一个带输入参数和输出参数的存储过程
Create Or Replace Procedure p_test(in_id IN Integer, out_total out Number) IS Error_Message Varchar2(100); Begin select Nvl(sal, 0) + Nvl(comm, 0) into out_total from emp where empno = in_id; Exception When Others Then Error_Message := Sqlerrm; Dbms_Output.Put_Line (Error_Message); Raise; Dbms_Output.Put_Line(out_total); End;
调用带输入和输出参数的存储过程
Declare in_id Integer; out_total Number; Begin in_id:=7369; p_test(in_id, out_total); Dbms_Output.Put_Line(out_total); End;
使用sys_refcursor游标创建一个结果集存储过程
Create Or Replace Procedure p_test(out_return out sys_refcursor) Is Begin open out_return for 'select * from emp'; End;
调用带结果集的存储过程(Fetch...into...)
Declare cur SYS_REFCURSOR; rowinfo emp%rowtype; Begin p_test(cur); Loop Fetch cur into rowinfo; Exit when cur%notfound; Dbms_Output.Put_Line(rowinfo.empno||','||rowinfo.ename); End Loop; close cur; End;
Cursor游标for...in...loop循环游标例子
Declare Cursor cur is select * from emp; rowinfo emp%rowtype; Begin for rowinfo in cur loop SYS.Dbms_Output.Put_Line(rowinfo.empno||','||rowinfo.ename); end loop; end;
Cursor游标while循环
Declare Cursor cur Is Select * From emp; v_myemp emp%rowtype; Begin Open cur; Fetch cur Into v_myemp; While cur%found Loop dbms_output.put_line(v_myemp.ename); Fetch cur Into v_myemp; End Loop; End;
bulk collectg高效循环游标
Declare Cursor cur Is Select * From emp; Type rowin Is Table Of emp%rowtype; -- Type 声明是类型, Is Table Of 指定是一个集合的表的数组类型,emp表上%ROWTYPE 指在表上的行的数据类型. rowinfo rowin; Begin Open cur; Loop Fetch cur bulk collect into rowinfo limit 3; -- BULK COLLECT INTO 指是一个成批聚合类型,它可以存储一个多行多列存储类型 For i In 1..rowinfo.count Loop Dbms_Output.Put_Line (rowinfo(i).empno||','||rowinfo(i).ename); --(i)表示下标号 End Loop; Exit when cur%notfound; --判断游标结束 End loop; End;
BULK COLLECT 子句会批量检索结果,即一次性将结果集绑定到一个集合变量中,并从SQL引擎发送到PL/SQL引擎。通常可以在SELECT INTO、FETCH INTO以及RETURNING INTO子句中使用BULK COLLECT.
BULK COLLECT的限制
- 不能对使用字符串类型作键的关联数组使用BULK COLLECT 子句。
- 只能在服务器端的程序中使用BULK COLLECT,如果在客户端使用,就会产生一个不支持这个特性的错误。
- BULK COLLECT INTO 的目标对象必须是集合类型。
- 复合目标(如对象类型)不能在RETURNING INTO 子句中使用。
- 如果有多个隐式的数据类型转换的情况存在,多重复合目标就不能在BULK COLLECT INTO 子句中使用。
- 如果有一个隐式的数据类型转换,复合目标的集合(如对象类型集合)就不能用于BULK COLLECTINTO 子句中。
- xx_cursor%ISOPEN 游标打开,其值为True;
- xx_cursor%FOUND Fetch语句返回一行记录,其值为True;
- xx_cursor%ROWCOUNT 返回已经从游标中取出的记录数;
- xx_cursor%NOTFOUND Fetch语句无返回,其值为True;
维护存储过程
1、查看过程状态
SELECT object_name,status FROM USER_OBJECTS WHERE object_type='PROCEDURE';
2、重新编译过程
ALTER PROCEDURE xs_proc COMPILE;
3、查看过程的源代码
SELECT * FROM USER_SOURCE WHERE TYPE='PROCEDURE';
4、删除存储过程
DROP PROCEDURE xs_proc;
没有高深的知识,没有进阶的技巧,万丈高楼平地起~!