postgresql 性能指标 plsql 性能分析

转载

云端小仙童 2024-05-16 04:38:42

文章标签 postgresql 性能指标 sql 数据库 oracle SQL 文章分类 云原生云计算

PL/SQL介绍

【优点】

PL/SQL是ORACLE在标准SQL语言上的过程性扩张，允许嵌入SQL语句，允许定义常量和变量，允许过程语言结果，允许使用异常处理ORACLE错误。
PL/SQL能提高程序的运行性能，将PL/SQL块内嵌到应用程序中，最大优点可以降低网络开销，提高应用程序的性能。
PL/SQL提供模块化的程序设计功能，简化应用程序的开发和维护工作，可以将企业规则和商业逻辑集成到PL/SQL程序中,包括存储过程，函数，包中，然后在应用程序中调用相应的功能。
具有过程语言控制机构，PL/SQL允许使用条件分支语句和循环语句。
具有良好的兼容性，ORACLE运行的任何平台都可以使用PL/SQL块。

【PL/SQL与网络传输】

postgresql 性能指标 plsql 性能分析_postgresql 性能指标

PL/SQL：在执行期间，将所有SQL语句传递给SQL语句执行器组件执行

正常使用SQL：需要逐条发送SQL语句
使用PL/SQL：打包传输，可以减少网络流量【降低了可重用性（移植性不好）】

PL/SQL程序结构

PL/SQL程序都是以块(block)为基本单位

DECLARE
	变量、常量、游标、自定义异常
BEGIN
	SQL语句
	PL/SQL控制语句
EXCEPTION
	错误发生时执行的动作
END;  /* 注意END之后的分号 !!! */

整个PL/SQL分为三个部分
声明部分（DECLARE开头）
执行部分（BEGIN开头）
异常处理部分（EXCEPTION开头）
其中，执行部分是必须的，其他两个部分可选

【eg】

DECLARE
	v_id integer;
	v_name varchar(20);
	v_v1 constant number := 90;
	cursor c_cursor is select * from tablename where id = 0;
BEGIN
	open c_cursor;  /* 打开游标 */
	
	loop
		fetch c_cursor into v_id, v_name;  /* 从游标获取数据 */
		exit when c_cursor%notfound;
	end loop;
	
	close c_cursor;  /* 关闭游标 */
	dbms_output.PUT_LINE(v_name);
EXCEPTION
	when no_data_found then
		dbms.output.PUT_LINT("no data");
END;

变量

无论需要什么类型的变量，能够在BEGIN部分中使用它之前，都必须在块的DECLARE段中声明这些变量

【声明变量必须提供变量名称和数据类型】

变量名称遵循以下约定：

以一个字母字符开头
最多包含30个字符
可以包含大写和小写字母、数字以及_、$、#以及其他特殊字符

基本数据类型

BINARY_INTEGER
NUMBER[(precision,scale)]
CHAR[(maximum_length)]
LONG
LONG RAW
VARCHAR2(maximum_length)
DATE
BOOLEAN

声明变量

变量名 [constant] 类型 [not null] [:=value]

注意：在声明部分，每一行只能有一个变量声明

【错误实例】
DECLARE v_firstname, v_lastname varchar2(20);
【正确示范】
DECLARE v_firstname varchar2(20); v_lastname varchar2(20);

变量的初始化

DECLARE
	v_counter number := 45;
	v_numberseat number default 45;

NOT NULL和CONSTANT

DECLARE
	v_num CONSTANT number(5) := 100;
	/* 如果使用了 CONSTANT
	 * 则变量应被初始化，且以后不能改变它的值
     */
    v_temp number(5) NOT NULL := 50;
    /* 如果使用了 NOT NULL
     * 就应该给该变量赋初始值，否则会报错
     */

使用DBMS_OUTPUT

SET SERVEROUTPUT ON

DECLARE
	v_date date := sysdate;
BEGIN
	dbms_output.put_line(v_date);
END;

SET SERVEROUTPUT ON

DECLARE
	v_num number(3);
BEGIN
	SELECT COUNT(*) INTO v_num FROM TestTable;
	dbms_output.put_line(v_num);
END;

PL/SQL复合数据类型

PL/SQL提供了复合的数据类型，允许创建一个变量，可以将不同数据类型的多个值存储为一个单独的单元

复合数据类型有两种

复合数据类型：主要是记录数据类型和记录表类型
集合数据类型：主要是按索引组织表类型、嵌套表类型和数组类型

这两者的区别是：复合变量包含多种数据类型，而集合则包含相同数据类型的多个值

复合数据类型

记录数据类型

记录数据类型和表的行结构非常相似
一行数据通常包括许多不同的字段，就一个记录变量而言，必须使用TYPE语句来创建自己的数据类型

使用TYPE语句来创建自己的数据类型

TYPE type_name is record (
	field1 type1 [not null] := expr1,
    field2 type2 [not null] := expr2,
    ...
);

【使用】

DECLARE
	TYPE t_studentRecord is RECORD (
    	studentId number(5),
        firstname varchar(20),
        lastname varchar(20)
    );
    
    v_studentInfo t_studentRecord;
BEGIN
	v_studentInfo.firstname := 'zlun';  /* 引用记录中的一个字段的方法 */

【记录赋值】

如果要将一个记录赋值给另一个记录，则这两个记录要类型完全相同
直接一点就是类型的名字一样咯，像下面那样也是不能够赋值的
DECLARE TYPE t_type1 is RECORD ( filed1 number, filed2 varchar2(5) ); TYPE t_type2 is RECORD ( filed1 number, filed2 varchar2(5) ); v_rec1 t_type1; v_rec2 t_type2; BEGIN v.rec1 := v_rec2; /* 非法！ */ v_rec1.field1 := v_rec2.field1; /* 合法 */ v_rec1.field2 := v_rec2.field2; /* 合法 */

此外，也可以使用SELECT语句对记录进行赋值，记录中的字段应该与查询选择结果列表的字段相匹配

DECLARE
	TYPE t_studentRec is RECORD (
    	firstname students.first_name%type,
        lastname students.last_name%type,
        major students.major%type
    );
    
    v_student t_studentRec;
BEGIN
	SELECT first_name, last_name, major INTO v_student
		FROM students
		WHERE id = 100;
END;

/* SELECT语句中的列顺序与在记录变量中的字段顺序一致 */

【%type和%rowtype】

%type用于变量类型的声明中
可用于保证定义的变量的数据类型和长度与数据库表中的列保持一致

DECLARE
	v_vxm tread.vxm%type;

%rowtype
定义记录类型为【表名%rowtype】，则返回和数据表一样类型的记录行

DECLARE
	v_roomRecord rooms%rowtype;
	
	/* v_roomRecord将与表rooms有相同的结构 */
	/* 如果表的定义变了，则记录的类型也会随之改变 */

记录表类型

记录表是另外一种复合数据类型，为什么需要记录表呢？
可以看看以下的例子

DECLARE
	v_name varchar2(20);
BEGIN
	SELECT username INTO v_name FROM students;  /* 这里可能会报错噢 */
	dbms_output.put_line(v_name);

如果查询语句的**返回值有多个**，则会出现错误"exact fetch returns more than requested numbers of rows"，因为INTO子句中的变量不能处理多行数据

那么就可以使用一个能够存储多个行以及多个字段的变量——记录表类型

DECLARE
	TYPE t_studentRec IS TABLE OF students%rowtype INDEX BY binary_integer;
	
	tbl_studentItems t_studentRec;
	
BEGIN
	tbl_studentItems(1).sid := 1;
	...
END;

集合数据类型

“集合”是一个已排序的元素组，它允许将相同数据类型的多个值作为一个单独的单元来处理
“集合”有三种：按索引组织的表、数组、嵌套表

按索引组织的表

它是一种可以处理多行数据但是只能处理一个字段的变量。
除了只包含单独的一列数据之外，按索引组织的表在本质上与记录表是相同的
声明按索引组织的表的数据类型与声明记录表数据类型非常相似。首先创建一种数据类型（包含一列的表结构和一个引用行的索引），其次，使用这种数据类型声明一个表

DECLARE
	TYPE t_roast IS TABLE OF number  /* num是一种普通数据类型 而不是记录表那样的一个表 */
		INDEX BY binary_integer;
	
	tbl_roast t_roast;
BEGIN
	tbl_roast(1) := 6.22;
	tbl_roast(2) := 6.13;
	tbl_roast(3) := 6.27;
	/*
	 * 因为按索引组织的表变量中只有一个单独的列是可用的，所以在将一个值
	 * 输入到这种变量中时，只需要指出行号即可
	 */

注意

按索引组织的表并不是数据库中实际存在的表。它们是一些变量，用来存储和操作PL/SQL程序中的数据。因此，不能在这些表上执行SQL命令
按索引组织的表具有“表属性”。“表属性”是可以和表变量一起使用的函数，它允许更好地处理表的值
【“表属性”就是处理表的时候可以用的一些变量，虽然前面说了按索引组织的表并不是数据库中实际存在的表，但是依然可以使用这些变量】

属性名称	数据类型	说明
count	number	表中的行数
delete	无	从表中删除一行
exists	boolean	如果指定的行存在，则为true
first	binary_integer	表中第一行的索引
last	binary_integer	表中最后一行的索引
next	binary_integer	在指定行之后，表中下一行的索引
prior	binary_integer	在指定行之后，表中上一行的索引

DECLARE
	TYPE t_roast IS TABLE OF number INDEX BY binary_integer;
	
	tbl_roast t_roast;
	lv_avg_num number;
BEGIN
	tbl_roast(1) := 6.22;
	tbl_roast(2) := 6.13;
	tbl_roast(3) := 6.27;
	
	lv_avg_num := tbl_roast.count;
	dbms_output.put_line(lv_avg_num);
END;

嵌套表

注意这个是【表table】，前面的都是【type】
嵌套表是表中之表。
一个嵌套表是某些行的集合，它在主表中表示为其中的一列。对主表中的每一条记录，嵌套表可以包含多个行

【举个例子】

假设有一个关于动物饲养员的表，希望其中具有他们饲养的动物的信息。用一个嵌套表，就可以在同一个表中存储饲养员和其饲养的全部动物的信息

创建类型animal_ty【这个用来保存动物信息；此类型中，对于每个动物都包含有一个记录，记载了其品种、名称和出生日期信息】

CREATE TYPE animal_ty AS OBJECT (
	breed varchar2(25), 
	name varchar2(25), 
	birthdate date
);

创建animals_nt【此类型将用作一个嵌套表的基础类型】

CREATE TYPE animals_nt as table of animal_ty;

创建表breeder【饲养员的信息表】

create table breeder (
    breedername varchar2(25),
	animals animals_nt
)
nested table animals store as animals_nt_tab;

向嵌套表中插入记录

insert into breeder values(
    'mary',
    animals_nt(animal_ty('dog','butch','970304'),
               animal_ty('dog','rover','970405'),
               animal_ty('dog','julio','970506')));
insert into breeder values(
    'jane',
    animals_nt(animal_ty('cat','an','970304'),
               animal_ty('cat','jame','970405'),
               animal_ty('cat','killer','970808')));

查询嵌套表

select name,birthdate from 
	table(select animals from breeder); 

select name,birthdate from 
	table(select animals from breeder where breedername=’mary’)
	where name=’dog’;

可变数组

可变数组与嵌套表相似，也是一种集合。
一个可变数组是对象的一个集合，其中每个对象都具有相同的数据类型。
可变数组的大小由创建时决定。
在表中建立可变数组后，可变数组在主表中作为一个列对待。
从概念上讲，可变数组是一个限制了行集合的嵌套表

创建类型comm_info

CREATE TYPE comm_info AS OBJECT ( /*此类型为通讯方式的集合*/
	no number(3),  /*通讯类型号*/
	comm_type varchar2(20),  /*通讯类型*/
	comm_no varchar2(30)  /*号码*/
);

创建可变数组comm_info_list

CREATE TYPE comm_info_list AS  VARRAY(50) OF comm_info;

创建表

create table user_info (
    user_id number(6),  /*用户ID号*/
    user_name varchar2(20),  /*用户名称*/
    user_comm comm_info_list  /*与用户联系的通讯方式*/
);

向可变数组插入记录

insert into user_info values(1,'mary',
	comm_info_list(
        comm_info(1,'手机','13651401919'),
        comm_info(2,'呼机','1281234567')));

insert into user_info values(2,'carl',
	comm_info_list(
        comm_info(1,'手机','13901018888'),
        comm_info(2,'呼机','1281234567')));

查询可变数组

select user_comm from user_info 
	where user_id=1; 

select comm_type,comm_no 
	from table(
    	select user_comm from user_info where user_id=1)
	where no=1;

绑定变量(主机变量)

变量名称前加上冒号告诉PL/SQL这是一个主机变量，如：
where idshopper = :g_shopper and orderplaced = 0;

使用variable命令来创建主机变量

注意：variable命令创建的主机变量是SQL*PLUS变量，不是PL/SQL变量

主机变量的类型是number、char、varchar2【没有date和boolean类型】

# 在 SQL*PLUS中输入
> variable g_shopper number  #注意末尾没有 ; 号

/* 在PL/SQL中使用 */
BEGIN
	:g_shopper := 25;
END;

变量的作用域和可见性

变量的作用域是可以访问该变量的程序部分
PL/SQL变量的作用域：从声明部分开始到块的结束

postgresql 性能指标 plsql 性能分析_oracle_02

【当一个变量超出了作用域，PL/SQL将释放变量的空间】

注意，当在内部的一个块中，又定义了一个同名变量时，将会在块内暂时覆盖外部变量

变量的可见性是可以访问变量而不必保证该变量的引用是否有效的程序部分。可见性始终在作用域内。如果一个变量超出了作用域范围，就不可见了

DECLARE
	v_flag boolean;
	v_seen char;
BEGIN
	/* (1) */
	/* 此处 [v_flag] 和 [char的v_seen] 可见 */
	DECLARE
		v_seen boolean;
		v_date date;
	BEGIN
		/* (2) */
		/* 此处 [v_flag] 和 [boolean的v_seen] , [v_date] 可见 */
		/* 注意，此时仍在 [char的v_seen] 的作用域内，但是不可见 */
	END;
	/* (3) */
	/* 此处 [v_flag] 和 [char的v_seen] 可见 */
END;

【但是，如果一个变量在作用域内，但不可见，可以使用标签加以引用变量】

<<outer>>
DECLARE
	v_flag boolean;
	v_seen char;
BEGIN
	/* (1) */
	/* 此处 [v_flag] 和 [char的v_seen] 可见 */
	DECLARE
		v_seen boolean;
		v_date date;
	BEGIN
		/* (2) */
		/* 此处 [v_flag] 和 [boolean的v_seen] , [v_date] 可见 */
		/* 可通过 [outer.v_seen] 引用 [char的v_seen] */
	END;
	/* (3) */
	/* 此处 [v_flag] 和 [char的v_seen] 可见 */
END;

PL/SQL表达式

赋值

[变量] := [表达式]
/* 在一个特定的PL/SQL语句中仅能够有一个赋值。下面的例子是错误的 */
v_val1 := v_val2 := v_val3 := 0;  /* 不能这样！ */

字符表达式

PL/SQL使用||进行字符串拼接

SELECT '工号为'||FNumber||'的员工姓名为'||FName 
FROM T_Employee WHERE FName IS NOT NULL

运算符的优先级

postgresql 性能指标 plsql 性能分析_sql_03

布尔表达式

null：

true and null的返回值为null
仅当操作数是null时，is null才返回true
不能使用关系运算符对null值进行检测

【一些注意事项】

不要在实数之间比较相等，因为实数作为近似值存储，这个比较值可能不为true

PL/SQL的程序控制结构

条件结构

If [condition] then
	statement1
ELSE
	statement2
END IF;

循环结构

DECLARE
	v_count binary_integer := 1
	
/* LOOP循环 */
BEGIN
	LOOP
		insert into tmp_table(num_col) value (v_count);
		
		v_count := v_count + 1;
		EXIT WHEN v_count > 50;
	END LOOP;
END;

/* FOR循环 */
BEGIN
	FOR v_count in 1..50 LOOP
		insert into tmp_table(num_col) value (v_count);
	END LOOP;
END;

顺序结构

GOTO

SQL基本命令

PL/SQL使用的数据库操作语言是基于SQL的，在PL/SQL中也可以使用SQL

数据定义语言（DDL）

CREATE、DROP、GRANT、REVOKE、…

数据操纵语言（DML）

UPDATE、INSERT、DELETE

数据控制语言（DCL）

COMMIT、ROLLBACK、SAVEPOINT、ALTER

其他

SYSTEM、CONNECT、ALLOCATE

游标

用游标来指代一个DML SQL操作返回的结果集
当一个对数据库的查询操作返回一组结果集时，用游标来标注这组结果集，以后通过对游标的操作来获取结果集中的数据信息

cursor [cursor_name] is [SQL语句]

cursor c_emp is SELECT * FROM emp WHERE emp_id = 3;

使用游标操作结果集的步骤

打开游标
使用fetch语句将游标里的数据取出
关闭游标

DECLARE
	v_firstname varchar2(20);
	v_lastname varchar2(20);
	cursor c_student is SELECT firstname, lastname FROM student;
BEGIN
	OPEN c_student;  /* 打开游标 */
	LOOP
		FETCH c_student INTO v_firstname, v_lastname /* 使用fetch语句将游标里的数据取出 */
		
		EXIT WHEN c_student%NOTFOUND;
	END LOOP;
	CLOSE c_student;  /* 关闭游标 */
END;

过程和函数

PL/SQL中的过程和函数与其他语言的过程和函数的概念一样，都是为了执行一定的任务而组合在一起的语句
过程无返回值
函数有返回值
与匿名块（就是之前普通的PL/SQL块）不同的地方是：它可以**存储到数据库的数据字典**中，以便重用

/* 过程 */
CREATE OR REPLACE PROCEDURE [proc_name]([parameter_list]) AS
	[PL/SQL语句块]

/* 函数 */
CREATE OR REPLACE FUNCTION [func_name]([parameter_list]) RETURN [return_value] AS
	[PL/SQL语句块]

【创建一个过程】

CREATE OR REPLACE PROCEDURE newdept (
	p_deptno dept.deptno%TYPE,  --部门编号
    p_dname dept.dname%TYPE,  --部门名称
    p_loc dept.loc%TYPE  --位置
)
AS  --声明区(DECLARE) 类型定义、游标、常量、变量、异常等 这些是本地的，程序退出时会自动销毁
	v_deptcount NUMBER;  --保存是否存在员工编号
BEGIN
	SELECT COUNT(*) INTO v_deptcount
		FROM dept WHERE deptno = p_deptno;
	
	IF v_deptcount > 0 THEN
		raise_application_error(-20002, '出现了相同的员工记录')  --报个异常
	END IF;
	
	INSERT INFO dept(deptno, dname, loc)
		VALUES (p_deptno, p_dname, p_loc);  --插入记录
	COMMIT;  --提交事务
END newdept;

【调用一个过程】

BEGIN
	newdept(10, '教育部', '长沙');
EXCEPTION
	WHEN OTHERS THEN
		dbms_output.put_line('error: '||SQLERRM);  --PL/SQL使用||拼接字符串
END;

包

PL/SQL的过程和函数可以和变量与类型共同组成包，包由两部分组成
说明部分【主要是包的一些定义信息，不包含具体的代码实现部分】
类型、记录、变量、常量、异常定义、游标、子程序的声明
【在这部分声明的内容才能在包外被引用】
包体：声明的子程序的实现部分，包体的内容对于外部程序是不可见的

【说明部分】

CREATE [OR REPLACE] PACKAGE package_name AS
	...
END [package_name];

------------------------------------------------
CREATE OR REPLACE PACKAGE emp_action_pkg AS
	v_deptno NUMBER(3) := 20;  --包公开的变量
	PROCEDURE newdept(   --定义一个增加新员工的过程
        p_deptno dept.deptno%TYPE,  --部门编号
    	p_dname dept.dname%TYPE,  --部门名称
    	p_loc dept.loc%TYPE  --位置
    );
    FUNCTION getSalary(  --定义一个根据员工id获取薪资的函数
        p_empno emp.empno%TYPE
    ) RETURN NUMBER;
END emp_action_pkg;

【包体】

CREATE [OR REPLACE] PACKAGE BODY package_name AS
	...
END [package_name];

------------------------------------------------
CREATE OR REPLACE PACKAGE BODY emp_action_pkg AS
	PROCEDURE newdept(   --这个在声明部分有，所以是公开的，在包的外部可以调用
        p_deptno dept.deptno%TYPE,
    	p_dname dept.dname%TYPE,
    	p_loc dept.loc%TYPE
    )
    AS
    	...
    BEGIN
    	...
    END newdept;
    
    FUNCTION getSalary(  --这个在声明部分有，所以是公开的，在包的外部可以调用
        p_empno emp.empno%TYPE
    ) RETURN NUMBER
    AS
    	...
    BEGIN
    	...
    END getSalary;
    
    FUNCTION checkDeptno(  --没有在声明部分声明，私有的，只能在包体内被引用
        p_deptno dept.deptno%TYPE
    ) RETURN NUMBER
    AS
    	...
    BEGIN
    	...
    END checkSomething;
END emp_action_pkg;

【调用包组件】

通过包名.元素名的形式调用
当包第一次被调用的时候，将进行初始化
比如将包从硬盘调到内存中来
放到系统全局工作区的共享缓冲池
包的运行状态则会被放入用户全局区的会话存储区中【这样保证每个调用包的会话都拥有包的运行副本】
当会话结束时，包的运行状态才会被释放【包具有会话级的作用域——可以跨多个事务存储数据】

BEGIN
	emp_action_pkg.v_deptno := 50;
	emp_action_pkg.newdept(45, '教育部', '长沙');
END;

动态SQL

借助于动态SQL，可以在运行期间构造并执行SQL语句【SQL语句是动态拼接而成的】
使用动态SQL语句有两种方法
一种是使用DBMS_SQL包
另一种是使用Oracle8i支持的本地动态SQL

【举个例子——使用DBMS_SQL包】

DECLARE
	table_name VARCHAR2(20);
	sql_query VARCHAR2(100);
	v_cursor binary_integer;
	v_returncode binary_integer;
BEGIN
	table_name := 'dept';
	sql_query := 'DROP TABLE ' || table_name;
	
	-- open the cursor
	v_cursor := dbms_sql.open_cursor;
	-- parse and execute the statement
	dbms_sql.parse(v_cursor, sql_query, dbms_sql.native);
	v_returncode := dbms_sql.execute(v_cursor);
	-- close the cursor
	dbms_sql.close_cursor(v_cursor);
END;

【举个例子——使用Oracle8i支持的本地动态SQL】

DECLARE
	table_name VARCHAR2(20);
	sql_query VARCHAR2(100);
	l_return PLS_INTEGER;
BEGIN
	table_name := 'dept';
	sql_query := 'SELECT COUNT(*) FROM ' || table_name;
	EXECUTE IMMEDIATE sql_query  --动态执行SQL语句
		INTO l_return;  --返回的结果值
END;

--当然也可以执行DDL语句
DECLARE
	table_name VARCHAR2(20);
	sql_query VARCHAR2(100);
BEGIN
	table_name := 'dept';
	sql_query := 'DROP TABLE ' || table_name;
	EXECUTE IMMEDIATE sql_query  --动态执行SQL语句
END;

-- EXECUTE IMMEDIATE 可以处理大部分DDL、DCL、DML语句
-- 但不能用EXECUTE IMMEDIATE 处理 多行查询语句(返回值有多行)

对象类型

对象类型由属性和方法组成，并可存储在数据库表中

CREATE OR REPLACE TYPE student AS OBJECT (
	id number(5),
    firstname varchar2(20),
    lastname varchar2(20),
    major varchar2(30),
    current_credits number(3),
    
    -- return the first and last names
    MEMBER FUNCTION formattedname RETURN VARCHAR2,
    PRAGMA RESTRICT_REFERENCES(formattedname, RNDS, WNDS, RNPS, WNPS),
    
    -- update the major...
    MEMBER PROCEDURE changemajor(p_newmajor in VARCHAR2),
    PRAGMA RESTRICT_REFERENCES(changemajor, RNDS, WNDS, RNPS, WNPS),
);


---------------------------------------
CREATE OR REPLACE TYPE BODY student AS
	MEMBER FUNCTION formattedname RETURN VARCHAR2 IS
	BEGIN
		...
	END formattedname;
	
	...
END;

3-1 PLSQL控制语句

条件控制

IF-THEN

IF condition THEN
	...
END IF;

IF-THEN-ELSE

IF condition THEN
	...
ELSE
	...
END IF;

IF-THEN-ELSIF

IF condition1 THEN
	...
ELSIF condition2 THEN
	...
[ELSE
	...]
END IF;

----------------------------------
-- null条件
-- 当condition为true时，执行块后的语句
-- 当condition为false或null时，跳过块或执行ELSE
-- 当condition中有null值进行比较时，condition = null (只能用 is null 对null处理)

CASE

CASE selector
	WHEN expression1 THEN ...;
	WHEN expression2 THEN ...;
	...
	[ELSE ...;]
END CASE;

CASE
	WHEN condition1 THEN ...;
	WHEN condition2 THEN ...;
	...
	[ELSE ...;]
END CASE;

循环结构

LOOP

LOOP-EXIT-END

LOOP
	...
	IF condition THEN EXIT;
END LOOP;

LOOP-EXIT-WHEN-END

LOOP
	...
	EXIT WHEN condition;
END LOOP;

WHILE-LOOP

WHILE condition LOOP
	...
END LOOP;

FOR

FOR loop_counter IN [REVERSE] low_bound..high_bound LOOP
	...
END LOOP;

注意：
FOR循环的循环索引被隐式声明为BINARY_INTEGER【loop_counter】
循环索引【loop_counter】在循环前**没有必要进行声明**
如果在循环之前进行了声明，那么循环索引将会隐蔽外层的声明

顺序结构

GOTO

GOTO label;
-- label 是用 <<>> 括起来定义的标号

DECLARE
	v_counter BINARY_INTEGER := 1;
BEGIN
	LOOP
		...
		v_counter := v_counter + 1;
		if V_counter > 50 THEN
			GOTO l_endtoloop;
		END IF;
	END LOOP;
	<<l_endtoloop>>
	...
END;

对GOTO的限制：
对于块、循环、IF，不能从外层转到内层，下面是一个非法的例子：
-- 一个非法的例子 BEGIN GOTO l_innerblock; BEGIN <<l_innerblock>> ... END; END;
使用GOTO语句从一个IF语句块转到另一个子句也是非法的，下面是一个非法的例子：
BEGIN IF x > 3 THEN ... GOTO <<lable>> ELSE <<lable>> ... END IF; END;
从一个异常处理块跳到当前执行块是非法的

NULL

如果想显示的指明不进行任何操作，可以使用NULL语句，它只是一个占位符

DECLARE
	v_num number := 7;
BEGIN
	IF v_num < 5 THEN
		INSERT INTO temp_table(char_col) VALUES('too small');
	ELSIF v_num < 10 THEN
		INSERT INTO temp_table(char_col) VALUES('just right');
	ELSE
		NULL;
	END IF;
END;

4-1 异常处理

异常处理处理的是运行时错误，异常分为预定义异常和用户自定义异常

声明异常情态

异常情态在块的声明部分进行声明，在块的执行部分进行触发，在块的异常部分进行处理

用户自定义类型异常情态

DECLARE
	e_toomany exception;

预定义型异常情态

预定义型异常情态可以直接使用，没有必要声明

Invalid_cursor：当执行非法的游标操作，会引发这个错误【如视图关闭已关闭游标】
Cursor_already_open：【试图打开已经打开的游标】，会引发这个错误
No_data_found：【当SELECT INTO没有返回行时】和【引用没有赋值过的PLSQL表的元素时】会引发这个错误
Too_many_rows：【当SELECT INTO返回多个行时】，会引发这个错误
Invalid_number：【当试图将字符串转换为数值失败时】，会引发这个错误

在过程性语句中引发Value_error错误

INSERT INTO students(id, firstname, secondname) VALUES('X', 'scott', 'smith');

-- 在id和'X'中引发了错误

Storage_error和Program_error：内部的异常情态，通常不会引发
Value_error：【当在过程性语句中发生了算术、转换、截尾或限制性错误时】会引发这个错误

如果是在SQL语句中发生错误，会引发Invalid_number
Value_error可能是一条赋值语句或者SELECT INTO语句执行的结果

-- 赋值语句引发的Value_error
DECLARE
	v_var varchar2(3);
BEGIN
	v_var = 'ABCD';
END;

-- SELECT INTO语句引发的Value_error
DECLARE
	v_var number;
BEGIN
	SELECT id INTO v_var FROM students WHERE lastname='smith';
END;

Rowtype_mismatch：当宿主变量和PLSQL游标变量不匹配时，会引发这个异常情态

触发异常情态

当与预定义的异常情态错误发生时，就会触发该异常情态。
用户定义的异常情态由raise语句触发【当然，预定义的异常情态也可以用raise引发】

DECLARE
	e_test exception;
	v_a number(3);
	v_b number(3);
BEGIN
	SELECT a, b INTO v_a, v_b FROM temp_table;
	if a > b THEN
		RAISE e_test
	END IF;
END;

处理异常情态

DECLARE
	...
BEGIN
	...
EXCEPTION
	WHEN exception_name1 THEN
		...
	WHEN exception_name2 or exception_name3 THEN
		...
	WHEN others THEN
		...
END;

4-2 过程与函数

过程

匿名块（就是之前那种PLSQL语句块）不能存储在数据库中，不能从其他PLSQL块中进行调用
过程、函数、包和触发器都是带名块，可以存储在数据库中，可以在需要的任何地方运行
【对过程的调用不作为表达式的一部分】
【过程是一个PL/SQL块，带有声明部分、执行部分和异常部分，只有执行部分是必须的】

CREATE OR REPLACE PROCEDURE {过程名} AS 
	/* 声明部分 */
BEGIN
	/* 执行部分 */
EXCEPTION
	/* 异常部分 */
END {过程名};

创建过程是DDL语句，因此，在语句之后有一条隐含的commit语句
形参有三种模式【in、out、in out】，没有指定形参的模式时，形参缺省的模式是in

模式	说明
in	当调用过程时，实参的值被传递给形参。在该过程内部，形参被认为是只读的，不能被改变。过程结束时，实参的值不会被改变
out	在调用过程时，实参所拥有的任何值都会被忽略。在过程内部，形参被认为是只写的，只能进行赋值，不能从中读取数据。当过程结束时，形参的值被赋给实参
in out	这种模式是in 和out 的组合。在调用过程时，实参的值被赋给形参。在过程内部，形参可以被读出，也可以被赋值。当过程结束时，形参的值被赋给实参

如果过程没有参数，则在过程说明和调用时都不需要括号（此规则对函数同样适用）

【注意事项】

在调用一个过程中，不仅实参的值被传递给形参，而且对变量的限制也传递进去。因此**在过程声明中限制char，varchar2参数的长度，number参数的精度和刻度都是非法的**【在声明过程时不要限制精度，只要声明类型】

-- 这样是不对的
CREATE OR REPLACE PROCEDURE name1 (
	p_parameter1 in out varchar2(10),
    p_parameter2 in out number(3, 2)
) AS
BEGIN
	...
END name1;

-- 要改成这样  在过程的调用过程中形参的精度由传入的实参决定
CREATE OR REPLACE PROCEDURE name2 (
	p_parameter1 in out varchar2,
    p_parameter2 in out number
) AS
BEGIN
	...
END name2;

对形参唯一限制的方法是用％type。如果使用了％type对形参进行说明，而基准类型是受限的，则该限制将作用于形参而不是实参，在过程的处理中采用的也是形参的精度【要求采用足够精度的实参调用】

-- table studets.current_credits number(3)

调用过程时，传参的位置标识法和带名标识法

CREATE OR REPLACE PROCEDURE name (p_p1 boolean, p_p2 varchar2, p_p3 number) AS
BEGIN
	...
END name;

--- 位置标识法
DECLARE
	v_v1 boolean;
	v_v2 varchar2(10);
	v_v3 number(7, 6);
BEGIN
	name(v_v1, v_v2, v_v3);
END;
--- 带名标识法
DECLARE
	v_v1 boolean;
	v_v2 varchar2(10);
	v_v3 number(7, 6);
BEGIN
	name(p_p3=>v_v3, p_p1=>v_v1, p_p2=>v_v2);
END;
--- 位置标识法和带名标识法混合使用
DECLARE
	v_v1 boolean;
	v_v2 varchar2(10);
	v_v3 number(7, 6);
BEGIN
	name(v_v1, p_p3=>v_v3, p_p2=>v_v2);
END;

参数缺省值

-- 两种方式
CREATE OR REPLACE PROCEDURE name(
	p_p1 student.firstname%type,
    p_p2 student.major%type default 'economics'
) AS ...

CREATE OR REPLACE PROCEDURE name(
	p_p1 student.firstname%type,
    p_p2 student.major%type := 'economics'
) AS ...

注意：如果使用位置标识法，则所有使用缺省值的参数都必须位于参数表的末尾

函数

作为表达式的一部分使用

CREATE OR REPLACE FUNCTION {function_name} RETURN {value_type} AS
...
BEGIN
	...
	RETURN {value};
END {function_name};

-- 举个例子
CREATE OR REPLACE FUNCTION zlun RETURN boolean AS
v_returnvalue boolean;
BEGIN
	...
	RETURN v_returnvalue;
END {function_name};

RETURN也可以用在过程中，此时RETURN不需要参数，过程里在RETURN处则立即停止运行，将控制立即传递给调用环境，被声明为out和in out的形参值传递给实参

函数与过程相同的一些特性

函数可以通过out参数返回多个数值
函数代码拥有声明、执行和错误处理部分
函数可以接受缺省值
函数可以通过位置标识法或带名标识法进行调用

删除过程和函数

删除过程和函数意味着过程和函数从数据字典中删除

DROP PROCEDURE {procedure_name};
DROP FUNCTION {function_name};

DROP是一个DDL语句，因此在执行该语句前后都会隐含执行commit命令

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：python实现海康球机预置点的切换并抓图海康球机怎么固定画面

下一篇：xcode15编译发布ios xcode编译app

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯