--过程,但不适合使用临时表作结果返回,因表记录不会删除且存在同一个用户在不同客户端登录如果
不删除结果,会造成结果不对
CREATE OR REPLACE Procedure P_TempTable(EMPNo in varchar2,EName in varchar2, cur_arg out sys_refcursor)
Authid Current_User
is
--declare is后面直接 定义变量不用 declare 调用时才用这个
v_StrSql varchar2(2000);
v_Condition varchar2(200);
vcount Number;
vNo varchar2(50);
vName varchar2(50);
begin
select count(1) into vcount from user_tables where table_Name =Upper('tab_tempEmp');
if vcount =0 then
v_StrSql :='create global temporary table tab_tempEmp
(EMPNo Varchar2(50),
EName Varchar2(50)
) on Commit Preserve Rows' ; --COMMIT DELETE ROWS Commit Preserve Rows global
dbms_output.put_line(v_StrSql);
Execute immediate v_StrSql;
end if;
/*
select count(*) into vcount from tab_tempEmp where rownum<2;
if vcount >0 then
delete from tab_tempEmp;
Commit;
end if;
*/
v_StrSql :='select EMPNO,EName from EMP where 1=1 ';
if (EMPNo is not null) then --an
ClientDataSet调用Oracle过程
最新推荐文章于 2023-09-14 13:40:28 发布