/*不带任何参数存储过程(输出系统日期)*/

CREATE OR REPLACE PROCEDURE output_date IS

BEGIN

    DBMS_OUTPUT.PUT_LINE(SYSDATE);

END output_date;

/*带参数in和out的存储过程*/

CREATE OR REPLACE PROCEDURE get_username(v_id IN NUMBER,v_username OUT VARCHAR2) AS

BEGIN

  SELECT username INTO v_username FROM t_users WHERE id = v_id; --变量赋值

EXCEPTION

    WHEN NO_DATA_FOUND THEN

    RAISE_APPLICATION_ERROR(-20001,'记录不存在');

END get_username;

CREATE OR REPLACE TRIGGER trigger_name

AFTER INSERT OR UPDATE OR DELETE ON ITIL_USR_DEPARTMENT_DAG

FOR EACH ROW

BEGIN

     CASE

         WHEN INSERTING THEN

              INSERT INTO sys(id,ORG_NAME,ALIAS,PARENT_ORG_NAME) VALUSE(:new.id,:new.name,:new.alias,:new.p_name);

         WHEN UPDATING THEN

              UPDATE sys SET ORG_NAME=:new.name,ALIAS=:new.alias,PARENT_ORG_NAME=:new.p_name WHERE id=:new.id;

         WHEN DELETING THEN

              DELETE sys WHERE id=:old.id;

     END CASE;

END;