Java Oracle 学习整理(一)

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才消除此错误
**/