如何使用PLSQL存储过程建表

Question

CREATE TABLE TEMP_DCY_DDM_GZ AS
SELECT AA.DEV_ID,AA.RX_POWER,AA.TX_POWER,AA.P_RX_POWER,AA.P_TX_POWER,
AA.DN_ATTN,AA.UP_ATTN,AA.COLLECT_TIME,AA.达标情况
FROM
(SELECT A.DEV_ID,A.RX_POWER,A.TX_POWER,A.P_RX_POWER,A.P_TX_POWER,
A.DN_ATTN,A.UP_ATTN,A.COLLECT_TIME,
CASE WHEN A.RX_POWER IS NULL OR A.RX_POWER =0 then '无光功率数据'

WHEN A.RX_POWER<-27 THEN '不达标'
ELSE '达标' END 达标情况,
ROW_NUMBER()OVER(PARTITION BY A.DEV_ID ORDER BY A.COLLECT_TIME DESC)ROW_NUM
FROM IAM.T_PERF_PON_DDM@gdaniam A
WHERE
EXISTS
(SELECT 1 FROM IAM.T_RES_DEVICE@gdaniam B
WHERE A.DEV_ID=B.DEV_ID AND B.AREA_ID='GD-GZ'))AA
WHERE AA.ROW_NUM<2;

CREATE TABLE gz_res_power AS
SELECT E.LINE_ID 接入号,
E.ACCOUNT || '@' || E.DOMAIN 账号,
G.ONUSN,
C.RX_POWER ONU接收光功率,
C.TX_POWER ONU发送光功率,
C.P_RX_POWER 对端OLT接收光功率,
C.P_TX_POWER 对端OLT发送光功率
FROM TEMP_DCY_ONU_GZ_NEW A,
TEMP_ANIAM_ONU_GZ B,
TEMP_DCY_DDM_GZ C,
CCATSTEP_TBL_LINEINFO@guangzhou E,
CCATSTEP_TBL_NE@guangzhou G
WHERE A.NE_NAME = B.DEV_NAME
AND B.DEV_ID = C.DEV_ID;
请教各位，如何写存储过程来建这两张表。

Answer 1

存储过程里建表要用 execute immediate；
比如
create PROCEDURE test is
begin
execute immediate 'CREATE TABLE TEMP_DCY_DDM_GZ AS
SELECT AA.DEV_ID,AA.RX_POWER,AA.TX_POWER,AA.P_RX_POWER,AA.P_TX_POWER,AA.DN_ATTN,AA.UP_ATTN,AA.COLLECT_TIME,AA.达标情况
FROM (SELECT A.DEV_ID,A.RX_POWER,A.TX_POWER,A.P_RX_POWER,A.P_TX_POWER,A.DN_ATTN,A.UP_ATTN,A.COLLECT_TIME,
CASE WHEN A.RX_POWER IS NULL OR A.RX_POWER =0 then ''无光功率数据'' WHEN A.RX_POWER<-27 THEN ''不达标'' ELSE ''达标'' END 达标情况,
ROW_NUMBER() OVER(PARTITION BY A.DEV_ID ORDER BY A.COLLECT_TIME DESC) ROW_NUM
FROM IAM.T_PERF_PON_DDM@gdaniam A
WHERE EXISTS (SELECT 1 FROM IAM.T_RES_DEVICE@gdaniam B WHERE A.DEV_ID=B.DEV_ID AND B.AREA_ID=''GD-GZ'')
)AA
WHERE AA.ROW_NUM<2';
end;