【湘大oracle数据库】期末复习笔记

可爱圈

已于 2022-11-23 10:27:16 修改

阅读量441

点赞数

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

于 2022-11-21 20:31:24 首次发布

本文链接：https://blog.csdn.net/pangpangjinhhh/article/details/127963259

版权

数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

数据库复习

实验一

考点一索引

考点二序列

考点三用户管理

实验二

PL/SQL 是什么

Oracle PL/SQL语言（Procedural Language/SQL）是结合了结构化查询语言SQL与Oracle自身过程控制为一体的强大语言，PL/SQL不但支持更多的数据类型，拥有自身的变量声明、赋值语句，而且还有条件、循环等流程控制语句。

过程控结构与SQL数据处理能力无缝地结合形成了强大的编程语言，可以创建过程和函数及程序包。

例一

创建一个简单的PL/SQL块，计算一个长为5，宽为4的矩形面积。创建完成后将该块在SQL*Plus中运行，其运行结果如图

    SQL>  DECLARE
      2  LEN NUMBER:=5;
      3  WIDTH NUMBER:=4;
      4  AREA NUMBER;
      5  BEGIN                         ------执行部分开始
      6  AREA:=LEN*WIDTH;
      7  DBMS_OUTPUT.PUT_LINE(AREA);   ------输出AREA的值到SQL*Plus
      8  END;
      9  /

在这里插入图片描述

简单地说，PL/SQL是一种块结构的语言，它将一组语句放在一个块中，一次性发送给服务器，PL/SQL引擎分析收到PL/SQL语句块中的内容，把其中的过程控制语句由PL/SQL引擎自身去执行，把PL/SQL块中的SQL语句交给服务器的SQL语句执行器执行。一个PL/SQL程序包含了一个或者多个逻辑块，逻辑块中可以声明变量，变量在使用之前必须先声明。除了正常的执行程序外，PL/SQL还提供了专门的异常处理部分进行异常处理。每个逻辑块分为三个部分，语法是：

  [DECLARE
        --declaration statements]
    BEGIN
        --executable statements
    [EXCEPTION
        --exception statements]
    END;

其中，DECLARE部分为声明部分，它包含了变量和常量的定义，该部分由关键字DECLARE开始，如果不声明变量或者常量，可以省略这部分；由关键字BEGIN开始，关键字END结尾的部分为执行部分，所有的可执行PL/SQL语句都放在这一部分，该部分执行命令并操作变量；由EXCEPTION关键字开始的部分为异常处理部分，它也是可选的。

注意：在SQL*Plus中运行PL/SQL块前，如果需要将执行结果直接输出，首先需要执行“set serveroutput on”命令，再用dbms_output.put_line()语句输出变量值。

例二

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

本实例从数据表STUDENT中取出学号为“120001”的学生，将其赋值到变量name中并输出

    SQL> DECLARE
      2  name VARCHAR2(50) DEFAULT 'Null';
      3  BEGIN
      4  SELECT sname INTO name FROM STUDENT WHERE sno='120001';
      5  DBMS_OUTPUT.PUT_LINE (name);
      6  END;
      7  /

例三

本实例中声明常量PI，并为其赋初始值为3.14，同时声明两个变量area和radius，用于存储圆面积和半径值

    SQL> DECLARE
      2  PI CONSTANT NUMBER:=3.14;
      3  RADIUS NUMBER DEFAULT 5;
      4  AREA NUMBER;
      5  BEGIN
      6  AREA:=PI*RADIUS*RADIUS;        --------计算圆面积
      7  DBMS_OUTPUT.PUT_LINE(AREA);   --------输出显示
      8  END;
      9  /

在这里插入图片描述

例四

除此之外，PL/SQL还提供了两个属性数据类型。当声明一个变量的值是数据库中的一行或者是数据库中某列时，可以直接使用属性类型来声明。其中，属性数据类型% ROWTYPE表示引用数据库中的一行（一条记录），因此也将其称为记录类型。

除%ROWTYPE外，Oracle PL/SQL还支持一个属性数据类型，即%TYPE类型，该类型引用数据库的列类型作为某变量的数据类型。本实例声明一个%TYPE类型变量，并通过该变量引用并输出数据表中的字段值。

本实例从数据表STUDENT中获取学号为“120003”的学生信息，将其年龄加上1 后在SQL*Plus控制端中输出

    SQL> DECLARE
      2  age STUDENT.sage%TYPE;
      3  newage number;
      4  BEGIN
      5  SELECT sage INTO age FROM STUDENT WHERE sno='120003';
      6  newage:=age+1;
      7  DBMS_OUTPUT.PUT_LINE (newage);
      8  END;
      9  /

本实例使用了%TYPE数据类型声明变量age为数据表STUDENT中sage列（字段）的变量，再通过SELECT查询语句从数据表STUDENT获取一个列值，并将其写入到%TYPE型变量age中。在上面代码的第6行中引用该变量age进行一个简单的算术运算，最后将运算结果输出。

PL/SQL控制语句和存储过程

与SQL Server采用T-SQL语言类似的，Oracle也拥有自己的控制语言，被称为PL/SQL。任何计算机语言（C、JAVA，PASCAL等）都有各种控制语句（条件语句，循环结构，顺序控制结构），在PL/SQL中也存在这样的控制结构。在PL/SQL中允许用户使用控制语句来实现流程控制。

if

单分支

例：Oracle PL/SQL提供了条件比较语句IF…THEN来实现该功能。本实例定义两个整型变量并比较其大小，如果符合第一个变量小于第二个变量条件，则输出字符串“NUMBER1小于NUMBER2”

    SQL> DECLARE
      2  NUMBER1 INTEGER:=10;
      3  NUMBER2 INTEGER:=20;
      4  BEGIN
      5  IF NUMBER1<NUMBER2 THEN
      6  DBMS_OUTPUT.PUT_LINE('NUMBER1小于NUMBER2');
      7  END IF;
      8  END;
      9  /

本实例通过IF…THEN…END IF语句实现了单条件的两个变量比较并输出，在Oracle PL/SQL中，该形式的实现语句格式为：

IF 条件 THEN
语句段
END IF

多分支

本实例中，当整型变量NUMBER1小于整型变量NUMBER2时，输出字符串“NUMBER1小于NUMBER2”，否则输出字符串“NUMBER1大于等于NUMBER2”

    SQL> DECLARE
      2  NUMBER1 INTEGER:=10;
      3  NUMBER2 INTEGER:=10;
      4  BEGIN
      5  IF NUMBER1<NUMBER2 THEN
      6  DBMS_OUTPUT.PUT_LINE('NUMBER1小于NUMBER2');
      7  ELSE
      8  DBMS_OUTPUT.PUT_LINE('NUMBER1大于等于NUMBER2');
      9  END IF;
     10  END;
     11  /

本实例通过IF…THEN…ELSE…END IF双分支语句实现条件满足和不满足时的不同输出，该语句的实现格式如下

if 条件表达式 then
语句块1;
else
语句块2;
end if;

ELSIF

create or replace PROCEDURE DX2_06(name EMP1.ENAME%TYPE,num EMP1.EMPNO%TYPE)
AS
job1 EMP1.JOB%TYPE;
newsal EMP1.SAL%TYPE;
BEGIN
select JOB,SAL into job1,newsal
from EMP1
where EMPNO=num OR ENAME = name;
IF job1 = 'clerk' THEN
newsal:=newsal+500;
ELSIF job1 = 'salesman' THEN
newsal:=newsal+1000;
ELSIF job1 = 'analyst' THEN
newsal:=newsal+1500;
ELSIF job1 = 'otherwise' THEN
newsal:=newsal+3000;
END IF;
update emp1 set sal = newsal where EMPNO=num OR ENAME = name;
dbms_output.put_line(name || '工资已经更改为:'|| newsal);
END;

case

本实例将前面实例判断SCORE值属于“优秀”、“良好”、“中等”、“及格”和“不及格”5个范围中的哪一个进行改写，以CASE语句来实现

SET SERVEROUTPUT ON;
declare
score integer:=50;
begin
case
when score>=90 then
dbms_output.put_line('优秀');
when (score<90 and score>=80) then
dbms_output.put_line('良好');
when (score<80 and score>=70) then
dbms_output.put_line('中等');
when (score<70 and score>=60) then
dbms_output.put_line('及格');
else
dbms_output.put_line('不及格');
end case;
end;

循环

PL/SQL中有4种基本循环结构，在它们的基础上又可以演变出许多嵌套循环控制。

第一种 IF

    LOOP
      循环语句段;
      IF条件语句THEN
        EXIT;
      ELSE
        处理语句段;
      END IF
    END LOOP

例:1+2+3+4+…+100

    SQL> DECLARE
      2   K integer:=0;
      3   S integer:=0;
      4  BEGIN
      5   LOOP                                  --开始循环
      6     S:=S+K;
      7     IF K=100 THEN                      --循环条件比较
      8       EXIT;                             --退出循环
      9     ELSE
      10       K:=K+1;                          --循环变量累加
      11     END IF;
      12   END LOOP;
      13  DBMS_OUTPUT.PUT_LINE('共循环次数:'||K);
      14  DBMS_OUTPUT.PUT_LINE('表达式的值为:'||S);
      15  END;
      16  /
    共循环次数:100
    表达式的值为:5050

第二种 WHEN

    LOOP
      循环语句段;
      EXIT  WHEN条件语句 ;
    END LOOP;

    SQL> DECLARE
      2   K integer:=0;
      3   S integer:=0;
      4   J integer:=0;
      5  BEGIN
      6   LOOP                             --开始循环
      7     S:=S+K;
      8     K:=K+2;                        --循环变量累加
      9     J:=J+1;
      10     EXIT WHEN K>100;               --退出循环的条件
      11   END LOOP;
      12  DBMS_OUTPUT.PUT_LINE('共循环次数:'||J);
      13  DBMS_OUTPUT.PUT_LINE('100以内偶数和为:'||S);
      14  END;
      15  /
    共循环次数:51
    100以内偶数和为:2550

第三种 WHILE

 WHILE条件语句LOOP
      循环语句段;
 END LOOP;

第四种 FOR

    FOR循环变量IN变量取值范围LOOP
      循环语句段;
    END LOOP;

    SQL> DECLARE
    2   K integer:=1;
    3   S integer:=0;
    4   J integer:=0;
    5  BEGIN
    6   FOR K IN 1..100 LOOP                      --开始循环
    7     S:=S+K;                                 --求和
    8     J:=J+1;
    9   END LOOP;                                 --结束循环
    10  DBMS_OUTPUT.PUT_LINE('共循环次数:'||J);
    11  DBMS_OUTPUT.PUT_LINE('100以内自然数和为:'||S);
    12  END;
    13  /
  共循环次数:100
  100以内自然数和为:5050

创建存储过程

存储过程是一个能执行某个特定操作的子程序，是常用的数据库对象之一。在具体应用中，存储过程与基本表、视图、索引一样使用频繁，是读者需重点掌握的对象。存储过程按照是否含参数分为不带参数和带参数的存储过程两类。

不带参数的存储过程

格式

Oracle中创建存储过程的常用语句格式为：

    CREATE OR REPLACE PROCEDURE存储过程名
    AS | IS
    定义语句块
    BEGIN
    执行语句块
    END

例:

create or replace procedure obj_1 as
cnumber int
begin
select count(*) into cnumber form STU;
dbms_output.put_line('stu表共有||cnumber||'行记录';
end obj_1;

存储过程创建完成后，读者可以通过查询数据字典USER_SOURCE来查看其名称和内容。例如，下面语句查询名称为“CNUM”的存储过程内容：

    SELECT TEXT FROM USER_SOURCE WHERE TYPE='PROCEDURE' AND NAME='CNUM';

调用

调用不带参数的存储过程
（1）EXEC存储过程名（2）BEGIN存储过程名;END;

带参数的存储过程

格式

    CREATE OR REPLACE PROCEDURE存储过程名(参数 参数类型)
    AS | IS
    定义语句块
    BEGIN
    执行语句块
    [EXCEPTION]
    异常处理语句块
    END

调用

（1）EXEC存储过程名(参数)（2）CALL存储过程名(参数)（3）BEGIN存储过程名(参数);END;

需要注意的是，如果传给一个存储过程的参数是变量时，必须使用上述第3种方式调用，并用DECLARE语句声明变量类型。例如，参数为变量SNUM的调用实现语句如下：

    SQL> DECLARE
      2  SNUM STU.SNO%TYPE;
      3  BEGIN
      4  SNUM:=120005;
      5  GETCLASS(SNUM);
      6  END;
      7  /
    120005的学生所在班级为 12计算机
    PL/SQL过程已成功完成。