oracle函数 isex,oracle之存储过程

oracle的版本为11g

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

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

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种存储过程的使用情况,记录学习的脚步!!