oracle plsql 包 package

创建表：

CREATE TABLE TB_USER
(
    ID INTEGER PRIMARY KEY,
    USER_NAME VARCHAR2(20) NOT NULL,
    USER_AGE INTEGER NOT NULL
);

CREATE SEQUENCE SEQ_USER
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
CACHE 10;

CREATE OR REPLACE TRIGGER TR_USER BEFORE INSERT ON TB_USER FOR EACH ROW
BEGIN
    SELECT SEQ_USER.NEXTVAL INTO :NEW.ID FROM DUAL;
END;

插入一些数据。然后创建包：

CREATE OR REPLACE PACKAGE SP_PACKAGE AS
       PROCEDURE UPDATE_USER(USER_ID IN NUMBER, AGE IN NUMBER);
       FUNCTION HI(USER_NAME IN VARCHAR2) RETURN VARCHAR2;
END;

CREATE OR REPLACE PACKAGE BODY SP_PACKAGE AS
PROCEDURE UPDATE_USER(USER_ID IN NUMBER, AGE IN NUMBER) IS
BEGIN
    UPDATE TB_USER SET USER_AGE = AGE WHERE ID = USER_ID;
    COMMIT;
END;

FUNCTION HI(USER_NAME IN VARCHAR2) RETURN VARCHAR2 IS
  BEGIN
    RETURN 'Hi ' || USER_NAME;
  END;
 
END;

执行：

SELECT SP_PACKAGE.HI('yang') FROM DUAL;

CALL SP_PACKAGE.UPDATE_USER(1, 50);