PL/SQL - CREATE statements Cheat Sheet (DRAFT) by private522288

CREATE OR REPLACE PACKAGE TEST_PACKAGE AS
  FUNCTION TEST_FUNCTION RETURN VARCHAR2;
  PROCEDURE TEST_PROCEDURE;
END TEST_PACKAGE;
/
CREATE OR REPLACE PACKAGE BODY TEST_PACKAGE AS
    FUNCTION TEST_FUNCTION RETURN VARCHAR2 AS
        V_TEST  VARCHAR2(10);
        V_TEST2 VARCHAR2(10) NOT NULL := 'test2';
    BEGIN
        V_TEST := 'test';
        RETURN V_TEST;
    END TEST_FUNCTION;

    PROCEDURE TEST_PROCEDURE IS
    BEGIN
        NULL;
    END TEST_PROCEDURE;
END TEST_PACKAGE;
/

-- an abstract object
CREATE OR REPLACE TYPE RECTANGLE FORCE AS
    OBJECT (
        LENGTH NUMBER,
        WIDTH NUMBER,
        NOT INSTANTIABLE NOT FINAL MEMBER PROCEDURE DISPLAY,
        -- define a map or order function for comparison
        NOT INSTANTIABLE NOT FINAL MAP MEMBER FUNCTION AREA
                RETURN NUMBER
    ) NOT INSTANTIABLE NOT FINAL;
/
CREATE OR REPLACE TYPE SQUARE UNDER RECTANGLE (
    CONSTRUCTOR FUNCTION SQUARE (LENGTH NUMBER, WIDTH NUMBER)
         RETURN SELF AS RESULT,
    OVERRIDING FINAL MEMBER PROCEDURE DISPLAY,
    OVERRIDING FINAL MAP MEMBER FUNCTION AREA RETURN NUMBER,
    STATIC FUNCTION TEST RETURN VARCHAR2
) FINAL;
/
CREATE OR REPLACE TYPE BODY SQUARE AS
    CONSTRUCTOR FUNCTION SQUARE (LENGTH NUMBER, WIDTH NUMBER)
         RETURN SELF AS RESULT AS
    BEGIN
        SELF.LENGTH := LENGTH;
        SELF.WIDTH := WIDTH;
        RETURN;
    END;
    OVERRIDING FINAL MEMBER PROCEDURE DISPLAY AS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Square: '
                             || SELF.LENGTH);
    END;
    OVERRIDING FINAL MAP MEMBER FUNCTION AREA
         RETURN NUMBER AS
    BEGIN
        RETURN LENGTH * LENGTH;
    END;
    STATIC FUNCTION TEST RETURN VARCHAR2 AS
    BEGIN
        -- SELF and instance attributes are not available
        -- in static functions
        RETURN 'test';
    END;
END;
/