plsql面向对象

一，例子

1.创建type父类

CREATE OR REPLACE TYPE CUSTOMER_T AS OBJECT
(
  CUSTOMER_ID NUMBER(18)
)
NOT INSTANTIABLE NOT FINAL

CREATE OR REPLACE TYPE CUSTOMER_INFO_T UNDER CUSTOMER_T(CUSTOMER_AGE VARCHAR2(100),
 constructor function CUSTOMER_INFO_T(customer_id number,customer_age VARCHAR2) return self as result
)

CREATE OR REPLACE TYPE BODY CUSTOMER_INFO_T AS
  CONSTRUCTOR FUNCTION CUSTOMER_INFO_T(CUSTOMER_ID  NUMBER,
                                       CUSTOMER_AGE VARCHAR2)
    RETURN SELF AS RESULT IS
  BEGIN
    SELF.CUSTOMER_ID  := CUSTOMER_ID;
    SELF.CUSTOMER_AGE := CUSTOMER_AGE;
      RETURN;
  END CUSTOMER_INFO_T;

END;

CREATE OR REPLACE PROCEDURE GET_CUSTOMER(P_CUSTOMER_INFO CUSTOMER_INFO_T) IS
  V_CUSTOMER_INFO CUSTOMER_INFO%ROWTYPE;
BEGIN
  SELECT T1.*
    INTO V_CUSTOMER_INFO
    FROM CUSTOMER_INFO T1
   WHERE T1.CUSTOMER_ID = P_CUSTOMER_INFO.CUSTOMER_ID;
  DBMS_OUTPUT.PUT_LINE('id:' || V_CUSTOMER_INFO.CUSTOMER_ID || ' study:' ||
                       V_CUSTOMER_INFO.STUDY);
END GET_CUSTOMER;

DECLARE
  -- Local variables here
  V_CUSTOMER      CUSTOMER_INFO_T;
BEGIN
  -- Test statements here
  V_CUSTOMER      := NEW CUSTOMER_INFO_T(90611278, 'test');
  GET_CUSTOMER(V_CUSTOMER);

END;

1.创建type

CREATE OR REPLACE TYPE CUSTOMER_INFO_RESULT_T UNDER CUSTOMER_T
(
  STUDY  VARCHAR2(100),
  MARRY  VARCHAR2(20),
  JOB    VARCHAR2(100),
  SALARY VARCHAR2(100),
  CONSTRUCTOR FUNCTION CUSTOMER_INFO_RESULT_T RETURN SELF AS RESULT
)

CREATE OR REPLACE TYPE BODY CUSTOMER_INFO_RESULT_T IS

CONSTRUCTOR FUNCTION CUSTOMER_INFO_RESULT_T RETURN SELF AS RESULT IS BEGIN RETURN; END CUSTOMER_INFO_RESULT_T;

END;

CREATE OR REPLACE PROCEDURE GET_CUSTOMER_INFO(P_CUSTOMER_INFO CUSTOMER_INFO_T,
                                              
                                              P_CUSTOMER_INFO_REUSLT OUT CUSTOMER_INFO_RESULT_T) IS
  
BEGIN
  P_CUSTOMER_INFO_REUSLT := NEW CUSTOMER_INFO_RESULT_T();
  SELECT T1.CUSTOMER_ID, T1.STUDY, T1.JOB,t1.salary
    INTO P_CUSTOMER_INFO_REUSLT.CUSTOMER_ID,
         P_CUSTOMER_INFO_REUSLT.STUDY,
         P_CUSTOMER_INFO_REUSLT.JOB,
         P_CUSTOMER_INFO_REUSLT.SALARY
    FROM CUSTOMER_INFO T1
   WHERE T1.CUSTOMER_ID = P_CUSTOMER_INFO.CUSTOMER_ID;
END GET_CUSTOMER_INFO;

DECLARE
  -- Local variables here
  V_CUSTOMER      CUSTOMER_INFO_T;
  V_CUSTOMER_INFO CUSTOMER_INFO_RESULT_T;
BEGIN
  -- Test statements here
  --V_CUSTOMER_INFO := NEW CUSTOMER_INFO_RESULT_T();
  V_CUSTOMER      := NEW CUSTOMER_INFO_T(90611278, 'test');
  GET_CUSTOMER(V_CUSTOMER);
  GET_CUSTOMER_INFO(V_CUSTOMER, V_CUSTOMER_INFO);
  DBMS_OUTPUT.PUT_LINE('id:' || V_CUSTOMER_INFO.CUSTOMER_ID || '  study:' ||
                       V_CUSTOMER_INFO.STUDY || '  job:' ||
                       V_CUSTOMER_INFO.JOB || '  salary:' ||
                       V_CUSTOMER_INFO.SALARY);

END;