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, SQLERRMdeclare    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, T2WHERE (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;