oracle之存储过程

   oracle的版本为11g

官方描述:   http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/procedure.htm#i35564 

  存储过程实际上是属于子过程的一种特例,用于执行特定的操作

本文主要涉及到如下几个知识点:

1.创建一个不带参数的存储过程以及调用

2.创建一个带输入参数的存储过程以及调用

3.创建一个带输入输出参数的存储过程以及用java代码进行调用

4.创建一个带输入参数,输出返回一个结果集的存储过程以及用java代码调用

  在执行所有的操作之前,创建一个用户,并创建一个表空间以及分配角色,并创建一张表

以下操作,都是以sys用户登录进行操作

创建一个表空间:

--创建一个表空间
--初始大小为50m 自动增长 每一次增长大小为50m 表空间最大为1024m 本地管理表空间
create tablespace under_data
       datafile 'E:\app\undergrowth\oradata\under\under.dbf'
       size 512m
       autoextend on
       next 50m maxsize 1024m
       extent management local;

创建一个用户,指定默认表空间:

--创建一个用户,用户名和密码均为under_test  默认表空间为under_data
create user under_test identified by under_test
  default tablespace under_data;

为用户授予角色:

--为用户授予角色
grant connect,resource to under_test;

   这里可以查看一下connect,resource角色都有哪些权限

   

  --查看角色有哪些权限
select * from dba_sys_privs where grantee='CONNECT';

  CONNECT角色具有连接数据库的权限

select * from dba_sys_privs where grantee='RESOURCE';

 RESOURCE角色具有创建表、序列、存储过程、触发器、视图等的权限

现在切换到under_test用户上

     

--切换用户
conn under_test/under_test;

  在under_test用户下,创建一个表

--创建表
create table under_test
(
       uname varchar2(20),
       usex varchar2(4),
       uage number(3,0),
       ubirthday date
);

插入数据

--插入数据
insert into under_test values('undergrowth','男',22,to_date('1988-2-2','yyyy-mm-dd'));
insert into under_test values('刘德华','男',52,to_date('1961-2-2','yyyy-mm-dd'));

到目前为止,准备工作完毕,开始存储过程的编写.

1.创建一个不带参数的存储过程以及调用

   

--创建不带参数的存储过程
--or replace 可以不要,加上的原因是当存储过程存在的话 就替换
create or replace procedure under_pro1
is
--声明部分,用于声明变量
--执行部分 从begin开始
begin
  --在控制台输出信息 dbms_output为系统的一个预定义的包  put_line为包中的一个子过程
  dbms_output.put_line('hello,存储过程');
end;
--用于执行创建 
/ 

  调用:  

--调用
exec under_pro1;

但是会发现控制台没有信息输出 因为控制台的输出关掉了  使用 set serveroutput on;  打开控制台输出信息 即可看到hello,存储过程这一句话了

2.创建一个带输入参数的存储过程以及调用

   

--创建带输入参数的存储过程
--or replace 可以不要,加上的原因是当存储过程存在的话 就替换
--iname in varchar2 指定输入参数为iname 数据类型为varchar2,in 关键字可以不要 默认就为in
create or replace procedure under_pro2(iname in varchar2)
is
--定义了一个变量 数据类型和under_test表的ubirthday字段的数据类型一致
 v_birthday under_test.ubirthday%type;
begin
  --根据输入的参数值 将iname的ubirthday赋值给v_birthday变量
  select ubirthday into v_birthday from under_test where uname=iname;
  --文本信息用''括起 不能用""  ||的作用起到字符串的连接作用
  dbms_output.put_line('姓名:' || iname || '生日:' || v_birthday);
end;
/

调用: 

--调用
exec under_pro2('刘德华');

  

3.创建一个带输入输出参数的存储过程以及用java代码进行调用

 

--创建一个带输入输出参数的存储过程以及用java代码进行调用
create or replace procedure under_pro3(iname in varchar2,oage out number,obirthday out date)
is
begin
  select uage,ubirthday into oage,obirthday from under_test where uname=iname;
end;
/

 编写java代码 ： JavaCallPro.java

     

package com.undergrowth;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.SQLException;

public class JavaCallPro {

	/**
	 * @param args
	 *
	 */
	private static String className="oracle.jdbc.driver.OracleDriver";
	private static String url="jdbc:oracle:thin:@localhost:1521:under";
	private static String user="under_test";
	private static String password="under_test";
	private static Connection con=null;
	private static CallableStatement cs=null;
	
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		try{
		//1.注册驱动
		Class.forName(className);
		//2.获取连接
		con=DriverManager.getConnection(url, user, password);
		//3.准备调用存储过程
		cs=con.prepareCall("{call under_pro3(?,?,?)}");
		  cs.setString(1, "刘德华");
		  cs.registerOutParameter(2, oracle.jdbc.OracleTypes.INTEGER);
		  cs.registerOutParameter(3, oracle.jdbc.OracleTypes.DATE);
		//4.执行
		cs.execute();
		//5.获取结果 
		Integer age=cs.getInt(2);
		Date birthday=cs.getDate(3);
		System.out.println("姓名:刘德华"+" 年龄:"+age+" 生日:"+birthday);
		}catch(Exception e)
		{
			e.printStackTrace();
		}finally{
			//6.关闭资源
			
				try {
					if(cs!=null) cs.close();
					if(con!=null) con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			
		}
		
		
	}

}

  不要忘记了添加ojdbc6.jar包

控制台输出:

姓名:刘德华 年龄:52 生日:1961-02-02

4.创建一个带输入参数,输出返回一个结果集的存储过程以及用java代码调用

   

--创建一个带输入参数,输出返回一个结果集的存储过程以及用java代码调用
--创建一个包 包中自定义了一个引用游标 用于返回结果集
create or replace package under_pac is
       type under_cursor is ref cursor;
end;
/

create or replace procedure under_pro4(isex in varchar2,ocursor out under_pac.under_cursor)
is
begin
  --打开游标 让游标指向select * from under_test where usex=isex的结果集
  open ocursor for select * from under_test where usex=isex;
end;
/

 java代码:

package com.undergrowth;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class JavaCallPro {

	/**
	 * @param args
	 *
	 */
	private static String className="oracle.jdbc.driver.OracleDriver";
	private static String url="jdbc:oracle:thin:@localhost:1521:under";
	private static String user="under_test";
	private static String password="under_test";
	private static Connection con=null;
	private static CallableStatement cs=null;
	
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		try{
		//1.注册驱动
		Class.forName(className);
		//2.获取连接
		con=DriverManager.getConnection(url, user, password);
		//3.准备调用存储过程
		cs=con.prepareCall("{call under_pro4(?,?)}");
		  /*cs.setString(1, "刘德华");
		  cs.registerOutParameter(2, oracle.jdbc.OracleTypes.INTEGER);
		  cs.registerOutParameter(3, oracle.jdbc.OracleTypes.DATE);*/
		cs.setString(1, "男");
		cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
		//4.执行
		cs.execute();
		//5.获取结果 
		ResultSet rs=(ResultSet) cs.getObject(2);
		while(rs.next())
		{
			System.out.println("姓名:"+rs.getString(1)+"\t性别:"+rs.getString(2)+"\t年龄:"+rs.getInt(3)+"\t生日:"+rs.getDate(4));
		}
		}catch(Exception e)
		{
			e.printStackTrace();
		}finally{
			//6.关闭资源
			
				try {
					if(cs!=null) cs.close();
					if(con!=null) con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			
		}
		
		
	}

}

控制台输出:

姓名:undergrowth	性别:男	年龄:22	生日:1988-02-02
姓名:刘德华	性别:男	年龄:52	生日:1961-02-02

    以上即是4种存储过程的使用情况,记录学习的脚步!!