oracle之type、database_link、synonym

    

--type用法
/**
多行多列--动态游标(ref cursor)
多行单列--表(table)
单行多列--记录(record)
自定义类型--对象(object)

%rowtype  -->与某一行的数据类型一致
%type     -->与某个字段的数据类型一致

**/

--创建数据链 连接不同oracle实例

--创建同义词  同义词在一个数据库中不同用户之间可以共享数据库对象
--PUBLIC 公有同义词可被数据库中所有用户使用
--私有的同义词只能被当前的用户使用

测试code

--oracle之type、database_link、synonym

--创建测试数据
CREATE TABLE test_TABLES AS SELECT * from USER_TABLES;
SELECT * from test_TABLES TT;

--type用法
/**
多行多列--动态游标(ref cursor)
多行单列--表(table)
单行多列--记录(record)
自定义类型--对象(object)

%rowtype  -->与某一行的数据类型一致
%type     -->与某个字段的数据类型一致

**/

--游标 多行多列
--CURSOR
CREATE OR REPLACE PROCEDURE PRO_REF_CUR_TEST
AS
 --定义动态游标数据类型
  TYPE CUSTOM_CURSOR IS REF CURSOR;
  --1、定义游标变量
  test_CUSTOM_CURSOR CUSTOM_CURSOR;
  REC               test_TABLES%ROWTYPE;
BEGIN
  --2、打开游标
  OPEN test_CUSTOM_CURSOR FOR
    SELECT * FROM test_TABLES;
  LOOP
     --3、获取值
    FETCH test_CUSTOM_CURSOR
      INTO REC;
      --4、退出
    EXIT WHEN test_CUSTOM_CURSOR%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(REC.TABLE_NAME || '    ' || REC.TABLESPACE_NAME || '    ' ||
                         TO_CHAR(REC.LAST_ANALYZED, 'yyyy-mm-dd'));
  END LOOP;
  --5、关闭
  CLOSE test_CUSTOM_CURSOR;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLCODE || SQLERRM);
END PRO_REF_CUR_TEST;

--TABLE 多行单列
CREATE OR REPLACE PROCEDURE PRO_table_TEST
AS
--定义表 类似于数组的使用  每一个元素数据类型为VARCHAR2(20) 索引值为BINARY_INTEGER
 TYPE CUSTOM_TABLE IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
test_CUSTOM_TABLE CUSTOM_TABLE;
BEGIN
  --索引值只要为BINARY_INTEGER即可
  test_CUSTOM_TABLE(1) := '刘德华';
  test_CUSTOM_TABLE(4) := '张学友';
  test_CUSTOM_TABLE(-20) := '郭富城';
  test_CUSTOM_TABLE(-10) := '郭富城';
  DBMS_OUTPUT.PUT_LINE(test_CUSTOM_TABLE(1) || '    ' ||
                       test_CUSTOM_TABLE(4));
  DBMS_OUTPUT.PUT_LINE('按照索引值进行排序');
  --table中的函数
  DBMS_OUTPUT.PUT_LINE(test_CUSTOM_TABLE.FIRST || '    ' ||
                       test_CUSTOM_TABLE.NEXT(test_CUSTOM_TABLE.FIRST) ||
                       '    ' ||
                       test_CUSTOM_TABLE.PRIOR(test_CUSTOM_TABLE.LAST) ||
                       '    ' || test_CUSTOM_TABLE.LAST);
  DBMS_OUTPUT.PUT_LINE('总数:' || test_CUSTOM_TABLE.COUNT);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLCODE || SQLERRM);
END PRO_table_TEST;


--记录
--RECORD 单行多列
CREATE OR REPLACE PROCEDURE PRO_Record_TEST
AS
--定义一行记录
TYPE CUSTOM_RECORD IS RECORD(TABLE_NAME TEST_TABLEs.Table_Name%TYPE,
                                     TABLESPACE_NAME Test_Tables.Tablespace_Name%TYPE,
                                     LAST_ANALYZED Test_Tables.Last_Analyzed%TYPE);
--定义记录变量
test_CUSTOM_RECORD CUSTOM_RECORD;
BEGIN
  SELECT TABLE_NAME, TABLESPACE_NAME, LAST_ANALYZED
    INTO test_CUSTOM_RECORD
    FROM test_TABLES
   WHERE TABLE_NAME = 'TEST_TABLE';
  DBMS_OUTPUT.PUT_LINE(test_CUSTOM_RECORD.TABLE_NAME || '    ' ||
                       test_CUSTOM_RECORD.TABLESPACE_NAME || '    ' ||
                       TO_CHAR(test_CUSTOM_RECORD.LAST_ANALYZED, 'yyyy-mm-dd'));
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLCODE || SQLERRM);
END PRO_Record_TEST;


--创建自定义类型
CREATE TYPE CUSTOM_OBJECT AS OBJECT(AGE INT, NAME VARCHAR2(20));
CREATE TABLE CUSTOM_TABLE(TABLE_NAME INT, OTHER CUSTOM_OBJECT);
INSERT INTO CUSTOM_TABLE VALUES (1, CUSTOM_OBJECT(2, 'hello'));
SELECT * FROM CUSTOM_TABLE;



--创建数据链 连接不同oracle实例
--查看实例名
SELECT * from GLOBAL_NAME;
--创建数据链
CREATE DATABASE LINK u_other CONNECT TO u2 IDENTIFIED BY u2 USING '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(service_name=ORCL)
)
)';
--查询数据链接
SELECT * from dba_db_links dd WHERE dd.DB_LINK='U_OTHER';
DROP DATABASE LINK u_other;
SELECT * from user_tables@u_other;


--创建同义词  同义词在一个数据库中不同用户之间可以共享数据库对象
--PUBLIC 公有同义词可被数据库中所有用户使用
--私有的同义词只能被当前的用户使用
CREATE PUBLIC SYNONYM ut FOR u2.user_tables;
DROP PUBLIC SYNONYM ut;
SELECT * from ut;
--查询同义词
SELECT * from Dba_Synonyms  ds WHERE ds.synonym_name='UT';

测试结果

1、测试游标

begin
  -- Call the procedure
  pro_ref_cur_test;
end;

PLAN_TABLE    UNDER    2014-10-29
EMPLOYEE    UNDER    2014-09-18
CREATE$JAVA$LOB$TABLE    UNDER    2014-09-17
IDCARD_INFO    UNDER    2014-11-12
PERSON_INFO    UNDER    2014-11-12
CUSTOM_TABLE    UNDER    2014-12-24
PC_CHECK_DETAIL        2014-09-06
STUDENT    UNDER    2014-10-29
TEST_TABLE    UNDER    2014-09-21

2、测试表

begin
  -- Call the procedure
  pro_table_test;
end;

刘德华    张学友
按照索引值进行排序
-20    -10    1    4
总数:4

3、测试记录

begin
  -- Call the procedure
  pro_record_test;
end;

TEST_TABLE    UNDER    2014-09-21

4、自定义类型

--创建自定义类型
CREATE TYPE CUSTOM_OBJECT AS OBJECT(AGE INT, NAME VARCHAR2(20));
CREATE TABLE CUSTOM_TABLE(TABLE_NAME INT, OTHER CUSTOM_OBJECT);
INSERT INTO CUSTOM_TABLE VALUES (1, CUSTOM_OBJECT(2, 'hello'));
SELECT * FROM CUSTOM_TABLE;