oracle同义词的建立实例,oracle 同义词建立

1 把一个用户的SELECT 权给另一个用户(以SYS身份执行)：

select 'grant select on cisco.'||table_name||  ' to chen ;' from all_tables where owner=upper('cisco');

把CISCO用户下的所有表授权给CHEN这个用户

2 创建同义词包语句如下(以SYS身份执行)：

CREATE OR REPLACE PACKAGE AMBOW_BI IS

-- Author  : cm

-- Created : 2011-2-17 14:53:15

PROCEDURE create_alltable_synonym(

/*

创建源用户全部表的同义词到目标用户中

*/

v_sowner            IN             VARCHAR2,       /*[1]源用户*/

v_towner            IN             VARCHAR2,       /*[2]目标用户*/

v_success_xml       OUT            LONG,           /*[3]输出已创建的同义词*/

v_fail_xml          OUT            LONG            /*[3]输出未创建的同义词*/

);

PROCEDURE create_assigntable_synonym(

/*

创建源用户指定表的同义词到目标用户中

*/

v_sowner            IN OUT             VARCHAR2,       /*[1]源用户*/

v_towner            IN OUT             VARCHAR2        /*[2]目标用户*/

);

END AMBOW_BI;

/

CREATE OR REPLACE PACKAGE BODY AMBOW_BI IS

PROCEDURE create_alltable_synonym(

/*

创建源用户全部表的同义词到目标用户中

*/

v_sowner            IN             VARCHAR2,       /*[1]源用户*/

v_towner            IN             VARCHAR2,       /*[2]目标用户*/

v_success_xml       OUT            LONG,           /*[3]输出已创建的同义词*/

v_fail_xml          OUT            LONG            /*[3]输出未创建的同义词*/

)

IS

TYPE myCur IS REF Cursor;

cur  myCur;

tmp_sql           VARCHAR2(3000);

vsql              VARCHAR2(5000);

tmp_tablename     VARCHAR2(50);

tmp_count         NUMBER;

BEGIN

IF v_sowner IS NULL OR v_towner IS NULL THEN

RETURN;

END IF;

vsql:='SELECT T.TABLE_NAME FROM DBA_TABLES T WHERE T.OWNER='''||v_sowner||'''';

--查询源用户所拥有的表

OPEN cur  FOR vsql;

LOOP

FETCH  cur INTO  tmp_tablename;

EXIT WHEN cur%NOTFOUND OR cur IS NULL;

tmp_sql:='SELECT COUNT(1) FROM DBA_SYNONYMS T WHERE T.OWNER='''||v_towner||''' AND T.SYNONYM_NAME='''||tmp_tablename||'''';

EXECUTE IMMEDIATE tmp_sql INTO tmp_count;

--查询同义词是否已在目标用户中存在

IF tmp_count=0 THEN

tmp_sql:='CREATE SYNONYM '||v_towner||'.'||tmp_tablename||' FOR '||v_sowner||'.'||tmp_tablename;

EXECUTE IMMEDIATE tmp_sql;

--执行同义词的创建操作

v_success_xml:=v_success_xml||tmp_tablename||'|';

END IF;

v_fail_xml:=v_fail_xml||tmp_tablename||'|';

END LOOP;

CLOSE cur;

/*EXCEPTION

WHEN OTHERS THEN

RETURN;*/

END create_alltable_synonym;

PROCEDURE create_assigntable_synonym(

/*

创建源用户指定表的同义词到目标用户中

*/

v_sowner            IN OUT             VARCHAR2,       /*[1]源用户*/

v_towner            IN OUT             VARCHAR2        /*[2]目标用户*/

)

IS

tmp_sql           VARCHAR2(3000);

BEGIN

v_sowner:='AMBOWSERVER';

v_towner:='BIUSER';

tmp_sql:='CREATE SYNONYM '||v_towner||'.AREA_ALL FOR '||v_sowner||'.AREA_ALL';

EXECUTE IMMEDIATE tmp_sql;

tmp_sql:='CREATE SYNONYM '||v_towner||'.SCHOOLTYPE FOR '||v_sowner||'.SCHOOLTYPE';

EXECUTE IMMEDIATE tmp_sql;

tmp_sql:='CREATE SYNONYM '||v_towner||'.T_CLASS_INFO_HY FOR '||v_sowner||'.T_CLASS_INFO_HY';

EXECUTE IMMEDIATE tmp_sql;

tmp_sql:='CREATE SYNONYM '||v_towner||'.T_CLASS_INFO_LM FOR '||v_sowner||'.T_CLASS_INFO_LM';

EXECUTE IMMEDIATE tmp_sql;

tmp_sql:='CREATE SYNONYM '||v_towner||'.T_CLASS_INFO_ZJ FOR '||v_sowner||'.T_CLASS_INFO_ZJ';

EXECUTE IMMEDIATE tmp_sql;

tmp_sql:='CREATE SYNONYM '||v_towner||'.T_CLASSROOM_HY FOR '||v_sowner||'.T_CLASSROOM_HY';

EXECUTE IMMEDIATE tmp_sql;

tmp_sql:='CREATE SYNONYM '||v_towner||'.T_CLASSROOM_SEAT_HY FOR '||v_sowner||'.T_CLASSROOM_SEAT_HY';

EXECUTE IMMEDIATE tmp_sql;

tmp_sql:='CREATE SYNONYM '||v_towner||'.T_CONTRACT_INFO_ZJ FOR '||v_sowner||'.T_CONTRACT_INFO_ZJ';

EXECUTE IMMEDIATE tmp_sql;

tmp_sql:='CREATE SYNONYM '||v_towner||'.T_CONTRACT_ORDER_RELATION_ZJ FOR '||v_sowner||'.T_CONTRACT_ORDER_RELATION_ZJ';

EXECUTE IMMEDIATE tmp_sql;

tmp_sql:='CREATE SYNONYM '||v_towner||'.T_COUPON_BASE_HY FOR '||v_sowner||'.T_COUPON_BASE_HY';

EXECUTE IMMEDIATE tmp_sql;

tmp_sql:='CREATE SYNONYM '||v_towner||'.T_COUPON_HY FOR '||v_sowner||'.T_COUPON_HY';

EXECUTE IMMEDIATE tmp_sql;

tmp_sql:='CREATE SYNONYM '||v_towner||'.T_COURSE_HY FOR '||v_sowner||'.T_COURSE_HY';

EXECUTE IMMEDIATE tmp_sql;

tmp_sql:='CREATE SYNONYM '||v_towner||'.T_COURSE_LM FOR '||v_sowner||'.T_COURSE_LM';

EXECUTE IMMEDIATE tmp_sql;

tmp_sql:='CREATE SYNONYM '||v_towner||'.T_CREDIT_HY FOR '||v_sowner||'.T_CREDIT_HY';

EXECUTE IMMEDIATE tmp_sql;

tmp_sql:='CREATE SYNONYM '||v_towner||'.T_CREDIT_PRINT_HY FOR '||v_sowner||'.T_CREDIT_PRINT_HY';

EXECUTE IMMEDIATE tmp_sql;

tmp_sql:='CREATE SYNONYM '||v_towner||'.T_EDU_STAGE FOR '||v_sowner||'.T_EDU_STAGE';

EXECUTE IMMEDIATE tmp_sql;

tmp_sql:='CREATE SYNONYM '||v_towner||'.T_GRADE_HY FOR '||v_sowner||'.T_GRADE_HY';

EXECUTE IMMEDIATE tmp_sql;

tmp_sql:='CREATE SYNONYM '||v_towner||'.T_LEARN_PRODUCT_ZJ FOR '||v_sowner||'.T_LEARN_PRODUCT_ZJ';

EXECUTE IMMEDIATE tmp_sql;

tmp_sql:='CREATE SYNONYM '||v_towner||'.T_ORDER_CHANGE_HY FOR '||v_sowner||'.T_ORDER_CHANGE_HY';

EXECUTE IMMEDIATE tmp_sql;

tmp_sql:='CREATE SYNONYM '||v_towner||'.T_ORDER_CHANGE_LM FOR '||v_sowner||'.T_ORDER_CHANGE_LM';

EXECUTE IMMEDIATE tmp_sql;

tmp_sql:='CREATE SYNONYM '||v_towner||'.T_ORDER_COURSE_REAL_HY FOR '||v_sowner||'.T_ORDER_COURSE_REAL_HY';

EXECUTE IMMEDIATE tmp_sql;

tmp_sql:='CREATE SYNONYM '||v_towner||'.T_ORDER_HY FOR '||v_sowner||'.T_ORDER_HY';

EXECUTE IMMEDIATE tmp_sql;

tmp_sql:='CREATE SYNONYM '||v_towner||'.T_ORDER_LM FOR '||v_sowner||'.T_ORDER_LM';

EXECUTE IMMEDIATE tmp_sql;

tmp_sql:='CREATE SYNONYM '||v_towner||'.T_ORDER_OPERATION_ZJ FOR '||v_sowner||'.T_ORDER_OPERATION_ZJ';

EXECUTE IMMEDIATE tmp_sql;

tmp_sql:='CREATE SYNONYM '||v_towner||'.T_REGIINFO_HY FOR '||v_sowner||'.T_REGIINFO_HY';

EXECUTE IMMEDIATE tmp_sql;

tmp_sql:='CREATE SYNONYM '||v_towner||'.T_REGIINFO_LM FOR '||v_sowner||'.T_REGIINFO_LM';

EXECUTE IMMEDIATE tmp_sql;

tmp_sql:='CREATE SYNONYM '||v_towner||'.T_REGIINFO_ZJ FOR '||v_sowner||'.T_REGIINFO_ZJ';

EXECUTE IMMEDIATE tmp_sql;

tmp_sql:='CREATE SYNONYM '||v_towner||'.T_SCHOOL_AREA_HY FOR '||v_sowner||'.T_SCHOOL_AREA_HY';

EXECUTE IMMEDIATE tmp_sql;

tmp_sql:='CREATE SYNONYM '||v_towner||'.T_SCHOOL_AREA_LM FOR '||v_sowner||'.T_SCHOOL_AREA_LM';

EXECUTE IMMEDIATE tmp_sql;

tmp_sql:='CREATE SYNONYM '||v_towner||'.T_SCHOOL_AREA_ZJ FOR '||v_sowner||'.T_SCHOOL_AREA_ZJ';

EXECUTE IMMEDIATE tmp_sql;

tmp_sql:='CREATE SYNONYM '||v_towner||'.T_SEASON_HY FOR '||v_sowner||'.T_SEASON_HY';

EXECUTE IMMEDIATE tmp_sql;

tmp_sql:='CREATE SYNONYM '||v_towner||'.T_SUBJECT_HY FOR '||v_sowner||'.T_SUBJECT_HY';

EXECUTE IMMEDIATE tmp_sql;

tmp_sql:='CREATE SYNONYM '||v_towner||'.T_SUBJECT_LM FOR '||v_sowner||'.T_SUBJECT_LM';

EXECUTE IMMEDIATE tmp_sql;

tmp_sql:='CREATE SYNONYM '||v_towner||'.T_SYS_BASECODE_ZJ FOR '||v_sowner||'.T_SYS_BASECODE_ZJ';

EXECUTE IMMEDIATE tmp_sql;

tmp_sql:='CREATE SYNONYM '||v_towner||'.T_TIME_SPAN_HY FOR '||v_sowner||'.T_TIME_SPAN_HY';

EXECUTE IMMEDIATE tmp_sql;

/*EXCEPTION

WHEN OTHERS THEN

RETURN;*/

END create_assigntable_synonym;

END AMBOW_BI;

/

3 打开package body 里的PROCEDURE执行其中的一个