1.存储过程和存储函数的主要区别
存储函数可以通过return返回函数值;存储函数可以在SQL语句内部调用;存储函数较存储过程有诸多限制;存储过程用select语句返回记录集,存储函数使用表变量返回记录集。
2.存储过程和存储函数的创建
存储过程的创建
创建存储过程:
CREATE OR REPLACE PROCEDURE MYPROCEDURE(PARAM1 IN NUMBER,PARAM2 OUT VARCHAR2)
AS|IS //说明部分,可定义变量
BIGIN
//可执行部分
SELECT SALARY INTO PARAM2 FROM SALARYTABLE WHERE USERS.ID=PARAM1;
END
存储函数的创建
创建使用FUNCTION 关键字,创建方式同上,当只有一个返回值时可使用return返回值,如果返回多个参数时可使用存储过程返回值得方式,
CREATE [OR REPLACE] FUNCTION MYPROCEDUREFUN(PARAM1 IN NUMBER)
//返回值类型
RETURN NUMBER
AS
PARAM2 NUMBER(5,2);
BEGIN
SELECT SALARY INTO PARAM2 WHERE SALARYTABLE WHERE USERS.ID=PARAM1;
RETURN NVL(PARAM2,0);
END;
3.存储函数和存储过程的调用方式
存储过程的调用 EXEC | EXECUTE 或PLSQL语句
存储函数的调用 AELECT MYPROCEDUREFUN(参数) FROM TABLE
4.JAVA中使用调用存储过程和存储函数
调用存储过程
String SQL="{CALL MYPROCEDURE(?,?)}";
Connection conn=JDBCUtils.getConnection();
CallableStatement call=conn.prepareCall(SQL);
//对于in参数(第一个参数)
call.setInt(1,12345);
//对于out参数(第二个参数)
call.registerOutParameter(2,oracleTypes.VARCHAR2);
call.execute();
//获取结果
String PARAM2=call.getString(2);
调用存储函数
String SQL="?={CALL MYPROCEDURE(?)}";
Connection conn=JDBCUtils.getConnection();
CallableStatement call=conn.prepareCall(SQL);
//对于输出参数(第一个参数)
call.registerOutParameter(1,oracleTypes.NUMBER);
//对于in参数(第二个参数)
call.setInt(2,12345);
call.execute();
//获取结果
Double returnVal=call.getDouble(1);
5.存储过程或存储函数返回结果集
返回集合需要使用到PLSQL中的光标和包的相关知识,将在下篇文章中介绍,这里给出使用方法。
包定义:
CREATE [OR REPLACE] PACKAGE MYPACKAGE
AS
//自定义数据类型--引用光标的类型
TYPE SALCURSOR IS REF CURSOR;
PROCEDURE QUERYSALLIST(DNO IN NUMBER, OUTLIST OUT SALCURSOR);
END MYPACKAGE;
包体:
CREATE [OR REPLACE] PACKAGE BODY MYPACKAGE
AS
//实现存储过程
PROCEDURE QUERYSALLIST(DNO IN NUMBER, OUTLIST OUT SALCURSOR)
AS
BEGIN
//使用之前打开光标
OPEN OUTLIST FOR SELECT * FROM SALTABLE WHERE DEPNO=DNO;
END [QUERYSALLIST];
END;
如果在应用程序中使用包中的存储过程或存储函数,需要加上包名。