Java Oracle 学习整理(一)

最新推荐文章于 2022-10-31 21:28:16 发布

原创最新推荐文章于 2022-10-31 21:28:16 发布 · 1.4k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #java #table #insert #function #object

Oracle Dev 专栏收录该内容

6 篇文章

订阅专栏

本文介绍Oracle PL/SQL中自定义类型的创建与使用方法，包括点、线、矩形等几何对象的定义及其成员函数实现，以及温度记录、人员信息等复杂对象类型的表定义。

CREATE TYPE POINT_TYPE AS OBJECT
(
X NUMBER,
Y NUMBER
)
/

CREATE TABLE CIRCLES (
RADIUS NUMBER,
CENTER POINT_TYPE
)
/

INSERT INTO CIRCLES VALUES(1, POINT_TYPE(10, 20))
/

SELECT * FROM CIRCLES;
SELECT RADIUS, C.CENTER.X, C.CENTER.Y
FROM CIRCLES C /*Must use table alias*/

CREATE TYPE LINE_TYPE AS OBJECT
(
    START_POINT POINT_TYPE,
    END_POINT   POINT_TYPE,
    MEMBER FUNCTION GETLENGTH RETURN NUMBER
)
/

CREATE OR REPLACE TYPE BODY LINE_TYPE AS
    MEMBER FUNCTION GETLENGTH RETURN NUMBER AS
    BEGIN
        RETURN SQRT(POWER(START_POINT.X - END_POINT.X, 2) +
                    POWER(START_POINT.Y - END_POINT.Y, 2));
    END;
END;
/

CREATE TABLE LINE OF LINE_TYPE;

INSERT INTO LINE VALUES(POINT_TYPE(1,1), POINT_TYPE(4,5));

SELECT C.GETLENGTH() FROM LINE C /*Must use table alias*/

CREATE TYPE RECTANGLE_TYPE AS OBJECT
(
    TOP_LEFT POINT_TYPE,
    WIDTH    NUMBER,
    HEIGHT   NUMBER,
    MAP MEMBER FUNCTION GETAREA RETURN NUMBER,
    MEMBER FUNCTION CONTAINS(PT IN POINT_TYPE) RETURN NUMBER

    /*
       ORDER MEMBER FUNCTION GETLEN(PT IN RECTANGLE_TYPE)
       RETURN NUMBER,
    */
    /*不能与MAP共存, IN参数必须与本身TYPE一致*/
)
/

CREATE OR REPLACE TYPE BODY RECTANGLE_TYPE AS

    MAP MEMBER FUNCTION GETAREA RETURN NUMBER AS
    BEGIN
        RETURN (WIDTH * HEIGHT);
    END;

    MEMBER FUNCTION CONTAINS(PT IN POINT_TYPE)
                    RETURN NUMBER
    AS
        IS_INSIDE NUMBER := 0;
    BEGIN
        IF (PT.X > TOP_LEFT.X AND PT.X < TOP_LEFT.X + WIDTH AND
            PT.Y > TOP_LEFT.Y AND PT.Y < TOP_LEFT.Y + HEIGHT
           )
        THEN
            IS_INSIDE := 1;
        END IF;

RETURN IS_INSIDE;
END;

END;
/

CREATE TABLE RECTANGLES
(
LABEL VARCHAR2(25),
RECTANGLE RECTANGLE_TYPE
)
/

INSERT INTO RECTANGLES
VALUES('One', RECTANGLE_TYPE(POINT_TYPE(10,50),40,20));

INSERT INTO RECTANGLES
VALUES('One', RECTANGLE_TYPE(POINT_TYPE(0,0),10,10));

SELECT LABEL, R.RECTANGLE.CONTAINS(POINT_TYPE(20,60)) AS CONTAIN
FROM RECTANGLES R;

SELECT LABEL, R.RECTANGLE.GETAREA() AS AREA
FROM RECTANGLES R
ORDER BY RECTANGLE;

DROP TABLE RECTANGLES;
DROP TYPE BODY RECTANGLE_TYPE;
DROP TYPE RECTANGLE_TYPE;

--Style 1
CREATE TYPE TRIANGLE_TYPE AS OBJECT
(
    PT_A POINT_TYPE,
    PT_B POINT_TYPE,
    PT_C POINT_TYPE,
    STATIC FUNCTION GETAREA(LAB IN VARCHAR2)
           RETURN NUMBER
    AS LANGUAGE JAVA
    NAME 'Triangle.getArea(java.lang.String) return double'
)
/

--Style 2
CREATE TYPE TRIANGLE_TYPE AS OBJECT
(
    PT_A POINT_TYPE,
    PT_B POINT_TYPE,
    PT_C POINT_TYPE,
    STATIC FUNCTION GETAREA(LAB IN VARCHAR2)
           RETURN NUMBER
)
/

CREATE OR REPLACE TYPE BODY TRIANGLE_TYPE AS
    STATIC FUNCTION GETAREA(LAB IN VARCHAR2)
           RETURN NUMBER
    AS LANGUAGE JAVA
    NAME 'Triangle.getArea(java.lang.String) return double';
END;
/

CREATE TABLE RECTANGLES
(
    LABEL VARCHAR2(25),
    RECTANGLE TRIANGLE_TYPE,
    CONSTRAINT TRINAGLE_PK
    PRIMARY KEY (LABEL)
)
/

/*
*Java连Oracle注意事项:
*    1, 不同版本的JDK对应不同的jar, Oracle 9i的jdbc/lib目录下只带有与JDK
*        1.3相对应的jar/zip, 要支持JDK1.4必须下载ojdbc14.jar
*    2, 必须throws SQLException
*    3, 关于CLASSPATH,如出现"main"...这类错误,
*       最好指定 -classpath option, 如 -classpath %CLASSPATH%;.
*    4, 提倡用DriverManager.registerDriver方式注册.
*    5, 用oci连接时,要把ORACLE_HOME/bin下的ocijdbc9.dll和heteroxa9.dll
*       copy至c:/winnt/system32下
**/

INSERT INTO RECTANGLES
VALUES('One',
TRIANGLE_TYPE(POINT_TYPE(1,2),POINT_TYPE(5,6),POINT_TYPE(3,2))
);

INSERT INTO RECTANGLES
VALUES('Two',
TRIANGLE_TYPE(POINT_TYPE(0,0),POINT_TYPE(0,4),POINT_TYPE(4,0))
);

SELECT LABEL, TRIANGLE_TYPE.GETAREA(LABEL) FROM RECTANGLES;

CREATE TYPE NUMBER_TABLE AS TABLE OF NUMBER;
/

CREATE TABLE DAILY_HIGHS
(
   CITY    VARCHAR2(32),
   STATE   VARCHAR2(32),
   COUNTRY VARCHAR(32),
   TEMPS   NUMBER_TABLE
)
NESTED TABLE TEMPS STORE AS DAILY_HIGHS_TEMPS
/

INSERT INTO DAILY_HIGHS
VALUES('Houston', 'TX', 'USA', NUMBER_TABLE(98, 97, 95));
/

UPDATE DAILY_HIGHS SET TEMPS = NUMBER_TABLE()--NULL

INSERT INTO TABLE(SELECT TEMPS FROM DAILY_HIGHS)
VALUES(98);
INSERT INTO TABLE(SELECT TEMPS FROM DAILY_HIGHS)
VALUES(97);
INSERT INTO TABLE(SELECT TEMPS FROM DAILY_HIGHS)
VALUES(95);

CREATE TYPE TEMP_RECORD AS OBJECT
(
   DAY     DATE,
   HIGH    NUMBER,
   LOW     NUMBER
)
/

CREATE TYPE TEMP_RECORD_TABLE AS TABLE OF TEMP_RECORD
/

CREATE TABLE DAILY_TEMPS
(
   CITY    VARCHAR2(32),
   STATE   VARCHAR2(32),
   COUNTRY VARCHAR(32),
   TEMPS   TEMP_RECORD_TABLE
)
NESTED TABLE TEMPS STORE AS DAILY_TEMPS_TEMPS
/

INSERT INTO DAILY_TEMPS
VALUES('Reno', 'NV', 'USA',
       TEMP_RECORD_TABLE(
                        TEMP_RECORD('18-04-2002',61,31),
                        TEMP_RECORD('29-04-2002',53,39),
                        TEMP_RECORD('29-04-2002',51,30)
                        )
      )
/

INSERT INTO DAILY_TEMPS
VALUES('Anchorage', 'AK', 'USA',
       TEMP_RECORD_TABLE(
                        TEMP_RECORD('18-04-2002',46,35),
                        TEMP_RECORD('29-04-2002',50,39),
                        TEMP_RECORD('30-04-2002',51,39)
                        )
      )
/

SELECT CITY, T.*
FROM DAILY_TEMPS D, TABLE(D.TEMPS) T
/

SELECT CITY, T.*
FROM DAILY_TEMPS D, TABLE(D.TEMPS) T
WHERE T.LOW < 35
/

CREATE TYPE PERSON_TYPE AS OBJECT
(
   NAME      VARCHAR2(32),
   DOB       DATE,
   SEX       CHAR(1),
   MEMBER FUNCTION GETAGE RETURN NUMBER
) NOT FINAL
/

CREATE TYPE EMPLOYEE_TYPE UNDER PERSON_TYPE
(
   COMPANY      VARCHAR2(25),
   DEPARTMENT   VARCHAR2(25),
   SALARY       NUMBER,
   JOB_TITLE    VARCHAR2(25),
   HIRE_DATE    DATE,
   MEMBER FUNCTION GETSENIORITY RETURN NUMBER
)
/

CREATE TYPE SHAPE_TYPE AS OBJECT
(
   COLOR          VARCHAR2(12),
   OUTLINE_STYLE VARCHAR2(12),
   NOT INSTANTIABLE MEMBER FUNCTION GETAREA RETURN NUMBER
) NOT INSTANTIABLE NOT FINAL
/

CREATE TYPE ELLIPSE_TYPE UNDER SHAPE_TYPE
(
   MINOR_AXIS    NUMBER,
   MAJOR_AXIS    NUMBER,
   CENTER        POINT_TYPE,
   OVERRIDING MEMBER FUNCTION GETAREA RETURN NUMBER
)
/

CREATE OR REPLACE TYPE BODY ELLIPSE_TYPE
AS
OVERRIDING MEMBER FUNCTION GETAREA
RETURN NUMBER
AS
BEGIN
RETURN MINOR_AXIS * MAJOR_AXIS * 3.1415927;
END;
END;
/

CREATE TABLE ELLIPSES
(
LABEL VARCHAR2(12),
ELLIPSE ELLIPSE_TYPE
)
/

INSERT INTO ELLIPSES
VALUES('One', ELLIPSE_TYPE('Red', 'Bold', 10, 20, POINT_TYPE(5,5)));

ALTER TYPE ELLIPSE_TYPE NOT FINAL CASCADE; //允许对象派生子类型

/*配置SQLJ
*    1, 从Oracle下载相应的SQLJ包, 并解压缩(假设存在C:/SQLJ9i文件夹中)
*    2, 在CLASSPATH变量中分别加入translator.jar和runtime12.jar文件的路径(C:/SQLJ9i/lib)
*    3, 在PATH变量中加入sqlj.exe文件的路径(C:/SQLJ9i/bin)
*    4, 要注意文件的版本问题.比如我开始在JDK1.4.2版本下配置了一个oracle 10g的ojdbc14.jar
*       运行sqlj时会报错说JDBC版本在8i以下, 后来又下载一个Oracle 9i的ojdbc14.jar才消除此错误
**/