13-Oracle学习_PL/SQL

PL/SQL

一, 概述

 1, 每种数据库都有内部的语言
 2, PL/SQL是 Oracle 中使用的编程语言    
 3, 编程语言
    ① 数据类型
    ② 语法
 4, Procedural Language/SQL 
    过程化语言, 也就是带有分支循环的语言
    SQL语言功能太单一, 需要结合其他的语言完成复杂的功能.
    PL/SQL是Oracle数据库对SQL语句的扩展。
    在普通SQL语句的使用上增加了编程语言的特点，
    所以PL/SQL就是把数据操作和查询语句组织在PL/SQL代码的过程性单元中，
    通过逻辑判断、循环等操作实现复杂的功能或者计算的程序语言。

二, PL/SQL

1, 匿名块

  (1)格式
     [declare
        变量声明
     ]
     begin
        正常流程语句
     [exception
        异常处理
     ]
     end    
  (2)HelloWorld
SQL> set serveroutput on;
SQL> begin
  2          dbms_output.put_line('Hello World!');
  3      end;
  4      /
Hello World!
     注: 必须打开输出开关, 才能看到输出显示
  (3)declare 
    ① 变量名命名惯例, 以 "v_" 为前缀
    ② declare 变量名 类型;
    ③ 赋值语句->  变量名 := 值;
SQL> declare
  2    v_name varchar2(20);
  3  begin
  4    v_name := 'myname';
  5    dbms_output.put_line(v_name);
  6  end;
  7  /
myname
  (4)exception
SQL> declare
  2    v_num number := 0;
  3  begin
  4    v_num := 1 / v_num;
  5    dbms_output.put_line(v_num);
  6  exception
  7    when others then
  8      dbms_output.put_line('error');
  9  end;
 10  /
error    

2, 变量声明的规则

(1) 变量名不能使用保留字, 如 from, select 等
(2) 第一个字符必须是字母
(3) 变量名最多包含30个字符
(4) 不要与数据库的表或者列同名
(5) 每一行只能声明一个变量

3, 常用变量类型

(1) binary_integer  : 整数, 主要用来计数而不是用来表示字段类型.
(2) number          : 数字类型
(3) char            : 定长字符串
(4) varchar(2)      : 变长字符串
(5) date            : 日期
(6) long            : 长字符串, 最大 2GB
(7) boolean         : 布尔类型, 取值 true/false/null

4, 简单变量

(1)

declare
    v_temp number(1) := 0;
    v_count binary_integer := 0;
    v_sal number(7, 2) := 4000.00;
    v_date date := sysdate;
    v_pi constant number(3, 2) := 3.14;     -- 常量
    v_valid boolean := false;
    v_name varchar2(20) not null := 'MyName';   -- 不能取空值
begin
    dbms_output.put_line('v_temp value:' || v_temp);
end;

(2) 变量的作用
    通常用于存储某张表的字段的值.
(3) %type , 同步 表字段/其他变量 的类型

declare
    v_empno number(4);       -- 自定义类型   
    v_empno2 emp.empno%type; -- emp表的empno字段的类型
    v_empno3 v_empno2%type;  -- v_empno2的类型
begin
    dbms_output.put_line('test %type');
end;

5, 复杂变量 table record

(1) Table 变量类型 , 类似 java中的数组
注: 先声明类型, 在声明该类型的变量

declare
    type type_table_empno           -- 声明 类型 
        is table of emp.empno%type  -- 指定是 table类型 以及每个元素的类型
        index by binary_integer;    -- 指定下标的类型


    -- 声明一个 type_table_empno 类型的变量
    v_empnos type_table_empno;
begin
    v_empnos(0) := 1111;
    v_empnos(1) := 2222;
    v_empnos(2) := 3333;
    dbms_output.put_line(v_empnos(1));
end;

    
(2) Record 变量类型, 类似于 C语言中的结构体
①

declare
    type type_record_dept is record
        (
            deptno dept.deptno%type,
            dname dept.dname%type,
            loc dept.loc%type
        );
    v_temp type_record_dept;
begin
    v_temp.deptno := 50;
    v_temp.dname := 'zhangsan';
    dbms_output.put_line('v_temp.deptno ' || v_temp.deptno);
    dbms_output.put_line('v_temp.dname ' || v_temp.dname);
end;

② 使用 %rowtype声明 record变量

declare
    v_temp dept%rowtype;
begin
    v_temp.deptno := 55;
    v_temp.dname := 'zhangsan';
    v_temp.loc := 'WuHan';
    dbms_output.put_line('v_temp.deptno ' || v_temp.deptno);
    dbms_output.put_line('v_temp.dname ' || v_temp.dname);
    dbms_output.put_line('v_temp.loc ' || v_temp.loc);
end;

6, SQL语句的运用 into

(1) select into
注: 有且只能返回一行数据
① %type

declare
    v_ename emp.ename%type;
    v_sal emp.sal%type;
begin
    select ename, sal into v_ename, v_sal
    from emp
    where empno = 7566;
    dbms_output.put_line('v_ename ' || v_ename);
    dbms_output.put_line('v_sal ' || v_sal);
end;

②  %rowtype

declare
    v_emp emp%rowtype;
begin
    select * into v_emp 
    from emp 
    where empno = 7566;
    dbms_output.put_line('v_emp.empno ' || v_emp.empno);
    dbms_output.put_line('v_emp.ename ' || v_emp.ename);
    dbms_output.put_line('v_emp.sal ' || v_emp.sal);   
end;

(2) insert into

create table dept2 as select * from dept;
declare
    v_deptno dept.deptno%type := 50;
    v_dname dept.dname%type := 'game';
    v_loc dept.loc%type := 'WuHan';
begin
    insert into dept2 values(v_deptno, v_dname, v_loc);
    commit;
end;

(3) update -- sql%rowcount

注: 关键字sql, 其属性rowcount, 刚刚执行的语句影响了几条记录
create table emp2 as select * from emp;
declare
    v_deptno emp2.deptno%type := 10;
    v_count number;
begin
    update emp2 set sal = sal / 2 where deptno = v_deptno;
    -- select count(*) into v_count from emp; --影响一条记录
    dbms_output.put_line(sql%rowcount || ' 条记录被影响');
    commit;
end;

7, 循环 loop

(1) 类似于 java中 do{ .. }while(); 循环

declare
    i binary_integer := 1;
begin
    loop
        dbms_output.put_line(i);
        i := i + 1;
        exit when ( i >= 11 );
    end loop;
end;

(2) 类似于 java中 while(){} 循环

declare
    i binary_integer := 1;
begin   
    while i < 11 loop
        dbms_output.put_line(i);
        i := i + 1;
    end loop;
end;

(3) 类似于 java中 for(){} 循环
① for each

begin
    for i in 1..10 loop
        dbms_output.put_line(i);
    end loop;
end;

② 反转

begin
    for i in reverse 1..10 loop
        dbms_output.put_line(i);
    end loop;
end;

8, IF语句

① 
格式:
    if condition then
        ...
    end if;
例子:

declare
    x number := 1;
begin
    if x < 10 then
        dbms_output.put_line(x || ' is less than 10');
    end if;
end;

②
格式:
    if condition then
        ...
    else
        ...
    end if;
例子:

declare
    x number(2) := 11;
begin
    if x < 10 then
        dbms_output.put_line(x || ' is less than 10');
    else
        dbms_output.put_line(x || ' is bigger than 10');
    end if;
end;

③
格式:
if condition_1 then
    ...
elsif condition_2 then
    ...
else
    ...
end if;
举例:

declare
    x number(2) := 10;
begin
    if x < 10 then
        dbms_output.put_line(x || ' is less than 10');
    elsif x < 20 then
        dbms_output.put_line(x || ' is less then 20');
    else
        dbms_output.put_line(x || ' is bigger than 20');
    end if;
end;

9, 异常处理

(1) too_many_rows

declare
    v_temp number(4);
begin
    select empno into v_temp from emp where deptno = 10;
exception
    when too_many_rows then
        dbms_output.put_line('太多记录了');
    when others then
        dbms_output.put_line('error');
end;

(2) no_data_found

declare
    v_temp number(4);
begin
    select empno into v_temp from emp where empno = 2222;
exception 
    when no_data_found then
        dbms_output.put_line('没有数据');
end;

(3) 记录错误

-- 记录错误的日志表
create table errorlog
(
    id number primary key,
    errorcode number,
    errormsg varchar2(1024),
    errordate date
);


-- 创建序列
create sequence seq_errorlog_id start with 1 increment by 1;


-- 出错后, 插入错误日志表, SQLCODE, SQLERRM
declare
    v_deptno dept.deptno%type := 10;
    v_errorcode errorlog.errorcode%type;
    v_errormsg errorlog.errormsg%type;
begin
    delete from dept where deptno = v_deptno;
    commit;
exception
    when others then
        rollback;
        v_errorcode := SQLCODE;
        v_errormsg := SQLERRM;
        insert into errorlog 
          values(seq_errorlog_id.nextval, v_errorcode, v_errormsg, sysdate);
        commit;
end;


-- clean
-- drop sequence seq_errorlog_id;
-- drop table errorlog;

10, case 语句

参考: http://www.cnblogs.com/eshizhan/archive/2012/04/06/2435493.html
(1) CASE WHEN 表达式有两种形式
① 简单 case函数

case sex
  when '1' then '男'
  when '2' then '女'
  else '其他' 
end  

② case 搜索函数

case
  when sex = '1' then '男'
  when sex = '2' then '女'
  else '其他'
end

(2) CASE WHEN 在语句中不同位置的用法
① SELECT CASE WHEN 用法

SELECT grade, COUNT (CASE WHEN sex = 1 THEN 1      /*sex 1为男生，2位女生*/
                     ELSE NULL
                     END) 男生数,
              COUNT (CASE WHEN sex = 2 THEN 1
                     ELSE NULL
                     END) 女生数
FROM students GROUP BY grade;

② WHERE CASE WHEN 用法

SELECT T2.*, T1.*
FROM T1, T2
WHERE (CASE WHEN T2.COMPARE_TYPE = 'A' AND T1.SOME_TYPE LIKE 'NOTHING%' THEN 1
       WHEN T2.COMPARE_TYPE != 'A' AND T1.SOME_TYPE NOT LIKE 'NOTHING%' THEN 1
       ELSE 0
       END) = 1

③ GROUP BY CASE WHEN 用法

SELECT  
        CASE WHEN salary <= 500 THEN '1'  
        WHEN salary > 500 AND salary <= 600  THEN '2'  
        WHEN salary > 600 AND salary <= 800  THEN '3'  
        WHEN salary > 800 AND salary <= 1000 THEN '4'  
        ELSE NULL 
        END salary_class, -- 别名命名
        COUNT(*)  
FROM Table_A  
GROUP BY  
        CASE WHEN salary <= 500 THEN '1'  
        WHEN salary > 500 AND salary <= 600  THEN '2'  
        WHEN salary > 600 AND salary <= 800  THEN '3'  
        WHEN salary > 800 AND salary <= 1000 THEN '4'  
        ELSE NULL 
        END;