Oracle pl/sql块实例

  1、pl/sql块包括三个部分，定义部分，执行部分，异常处理部分，以下是一个例子：

declare

 --定义变量（可选）

  v_name varchar2(20);

  v_sal number;

 begin

  --执行部分

  select name,sal into v_name,v_sal from user1 where id=2;

  dbms_output.put_line('查询到的用户名为:'||v_name);

  --异常处理（可选）

 exception

  when no_data_found then

    dbms_output.put_line('查询不到制定的用户');

 end;

2、存储过程示例：

(1)创建一个名称为prc_user 的不带参数的存储过程：

 

 create or replace procedure prc_user is

 begin

 update user1 set sal=11 where id=1;

 end;

对应的java代码调用如下：

 

import java.sql.*;
public class DB {
	public static void main(String[] args) {
		try {
			//1.加载驱动
			Class.forName("oracle.jdbc.driver.OracleDriver");
			//2.建立数据库连接
			Connection con=DriverManager.getConnection(
					"jdbc:oracle:thin:@127.0.0.1:1521:orcl","cxq", "cxq123");
			//3.创建CallableStatement
		    CallableStatement cst=con.prepareCall("{call prc_user}");
		    //4.执行
		    cst.execute();
		    //4.关闭
		    cst.close();
		    con.close();
		} catch (Exception e) {
			e.printStackTrace();
 		}
	}
}

 

(2)创建一个名称为prc_user1 的带输入参数的存储过程：

 create or replace procedure prc_updateUserSal(userId IN number,newSal IN number)

 is

 begin

 update user1 set sal=newSal where id=userId;

 end;

注意：输入参数IN是可以省略的，默认就是IN。

对应的java代码调用如下：

  

import java.sql.*;
public class DB {
	public static void main(String[] args) {
		try {
			//1.加载驱动
			Class.forName("oracle.jdbc.driver.OracleDriver");
			//2.建立数据库连接
			Connection con=DriverManager.getConnection(
					"jdbc:oracle:thin:@127.0.0.1:1521:orcl","cxq", "cxq123");
			//3.创建CallableStatement
		    CallableStatement cst=con.prepareCall("{call prc_updateUserSal(1,3500)}");
		    
		    /*或者这样
		    3.创建CallableStatement
		    CallableStatement cst=con.prepareCall("{call prc_updateUserSal(?,?)}");
		    cst.setInt(1, 1);
		    cst.setInt(2, 3500);
		    */
		    //4.执行
		    cst.execute();
		    //4.关闭
		    cst.close();
		    con.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

 

(3)创建一个名称为prc_user2 的带输入参数和输出参数的存储过程：

create or replace procedure prc_getUserSal(userId IN number,userSal OUT number) is

begin

select sal into userSal from user1 where id=userId;

end;

对应的java代码调用如下：

3、函数示例：

(1)输入用户的姓名，返回用户的年薪：

create or replace function f_getUserYearSal(userName varchar2) return number

as

yearSal number;

begin

  select sal*12 into yearSal from user1 where name=userName;

  return yearSal;

end;

在命令行中调用函数如下：

SQL> var yearSal number;

SQL> call f_getUserYearSal('cxq') into:yearSal;

返回结果：

Method called

yearSal

---------

60000

如果没返回就打印变量：

SQL> print yearSal; 

会返回结果：

yearSal

---------

60000

对应的java代码调用如下：

import java.sql.*;
public class Test {
	public static void main(String[] args) {
		try {
			//1.加载驱动
			Class.forName("oracle.jdbc.driver.OracleDriver");
			//2.建立数据库连接
			Connection con=DriverManager.getConnection(
					"jdbc:oracle:thin:@127.0.0.1:1521:orcl","cxq", "cxq123");
			//3.创建PreparedStatement
			String sqlStr="select f_getUserYearSal(?) from dual";
		    PreparedStatement psmt=con.prepareStatement(sqlStr);
		    psmt.setString(1, "cxq");
		    //4.执行查询
		    ResultSet rs=psmt.executeQuery();
		    //5.处理结果集
		    while(rs.next()){
		    	int yearSal=rs.getInt(1);
		    	System.out.println("用户cxq的年薪为"+yearSal);
		    }
		    //6.关闭
		    psmt.close();
		    con.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}