第六章：PL/SQL(Oracle对SQL的拓展）

萨菲罗

已于 2024-01-30 15:53:54 修改

阅读量860

点赞数 21

分类专栏： Oracle数据库学习笔记文章标签： sql oracle 数据库

于 2024-01-22 12:23:31 首次发布

本文链接：https://blog.csdn.net/m0_64264641/article/details/135714633

版权

Oracle数据库学习笔记专栏收录该内容

6 篇文章 0 订阅

订阅专栏

八.包（package类似c++库函数）：

九.集合：

前言

是对标准SQL的拓展，目前包括两部分：数据库引擎称为数据库SQL；可嵌入到许多产品（例如c，java等）的独立引擎，本文介绍数据库PL/SQL(简称PL/SQL)。

一、PL/SQL概述

1.组成：

DDL,DML,DCL。PL/SQL是面向过程语言和SQL语言的结合（从名字可看出p是指procedure-oriented）所以它扩充了面向过程的结构：变量，类型，控制语句，过程和函数，对象类型和方法等。

二.字符集：

和SQL差不多，在此就只解释一些常用符号

：=	赋值	a:=a+1（SQL中的=类似于C语言的==）
\|\|	并置	全名：='bai'\|\|' '\|\|'du'(连接形成新字符bai du箭头百+空格+度）
- -	注释符
/* */	注释界定符

三.变量，常量和数据类型：

程序代码大体样子：

1.变量：指有程序读取或赋值的存储单元，用于临时存放数据。数据是通过变量在数据库和pl/sql

中传递的

（1）变量声明：

DECLARE 

   变量名 数据类型(宽度):=初值；

……

（2）变量属性：

%TYPE:此属性提供了变量和数据库列的数据类型，用于声明一个包含数据库值的变量。例如声明一个变量和学生表中学号列有相同数据类型
```
my_xh 学生表.学号%TYPE;
```
优势：不用知道学号数据类型；改变学号则my_xh自动更新；
%ROWTYPE属性：和上面属性类似，这是针对一个记录（一行）的

记录名 目标的表名%ROWTYPE;

对游标（CURSOR）使用则是声明一个记录存储相同信息。

DECLARE
CURSOR c1
IS
SELECT 学号, 课程号, 成绩
FROM CJB;
cj_rec c1%ROWTYPE;

打开游标后使用fetch into则将游标的值都赋予给它

FETCH c1 INTO cj_rec;

（3）变量作用域：就是可以访问该变量的程序部分这个区域。
2.常量：

名称和数据类型之间加一个constant。

3.常用数据类型：

varchar(n)：和varchar2完全相同；
number（总长度，小数位）：例如（10,2）就是整数8位，小数2位；
date：用7字节分别描述年月日时分秒，由初始化函数NLS_DATE_FORMAT来设置。
boolean：只有TRUE和FALSE

4.对象类型：类似于C++语言的类，封装了数据结构和操纵数据结构的过程和函数（即属性和方法）。

属性不能用long,long raw,rowid,urowid以及PL/SQL特有类型（binary+integer,Boolean,%type,%rowtype,ref cursor,record,pls_integer）等

语法格式：

CREATE [OR REPLACE] 用户方案名.类型名称   //or replace 声明若存在同名的类型不会报错，并将创建新类型；

[AUDTHID {CURRENT_USER|DEFINER}]AS OBJEC//AUTHID表示必须使用创建时定义的CURRENT_USER（调用该方法的用户）和DEFINER（该对象类型的所有者）权限集合，

(属性名 数据类型，

属性名……

）

例子:

CREATE [OR REPLACE] TYPE test_obj
AS OGJECT
(
item_id char(5),
price number(10,3)
);

将对象类型应用到表中：对象名对象类型名

CREATE TABLE SELL
(
name number(3) NOT NULL PRIMARY KEY,
info test_obj
);

向表中插入记录

INSERT INOT SELL(name,info）

VALUES(1,test_obj('002,32.5));

5.数据类型转换

TO_CHAR(需要转换的suju）:转nember和date
TO_DATE：转char
TO_NUMBER：转char
存在某些自动转换（例如number转varchar2，数值转字符）但是为了可读性还是写出来

例子

DECLARE
xh varchar2（5）；
begin 
      select TO_CHAR(MAX(学号)) INTO xh FROM xsb;//将学号赋值给xh，xh是字符，学号是数值
END;

四.PL/SQL基本程序结构和语句

顺序结构和控制结构（条件循环和循环结构）

1.程序块

pl/sql是块结构的语言，组成单元是逻辑块，一个程序块分为声明（DECLARE变量，常量的数据类型和初始值），执行（BEGIN END可执行语句），异常处理（EXCEPTION可选，处理异常和错误）。

需要输出结果看反馈（即printf一下）：在declare前用SET SERVEROUTPUT ON;(打开输出缓冲），就可以在执行语句中DBMS_OUTPUT.PUT_LINK('我要输出的文字'）

SET SERVEROUTPUT ON;
DECLARE
   a number:=1;
BEGIN
a:=a+1;
DBMS_OUTPUT.PUT_LINK('和为：'||TO_CHAR(a));
END;

结果为：和为2

2.条件结构：

IF 条件表达式 THEN

语句;

[

ELSE

语句

]

END IF;

if中嵌套if可以用ELSIF 条件表达式 THEN，或IF;

3.循环结构：

①LOOP ENDLOOP;型

LOOP

循环体

IF 条件表达式 THEN

EXIT;//退出循环

END IF;

END LOOP;

可以将IF语句换为：

EXIT WHERE 条件表达式;

也可以换为将WHILE放在LOOP前面：WHILE 条件表达式→这种先测试条件再执行，而前面两个先执行一次在判定条件（参考C语言do while）

②FOR IN LOOP END型

FOR 循环变量名 IN 变量初值..变量终值//例如，变量名cout，初值2，终值5；

LOOP

循环体

END LOOP;

4.选择和跳转语句

①case语句

②GOTO语句

5.异常

①系统自带预定义异常（如除以0，）

②用户定义异常

调用异常：RAISR 异常名

……

EXCEPTION //定义异常

WHEN 异常名 THEN //异常名这可以使用OR语句;类似于if的else，这里用OTHERS

DBMS_OUTPUT.PUT_LINK('异常提示的文字和变量（类似于printf），这里常用||来当做java中的连接符+号')

WHEN 异常名2……

五.系统内置函数

较为简单，类似C语言用法而且不用声明库函数

六.用户自定义函数

有三种模式：

IN模式：该参数只能是输入给函数的参数（即只能出现在:=的右边）
OUT模式：该参数在函数中只能被赋值（即只能出现在:=的左边）
IN OUT模式：随意

创建函数：

CREATE [OR REPLACE] FUNCTION 函数名
(
 参数名1，参数类型（就是in，out，in out），数据类型，       //类似于函数的参数
参数名2，……，数据类型
）
RETURN 返回值类型
IS或AS
变量名 数据类型;
BEGIN
   函数体
[RETURN 变量名]        //和C语言的函数一样，有无返回都可以

END 函数名




/*实际例子*/
CTEATE OR REPLACE FUNCTION average
(
cnum IN char
)
RETURN number;
AS
  avger number;
BEGIN
 SELECT AVG(成绩) INTO avger
 FROM CJB
WHERE 课程号=cnum
GROUP BY 课程号;
RETURN(avger);
END average;

如何使用函数

类似C语言：变量名:=函数名（参数）；

删除函数：DROP FUNCTION 函数名；

七.游标

实现对结果集进行逐行处理的能力的机制（因为许多语言c，java不能将结果集作为一个单元来处理）；作为一种特殊指针与查询结果相联系，指向结果集的任意位置来方便操作使用游标还可以在查询数据同时处理数据；游标分显式和隐式，如下：

1，显示游标：

DECLARE CURSOR 游标名

SELECT语句；

（1）声明后使用游标必须先打开：

OPEN 游标名；

(2)使用%ROWCOUNT返回最近指向的行号

（3）读取数据：FETCH 游标名 [INTO 变量名1，变量名2……]→每返回一个数据会自动指向下一个数据行

综合例子

DECLARE
v_xh char(4);
v_zxf number(2);
CURSOR XS_CUR
IS
SELECT 学号，总学分
     FROM XSB
     WHERE 专业='计算机';
BEGIN
   OPEN CURSOR
  FETCH XS_CURSOR INTO v_xh,v_zxf;
  WHERE XS_CUR%FOUND       //检验当前游标指向是否有效作为循环条件
  LOOP
      DBMS_OUTPUT.PUT_LINK(v_zh+v_zxf);
      FETCH XS_CUR INTO v_xh,v_zxf;
  END LOOP;
  CLOSE XS CUR;//关闭游标即关闭它的select操作，释放内存
END;

注：不清楚游标是否打开可使用&ISOPEN来检测；根据返回true或false来操作（怎么根据操作呢？当做if语句的判断条件呀）

2.隐式游标：使用SELECT会隐含处理游标定义，不需要声明和打开关闭（隐式游标必须用INTO）。

举例：

CREATE OR REPLACE PROCEDURE CX_XM
(
in_xh IN char,
out_xm OUT varchar2
)
AS
BEGIN
SELECT 姓名 INTO out_xm      /*隐式游标必须用*/
  FROM XSB
  WHERE 学号=in_xh AND ROWNUM=1;
DBMS_OUTPUT.PUT_LINK(out_xm);
END CX_XM;

注：

①每个隐式游标必须有一个INTO

②隐式游标一次仅能返回一行数据，使用时必须检查异常，常见异常有：NO_DATA_FOUND,TOO_MANY_ROWS

③为确保返回一行，用ROWUND=1来限定

④为了结构化更高，最好用显式游标

3.游标FOR循环：

此结合使游标使用更简明（不需要打开关闭，取数据，测试数据等重复操作）

FOR 变量名 IN 游标名(参数1，参数……)LOOP

//括号中是应用程序传递给游标的参数，变量名是隐含声明的记录变量

语句段

END LOOP;

DECLARE
 v_xh char(5);
 v_kch char(3);
 v_cj number(4,2);
CURSOR kc_cur
IS
    SELECT 学号,课程号,成绩
      FROM CJB;
BEGIN
 FOR kc_cur_rec IN kc_cur LOOP
v_xh:=kc_cur_rec.学号;
v_kch:=kc_cur_rec.课程号;
v_cj:=kc_cur_rec.成绩;
IF v_cj>90 THEN
   INSERT INTO tempCj VALUES(v_xh,v_kch,v_cj);
END IF;
END LOOP;
END;



//while 条件 LOOP 语句 EDN;版
DECALRE
v_xh char(6);
v_kch char(3);
v_cj number(4,2);
CURSOR kc_cur
IS
  SELECT 学号，课程号，成绩
   FROM CJB;
BEGIN
  OPEN kc_cur
FETCH kc_cur INTO v_xh,v_kch,v_cj;
  WHERE kc_cur%FOUND LOOP
     IF ……
END LOOP;
CLOSE kc_cur；
END;

4.游标变量：
运行时与不同语句关联，常用于处理多行的查询结果集，打开时才能确定对应的查询；

（1）定义REF CURSOR类型（位于DECLARE中）

TYPE REF CURSOR的类型名 IS CURSOR [RETURN 返回类型];//可以是强或弱类型，弱类型没有返回；

（2）声明游标变量（可定义用户自定义的RECORO类型，可声明游标变量作为函数和过程的参数）

（3）控制游标变量

DECLARE TYPE kc_cj IS REF CURSOR RETURN XSB%ROWTYPE;
xscur xs_cur;

八.包（package类似c++库函数）：

利用包可以将过程和函数安排在逻辑分组中；包为两个分离部件：包说明（包头，规范）和包体（主体）；储存在数据字典中；本质上就是一个命名的声明部分

包和过程与函数区别：包仅能存储在非本地的数据库中。除了运行相关的对象结合成组，包与依赖性较强的存储子程序相比其所受限制少，另外包效率高（建议参考类）

1.创建包：

（1）包头：

CREATE [OR REPLACE] PACKAGE 包名

IS |AS

程序序列；

删除包体和包头：DROP PACKAGE 包名

（2）包体：(package后面加一个BODY)包体是独立于包头的数据字典对象，只能在包头完成编译后才编译，带有实现包头描述的前项子程序的代码段；还可以包括具有包体全局属性的附加声明部分但对说明部分不可见；

删除包体：DROP PACKAGE 包名；

2.包的初始化：

3.包内部过程和函数的重载

3.内置包：

九.集合：

类似高级语言数组，处理多行数据必须的结构体；集合就是列表，可有序无序；有序的索引为下标，无序的为唯一性的标识符（数字，哈希值，字符串名）

三种集合类型：

1.联合数组：

TYPE 联合数组名

TABLE OF 数据类型 INDEX BY BINARY_INTEGER;

e.g.:

TYPE xs_name
IS 
TABLE OF XSB.姓名%TYPE
  INDEX BY BINARY_INTEGER;   //index的BINARY_INTEGER范围为-2147483647到+2147483647，所以v_naem（-199）是对的
v_name xs_name;



//赋值
BEGIN
 v_name(1)='lala';
v_name(2)='lili';
END;

注：调用时（例如输出）前一定要先赋值

2.嵌套表：没有INDEX BY BINARY_INTEGER;而是对其的初始化（没有初始化的话会自动初始化为NULL且是只读的不能添加元素了，联合数组可以添加）

（1）元素有序性：

3.可变数组（可变指的是在指明一个max后，元素个数可在max之下变化；其在内存存储和C语言数组一样是连续的）

4.集合的属性和方法：COUNT,DELETE,EXISTS,EXTEND,LIMIT,FIRST/LAST,NEXT/PRIOR,TRIM;

萨菲罗

关注

21
点赞
踩
23

收藏

觉得还不错? 一键收藏
0
评论
第六章：PL/SQL(Oracle对SQL的拓展）

1.组成：PL/SQL是面向过程语言和SQL语言的结合（从名字可看出p是指procedure-oriented）所以它扩充了面向过程的结构：变量，类型，控制语句，过程和函数，对象类型和方法等。IN模式：该参数只能是输入给函数的参数（即只能出现在:=的右边）OUT模式：该参数在函数中只能被赋值（即只能出现在:=的左边）IN OUT模式：随意CREATE [OR REPLACE] FUNCTION 函数名参数名1，参数类型（就是in，out，in out），数据类型， //类似于函数的参数。
复制链接

扫一扫