动态SQL的学习

 ---下面是本人在学习中遇到的动态SQL在这里记录一下：

包 giti_get_avg_pkg

/*均匀性计算加权平均数和平均数*/

   ----------------------------------------------------

   FUNCTION get_avg(p_column    IN VARCHAR

                   ,p_date      IN VARCHAR

                   ,p_item_code IN VARCHAR

                   ,p_fac_flag  IN NUMBER) RETURN NUMBER IS

      RESULT      NUMBER;

      l_count     NUMBER;             --计算：记录行数

      l_fz        NUMBER := 0; --计算：分子

      l_test_date VARCHAR2(100);

      l_item_code VARCHAR2(100);

      l_fac_flag  NUMBER;

      l_column    VARCHAR2(100);

      -- l_column2   VARCHAR2(100);

      l_sigma  NUMBER;

      l_sqlstr VARCHAR2(1000);

         --动态游标的第一步：定义游标变量

      TYPE my_cursor IS REF CURSOR;

      my_cur my_cursor;

  

   BEGIN

      l_test_date := p_date;

      l_item_code := p_item_code;

      l_fac_flag  := p_fac_flag;

      l_column    := p_column;

      -----------------计算记录行数------------------

      --INTO l_count

      /*  l_sqlstr := 'SELECT COUNT(*)

       FROM bg_jy_quality_v bdq

      WHERE bdq.test_date = to_date(''' || :test_date || ''', ''yyyy - mm - dd'')

      AND bdq.item_code =''' || p_item_code || '''

      AND bdq.organization_id = nvl(''' || p_fac_flag || ''', bdq.organization_id)

      AND ''' || l_column2 || '''IS NOT NULL';*/

  

                

     l_sqlstr := ' SELECT COUNT(*) ' || ' FROM  bg_jy_quality_v t ' ||

                  ' WHERE t.test_date       = to_date(''' || l_test_date || ''',''yyyy-mm-dd'') ' ||

                  ' AND   t.item_code       = ''' || l_item_code || '''' ||

                  ' AND   t.organization_id = nvl(:3, t.organization_id) ' ||

                  ' AND '|| l_column || ' IS NOT NULL ' ;            

   --单行查询时候用：

      EXECUTE IMMEDIATE l_sqlstr

         INTO l_count

         USING l_fac_flag;

  

      ------------计算平均数----------------------------

      IF l_count = 0 THEN

         RESULT := 0;

      ELSE

         /*l_test_date := p_date;

         l_item_code := p_item_code;

         l_fac_flag  := p_fac_flag;

         l_column    := p_column;*/

            ----打开游标

         OPEN my_cur FOR 'SELECT  ' || l_column || '

              FROM gt.bg_jy_quality_V t

              WHERE t.test_date = to_date(''' || l_test_date || ''', ''yyyy-mm-dd'')

              AND t.item_code = ''' || l_item_code || ''' AND t.organization_id = ' ||            l_fac_flag;

     

         LOOP

               --循环提取数据

            FETCH my_cur

               INTO l_sigma;

            EXIT WHEN my_cur%NOTFOUND;

            l_fz := l_fz + nvl(l_sigma, 0);

         END LOOP;

         RESULT := l_fz / l_count;

      END IF;

           ---关闭游标变量

      RETURN RESULT;

   EXCEPTION

      WHEN OTHERS THEN

         RETURN 0;

   END;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13996372/viewspace-615305/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13996372/viewspace-615305/