存储过程(Procedure)和函数(Function)及Java调用

[size=medium][color=darkred]存储过程(Procedure)[/color][/size]
[color=olive][size=medium]创建过程的语法：[/size][/color]
CREATE [OR REPLACE] PROCEDURE
<procedure name> [(<parameter list>)]
IS|AS
<local variable declaration>
BEGIN
<executable statements>
[EXCEPTION
<exception handlers>]
END;
[color=indigo][size=small]带输入输出参数[/size][/color]

create or replace procedure user_pro(ssuser in varchar2,vname out varchar2)
as
v_name varchar2(30);
begin
      select t.name into v_name from t_user t where suser=ssuser;
      if v_name!='龚帅' then 
         vname:='龚帅';
      end if; 
end;

--执行过程
declare
v_str varchar2(30);
begin
      user_pro('test9',v_str);
      dbms_output.put_line(v_str);
end;

[color=indigo][size=small]带游标的存储过程[/size][/color]

create or replace procedure cursor_pro
is 
cursor user_cursor is select * from t_user;
begin
       for rs_row in user_cursor 
       loop
           dbms_output.put_line(rs_row.suser);
       end loop;
end;

declare
begin
      cursor_pro;
end;

[size=medium][color=darkred]函数(Function)[/color][/size]

[color=olive][size=medium]创建函数的语法：[/size][/color]

CREATE [OR REPLACE] FUNCTION
<function name> [(param1,param2)]
RETURN <datatype> IS|AS
[local declarations]
BEGIN
Executable Statements;
RETURN result;
EXCEPTION
Exception handlers;
END;

create or replace function user_fun(ssuser varchar2)
return varchar2
is
v_name varchar2(30);
begin
     select t.name into v_name from t_user t where suser=ssuser; 
     return v_name;
end;

[color=olive][size=medium]函数的两种调用方式pl/sql和sql语句[/size][/color]

declare
v_name varchar2(30);
begin 
     v_name:=user_fun('test9');
     dbms_output.put_line(v_name);
end;

select user_fun('test9') as 用户名 from dual;

[size=medium][color=darkred]程序包(Packages)与程序包主体(Packages Bodies)[/color][/size]

create or replace package my_user
as
  cursor my_cursor(ssuser varchar2) return t_user%rowtype;
  procedure my_pro(ssuser in varchar2,v_str out varchar2);
  function my_fun(ssuser varchar2) return varchar2;
end;

create or replace package body my_user
as
  cursor my_cursor(ssuser varchar2) return t_user%rowtype is
         select * from t_user where suser=ssuser;      
  procedure my_pro(ssuser in varchar2,v_str out varchar2)
  as
  --v_name t_user.username%type;
  rs_row t_user%rowtype;
  begin
       --select username into v_name from t_user where userid=user_id;
       --dbms_output.put_line(v_name);
       open my_cursor(ssuser);
            loop
                fetch my_cursor into rs_row;
                exit when my_cursor%notfound;
                v_str:=rs_row.name;
                if v_str!='龚帅' then
                   v_str:='龚帅';  
                end if;
            end loop;
       close my_cursor;
  end;

  function my_fun(ssuser varchar2)
  return varchar2
  as
  my_name t_user.name%type;
  begin
       select t.name into my_name from t_user t where suser=ssuser;
       return my_name;
  end;
end;

declare
v_str varchar2(30);
begin
     dbms_output.put_line('FUN---'||my_user.my_fun('test9'));
     my_user.my_pro('test6',v_str);
     dbms_output.put_line('PRO---'||v_str);
end;

[color=olive][size=medium]Java调用存储过程[/size][/color]

public String getNamePro(String suser) {
	String name = null;
	try {
		CallableStatement cstmt = this.getHibernateTemplate()
				.getSessionFactory().getCurrentSession().connection()
				.prepareCall("{call fantasy.user_pro(?,?)}");
		cstmt.setString(1, suser);
		cstmt.registerOutParameter(2, Types.VARCHAR);
		cstmt.execute();
		name = cstmt.getString(2);
	} catch (Exception e) {
		e.printStackTrace();
	}
	return name;
}

[color=olive][size=medium]Java调用函数[/size][/color]

public String getNameFun(String suser) {
	String name = null;
	try {
		CallableStatement cstmt = this.getHibernateTemplate()
		.getSessionFactory().getCurrentSession().connection()
		.prepareCall("{?=call fantasy.user_fun(?)}");
		cstmt.registerOutParameter(1, Types.VARCHAR);
		cstmt.setString(2, suser);
		cstmt.execute();
		name=cstmt.getString(1);
	} catch (Exception e) {
		e.printStackTrace();
	}
	return name;
}