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

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

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;

带游标的存储过程

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;

函数(Function)

创建函数的语法：

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;

函数的两种调用方式pl/sql和sql语句

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;

程序包(Packages)与程序包主体(Packages Bodies)

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;

Java调用存储过程

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;
}

Java调用函数

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;
}