This is an old revision of the document!


ORACLE SQL QUERIES : PL/SQL

DECLARE
   c1 NUMBER;
   c2 NUMBER;
   c3 NUMBER;
--    ressult testing_binds%ROWTYPE;
--    inttype NUMBER := 4001;
   theSQL    VARCHAR2(2000);
BEGIN
    theSQL := '
    SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel OPT_ESTIMATE(@"innerQuery", TABLE, "T_ITS", SCALE_ROWS=21196.12092) */
    c1,
    c2,
    c3
FROM
    (
        SELECT /*+ qb_name("innerQuery") INDEX( "T_ITS" "IT_TBUFFER_SEARCH_UX3_R") */
            COUNT(*) AS c1,
            4294967295 AS c2,
            SUM(
                CASE
                    WHEN("T_ITS"."INTTYPE" =:b1) THEN 1
                    ELSE 0
                END
            ) AS c3
        FROM
            voxel."T_IT_TBUFFER_SEARCH" "T_ITS"
        WHERE
            ( "T_ITS"."INTTYPE" =:b1 )
    ) innerquery' ;
    EXECUTE immediate theSQL INTO c1, c2, c3 USING 4001, 4001;
END;
/
  • oracle/oracle_sql_querys/pl_sql.1539168477.txt.gz
  • Last modified: 2018/10/10 12:47
  • by dodger