20201204黄春跃

20201204黄春跃

知识点

视图

视图就是封装了一条复杂查询的语句,视图提供了一个查询的窗口,所有的数据来自于原表。
视图是从一个或多个表导出的虚拟的表,其内容由查询定义。具有普通表的结构,但是不实现数据存
储。

创建视图需要有 dba 角色或者 create view 权限才可以

1.先退出登录,切换sys账户并选择sysdba登录

2.登录后找到users

3.找到要赋予权限的用户 hcy 赋予view权限。

创建视图的语法:

create view 视图名称 as 子查询
如:
create view empd20 as select * from emp where deptno = 20
建立完视图后,查询出视图中的数据
select * from empd20

如果视图已经存在我们可以使用此语法来创建视图,这样已有的视图会被覆盖。

create or replace view 视图名称 as 子查询
如
create or replace view empd20 select * from emp where deptno = 10

再次查询后

修改视图:

update empd20 set sal = 900.00 where empno = 7782; 

我们在修改视图中的数据时,原表中的数据也发生了改变。所以我们一般不推荐去修改视图中的数据。
这时我们可以设置视图为只读。

创建只读视图

create (or replace) view 视图名称 as 子查询 with read only
如
create view empd30 as select * from where deptno = 30 with read only

同修创建视图一样

当我要修改视图时,会出现如下Error

视图的作用:

  1. 安全性,视图可以屏蔽掉一些敏感字段,用户只能查询和修改能看到的数据
  2. 简化了操作,把经常使用的数据定义为视图。

索引

索引是用于加速数据读取的数据对象。合理的使用索引可以大大降低 i/o 次数,从而提高数据访问性能。
索引就是在表的列上构建一个二叉树达到提高查询速度的目的,但是会影响增删改的效率。
为什么添加了索引之后,会加快查询速度呢?
图书馆:如果杂乱地放书的话检索起来就非常困难,所以将书分类,然后再建一个箱子,箱
子里面放卡片,卡片里面可以按类查询,按书名查或者类别查,这样的话速度会快很多很多,
这个就有点像索引。索引的好处就是提高你找到书的速度,但是正是因为你建了索引,就应该有人专门
来维护索引,维护索引是要有时间精力的开销的,也就是说索引是不能乱建的,所以建索引有个原则:
如果有一个字段如果不经常查询,就不要去建索引。现在把书变成我们的表,把卡片变成我们的索引,
就知道为什么索引会快,为什么会有开销。
索引有一下几种:
单列索引:单列索引是基于单个列所建立的索引
复合索引:复合索引是基于两个列或多个列的索引。
在同一张表上可以有多个索引,但是要求列的组合必须不同

PL/SQL

在之前的数据库学习中,我们一直都使用单一的SQL语句进行数据操作,没有流程控制,无法开发
复杂的应用。Oracle PL/SQL语言(Procedural Language/SQL)是结合了结构化查询与Oracle自身过
程控制为一体的强大语言,PL/SQL不但支持更多的数据类型,拥有自身的变量声明、赋值语句,而且
还有条件、循环等流程控制语句。过程控制结构与SQL数据处理能力无缝的结合形成了强大的编程语
言,可以创建过程和函数以及程序包。
PL/SQL是一种块结构的语言,它将一组语句放在一个块中,一次性发送给服务器,引擎分析收到
语句块中的内容进行,把其中的过程控制语句由引擎自身去执行,把块中的语句交给服务器的语句执行
器执行。PL/SQL块发送给服务器后,先被编译然后执行,对于有名称的PL/SQL块(比如下一章讲解的子程
序)可以单独编译,永久的存储在数据库中,随时准备执行。PL/SQL的优点还有:

  1. 支持SQL
    SQL是访问数据库的标准语言,通过SQL命令,用户可以操纵数据库中的数据。PL/SQL支持所有
    的SQL数据操纵命令、游标控制命令、事务控制命令、SQL函数、运算符和伪列。同时PL/SQL和
    SQL语言紧密集成,PL/SQL支持所有的SQL数据类型和NULL值。
  2. 支持面向对象编程
    PL/SQL支持面向对象的编程,在PL/SQL中可以创建类型,可以对类型进行继承,可以再子程序中
    重载方法等。
  3. 更好的性能
    SQL是非过程语言,只能一条一条执行,而PL/SQL把一个PL/SQL块统一进行编译后执行,同时还
    可以把编译好的PL/SQL块存储起来,以备重用,减少了应用程序和服务器之间的通信时间,
    PL/SQL是快速而高效的。
  4. 可移植性
    使用PL/SQL编写的应用程序,可以移植到任何操作系统平台上的Oracle服务器,同时还可以编写
    可移植程序库,在不同环境中重用。
  5. 安全性
    可以通过存储过程对客户机和服务器之间的应用程序逻辑进行分隔,这样可以限制对Oracle数据
    库的访问,数据库还可以授权和撤销其他用户访问的能力。

PL/SQL块

PL/SQL是一种块结构的语言,一个PL/SQL程序包含了一个或者多个逻辑块,逻辑块中可以声明变
量,变量在使用之前必须先声明。除了正常的执行程序外,PL/SQL还提供了专门的异常处理部分进行
异常处理。每个逻辑块分为三个部分,语法是:

[DECLARE
  -- declaration statements]
BEGIN
  -- executable statements
[EXCEPTION
  -- exception statements]
END;
  1. 声明部分:声明部分包含了变量和常量的定义。这个部分由关键字DECLARE开始,如果不声明变
    量或者常量,可以省略这部分。
  2. 执行部分:执行部分是 PL/SQL块的指令部分,由关键字BEGIN开始,关键字END结尾。所有的可
    执行PL/SQL语句都放在这一部分,该部分执行命令并操作变量。其他的PL/SQL块可以作为子块嵌
    套在该部分。PL/SQL块的执行部分是必选的。注意END关键字后面用分号结尾。
  3. 异常处理部分:该部分是可选的,该部分用EXCEPTION关键字把可执行部分分成两个小部分,之
    前的程序是正常运行的程序,一旦出现异常就跳转到异常部分执行。

PL/SQL是一种面向过程编程语言,与Java和C#一样,除了有自身独有的数据类型、变量声明和赋值以
及流程控制语句外,PL/SQL还有自身的语言特性:
PL/SQL对大小写不敏感,为了良好的程序风格,开发团队都会选择一个合适的编码标准。比如有
的团队规定:关键字全部大写,其余的部分小写。
PL/SQL块中的每一条语句都必须以分号结束,语句可以是多行的,但分号表示该语句结束。一行
中可以有多条语句,他们之间以分号分隔,但是不推荐一行中写多条语句。

PL/SQL中的特殊符号说明:

变量声明

PL/SQL支持SQL中的数据类型,PL/SQL中正常支持NUMBER,VARCHAR2,DATE等Oracle SQL数据
类型。声明变量必须指明变量的数据类型,也可以声明变量时对变量初始化,变量声明必须在声明部
分。声明变量的语法是:

变量名 数据类型[ :=初始值]; 
例如:
declare
i number(2) := 66;
begin
dbms_output.put_line(i);
end;
-- 1. 声明一个变量 i ,初始化值是 66 。
-- 2. dbms_output.put_line是输出语句,可以把一个变量的值输出,在SQLPlus中输出数据时,可能没   有结果显示,可以使用命令: set serveroutput on 设置输出到SQLPlus控制台上。

对变量赋值还可以使用SELECT…INTO语句从数据库中查询数据对变量进行赋值。但是查询的结果只能
是一行记录,不能是零行或者多行记录。

declare
-- 允许10位NUMBER类型数据,允许有8位整数,小数点后2位小数
i number(10,2);
begin
 select sal into i from emp where empno = 7499;
dbms_output.put_line(i);
end;

使用select…into语句对变量赋值,要求查询的结果必须是一行,不能是多行或者没有记录。

声明常量

常量在声明时赋予初值,并且在运行时不允许重新赋值。使用CONSTANT关键字声明常量。

declare
pi constant number(3,2) := 3.14;
r number(1) := 5;
area number;
begin
  area := power(r,2) * pi;
  dbms_output.put_line('圆的面积是' || area);
 end;

PL/SQL 数据类型

前面在建表时,学习过Oracle SQL的数据类型,PL/SQL不但支持这些数据类型,还具备自身的数据类
型。PL/SQL的数据类型包括标量数据类型,引用数据类型和存储文本、图像、视频、声音等非结构化
的大数据类型(LOB数据类型)等。下面列举一些常用的类型。

标量数据类型

标量数据类型的变量只有一个值,且内部没有分量。标量数据类型包括数字型,字符型,日期型和
布尔型。这些类型有的是Oracle SQL中定义的数据类型,有的是PL/SQL自身附加的数据类型。字符型
和数字型又有子类型,子类型只与限定的范围有关,比如NUMBER类型可以表示整数,也可以表示小
数,而其子类型POSITIVE只表示正整数

属性数据类型

当声明一个变量的值是数据库中的一行或者是数据库中某列时,可以直接使用属性类型来声明。Oracle
中存在两种属性类型:%TYPE和%ROWTYPE。

% ROWTYPE

引用数据库表中的一行作为数据类型,即RECORD类型(记录类型),是PL/SQL附加的数据类
型。表示一条记录,就相当于Java中的一个对象。可以使用“.”来访问记录中的属性。

declare
emp_scott emp%rowtype;
begin
select * into emp_scott from emp where empno = 7788;
dbms_output.put_line('emp_scott.ename = ' || emp_scott.dept);
end;

%TYPE

引用某个变量或者数据库的列的类型作为某变量的数据类型。

declare
sal emp.sal %type;
mysal number(4) := 3000;
totolsal mysal%type;
begin
  select sal into sal from emp where empno = 7788;
  totolsal := sal + mysal;
  dbms_output.put_line(totolsal);
  end;

PL/SQL条件控制和循环控制

PL/SQL程序可通过条件或循环结构来控制命令执行的流程。PL/SQL提供了丰富的流程控制语句,与
Java一样也有三种控制结构:
顺序结构
条件结构
循环结构

条件控制

Java中的条件控制使用关键字if和switch。PL/SQL中关于条件控制的关键字有IF-THEN、IF-THEN-
ELSE、IF-THEN-ELSIF和多分枝条件CASE

IF-THEN

该结构先判断一个条件是否为TRUE,条件成立则执行对应的语句块,与Java中的if语句很相似,具体语
法是:

-- PL/SQL中IF语法
if 条件 then
-- 条件结构体
end if;

说明:
① 用IF关键字开始,END IF关键字结束,注意END IF后面有一个分号。
② 条件部分可以不使用括号,但是必须以关键字THEN来标识条件结束,如果条件成立,则执行THEN
后到对应END IF之间的语句块内容。如果条件不成立,则不执行条件语句块的内容。
③ Java结构用一对大括号来包含条件结构体的内容。PL/SQL中关键字THEN到END IF之间的内容是条
件结构体内容。
④ 条件可以使用关系运算符合逻辑运算符。

示例:查询 JONES 的工资,如果大于900元,则发奖金800元。

declare
newSal emp.sal%type;
begin
select sal into newSal from emp where ename = 'JONES';
  if newSal > 900 then
  update emp set comm = 800 where ename = 'JONES';
  end if;
  commit;
end;

  1. 先判断条件,如果条件为TRUE,则执行条件结构体内部的内容。
  2. 在PL/SQL块中可以使用事务控制语句,该COMMIT同时也能把PL/SQL块外没有提交的数据一并提交,使用时需要注意。
IF-THEN-ELSE

示例:查询JAMES的工资,如果大于1000元,则发奖金800元,否则发奖金400元。

declare
newSal emp.sal%type;
begin
  select sal into newSal from emp where ename = 'JAMES';
  if newSal > 1000 then
    update emp set comm = 800 where ename = 'JAMES';
    else
      update emp set comm = 400 where ename = 'JAMES';
      end if;
      commit;
end;

IF-THEN-ELSIF

示例:查询ADAMS的工资,如果大于1500元,则发放奖金1000元,如果工作大于900元,则发奖金800元,否则发奖金400元。

declare
newSal emp.sal%type;
begin
  select sal into newSal from emp where ename = 'ADAMS';
  if newSal > 1500 then
    update emp set comm = 1000 where ename = 'ADAMS';
    elsif newSal > 900 then
      update emp set comm = 800 where ename = 'ADAMS';
      else
        update emp set comm = 400 where ename = 'ADAMS';
       end if;
       commit;
end;

CASE

CASE是一种选择结构的控制语句,可以根据条件从多个执行分支中选择相应的执行动作。也可以作为
表达式使用,返回一个值。类似于Java中的switch语句。语法是:

CASE [selector]
  WHEN 表达式1 THEN 语句序列1;
  WHEN 表达式2 THEN 语句序列2;
  WHEN 表达式3 THEN 语句序列3;
 ……
 [ELSE 语句序列N];
END CASE;

示例:输入一个字母A、B、C分别输出对应的级别信息

declare
v_grade char(1) := upper('&v_grade');
begin
  case v_grade
    when 'A' then
      dbms_output.put_line('Excellent');
    when 'B' then
      dbms_output.put_line('Very Good');
    when 'C' then
      dbms_output.put_line('Good');
    else
      dbms_output.put_line('No such grade');
   end case;
end;

  1. &V_grade表示在运行时由键盘输入字符串到grade变量中。
  2. v_grade分别于WHEN后面的值匹配,如果成功就执行WHEN后的程序序列。
    CASE语句还可以作为表达式使用,返回一个值。比如:上面的案例中
DECLARE
  v_grade CHAR(1):=UPPER('&grade');
  p_grade VARCHAR(20) ;
BEGIN
  p_grade :=
  CASE v_grade
       WHEN 'A' THEN
            'Excellent'
            WHEN 'B' THEN
            'Very Good'
            WHEN 'C' THEN
            'Good'
            ELSE
            'No such grade'
            END;
            dbms_output.put_line('Grade:' ||v_grade||',the result is '||p_grade);
END;
  1. CASE语句可以返回一个结果给变量p_grade
    PL/SQL还提供了搜索CASE语句。也就是说,不使用CASE中的选择器,直接在WHEN后面判断条件,第
    一个条件为真时,执行对应THEN后面的语句序列。
循环结构

PL/SQL提供了丰富的循环结构来重复执行一些列语句。Oracle提供的循环类型有:
无条件循环LOOP-END LOOP语句
WHILE循环语句
FOR循环语句
在上面的三类循环中EXIT用来强制结束循环,相当于Java循环中的break。

LOOP循环

LOOP循环是最简单的循环,也称为无限循环,LOOP和END LOOP是关键字,语法是:

LOOP
  --循环体
END LOOP;
  1. 循环体在LOOP和END LOOP之间,在每个LOOP循环体中,首先执行循环体中的语句序列,执行
    完后再重新开始执行。
  2. 在LOOP循环中可以使用EXIT或者[EXIT WHEN 条件]的形式终止循环。否则该循环就是死循环。
DECLARE
counter number(3):=0;
sumResult number:=0;
BEGIN
LOOP
counter := counter+1;
sumResult := sumResult+counter;
IF counter>=100 THEN
EXIT;
END IF;
-- EXIT WHEN counter>=100;
END LOOP;
dbms_output.put_line('result is :'||to_char(sumResult));
END;
WHILE循环

与Java中的while循环很类似。先判断条件,条件成立再执行循环体。

WHILE 条件 LOOP
  --循环体
END LOOP;
DECLARE
  counter number(3):=0;
  sumResult number:=0;
BEGIN
   WHILE counter < 100 LOOP
    counter := counter+1;
    sumResult := sumResult+counter;
   END LOOP;
   dbms_output.put_line('result is :'||sumResult);
END;

标签

评论

© 2021 成都云创动力科技有限公司 蜀ICP备20006351号-1