PLSQL系列培训之三PLSQL块SQL优化幻灯说明稿.docx
- 文档编号:24276002
- 上传时间:2023-05-26
- 格式:DOCX
- 页数:27
- 大小:126.28KB
PLSQL系列培训之三PLSQL块SQL优化幻灯说明稿.docx
《PLSQL系列培训之三PLSQL块SQL优化幻灯说明稿.docx》由会员分享,可在线阅读,更多相关《PLSQL系列培训之三PLSQL块SQL优化幻灯说明稿.docx(27页珍藏版)》请在冰豆网上搜索。
PLSQL系列培训之三PLSQL块SQL优化幻灯说明稿
PL/SQL系列培训之三:
PLSQL块、SQL优化
PL/SQL块
PL/SQL
如果不使用PL/SQL,ORACLE一次只能处理一条SQL语句,这样在性能上会产生很大的开销。
1988年,ORACLE公司发布ORACLE6,PL/SQL诞生。
PL/SQL的全称是“ProceduralLanguageextensionstotheStructuredQueryLanguage”。
ORACLE通过PL/SQL对标准SQL进行了扩展并提供了一个完整的系统实现方案。
PL/SQL块及其结构
PL/SQL块(PL/SQL块)是PL/SQL代码分组的最小单位。
PL/SQL块由四个部分组成:
头部分,定义部分,可执行部分和异常处理部分。
头部分:
定义非匿名块的调用方式。
只有非匿名块需要这部分,匿名块没有此部分。
定义部分:
定义将在可执行部分中调用的所有变量、常量、游标和用户自定义的例外处理。
这部分可以没有。
可执行部分:
包括对数据库中进行操作的SQL语句,以及对块中进行组织、控制的PL/SQL语句。
这部分必须存在。
异常处理部分:
对可执行部分中的语句,在执行过程中出错或出现非正常现象时所做的相应处理。
这部分可以没有。
PL/SQL块的分类
PL/SQL块存在以下分类:
(1)匿名块:
没有命名的PL/SQL块
(2)嵌套块:
可以通过使用嵌套块来控制程序的边界和变量的使用域。
(3)函数
(4)存储过程
(5)触发器
(6)包
PLSQL块的执行
PL/SQL是一种“解释型”的语言。
PL/SQL编译器会将PL/SQL语句编译为机器代码,然后在一个称为PL/SQL运行时引擎的虚拟机上运行。
这个过程与JAVA的编译执行过程比较类似。
PL/SQLCOMPILER:
PL/SQL编译器,它的工作可以分为两个阶段。
前一阶段解析PLSQL语句,产生一个树型结构以及相关语法、语义信息(称为DIANA);
后一阶段进行编译,产生一个可在虚拟机上执行的PLSQL字节码。
如果编译成功,将字节码存放到一块共享内存区域中;否则,返回错误信息。
PL/SQLRUNTIMEENGINE
PL/SQL运行时引擎,也称PL/SQL虚拟机,简称PVM。
主要工作是执行PL/SQL字节码,根据情况去调用SQL引擎(注意这是另外一个概念!
)。
PL/SQL调用图例1:
PL/SQL调用图例2(含SQL语句):
图例中PL/SQL编译器拿到整个PL/SQL块之后,进行解析,然后发现一句SQL语句,接着就向SQLCOMPILER(SQL解析器)发送解析请求,SQL解析器首先检查SQL语句语法是否正确,然后去识别DUAL是一个表,dummy是这个表的一个列,此外还要进行权限方面的检查。
接着,就要将解析后的PL/SQL块编译为可执行的代码。
在这个过程中,SQL解析器会计算并选择一个效率最高的执行计划,然后SQL编译器将执行计划发送给SQL执行引擎并由SQL执行引擎去进行取数并返回给PL/SQL运行时引擎。
PL/SQL调用图例3(存储过程调用):
PL/SQL编译器开始编译,但是它可以在ORACLE数据字典里面找到SP对应的DIANA和字节码,因此编译器只需要解析并编译PL/SQL块本身而不需要解析或编译存储过程。
此外,一旦存储过程被调用,它的这些信息从磁盘中读取之后,ORACLE会将这些信息存放在一块称为LibraryCache的内存区域中,这样进一步提高了整个执行过程的效率。
存储过程
(1)关于RETURN
终止SP执行并将控制权返回调用者。
RETURN后面不需要跟任何表达式。
对于存储过程的可执行部分而言,RETURN不是必须的。
(2)关于参数
·参数定义
·参数的输入输出类型
·参数的默认值
·形式参数与实际参数
参数的定义
在存储过程中,我们将参数定义在头部分,而将变量定义在声明部分,除此之外,它们还存在两个方面的差异:
·参数定义存在输入输出的模式设置,而变量定义没有
·参数定义不像变量定义那么严格:
变量定义需要根据数据类型指定类型长度,而参数定义只需要定义数据类型。
变量定义:
DECLARE
v_item_codeNVARCHAR2(10);
v_item_nameNVARCHAR2(10);
参数定义:
CREATEORREPLACEPROCEDUREtest_sp(pi_idNVARCHAR2)
参数的输入输出类型
参数存在三种输入输出类型:
输入输出类型
传值/传引用
可读/可写
作用
IN(默认)
传引用
只可读
在存储过程等PLSQL模块中可以使用传进来的参数的值,而不能修改。
可以设置默认值。
OUT
传值
可写/写后可读
存储过程等PLSQL模块需要赋值给该参数。
事实上,在程序执行过程中,每次对OUT参数的赋值实际上是对一个OUT参数的本地内部拷贝进行赋值;当SP成功执行结束且控制权回到调用者,存放在本地内部拷贝中的值才会赋给OUT的实际参数。
注意事项:
(1)不能设置默认值;
(2)对应的实际参数必须为变量。
(3)如果模块中没有对该参数赋值,最终该OUT参数的值为NULL。
(4)如果程序执行过程中发生异常且异常没有处理,那么OUT参数对应的实际参数的值将会保持调用PLSQL模块之前的值。
INOUT
传值
可读也可写
存储过程等PLSQL模块中可以读取该参数的值,也可以修改该参数的值。
注意事项:
(1)不能设置默认值
(2)对应的实际参数必须为变量
(3)如果未对OUT参数赋值,对应的实际参数将保留原值(这与OUT参数不同)
(4)如果程序执行过程中发生异常且异常没有处理,与OUT参数一样,对应的实际参数将保留调用前的值
举例,测试四种情况:
(1)没有异常的情况
(2)无异常,但是未对OUT参数赋值的情况
(3)有异常的情况
(4)有异常,但异常处理的情况
CREATEORREPLACEPROCEDUREtest_sp(
pi_paraINNVARCHAR2,
po_paraOUTNVARCHAR2,
pio_paraINOUTNVARCHAR2
)
IS
tanjie_exceptionEXCEPTION;
BEGIN
NULL;
po_para:
='HELLOWORLD';
pio_para:
='HELLOWORLD';
RAISEtanjie_exception;
EXCEPTION
WHENOTHERSTHEN
--NULL;
RAISE;
END;
调用:
DECLARE
v_in_paraNVARCHAR2(20);
v_out_paraNVARCHAR2(20);
v_inout_paraNVARCHAR2(20);
BEGIN
v_in_para:
='HI';
v_out_para:
='HI';
v_inout_para:
='HI';
BEGIN
test_sp(v_in_para,v_out_para,v_inout_para);
EXCEPTION
WHENOTHERSTHEN
NULL;
END;
DBMS_OUTPUT.PUT_LINE('IN:
'||v_in_para||CHR(10)||
'OUT:
'||v_out_para||CHR(10)||
'OUT:
'||v_inout_para||CHR(10)
);
END;
参数的默认值
如上,可以对IN类型的参数指定默认值。
可以通过DEFAULT关键字或赋值操作符:
=进行默认参数的设置。
举例:
CREATEORREPLACEPROCEDUREtest_sp(
pi_aINNVARCHAR2:
='HELLOA',
pi_bINNVARCHAR2DEFAULT'HELLOB'
)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('pi_a:
'||pi_a||CHR(10)||
'pi_b:
'||pi_b||CHR(10)
);
END;
调用:
BEGIN
test_sp('HIA','HIB');
test_sp('HIA');
test_sp();
END;
形式参数和实际参数
定义在存储过程头中的参数定义列表为形式参数。
实际调用存储过程时传入的相应的值或表达式称为实际参数。
形式参数:
CREATEORREPLACEPROCEDUREtest_sp(
pi_idNVARCHAR2--formparameter
)
实际参数:
BEGIN
test_sp('001');--actualparameter
END;
形式参数与实际参数的对应存在两种方式:
·通过位置对应:
通过实际参数的顺序位置隐含地与形式参数进行对应;
·通过名称对应:
通过显式指定形式参数的名称来与实际参数进行对应;
对应语法:
formal_parameter_name=>argument_value
需要注意两点:
一是既然通过名称来对应,参数位置与顺序无关;
二是可以与位置对应方式混用,但是位置对应的必须放在名称对应的前面。
对应方式举例:
CREATEORREPLACEPROCEDUREtest_sp(
pi_person_idNVARCHAR2,
pi_dept_idNVARCHAR2
)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('ID:
'||pi_person_id||CHR(10)||
'DEPT:
'||pi_dept_id||CHR(10)
);
END;
调用:
BEGIN
--通过位置进行对应
test_sp('001','A');
--通过名称进行对应
test_sp(pi_person_id=>'001',pi_dept_id=>'A');
--通过名称进行对应(说明与位置无关)
test_sp(pi_dept_id=>'A',pi_person_id=>'001');
--位置和名称对应的混用(注意按位置对应的参数需要放到按名称查找的参数前面)
test_sp('001',pi_dept_id=>'A');
END;
使用名称进行参数对应的方式值得推荐,理由如下:
(1)根据参数名称显式地参数对应,使程序更加明了
(2)如果参数列表中存在设置默认值的情况,使用名称对应的方式能够使得程序更加灵活。
NOCOPY参数
对于OUT或INOUT类型的参数,使用NOCOPY将改变传值方式为传引用方式。
这里不予赘述。
函数
(1)函数可以作为表达式来使用
(2)关于参数
参见存储过程的参数使用。
(3)关于RETURN
函数的头部分必须指定RETURN的数据类型。
函数的可执行部分中各条逻辑分支(包括异常处理部分)都必须RETURN一个值,这个值的数据类型需要与HEAD中所指定的相匹配)
举例:
CREATEORREPLACEFUNCTIONTEST_FUNCTION(pi_paraNVARCHAR2)
RETURNNVARCHAR2
AS
tanjie_exceptionEXCEPTION;
BEGIN
IF(pi_para='OK')THEN
RETURN'HELLOWORLD';
ELSE
RAISEtanjie_exception;
ENDIF;
EXCEPTION
WHENOTHERSTHEN
RETURN'ERROR';
END;
调用:
BEGIN
DBMS_OUTPUT.PUT_LINE('OK:
'||TEST_FUNCTION('OK')||CHR(10)||
'ERR:
'||TEST_FUNCTION('X')||CHR(10)
);
END;
触发器
当数据库中发生某种事件时自动触发的PL/SQL块称为触发器。
触发器常用类型:
(1)DML触发器
当一条记录被插入,修改或删除时触发。
可以用来验证数据,设置默认值,审核数据变更或拒绝某种类型的DML
(2)DDL触发器
当DDL执行时触发,例如表创建时触发。
可以用来执行验证或阻止特定DDL发生。
(3)INSTEADOF触发器
INSTEADOF触发器是一种视图DML操作时触发用来阻止对视图的更新操作的触发器。
当DML操作发生时,不会执行这些DML语句,而是执行触发器中的语句
关于DML触发器
(1)BEFORE/AFTER
(2)STATEMENT-LEVEL/ROW-LEVEL
(3)NEW/OLD
(4)WHEN子句
CREATE[ORREPLACE]TRIGGERtriggername
{BEFORE|AFTER}
{INSERT|DELETE|UPDATE|UPDATEOFcolumnlist}ONtablename
[FOREACHROW]
[WHEN(...)]
[DECLARE...]
BEGIN
...executablestatements...
[EXCEPTION...]
END[triggername];
关于DML触发器的事务
(1)通常,DML触发器是触发它的事务的一部分
(2)如果DML触发器中抛出异常,主事务将回滚
(3)不能在DML触发器中进行COMMIT或ROLLBACK操作
(4)可以指定DML触发器的事务为自治事务
举例:
CREATEORREPLACETRIGGERtanjie_test_trigger
AFTERUPDATE
ONtanjie21
FOREACHROW
DECLARE
PRAGMAAUTONOMOUS_TRANSACTION;
BEGIN
INSERTINTOtanjie_history
VALUES(:
OLD.ID,
:
NEW.ID);
COMMIT;
END;
包
包是PL/SQL中的一个重要组件。
包的一个重要特性就是区分公有和私有,通过隐藏不必要的数据或操作,可以降低系统的复杂度。
包由包说明(PACKAGESPECIFICATION)和包体(PACKAGEBODY)两部分组成。
包说明和包体分开编译,并作为两个分开的对象存放在数据字典中。
包不能嵌入在其他PL/SQL块中。
包说明中说明了包中所有PUBLIC的元素,这些元素可以被包以外的程序所调用,称这些元素为包的公有元素。
包说明中不包含任何PL/SQL块或可执行代码。
外界程序完全通过包说明来获知包能够提供哪些功能以及如何调用它们。
包体中实现了所有在包说明中定义的元素;此外,包体中也可以包含没有出现在包说明中的元素,它们对外界程序不可见,不能被外界程序所调用,因此称这些元素为包的私有元素,。
包的初始化部分:
在包体的尾部可以定义包的初始化部分。
有些类似JAVA的构造函数,可以在包初始化部分执行复杂的代码来进行整个包的初始化工作。
需要注意的是,在一个会话中,包只会被初始化一次。
包的初始化部分不是必须定义的。
举例:
包说明部分
CREATEORREPLACEPACKAGETANJIE_TEST_PKGIS
--非安全状态
STATE_NOTSAFECONSTANTCHAR
(1):
='N';
--安全状态
STATE_SAFECONSTANTCHAR
(1):
='S';
/**
* 取得库房状态
*/
FUNCTIONGET_STATE(pi_warehouse_idNVARCHAR2)
RETURNCHAR;
/**
* 改变库房物料数
*/
PROCEDUREDO_ADD_SP(pi_warehouse_idNVARCHAR2,
pi_numberNUMBER);
ENDTANJIE_TEST_PKG;
包体部分
CREATEORREPLACEPACKAGEBODYTANJIE_TEST_PKG
IS
--物料下限
LOWER_LIMITCONSTANTNUMBER:
=0;
--物料安全储备
SAFE_LIMITCONSTANTNUMBER:
=10;
--物料上限
UPPER_LIMITCONSTANTNUMBER:
=100;
TYPEtype_warehouse_listISTABLEOFNUMBERINDEXBYVARCHAR2(100);
--库房列表
glo_warehouse_listtype_warehouse_list;
/***********私有方法部分**********/
/**
* 记录并处理异常
*/
PROCEDURELOGGER_ERROR(pi_errorNVARCHAR2)
IS
BEGIN
RAISE_APPLICATION_ERROR('-20050',pi_error);
END;
/**
* 验证库房是否存在
*/
PROCEDUREVALIDATE_EXISTS(pi_warehouse_idNVARCHAR2)
IS
BEGIN
IF(glo_warehouse_list.EXISTS(pi_warehouse_id))THEN
LOGGER_ERROR('WAREHOUSENOTEXISTS:
'||pi_warehouse_id);
ENDIF;
END;
/**
* 验证改变库房数目是否合理
*/
PROCEDUREVALIDATE_ADD_NUMBER(pi_warehouse_idNVARCHAR2,
pi_numberNUMBER)
IS
v_now_numberNUMBER;
BEGIN
v_now_number:
=glo_warehouse_list(pi_warehouse_id);
IF(v_now_number+pi_number LOGGER_ERROR('LACKOFITEM: '||v_now_number); ELSIF(v_now_number+pi_number>UPPER_LIMIT)THEN LOGGER_ERROR('OVERFLOW: '||v_now_number); ENDIF; END; /***********公有方法部分**********/ /** *获取库房状态 */ FUNCTIONGET_STATE(pi_warehouse_idNVARCHAR2) RETURNCHAR IS v_now_numberNUMBER; BEGIN VALIDATE_EXISTS(pi_warehouse_id); v_now_number: =glo_warehouse_list(pi_warehouse_id); IF(v_now_number RETURNSTATE_NOTSAFE; ELSE RETURNSTATE_SAFE; ENDIF; END; /** * 改变库房物料数 */ PROCEDUREDO_ADD_SP(pi_warehouse_idNVARCHAR2, pi_numberNUMBER) IS BEGIN VALIDATE_EXISTS(pi_warehouse_id); VALIDATE_ADD_NUMBER(pi_warehouse_id,pi_number); glo_warehouse_list(pi_warehouse_id): = glo_warehouse_list(pi_warehouse_id)+pi_number; END; BEGIN glo_warehouse_list('A'): =30; glo_warehouse_list('B'): =30; EXCEPTION WHENOTHERSTHEN DBMS_OUTPUT.PUT_LINE('INITEXCEPTION'); ENDTANJIE_TEST_PKG; 外部程序调用: BEGIN TANJIE_TEST_PKG.DO_ADD_SP('A',-25); IF(TANJIE_TEST_PKG.GET_STATE('A')= TANJIE_TEST_PKG.STATE_SAFE)THEN DBMS_OUTPUT.PUT_LINE('SAFE'); ELSE DBMS_OUTPUT.PUT_LINE('NOTSAFE'); ENDIF; END; 关于包说明和包体的进一步说明: (1)可以在包说明或包体中声明几乎所有类型的元素,包括数值、异常、集合等。 但是建议避免在包说明中声明变量。 (2)在包中定义公有的过程和函数时,需要在包说明中声明它们的头部分,在包体中来实现它们。 (3)在包说明或包体中可以声明各种数据结构的TYPE,包括集合TYPE,RECORDTYPE,游标变量TYPE等。 (4)可以在包中定义公有或私有的显式游标,ORACLE不支持在包中定义游标变量。 但是建议避免在包说明中声明显式游标。 (5)包体不总是必须的,但是如果在包说明中包含了过程、函数、显式游标的声明,则必须定义包体。 (6)包体如果被定义,内部不可以为空,要么定义初始化部分,要么定义声明部分,二者也可以都存在。 (7)包体的异常处理部分只处理所有由初始化部分抛出的异常 (8)外部程序调用包中的元素,需要使用PACKAGE_NAME.ELEMENT_NAME的方式;而包内部调用包中的元素,则不需要加包名。 关于包数据和会话持有的说明: 包数据指在包说明或包体内定义的常量或变量(注意不是在包方法里面的常量或变量)。 它们是包级别的数据,作用域为整个包。 会话第一次调用包(无论以任何形式引用包中的元素),ORACLE就会对包进行初始化操作: 实例化所有包数据并赋值,执行初始化部分的语句。 包一旦被加载,整个包都将保持在会话中。 这样以后对包中的子程序进行调用,将会减少I/
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- PLSQL 系列 培训 SQL 优化 幻灯 说明
![提示](https://static.bdocx.com/images/bang_tan.gif)