本文共 5788 字,大约阅读时间需要 19 分钟。
块 (Block)是PL/SQL的基本程序单元,编写PL/SQL程序实际就是编写PL/SQL块。要 完成简单的应用功能。可能只需要写一个简单的PL/SQL块;如果要实现复杂的应用功能,就要在PL/SQL块中嵌套其他的PL/SQL块;编写PL/SQL应用模块,块的嵌套层次没有限制。
由三部分组成,声明部分(DECLARE/declare),执行部分(BEGIN-END/begin-end),异常部分(EXCEPTION/exception)。
- 声明部分:用于定义常量、变量、游标、例外(异常)、复杂数据类型等; - 执行部分:用于实现应用模块功能,包含了要执行的SQL语句和PL/SQL语句 - 例外部分:用于对于执行部分出现的例外进行处理-- 声明部分declare -- 声明变量、常量、例外(异常)、游标、复杂数据类型 -- 声明时,需要确定数据类型的长度begin -- 执行SQL语句或PL/SQL语句exception -- 处理执行过程中出现的例外(异常)end; -- 块结束标记
注意: declare(声明部分)和exception(例外/异常部分)是可选的,begin(执行部分)是不需要有的,块最终以end结束,end后面要加“;”号
注意,当使用dbms_output包输出数据或准息时,必须将SQL*Plus的环境变量serveroutput设置为on。
根据需要实现的应用模块功能,可以将PL/SQL块划分为匿名块、命名块、子程序和触发器等四类。
declare e_no number := &eno; e_name varchar2(20) ;begin select ename into e_name from emp where empno = e_no; dbms_output.put_line(e_name);exception when no_data_found then dbms_output.put_line('此员工不存在');end;
上述例子中,因为直接通过declare定义的块,所以是一个匿名块
-- 这里用的是PL/SQL Developer进行测试,所以需要在命名块外部添加一个匿名块,有网友说在SQLPlus中,命名块可以直接写,不需要再匿名块中,有待实验begin <> declare e_name varchar2(20); begin < > begin select ename into e_name from emp where empno = &eno; end; dbms_output.put_line(e_name); end;end;
1.过程:过程用于执行特定操作,当建立过程时,既可以指定输入参数(in), 也可以制定输出参数(out)。通过在过程中使用输入参数,可以将应用环境的数据传递到执行部分,通过使用输出参数,可以将执行部分的数据传递到应用环境。可以使用create or replace procedure命令创建过程。
格式: create or replace procedure 过程名(参数列表) is begin sql语句; end;
-- 创建一个独立的过程create or replace procedure update_sal(esal in emp.sal%type, eno in number) isbegin update emp set sal = esal where empno = eno; commit;end;-- 执行这个过程方式一begin update_sal(800, 7369);end;-- 执行这个过程方式二SQL> exec update_sal(800, 7369);-- 执行这个过程方式三SQL> call update_sal(800, 7369);
注意: 1.过程的形式参数不能定义数据类型的长度 2.过程的形式参数中可以给定in或out表示这个参数是输入参数,还是输出参数,输出参数表示语句执行结束后需要用同数据类型的值接收 3.如果不写in或out,默认为in,即输入参数
格式: create or replace function 方法名(参数) return 数据类型 is 参数名 数据类型; begin sql语句; return 参数名; end;
-- 创建一个独立的函数create or replace function annual_income(e_no emp.empno%type) return number is sum_sal number;begin select sal+nvl(comm, 0) into sum_sal from emp where empno = e_no; return sum_sal;end;-- 执行函数declare a number;begin a := annual_income(7369); dbms_output.put_line(a);end;
3.包:
包用于逻辑组合相关的过程和函数, 它由包规范(包头)和包体两部分组成,相当于Java中的借口和实现类。包规范用于定义公用的常量、变量、过程和函数,可以使用create or replace package创建包-- 包头:create or replace package 包名 is function find(参数列表) return 返回值类型; procedure 过程名(参数列表) ;end;create or replace package test is function find(a number) return number; procedure find2(b in number, c out number);end;
包头(包规范)只包含了过程和函数的说明,而没有过程和函数的实现代码。包体用于实现包头(包规范)中的过程和函数,使用create or replace package body创建
-- 包体create or replace package body 包体名 is function 函数名(参数列表) return 返回值类型 is 返回值列表 ; begin 函数的方法体; return 返回值; end; procedure 过程名(参数列表) is begin 过程方法体; end;end;create or replace package body test is function find(a number) return number is e_sal number ; begin select sal into e_sal from emp where empno = a; return e_sal; end; procedure find2(b in number, c out number) is begin select sal into c from emp where empno = b ; end;end;
当调用包的过程和函数时,在过程和函数名之前必须带上包名座位前缀(包名.子程序名),如果访问其他方案的包,还必须加上方案名作为前缀(方案名.包名.子程序名)
declare num number;begin num := test.find(7369);end;
编写PL/SQL程序时,若临时存储数值,必须要定义变量和常量;若在应用环境和子程序之间传递数据,必须要为子程序指定参数。在PL/SQL程序中定义变量、常量和参数时,必须指定数据类型。PL/SQL中有四种数据类型:标量类型(Scalar)、复合类型(Composite)、参照类型(Reference)和LOB(Large Object)类型。
指只能存放单个数值的变量。
当时用此数据类型时,必须指定长度
。 2.char(n) 存储固定长度的字符串,n指定字符串的最大长度,最大值为32767字节,如果没有指定n,默认为1
3.number(p,s) 存储固定长度的证书和浮点数,p表示精度,指定数字的总位数,s表示标度,指定小数点后的数字位数 4.date 存储时间和日期数据,长度为固定的7个字节,当给date变量赋值时,数据需与日期格式和日期语言匹配
5.timestamp 是Oracle9i新增的数据类型,存储日期和时间数据,与date使用方法相同,在显示日期时,还会显示时间和上下午标记
6.long和long raw long用于定义变长字符串,最大长度为32760字节;long raw用于定义变长的二进制数据,最大长度32760字节 7.boolean 存储布尔变量,其值为true、false、null;为PL/SQL数据类型,表列不能采用boolean数据类型
8.binary_integer 定义整数,数值范围-2147483647~2147483647之间,在Oracle9i之前,当在PL/SQL块中定义PL/SQL表时,必须使用该数据类型作为下标的数据类型,为PL/SQL数据类型,表列不能采用boolean数据类型
9.binary_float和binary double 是Oracle 10g 新增的数据类型,主要用于高速的科学计算。变量名 [constant] 数据类型 [not null] [:= | default 初始值]constant可选,如果定义常量,则必须有这个标识not null可选,规定数据是否可为null:= 赋值符号、defalut 定义数据的初始值,如果为常量,则必须定义
2.%type属性 定义变量时,可以使用%type属性确定变量的数据类型
sal emp.sal%type; -- 这就表示定义了一个名为sal,数据类型为emp表中的sal字段的数据类型的变量
这样定义变量的好处在于不容易出现数据类型错误
-- 格式:declare type 复合类型名称 is record( 参数列表 );begin 执行体;end;-- 例子declare type emp_no_name is record( eno emp.empno%type, enam emp.ename%type); emp_no_name_test emp_no_name;begin select empno, ename into emp_no_name_test from emp where empno = 7369; dbms_output.put_line(emp_no_name_test.eno || ' ' || emp_no_name_test.enam);end;
2.PL/SQL表
类似于Java中的数组,但PL/SQL表的下标可以为负值,元素个数没有限制,需要先声明。-- 格式:declare type 表名 is table of 数据类型 index by binary_integer; 变量名 数据类型;begin 执行语句;end;-- 例子:declare type emp_table is table of emp.sal%type index by binary_integer; emp_table_test emp_table;begin select sal into emp_table_test(-1) from emp where empno = 7369; dbms_output.put_line(emp_table_test(-1));end;