pl/sql （Oracle）可编程SQL

pl/sql : PL/SQL 是Oracle在标准法人SQL语句上的过程性扩展。PL/SQL 不仅允许嵌入SQL语句，还可以定义变量常量，允许使用条件语句和循环语句，异常处理
PL/SQL ：优势

支持面向对象编程，支持自定义类型，实例化，继承，程序块中的重载
更好的提升程序性能， PL/SQL 把一个PL/SQL语句块统一进行编译后，同时还可以把编译好的SQL快速存储到Oracle的服务器上面，可以重复调用，而SQL非过程语句，只能一次执行一条，PL/sql的执行快速高效
良好的移植性：使用PL/SQL编写应用程序，可以移植到任何操作系统上面，同时可以编写程序库，在不同环境中重用，
安全性：可以使用权限控制是否可以访问存储过程

目标

掌握PL/SQL程序结构
掌握流程控制语法
掌握复合变量的语法
掌握游标的用法
会使异常

过程、函数。无名块，他们之间可以互相嵌套

当定义变量的时候，建议使用v_作为前缀，v_sal,v_job。。。
当定义常量的时候，建议使用c_ 前缀
定义游标的时候，通常使用 _cursor 作为前缀 dept_cursor
定义异常的时候使用 e_

PL/SQL程序块

pl/sql程序块

pl/SQL 分为两种:无名块,命名块:函数,过程,包,触发器....
pl/sql 由3部分组成:定义部分,执行部分,异常处理部分
    定义部分 : 定义变量,常量,游标,异常,复合数据等类型.
    执行部分 : 实现用用模块的一些功能,dml,输出语句,判断....
    异常部分 : 处理异常的代码,当程序出现异常的时候执行.


    [DECLARE
        ... -- 定义部分
    ]
    begin
        .. -- 执行部分
        [ EXCEPTION
            ... -- 异常处理部分
        ]
    end;
DECLARE,begin,EXCEPTION 后面都没有分号,而end后则必须要带分号;

DECLARE
  -- 定义
  num NUMBER; -- 定义变量
BEGIN
  num := 6+6; -- 为变量赋值
  dbms_Output.put_line('6 + 6 = '||num);--输出变量

EXCEPTION -- 异常处理
    when OTHERS THEN
    Dbms_Output.put_line('出现异常了!');

END;

变量和类型

PL/SQL数据类型有标量类型，复合数据类型，参照数据类型，lob数据类型

标量只能存放单个数值，最常用的变量都是标量
1. 变量名以字母开头，不区分大小写
2. 变量由字母，数字，$,_组成
3. 变量长度最多30个字符
4. 变量名中不能有空格
5. 不能使用关键字
  variable_name [constant] data_type not null, [default| := value]
  varable_name: 变量名
  conatant ：是否是常量
  data_type : 数据类型（char,number, date, ）
  not null : 表示该变量非空,必须指定默认值
  default 或 := value : 给变量赋值默认值

c_id constant integer := 66;
v_gender char(1);
v_num number(5,2) default 66.6;
v_date date;
isfindshed boolean default true;
begin
end;

DECLARE

/*
--定义
num NUMBER; --定义变量
*/
v_sal NUMBER;

BEGIN
select sal into v_sal from emp where empno = 7788;
Dbms_Output.put_line(v_sal);

END;

pl/sql运算符

=   等于(逻辑判断)
<>,!=,~=,^= 不等于
:=  赋值运算符
=> 关系号
..  范围运算符
|| 字符串连接

注释

-- 单行注释

/*
    多行注释
    */
declare

/*
c_id constant integer := 66;
 v_gender char(1);
  v_num number(5,2) default 66.6;
  v_date date;
  isfindshed boolean default true;

*/

  v_sal number;
  
begin
  
  select sal into v_sal from emp where empno = 7788;
  dbms_output.put_line(v_sal);

end;

if 分支语句

condition1， condition2，只能是boolean 表达式
statements1,statements2,statements3 是PL/SQL语句，如果condition1为true 执行statement1
DECLARE

grade char(1) := 'F';
appraisal varchar2(20);
BEGIN
appraisal :=
case grade
when 'A' then '优'
when 'B' then '良'
when 'C' then '中'
when 'F' then '差'
else '没有这种成绩'
end;
dbms_output.put_line(appraisal);
END;
if语句基本选择结构,每一个if语句都有then,以if开头的语句不能跟结束符合,每一个if语句以 end if结束.每一个if只能有1个else

循环

基本循环， while 循环，for循环

1. 基本循环

loop
    statements
  exit [when condition]
  end loop;
至少会被执行一次.

declare
total number :=0;
salary number := 1200;
begin
loop total := total + salary;
exit when total >5000;
end loop;
dbms_output.put_line(total);
end;

2. while循环

while condition loop
......

    end loop;

declare
  total number :=1;
  v_count number :=1;
begin
    while total <50 loop
    total := total + total;
    v_count := v_count+1;
  end loop;
dbms_output.put_line('v_count'|| v_count ||'次结果'||total);

end;

3. for 循环

for loop_variable in [reverse] lower..upper loop
....
end loop;

loop_variable : 指定循环变量
reverse : 每次递减输出.
lower : 循环的起始值
upper : 循环的终止值

declare 
      i integer;
begin 
  for i in reverse 4..20 loop
  dbms_output.put_line(i);

end loop;
end;

GOTO 和 NULL

goto : 跳转结构.使用goto可以跳转到指定的设定标签"<<>>"创建标签

  declare 
      num number := 3;
      begin
        if num >1 then
        goto comper;
     end if;
       dbms_output.put_line('这是第一行输出');
           <<comper>>
    dbms_output.put_line('这是第二行输出');
end;

null

      declare
        i integer := 5;
          n1 number := null;
          n2 number := null;
      begin
        if n1 = n2 then 
        dbms_output.put_line('等于');
         elsif n1!= n2  then
             dbms_output.put_line('怒等于');
        else 
            dbms_output.put_line('其他');
        end if;
    end;

复合变量

1. %type ：指定变量的数据类型和数据库中的列类型一致

  1. 获取一个变量的数据类型

2. %rowtype

  1.获取一行记录
     declare 
        v_sal emp.sal%type;
        v_name emp.ename%type;
        v_emp emp%rowtype;
      begin
        select sal,ename into v_sal, v_name from emp where empno = 7788;
        dbms_output.put_line(v_sal||'   '|| v_name);
        select * into v_emp from emp where empno = 7788;
        dbms_output.put_line(v_emp.ename ||'   '||v_emp.sal ||'  '|| v_emp.job ); 
    end;

3. PL/SQL记录(自定义类型)

一个类型中可以包含多个变量
type record_name_type is record(

    field1_name data_type [not null][default| :=] default_value.
.........
    
)

record_name_type : 自定义记录(类型) 的名称
field1_name : 字段名称
data_type : 字段属性

标量是存储一行单列的值(标量),复合变量一行多列(pl/sql记录)

declare
  type emp_record_type is record(
   name emp.ename%type,
   salary emp.sal%type,
   dno emp.deptno%type
);
emp_record emp_record_type;
emp2_record emp_record_type;
begin
  select ename,sal,deptno into emp_record from emp where empno =7788;
  dbms_output.put_line(emp_record.name||'   '|| emp_record.salary||'       '||emp_record.dno);

select ename,sal, deptno into emp2_record from emp where empno = 7900;
dbms_output.put_line(emp2_record.name||'    '||emp2_record.salary||'    ' ||emp2_record.dno);

end;

标量是存储一行单列的值（标量）。复合变量一行多列（PL/SQL记录）

多行单列，PL/SQL集合

PL/SQL集合类型是类似与高级语言中的数据（集合），集合类型包括

1. 索引表

    1. 处理pl/sql中的数组数据类型
        1. 高级语言的数组长度是有限制的，并且下标不能为负数
        2. 索引表中的元素个数没有限制，并且下标可以为负数
              type type_name is table of element_type
              [not null]index by key_type
              identifier type_name
              type_name : 自定义数据类型的名称(is table..index 表示是一个索引表)
        element_type 索引表元素的数据类型
        not null : 表示不能引入null元素
        key_type : 索引表元素下标的数据类型(binary_integer,pls_integer或varchar2)
        
        整形(只能为整数)
        binary_integer : 2~31次方 ,如果超出存储上限,自动转换成number类型
        pls_integer  : 2~31次方 如果超出存储上限,抛出异常
        identifier : 自定义索引表变量名称;
    

  declare
    type dname_table_type is table of dept.dname%type --指定索引表的存储的数据类型
    index by binary_integer;--指定索引表下标的数据类型
    dname_table dname_table_type;
  i integer;
  begin

  select dname into dname_table(1) from dept where deptno = 10;
  select dname into dname_table(2) from dept where deptno = 20;
  select dname into dname_table(3) from dept where deptno = 30;
  select dname into dname_table(4) from dept where deptno = 40;

  dbms_output.put_line(dname_table(1));

  for i in 1..4 loop
    dbms_output.put_line(dname_table(i));
    end loop;
  end;

declare 
  i integer;
  type area_table_type is table of number
  index by varchar2(10);
  area_table area_table_type;
begin

area_table('新乡') :=6;
area_table('郑州') :=5;
area_table('许昌') :=3;
dbms_output.put_line('第一个元素'||area_table.first);
dbms_output.put_line('最后一个元素'||area_table.last);
dbms_output.put_line('下一个元素'||area_table.next('新乡'));
end;

2. 嵌套表

    1.  嵌套表也是PL/SQL数组的数据类型
    2. 高级语言数组元素下标从0或1 开始的， 并且元素个数没有限制
    3. 嵌套表下标必须从1 开始， 并且元素没有元素限制
    4. 高级语言中数组是有序的。嵌套表元素数组可以是无序的
    5. 索引表类型不能作为表中列的数据类型使用
    6. 嵌套表类型可以作为表中列的数据类型使用
      type type_name is table of element_type
    idetifer type_name;
    type_name : 用于指定嵌套表的类型名
    element_type ： 嵌套表元素的数据类型
    idetifer : 定义嵌套表类型变量
** 在使用嵌套表元素之前,必须首先使用其构造方法初始化嵌套表.

declare 
  type ename_table_type is table of emp.ename%type;
  ename_table ename_table_type;
  begin
    ename_table := ename_table_type('李四','王五','赵柳','sdf');
    select ename into ename_table(3) from emp where empno=7788;
    dbms_output.put_line(ename_table(2));
end;

create type phone_type is table of varchar2(50);
  create table employee(
   id number(4),
   name varchar2(30),
   phone phone_type

)
nested table phone store as phone_table;

insert into employee values(1001,'35476457',phone_type('first','second'));

insert into employee values(1002,'32563487',phone_type('first','second','three'));

select * from employee

3. 变长数组

    1. 其他同上
    2. 元素的最大个数是有限制的

type type_name is varray(size) of element_type [not null];

declare 
    type ename_arr_type is varray(10) of emp.ename%type;
    ename_array ename_arr_type;

    v_address address_type;
    begin
      /*
      ename_array := ename_arr_type('张三','李四','王五');
      dbms_output.put_line(ename_array(2));
      */
    select address into v_address from users where id = 1;
      for i in 1..v_address.count loop
      dbms_output.put_line(v_address(i));
      end loop;
    end;

  create type address_type is table of emp.ename%type;
  create table users (
     id number(10);
     grende varchar2(30);
    );
  insert into users values(1,'张三',);

declare
  type dept_count_record is record(
       dno emp.deptno%type,
       v_count number
    );
type dept_count_type is table of dept_count_record index by binary_integer;-- 定义索引集合
dept_table dept_count_type; --创建索引表实例
i integer; --定义标量
begin
  --查询第一行数据赋值给索引表集合第一个元素
select deptno, n
       into dept_table(1)
       from (select rownum r, c.* 
            from (select deptno, count(1) n from emp group by deptno) c) t
            where t.r = 1; 
 
select deptno , n 
       into dept_table(2)
       from (select rownum r, c.* 
       from (select deptno ,count(1) n from emp group by deptno) c) t
       where t.r = 2;
       
 select deptno , n
        into dept_table(3)
        from (select rownum r, c.* 
        from (select deptno, count(1)  n from emp group by deptno) c) t
        where t.r =3;
        
        for i in 1..dept_table.count loop
          dbms_output.put_line(dept_table(i).dno||'总人数： '|| dept_table(i).v_count);
          if dept_table(i).v_count >5 then
            dbms_output.put_line(dept_table(i).dno ||'是土豪。。。'|| dept_table(i).v_count);
          elsif dept_table(i).v_count >3 then
              dbms_output.put_line(dept_table(i).dno||'是小土豪。。。'||dept_table(i).v_count);
              else 
                dbms_output.put_line(dept_table(i).dno||'太穷了。。。'||dept_table(i).v_count);
          end if;
        end loop;
 end;

集合方法

            exists() : 检查集合中是否存在指定的下标
    count() : 获取集合中元素的个数
    limit() : 获取最大元素个数
    first()
    last()
    next()
    prior()
    ....    
    delete() : 删除

游标

PL/SQL 的游标是指把从数据库中查询出来的数据以临时表的形式存放内中，游标可以对存放在内存中的数据进行操作，返回一条或一组记录，或者一条也不返回
pl/sql 中的记录和表类型虽然可以存储数据

游标的基本操作:
pl/sql 包含隐士游标和显示游标

定义游标
cursor cursor_name is select....
打开游标
open cursor_name;
提取游标
fetch cursor_name into variable1,variable2.....
关闭游标
close cursor_name

cursor cursor_name is select...
cursor : 游标关键字
cursor_name : 游标名称
select.. : 建立游标所使用的查询语句


declare
--1.定义游标

    /*
    cursor c_dept is select * from dept;
    */
    cursor c_dept is select deptno,dname from dept;
    v_dname dept.dname% type;
    v_dno dept.deptno%type;
    v_dept dept%rowtype;
    begin
--2\. 打开游标
open c_dept;
--3\. 提取游标

    loop
    /*
    fetch c_dept into v_dept;
    dbms_output.put_line(v_dept.dname||' '||v_dept.loc);
    */
    fetch c_dept into v_dno,v_dname;
    dbms_output.put_line(v_dname||' '|| v_dno);
    exit when c_dept%notfound;
    end loop;
4\. 关闭游标

    close c_dept;
    end;

# 游标属性

游标作为一个临时表，可以通过游标的属性获取游标的状态
    1. %isopen 属性主要用于判断游标是否打开，在使用游标的时候如果不能确定是否已打开可以判断使用（为打开游标不可提取）
    2. %found  属性主要用于判断游标是否找到记录,如果找到记录用fetch语句提取游标数据
    3. %notfound 如果提取到数据返回false否则返回true和%found正好相反
    4. %rowcount  该属性用于返回到当前提取的实际行数

    declare 
       cursor emp_cursor(dno number) is select ename from emp where deptno = dno;
       v_name emp.ename%type;

    begin
    open emp_cursor(20);
    loop
    fetch emp_cursor into v_name;
    dbms_output.put_line(v_name);
    exit when emp_cursor%notfound;
    end loop ;
    close emp_cursor;

    dbms_output.put_line('-------------');
    open emp_cursor(10);
    loop
    fetch emp_cursor into v_name;
    dbms_output.put_line(v_name);
    exit when emp_cursor%notfound;
    end loop ;
    close emp_cursor;
    end;

## 参数化游标

定义游标后,使用后再传参
cursor cursor_name(paramter) is select....

-- Created on 2019-09-04 by LINNE
declare
cursor emp_cursor(dno number) is select ename from emp where deptno = dno;

v_ename emp.ename%type;

begin
-- Test statements here
open emp_cursor(20);
loop
fetch emp_cursor into v_ename;
dbms_output.put_line(v_ename);
exit when emp_cursor%notfound;
end loop;
close emp_cursor;

dbms_output.put_line('---------------------------------');

open emp_cursor(10);
loop
fetch emp_cursor into v_ename;
dbms_output.put_line(v_ename);
exit when emp_cursor%notfound;
end loop;
close emp_cursor;
end;


### 游标for循环

游标for循环是在pl/sql 块中使用游标最简单的方式，简化了游标的处理，Oracle会隐含的打开游标，提取游标，关闭游标
for record in cursor_name loop
.....
end loop;
declare

cursor c_dept(dno number) is select * from dept where deptno = dno;

begin

for v_dept in c_dept(20) loop
dbms_output.put_line('第'||c_dept%rowcount||'个员工'||v_dept.dname);
end loop;
end;


## 隐式游标

在执行一个SQL语句的时候,Oracle服务器将自动创建一个隐式游标.隐式游标的固定名称 SQL,隐式游标不需要声明和打开.使用完也不需要关闭.
隐式游标只能处理一行数据,所以into只能给一组变量赋值!
操作游标(游标的删除和修改)
存储过程
函数
包
触发器
序列## ##
同义词
视图
索引
备份和恢复
审计/数据库的数据加密/数据加载/数据传输/闪回..../日志恢复..

除了可以一行一行展示select结构外，还可以更新或删除，当前游标行数据，如果需要对游标中的行数据进行修改或删除，在定义游标的时候必须携带for update 字句
cursor cursor_name is select .....for update

declare
cursor emp_cursor is select ename, sal ,deptno from emp for update;
v_ename emp.ename%type;
v_sal emp.sal%type;
v_deptno emp.deptno%type;
begin
open emp_cursor;
loop
fetch emp_cursor into v_ename,v_sal,v_deptno;
exit when emp_cursor%notfound;
-- dbms_output.put_line(deptno||v_ename||v_sal);
update emp set deptno = deptno+ 10 where current of emp_cursor;
end loop;
close emp_cursor;
end;
--select * from emp

declare
type emp_type is ref cursor return emp%rowtype;
v_emp emp_type;
rec_emp emp%rowtype;

begin
open v_emp for select * from emp;
loop
fetch v_emp into rec_emp;
exit when v_emp%notfound;
dbms_output.put_line(rec_emp.ename);

end loop;
close v_emp;
dbms_output.put_line('-----------------------');

open v_emp for select * from emp order by deptno;
loop
fetch v_emp into rec_emp;
exit when v_emp%notfound;
dbms_output.put_line(rec_emp.ename);
end loop;
close v_emp;
end;

最后编辑于：2019.10.05 19:18:39

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 204,590评论 6赞 478
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 86,808评论 2赞 381
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 151,151评论 0赞 337
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,779评论 1赞 277
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,773评论 5赞 367
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,656评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 38,022评论 3赞 398
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,678评论 0赞 258
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 41,038评论 1赞 299
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,659评论 2赞 321
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,756评论 1赞 330
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,411评论 4赞 321
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 39,005评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,973评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,203评论 1赞 260
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 45,053评论 2赞 350
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,495评论 2赞 343

pl/sql （Oracle） 可编程SQL

目标

过程、函数。无名块， 他们之间可以互相嵌套