Oracle学习(十二)：存储过程/存储函数 - 数据库编程

1.知识点

--第一个存储过程
/*
打印Hello World
create [or replace] PROCEDURE 过程名(参数列表)  
AS 
        PLSQL子程序体；
调用存储过程:
1. exec sayHelloWorld();
2. begin
      sayHelloWorld();
      sayHelloWorld();
   end;
   /
*/
create or replace procedure sayHelloWorld	--sayHelloWorld为过程名
as
--declare
--变量说明
begin
  dbms_output.put_line('Hello World');
end;
/
----------------------------------------------
/*
给指定的员工涨100的工资，并打印涨前和涨后的薪水

create [or replace] PROCEDURE 过程名(参数列表)  --in为输入参数，out为输出参数
AS 
        PLSQL子程序体；
        
SQL> begin
  2     raiseSalary(7839);
  3     raiseSalary(7566);
  4     commit;
  5  end;
  6  /
涨前:7986  涨后:8086
涨前:5024.53  涨后:5124.53

PL/SQL 过程已成功完成。        
*/
create or replace procedure raiseSalary(eno in number)	--带输入参数的存储过程
as
  --变量
  psal emp.sal%type;
begin
  --得到涨前薪水
  select sal into psal from emp where empno=eno;
  
  --涨工资
  update emp set sal=sal+100 where empno=eno;
  --问题：要不要commit？？答：不需要，因为整个事务还没有结束，等调用此存储过程后，再commit

  --打印
  dbms_output.put_line('涨前:'||psal||'  涨后:'||(psal+100));
  
end;
/
-------------------------------------------------------------------
/*
查询某个员工的年收入

CREATE [OR REPLACE] FUNCTION 函数名(参数列表) 
 RETURN  函数值类型
AS
PLSQL子程序体；

*/
create or replace function queryEmpIncome(eno in number)
return number	
as
  --变量
  psal emp.sal%type;
  pcomm emp.comm%type;
begin
  select sal,comm into psal,pcomm from emp where empno=eno;
  return psal*12+nvl(pcomm,0);
end;
/
---------------------------------------------------------------------
--OUT参数
/*
查询某个员工的姓名 月薪 职位

*/
create or replace procedure queryEmpInfo(eno in number,
                                         pename out varchar2,
                                         psal   out number,
                                         pjob   out varchar2)
as
begin
  select ename,sal,empjob into pename,psal,pjob from emp where empno=eno;

end;
/
--------------------------------------------------------------------
--查询某个部门中所有员工的所有信息

--1.创建一个包：MYPACKAGE 
--2.在该包中定义一个自定义类型：empcursor 类型为游标，一个存储过程：queryemp

CREATE OR REPLACE 
PACKAGE MYPACKAGE AS 
  
  type empcursor is ref cursor;	--empcursor是一个自定义类型：引用cursor的类型作为empcursor的类型
  procedure queryEmpList(dno in number,empList out empcursor);	

END MYPACKAGE;
==============================================
CREATE OR REPLACE
PACKAGE BODY MYPACKAGE AS

  procedure queryEmpList(dno in number,empList out empcursor) AS
  BEGIN
      
      open empList for select * from emp where deptno=dno;
    
  END queryEmpList;

END MYPACKAGE;

------------------------------------------------------------------

2.JAVA程序中测试存储过程/存储函数

package demo.util;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;

import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;

import org.junit.Test;

/*
 * 
 * 性能:
 * Statement < PreparedStatement  < CallableStatement
 * PreparedStatement：预编译SQL语句，执行的时候告诉参数，至少编辑一次
 * CallableStatement：完成对存储过程/存储函数的调用，没有编译过程，直接调用
 */
public class TestOracle {
	
	/*
	 * create or replace
procedure queryEmpInfo(eno in number,
                                         pename out varchar2,
                                         psal   out number,
                                         pjob   out varchar2)
	 */
	@Test
	public void testProcedure(){
		//调用存储过程
		//{call [(,, ...)]}
		String sql = "{call queryEmpInfo( , , , )}";
		Connection conn=null;
		CallableStatement call = null;
		try{
			conn = JDBCUtils.getConnection();
			call = conn.prepareCall(sql);
			//赋值
			call.setInt(1, 7839);
			//对于out参数，申明
			call.registerOutParameter(2, OracleTypes.VARCHAR);
			call.registerOutParameter(3, OracleTypes.NUMBER);
			call.registerOutParameter(4, OracleTypes.VARCHAR);
			
			//调用
			call.execute();
			
			//取出结果
			String name = call.getStri

Oracle学习(十二)：存储过程/存储函数(一)