各位大虾
是这样的,我要写个存储过程,通过传入的参数从2张表里取一堆数据出来,请问用什么来存储这些数据呢?临时表?
execute immediate 'CREATE GLOBAL TEMPORARY TABLE tt_Table AS (SELECT JiaTingXXDZBM FROM WG_YongHu2 A,(SELECT * FROM vWG_XiangXiDZ WHERE WangGeBM=P_WangGeBM) B WHERE A.WangGeBM=B.WangGeBM AND YongHuBM = P_YongHuBH)ON COMMIT DELETE ROWS;'; 这样的话tt_Table 不可用吧,有啥好的办法解决吗?在线等待SOS
是这样的,我要写个存储过程,通过传入的参数从2张表里取一堆数据出来,请问用什么来存储这些数据呢?临时表?
execute immediate 'CREATE GLOBAL TEMPORARY TABLE tt_Table AS (SELECT JiaTingXXDZBM FROM WG_YongHu2 A,(SELECT * FROM vWG_XiangXiDZ WHERE WangGeBM=P_WangGeBM) B WHERE A.WangGeBM=B.WangGeBM AND YongHuBM = P_YongHuBH)ON COMMIT DELETE ROWS;'; 这样的话tt_Table 不可用吧,有啥好的办法解决吗?在线等待SOS
--使用视图:create view v_test1 as
SELECT JiaTingXXDZBM
FROM WG_YongHu2 A,
(SELECT * FROM vWG_XiangXiDZ WHERE WangGeBM = P_WangGeBM) B
WHERE A.WangGeBM = B.WangGeBM
AND YongHuBM = P_YongHuBH;
--给了个例句,请LZ看一下:SQL> select * from v_test; ID NAME
---------- --------
1 张三
2 李四
3 王五
4
赵六SQL>
SQL>
SQL> select * from tabcopy; ID NAME SAL BIRTHDATE
---------- -------- ---------- -----------
1 4 SQL> select * from tabcopy4; ID NAME
---------- --------
1 张三
2 李四
3 王五
4
赵六SQL> create or replace view v_test as select b.* from tabcopy a,tabcopy4 b where a.id=b.id;View createdSQL> select * from v_test; ID NAME
---------- --------
1 张三SQL>
必须这样写了 execute immediate 'create or replace view v_test as select b.* from tabcopy a,tabcopy4 b where a.id=b.id;
' v_test同样不可用啊