梦幻之旅

DEBUG - 天道酬勤

   :: 首页 :: 新随笔 :: 联系 :: 聚合  :: 管理 ::
  671 随笔 :: 6 文章 :: 256 评论 :: 0 Trackbacks
CREATE OR REPLACE
PACKAGE BODY "EDM_PACK" AS
  PROCEDURE get_inc(
    i_user_id IN NUMBER,
    o_inc OUT ginc)
    IS
      CURSOR id_name IS SELECT id,name FROM groups WHERE user_id = i_user_id;
      i_n id_name%ROWTYPE;
      temp_count NUMBER;
      temp_id NUMBER;
      temp_name VARCHAR2(2000);
      temp_sql VARCHAR2(2000);
      temp_exist NUMBER;
    BEGIN
      OPEN id_name;
      LOOP
        FETCH id_name INTO i_n;
        EXIT WHEN id_name%NOTFOUND;
        SELECT COUNT(*) INTO temp_exist FROM USER_TABLES WHERE ;
        IF temp_exist=1 THEN
          tmp_sql := 'SELECT COUNT(*) FROM '||'EMAILS_'||i_n.id;
          EXECUTE IMMEDIATE tmp_sql INTO temp_count;
        ELSE
          temp_count := -1;
        END If;
        INSERT INTO tmp NOLOGGING VALUES('||i_n.id||','||i_n.name||','||temp_count||');
      END LOOP;
      CLOSE id_name;
      OPEN o_inc FOR SELECT group_id,group_name,email_count FROM tmp;
      EXECUTE Immediate 'DROP TABLE tmp';
    END get_inc;
END;
posted on 2007-12-20 19:47 HUIKK 阅读(82) 评论(0)  编辑  收藏 所属分类: DataBase

只有注册用户登录后才能发表评论。


网站导航: