PLSQL语言基础

re2212

构：
    DECLARE        //标记声明部分
            //此处用来定义常量，变量，类型和游标
   
    BEGIN        //标记程序体开头部分        
            //编写各种PL/SQL语句，函数，存储过程

    EXCEPTION    //异常处理开始
            //编写异常处理代码
   
    END;        //程序体结束

PL/SQL可以独立编译并存储在数据库中，有4种可存储的程序：函数，存储过程，包和触发器
例如：
SYS AS SYSDBA@ORCL>SET SERVEROUTPUT ON;
SYS AS SYSDBA@ORCL>DECLARE
  2  VAR_DEPNAME VARCHAR2(100);
  3  BEGIN
  4  SELECT DEPARTMENT_NAME INTO VAR_DEPNAME
  5  FROM HR.DEPARTMENTS WHERE DEPARTMENT_ID=270;
  6  DBMS_OUTPUT.PUT_LINE(VAR_DEPNAME);
  7  END;
  8  /
声明变量和常量：
    DECLARE
        <变量名1> <数据类型1>;
        <变量名N> <数据类型N>;

    PL/SQL常用的数据类型：
    BLOB    二进制大对象，可以用来存储图像和文档等二进制数据
    BOOLEAN    布尔数据类型，支持TRUE/FALSE
    CHAR    固定长度字符串
    CLOB    字符大对象，最大保存4G字符数据
    DATE    存储全部日期的固定长度字符串
    LONG    可变长度字符串
    NUMBER    可变长度数值
    RAW    二进制数据的可变长度字符串
    VARCHAR2    可变长度字符串
   
    声明常量：
    <常量名> CONSTANT <数据类型> := <值>
    SYS AS SYSDBA@ORCL>SET SERVEROUTPUT ON;
    SYS AS SYSDBA@ORCL>DECLARE
      2  CONVERSION CONSTANT VARCHAR2(20) :='1.0';
      3  BEGIN
      4  DBMS_OUTPUT.PUT_LINE(CONVERSION);
      5  END;
     6  /
   
    声明变量：
    <变量名> <数据类型> [(宽度):=<初始值>]
    SYS AS SYSDBA@ORCL>SET SERVEROUTPUT ON
    SYS AS SYSDBA@ORCL>DECLARE
      2  DEPNAME VARCHAR2(20):='HR DEPARTMENT';
      3  BEGIN
      4  DBMS_OUTPUT.PUT_LINE(DEPNAME);
      5  END;
      6  /
    HR DEPARTMENT

    使用赋值语句
    SYS AS SYSDBA@ORCL>SET SERVEROUTPUT ON;
    SYS AS SYSDBA@ORCL>DECLARE
      2  BOOKNAME VARCHAR2(50);
      3  BEGIN
      4  BOOKNAME:='ORACLE BOOKS';
      5  DBMS_OUTPUT.PUT_LINE(BOOKNAME);
      6  END;
      7  /
    ORACLE BOOKS
   
    使用条件语句(IF)
    IF<条件表达式> THEN
        <执行语句>....<执行语句>
    [ELSIF<条件表达式> THEN
        <执行语句>....<执行语句>
    ...
    ELSE
    <执行语句>]
    END IF;
    SYS AS SYSDBA@ORCL>SET SERVEROUTPUT ON;
    SYS AS SYSDBA@ORCL>DECLARE
      2  NUM INTEGER:=-11;
      3  BEGIN
      4  IF NUM<0 THEN
      5  DBMS_OUTPUT.PUT_LINE('FUSHU');
      6  ELSIF NUM>0 THEN
      7  DBMS_OUTPUT.PUT_LINE('ZHENGSHU');
      8  ELSE
      9  DBMS_OUTPUT.PUT_LINE('0');
     10  END IF;
     11  END;
     12  /
    FUSHU

    分支语句CASE
    CASE <变量>
        WHEN <表达式1> THEN 值1
        WHEN <表达式n> THEN 值n
        ELSE 值n + 1
    END;

    SYS AS SYSDBA@ORCL>SET SERVEROUTPUT ON;
    SYS AS SYSDBA@ORCL>DECLARE
      2  VARDAY INTEGER:=3;
      3  RESULT VARCHAR2(20);
      4  BEGIN
      5  RESULT:=CASE VARDAY
      6  WHEN 1 THEN '1'
      7  WHEN 2 THEN '2'
      8  WHEN 3 THEN '3'
      9  ELSE '0'
     10  END;
     11  DBMS_OUTPUT.PUT_LINE(RESULT);
     12  END;
     13  /
    3

    循环语句LOOP....EXIT....END
      LOOP
        <程序块1>
        IF<条件表达式>THEN
           EXIT
        END IF
        <程序块2>
        END LOOP;

    SYS AS SYSDBA@ORCL>SET SERVEROUTPUT ON;
    SYS AS SYSDBA@ORCL>DECLARE
      2  V_NUM INTEGER:=1;
      3  V_SUM INTEGER:=0;
      4  BEGIN
      5  LOOP
      6  V_SUM:=V_SUM+V_NUM;
      7  DBMS_OUTPUT.PUT_LINE(V_NUM);
      8  IF V_NUM=4 THEN
      9  EXIT;
     10  END IF ;
     11  DBMS_OUTPUT.PUT_LINE('+');
     12  V_NUM:=V_NUM+1;
     13  END LOOP;
     14  DBMS_OUTPUT.PUT_LINE('=');
     15  DBMS_OUTPUT.PUT_LINE(V_SUM);
      16  END;
     17  /
    1
    +
    2
    +
    3
    +
    4
    =
    10

    循环语句LOOP....EXIT...WHEN...END
   
    LOOP
        <程序块1>
        EXIT WHEN <条件表达式>
        <程序块2>
    END LOOP;

    SYS AS SYSDBA@ORCL>SET SERVEROUTPUT ON;
    SYS AS SYSDBA@ORCL>DECLARE
      2  V_NUM INTEGER:=1;
      3  V_SUM INTEGER:=0;
      4  BEGIN
      5  LOOP
      6  V_SUM:=V_SUM+V_NUM;
      7  DBMS_OUTPUT.PUT_LINE(V_NUM);
      8  EXIT WHEN V_NUM=4;
      9  DBMS_OUTPUT.PUT_LINE('+');
     10  V_NUM:=V_NUM+1;
     11  END LOOP;
     12  DBMS_OUTPUT.PUT_LINE('=');
     13  DBMS_OUTPUT.PUT_LINE(V_SUM);
     14  END;
     15  /
    1
    +
    2
    +
    3
    +
    4        
    =   
    10


    循环语句WHILE....LOOP....END LOOP
   
    WHILE<条件表达式>
    LOOP
        <程序块>
    END LOOP;

    SYS AS SYSDBA@ORCL>SET SERVEROUTPUT ON;
    SYS AS SYSDBA@ORCL>DECLARE      
    2  V_NUM INTEGER:=1;
      3  V_SUM INTEGER:=0;
      4  BEGIN
      5  WHILE V_NUM<=4
      6  LOOP
      7  V_SUM:=V_SUM+V_NUM;
      8  DBMS_OUTPUT.PUT_LINE(V_NUM);
      9  IF V_NUM<4 THEN
     10  DBMS_OUTPUT.PUT_LINE('+');
     11  END IF;
     12  V_NUM:=V_NUM+1;
     13  END LOOP;
     14  DBMS_OUTPUT.PUT_LINE('=');
     15  DBMS_OUTPUT.PUT_LINE(V_SUM);
     16  END;
     17  /
    1
    +
    2
    +
    3
    +
    4
    =
    10

    循环语句FOR...IN...LOOP...END LOOP
    FOR <循环变量>  IN <初始值>...<终止值>
    LOOP
        <程序块>
    END LOOP;

        SYS AS SYSDBA@ORCL>SET SERVEROUTPUT ON
    SYS AS SYSDBA@ORCL>DECLARE
      2  V_NUM INTEGER;
      3  V_SUM INTEGER:=0;
      4  BEGIN
      5  FOR V_NUM IN 1..4
      6  LOOP
      7  V_SUM:=V_SUM+V_NUM;
      8  DBMS_OUTPUT.PUT_LINE(V_NUM);
      9  IF V_NUM<4 THEN
     10  DBMS_OUTPUT.PUT_LINE('+');
     11  END IF ;
     12  END LOOP;
     13  DBMS_OUTPUT.PUT_line('=');
     14  DBMS_OUTPUT.PUT_LINE(V_SUM);
     15  END;
     16  /
    1
    +
    2
    +
    3
    +
    4
    =
    10


    数值型函数：
    ABS函数：
    SYS AS SYSDBA@ORCL>SET SERVEROUTPUT ON;
    SYS AS SYSDBA@ORCL>BEGIN
      2  DBMS_OUTPUT.PUT_LINE(ABS(-450));
      3  END;
      4  /
    450
   
    CEIL函数
    SYS AS SYSDBA@ORCL>BEGIN
      2  DBMS_OUTPUT.PUT_LINE(CEIL(0));
      3  DBMS_OUTPUT.PUT_LINE(CEIL(-112.74));
      4  DBMS_OUTPUT.PUT_LINE(CEIL(116.34));
      5  END;
      6  /
    0
    -112
    117
   
    FLOOR函数
    SYS AS SYSDBA@ORCL>SET SERVEROUTPUT ON;
    SYS AS SYSDBA@ORCL>BEGIN
      2  DBMS_OUTPUT.PUT_LINE(FLOOR(116.34));
      3  DBMS_OUTPUT.PUT_LINE(FLOOR(-112.74));
      4  DBMS_OUTPUT.PUT_LINE(FLOOR(0));
      5  END;
      6  /
    116
    -113
    0
   
    POWER 函数
    SYS AS SYSDBA@ORCL>SET SERVEROUTPUT ON
    SYS AS SYSDBA@ORCL>BEGIN
      2  DBMS_OUTPUT.PUT_LINE(POWER(10,3));
      3  END;
      4  /
    1000
    ROUND函数
    SYS AS SYSDBA@ORCL>SET SERVEROUTPUT ON
    SYS AS SYSDBA@ORCL>BEGIN
      2  DBMS_OUTPUT.PUT_LINE(ROUND(123.45,2));
      3  DBMS_OUTPUT.PUT_LINE(ROUND(123.45,0));
      4  DBMS_OUTPUT.PUT_LINE(ROUND(123.45,-1));
      5  END;
      6  /
    123.45
    123
    120
   
    字符型函数
    ASCII函数：(返回首字母的ASCII值)
    SYS AS SYSDBA@ORCL>SET SERVEROUTPUT ON
    SYS AS SYSDBA@ORCL>BEGIN
      2  DBMS_OUTPUT.PUT_LINE(ASCII('ABC'));
      3  END;
      4  /
    65

    PL/SQL procedure successfully completed.

    Elapsed: 00:00:00.05
    SYS AS SYSDBA@ORCL>BEGIN
      2  DBMS_OUTPUT.PUT_LINE(ASCII('BC'));
      3  END;
      4  /
    66

    LENGTH函数（返回指定字段的长度）
    SYS AS SYSDBA@ORCL>SELECT DEPARTMENT_NAME,LENGTH(DEPARTMENT_NAME) FROM HR.DEPARTMENTS WHERE ROWNUM<3;

    DEPARTMENT_NAME            LENGTH(DEPARTMENT_NAME)
    ------------------------------ -----------------------
    Administration                        14
    Marketing                         9

    UPPER函数：（将字符串转换为大写字母）
    SYS AS SYSDBA@ORCL>SET SERVEROUTPUT ON
    SYS AS SYSDBA@ORCL>BEGIN
      2  DBMS_OUTPUT.PUT_LINE(UPPER('abc'));
      3  END;
      4  /
       ABC
   
    日期型函数：
    SYSDATE函数
    SYS AS SYSDBA@ORCL>SET SERVEROUTPUT ON;
    SYS AS SYSDBA@ORCL>BEGIN
      2  DBMS_OUTPUT.PUT_LINE(SYSDATE);
      3  END;
      4  /
   
    TO_CHAR函数(将日期型数据DATE，转换成FORMAT指定格式的字符串) TO_CHAR(DATE,FORMAT)
    SYS AS SYSDBA@ORCL>SET SERVEROUTPUT ON
    SYS AS SYSDBA@ORCL>BEGIN
      2  DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE));
      3  END;
      4  /
    22-DEC-14
   
    LAST_DAY函数（返回指定日期所在月的最后一天）
    SYS AS SYSDBA@ORCL>BEGIN
      2  DBMS_OUTPUT.PUT_LINE(LAST_DAY(SYSDATE));
      3  END;
      4  /
    31-DEC-14

    TO_DATE函数TO_DATE(STRING,FORMAT)将字符串STRING转换成以FORMAT指定格式的日期型数据
      1  BEGIN
      2  DBMS_OUTPUT.PUT_LINE(TO_DATE('2010-2-5','YYYY-MM-DD'));
      3* END;
    SYS AS SYSDBA@ORCL>/
    05-FEB-10
        
    MONTHS_BETWEEN函数(返回DATE1减去DATE2得到的月份整数)
    SYS AS SYSDBA@ORCL>DECLARE
      2  DATE1 VARCHAR2(20):='2010-05-05';
      3  DATE2 VARCHAR2(20):='2010-10-05';
      4  BEGIN
      5  DBMS_OUTPUT.PUT_LINE(MONTHS_BETWEEN(TO_DATE(DATE2,'YYYY-MM-DD'),TO_DATE(DATE1,'YYYY-MM-DD')));
      6  END;
      7  /
    5
   
    统计函数：
    COUNT()统计总数量
    SYS AS SYSDBA@ORCL>SELECT COUNT(DEPARTMENT_ID) FROM HR.DEPARTMENTS;

    COUNT(DEPARTMENT_ID)
    --------------------
          27
   
    MAX函数，统计最大值
    SYS AS SYSDBA@ORCL>SELECT MAX(DEPARTMENT_ID) FROM HR.DEPARTMENTS;

    MAX(DEPARTMENT_ID)
    ------------------
           270
        
    MIN函数(统计最小值)
    SYS AS SYSDBA@ORCL>SELECT MIN(DEPARTMENT_ID) FROM HR.DEPARTMENTS;

    MIN(DEPARTMENT_ID)
    ------------------
        10
   
    AVG()函数统计平均值：
    SYS AS SYSDBA@ORCL>SELECT AVG(DEPARTMENT_ID) FROM HR.DEPARTMENTS;

    AVG(DEPARTMENT_ID)
    ------------------
           140
    SUM()求总和
    SYS AS SYSDBA@ORCL>SELECT sum(DEPARTMENT_ID) FROM HR.DEPARTMENTS;

    SUM(DEPARTMENT_ID)
    ------------------
          3780

    异常处理：
    EXCEPTION
        WHEN <异常情况名> THEN        //异常情况名由ORACLE系统定义好的名字
            异常处理代码
        WHEN <异常情况名> THEN
            异常处理代码
        WHEN OTHERS THEN
            异常处理代码
            
    SYS AS SYSDBA@ORCL>DECLARE
      2  X NUMBER;
      3  BEGIN
      4  X:='ABC';
      5  EXCEPTION
      6  WHEN VALUE_ERROR THEN
      7  DBMS_OUTPUT.PUT_LINE('1223323');
      8  END;
      9  /
    1223323