Oracle 学习（四）PL/SQL

最新推荐文章于 2024-03-16 17:53:56 发布

已改行

最新推荐文章于 2024-03-16 17:53:56 发布

阅读量1k

点赞数 1

分类专栏： Oracle

任何你写的代码，超过6个月不去看它，当你再看时，都像是别人写的

本文链接：https://blog.csdn.net/qq_38977097/article/details/100708108

版权

Oracle 专栏收录该内容

3 篇文章 26 订阅

订阅专栏

一、SQL和PL/SQL的区别

SQL是结构化查询语言，比较接近自然语言，使用SQL，只需要说干什么，不需要说怎么干。由数据定义语言、数据操纵语言、数据控制语言构成，它不面向过程，即前一条语句与后一条语句无关。SQL是标准的数据库语言。

PL/SQL，Oracle对SQL标准的扩充，增加了面向过程的功能，所以可以用来编写存储过程、存储函数、触发器等等。 PL/SQL是结构化SQL，就是在标准SQL中加入了IF…ELSE… For…等控制过程的SQL。

二、具体的什么是PL/SQL

PL/SQL(Procedural Language/SQL) 是对SQL的扩充,它吸收了近年来编程语言的许多最高设计特点:如数据封装性、信息隐蔽性、重载和例外处理等。它允许SQL的数据操纵语言和查询语句包含在块结构(block_structured)和代码过程语言中,使PL/SQL成为一个功能强大的事务处理语言。

三、PL/SQL基本语法

1、基本架构

declare 
  -- Local variables here
  i integer;
begin
  -- Test statements here
end;

上边就是一个plsql能够执行的基本架构（并不是存储过程），其中 declare 顾名思义就是描述的意思就是在这个下边可以对变量进行声明。

2、声明变量

变量可以在声明时赋值，也可以先定义后赋值；
使用%TYPE与%ROWTYPE可以根据已有类型定义变量。

PL/SQL是一种强类型的编程语言，所有的变量都必须在它声明之后才可以使用，变量都要求在declare部分进行声明，而对于变量的名称也有如下的一些规定：

变量名称的组成可以由字母、数字、_、$、#等组成；
所有的变量名称要求以字母开头，不能是Oracle中的保留字（关键字）；
变量的长度最多只能为30个字符。

声明变量的语法：

变量名称 [CONSTANT] 类型 [NOT NULL] [:=value] ;

组成：
CONSTANT：定义常量，必须在声明时为其赋予默认值；
NOT NULL：表示此变量不允许设置为NULL；
:=value：表示在变量声明时，设置好其初始化内容。

声明变量有大概如下两三种方式：

v_temp varchar(10);
v_temp tablename.property%type;（表明属性名%type就是该表属性的类型，这样可以灵活使用）
v_stu student%rowtype;（这种类型就是定义一个变量为表数据的行类型用于接受查询的一行数据，注意是一行数据）

%rowtype 栗子：

DECLARE
deptRow    dept%ROWTYPE ;        -- 装载一行dept记录
BEGIN
SELECT * INTO deptRow FROM dept WHERE deptno=10 ;
 --当用户使用了“SELECT … INTO …”将表中的一行记录设置到了ROWTYPE类型的变量之中，
 就可以利用“rowtype变量.表字段”的方式取得表中每行的对应列数据。
DBMS_OUTPUT.put_line('部门编号：'||deptRow.deptno || '，名称：' || deptRow.dname || '，位置：' || deptRow.loc) ;
END ;

部门编号：10，名称：ACCOUNTING，位置：NEW YORK

说到变量，必须了解数据表中的数据类型

3、数据类型

数据表中的数据类型:字符型，数值型，日期型，其他型

字符型:
char(n) char(8)只能存8个字母 n最大2000 不满使用空格补
nchar(n) 根据Unicode标准定义的，所以nchar(8)能存8个汉字 n最大1000
varchar(n) 不要使用，使用varchar2，目前和varchar2一样，以后计划会改
varchar2(n) 可变长度，不会使用空格填充至最大长度 n最大4000
nvarchar2(n) 可变长度，根据Unicode标准定义 n最大2000

数值型：
number(p,s) s:小数点后的位数 p:有效数字如：number(5，2) 有效数字5，保留2位小数，如123.45
float(n) 主要用于存储二进制数据，1-126位 *0.30103转十进制

日期型：
date 表示范围：公元前4712年1月1日到9999年12.31 可以精确到秒
timestamp 更精确

其他类型:
blog 4G 以二进制存储的数据
clog 4G 以字符串存储的数据

回到基本架构，接下来可以看到begin，这里的begin类似于我们的java中的main函数这里可以对变量进行赋值，而且程序是从这来开始来正式执行的。所以一般我们在这里对变量进行赋值

4、变量赋值

declare 
  --薪水
 v_salary emp.ename%type;

begin
   v_salary:=1500;
   --语句赋值操作。
end;

上边这里begin下边就是对v_salary进行赋值，这里有一点需要注意：plsql中的复制是:=而不是单个=，单个=是判断是否相等意思。这一点需要大家注意。

既然变量也声明了，也赋值了，那么我们需要测试如何输出打印该值。

5、打印输出

dbms_output.put_line()

上边这个语句类似于System.out.println()操作

declare 
  --姓名
  v_name varchar2(50) ;
  
begin
v_name:='张三丰';
dbms_output.put_line('姓名---〉'||v_name);
end;

上边这个程序中就可以按一下F8（快捷键）来执行了，执行结果可以在下边这个方框里查看了。

其中dbms_output.put_line是输出的重要操作。

在这里插入图片描述
6、循环

下边讲一下关于plsql中关于循环的使用。

在PLSQL中，循环的语法有三种：

WHILE循环：

while后面跟的是循环条件，与Java的差不多，LOOP和END LOOP是关键字。

WHILE  total  <= 25000  

LOOP
    total : = total + salary;
END  LOOP;

LOOP循环：

exit后面的条件成立了才退出循环【有点绕】

Loop
   exit [when 条件成立];
   total:=total+salary;
end loop;

FOR循环：

循环的递增只能是1，不能自定义步长

FOR   I   IN   1 . . 3  

LOOP

语句序列 ;

END    LOOP ;

栗子：
使用loop循环显示1-10

declare
    i number(2) := 1;
begin
    loop
        --当i>10时，退出循环
        exit when i>10;
        --输出i的值
        dbms_output.put_line(i);
        --变量自加
        i := i + 1;  
    end loop;
end;
/

使用while循环显示1-10

declare
    i number(2) := 1;
begin
    while i<11 
    loop
        dbms_output.put_line(i);
        i := i + 1;
    end loop;
end;
/

使用while循环，向emp表中插入999条记录

declare
    i number(4) := 1;
begin 
    while( i < 1000 )
    loop
        insert into emp(empno,ename) values(i,'哈哈');
        i := i + 1;
    end loop;   
end;
/

7、控制结构

①if语句

If 条件 Then 执行语句;

Elsif 条件 then 执行语句;

Else 执行语句 ;

End if;

②case语句

Case 变量

When 值1 then 执行语句；

When 值2 then 执行语句；

...

End case;

或者是:

Case

When 条件1  then 执行语句；

When 条件2  then 执行语句；

....

End case;

同corejava中的switch，虽然没有break语句，但是当满足一个when的条件而执行其后的操作后，直接结束case语句块。

8、异常

Declare

声明异常（自定义异常、非预定义异常）

Begin

（执行体。条件判断后，执行语句抛出自定义异常，同try）

Exception

When判断异常（相当于corejava中的catch)

End;

PL/SQL中的异常，三类：

预定义异常、非预定义异常和用户自定义异常。

非预定义异常： 当pl/sql块执行出现错误时，报错一个错误号，为了捕捉该错误，将错误号与异常关联，即成非预定义异常。

异常名  exception;

Pragma exception_init(异常名，错误号）；

9、游标

游标概念： 游标本质是一块内存区域，由系统或用户以变量的形式定义，可以提高数据库数据处理速度。游标的工作机制是一种能从包括多行数据记录的结果集中每次提取一行记录的机制，即游标提供了在逐行的基础上操作表中数据的方法。

注意：类似于java中迭代器（iterator）

解决问题： 数据库在并没有一种描述表中单一记录的表达形式，除非使用where 子句来限制只有一条记录被选中。因此我们必须借助于游标来进行面向单条记录的数据处理。在 PL/SQL 程序中，对于处理多行记录的事务经常使用游标来实现。

游标这个是非常重要的知识点，下面内容只是简单了解游标，详解推荐博文：PLSql之游标（cursor）重点

先来说说游标的属性：

游标的属性：
%rowcount 整型获得fetch语句返回的数据行数
%found 布尔型最近的fetch语句返回一行数据则为真，否则为假
%notfound 布尔型与%found属性的返回值相反
%isopen 布尔型游标已经打开则为真，否则为假

其中%notfound是游标中找不到元素时候返回true,通常用来判断推退出循环。

游标的使用需要有四个步骤，记住这四个步骤即可。

声明------》打开-----》取值-----》关闭

声明游标：关键字 cursor + 任意名字[参数列表（可有可无）] +is +你的sql语句；

栗子：

cursor cur is
    select name,age from student;

这里就是声明了一个游标并且给其赋值。

打开游标： open + 游标；

栗子：

open cur;

这里没有什么可说的就类似于jdbc 的connection的open方法

取值： 取值有一个关键字叫做fetch英文翻译来就是抓取的意思使用方法如下：：

fetch +游标名称 into 你定义的变量；就是将抓取的游标赋给你之前定义的其他变量。

因为既然使用了游标肯定是有多行数据。所以一般这里的fetch都是放在loop里边的。

栗子:

declare 
cursor cur is select e.ename,e.empno from emp e;
 --声明变量接受游标中的数据。
 v_name emp.ename%type;
 v_num emp.empno%type;
 
begin
  --打开游标
  open cur;
  --遍历取值
  loop
  --获取游标中德数据，如果有则赋值变量，否则退出
    fetch cur into v_name,v_num;
    exit when cur %notfound;  
    --打印输出
    dbms_output.put_line('姓名:' || v_name || '年龄:' ||v_num);
  end loop;
  
  --关闭游标
  close cur;
end;

关闭游标： 当便利完毕关闭游标即可：close +游标名；

上边有个语法叫做

exit when cur%notfound；

这里的属性cur%notfound就是如果游标中没有值返回true，因为每次游标都是向下读取的。读到没有数据就会返回cur%notfound为true。

10、视图

视图具有以下优点：

可以限制用户只能通过视图检索数据。这样就可以对最终用户屏蔽建表时底层的基表。
可以将复杂的查询保存为视图。可以对最终用户屏蔽一定的复杂性。
限制某个视图只能访问基表中的部分列或者部分行的特定数据。这样可以实现一定的安全性。
从多张基表中按一定的业务逻辑抽出用户关心的部分，形成一张虚拟表。

语法：

CREATE [OR REPLACE] [{FORCE|NOFORCE}] VIEW view_name
 
AS
 
SELECT查询
 
[WITH READ ONLY CONSTRAINT]

解析：

OR REPLACE：如果视图已经存在，则替换旧视图。
FORCE：即使基表不存在，也可以创建该视图，但是该视图不能正常使用，当基表创建成功后，视图才能正常使用。
NOFORCE：如果基表不存在，无法创建视图，该项是默认选项。
WITH READ ONLY：默认可以通过视图对基表执行增删改操作，但是有很多在基表上的限制(比如：基表中某列不能为空，但是该列没有出现在视图中，则不能通过视图执行insert操作)，WITH READ ONLY说明视图是只读视图，不能通过该视图进行增删改操作。现实开发中，基本上不通过视图对表中的数据进行增删改操作。

案例： 基于EMP表和DEPT表创建视图

CREATE OR REPLACE VIEW EMPDETAIL
 
AS
 
SELECT EMPNO,ENAME,JOB,HIREDATE,EMP.DEPTNO,DNAME
 
FROM EMP JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO
 
WITH READ ONLY

使用视图：

SELECT * FROM EMPDETAIL;

视图就是将复杂的sql语句封装成一个虚表，一般都是常用的业务逻辑进行封装视图，具体操作和表一样，一般视图都是设置为只读的。

11、存储过程

下边我们开始建立简单的存储过程：

右键单击这个—>new
在这里插入图片描述
然后name写上存储过程名称，parameters可以不写。

下边就是一个模板：

然后就可以在这里写具体业务逻辑了。其实这里的declare没有了但我们声明变量可以直接在begin上边声明，begin里进行赋值然后写业务逻辑即可。

create or replace procedure p_hello is
--无参的存储过程。
--声明变量
begin
       dbms_output.put_line('hello world');      
end p_hello;

上边就是一个helloworld版本的存储过程。存储过程是不能直接执行的我们可以借助上边用的Test window来调用存储过程。

调用方法：新建一个TEST WINDOW begin与end之间输入 p_hello；即可执行

declare
begin
    p_hello;
  end;

这样执行F8就可以看到输出helloworld至此调用存储过程成功。

12、包

包分为包规范和包体。包即是将相关的pl/sql元素组织在一起，打包。

包体的实现一定要先定义包规范。

包可以只有规范，而没有包体。

包规范：

Create or replace package 名字

Is|as

...（元素声明）

如：Procedure 名字（参数列表）；

End [名字]；

包体：

Create or replace package body名字

Is|as

...（元素）

End [名字]；

13、PLSQL的各种窗口区别

数据库对于从事IT行业的来说可以说是经常打交道，并不陌生，不少企业plsql这款工具，可是大家都知道每次创建一个新任务窗口时，总会看到好几个类型窗口选择（如下图所示），绝大部分人用sql窗口够用了，可大家有没有想过其他类型的窗口是做啥用呢，下面给大家简要讲解下：

在这里插入图片描述
PW（程序窗口）:
可以执行 sql,sqlplus 相关的语句，例如存储过程，方法，一般用来开发程序用的。

TW（测试窗口）:
一般是用来测试存储过程等的debug。

SW（SQL窗口）:
执行的是dml,ddl语句,主要用户语句的查询、显示、执行统计信息等（应用最多的一个窗口）。例如 desc table不能在SQL 窗口中执行，必须在Command 窗口中才能执行。

RW（报告窗口）:
方便用于展示有聚合查询的用图表形式展示的窗口，例如sum(),count()等，有x,y轴的。

CW（command命令窗口）:
除了可以执行sql/sqlplus 相关的命令、sql脚本，还可以执行更多的命令，例如show parameter, set define off等。

EPW（解释计划窗口）:
解释执行计划的，调优时，经常用到(F5)。

DW（图表窗口）:
问百度

补充： 大部分人主要用的还是sql窗口和命令窗口，下面来说明下sql窗口和命令窗口区别：

SQL窗口与命令窗口的区别：

command窗口是命令窗口，即为sqplus窗口，有命令提示符，识别sqlplus命令，基本的命令都可以执行;
sql窗口仅可执行DDL、DML等。

最后

推荐PLSQL学习优秀博文：Oracle总结【PLSQL学习】

推荐PLSQL使用技巧博文：PLSQL使用技巧----加快你的编程效率

已改行

关注

1
点赞
踩
14

收藏

觉得还不错? 一键收藏
0
评论
Oracle 学习（四）PL/SQL

一、SQL和PL/SQL的区别SQL是结构化查询语言，比较接近自然语言，使用SQL，只需要说干什么，不需要说怎么干。由数据定义语言、数据操纵语言、数据控制语言构成，它不面向过程，即前一条语句与后一条语句无关。SQL是标准的数据库语言。PL/SQL，Oracle对SQL标准的扩充，增加了面向过程的功能，所以可以用来编写存储过程、存储函数、触发器等等。 PL/SQL是结构化SQL，就是在标准SQ...
复制链接

扫一扫

专栏目录