pl/sql|pl/sql （Oracle）可编程SQL pl

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; beginselect 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('这是第一行输出'); <> 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!= n2then 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; beginselect 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; beginarea_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;
【pl/sql|pl/sql （Oracle）可编程SQL】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;