第四章：PlSql书写代码块
第一节：PL/SQL简介
**匿名块（在调用的时候用）：**是出现在应用程序中没有名称且不存储到数据库中的块。匿名块可以出现在SQL语句出现的地方，他们可以调用其他程序，却不能被其他程序调用。
**命名块（起名字，在不同时期进行调用）：**命名块是一种带有标签的匿名块，标签为块指定一个名称，其中命名块包含以下3程序：
- **子程序：**子程序是存储在数据库中的过程（procedure）函数（function）、生成之后可以被多次执行。
- **程序包：**程序包是存储在数据库中的一组子程序、变量定义。程序包中的子程序；
- 触发器：
第二节：结构简介
PL\SQL程序块由三部分组成：
-
定义部分：
用于定义常量、变量、游标、异常和复杂数据类型等；
-
执行部分：
用于实现应用模块功能，该部分包含需要执行的PL\SQL语句和SQL语句；
-
异常处理部分：
用于执行部分可能出现的运行错误。
PL\SQL程序基本语法：
declare -- 有变量声明的时候写；
-- 声明
-- 变量 变量名 数据类型； （直接赋值）变量名 数据类型:=值；
-- 常量 名 constant 数据类型: = ?;
-- 游标
-- 异常 名exception；
-- 一些复杂的数据类型 %rowtype %row table 可以没有声明
begin -- 必须有
-- 主要核心代码，sql处理
-- 1、给变量赋值 select 列1，列2...... into 变量1，变量2 from table where 条件
--2、insert update select delectable
-- 3判断、循环
exception
-- 付begin中的代码有异常的进行处理。
when 异常类型 then dbms_output.put_line(); -- 输出异常
-- 多个异常
when ......
when others......-- 也可以没有异常
end --必须有
-- 示例：用匿名块来表达一下接收用户输入的员工编号，输出改员工的姓名，以及处理用户输入的员工编号不存在的异常：
declare
v_empno number(5):=&e;--接收录入值：&固定；e随便起
v_ename varchar2(200);
begin
-- 具体代码，select into 给变量赋值
select ename into v_ename from emp where empno=v_empno;
-- 输出system.out.print || 字符串拼接；
dbms_output.put_line('姓名为：'|| v_ename);
exception
-- 异常处理 no_data_found 没有查到任何内容；输出异常信息
when no_data_found then dbms_output.put_line('查无此人');
end;
-- 圆周率 圆面积 π 半径 面积
declare
v_pai constant number(7,2):=3.14; -- constant 常量值;
v_r number(3,1):= &e;
v_area number(9,2);
begin
v_Area:=v_pai*v_r*v_r;
dbms_output.put_line(v_area);
end;
-- sql块中可以放那些语句
-- select into
-- update、insert、delete、commit、rllback；
-- drop table、alter table、create table、select；
-- declare 有变量声明的时候
begin
select * from emp;
end;
第三节：PL/SQL数据类型
1、PL\SQL数据类型等同于Oracle数据类型
2、%TYPE数据类型：当定义PL/SQL变量存放值时，必须确保变量使用合适的数据类型和长度，否则可能会运行错误；
只针对单列值；
3、%ROWTYPE：存储表的一行变量（行变量）放值直接 ：
select * into 变量名 from 表名 where 条件
取值：变量名.列名；
4、record自定义数据类型：只能在declare中用；
使用：
-- 声明数据类型，相当于创建了一个类
type 名字 is recode(
名字 表名.列名%type
);
-- 声明一个变量
别名 数据类型（上 名字）；
声明在外面可以多次使用：声明在外面
create type 名字 as object (
名字 数据类型（长度）
);
5、table数据类型（相当于数组） 行对象：
索引表，可以保存多个对象
-- 批量向数组中添加
select * into
-- 不会自动递增
declare
-- 声明数据类型
type dnamearray is table of varchar2(200);
-- 声明变量
dnames dnamearray: = dnamearray();
begin
dnames.extend;
select dname into dnames(1) from dept where deptno = 10;
dnames.extend;
select dname into dnames(2) from dept where deptno = 20;
dbms_output.put_line(dnames(1)||'------'||dnames(2))
end;
-- 自动递增
declare
-- 声明数据类型 自增 下标自增
type dnamearray is table of varchar2(200) index by binary_integer;
-- 声明变量
dnames dnamearray;
begin
dnames.extend;
select dname into dnames(1) from dept where deptno = 10;
select dname into dnames(2) from
dbms_output.put_line(dnames(1)||'------'||dnames(2))
end;
第四节：流程控制
1、条件控制：
-
条件控制——if
-- 写法一 if 条件表达式 then 语句段 end if; -- 写法二 if 条件表达式 then 语句段1 else 语句段2 end if; -- 写法三 if 条件表达式1 then 语句段1 elsif 条件表达式2 then 语句段2 elsif 条件表达式3 then ...... elsif 条件表达式n end if; -- 例子 /** 输入员工编号如果员工原来没有奖金则按照工资的10%发放原来有奖金，但是奖金金额不超过1000的补到1000其余的按照原来的奖金基础在加上10%发放 **/ declare -- 输入员工编号 v_enno emp.empno%type := &e; v_comm emp.comm%type; v_sal emp.sal%type; begin select comm,sal into v_comm,v_sal from emp where empno = v_enno; if v_comm is null then update emp set comm = v_sal*0.1 where empno = v_enno; elsif 0<v_comm and v_comm<1000 then update emp set comm = 1000 where empno = v_enno; elsif v_comm>1000 then update emp set comm = (comm+comm*0.1) where empno = v_enno; end if; exception when no_data_found then dbms_output.put_line('查无此人'); end;判断是否为空 列值 is null
-
条件控制——case
-- 书写方法1 case 表达式 when 条件表达式 结果1 then 语句段1； when 条件表达式 结果2 then 语句段2； ...... 语句段n； end case; -- ======================例子================================== -- 根据员工编号输出员工工资级别(1000以下 c；1000-2000B ；2000以上C); declare v_empno number(10):=&e; v_sal emp.sal%type; begin select sal into v_sal from emp where v_empno = empno; dbms_output.put_line(v_sal); case when v_sal<1000 then dbms_output.put_line('员工工资的等级为'||'--C'); when v_sal<2000 then dbms_output.put_line('员工工资的等级为'||'--B'); when v_sal>2000 then dbms_output.put_line('员工工资的等级为'||'--A'); end case; exception when no_data_found then dbms_output.put_line('查无此人'); end; -- =========================================================== -- 书写方法2 case(不写) when 表达式 then ...... -- =======================例子================================== -- case when 输入不同的数值，显示不同的地点； declare v_numbses number(2):=&e; begin case v_numbses when 20 then dbms_output.put_line('中国'); when 30 then dbms_output.put_line('河南'); when 40 then dbms_output.put_line('郑州'); end case; end;
2、循环控制
-
Loop end loop 用法
declare type insetab is table of dept%rowtype index by binary_integer; v_insetab insetab; v_i number(2) := 1; begin v_insetab(1).deptno := 5; v_insetab(1).dname := 'ee'; v_insetab(2).deptno := 6; v_insetab(2).dname := 'tt'; v_insetab(3).deptno := 7; v_insetab(3).dname := 'uu'; v_insetab(4).deptno := 9; v_insetab(4).dname := 'vv';loop 语句段 exit when 条件表达式; -- 或者 if(条件) then exit; end loop; -- =============================================== -- ==========示例============== -- 使用loop循环插入值 -- ========承接上面的代码块 loop -- exit when v_i>4; if v_i>4 then exit; end if; insert into dept(deptno,dname) values(v_insetab(v_i).deptno,v_insetab(v_i).dname); v_i := v_i+1; -
while loop end loop 用法
while 条件表达式 loop 语句段 end loop; -- =============================示例========================== -- while loop end loop 循环 while(v_i>4) loop insert into dept(deptno,dname) values(v_insetab(v_i).deptno,v_insetab(v_i).dname); v_i := v_i+1; -
for in loop end loop 循环用法
for 循环变量 in [reverse] 初值表达式 loop 语句段; end loop; -- ===============================示例========================= for v_i in 1..v_insetab.count loop insert into dept(deptno,dname) values(v_insetab(v_i).deptno,v_insetab(v_i).dname);-- 结尾代码 end loop; end;
第五节：异常处理
异常处理的语法
exception
when 异常错误名称1 [or 异常错误名称2] then
语句段1；
when 异常错误名称3 [or 异常错误名称4] then
语句段2；
......
when others then
语句段3；
自定义异常：
-- 自定义异常处理书写方法
declare
v_excep exception;
begin
delete emp where empno = 10;
if sql%notfound then
raise v_excep;
end if;
exception
when v_excep then
dbms_output.put_line('异常处理');
end;
第六节：事务管理
-
什么是事务：
用于保证数据的一致性，它由一组相关的DML语句组成，组成该语句的DML（数据操作语言 insert delete update） 语句要么全部成功，要么全部失败；
-
事务特性
- 原子性
- 一致性
- 隔离性
- 持久性
-
事务控制语句
- commit ：语句用于提交
- Rollback： 回滚事务
- save point ： 保存点
-
枷锁是实现数据库并发控制的一个非常重要的技术
- 排他锁：当数据对象被加上排他锁时，其他事务不能对他读取和修改
- 共享锁：加了共享锁的数据对象可以被其他事务读取，但不能修改；
- 因为有事务才有锁的概念；
-
Oracle数据库锁可以分为一下几大类：
- DML锁：数据锁，用于保护数据的完整性；
- DDL锁：数据字典锁，用于保护数据库对象的结构，如表、索引的结构定义
- SYSTEM锁：保护数据库的内部结构。
- 当一个用户对某表DML操作是，也会加DDL锁，这样在事务未结束前，可以防止另一个用户对该表做DDL操作；
-
我们探讨的是Oracle的DML 锁（由叫数据锁）他包括TM和TX两种；
- TM时面向对象的锁，他表示你锁定了系统中的一个对象，在锁定期间，不允许其他人对这个对象做DDL操作，TM锁首先产生，目的就是为了事实DDL保护
- TX时面向事务的锁，表示发起了一个事务，是否有事务产生，这是根据是否使用UNDO段作为评判标准的。
- 比如一个update语句 ，有表级锁；Oracle是先申请表级锁TM（其中RX锁）
扫一扫
7269
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
