oracle 包，函数，过程，块的创建和执行及在java中执行

浏览数：55 / 时间：2015年06月12日

SQL> create or replace procedure sp_guocheng1 is--如果有这个名字就替换
2 begin--执行部分
3 insert into guocheng values(‘liyifeng‘,‘liyifeng‘);
4 end;
5 / --执行的意思

Procedure created

显示错误
SQL> show error;
Errors for PROCEDURE LIYIFENG.SP_GUOCHENG1:

LINE/COL ERROR
-------- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

--------------------------------------
3/22     PL/SQL: ORA-00928: 缺失 SELECT 关键字
3/1      PL/SQL: SQL Statement ignored
6/0      PLS-00103: 出现符号 "end-of-file"在需要下列之一时：   ( begin case     declare end exception exit for goto if loop mod null pragma     raise return select update while

with <an identifier> <a double-quoted delimited-identifier> <a bind variable> << continue close current delete fetch lock insert open rollback savepoint set sql

execute commit forall merge pipe purge

执行过程

SQL> exec sp_guocheng1;

PL/SQL procedure successfully completed

SQL> commit;

一个新的快
SQL> set serveroutput on;--打开输出选项
SQL>
SQL> begin
2 dbms_output.put_line(‘hello,world‘);--dbms_output是包.put_line是过程
3 end;
4 /

hello,world

PL/SQL procedure successfully completed

带有变量的查询
SQL> declare
2 v_ename varchar2(25);
3 v_id number;
4 begin
5    select name,id into v_ename,v_id from stu where name=&aa;
6    dbms_output.put_line(‘用户名是：‘||v_ename||v_id );
7 end;
8 /

用户名是：liyifeng            0

PL/SQL procedure successfully completed

带有类外的查询
SQL> declare
2 v_ename varchar2(25);
3 v_id number;
4 begin
5    select name,id into v_ename,v_id from stu where name=&aa;
6    dbms_output.put_line(‘用户名是：‘||v_ename||v_id );
7 exception
8    when no_data_found then
9      dbms_output.put_line(‘您输入的数据不存在！‘);
10 end;
11 /

您输入的数据不存在！

PL/SQL procedure successfully completed

--传入参数的过程注意，定义参数的时候不能带上长度。
create procedure sp_gc1(v_id number,v_name varchar2) is
begin
update stu set name=v_name where id=v_id;
end;

过程在java中运行。。注意jdbc_oracle架包。。
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class testExec {

/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub

  Connection ct=null;
  try{
   //加载驱动
//  Class.forName("com.hxtt.sql.access.AccessDriver");//连接access的
  Class.forName("oracle.jdbc.driver.OracleDriver");
  //创建连接
  ct=DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.9:1521:orcl","liyifeng","liyifeng");
  //创建 callablestatement
  CallableStatement cs=ct.prepareCall("{call sp_gc1(?,?)}");
  //给？复制
  cs.setInt(1, 0);
  cs.setString(2, "mountLee");
  //设置不自动提交事务
  ct.setAutoCommit(false);

  cs.execute();

  //提交事务
  ct.commit();
  //关闭资源
  cs.close();
  ct.close();
  }
  catch (Exception e){
   try {
   //如果失败，就回滚
   ct.rollback();
   }
   catch(Exception ex){ex.printStackTrace();}

   e.printStackTrace();
  }
}

}

--传入参数并有返回值的函数
create or replace function sp_guoc(v_id number)
return varchar2 is v_name varchar2(20);
begin
select name into v_name from stu where id=v_id;
return v_name;
end;

java中调用函数

Statement sm=ct.createStatement();
  ResultSet rs=sm.executeQuery("select sp_guoc(0) from stu");
  if(rs.next()){
  System.out.print(rs.getString(1));}

--创建包
create or replace package sp_pack is
procedure sp_pd (v_id number,v_name varchar2);
function sp_ft (v_id number) return number;
end;

--创建包体
create or replace package body sp_pack is
procedure sp_pd(v_id number,v_name varchar2) is
begin
update stu set name=v_name where id=v_id;
end;
function sp_ft(v_id number)
return number is v_name number;
begin
select id into v_name from stu where id=v_id;
return v_name;
end;
end;

执行包中的过程

SQL> exec sp_pack.sp_pd(0,‘lee‘);

PL/SQL procedure successfully completed

SQL> commit;

Commit complete

java中执行包中函数的方法。

Statement sm=ct.createStatement();
  ResultSet rs=sm.executeQuery("select sp_pack.sp_ft(0) from stu");
  if(rs.next()){
  System.out.print(rs.getString(1));}