在oracle中检查进程的进程，使用变量数组

问题描述：

感谢您的帮助，今天我希望我至少帮助过一个人，但我不是在这里为congrat和东西。在oracle中检查进程的进程，使用变量数组

我有一个问题，当我尝试检查数据库的一些过程的状态

我已经进行查询：

DECLARE 
CLAVES varchar(12); 
FECHA date; 

BEGIN 

FECHA := TO_DATE('16/10/2015','DD/MM/YYYY'); 
FOR KEYS IN (
     SELECT SKEY, 
       SUBSTR(RPAD(COD.SCOMMAND,43),41) PROD 
     FROM INSUDB.BATCH_JOB COD, 
       INSUDB.BATCH_PROCESS PRO 
     WHERE COD.NUSERCODE = '3345' 
     AND  COD.NBATCH = PRO.NBATCH 
     AND  COD.NUSERSUBMIT = COD.NUSERCODE 
     AND  TRUNC(COD.DSUBMIT) = TO_DATE ('16/10/2015', 'DD/MM/YYYY') 
     AND  PRO.SCODISPL = 'VIL7002' 
     ORDER BY COD.DSUBMIT ASC 

    )LOOP 
     CLAVES := TRIM(TO_CHAR(KEYS.SKEY)); 

     BEGIN 
      SELECT '01 - EJEC' " ", 
        NPRODUCT PRODUCTO, 
        COUNT(*) CONTAR, 
        TO_CHAR(NVL(TRUNC(MOD((MAX(DCOMPDATE) - MIN(DCOMPDATE))*24,24)),0)) ||' HRS. '|| 
        TO_CHAR(NVL(TRUNC(MOD((MAX(DCOMPDATE) - MIN(DCOMPDATE))*24*60,60)),0)) ||' MIN. '|| 
        TO_CHAR(NVL(TRUNC(MOD((MAX(DCOMPDATE) - MIN(DCOMPDATE))*24*60*60,60)),0)) ||' SEG.' TIEMPO, 
        MIN(DCOMPDATE) , 
        MAX(DCOMPDATE) 
      FROM TIMETMP.TMP_VIL7002 
      WHERE SKEY = CLAVES 
      AND  NSTATUS = 1 
      group by nproduct 
      UNION 
      SELECT '02 - PEND' COL, 
        null, 
        COUNT(*), 
        NULL, 
        NULL, 
        NULL 
      FROM TIMETMP.TMP_VIL7002 
      WHERE SKEY = CLAVES 
      AND  NSTATUS <> 1 
      UNION 
      SELECT '03 - ERR ' COL, 
        null, 
        COUNT(*), 
        NULL, 
        NULL, 
        NULL 
      FROM TIMETMP.TMP_VIL7002 
      WHERE SKEY = CLAVES 
      AND  NSTATUS = 3 
      UNION 
      SELECT '04 - TOTAL' COL, 
        null, 
        COUNT(*), 
        NULL, 
        NULL, 
        NULL 
      FROM TIMETMP.TMP_VIL7002 
      WHERE SKEY = CLAVES; 
     END; 
    END LOOP; 
END;

此只给我一个错误信息

END; 
Error at line 1 
ORA-06550: línea 24, columna 17: 
PLS-00428: an INTO clause is expected in this SELECT statement

答

这正是消息所说的。如果你在PLSQL中执行select语句，你必须对结果做些什么。您可以使用for循环来迭代它（与使用第一个查询时一样），或者添加一个into子句，您可以在其中为查询中的每个列指定一个PLSQL变量。 PLSQL希望执行像内部SQL语句这样的语句。

就像现在这样，它只是执行大的内部查询而没有对结果做任何事情。由于这很愚蠢，PLSQL不允许这样做。

答

当你在PL/SQL中编写SELECT语句时，实际上应该使用SELECT INTO语句，以便结果可以到达某处。

你可以看到如何让一个SELECT INTO这里：

http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/selectinto_statement.htm

答

所以，我终于解决了我的问题，使用此查询，并请勿使用过程

SELECT SKEY, 
    SUBSTR(RPAD(COD.SCOMMAND,43),41) PROD, 
    SUBSTR(RPAD(COD.SCOMMAND,67),66)||'/'||SUBSTR(RPAD(COD.SCOMMAND,65),64)||'/'||SUBSTR(RPAD(COD.SCOMMAND,63),60) FECHA, 
    (SELECT COUNT(*) FROM TIMETMP.TMP_VIL7002 TMP WHERE TMP.SKEY = COD.SKEY AND NSTATUS = 1) EJECUTADOS, 
    (SELECT COUNT(*) FROM TIMETMP.TMP_VIL7002 TMP WHERE TMP.SKEY = COD.SKEY AND NSTATUS <> 1) PENDIENTES, 
    (SELECT COUNT(*) FROM TIMETMP.TMP_VIL7002 TMP WHERE TMP.SKEY = COD.SKEY AND NSTATUS = 3) ERRORES, 
    (SELECT COUNT(*) FROM TIMETMP.TMP_VIL7002 TMP WHERE TMP.SKEY = COD.SKEY) TOTAL, 
    (SELECT MIN(DCOMPDATE) FROM TIMETMP.TMP_VIL7002 TMP WHERE TMP.SKEY = COD.SKEY) INICIO, 
    (SELECT MAX(DCOMPDATE) FROM TIMETMP.TMP_VIL7002 TMP WHERE TMP.SKEY = COD.SKEY) TERMINO 
FROM INSUDB.BATCH_JOB COD, 
    INSUDB.BATCH_PROCESS PRO 
WHERE COD.NUSERCODE = '3345' 
AND  COD.NBATCH = PRO.NBATCH 
AND  COD.NUSERSUBMIT = COD.NUSERCODE 
AND  TRUNC(COD.DSUBMIT) = TO_DATE ('18/10/2015', 'DD/MM/YYYY') -- unico parametro a cambiar es la fecha de ejecucion 
AND  PRO.SCODISPL = 'VIL7002' 
ORDER BY COD.DSUBMIT ASC

在oracle中检查进程的进程，使用变量数组

相关推荐