赞
踩
在PL/SQL程序中,除匿名块以外,还有一类被命名的PL/SQL程序块,称为存储子程序。存储子程序以编译的形式存储在数据库服务器中,可以在应用程序中进行多次调用,是PL/SQL程序模块化的一种体现。PL/SQL存储子程序包括存储过程和(存储)函数两种。存储过程用于执行特定的操作,不需要返回值;函数用于返回特定的数据。在调用时,存储过程可以作为一个独立的表达式被调用,而函数只能作为表达式的一个组成部分被调用。
语法:
CREATE [OR REPLACE] PROCEDURE 过程名称(
参数名称 [参数模式] 数据类型 [DEFALT|:=value],...)
)]
AS|IS
声明部分;
EXCEPTION
异常处理;
END;
存储过程相关权限:(未指定用户授权)
权限名称 | 作用 |
---|---|
CREATE ANY PROCEDURE | 为任意用户创建存储过程的权限 |
CREATE PROCEDURE | 为用户创建存储过程的权限 |
ALTER PROCEDURE | 修改拥有的存储过程的权限 |
EXECUTE PROCEDURE | 执行用户存储过程的权限 |
DROP ANY PROCEDURE | 删除任意存储过程的权限 |
授权并创建存储过程:
--创建用户
create user test identified by test123;
--授权
grant create procedure to test;
--连接
conn test/test123;
--创建存储过程
create or replace procedure proc_demo
is
begin
dbms_output.put_line('欢迎你'||user);
dbms_output.put_line('现在的时间是'||to_char(sysdate,'YYYY-mm-DD hh24:MM:ss'));
end;
调用存储过程: EXECUTE|EXEC|CALL 存储过程名称(实参...)
在PL/SQL程序块中调用存储过程:
--创建
CREATE OR REPLACE PROCEDURE proc_test(p_str1 VARCHAR2,p_str2 VARCHAR2)
AS
BEGIN
DBMS_OUTPUT.PUT_LINE(p_str1||p_str2);
END;
--调用
declare
v_var1 varchar2(20):='hello';
v_var2 varchar2(20):='procedure!';
begin
proc_test(v_var1,v_var2);
end;
--调用完成
存储过程定义的参数有三种模式,分别是IN、OUT、IN OUT。
在存储过程中声明形参时,不能定义形参的长度或精度,他们是作为参数传递机制的一部分传递的,是由实参决定的。
IN参数模式
CREATE OR REPLACE PROCEDURE proc _ in ( p _ empno IN NUMBER )
AS
V _ ename scott , emp . ename TYPE ;
V _ sal scott . emp . sal 왕 TYPE ;
BEGIN
SELECT ename , sal INTO v _ ename , V _ sal FROM scott . emp
WHERE empno = p _ empno ;
DBMS _ OUTPUT . PUT _ LINE ('雇员的姓名是:' V _ enamel '工资是:' v _ sal );
EXCEPTION
WHEN NO _ DATA _ FOUND THEN
DBMS _ OUTPUT . PUT _ LINE ('雇员编号未找到);
END;
输出:雇员的姓名是:张三 工资是:2500
使用IN参数模式传参时,也可以使用DEFAULT关键字为参数设置默认值,这样在调用时不传递此参数也不会出错。但需要注意的是,有默认值的参数应该放在参数列表的最后。
参数默认值
create or replace procedure proc_user_insert(
v_user number;
V _ dname VARCHAR2 DEFAULT ' TEMP ', v _ loc VARCHAR2 DEFAULT ' TEMP ')
AS
BEGIN
INSERT INTO scott . dept VALUES ( v _ deptno , V _ dname , V _ loc ); COMMIT ;
EXCEPTION
WHEN OTHERS THEN
DBMS _ OUTPUT . PUT _ LINE ('添加失败!原因为 IsQLERRM );
ROLLBACK ;
END;
)
OUT参数模式
CREATE OR REPLACE PROCEDURE proc _ out ( P _ deptno NUMBER , P _ num OUT NUMBER , p _ avgsal oU Т NUMBER ) AS BEGIN SELECТ COUNT (*) num , ROUND ( AVG ( sal ),2) avgsal INTO p _ num , P _ avgsal FROM scott . emp WHERE deptno = p _ deptno ; EXCEPTION WHEN NO _ DATA _ FOUND THEN RAISE _ APPLICATION _ ERROR (-20000,'该部门编号不存在'); END ; --定义一个 PL / sQL 块调用存储过程 DECLARE V_num NUMBER ; V_avgsal NUMBER ; BEGIN proc _ out (10, v _ num , V _ avgsal ); DBMS_OUTPUT.put_line ('10号部门的总人数为 v _num1',平均工资为 lv_avgsal); END ;
此时参数只是作为一个形参标记使用,其内容不会传递到存储过程中,在过程中对参数值的修改将返回给相应的实参。
IN OUT参数模式
CREATE OR REPLACE PROCEDURE proc _ dept _ dname _ exist ( P _ io _ value IN OUT VARCHAR2) IS V _ count NUMBER ; BEGIN SELECТ COUNT (*) INTO v_count FROM scott.dept WHERE dname= P_io_value; IF ( v_count>0) THEN P_io_value:='已存在'; ELLSE P_io_value:='不存在'; END IF ; END ; --存储过程已创建 --定义一个 PL / sQL 块调用存储过程 DECLARE v io value VARCHAR2(20):=' ACCOUNTING '; BEGIN proc _ dept _ dname _ exist ( v _ io _ value ); DBMS _ OUTPUT . PUT _ LINE ('部门名称 END;
IN OUT参数模式相当于IN和OUT两种模式的集合,可以将变量的内容传递到过程中,也可以将过程中对变量的修改返回到原始变量。
查看当前用户的子程序信息:
select object_name,object_type from user_procedure;
查看存储过程定义的内容:
select name,text from user_source where type='PROCEDURE';
查询存储过程和数据库对象之间的依赖关系:
select name,type,referenced_name from user_dependencies
where referenced_name='EMP' or referenced_name='DEPT';
DROP PROCEDURE proc_test;
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。