ora2c20070724.sql

c#数据库访问自动生成代码程序

     v_str VARCHAR2(4096);
  BEGIN
     IF v_parastr IS NULL THEN
        v_paracnt:=0;
        RETURN;
     END IF;
     v_str:=v_parastr;
     v_paracnt:=0;
     v_i:=1;
     v_iloc:=instr(v_str,',');
     WHILE v_iloc>0  LOOP

           v_tab(v_i):=substr(v_str,1,v_iloc-1);
           v_i:=v_i+1;
           v_str:=substr(v_str,v_iloc+1);
           v_iloc:=instr(v_str,',');
     END LOOP;
     v_tab(v_i):=v_str;
     v_paracnt:=v_i;
  END;

  PROCEDURE getPara(v_parastr VARCHAR2,v_paraname OUT arraystr,
  v_paratype OUT arraystr, v_paradir OUT arraystr, v_paracnt IN OUT BINARY_INTEGER)
  AS
     v_tab arraystr;
     v_iloc BINARY_INTEGER;
     v_iloc1 BINARY_INTEGER;

     v_str1 VARCHAR2(300);
     v_str2 VARCHAR2(300);
     v_str3 VARCHAR2(300);
  BEGIN
       --解析参数
       v_iloc:=instr(v_parastr,',');
       IF v_iloc=0 THEN
          v_tab(1):=v_parastr;
          v_paracnt:=1;
       ELSE
          comma_to_table(v_parastr,v_paracnt,v_tab);
       END IF;

       FOR i IN 1..v_paracnt LOOP
           --替换tab键，只考虑空格
           v_str1 :=TRIM(REPLACE(v_tab(i),chr(9),' '));
           v_str2 :=upper(v_str1);

           v_iloc:=instr(v_str2,' IN ');
           v_iloc1:=instr(v_str2,' OUT ');

           IF v_iloc>0 AND v_iloc1>0 THEN
              v_paradir(i):='InputOutput';
           ELSIF v_iloc1>0 THEN
              v_paradir(i):='Output';
           ELSE
              v_paradir(i):='Input';
           END IF;

           v_str3:=REPLACE(v_str2,' IN ',' ');
           v_str3:=REPLACE(v_str3,' OUT ',' ');

           v_iloc:=instr(v_str3,' ');

           v_paraname(i):=TRIM(substr(v_str1,1,v_iloc));

           v_paratype(i):=TRIM(substr(v_str3,v_iloc+1));

       END LOOP;

  END;



  FUNCTION getIDataType(v_oratype VARCHAR2) RETURN VARCHAR2
  IS
  BEGIN
       IF v_oratype='VARCHAR2' THEN
          RETURN 'IDataType.VarChar';
       ELSIF v_oratype='NUMBER' THEN
          RETURN 'IDataType.Number';
       ELSIF v_oratype IN ('T_CURSOR','REF_CURSOR') THEN
          RETURN 'IDataType.Cursor';
       ELSIF v_oratype='DATE' THEN
          RETURN 'IDataType.DateTime';
       END IF;
  END ;

  FUNCTION getC#Type(v_oratype VARCHAR2) RETURN VARCHAR2
  IS
  BEGIN
       IF v_oratype='VARCHAR2' THEN
          RETURN 'string';
       ELSIF v_oratype='NUMBER' THEN
          RETURN 'int';
       ELSIF v_oratype IN ('T_CURSOR','REF_CURSOR') THEN
          RETURN 'DataSet';
       ELSIF v_oratype='DATE' THEN
          RETURN 'DateTime';
       END IF;
  END ;


  FUNCTION getC#OutputValue(v_oratype VARCHAR2, v_id NUMBER) RETURN VARCHAR2
  IS
  BEGIN
       IF v_oratype='VARCHAR2' THEN
          RETURN 'pms['||(v_id-1)||'].Value.ToString());';
       ELSIF v_oratype='NUMBER' THEN
          RETURN 'int.Parse(pms['||(v_id-1)||'].Value.ToString());';

       ELSIF v_oratype='DATE' THEN
          RETURN '(DateTime)pms['||(v_id-1)||'].Value;';
       END IF;
  END ;


BEGIN

     v_paracnt:=0;


     v_rlname:=TRIM(v_name);
     v_iLoc:=instr(v_rlname,';');

     IF v_iLoc>0 THEN
        v_rlname:=substr(v_rlname,1,v_iLoc);
     END IF;

     v_rlname:=rTRIM(v_rlname,chr(13));
     v_rlname:=rtrim(v_rlname,';');
     v_rlname:=REPLACE(v_rlname,CHR(9),' ');
     v_upname:=upper(v_rlname);
     v_iLoc:=instr(v_upname,'FUNCTION');
     v_isFunc:=TRUE;
     IF v_iLoc=0 THEN
        v_iLoc:=instr(v_upname,'PROCEDURE');
        IF v_iLoc=0 THEN
           RETURN '语法错误';
        END IF;
        v_isFunc:=FALSE;
     END IF;

     --判断括号，在过程名中只能出现一对括号
     v_iLoc0:=instr(v_upname,'(');
     IF v_iLoc0=0 THEN
        v_havec:=FALSE;
     ELSE
        v_iLoc1:=instr(v_upname,'(',v_iLoc1+1);
        IF v_iLoc1>0 THEN
           RETURN 'have more than one (;';
        END IF;
        v_havec:=TRUE;
     END IF;



     IF v_isFunc=TRUE THEN
        v_iLoc1:=instr(v_upname,'RETURN');
        IF v_iLoc1=0 THEN
           RETURN 'function no return';
        END IF;



        IF v_havec=TRUE THEN
           --有参数
           v_iLoc2:=instr(v_upname,')');
           v_parastr:=substr(v_rlname,v_iLoc0+1, v_iloc2-v_iLoc0-1);

           v_methodname:=trim(substr(v_rlname,v_iLoc+8,v_iLoc0-v_iLoc-8));

           getPara(v_parastr,v_paraname , v_paratype, v_paradir, v_paracnt );

        ELSE
           v_methodname:=trim(substr(v_rlname,v_iLoc+8,v_iLoc1-v_iLoc-8));
        END IF;


        --返回参数
        v_paratype(v_paracnt+1):=trim(substr(v_upname,v_iLoc1+6));

        SELECT decode(v_paratype(v_paracnt+1), 'VARCHAR2','string','NUMBER',
         'int','DATE','DateTime', NULL) INTO v_returntype FROM dual;

        IF v_returntype IS NULL THEN
           RETURN 'function return type error.';
        END IF;
        v_paraname(v_paracnt+1):='result';
        v_paradir(v_paracnt+1):='ReturnValue';

     ELSE

        IF v_havec=TRUE THEN

           v_iLoc2:=instr(v_upname,')');
           v_parastr:=substr(v_rlname,v_iLoc0+1, v_iloc2-v_iLoc0-1);

           v_methodname:=trim(substr(v_rlname,v_iLoc+9,v_iLoc0-v_iLoc-9));
           getPara(v_parastr,v_paraname , v_paratype, v_paradir, v_paracnt );
        ELSE
           v_methodname:=trim(substr(v_rlname,v_iLoc+9));
        END IF;
        v_returntype:='void';
     END IF;

     --教研参数类型
     FOR i in 1..v_paracnt LOOP
         IF v_paratype(i) NOT IN ('VARCHAR2','NUMBER','DATE','REF_CURSOR','T_CURSOR') THEN
            RETURN 'function or procedure parameter type error.';
         END IF;

         SELECT decode(v_paratype(i),'VARCHAR2','string','NUMBER',
         'int','DATE','DateTime','REF_CURSOR','DataSet', 'T_CURSOR', 'DataSet') INTO v_paratype_c#(i) FROM dual ;


         IF v_paratype(i) IN ('REF_CURSOR','T_CURSOR') THEN
            v_returntype:='DataSet';
         END IF;
     END LOOP;

     IF v_returntype = 'DataSet' THEN

        IF v_paratype(v_paracnt) NOT  IN ('REF_CURSOR','T_CURSOR') THEN
            RETURN '存储过程返回游标因该为最后一个参数';
        END IF;
        v_paracnt:=v_paracnt-1;
     END IF;


     v_rt:='public static '||v_returntype||' '||v_methodname||'(';

     FOR i in 1..v_paracnt LOOP

         IF v_paradir(i) IN ('Output','InputOutput') THEN
            v_rt:=v_rt|| ' ref '||v_paratype_c#(i)||' ';
         ELSE
            v_rt:=v_rt||v_paratype_c#(i)||' ';
         END IF;

         IF i=v_paracnt THEN
            v_rt:=v_rt||v_paraname(i);
         ELSE
            v_rt:=v_rt||v_paraname(i)||',';
         END IF;
     END LOOP;

     v_rt:=v_rt||')'||chr(13)||chr(10)||'{'||chr(13)||chr(10);

     v_tstr:='string mConn = IConfiguration.getParameter("connectString");';
     v_rt:=v_rt||chr(9)||v_tstr||chr(13)||chr(10);
     v_tstr:='IDBO.IDBO dbo = IDBO.IDBO.getIDBO(mConn);';
     v_rt:=v_rt||chr(9)||v_tstr||chr(13)||chr(10);
     v_tstr:='try';
     v_rt:=v_rt||chr(9)||v_tstr||chr(13)||chr(10);
     v_tstr:='{';
     v_rt:=v_rt||chr(9)||v_tstr||chr(13)||chr(10);
     v_tstr:='dbo.openDatabase();';
     v_rt:=v_rt||chr(9)||chr(9)||v_tstr||chr(13)||chr(10);
     ---------------

     IF v_returntype<>'void' THEN
        v_paracnt:=v_paracnt+1;
     END IF;


     v_tstr:='IPrameter[] pms=new IPrameter['||v_paracnt||'];';
     v_rt:=v_rt||chr(9)||chr(9)||v_tstr||chr(13)||chr(10);

     FOR i IN 1..v_paracnt LOOP

         --初始化
         v_tstr:='pms['||(i-1)||'] = new IDBO.IPrameter("'||
         v_paraname(i)||'",'||
         getIDataType(v_paratype(i))||
         ',ParameterDirection.'||v_paradir(i)||
         ');';
         v_rt:=v_rt||chr(9)||chr(9)||v_tstr||chr(13)||chr(10);

/*         --参数名
         v_tstr:='pms['||(i-1)||'].Name="'||v_paraname(i)||'";';
         v_rt:=v_rt||chr(9)||chr(9)||v_tstr||chr(13)||chr(10);
         --类型
         v_tstr:='pms['||(i-1)||'].DataType='||getIDataType(v_paratype(i))||';';
         v_rt:=v_rt||chr(9)||chr(9)||v_tstr||chr(13)||chr(10);
         --方向
         v_tstr:='pms['||(i-1)||'].Direction='||'ParameterDirection.'||v_paradir(i)||';';
         v_rt:=v_rt||chr(9)||chr(9)||v_tstr||chr(13)||chr(10);
*/

         IF v_paradir(i) IN ('Input','InputOutput') THEN
            v_tstr:='pms['||(i-1)||'].Value='||v_paraname(i)||';';
            v_rt:=v_rt||chr(9)||chr(9)||v_tstr||chr(13)||chr(10);
         END IF;


         IF v_paradir(i) IN ('InputOutput','Output','ReturnValue') AND v_paratype(i)='VARCHAR2' THEN
            v_tstr:='pms['||(i-1)||'].Size=1024;';
            v_rt:=v_rt||chr(9)||chr(9)||v_tstr||chr(13)||chr(10);
         END IF;
     END LOOP;

     IF v_pkgname IS NOT NULL THEN
        v_methodname:=v_pkgname||'.'|| v_methodname;
     END IF;


     IF v_returntype = 'DataSet' THEN
         v_tstr:='DataSet ds = dbo.executeProcedure("'||v_methodname||'", pms);';
     ELSE
         v_tstr:='dbo.executeFunction("'||v_methodname||'", pms);';
     END IF;
     v_rt:=v_rt||chr(9)||chr(9)||v_tstr||chr(13)||chr(10);

     FOR i IN 1..v_paracnt LOOP
         IF v_paradir(i) IN ('Output','InputOutput') AND v_paratype(i) NOT IN ('T_CURSOR','REF_CURSOR') THEN
            v_tstr:=v_paraname(i)||'= '||getC#OutputValue(v_paratype(i),i);
            v_rt:=v_rt||chr(9)||chr(9)||v_tstr||chr(13)||chr(10);
         END IF;
     END LOOP;


     IF v_returntype = 'DataSet' THEN
         v_tstr:='return ds;';
         v_rt:=v_rt||chr(9)||chr(9)||v_tstr||chr(13)||chr(10);
     ELSIF v_returntype<>'void' THEN
         v_tstr:='return ('||getC#Type(v_paratype(v_paracnt))||')'||'pms['||(v_paracnt-1)||'].Value;';
         v_rt:=v_rt||chr(9)||chr(9)||v_tstr||chr(13)||chr(10);
     END IF;

     ---------------
     v_tstr:='}';
     v_rt:=v_rt||chr(9)||v_tstr||chr(13)||chr(10);
     v_tstr:='catch (Exception e)'||chr(13)||chr(10)||
     chr(9)||'{'||chr(13)||chr(10)||
     chr(9)||chr(9)||'iLog.Error(e.Message.ToString());'||chr(13)||chr(10)||
     chr(9)||chr(9)||'throw e;'||chr(13)||chr(10)||
     chr(9)||'}'||chr(13)||chr(10)||
     chr(9)||'finally'||chr(13)||chr(10)||
     chr(9)||'{'||chr(13)||chr(10)||
     chr(9)||chr(9)||'dbo.closeDatabase();'||chr(13)||chr(10)||
     chr(9)||'}';

     v_rt:=v_rt||chr(9)||v_tstr||chr(13)||chr(10)||'}';
     RETURN v_rt;
END;


END ;
/


spool off