2015/8/17笔记整理 第12章 PL/SQL编程简介1 块 过程 函数
Oracle数据库中包含一种过程化编程语言PL/SQL,PL/SQL是ORACLE对标准数据库语言的扩展。
一.PL/SQL的优点
PL/SQL 不是一个独立的产品,他是一个整合到ORACLE服务器和ORACLE工具中的技术,可以把PL/SQL看作ORACLE服务器内的一个引擎,sql语句执行者处理单个的sql语句,PL/SQL引擎处理PL/SQL程序块。当PL/SQL程序块在PL/SQL引擎处理时,ORACLE服务器中的SQL语句执行器处理pl/sql程序块中的SQL语句。
PL/SQL语句—》ORACLE服务器—》分解代码—》PL/SQL引擎
--》SQL引擎
对于SQL,ORACLE必须在同一时间处理每一条SQL语句,在网络环境下这就意味作每一个独立的调用都必须被oracle服务器处理,这就占用大量的服务器时间,同时导致网络拥挤。而PL/SQL是以整个语句块发给服务器,这就降低了网络拥挤。
二.PL/SQL块结构
PL/SQL是一种块结构的语言,组成PL/SQL程序的单元是逻辑块,一个PL/SQL 程序包含了一个或多个逻辑块,每个块都可以划分为三个部分。与其他语言相同,变量在使用之前必须声明,PL/SQL提供了独立的专门用于处理异常的部分,下面描述了PL/SQL块的不同部分:
1.声明部分(Declaration section)
声明部分包含了变量和常量的数据类型和初始值。这个部分是由关键字DECLARE开始,如果不需要声明变量或常量,那么可以忽略这一部分;需要说明的是游标的声明也在这一部分。
2.执行部分(Executable section)
执行部分是PL/SQL块中的指令部分,由关键字BEGIN开始,所有的可执行语句都放在这一部分,其他的PL/SQL块也可以放在这一部分。
3.异常处理部分(Exception section)
这一部分是可选的,在这一部分中处理异常或错误,对异常处理的详细讨论我们在后面进行。
PL/SQL块语法
[DECLARE] |
PL/SQL块中的每一条语句都必须以分号结束,SQL语句可以使多行的,但分号表示该语句的结束。一行中可以有多条SQL语句,他们之间以分号分隔。每一个PL/SQL块由BEGIN或DECLARE开始,以END结束。注释由--标示。
练习:定义三个变量 分别是长方形 v_width, v_height,v_area 高和面积有初始值: 2,6,对v_width求值,并输出(考虑如果高为0的异常).
DECLARE
v_width number;
v_height number := 2;
v_area number := 6;
BEGIN
v_width := v_area /v_height;
DBMS_OUTPUT.PUT_LINE(' v_width:'|| v_width);
END;
/
三.PL/SQL块的命名块和匿名块
PL/SQL程序块:命名的程序块 和 匿名程序块。
命名程序块:可以出现在其他PL/SQL程序块的声明部分,这方面比较明显的是子程序,子程序可以在执行部分引用,也可以在异常处理部分引用。
PL/SQL程序块可被独立编译并存储在数据库中,任何与数据库相连接的应用程序都可以访问这些存储的PL/SQL程序块。
ORACLE提供了四种类型的可存储的程序:
. 函数
. 过程
. 包
. 触发器
1.函数
函数是命名了的、存储在数据库中的PL/SQL程序块。函数接受零个或多个输入参数,有一个返回值,返回值的数据类型在创建函数时定义。定义函数的语法如下:
CREATE OR REPLACE FUNCTION name [(parameter [IN|OUT|IN OUT] datatype[,parameter,...])] RETURN datatypes IS |
2.过程
存储过程是一个PL/SQL程序块,接受零个或多个参数作为输入(INPUT)或输出(OUTPUT)、或既作输入又作输出(INOUT),与函数不同,存储过程没有返回值,存储过程不能由SQL语句直接使用,只能通过call关键字/EXECUT命令或PL/SQL程序块内部调用,定义存储过程的语法如下:
CREATE [OR REPLACE] PROCEDURE name [(parameter [IN | OUT | INOUT] datatype [,parameter,...])] IS |
IN: 必须为过程、函数的IN参数赋初值,只传入,不传出,不能在代码中再赋值。(传值)
OUT: 只传出,不传入,不接收赋值(传址)
IN OUT: 既能输入,又能更改其值,并输出。
后两种情况,只允许输入已存在的变量。
例范:
过程的例子:
CREATE OR REPLACE PROCEDURE Test(v_ height number, v_area number ) IS
v_width number;
BEGIN
v_width := v_area /v_height;
DBMS_OUTPUT.PUT_LINE(' v_width:'|| v_width);
END;
/
CREATE OR REPLACE PROCEDURE Ptest(v_ename IN emp.ename%TYPE) IS
v_empno emp.empno%TYPE;
BEGIN
SELECT empno INTO v_empno FROM emp WHERE ename = v_ename;
v_ename := ‘SOCTT23’;
DBMS_OUTPUT.PUT_LINE('enmpno '||v_empno||' is '||v_ename);
END Ptest;
CREATE OR REPLACE PROCEDURE Ptest2(v_out IN number) IS
BEGIN
DBMS_OUTPUT.PUT_LINE(NVL(v_out,0));
-- v_out := 10;
DBMS_OUTPUT.PUT_LINE(v_out);
END;
如果显示编译错误,可是使用SHOW ERROR显示错误发生的地点和原因
查看存储过程: select * from user_procedures;
删除: DROP PROCEDURE 过程名
无参的PROCEDURE:
CREATE OR REPLACE PROCEDURE Test IS
v_width number;
BEGIN
v_width := 6 /2;
DBMS_OUTPUT.PUT_LINE(' v_width:'|| v_width);
END;
/
函数的例子:要求输入圆的半径,求面积
CREATE OR REPLACE FUNCTION circle_area ( p_radius IN number) RETURN number IS
v_pi number := 3.1415926;
v_area number;
BEGIN
v_area := v_pi* POWER(p_radius,2);
RETURN v_area;
END circle_area;
调用:注意,不能使用exec 函数名的形式!
第一种方式:
SQL> select circle_area(3) from dual;
CIRCLE_AREA(3)
--------------
28.2743334
第二种方式:
SQL> declare
2 v_num number;
3 begin
4 v_num := circle_area(3);
5 dbms_output.put_line(v_num);
6 end;
7 /
28.2743334
PL/SQL块还可以接收用户的输入信息,例如:现在要求用户输入一个雇员编号,之后根据输入的内容进行查询,查询雇员的姓名。
• 用户的输入信息使用“&”完成。
DECLARE
eno NUMBER ;
en VARCHAR2(30) ;
BEGIN -- 输入的信息保存在eno里
eno := &no ; -- 之后根据eno的值,对数据库进行查询操作
SELECT first_name INTO en FROM customers WHERE customer_id=eno ;
DBMS_OUTPUT.put_line('编号为:'||eno||'雇员的姓名为:'||en) ;
EXCEPTION
WHEN no_data_found
THEN DBMS_OUTPUT.put_line('没有此客户') ;
END ;
/