oracle自定义函数和存储过程

oracle自定义函数和存储过程

oracle自定义函数和存储过程（转）2008-07-23 10:43--过程(PROCEDURE)--------------------------------------------------//

--创建表

CREATE TABLE user_info

(

id VARCHAR2(4),

name VARCHAR2(15),

pwd VARCHAR2(15),

address VARCHAR2(30)

);

--插入数据

INSERT INTO user_info VALUES('u001','zhao','zhao','shanghai'); --如要经常执行插入，Oracle每次都要进行编译，并判断语法正确性，因此执行速度可想而知，

--所以我们要创建一个过程来实现

CREATE OR REPLACE PROCEDURE AddNewUser

(

n_id user_info.id%TYPE,

n_name user_https://www.360docs.net/doc/0313547716.html,%TYPE,

n_pwd user_info.pwd%TYPE,

n_address user_info.address%TYPE

)

AS

BEGIN

--向表中插入数据

INSERT INTO user_info(id,name,pwd,address)

VALUES(n_id,n_name,n_pwd,n_address);

END AddNewUser;

/

--下面我们利用PL/SQL匿名块调用该过程

DECLARE

--描述新用户的变量

v_id user_info.id%TYPE := 'u002';

v_name user_https://www.360docs.net/doc/0313547716.html,%TYPE := 'wish';

v_pwd user_info.pwd%TYPE := 'history';

v_add user_info.address%TYPE := 'shanghai';

BEGIN

--调用过程，添加wish用户到数据库

AddNewUser(v_id,v_name,v_pwd,v_add);

DBMS_OUTPUT.PUT_LINE('用户' || v_name || ' 已经成功插入');

END;

/

--或者可以利用EXEC()直接插入

EXEC

AddNewUser('u003','jian','jian','beijing'); --或

EXECUTE

AddNewUser('u004','zhang','zhang','beijing&# 39;);

--在这种上下文中，调用过程中的变量就类似于（C、VB）中的实参，而过程里的变量就是形参

--形参的模式(IN、OUT、IN OUT)，默认为IN

--IN ：只读属性，即不能修改

--Out ：读写属性，即可读可写

--In Out ：顾名思义，他是IN 和OUT 的集合

--下面通过一个示例来理解

CREATE OR REPLACE PROCEDURE ModeSimple

(

p_InParameter IN NUMBER,

p_OutParameter OUT NUMBER,

p_InOutParameter IN OUT NUMBER

)

IS

v_LocalVariable NUMBER := 0;

BEGIN

DBMS_OUTPUT.PUT_LINE('过程前：');

IF (p_InParameter IS NULL) THEN

DBMS_OUTPUT.PUT_LINE('p_InParameter is NULL');

ELSE

DBMS_OUTPUT.PUT_LINE('p_InParameter = ' || p_InParameter);

END IF;

IF (p_OutParameter IS NULL) THEN

DBMS_OUTPUT.PUT_LINE('p_OutParameter is NULL');

ELSE

DBMS_OUTPUT.PUT_LINE('p_OutParameter = ' || p_OutParameter); END IF;

IF (p_InOutParameter IS NULL) THEN

DBMS_OUTPUT.PUT_LINE('p_InOutParameter is NULL');

ELSE

DBMS_OUTPUT.PUT_LINE('p_InOutParameter = ' || p_InOutParameter); END IF;

--赋值

v_LocalVariable := p_InParameter; --合法

v_LocalVariable := p_OutParameter; --合法，注：必须Oracle 9i或以上版本

v_LocalVariable := p_InOutParameter; --合法

--!p_Inparameter := 7; --非法，因为IN 参数不能被修改

p_OutParameter := 7; --合法

p_InOutParameter := 8; --合法

DBMS_OUTPUT.PUT_LINE('过程末：');

IF (p_InParameter IS NULL) THEN

DBMS_OUTPUT.PUT_LINE('p_InParameter is NULL');

ELSE

DBMS_OUTPUT.PUT_LINE('p_InParameter = ' || p_In

Parameter);

END IF;

IF (p_OutParameter IS NULL) THEN

DBMS_OUTPUT.PUT_LINE('p_OutParameter is NULL');

ELSE

DBMS_OUTPUT.PUT_LINE('p_OutParameter = ' || p_OutParameter); END IF;

IF (p_InOutParameter IS NULL) THEN

DBMS_OUTPUT.PUT_LINE('p_InOutParameter is NULL');

ELSE

DBMS_OUTPUT.PUT_LINE('p_InOutParameter = ' || p_InOutParameter); END IF;

END ModeSimple;

/

--利用PL/SQL匿名块调用过程来查看结果

DECLARE

v_in NUMBER := 1;

v_out NUMBER := 2;

v_inout NUMBER := 3;

BEGIN

DBMS_OUTPUT.PUT_LINE('在调用过程前：');

DBMS_OUTPUT.PUT_LINE('v_In = ' || v_in);

DBMS_OUTPUT.PUT_LINE('v_Out = ' || v_out);

DBMS_OUTPUT.PUT_LINE('v_InOut = ' || v_inout);

--调用过程ModeSimple

ModeSimple(v_in,v_out,v_inout);

DBMS_OUTPUT.PUT_LINE('在调用过程后：');

DBMS_OUTPUT.PUT_LINE('v_In = ' || v_in);

DBMS_OUTPUT.PUT_LINE('v_Out = ' || v_out);

DBMS_OUTPUT.PUT_LINE('v_InOut = ' || v_inout);

END;

/

--与过程相关的数据字典（这里的条件必须大写）

SELECT object_name,object_type,status FROM user_objects

WHERE object_name = 'MODESIMPLE';

--函数(Function)---------------------------------------------------//

--函数与过程结构上相似，不同的是函数有一条RETURN语句，用来返回值

--例如我们要查询用户zhao是否在user_info中存在

CREATE OR REPLACE FUNCTION CheckUser

(

f_user user_https://www.360docs.net/doc/0313547716.html,%TYPE

)

RETURN BOOLEAN --函数返回类型

IS

v_userCount NUMBER;

BEGIN

SELECT COUNT(name) INTO v_userCount FROM user_info WHERE name = f_user;

IF (v_userCount > 0) THEN

RETURN TRUE;

ELSE

RETURN FALSE;

END IF;

END CheckUser;

/

--下面我们可以这样调用该函数

DECLARE

user user_https://www.360docs.net/doc/0313547716.html,%TYPE := 'jian';

a BOOLEAN;

BEGIN

IF CheckUser(user) THEN

DBMS_OUTPUT.PUT_LINE('用户' || user || ' 存在');

ELSE

DBMS_OUTPUT.PUT_LINE('用户' || user || ' 不存在'); END IF;

END;

/* 过程与函数的差别

函数与过程有很多相似的地方，但也有一些差别，其中的一点就是，过程的参数可以有三种模式(IN、OUT、

IN OUT)，而函数只有一种(IN),因为使用函数的目的是传入0或多个参数，返回单

一的值，想让函数返回多

个值是一种不良的编程习惯，我们应该加以改正。