Oracle从表的中文注释创建中文视图

2019独角兽企业重金招聘Python工程师标准>>>

CREATE OR REPLACE PROCEDURE CN_COMMENT_TO_CHINESE_VIEW(TABLENAME IN VARCHAR2)

  AUTHID CURRENT_USER IS--解决创建视图权限不足的问题

  CURSOR TABLE_COMMENT IS

    SELECT * FROM USER_COL_COMMENTS WHERE TABLE_NAME = TABLENAME;

  RECORD_TABLE_COMMENT TABLE_COMMENT%ROWTYPE;

  CREATE_VIEW_SQL VARCHAR2(1000);

BEGIN

  CREATE_VIEW_SQL := 'create view ' || TABLENAME || '_CN as select ';

  FOR RECORD_TABLE_COMMENT IN TABLE_COMMENT LOOP

    CREATE_VIEW_SQL := CREATE_VIEW_SQL || RECORD_TABLE_COMMENT.COLUMN_NAME ||

                       ' as ' || RECORD_TABLE_COMMENT.COMMENTS || ' ,';

  END LOOP;

  CREATE_VIEW_SQL := RTRIM(CREATE_VIEW_SQL, ' ,');

  CREATE_VIEW_SQL := CREATE_VIEW_SQL || ' from ' || TABLENAME;

  DBMS_OUTPUT.PUT_LINE(CREATE_VIEW_SQL);

  EXECUTE IMMEDIATE CREATE_VIEW_SQL;

END;

转载于:https://my.oschina.net/u/814492/blog/163031