Oracle复习(PL/SQL)
一、概述
PL/SQL 作为 ORACLE 编程环境中的工具,是 SQL 的扩展,在 SQL 语句的使用上增加了编程语言的特点,PL/SQL 就是把数据操作和查询语句组织在 PL/SQL 代码的过程性单元中,通过逻辑判断、循环等操作实现复杂的功能或者计算的程序语言。
二、基本语法
PL/SQL 是块结构语言;PL/SQL 程序划分成几个部分,并在每个部分中写入逻辑代码块;每个块都由三个部分组成。
声明部分
此部分以关键字 DECLARE
开头,可选部分,定义了程序中需要使用的所有变量,游标,子程序和其他元素。
可执行命令部分
此部分包含在关键字 BEGIN
和 END
之间,强制性部分。由 PL/SQL 语句组成。必须至少有一个可执行代码行,它可以只是 NULL
命令,表示不执行任何操作。
异常处理部分
此部分以关键字 EXCEPTION
开头。可选部分,它包含处理程序中错误的异常。
DECLARE
<declarations section>
BEGIN
<executable command(s)>
EXCEPTION
<exception handling>
END;
Hello World 示例
DECLARE
message varchar2(20):= 'Hello, World!';
BEGIN
dbms_output.put_line(message);
END;
/
SQL
注:要从 SQL 命令行运行代码,需要在代码的最后一行之后键入 /
字符。
PL/SQL 标识符
PL/SQL标识符是常量,变量,异常,过程,游标和保留字。标识符包括一个字母,可选地后跟多个字母,数字,美元符号,下划线和数字符号,不得超过30
个字符。
默认情况下,标识符不区分大小写。例如,可以使用integer
或INTEGER
来表示一个数值。 不能使用保留关键字作为标识符。
PL/SQL 分隔符
分隔符是具有特殊含义的符号。以下是PL/SQL中的分隔符列表
PL/SQL程序单元
PL/SQL单元是以下任何一个
- PL/SQL 块
- 函数
- 包
- 包体
- 过程
- 触发器
- 类型
- 类型体
三、数据类型
PL/SQL变量,常量和参数必须具有有效的数据类型,数据类型指定了存储格式,约束和有效的值范围。
- 标量(SCALAR)类型 - 它是没有内部组件的单个值,例如:NUMBER,DATE或BOOLEAN等。
- 大对象(LOB)类型 - 指向与其他数据项(例如:文本,图形图像,视频剪辑和声音波形)分开存储的大对象的指针。
- 复合类型 - 具有可单独访问的内部组件的数据项。例如,集合和记录。
- 引用类型 - 指向其他数据项。
四、变量
变量声明
变量声明语法:变量名 [约束] 数据类型 [NOT NULL] [默认值];
--例如:
counter binary_integer := 0;
greetings varchar2(20) DEFAULT 'Have a Good Day';
如果使用NOT NULL约束,则必须为变量显式分配初始值。
DECLARE
a integer := 10;
b integer := 20;
c integer;
f real;
BEGIN
c := a + b;
dbms_output.put_line('Value of c: ' || c);
f := 70.0/3.0;
dbms_output.put_line('Value of f: ' || f);
END;
/
变量作用域
PL/SQL允许块的嵌套,即每个程序块可以包含另一个内部块。 如果在内部块中声明了一个变量,则外部块不可访问内部变量。 但是,如果一个变量声明并且可以被外部块访问,那么所有嵌套的内部块都可以访问该变量。变量有两种类型的范围
- 局部变量 - 内部块中声明的变量,外部块不可访问。
- 全局变量 - 在最外部块或包中声明的变量。
将SQL查询结果分配给PL/SQL变量
可以使用SQL的SELECT INTO语句将值分配给PL/SQL变量。 对于SELECT列表中的每个项目,INTO列表中必须有一个对应的类型兼容变量。
DECLARE
c_id customers.id%type := 1;
c_name customerS.No.ame%type;
c_addr customers.address%type;
c_sal customers.salary%type;
BEGIN
SELECT name, address, salary INTO c_name, c_addr, c_sal
FROM customers
WHERE id = c_id;
dbms_output.put_line
('Customer ' ||c_name || ' from ' || c_addr || ' earns ' || c_sal);
END;
/
五、条件控制
六、循环
循环类型
循环控制语句
基本循环
SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
x number := 10;
BEGIN
LOOP
dbms_output.put_line(x);
x := x + 10;
IF x > 50 THEN
exit;
END IF;
END LOOP;
-- after exit, control resumes here
dbms_output.put_line('After Exit x is: ' || x);
END;
/
while 循环
SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
x number := 10;
BEGIN
LOOP
dbms_output.put_line(x);
x := x + 10;
IF x > 50 THEN
exit;
END IF;
END LOOP;
-- after exit, control resumes here
dbms_output.put_line('After Exit x is: ' || x);
END;
/
for 循环
SET SERVEROUTPUT ON SIZE 100000;
DECLARE
a number(2);
BEGIN
FOR a in 10 .. 20 LOOP
dbms_output.put_line('value of a: ' || a);
END LOOP;
END;
/
七、存储过程
子程序是执行特定任务的程序单元/模块。 这些子程序组合起来形成更大的程序。这种做法被称为“模块化设计”。 子程序可以被称为调用程序的另一个子程序或程序调用。
可以在以下几个地方中创建一个子程序 -
- 在模式(schema)级别中
- 一个程序包中
- 在PL/SQL块中
在模式(schema)级别中,子程序是一个独立的子程序。它是使用CREATE PROCEDURE或CREATE FUNCTION语句创建的。它存储在数据库中,可以使用DROP PROCEDURE或DROP FUNCTION语句进行删除。
在包中创建的子程序是打包的子程序。它存储在数据库中,只有当使用DROP PACKAGE语句删除程序包时,才能将其删除。我们将在“PL/SQL程序包”一章中讨论程序包的应用。
PL/SQL子程序被命名为可以使用一组参数调用的PL/SQL块。 PL/SQL提供两种子程序 -
- 函数 - 这些子程序返回单个值; 主要用于计算和返回值。
- 存储过程(程序) - 这些子程序不直接返回值; 主要用于执行动作。
创建并执行存储过程
CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
{IS | AS}
BEGIN
< procedure_body >
END procedure_name;
/
exec greetings;
八、函数
创建并执行存储函数
CREATE [OR REPLACE] FUNCTION function_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
RETURN return_datatype
{IS | AS}
BEGIN
< function_body >
END [function_name];