PLSQL(1)

PLSQLl编程                      

    plsql是Oracle在标准的sql语言上的扩展

    特点：可以在数据库中定义变量，常量，还可以使用条件语句和判断语句以及异常等

PLSQL程序组成部分         

    PLSQL由 声明部分、执行部分、异常处理部分组成

    DECLARE   -- 声明部分

    -- 在此声明pl/sql用到的变量

    BEGIN   -- 执行

　 -- 在此写执行语句

    EXCEPTION

    -- 执行异常 

    END;

简单的PLSQL块               

DECLARE     -- 声明并赋值       变量名 类型 := 值;          ：= 表示赋值    = 表示条件判断
    V_NUM NUMBER := 10;    -- PLSQL在声明变量时在前面加v_ 代表变量
BEGIN
    V_NUM := V_NUM + 1;   -- 对V_NUM加1
    DBMS_OUTPUT.put_line('NUM' || V_NUM);   -- 对结果进行输出
END;

SELECT...INTO、%TYPE、 %ROWTYPE、常量        

SELECT...INTO...    

[1] 声明变量赋值为员工表中Smith的员工编号    SELECT ...INTO 

SELECT...INTO可以将表中查询到的数据赋值给变量V_NUM

DECLARE
    V_NUM NUMBER;    -- 声明
BEGIN
    SELECT EMPNO INTO V_NUM FROM EMP WHERE ENAME = 'SMITH';
    DBMS_OUTPUT.put_line('NUM' || V_NUM);
END;

%TYPE               

[2] 声明变量,变量的类型是scott用户下emp表empno字段的字段类型    %TYPE

DECLARE
    V_NUM SCOTT.EMP.EMPNO%TYPE;  -- 声明类型是SCOTT用户下的EMP表的EMPNO列对应的类型
BEGIN
    SELECT EMPNO INTO V_NUM FROM EMP WHERE ENAME = 'SMITH';
    DBMS_OUTPUT.put_line('NUM' || V_NUM);
END;

%ROWTYPE              

[3] 取一行数据   %rowtype

DECLARE
    V_EMPROW SCOTT.EMP%ROWTYPE;  -- 拿到SCOTT用户下的EMP表的所有列数据的类型
BEGIN
    SELECT * INTO V_EMPROW FROM EMP WHERE ENAME = 'SMITH';
    DBMS_OUTPUT.put_line(V_EMPROW.ENAME || V_EMPROW.EMPNO);
END;

常量                          

DECLARE
    -- 声明
    C_PI CONSTANT NUMBER := 3.1415; -- CONSTANT: 常量，一旦设定，不能改变    命名规范：常量前面加C
    V_R NUMBER := 20;
    V_AREA NUMBER;
BEGIN
    V_AREA := C_PI*V_R*V_R;
    DBMS_OUTPUT.put_line('圆的面积：' || V_AREA);
END;

判断语句                      

IF判断       

DECLARE
    V_SCORE NUMBER;
BEGIN
    V_SCORE := '&请输入成绩';    -- &表示输入
    IF(V_SCORE >= 90) THEN 
        DBMS_OUTPUT.PUT_LINE('去玩游戏');
    ELSIF (V_SCOE >= 80) THEN
        DBMS_OUTPUT.PUT_LINE('去报补习班');
    ELSIF (V_SCORE >= 70) THEN
        DBMS_OUTPUT.PUT_LINE('去报两个补习班');
    ELSE
        DBMS_OUTPUT.PUT_LINE('去写作业');
    END IF;
END;

SWITCH判断           

DECLARE
    V_SCORE NUMBER;
BEGIN
    V_SCORE := '&请输入成绩';
    CASE
        WHEN V_SCORE > 90 THEN DBMS_OUTPUT.PUT_LINE('去玩游戏');
        WHEN V_SCORE > 80 THEN DBMS_OUTPUT.PUT_LINE('去报补习班');
        WHEN V_SCORE > 70 THEN DBMS_OUTPUT.PUT_LINE('去报两个补习班');
        ELSE DBMS_OUTPUT.PUT_LINE('去写作业');
    END CASE;
END;

循环结构                  

LOOP循环     

-- 要求一个变量,每次循环减1,并输出结果 ,当变量小于3时,退出循环
DECLARE
    V_COUNT NUMBER := 10;
BEGIN
    LOOP    -- 循环开始
        V_COUNT := V_COUNT - 1;     -- 减1
        EXIT WHEN V_COUNT < 3;      -- 当V_COUNT小于3时，退出循环
        DBMS_OUTPUT.PUT_LINE(V_COUNT);
    END LOOP;     -- 结束循环
END;


DECLARE
    V_COUNT := 10;
BEGIN
    LOOP
        V_COUNT : V_COUNT - 1;
        IF V_COUNT < 3 THEN
            EXIT;       -- 满足条件，退出循环
        DBMS_OUTPUT.PUT_LINE(V_COUNT);
    END LOOP;
END;

WHILE循环                 

-- 声明变量1,每次循环加1,并输出结果,当变量大于20的时候,退出循环
DECLARE
    V_NUM NUMBER := 1;    -- 声明变量并赋值
BEGIN
    WHILE V_NUM < 20 LOOP    -- 开始循环
        DBMS_OUTPUT_PUT_LINE(V_NUM);
        V_NUM := V_NUM + 1;
        END LOOP;                      -- 结束循环
END; 

FOR循环                     

-- 变量从0开始,变量等于10,每次循环+1,算循环的次数
DECLARE
    V_NUM NUMBER := 10;
BEGIN
    FOR I IN 0..10 LOOP    -- 0..10表示0到10
        V_NUM := V_NUM + 1;
        DBMS_OUTPUT.PUT_LINE(V_NUM);
        END LOOP;
END;

PLSQL之异常处理                    

使用EXCEPTION处理异常      

DECLARE
    V_TEMP NUMBER(4);
BEGIN
    SELECT EMPNO INTO V_TEMP FROM EMP WHERE DEPNO = 100; -- 这里DEPNO等于10的列不只一条，而V_TEMP只能接收一条数据，所以会造成异常
EXCEPTION
    WHEN TOO_MANY_ROWS THEN   
        DBMS_OUTPUT.PUT_LINE('返回的数据太多了');
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('没有数据啊');
    WHEN OTHERS THEN       -- 使用OTHERS表示其他异常 
        DBMS_OUTPUT.PUT_LINE('其他异常');
END;

自定义异常          

DECLARE
    V_NUMBER1 NUMBER;      -- 声明变量
    V_NUMBER2 NUMBER;
    V_RESULT NUMBER;
    E_NONUMBER EXCEPTION;    -- 声明异常
 
    PRAGMA EXCEPTION_INIT (E_NONUMBER, -6502);  -- 将错误的代码跟自定义的异常名称绑定，并注册到系统里面
BEGIN
    V_NUMBER1 := '& 请输入被除数';
    V_NUMBER2 := '& 请输入除数';
    V_RESULT := V_NUMBER1 / V_NUMBER2;
    DBMS_OUTPUT.PUT_LINE(V_RESULT);
EXCEPTION
    WHEN E_NONUMBER THEN
        DBMS_OUTPUT.PUT_LINE('你输入的不是数字');
END;

建立错误日志表      

-- 创建表
CREATE TABLE ERROLOG(    --错误日志
   ERROCODE NUMBER,       -- 出错的编码   (ORA-XXXX)
   ERROMSG VARCHAR2 (1024),  --出错的信息
   ERRODATE DATE      --出错的时间
)



-- 记录错误日志
DECLARE 
   V_DEPTNO DEPT.DEPTNO%TYPE := 10;   --声明一个变量v_deptno  是dept 表的deptno类型
   ERROCODE NUMBER;   --出错的编码
   ERROMSG VARCHAR2(1024);      --出错的信息
   
BEGIN
  DELETE FROM DEPT WHERE DEPTNO = 90;  
  DELETE FROM DEPT WHERE DEPTNO = V_DEPTNO;
       COMMIT;
EXCEPTION
  WHEN OTHERS THEN
        ROLLBACK;    --回滚到之前的状态
    ERROCODE := SQLCODE;  --打印错误编码
    ERROMSG  := SQLERRM;  --打印错误的信息
    INSERT INTO ERROLOG VALUES (ERROCODE,ERROMSG,SYSDATE);
     COMMIT;  
END;