oracle as 不可以省略,Oracle：存储过程，存储函数

什么是存储过程和存储函数：指存储在数据库中供所有用户程序调用的子程序叫做存储过程、存储函数。

这个子程序是用PL/SQL写的。可以用Java程序调用，就是完成特定功能的子程序。

用create procedure命令创建存储过程。

语法：

Create [or replace] procedure 过程名(参数列表) asPLSQL子程序体;

As相当于declare，所以我们可以在as后面定义变量。As不可以省略。

--打印Hello World。

Create or replace procedure sayHelloWorld

As

--说明部分

Begin

Dbms_output.put_line(‘HelloWorld’);

End;

/

先将存储过程编译，在SQL Developer左边的树结构中的过程里就产生了这个存储过程。

如果图标上有小红叉，表示有语法错误，如果是绿叶，表示没有问题。这个存储过程的功能是打印HelloWorld。

如何调用这个存储过程呢？

第一种方式：使用execute命令

Exec sayHelloWorld();

第二种调用方式：

Begin

sayHelloWorld();

sayHelloWorld();

sayHelloWorld();

end;

/

带参数的存储过程：

--给指定员工的工资涨100元工资，并且打印涨前和涨后的薪水。

--既然是指定，就是员工会变化，所以要接收一个员工参数。

--指定参数的时候要分为输入参数和输出参数，默认是输入参数，但是显示指出比较好，用in指出。

Create or replace procedure raiseSalary(enoin number)

As

--变量

Psalemp.sal%type;

Begin

--得到涨前的薪水。

Selectsal into psal from emp where empno = eno;

--涨100元

Updateemp set sal = sal + 100 where empno = eno;

--这里不要提交事务，谁调用谁来提交事务

--打印涨前和涨后的薪水。

Dbms_output.put_line(‘涨前:’||psal||’涨后’||(psal+100));

End;

/

编译。

调用：

Begin

raiseSalary(7839);

raiseSalary(7566);

commit;

end;

/

存储过程和存储函数的区别：

存储过程没有返回值，存储函数可以有一个返回值。

存储函数：

函数为一命名的存储程序，可以带参数，并返回一个计算值。函数和过程的结构类似，但必须有一个return子句，用于返回函数值。函数说明要指定函数名、结果值的类型，以及参数类型等。

语法：

Create [or replace] function 函数名(参数列表)

Return 函数值类型

As

PLSQL子程序体;

--查询某个员工的年收入，通过函数查询，查询完将查询结果返回。

--接收一个员工参数

Create or replace functionqueryEmpIncome(eno in number)

Return number

As

--年收入与月薪和奖金有关，定义两个变量，接收月薪和奖金。

Psalemp.sal%type;

Pcommemp.comm%type;

Begin

--得到该员工的月薪和奖金

Selectsal,comm into psal,pcomm from emp where empno = eno;

--返回年收入

Returnpsal*12+nvl(pcomm,0);

End;

/

编译一下，在左边的树结构中就出现了函数的部分。

直接在图标上点右键选择运行就可以运行。

打开对话框，传参数：

控制台结果：

双击函数，可以打开一个窗口，这个窗口有debug的功能，在侧边双击可以打断点。

Debug需要权限，需要授权。

Grant DEBUG CONNECT SESSION , DEBUG ANY PROCEDUREto scott;

关于输出参数：

存储过程和存储函数都可以通过out指定一个或多个输出参数。我们可以利用out参数，在过程和函数中实现返回多个值。

原则：如果只有一个返回值，用存储函数，否则，就用存储过程。

--out参数的例子。查询并返回某个员工的姓名 月薪 职位

--因为要返回姓名，月薪，职位，就要在参数中定义为输出参数。

Create or replace procedurequeryEmpInfo(eno in number,pename out varchar2,psal out number,pjob outvarchar2)

As

Begin

Selectename,sal,empjob into pename,psal,pjob from emp where empno = eno;

End;

/

编译，在过程上刷新，右键运行。

如何在Java程序中调用存储过程或存储函数呢？

先获取Connection，在获取CallableStatement(Statement的子类)。

通过Connection的prepareCall(String sql)方法创建CallableStatement。

CallableStatement是用来执行SQL存储过程的接口。JDBCAPI提供了一个存储过程SQL转义语法，该语法允许对所有RDBMS使用标准方式调用存储过程。

调用的SQL语法：

调存储函数：?=call[(,,…)]

掉存储过程：call[(,…)]

调用示例：用PL/SQL比直接使用SQL调用效率要高。

创建工程，导入Oracle的jar包。Jar包位置如地址栏所示。

(1)JDBC工具类

Public class JDBCUtils {

Private static String driver = “oracle.jdbc.OracleDriver”;

Private static String url = “jdbc:oracle:thin:@localhost:1521:orcl”;

Private static String user = “scott”;

Private static String password = “tiger”;

Static {

Try{

Class.forName(driver);

} catch (ClassNotFoundException e) {

e.printStackTrace();

}

}

Public static Connection getConnection() {

Try{

Return DriverManager.getConnection(url,user,password);

} catch (SQLException e) {

e.printStackTrace();

}

Return null;

}

Public static void release(Connection conn,Statement st, ResultSet rs) {

If(rs != null) {

Try{

Rs.close();

} catch(SQLException e) {

e.printStackTrace();

} finally {

Rs = null; //为是么置null呢？需要垃圾回收，gc机制。

}

}

If(st != null) {

Try{

st.close();

} catch(SQLException e) {

e.printStackTrace();

} finally {

st = null;

}

}

If(conn != null) {

Try{

conn.close();

} catch(SQLException e) {

e.printStackTrace();

} finally {

conn = null;

}

}

}

}

Public class TestOracle {

@Test

Public void testProcedure() {

//第一个是输入参数，后三个是输出参数。

String sql = “{call queryEmpInfo(?,?,?,?)}”;

Connection conn = null;

CallableStatement call = null;

Try {

Conn = JDBCUtils.getConnection();

Call = conn.prepareCall(sal);

//对于in参数赋值

Call.setInt(1,7839);

//对于out参数。在执行后才有值。通过OracleTypes中的常量可以将Oracle的类型转换成Java的类型

Call.registerOutParameter(2,OracleTypes.VARCHAR);

Call.registerOutParameter(3,OracleTypes.NUMBER);

Call.registerOutParameter(4,OracleTypes.VARCHAR);

//执行

Call.execute();

//取出结果。

String name = call.getString(2);

double sal = call.getDouble(3);

String job = call.getString(4);

System.out.println(name);

System.out.println(sal);

System.out.println(job);

}catch(Exception e){

e.printStackTrace();

}finally{

JDBCUtils.release(conn,call,null);

}

}

@Test

Public void testFunction(){

String sql = “{?=call queryEmpIncone(?)}”;

Connection conn = null;

CallableStatement call = null;

Try {

Conn = JDBCUtils.getConnection();

Call = conn.prepareCall(sal);

//第一个参数是返回值，先处理out参数

//对于out参数。在执行后才有值。通过OracleTypes中的常量可以将Oracle的类型转换成Java的类型

Call.registerOutParameter(1,OracleTypes.NUMBER);

//对于in参数赋值

Call.setInt(2,7839);

//执行

Call.execute();

//取出结果。

double income = call.getDouble(1);

System.out.println(income);

}catch(Exception e){

e.printStackTrace();

}finally{

JDBCUtils.release(conn,call,null);

}

}

}

--返回某个部门的所有员工的所有信息。这样out就太多了

--可以通过在光标中定义。

在out中使用光标，有一个要求，必须将光标和存储过程或者存储函数放到一个包里面。

什么是包？

是一个数据库对象，是包头，包头只负责声明，包体只负责实现。

声明包：

--例：根据员工的员工号查询员工信息，要求返回员工的所有信息。

Create or replace package MYPACKAGE as

--自定义一个类型empcursor，这个类型引用cursor光标类型，也就是说，设个empcursor类型就是一个光标

Type empcursoris ref cursor;

--在out中使用自定义的光标类型

Procedurequeryemp(eid in number,empinfo out empcursor);

End MYPACKAGE;

什么是包体？

也是一个数据库对象，包体要实现存储过程和存储函数。

CREATE OR REPLACE

PACKAGE BODY MYPACKAGE AS

PROCEDUREqueryemp(eid in number,empinfo out empcursor) AS

BEGIN

Openempinfo for select * fro emp where empno = eid;

ENDqueryemp;

END MYPACKAGE;

--返回某个部门的所有员工的所有信息

CREATE OR REPLACE

PACKAGE MYPACKAGE AS

Typeempcursor is ref cursor;

ProcedurequeryEmpList(dno in number,empList out empcursor);

END MYPACKAGE;

--编译好后，在左边的树结构中的程序包节点中就有了刚定义的包。右键，可以选择创建包体。

--会将包头中所有需要实现的程序都列出来。

CREATE OR REPLACE

PACKAGE BODY MYPACKAGE AS

ProcedurequeryEmpList(dno in number,empList out empcursor) AS

BEGIN

--这里open了光标，并没有关闭光标，其实光标关闭了，

--因为在返回rs结果集后，程序关闭了rs，关闭了rs也就关闭了光标。

OpenempList for select * from emp where deptno = dno;

ENDqueryEmpList;

END MYPACKAGE;

//编写测试程序

@Test

Public void testCursor() {

String sql = “{call MYPACKAGE.queryEmpList(?,?)}”;

Connection conn = null;

CallableStatement call = null;

ResultSet rs = null;

Try{

Conn = JDBCUtils.getConnection();

Call = conn.prepareCall(sql);

//对于in参数赋值。部门号

Call.setInt(1,10);

//对于out参数，所有员工的所有信息。

Call.registerOutParameter(2,OracleTypes.CURSOR);

//执行。

Call.execute();

//取出该部门中的员工的信息。Call中没有getCursor，需要转成OracleCallableStatement

Rs = (OracleCallableStatement)call.getCursor(2);

While(rs.next()) {

String name = rs.getString(“ename”);

double sal = rs.getDouble(“sal”);

System.out.println(name + “ ” + sal);

}

} catch(Exception e) {

e.printStackTrace();

} finally {

JDBCUtils.release(conn,call,rs);

}

}

上面的程序可以在MySQL中跑吗？不可以，因为实现的是Oracle的接口。