Oracle19C入门到熟练017-PL/SQL编程

浪飞yes

已于 2024-09-11 09:51:02 修改

阅读量1.2k

点赞数 1

分类专栏：入门Oracle19C 文章标签： oracle19c PL/SQL sql

于 2022-03-30 13:44:40 首次发布

本文链接：https://blog.youkuaiyun.com/langfeiyes/article/details/123844072

版权

入门Oracle19C 专栏收录该内容

25 篇文章

订阅专栏

视频教程

新版Oracle19C入门到熟练_哔哩哔哩_bilibili

学习要求

有一定关系型数据的操作功底，会SQL语句

教学目标

熟练掌握Oracle数据库PL/SQL编程

PL/SQL简介

SQL语言是访问、操作数据库的语言，并不是一门程序设计语言，因此不能用于程序开发，接下讲的PL/SQL(Procedual Language/SQL)就是Oracle在标准SQL语言基础上进行过程性扩展后形成的程序设计语言，是一种Oracle数据库特有的，支持应用级别开发的语言。

PL/SQL 是 Procedure Language & Structured Query Language 的缩写。ORACLE 的 SQL 是支持 ANSI(American national Standards Institute)和 ISO92 (International Standards Organization)标准的产品。PL/SQL 是对 SQL 语言存储过程语言的扩展。从 ORACLE6 以后，ORACLE 的 RDBMS 附带了 PL/SQL。它现在已经成为一种过程处理语言，简称 PL/SQL。目前的 PL/SQL 包括两部分，一部分是数据库引擎部分；另一部分是可嵌入到许多产品（如 C 语言，JAVA 语言等）工具中的独立引擎。可以将这两部分称为：数据库 PL/SQL 和工具 PL/SQL。两者的编程非常相似。都具有编程结构、语法和逻辑机制。

大白话：PL/SQL是有门类似脚本语言过程式编程语言。

PL/SQL VS SQL

PL/SQL 是 ORACLE 系统的核心语言，现在 ORACLE 的许多部件都是由 PL/SQL 写成。

在 PL/SQL 中可以直接使用部分SQL 语句：

INSERT，UPDATE，DELETE，SELECT … INTO，COMMIT，ROLLBACK，SAVEPOINT等

注意

在 PL/SQL 中只能用 SQL 语句中的 DML 部分，不能用 DDL 部分

PL/SQL使用特点

PL/SQL 程序执行前，会对在其所使用的表名、列名及数据类型进行检查。
PL/SQL 可以在 SQL*PLUS 中使用。
PL/SQL 可以在高级语言中使用。
PL/SQL 可以在 ORACLE 的开发工具中使用。
其它开发工具也可以调用 PL/SQL 编写的过程和函数

基本结构

跟所有过程化语言一样，PL/SQL也是一种模块式结构的语言，它由三个块组成，即声明部分、执行部分、异常处理部分，结构如下：

DECLARE -- 声明块
	-- 声明一些变量、常量、用户定义的数据类型以及游标等
	-- 这一部分可选，如不需要可以不写
BEGIN  -- 执行块
	-- 程序主体，可以写各种逻辑
EXCEPTION --异常块
	-- 异常处理程序，当程序出错误是执行
END; 
	-- 程序结束

其中的DECLARE,EXCEPTION 是可以省略的

比如下面几种都是合法的

BEGIN
	-- 执行部分
END;

DECLARE
	-- 声明
BEGIN
	-- 执行部分
END;

BEGIN
	-- 执行部分
EXCEPTION
	-- 异常
END;

根据不同编写格式可以将PL/SQL程序分为三类：

无名块：动态构造，只能执行一次。类似js中匿名函数一样，执行完就完了
子程序：存储在数据库中的存储过程、函数及包等。当在数据库上建立好后可以在其它程序中调用它们。
触发器：当数据库发生操作时，会触发一些事件，从而自动执行相应的程序。

需求：使用PL/SQL写一个HelloWord

注意：先执行set serveroutput on 打开ORCALE默认输入功能

begin
    dbms_output.put_line('hello pl/sql');
end;

这里的 dbms_output.put_line('hello pl/sql'); 类似java中System.out.println("...");

标识符

PL/SQL 程序设计中的标识符定义与 SQL 的标识符定义的要求相同。要求和限制有：

标识符名不能超过 30 字符；
第一个字符必须为字母；
不分大小写；
不能用’-‘(减号);
不能是 SQL 保留字

注意

定义变量名时，不建议跟列名一样，PL/SQL操作时，容易编译出问题。

比如：

declare
	ename varchar2(10);
begin
	select * from emp where ename = ename;  --此时的ename就不止是列还是变量名
end;

命名规则

标识符命名建议：一般建议使用前缀，如果是多单词组成，使用 _ 隔开而不是驼峰命名方式

变量

基本类型

PL/SQL支持变量类型，跟列类型基本一致

PL/SQL 数据类型	SQL 数据类型	描述
BINARY_INTEGER	INTEGER	整型数字数据
BLOB	BLOB（4096）	二进制数据
BLOB (n)	BLOB (n) n = 1 到 2147483647	二进制大对象数据
BOOLEAN	BOOLEAN	逻辑布尔值（true 或 false）
CHAR	CHAR (n) 如果环境的字符串单元设置为 CODEUNITS32，那么 n = 63 否则，n = 255	长度为 n 的定长字符串数据
CHAR (n)	CHAR (n) n = 1 到 255	长度为 n 的定长字符串数据
CHAR (n CHAR)	CHAR (n CODEUNITS32 ) n = 1 到 63	长度为 n 个 UTF-32 代码单元的定长字符串数据1
CHAR VARYING (n)	VARCHAR (n)	最大长度为 n 的可变长度字符串数据
CHAR VARYING (n CHAR)	VARCHAR (n CODEUNITS32 ) n = 1 到 8 168	最大长度为 n 个 UTF-32 代码单元的可变长度字符串数据 1
CHARACTER	CHARACTER (n) 如果环境的字符串单元设置为 CODEUNITS32，那么 n = 63 否则，n = 255	长度为 n 的定长字符串数据
CHARACTER (n)	CHARACTER (n) n = 1 到 255	长度为 n 的定长字符串数据
CHARACTER (n CHAR)	CHARACTER (n CODEUNITS32 ) n = 1 到 63	长度为 n 个 UTF-32 代码单元的定长字符串数据1
CHARACTER VARYING (n)	VARCHAR (n) n = 1 到 32672	最大长度为 n 的可变长度字符串数据
CHARACTER VARYING (n CHAR)	VARCHAR (n CODEUNITS32 ) n = 1 到 8 168	最大长度为 n 个 UTF-32 代码单元的可变长度字符串数据 1
CLOB	CLOB (1M)	字符大对象数据
CLOB (n)	CLOB (n) n = 1 到 2147483647	长度为 n 的字符大对象数据
CLOB (n CHAR)	CLOB (n CODEUNITS32 ) n = 1 到 536 870 911	长度为 n 个 UTF-32 代码单元的字符大对象字符串数据1
DATE	DATE 2	日期和时间数据（精度为秒）
DEC	DEC (9, 2)	十进制数字数据
DEC (p)	DEC (p) p = 1 到 31	精度为 p 的十进制数字数据
DEC (p, s)	DEC (p, s) p = 1 到 31；s = 1 到 31	精度为 p 并且小数位数为 s 的十进制数字数据
DECIMAL	DECIMAL (9, 2)	十进制数字数据
DECIMAL (p)	DECIMAL (p) p = 1 到 31	精度为 p 的十进制数字数据
DECIMAL (p, s)	DECIMAL (p, s) p = 1 到 31；s = 1 到 31	精度为 p 并且小数位数为 s 的十进制数字数据
DOUBLE	DOUBLE	双精度浮点数
DOUBLE PRECISION	DOUBLE PRECISION	双精度浮点数
FLOAT	FLOAT	浮点数字数据
FLOAT (n) n = 1 到 24	REAL	实数数字数据
FLOAT (n) n = 25 到 53	DOUBLE	双精度数字数据
INT	INT	带符号 4 字节整型数字数据
INTEGER	INTEGER	带符号 4 字节整型数字数据
LONG	CLOB (32760)	字符大对象数据
LONG RAW	BLOB (32760)	二进制大对象数据
LONG VARCHAR	CLOB (32760)	字符大对象数据
NATURAL	INTEGER	带符号 4 字节整型数字数据
NCHAR	NCHAR (n) 3 如果 NCHAR_MAPPING 配置参数设置为 GRAPHIC_CU32 或 CHAR_CU32，那么 n = 63 n = 127，否则	长度为 n 的固定长度本地语言字符串数据
NCHAR (n) n = 1 到 2000	NCHAR (n) 3	长度为 n 的固定长度本地语言字符串数据
NCLOB 4	NCLOB(1M) 3	National 字符大对象数据
NCLOB (n)	NCLOB (n) 3	最大长度为 n 的本地语言字符大对象数据
NVARCHAR2	NVARCHAR 3	可变长度 National 字符串数据
NVARCHAR2 (n)	9NVARCHAR (n) 3	最大长度为 n 的可变长度本地语言字符串数据
NUMBER	NUMBER 5	精确数字数据
NUMBER (p)	NUMBER (p) 5	最大精度为 p 的精确数字数据
NUMBER (p, s)	NUMBER (p, s) 5 p = 1 到 31；s = 1 到 31	最大精度为 p 并且小数位数为 s 的精确数字数据
NUMERIC	NUMERIC (9.2)	精确数字数据
NUMERIC (p)	NUMERIC (p) p = 1 到 31	最大精度为 p 的精确数字数据
NUMERIC (p, s)	NUMERIC (p, s) p = 1 到 31; s = 0 到 31	最大精度为 p 并且小数位数为 s 的精确数字数据
PLS_INTEGER	INTEGER	整型数字数据
RAW	VARBINARY(32672)	可变长度二进制字符串数据
RAW (n)	VARBINARY(n) n = 1 到 32672	可变长度二进制字符串数据
SMALLINT	SMALLINT	带符号 2 字节整型数据
TIMESTAMP (0)	TIMESTAMP (0)	包含时间戳记信息的日期数据
TIMESTAMP (p)	TIMESTAMP (p)	包含可选的小数秒和精度 p 的日期和时间数据
VARCHAR	VARCHAR (4096)	最大长度为 4096 的可变长度字符串数据
VARCHAR (n)	VARCHAR (n)	最大长度为 n 的可变长度字符串数据
VARCHAR (n CHAR)	VARCHAR (n CODEUNITS32 ) n = 1 到 8 168	最大长度为 n 个 UTF-32 代码单元的可变长度字符串数据 1
VARCHAR2 (n)	VARCHAR2 (n) 6	最大长度为 n 的可变长度字符串数据
VARCHAR2 (n CHAR)	VARCHAR2 (n CODEUNITS32 ) n = 1 到 8 168 6	最大长度为 n 个 UTF-32 代码单元的可变长度字符串数据 1

非常多，但是开发中用不上那么多，下面几个是常用的。

类型	说明
CHAR(n)	定长字符串,长度可选,缺省1。最大可达2000字节
VARCHAR2(n)	可变字符串,长度不能缺省,最大可达4000字节
BINARY_INTEGER	带符号整数,为整数计算优化性能
NUMBER(p,s)	数值，p代表刻度，s代表精度
LONG	变长字符串
DATE	日期
BOOLEAN	布尔
ROWID	存放数据库行号

记录类型

记录类型是把逻辑相关的数据作为一个单元存储起来，称作 PL/SQL RECORD ，其作用是存放互不相同但逻辑相关的信息。类似Java中的类。

定义语法

TYPE 记录名 IS RECORD(
     Field1 type1 [NOT NULL] [:= exp1 ],
     Field2 type2 [NOT NULL] [:= exp2 ],
     . . . . . .
     Fieldn typen [NOT NULL] [:= expn ] 
)

案例

declare
	type user_record_type is record(   -- 定义记录类型， 类似java定义类
    	name varchar(10),
        age number
    );
    v_user user_record_type;	-- 定义记录类型变量
begin
	v_user.name := 'dafei';
	v_user.age := 18;
	dbms_output.put_line('name : ' || v_user.name || ', age ：' || v_user.age);
end;

案例

declare
	type emp_record_type is record(   
    	ename varchar(10),
        sal number
    );
    v_emp emp_record_type;	
begin
	select ename, sal into v_emp from emp where ename = 'KING';
	dbms_output.put_line('name : ' || v_emp.ename || ', sal ：' || v_emp.sal);
end;

注意： select ... into .. 语句要求查询出来列与记录属性一一对象

记录类型简化-%TYPE

定义一个变量，其数据类型与已经定义的某个数据变量的类型相同，或者与数据库表的某个列的数据类型相同，这时可以使用%TYPE。

使用%TYPE 特性的优点在于：

所引用的数据库列的数据类型可以不必知道；
所引用的数据库列的数据类型可以实时改变。

declare
	type emp_record_type is record(   
    	ename emp.ename%type,
        sal emp.sal%type
    );
    v_emp emp_record_type;	
begin
	select ename, sal into v_emp from emp where ename = 'KING';
	dbms_output.put_line('name : ' || v_emp.ename || ', sal ：' || v_emp.sal);
end;

记录类型简化-%ROWTYPE

PL/SQL 提供%ROWTYPE 操作符, 返回一个记录类型, 其数据类型和数据库表的数据结构相一致。

使用%ROWTYPE 特性的优点在于：

所引用的数据库中列的个数和数据类型可以不必知道；
所引用的数据库中列的个数和数据类型可以实时改变。

declare
    v_emp emp%rowtype;	
begin
	select * into v_emp from emp where ename = 'KING';
	dbms_output.put_line('name : ' || v_emp.ename || ', sal ：' || v_emp.sal);
end;

PL/SQL注释

注释目的为了增强程序可读性,分单行注释，多行注释

单行注释

使用 -- 表示，跟sql中注释一样

DECLARE
	v_deptno number;	-- 定义变量，执行时，需要传入
	v_dname  varchar2(10);
	v_loc 	 varchar2(10);
BEGIN
	insert into dept(deptno, dname, loc)
	values(v_deptno, v_dname,v_loc );  -- 执行insert sql，使用之前定义变量
END;

多行注释

使用/**/表示，类似java中的单行注释

/*这是多行注释*/

简单练习

需求：使用PL/SQL方式进行添加一个部门

declare
	v_no   number(2) := 60;
	v_name varchar2(14) := 'DEV';
	v_loc  varchar2(13) := 'GUANGZHOU';
begin
	insert into dept(deptno, dname, loc)
	values(v_no, v_name, v_loc);
end;

需求：使用PL/SQL方式进行删除一个指定id的部门

declare
	v_no   number(2) ;
begin
	delete from dept where deptno = &v_no;
	commit;
end;

注意：在PL/SQL中需要自己手动控制事务，而开发中，一般由调用PL/SQL语句块代码控制