ora2c20070724.sql

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

-----------------------------------------------------
-- Export file for user WMIS_WEB                   --
-- Created by Administrator on 2007-6-25, 10:38:07 --
-----------------------------------------------------

spool ora2c20070724.log

prompt
prompt Creating package ORA2C
prompt ======================
prompt
CREATE OR REPLACE PACKAGE ORA2C  IS

TYPE ref_cursor IS REF CURSOR;
FUNCTION getc#method_byname(v_name VARCHAR2, v_pkgname VARCHAR2) RETURN VARCHAR2;

FUNCTION  get_method_declare(v_pkgname VARCHAR2, v_method_name VARCHAR2 , v_overload NUMBER) RETURN VARCHAR2;
FUNCTION  getc#method_bypkg(v_pkgname VARCHAR2) RETURN VARCHAR2;
PROCEDURE getrcd(cur_rt OUT ref_cursor);
PROCEDURE getMethod(v_pkgName VARCHAR2,cur_rt OUT REF_CURSOR);
PROCEDURE p_test1(a VARCHAR2);
PROCEDURE p_test;
--PROCEDURE p_test(a VARCHAR2);
--FUNCTION p_test(a VARCHAR2) RETURN VARCHAR2 ;
END ;
/

prompt
prompt Creating package body ORA2C
prompt ===========================
prompt
CREATE OR REPLACE PACKAGE  BODY ORA2C  IS


PROCEDURE getrcd(cur_rt OUT ref_cursor)
AS
BEGIN
     OPEN cur_rt  FOR SELECT 1 aa FROM dual;
END;

FUNCTION getpkg_function(v_pkgname VARCHAR2, v_methodname VARCHAR2, v_overload NUMBER)
RETURN VARCHAR2
IS

   CURSOR cur_arg(v_pkgname VARCHAR2,v_methodname VARCHAR2, v_overload NUMBER) IS
   SELECT * FROM USER_ARGUMENTS t
   WHERE  t.OBJECT_NAME=v_methodname
   AND t.PACKAGE_NAME=v_pkgname
   AND nvl(overload,0)=v_overload
   AND t.POSITION<>0
   ORDER BY t.POSITION;

   rcd_arg cur_arg%ROWTYPE;

   v_iExist BINARY_INTEGER;

   v_method_declare VARCHAR2(1024);
   v_returntype VARCHAR2(1024);
BEGIN
   SELECT t.DATA_TYPE INTO v_returntype
   FROM USER_ARGUMENTS t
   WHERE t.OBJECT_NAME=v_methodname
   AND t.PACKAGE_NAME=v_pkgname
   AND nvl(overload,0)=v_overload
   AND t.POSITION=0;

   SELECT COUNT(*) INTO v_iExist FROM USER_ARGUMENTS t
   WHERE t.OBJECT_NAME=v_methodname
   AND t.PACKAGE_NAME=v_pkgname
   AND nvl(overload,0)=v_overload
   AND t.POSITION<>0;

   IF v_iExist =0 THEN
      v_method_declare:='FUNCTION '||v_methodname||' return '||v_returntype;
      RETURN v_method_declare;
   END IF;

   v_method_declare:='FUNCTION '||v_methodname||'(';


   OPEN cur_arg(v_pkgname,v_methodname, v_overload);
   FETCH cur_arg INTO rcd_arg;
   WHILE cur_arg%FOUND LOOP
          SELECT v_method_declare||rcd_arg.ARGUMENT_NAME||' '
          ||decode(rcd_arg.IN_OUT,'IN/OUT' ,'IN OUT',rcd_arg.IN_OUT) ||' '||
          decode(rcd_arg.DATA_TYPE, 'REF CURSOR' ,'ref_cursor',rcd_arg.DATA_TYPE)||','
          INTO v_method_declare FROM dual;
          FETCH cur_arg INTO rcd_arg;
          FETCH cur_arg INTO rcd_arg;
   END LOOP;
   CLOSE cur_arg;

   v_method_declare:=substr(v_method_declare,1, length(v_method_declare)-1);

   v_method_declare:=v_method_declare||') return ' ||v_returntype;

   RETURN v_method_declare;


END;




FUNCTION getpkg_procedure(v_pkgname VARCHAR2, v_methodname VARCHAR2, v_overload NUMBER)
RETURN VARCHAR2
IS

   CURSOR cur_arg(v_pkgname VARCHAR2,v_methodname VARCHAR2, v_overload NUMBER) IS
   SELECT * FROM USER_ARGUMENTS t
   WHERE  t.OBJECT_NAME=v_methodname
   AND t.PACKAGE_NAME=v_pkgname
   AND nvl(overload,0)=v_overload
   AND t.SEQUENCE<>0
   ORDER BY t.POSITION;

   rcd_arg cur_arg%ROWTYPE;

   v_iExist BINARY_INTEGER;

   v_method_declare VARCHAR2(1024);

BEGIN

   SELECT COUNT(*) INTO v_iExist FROM USER_ARGUMENTS t
   WHERE t.OBJECT_NAME=v_methodname
   AND t.PACKAGE_NAME=v_pkgname
   AND nvl(overload,0)=v_overload
   AND t.SEQUENCE<>0;

   IF v_iExist =0 THEN
      v_method_declare:='PROCEDURE '||v_methodname;
      RETURN v_method_declare;
   END IF;

   v_method_declare:='PROCEDURE '||v_methodname||'(';


   OPEN cur_arg(v_pkgname,v_methodname, v_overload);
   FETCH cur_arg INTO rcd_arg;
   WHILE cur_arg%FOUND LOOP

          SELECT v_method_declare||rcd_arg.ARGUMENT_NAME||' '
          ||decode(rcd_arg.IN_OUT,'IN/OUT' ,'IN OUT',rcd_arg.IN_OUT) ||' '||
          decode(rcd_arg.DATA_TYPE, 'REF CURSOR' ,'ref_cursor',rcd_arg.DATA_TYPE)||','
          INTO v_method_declare FROM dual;
          FETCH cur_arg INTO rcd_arg;
   END LOOP;
   CLOSE cur_arg;

   v_method_declare:=substr(v_method_declare,1, length(v_method_declare)-1);

   v_method_declare:=v_method_declare||')';

   RETURN v_method_declare;


END;


FUNCTION  getc#method_bypkg(v_pkgname VARCHAR2) RETURN VARCHAR2
IS

   CURSOR cur_method(v_package_name VARCHAR2) IS
   SELECT object_name methodname, nvl(overload, 0) overload
   FROM  USER_ARGUMENTS WHERE PACKAGE_NAME=v_package_name
   GROUP BY object_name, overload;
   rcd_method cur_method%ROWTYPE;

   v_method_declare VARCHAR2(1024);
   v_method_c#  VARCHAR2(4000);
   v_iExist BINARY_INTEGER;
   v_id NUMBER;


   v_pkgbody VARCHAR2(30000);
BEGIN
   v_id:=1;
   v_pkgbody:=NULL;
   OPEN cur_method(v_pkgname);
   FETCH cur_method INTO rcd_method;
   WHILE cur_method%FOUND LOOP

         SELECT COUNT(*) INTO v_iExist FROM USER_ARGUMENTS t
         WHERE t.OBJECT_NAME=rcd_method.METHODNAME
         AND nvl(overload,0)=rcd_method.OVERLOAD
         AND t.POSITION=0;

         IF v_iExist=0 THEN
            v_method_declare:= getpkg_procedure(v_pkgname, rcd_method.METHODNAME, rcd_method.OVERLOAD);
         ELSE
            v_method_declare:=getpkg_function(v_pkgname, rcd_method.METHODNAME, rcd_method.OVERLOAD);
         END IF;

         v_method_c#  :=getc#method_byname(v_method_declare, v_pkgname);

         v_pkgbody:=v_pkgbody||chr(13)||chr(13)||v_method_c#;


         v_id:=v_id+1;

         FETCH cur_method INTO rcd_method;
   END LOOP;
   CLOSE cur_method;

   RETURN v_pkgbody;
END ;


FUNCTION  get_method_declare(v_pkgname VARCHAR2, v_method_name VARCHAR2 , v_overload NUMBER) RETURN VARCHAR2
IS


   v_method_declare VARCHAR2(1024);
   v_method_c#  VARCHAR2(4000);
   v_iExist BINARY_INTEGER;
   v_id NUMBER;

   CURSOR cur_method(v_pkgname VARCHAR2, v_method_name VARCHAR2) IS
   SELECT text FROM USER_SOURCE WHERE TYPE='PACKAGE' AND NAME=v_pkgname
   AND ( upper(text) LIKE '%'||upper(v_method_name)||'('||'%' OR
   upper(text) LIKE '%'||upper(v_method_name)||';'||'%');

   rcd_method cur_method%ROWTYPE;

   v_i BINARY_INTEGER;
BEGIN

   IF v_overload=0 THEN


      SELECT text INTO v_method_declare
      FROM USER_SOURCE WHERE TYPE='PACKAGE' AND NAME=v_pkgname
      AND (upper(text) LIKE '%'||upper(v_method_name)||'('||'%'
       OR
   upper(text) LIKE '%'||upper(v_method_name)||';'||'%')
      AND upper(text) NOT LIKE '--%';

      IF instr(v_method_declare,';')>0 THEN
         RETURN v_method_declare;
      END IF;
   ELSE
       v_i:=1;
       OPEN cur_method(v_pkgname, v_method_name);
       FETCH cur_method INTO rcd_method;

       WHILE cur_method%FOUND LOOP
             IF v_i= v_overload THEN
                v_method_declare:=rcd_method.text;
             END IF;
             v_i:=v_i+1;
             FETCH cur_method INTO rcd_method;
       END LOOP;
       CLOSE cur_method;
       IF instr(v_method_declare,';')>0 THEN
         RETURN v_method_declare;
       END IF;

   END IF;




         SELECT COUNT(*) INTO v_iExist FROM USER_ARGUMENTS t
         WHERE t.OBJECT_NAME=v_method_name
         AND nvl(overload,0)=v_overload
         AND t.POSITION=0;

         IF v_iExist=0 THEN
            v_method_declare:= getpkg_procedure(v_pkgname, v_method_name, v_overload);
         ELSE
            v_method_declare:=getpkg_function(v_pkgname, v_method_name,v_overload);
         END IF;

         RETURN v_method_declare;
END ;



PROCEDURE p_test
IS
BEGIN
     NULL;

END ;

PROCEDURE p_test1(a VARCHAR2)
IS
BEGIN
    NULL;
END ;



PROCEDURE p_test(a VARCHAR2)
IS
BEGIN
    NULL;
END ;

FUNCTION p_test(a VARCHAR2) RETURN VARCHAR2
IS
BEGIN
RETURN a||'abccddfsddf';

END;

PROCEDURE getMethod(v_pkgName VARCHAR2,cur_rt OUT REF_CURSOR)
AS
BEGIN
 OPEN cur_rt FOR
 SELECT ROWNUM 序号,方法名,重载 FROM
 (SELECT object_name 方法名, nvl(overload, 0) 重载 FROM
 USER_ARGUMENTS WHERE PACKAGE_NAME=v_pkgName GROUP BY object_name, overload);
END;


FUNCTION getc#method_byname(v_name VARCHAR2, v_pkgname VARCHAR2) RETURN VARCHAR2
/*
  v_name 过程或函数声明
  v_pkgname 包名 null--一般过程或函数
  return C#访问函数代码
*/
IS
  v_rt VARCHAR2(10000);
  TYPE arraystr IS TABLE OF VARCHAR2(227) index by BINARY_INTEGER;

  v_upname VARCHAR2(1024);
  v_rlname VARCHAR2(1024);

  v_iLoc BINARY_INTEGER;

  v_iLoc2 BINARY_INTEGER;
  v_iLoc0 BINARY_INTEGER;
  v_iLoc1 BINARY_INTEGER;
  v_isFunc BOOLEAN;
  v_havec BOOLEAN;

  v_parastr VARCHAR2(1024);

  v_methodname VARCHAR2(1024);
  v_returntype VARCHAR2(1024);
  v_paraname arraystr;
  v_paratype arraystr;
  v_paradir  arraystr;
  v_paracnt NUMBER;
  v_paratype_c# arraystr;
  v_tstr VARCHAR2(1024);


  PROCEDURE comma_to_table(v_parastr IN VARCHAR2,v_paracnt OUT NUMBER ,v_tab OUT arraystr)
  AS
     v_iLoc NUMBER;
     v_i NUMBER;