PL/SQL块分类（匿名块、命名块、子程序、触发器）

-匿名块
DECLARE
    V_NAME VARCHAR2(5);
BEGIN
   SELECT NAME INTO V_NAME WHERE AGE=100;
   DBMS_OUTPUT.PUT_LINE(V_NAME);
EXCEPTION 
   WHER NO_DATA_FOUND THEN
   DBMS_OUTPUT.PUT_LINE('未查到到任何数据');
END;

-命名块<<OUTER>>DECLARE V_NAME VARCHAR2(5);BEGIN SELECT NAME INTO V_NAME WHERE AGE=100; DBMS_OUTPUT.PUT_LINE(V_NAME);EXCEPTION WHER NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('未查到到任何数据');END;--<<OUTER>>

-子程序之过程
CREATE OR REPLACE PROCEDURE UPDATE_SAL(NEWNAME VARCHAR2，_ID NUMBER)
IS 
ERROR_EXP EXCEPTION;
BEGIN
UPDATE TABLENAME SET NAME=NEWNAME WHERE ID=_ID;
IF SQL%NOTFOUND THEN
RAISE ERROR_EXP;
END IF;
EXCEPTION
WHEN ERROR_EXP
RAISE_APPLICATION_ERROR('-20004','error');
END;-
-执行过程
exec UPDATE_SAL('WT',100);

--子程序之函数
CREATE FUNCTION ANNUAL_INCOME(NAME VARCHAR2)
RETURN NUMBER IS
 ANNUAL_SALARY NUMBER(7，2);
BEGIN
 SELECT SAL*12+NVL(COMM,0) INTO ANNUAL_SALARY
FROM TABLENAME WHERE LOWER(ENUMA)=LOWER(NAME);
END;
--执行函数
SQL> VAR INCOME NUMBER
SQL> CALL ANNUAL_INCOME('NAME') INTO:INCOME
SQL> PRINT INCOME

--触发器 指隐含执行的存储过程
CREATE TRIGGER UPDATE_CASCADE
 AFTER UODATE OF deptno ON dept FOR EACH ROW
BEGIN
 UPDATE emp SET deptno=:new.deptno WHERE deptno=:old.deptno;
END;
如上例所示，触发器UPDATE_CASCADE用于实现联级更新，当更新 deptno列时，emp的deptno列也会相应更新