如題所述,使用Java代碼執行刪除、創建臨時表的存儲過程時總是執行不成功(不報錯,
但是獲取的影響行數為-1),別的存儲過程都可以執行成功,求大師指點啊,
存儲過程和Java代碼如下:
1、存儲過程代碼
ALTER PROCEDURE [dbo].[PROC_TEMP]
AS
BEGIN
if object_id('tempdb..##temp') is not null
Begin
DROP TABLE ##temp
End
select identity(int) id0,* into ##temp from AlarmRecordRe ORDER BY [TIMESTAMP] DESC
END
2、Java代碼
String sql = "{call PROC_TEMP}";
CallableStatement proc = conn.prepareCall(sql);
proc.execute();
int tmp = proc.getUpdateCount();
System.out.println(tmp);//這裡打印出來的總是-1
if (tmp<0)
{
System.out.println("存儲過程執行失敗");
proc.close();
DBPool.destroyConnection(conn);
return;
}
System.out.println("成功了啊");
proc.close();
執行動態語句的話:
mysql:
create procedure p_drop_table_if_exist_v2(p_table_name varchar(20))
begin
declare v_table_name varchar(20);
declare exit handler for not found begin end;
select distinct table_name into v_table_name from information_schema.columns WHERE table_name = p_table_name;
if length(v_table_name)>0 then
set @sql=concat('drop table ', p_table_name);
prepare dtb from @sql;
execute dtb;
deallocate prepare dtb;
end if;
end;
//
oracle:
procedure p_drop_table_if_exist_v2(p_table_name in varchar2)
is
v_table_name varchar2(20);
begin
select table_name into v_table_name from user_tables where table_name=upper(p_table_name);
if length(v_table_name)>0 then
execute immediate 'drop table ' || p_table_name || ' purge';
end if;
exception
when no_data_found then
begin
null;
end;