Oracle锁、PL/SQL

最新推荐文章于 2022-07-22 20:42:34 发布

温故。

最新推荐文章于 2022-07-22 20:42:34 发布

阅读量230

点赞数

分类专栏：数据库文章标签：数据库 oracle plsql

本文链接：https://blog.csdn.net/qq_53449032/article/details/117932355

版权

数据库专栏收录该内容

6 篇文章 0 订阅

订阅专栏

文章目录

一、课程目标
二、Oracle锁
三、Oracle视图
四、Oracle索引
五、PL/SQL

一、课程目标

【理解】Oracle锁
【理解】Oracle PL/SQL语法
【理解】游标

二、Oracle锁

Oracle默认事务的级别为读已提交，如果存在其他事务在进行数据操作但是没有提交，那么数据我们不能进行操作。这使用的就是oracle锁的概念。Oracle中的锁主要用于解决并发问题，保证在并发操作的过程中的数据一致性和完整性的需求。

Oracle锁的分类

行级锁
表级锁

2.1 行级锁

在一个数据对行数据进行操作时，防止其他事务对相同的行数据进行操作，行级锁是一种排他锁（除当前事务外不能解锁）

在使用以下语句时，Oracle会自动应用行级锁：

INSERT语句
UPDATE语句
DELETE语句
SELECT … FOR UPDATE [wait second] [no wait]]
SELECT … FOR UPDATE语句允许用户一次锁定多条记录进行更新

当事务执行成功提交，或执行失败回滚后才会释放对应的锁

--用户A:
select * from emp where empno=7934 for update  
--用户B:
update scott.emp set sal=sal+10  where empno=7934 --不能更改
update scott.emp set sal=sal+10  where empno=7902 --可以更改

只有用户A提交事务或者回滚事务后用户B对应操作才能执行（对同一条数据操作的情况下）

在这里插入图片描述

2.2 表级锁

表级锁:锁定整个表，限制其他用户对表的访问。

限制的是对数据的操作

LOCK TABLE table_name IN mode MODE

--用户A:
lock table emp in share mode;
--用户B:
select * from scott.emp --可以
update scott.emp set sal=sal+10  where empno=7902 --不可以

在这里插入图片描述

2.3 死锁

死锁:当两个事务相互等待对方释放资源时，就会形成死锁。

Oracle会自动检测死锁，并通过结束其中的一个事务来解决死锁。

虽然可以锁定表限制其他连接的修改操作，但是不同事务可以锁定同一张表，导致死锁。

--用户A:
lock table emp in share mode;
update emp set sal=sal+10  where empno=7902
--用户B:
lock table emp in share mode;
update scott.emp set sal=sal+10  where empno=7902

在这里插入图片描述

三、Oracle视图

视图以经过定制的方式显示来自一个或多个表的数据，视图可以视为“虚拟表”或“存储的查询”，创建视图所依据的表称为“基表”。

视图的优点

提供了另外一种级别的表安全性
隐藏的数据的复杂性
简化的用户的SQL命令
隔离基表结构的改变
通过重命名列，从另一个角度提供数据

创建语法:
create [or replace] view 视图名称
as select 语句

删除语法:
drop view 视图名称;

注意：需要create view 权限

四、Oracle索引

索引类似于字典中的索引目录，为表添加合适的索引可以提高检索效率。

索引优点:

用以提高 SQL 语句执行的性能
减少磁盘I/O访问次数

添加索引原则:

只有在大表上创建索引才有意义
在where子句中的条件(列)经常建立索引
主键会自动添加唯一索引。
建立索引会带来一些额外的开销:
写的速度会变慢:需要额外维护索引信息
加大磁盘和内存空间的占用量(1.2倍)

创建索引语法:
CREATE INDEX 索引名称 ON 表名 (列名)
TABLESPACE 表空间

--语法: create index 索引名 on 表名(列名) 
create index myIndex on emp(ename);
select * from emp where ename='test'
--组合索引:create index 索引名 on 表名(列名1,列名2...) 
create index myIndex2 on emp(ename,sal)
select * from emp where ename='test'and sal=1300

删除索引语法: 
drop index 索引名称;

创建唯一索引语法:
CREATE UNIQUE INDEX 索引名 ON 表名 (列名);
TABLESPACE 表空间

五、PL/SQL

PL/SQL也是一种程序语言，叫做过程化SQL语言（Procedural Language/SQL）。PL/SQL是Oracle数据库对SQL语句的扩展。在普通SQL语句的使用上增加了编程语言的特点，所以PL/SQL就是把数据操作和查询语句组织在PL/SQL代码的过程性单元中，通过逻辑判断、循环等操作实现复杂的功能或者计算的程序语言。

PL/SQL是Oracle在标准SQL语言上的过程性扩展
允许嵌入SQL语句，定义变量和常量，
允许过程语言结构（条件分支语句和循环语句），
允许使用异常来处理Oracle错误
可以用于创建存储过程、触发器和程序包等，
也可以用于处理业务规则、数据库事件或给SQL命令的执行添加程序逻辑

所有的PL/SQL程序都以块作为基本单位，块中包含过程化语句和SQL的DML语句。这些块可以按顺序出现，也可以相互嵌套（一个块在另一个块的内部）。

匿名块
匿名块是出现在应用程序中的没有名字且不存储到数据库中的块
匿名块出现在SQL语句可以出现的地方，它们可以调用其他程序，却不能被其他程序调用命名块
命名块是一种带有标签的匿名块，标签为块指定了一个名称
子程序
子程序是存储在数据库中的过程（procedure）、函数（function），生成之后可以被多次执行
程序包
程序包是存储在数据库中的一组子程序、变量定义，程序包中的子程序可以被其他程序包或者子程序调用
触发器
触发器是一种存储在数据库中的命名块，生成之后可以被多次执行
在相应的触发器事件发生之前或之后就会被执行一次或多次

5.1 程序块的基本结构语法

组成部分
定义部分 ：用于定义常量、变量、游标、异常和复杂数据类型
执行部分：用于实现应用模块功能，该部分包含需要执行的PL/SQL语句和SQL语句
异常处理部分：用于处理执行部分可能出现的运行错误

DECLARE
定义部分
BEGIN
执行部分
EXCEPTION
异常处理部分
END;

注意：DECLAREE、BEGIN和EXCEPTION后面没有“；”（分号），而END后则必须要带“；”（分号）。

5.2 变量与常量

在声明部分中可以声明需要使用的常量、变量、函数、游标、异常处理名称。

声明变量语法：变量名类型:=值;

声明常量语法：变量名 constant 类型:=值;

为变量赋值语法：变量名:=值;
在sqlplus中首先设置，否则执行没有输出结果
set serveroutput on;

--声明变量(建议变量名以v_开头)
--变量名 类型:=值;
--赋值 变量名:=值;
declare 
   v_hello varchar2(20):='hello';
   v_world varchar2(20);
   v_cons constant number(4):=123;--声明常量:不能重新赋值
begin
  v_world:=',oralce!';
  --v_cons:=345;
  dbms_output.put_line(v_hello||v_world);--类似于System.out.println();
  dbms_output.put_line('v_cons='||v_cons); --||代表将结果拼接在一起
end;

--使用select ..into语句为变量赋值
declare
    v_name varchar2(20);
    v_sal number(10);
    v_empno number(10);
begin
   v_empno:='&empno';--获取控制台输入的编号:&(交互式命令)
   select ename,sal into v_name,v_sal from emp where empno=v_empno;
   dbms_output.put_line('name:'||v_name);
   dbms_output.put_line('sql:'||v_sal);
end;

--%type:引用变量和数据库列的数据类型
--%rowtype:提供表示表中一行的记录类型
declare
    v_name emp.ename%type;-- 与emp表中ename列的类型一致
    v_sal emp.sal%type;
    v_empno emp.empno%type;
    v_record emp%rowtype;--保持一条记录
begin
   v_empno:='&empno';--获取控制台输入的编号:&(交互式命令)
   select ename,sal into v_name,v_sal from emp where empno=v_empno;
   dbms_output.put_line('name:'||v_name);
   dbms_output.put_line('sql:'||v_sal);
   dbms_output.put_line('**************************');
   select * into v_record from emp where empno=v_empno;--将查询的记录赋值给v_record
   dbms_output.put_line('work:'||v_record.job);--可以通过.列名获取一条数据中指定列的数据
end;

5.3 流程控制

5.3.1 分支（条件）结构

if…end if 结构

--条件结构
--if.... end if;
--语法  if 条件 then 语句 end if;
declare
   v_age number(3);
begin
   v_age:='&age';
   if v_age>=18 then
      dbms_output.put_line('ok');
   end if;  
   if v_age<18 then
      dbms_output.put_line('no ok');
   end if;  
end;

if…else… end if 结构

--if....else....end if;
declare 
  v_age number(3);
begin
  v_age:='&age';
  if v_age >= 18 then
     dbms_output.put_line('yes!');
  else 
     dbms_output.put_line('no!');
  end if;
end;

if…elsif…else…end if结构

---if...elsif...else....end if
declare 
  v_age number(3);
begin
  v_age:='&age';
  if v_age >= 50 then
     dbms_output.put_line('老年!');
  elsif v_age>=30 then
     dbms_output.put_line('状年!');
  elsif v_age>=18 then
     dbms_output.put_line('青年!');  
  else
     dbms_output.put_line('未成年!');
  end if;
end;

case…end case结构

--case子句:类似于switch
declare
   v_score varchar2(2);
begin
   v_score:='&score';
   case v_score   --类似switch语句中的switch
     when 'A' then  --类似于switch语句中case
        dbms_output.put_line('优秀!');
     when 'B' then
        dbms_output.put_line('良好!');
     when 'C' then
        dbms_output.put_line('一般!');
     when 'D' then
        dbms_output.put_line('不及格!');
     else  --类似于swtich中的default
        dbms_output.put_line('输入不正确!'); 
   end case;  
end;

5.3.2 循环结构

loop循环结构: 类似于do…while循环

--loop循环结构: do...while循环
--loop
--循环体;
-- exit when 条件
--end loop;
declare
  i number(10,0):=1;
begin
  loop
     dbms_output.put_line('i='||i);
     i:=i+1;
     exit when i>10; --满足条件退出循环
  end loop;
end;

while循环结构: 类似于while循环

--while循环结构:类似于while循环
--while 条件 
--loop
--循环体
--end loop;
declare
  i number(10,0):=1;
begin
  while i<=10
  loop
     dbms_output.put_line('i='||i);
     i:=i+1;
  end loop;
end;

for循环结构: 类似于for循环

--for 循环变量 in [REVERSE] 初值表达式..终值表达式 
--loop
--	循环体;
--end loop;
declare
  v_i number(10,0);
begin
  for v_i in [reverse 倒序] 1..100
  loop
      dbms_output.put_line('i='||v_i);
  end loop;
end;

5.4 游标

当在PL/SQL块中执行查询语句（SELECT）和数据操纵语句（DML）时，Oracle会为其分配一个上下文区（Context Area），游标是指向上下文区的指针，它为应用提供了一种对具有多行数据查询结果集中的每一行数据分别进行单独处理的方法

本质就是保存多条数据的结果集，使用循环遍历操作进行数据的相应操作

使用显式游标的步骤

定义游标：CURSOR cursor_name IS select_statement;

打开游标：OPEN cursor_name;

提取数据：FETCH cursor_name INTO variable1,variable2,…;

关闭游标：CLOSE cursor_name;

/*
显示游标
显式游标在 PL/SQL 块的声明部分定义查询，该查询可以返回多行
*/
--使用loop循环遍历游标
declare
   v_emp_record emp%rowtype; --声明变量:保持员工表中一行的记录信息
   cursor emp_cur is select * from emp;--定义游标:该游标保持了所有的员工信息
begin
   open emp_cur; --打开游标
   loop
     fetch emp_cur into v_emp_record; --从游标中提取数据
     exit when emp_cur%NOTFOUND; --判断游标中是否没有数据
      dbms_output.put_line('编号:'||v_emp_record.empno||'姓名:'||v_emp_record.ename);
   end loop;  
   close emp_cur;
end;

--带参数的游标
declare
    v_sal emp.sal%type;
    v_emp_record emp%rowtype;
    cursor emp_cur(salPara number) is select * from emp where sal < salPara; --定义带参数的游标
begin
    for v_emp_record in emp_cur('&请输入薪酬')
    loop
         dbms_output.put_line('姓名:'||v_emp_record.ename||'薪酬:'||v_emp_record.sal);   
    end loop;
end;

温故。

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle锁、PL/SQL

文章目录一、课程目标二、Oracle锁2.1 行级锁2.2 表级锁2.3 死锁三、Oracle视图四、Oracle索引五、PL/SQL5.1 程序块的基本结构语法5.2 变量与常量5.3 流程控制5.3.1 分支（条件）结构5.3.2 循环结构5.4 游标一、课程目标【理解】Oracle锁【理解】Oracle PL/SQL语法【理解】游标二、Oracle锁Oracle默认事务的级别为读已提交，如果存在其他事务在进行数据操作但是没有提交，那么数据我们不能进行操作。这使用的就是oracle锁的概念
复制链接

扫一扫

专栏目录