plsql

https://www.yiibai.com/plsql/

--/ declare v_count number(10) := 0; begin for temp_cursor in ( select a.rowid as rid,substr(a.业务工号,1,8) as emp_id from sfa2.ORDER_DETAIL a where length (业务工号) > 8 and substr(a.业务工号,1,8) = '00106430' ) loop dbms_output.put_line('test '||temp_cursor.rid); /* */ update sfa2.ORDER_DETAIL a set (a.业务姓名,a.业务职务,a.上级工号,a.上级姓名,a.EMP_POS_ID) =( select EMP_NAME,POS_NAME,DIRECTOR_EMP_ID,DIRECTOR_EMP_NAME,POS_ID from cr_report.EMP_POSITION_ORG_VIEW b where b.emp_id = temp_cursor.emp_id ) where a.rowid= temp_cursor.rid; /* ++1*/ v_count := v_count + 1 ; /* 每1000 笔 提交1次 */ if v_count >= 1000 then dbms_output.put_line('v_count'||v_count); commit; v_count := 0; end if; end loop; commit; end; /

declare begin <executable command(s)> exception end;

----‘hello world’ 示例: declare message varchar2(20):= ‘hello, world!’; begin dbms_output.put_line(message); end; /

pl/sql分隔符 分隔符是一个具有特殊意义的符号。以下是在pl/sql分隔符的列表：

分隔符 描述 +, -, , / 加法，减法/否定，乘法，除法 % 属性索引 ’ 字符串分隔符 . 组件选择 (,) 表达式或列表分隔符 : 主机变量指示符 , 项目分离符 " 带引号的标识符分隔符 = 关系运算符 @ 远程访问指示符 ; 声明终止符 := 赋值运算符 => 操作符关联 || 连接运算符 ** 乘方运算符 <<, >> 标签分隔符（开始和结束） /, */ 多行注释分隔符（开始和结束） – 单行注释指示符 … 范围操作 <, >, <=, >= 关系运算符 <>, '=, ~=, ^= 不同版本的不相等的

注释 隔符 - （双连字符）和多行注释被 /* 和 / 括起来 –/ declare – variable declaration message varchar2(20):= ‘hello, world!’; begin / * pl/sql executable statement(s) */ dbms_output.put_line(message); end; /

数字数据类型及其子类型的详细信息：

数据类型 描述 pls_integer 通过2,147,483,647到-2147483648范围内有符号整数，以32位表示 binary_integer 通过2,147,483,647到-2147483648范围内的有符号整数，以32位表示 binary_float 单精度ieee 754格式的浮点数 binary_double 双精度ieee 754格式的浮点数 number(prec, scale) 定点或浮点数在范围1e-130至（但不包括）绝对值1.0e126。 number变量也可以表示0 dec(prec, scale) ansi具体的定点类型使用38位小数最大精度 decimal(prec, scale) ibm特定的固定点型具有38位小数最大精度 numeric(pre, secale) 浮点类型具有38位小数最大精度。 double precision 具有126个二进制数字最大精度ansi特定浮点型（约38位十进制数） float 具有126个二进制数字（约38位十进制数）最大精度ansi和ibm特定的浮点型 int 具有38位小数最大精度ansi具体的整数类型 integer ansi和ibm的38位小数最大精度具体的整数类型 smallint ansi和ibm的38位小数最大精度具体的整数类型 real 具有63位二进制数字最大精度浮点型（大约18位小数）

字符数据类型及其子类型的详细信息：

数据类型 描述 char 具有32,767个字节的最大尺寸固定长度字符串 varchar2 具有32,767个字节的最大尺寸变长字符串 raw 可变长度的二进制或字节字符串的32,767个字节的最大尺寸，而不是由pl/ sql解释 nchar 具有32,767个字节的最大尺寸的固定长度国家字符串 nvarchar2 具有32,767个字节的最大尺寸可变长度国家字符串 long 具有32,760字节最大尺寸变长字符串 long raw 可变长度的二进制或字节字符串的32,760字节的最大尺寸，而不是由pl/sql解释 rowid 物理行标识符，一行在一个普通的表中的地址 urowid 物理通用行标识符（物理，逻辑，或外国的行标识符）

字段名称 有效日期时间值 有效的间隔值 year -4712 to 9999 (年除外 0) 任何非零整数 month 01 to 12 0 to 11 day 01 to 31 (受制于年份和月份的数值，按日历的区域设置的规则) 任何非零整数 hour 00 to 23 0 to 23 minute 00 to 59 0 to 59 second 00 to 59.9(n), 其中，9（n）是时间小数秒精度 0 to 59.9(n), 其中，9（n）是区间小数秒精度 timezone_hour -12 to 14 （范围可容纳夏令时更改） 不适用 timezone_minute 00 to 59 不适用 timezone_region 在动态性能视图找到v$timezon{e}_{n}ames不适用timezon{e}_{a}bbr在动态性能视图找到v$timezone_names 不适用

可以定义和使用自己的子类型。

declare subtype name is char(20); subtype message is varchar2(100); salutation name; greetings message; begin salutation := 'reader '; greetings := ‘welcome to the world of pl/sql’; dbms_output.put_line('hello ’ || salutation || greetings); end; /

声明一个变量的语法是：

variable_name [constant] datatype [not null] [:= | default initial_value]

sales number(10, 2); pi constant double precision := 3.1415; name varchar2(25); address varchar2(100);

使用 default 关键字 使用 assignment 操作符

counter binary_integer := 0; greetings varchar2(20) default ‘have a good day’;

declare c_id customers.id%type := 1; c_name customers.name%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; /

声明一个常量 常量使用constant关键字声明。它需要一个初始值，并且不允许被改变该值。例如： pi constant number := 3.141592654;

declare – constant declaration pi constant number := 3.141592654; – other declarations radius number(5,2); dia number(5,2); circumference number(7, 2); area number (10, 2); begin – processing radius := 9.5; dia := radius * 2; circumference := 2.0 * pi * radius; area := pi * radius * radius; – output dbms_output.put_line('radius: ’ || radius); dbms_output.put_line('diameter: ’ || dia); dbms_output.put_line('circumference: ’ || circumference); dbms_output.put_line('area: ’ || area); end; /

当上述代码在sql提示符执行时，它产生了以下结果：

radius: 9.5 diameter: 19 circumference: 59.69 area: 283.53

pl/sql procedure successfully completed.

declare message varchar2(30):= ‘‘that’‘s yiibai.com!’’; begin dbms_output.put_line(message); end; /

算术运算符 关系运算符 比较运算符 逻辑运算符 字符串运算符

算术运算符 描述 示例

相加两个操作数 a + b = 15 第一个操作数减去第二个操作数 a - b = 5 两个操作数相乘 a * b = 50

/ 两个操作数相除 a / b = 2 ** 乘方运算 a ** b = 100000

关系运算符 描述 示例 = 检查两个操作数的值是否相等，如果是的话那么条件为真。 (a = b) 结果为 false. != ，<> ，~= 检查两个操作数的值是否相等，如果值不相等，则条件变为真。 (a != b) 结果为 true.

检查左边的操作数的值是否大于右操作数的值，如果是的话那么条件为真。 (a > b) 结果为 false. < 检查左边的操作数的值是否小于右操作数的值，如果是的话那么条件为真。 (a < b) 结果为 true. = 检查左边的操作数的值是否大于或等于右操作数的值，如果是的话那么条件为真。 (a >= b) 结果为 false. <= 检查左边的操作数的值是否小于或等于右操作数的值，如果是的话那么条件为真。 (a <= b) 结果为 true.

比较运算符 描述 示例 like like操作一个字符，字符串或clob值进行比较匹配模式则返回true，如果不匹配模式则false 如果 ‘zara ali’ like ‘z% a_i’ 返回一个布尔值true， 然而, ‘nuha ali’ like ‘z% a_i’ 返回布尔值 false between between 运算符测试一个值是否位于规定的范围内. x between a and b 意思就是 x >= a and x <= b. 如果 x = 10 那么 x between 5 and 20 返回 true, x between 5 and 10 返回 true, 但是 x between 11 and 20 返回 false in in运算符的测试设置成员. x in (set) 意味着x等于集合中的某一个成员 如果 x = ‘m’ then, x in (‘a’, ‘b’, ‘c’) 返回布尔值false，但x在(‘m’, ‘n’, ‘o’) 返回布尔值 true. is null is null运算符返回布尔值true，如果它的操作数是null或false（如果它不为null）。包括null值的比较总能取得null 如果 x = ‘m’, 那么 ‘x is null’ 返回布尔值false

逻辑运算符 描述 示例 and 称为逻辑and运算。 如果两个操作数为true，则条件为true (a and b) 结果为 false. or 所谓的逻辑或操作。如果任何两个操作数为true，则条件变为true (a or b) 结果为 true. not 所谓逻辑非运算符。用于反向操作数的逻辑状态。如果条件为true，那么逻辑非运算符将使它为false not (a and b) 结果为 true.

具有最高优先级的操作出现在表的顶部，那些具有最低出现在底部。在表达式，更高的优先级运算符将首先计算。

运算符 操作符 ** 指数运算 +, - 加法，取反 *, / 乘法，除法 +, -, || 加，减，并置 ‘= <, >, <=, >=, <>, !=, ~=, ^=, is null, like, between, in 比较 not 逻辑否定 and 关联 or 包含

like 运算符:

declare procedure compare (value varchar2, pattern varchar2 ) is begin if value like pattern then dbms_output.put_line (‘true’); else dbms_output.put_line (‘false’); end if; end;

begin compare(‘zara ali’, ‘z%a_i’); compare(‘nuha ali’, ‘z%a_i’); end; / 当上述代码在sql提示符执行时，它产生了以下结果：

true false

between运算符：

declare x number(2) := 10; begin if (x between 5 and 20) then dbms_output.put_line(‘true’); else dbms_output.put_line(‘false’); end if;

if (x between 5 and 10) then dbms_output.put_line(‘true’); else dbms_output.put_line(‘false’); end if;

if (x between 11 and 20) then dbms_output.put_line(‘true’); else dbms_output.put_line(‘false’); end if; end; /

in和is null运算符：

declare letter varchar2(1) := ‘m’; begin if (letter in (‘a’, ‘b’, ‘c’)) then dbms_output.put_line(‘true’); else dbms_output.put_line(‘false’); end if;

if (letter in (‘m’, ‘n’, ‘o’)) then dbms_output.put_line(‘true’); else dbms_output.put_line(‘false’); end if;

if (letter is null) then dbms_output.put_line(‘true’); else dbms_output.put_line(‘false’); end if; end; /

false true false

----if elseif else end-------------

declare a number(2) := 10; begin a:= 10; – check the boolean condition using if statement if( a < 20 ) then – if condition is true then print the following dbms_output.put_line('a is less than 20 ’ ); end if; dbms_output.put_line('value of a is : ’ || a); end; /

declare c_id customers.id%type := 1; c_sal customers.salary%type; begin select salary into c_sal from customers where id = c_id; if (c_sal <= 2000) then update customers set salary = salary + 1000 where id = c_id; dbms_output.put_line (‘salary updated’); end if; end; /

declare a number(3) := 100; begin if ( a = 10 ) then dbms_output.put_line(‘value of a is 10’ ); elsif ( a = 20 ) then dbms_output.put_line(‘value of a is 20’ ); elsif ( a = 30 ) then dbms_output.put_line(‘value of a is 30’ ); else dbms_output.put_line(‘none of the values is matching’); end if; dbms_output.put_line('exact value of a is: '|| a ); end; /

declare grade char(1) := ‘a’; begin case grade when ‘a’ then dbms_output.put_line(‘excellent’); when ‘b’ then dbms_output.put_line(‘very good’); when ‘c’ then dbms_output.put_line(‘well done’); when ‘d’ then dbms_output.put_line(‘you passed’); when ‘f’ then dbms_output.put_line(‘better try again’); else dbms_output.put_line(‘no such grade’); end case; end; /

declare grade char(1) := ‘b’; begin case when grade = ‘a’ then dbms_output.put_line(‘excellent’); when grade = ‘b’ then dbms_output.put_line(‘very good’); when grade = ‘c’ then dbms_output.put_line(‘well done’); when grade = ‘d’ then dbms_output.put_line(‘you passed’); when grade = ‘f’ then dbms_output.put_line(‘better try again’); else dbms_output.put_line(‘no such grade’); end case; end; /

declare a number(3) := 100; b number(3) := 200; begin – check the boolean condition if( a = 100 ) then – if condition is true then check the following if( b = 200 ) then – if condition is true then print the following dbms_output.put_line(‘value of a is 100 and b is 200’ ); end if; end if; dbms_output.put_line('exact value of a is : ’ || a ); dbms_output.put_line('exact value of b is : ’ || b ); end; /

—循环语句—

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; /

declare x number := 10; begin loop dbms_output.put_line(x); x := x + 10; exit when x > 50; end loop; – after exit, control resumes here dbms_output.put_line('after exit x is: ’ || x); end; /

declare a number(2) := 10; begin while a < 20 loop dbms_output.put_line('value of a: ’ || a); a := a + 1; end loop; end; /

for counter in initial_value … final_value loop sequence_of_statements; end loop;

declare a number(2); begin for a in 10 … 20 loop dbms_output.put_line('value of a: ’ || a); end loop; end; /

declare a number(2) ; begin for a in reverse 10 … 20 loop dbms_output.put_line('value of a: ’ || a); end loop; end; / 当上述代码在sql提示符执行时，它产生了以下结果：

value of a: 20 value of a: 19 value of a: 18 value of a: 17 value of a: 16 value of a: 15 value of a: 14 value of a: 13 value of a: 12 value of a: 11 value of a: 10

loop sequence of statements1 loop sequence of statements2 end loop; end loop;

for counter1 in initial_value1 … final_value1 loop sequence_of_statements1 for counter2 in initial_value2 … final_value2 loop sequence_of_statements2 end loop; end loop;

while condition1 loop sequence_of_statements1 while condition2 loop sequence_of_statements2 end loop; end loop;

declare i number(3); j number(3); begin i := 2; loop j:= 2; loop exit when ((mod(i, j) = 0) or (j = i)); j := j +1; end loop; if (j = i ) then dbms_output.put_line(i || ’ is prime’); end if; i := i + 1; exit when i = 50; end loop; end; /

declare a number(2) := 10; begin – while loop execution while a < 20 loop dbms_output.put_line ('value of a: ’ || a); a := a + 1; if a > 15 then – terminate the loop using the exit statement exit; end if; end loop; end; /

declare a number(2) := 10; begin – while loop execution while a < 20 loop dbms_output.put_line ('value of a: ’ || a); a := a + 1; – terminate the loop using the exit when statement exit when a > 15; end loop; end; /

declare a number(2) := 10; begin – while loop execution while a < 20 loop dbms_output.put_line ('value of a: ’ || a); a := a + 1; if a = 15 then – skip the loop using the continue statement a := a + 1; continue; end if; end loop; end; /

value of a: 10 value of a: 11 value of a: 12 value of a: 13 value of a: 14 value of a: 16 value of a: 17 value of a: 18 value of a: 19

declare a number(2) := 10; begin <> – while loop execution while a < 20 loop dbms_output.put_line ('value of a: ’ || a); a := a + 1; if a = 15 then a := a + 1; goto loopstart; end if; end loop; end; /

value of a: 10 value of a: 11 value of a: 12 value of a: 13 value of a: 14 value of a: 16 value of a: 17 value of a: 18 value of a: 19

declare name varchar2(20); company varchar2(30); introduction clob; choice char(1); begin name := ‘john smith’; company := ‘infotech’; introduction := ’ hello! i’‘m john smith from infotech.’; choice := ‘y’; if choice = ‘y’ then dbms_output.put_line(name); dbms_output.put_line(company); dbms_output.put_line(introduction); end if; end; / 当上述代码在sql提示符执行时，它产生了以下结果：

john smith infotech corporation hello! i’m john smith from infotech.

s.n. 函数及用途 1 ascii(x)； 返回字符 x 的 ascii 值 2 chr(x)； 返回字符 x 的 ascii 值 3 concat(x, y)； 连接字符串x和y，并返回附加的字符串 4 initcap(x)； 每个单词的首字母x中转换为大写，并返回该字符串 5 instr(x, find_string [, start] [, occurrence])； 搜索find_string在x中并返回它出现的位置 6 instrb(x)； 返回另一个字符串中字符串的位置，但返回以字节为单位的值 7 length(x)； 返回x中的字符数 8 lengthb(x)； 返回为单字节字符集的字节的字符串的长度 9 lower(x)； 在x转换为小写字母，并返回该字符串 10 lpad(x, width [, pad_string]) ； x用空格向左填充，把字符串的总长度达宽字符 11 ltrim(x [, trim_string])； 从x的左修剪字符 12 nanvl(x, value)； 如果x匹配nan的特殊值（非数字）则返回其值，否则返回x 13 nls_initcap(x)； 相同initcap函数，但它可以使用不同的排序方法所指定nlssort 14 nls_lower(x) ； 同样的，不同的是它可以使用不同的排序方法所指定nlssort lower函数 15 nls_upper(x)； 相同，不同之处在于它可以使用不同的排序方法所指定nlssort upper函数 16 nlssort(x)； 改变排序的字符的方法。任何nls函数之前必须指定该参数; 否则，默认的排序被使用 17 nvl(x, value)； 返回如果x为null返回null; 否则返回x 18 nvl2(x, value1, value2)； 如果x不为null返回value1; 如果x为null，则返回value2 19 replace(x, search_string, replace_string)； 搜索x对于search_string并替换使用replace_string它 20 rpad(x, width [, pad_string])； 填充x到右侧 21 rtrim(x [, trim_string])； 从x右边修剪 22 soundex(x) ； 返回包含x的拼音表示形式的字符串 23 substr(x, start [, length])； 返回x的一个子开始于由start指定的位置。可选长度为子字符串 24 substrb(x)； 相同substr除外的参数均以字节代替字符的单字节字符的系统 25 trim([trim_char from) x)； 从左侧和右侧修剪x字符 26 upper(x)； x转换为大写字母，并返回该字符串

declare greetings varchar2(11) := ‘hello world’; begin dbms_output.put_line(upper(greetings));

dbms_output.put_line(lower(greetings));

dbms_output.put_line(initcap(greetings));

/* retrieve the first character in the string */ dbms_output.put_line ( substr (greetings, 1, 1));

/* retrieve the last character in the string */ dbms_output.put_line ( substr (greetings, -1, 1));

/* retrieve five characters, starting from the seventh position. */ dbms_output.put_line ( substr (greetings, 7, 5));

/* retrieve the remainder of the string, starting from the second position. */ dbms_output.put_line ( substr (greetings, 2));

/* find the location of the first “e” */ dbms_output.put_line ( instr (greetings, ‘e’)); end; /

当上述代码在sql提示符执行时，它产生了以下结果：

hello world hello world hello world h d world ello world 2

-----数组----- create or replace type varray_type_name is varray(n) of <element_type>

varray_type_name 就是一个有效的属性名 n是varray元素（最大值）的数目 element_type 就是所述数组的元素的数据类型。 varray的最大长度可以使用alter type语句来改变。

例如, create or replace type namearray as varray(3) of varchar2(10);

type namearray is varray(5) of varchar2(10); type grades is varray(5) of integer;

declare type namesarray is varray(5) of varchar2(10); type grades is varray(5) of integer; names namesarray; marks grades; total integer; begin names := namesarray(‘kavita’, ‘pritam’, ‘ayan’, ‘rishav’, ‘aziz’); marks:= grades(98, 97, 78, 87, 92); total := names.count; dbms_output.put_line('total ‘|| total || ’ students’); for i in 1 … total loop dbms_output.put_line('student: ’ || names(i) || ’ marks: ’ || marks(i)); end loop; end; /

declare cursor c_customers is select name from customers; type c_list is varray (6) of customers.name%type; name_list c_list := c_list(); counter integer :=0; begin for n in c_customers loop counter := counter + 1; name_list.extend; name_list(counter) := n.name; dbms_output.put_line(‘customer(’||counter ||’):’||name_list(counter)); end loop; end; /

----创建过程---- 过程使用create or replace procedure语句创建，使用create or replace procedure语句简化语法如下：

create [or replace] procedure procedure_name [(parameter_name [in | out | in out] type [, …])] {is | as} begin < procedure_body > end procedure_name; 这里,

procedure-name 指定的程序的名称 [or replace] 选项允许修改现有的程序 可选的参数列表中包含的名称，模式和类型的参数。in表示该值将被从外部传递，out表示该参数将被用于从过程返回一个值到外面 procedure-body 包含可执行部分 as关键字来代替了is关键字用于创建一个独立的程序。

例子： 下面的示例创建一个字符串的简单过程执行时将’hello world！“显示在屏幕上。

create or replace procedure greetings as begin dbms_output.put_line(‘hello world!’); end; /

使用execute关键字

从pl/sql块调用过程的名称

名为“greetings”的上述程序可以调用execute关键字为：

execute greetings; 上述调用会显示： hello world

pl/sql procedure successfully completed.

begin greetings; end; /

删除独立程序 一个独立的程序使用drop procedure语句删除。删除一个程序的语法是：

drop procedure procedure-name; begin drop procedure greetings; end; /

declare a number; b number; c number;

procedure findmin(x in number, y in number, z out number) is begin if x < y then z:= x; else z:= y; end if; end;

begin a:= 23; b:= 45; findmin(a, b, c); dbms_output.put_line(’ minimum of (23, 45) : ’ || c); end; /

declare a number; procedure squarenum(x in out number) is begin x := x * x; end; begin a:= 23; squarenum(a); dbms_output.put_line(’ square of (23): ’ || a); end; /

----创建函数----- 建立一个独立函数可以使用create function语句创建。create or replace procedure语句简化语法如下：

create [or replace] function function_name [(parameter_name [in | out | in out] type [, …])] return return_datatype {is | as} begin < function_body > end [function_name]; 这里,

function-name 指定函数的名称 [or replace] 选项允许修改现有的函数 可选的参数列表中包含的名称，模式和类型的参数。in表示该值将被从外部传递和out表示该参数将被用于过程外面返回一个值 函数必须包含一个return语句 return 子句指定要在函数返回的数据类型 function-body 包含可执行部分 as关键字来代替了is关键字用于创建一个独立的函数

示例: 下面的示例说明创建和调用一个独立的函数。函数返回在customers表中的客户总数。我们将使用customers表，已在前面pl/sql变量的章节中创建：

select * from customers;

±—±---------±----±----------±---------+ | id | name | age | address | salary | ±—±---------±----±----------±---------+ | 1 | ramesh | 32 | ahmedabad | 2000.00 | | 2 | khilan | 25 | delhi | 1500.00 | | 3 | kaushik | 23 | kota | 2000.00 | | 4 | chaitali | 25 | mumbai | 6500.00 | | 5 | hardik | 27 | bhopal | 8500.00 | | 6 | komal | 22 | mp | 4500.00 | ±—±---------±----±----------±---------+ create or replace function totalcustomers return number is total number(2) := 0; begin select count(*) into total from customers;

return total; end; /

declare c number(2); begin c := totalcustomers(); dbms_output.put_line('total no. of customers: ’ || c); end; /

declare a number; b number; c number; function findmax(x in number, y in number) return number is z number; begin if x > y then z:= x; else z:= y; end if;

return z; end; begin a:= 23; b:= 45;

c := findmax(a, b); dbms_output.put_line(’ maximum of (23,45): ’ || c); end; /

maximum of (23,45): 45

递归函数 declare num number; factorial number;

function fact(x number) return number is f number; begin if x=0 then f := 1; else f := x * fact(x-1); end if; return f; end;

begin num:= 6; factorial := fact(num); dbms_output.put_line(’ factorial '|| num || ’ is ’ || factorial); end; /

factorial 6 is 720

属性 描述 %found 返回true如果一个insert，update或delete语句影响了一行或多行或select into语句返回一行或多行。否则，它将返回false。 %notfound 逻辑相反%found。返回true如果一个insert，update或delete语句影响没有行或select into语句返回任何行。否则，它将返回false。 %isopen 隐式游标总是返回false，因为oracle执行其相关的sql语句之后自动关闭sql游标。 %rowcount 返回受insert，update影响的行数，或delete语句，或者通过一个select into语句返回。

declare total_rows number(2); begin update customers set salary = salary + 500; if sql%notfound then dbms_output.put_line(‘no customers selected’); elsif sql%found then total_rows := sql%rowcount; dbms_output.put_line( total_rows || ’ customers selected '); end if; end; /

6 customers selected

----显式游标---- cursor cursor_name is select_statement; 使用显式游标的工作包括四个步骤：

声明游标用于初始化在存储器 打开游标分配内存 获取游标检索数据 关闭游标释放分配的内存

declare c_id customers.id%type; c_name customers.name%type; c_addr customers.address%type; cursor c_customers is select id, name, address from customers; begin open c_customers; loop fetch c_customers into c_id, c_name, c_addr; exit when c_customers%notfound; dbms_output.put_line(c_id || ’ ’ || c_name || ’ ’ || c_addr); end loop; close c_customers; end; / 当上述代码在sql提示符执行时，它产生了以下结果：

1 ramesh ahmedabad 2 khilan delhi 3 kaushik kota 4 chaitali mumbai 5 hardik bhopal 6 komal mp

-----记录----------- 记录的以下几种类型： 基于数据表 基于游标的记录 用户自定义记录

基于表的记录 在％rowtype属性使程序员创建 基于表格和基于游标记录。

说明基于表的记录的概念。使用我们已经创建，并在前面的章节中使用的customers表：

declare customer_rec customers%rowtype; begin select * into customer_rec from customers where id = 5;

dbms_output.put_line('customer id: ’ || customer_rec.id); dbms_output.put_line('customer name: ’ || customer_rec.name); dbms_output.put_line('customer address: ’ || customer_rec.address); dbms_output.put_line('customer salary: ’ || customer_rec.salary); end; /

customer id: 5 customer name: hardik customer address: bhopal customer salary: 9000

基于游标记录 将说明了基于游标记录的概念。使用我们已经创建，并在前面的章节中使用的customers表：

declare cursor customer_cur is select id, name, address from customers; customer_rec customer_cur%rowtype; begin open customer_cur; loop fetch customer_cur into customer_rec; exit when customer_cur%notfound; dbms_output.put_line(customer_rec.id || ’ ’ || customer_rec.name); end loop; end; /

1 ramesh 2 khilan 3 kaushik 4 chaitali 5 hardik 6 komal

用户自定义记录 pl/sql提供了一个用户定义的记录类型，允许定义不同的记录结构。 记录由不同的字段组成。 假设要跟踪图书馆的书籍。 可能要跟踪有关每本书以下属性： 标题 作者 科目 书籍id

定义一条记录 记录类型定义为： type type_name is record ( field_name1 datatype1 [not null] [:= default expression], field_name2 datatype2 [not null] [:= default expression], … field_namen datatypen [not null] [:= default expression); record-name type_name; 这里是声明图书的记录方式：

declare type books is record (title varchar(50), author varchar(50), subject varchar(100), book_id number); book1 books; book2 books; 访问字段 要访问记录的字段，我们使用点（.）运算符。成员访问运算符编码为记录变量名和访问字段期间。

declare type books is record (title varchar(50), author varchar(50), subject varchar(100), book_id number); book1 books; book2 books; begin – book 1 specification book1.title := ‘c programming’; book1.author := 'nuha ali '; book1.subject := ‘c programming tutorial’; book1.book_id := 6495407;

– book 2 specification book2.title := ‘telecom billing’; book2.author := ‘zara ali’; book2.subject := ‘telecom billing tutorial’; book2.book_id := 6495700;

– print book 1 record dbms_output.put_line('book 1 title : '|| book1.title); dbms_output.put_line('book 1 author : '|| book1.author); dbms_output.put_line('book 1 subject : '|| book1.subject); dbms_output.put_line('book 1 book_id : ’ || book1.book_id);

– print book 2 record dbms_output.put_line('book 2 title : '|| book2.title); dbms_output.put_line('book 2 author : '|| book2.author); dbms_output.put_line('book 2 subject : '|| book2.subject); dbms_output.put_line('book 2 book_id : '|| book2.book_id); end; /

book 1 title : c programming book 1 author : nuha ali book 1 subject : c programming tutorial book 1 book_id : 6495407 book 2 title : telecom billing book 2 author : zara ali book 2 subject : telecom billing tutorial book 2 book_id : 6495700

记录作为子程序参数 可以通过记录作为子程序参数，非常相似传递任何其他变量的方式。访问可以象在上面的例子 - 已访问类似的方式记录字段：

declare type books is record (title varchar(50), author varchar(50), subject varchar(100), book_id number); book1 books; book2 books;

procedure printbook (book books) is begin dbms_output.put_line ('book title : ’ || book.title); dbms_output.put_line('book author : ’ || book.author); dbms_output.put_line( 'book subject : ’ || book.subject); dbms_output.put_line( 'book book_id : ’ || book.book_id); end;

begin – book 1 specification book1.title := ‘c programming’; book1.author := 'nuha ali '; book1.subject := ‘c programming tutorial’; book1.book_id := 6495407;

– book 2 specification book2.title := ‘telecom billing’; book2.author := ‘zara ali’; book2.subject := ‘telecom billing tutorial’; book2.book_id := 6495700;

– use procedure to print book info printbook(book1); printbook(book2); end; /

----------异常--------------- 两种类型的异常： 系统定义的异常 用户定义的异常

declare begin <executable command(s)> exception when exception1 then exception1-handling-statements when exception2 then exception2-handling-statements when exception3 then exception3-handling-statements … when others then exception3-handling-statements end;

declare c_id customers.id%type := 8; c_name customers.name%type; c_addr customers.address%type; begin select name, address into c_name, c_addr from customers where id = c_id;

dbms_output.put_line ('name: '|| c_name); dbms_output.put_line ('address: ’ || c_addr); exception when no_data_found then dbms_output.put_line(‘no such customer!’); when others then dbms_output.put_line(‘error!’); end; /

no such customer!

引发异常 异常是数据库服务器自动在内部数据库错误，但异常可以明确地提出由程序员使用命令raise。以下是引发异常的简单的语法：

declare exception_name exception; begin if condition then raise exception_name; end if; exception when exception_name then statement; end; 可以在引发oracle的标准异常或任何用户定义的异常使用上述语法。 下一节会显示如何引发用户定义异常，引发oracle标准异常以及类似的方法的例子。

用户自定义异常 pl/sql允许根据程序的需要定义自己的异常。用户定义的异常必须声明， 然后明确地提出使用一个raise语句或程序dbms_standard.raise_application_error。

声明一个异常的语法是：

declare my-exception exception; 示例: 下面的例子说明了这个概念。该程序需要一个客户id，当用户输入了一个无效的id，异常invalid_id引发。

declare c_id customers.id%type := &cc_id; c_name customers.name%type; c_addr customers.address%type;

– user defined exception ex_invalid_id exception; begin if c_id <= 0 then raise ex_invalid_id; else select name, address into c_name, c_addr from customers where id = c_id;

dbms_output.put_line ('name: '|| c_name); dbms_output.put_line ('address: ' || c_addr);

end if; exception when ex_invalid_id then dbms_output.put_line(‘id must be greater than zero!’); when no_data_found then dbms_output.put_line(‘no such customer!’); when others then dbms_output.put_line(‘error!’); end; / 当上述代码在sql提示符执行时，它产生了以下结果：

enter value for cc_id: -6 (let’s enter a value -6) old 2: c_id customers.id%type := &cc_id; new 2: c_id customers.id%type := -6; id must be greater than zero!

– select * from customer –/ declare c_id customer.id%type :=‘0011001472’; ----‘0011001472’ ; – &cc_id; c_name customer.name%type; c_addr customer.short_name%type; – user defined exception ex_invalid_id exception; begin if c_id <= 0 then raise ex_invalid_id; else select name, short_name into c_name, c_addr from customer where id = c_id;

dbms_output.put_line ('name: '|| c_name); dbms_output.put_line ('address: ' || c_addr);

end if; exception when ex_invalid_id then dbms_output.put_line(‘id must be greater than zero!’); when no_data_found then dbms_output.put_line(‘no such customer!’); when others then dbms_output.put_line(‘error!’); end; /

触发器是存储程序，它会自动执行或发射当一些事件发生。触发器，事实上，写入响应于以下任一事件将被执行： 数据库操作(dml)语句(delete，insert，update或) 数据库定义(ddl)语句(create，alter或drop) 数据库操作(servererror，登录，注销，启动或关机)

触发器可以在表，视图，模式或数据库与该事件相关联上定义

触发器的优点 触发器可以用于以下目的写：

自动生成一些派生列值 实施参照完整性 事件日志和对表的访问存储信息 审计 表的同步复制 实行安全许可 防止非法交易 创建触发器

创建触发器的语法：

create [or replace ] trigger trigger_name {before | after | instead of } {insert [or] | update [or] | delete} [of col_name] on table_name [referencing old as o new as n] [for each row] when (condition) declare declaration-statements begin executable-statements exception exception-handling-statements end; 那么,

create [or replace] trigger trigger_name: 创建或替换现有的触发器：trigger_name {before | after | instead of} : 指定当触发将被执行。在instead of子句用于在视图中创建触发器 {insert [or] | update [or] | delete}: 指定dml操作 [of col_name]: 指定将被更新的列名 [on table_name]: 指定触发器相关联的表的名称 [referencing old as o new as n]: 可以参考新旧值的各种dml语句，如insert，update和delete [for each row]: 指定的行级触发器，即触发器将每一行受到影响执行。否则，当执行sql语句，这被称为表级触发器触发将执行一次 when (condition): 触发器将触发的条件。此子句仅适用于行级触发器有效

首先，将使用我们已经创建，并在前面的章节中使用的customers表：

select * from customers;

±—±---------±----±----------±---------+ | id | name | age | address | salary | ±—±---------±----±----------±---------+ | 1 | ramesh | 32 | ahmedabad | 2000.00 | | 2 | khilan | 25 | delhi | 1500.00 | | 3 | kaushik | 23 | kota | 2000.00 | | 4 | chaitali | 25 | mumbai | 6500.00 | | 5 | hardik | 27 | bhopal | 8500.00 | | 6 | komal | 22 | mp | 4500.00 | ±—±---------±----±----------±---------+ 下面的程序创建了customers表中，将触发insert或update或delete在customers表进行操作的行级触发。 触发器将显示工资的旧值和新值之间的差额：

create or replace trigger display_salary_changes before delete or insert or update on customers for each row when (new.id > 0) declare sal_diff number; begin sal_diff := :new.salary - :old.salary; dbms_output.put_line('old salary: ’ || :old.salary); dbms_output.put_line('new salary: ’ || :new.salary); dbms_output.put_line('salary difference: ’ || sal_diff); end; / 当上述代码在sql提示符执行时，它产生了以下结果：

trigger created. 这里以下两点是重要的，应小心注意：

old和new引用是不可用于表级触发器，而不可以使用它们的创纪录级别的触发器。

如果想查询表中相同的触发，那么应用应该使用after关键字，因为触发器可以查询该表， 或再次改变它最初的变化仅适用后的表是回到一致的状态。

上述触发已经写在这样一种方式，它会在任何delete、insert或update操作在表上之前执行， 但可以夺触发器上编写一个或多个操作，例如before delete，这将会触发每当一个记录将使用在表上删除操作被删除。 触发一个触发器 让我们在customers表执行某些dml操作。这里有一个insert语句，这将在表中创建一个新的记录：

insert into customers (id,name,age,address,salary) values (7, ‘kriti’, 22, ‘hp’, 7500.00 ); 当记录在customers表中创建，上面创建触发器display_salary_changes将被触发，它会显示以下结果： old salary: new salary: 7500 salary difference: 因为这是一个新的记录，因此基本工资不可用及以上的结果来为空(null)。现在，让我们对customers表进行多一个dml操作。这里有一个update语句，该语句将更新表中现有的记录：

update customers set salary = salary + 500 where id = 2; 当记录在customers表更新，上面创建触发器display_salary_changes将被触发，它会显示以下结果：

old salary: 1500 new salary: 2000 salary difference: 500

包有两个强制性的部分：

包装规范定义包体或定义

包装规范定义 规范是接口到包。 它只是声明的类型，变量，常量，异常，游标和子程序可从封装外部引用。 换句话说，它包含关于包的内容的所有信息，但不包括用于子程序的代码。

置于规范的所有对象被称为公共对象。任何子程序在封装主体中没有包定义但编码被称为私有对象。

下面的代码片段显示了具有单一的程序包规范定义。一个包中可以定义的全局变量和多个程序或函数。

create package cust_sal as procedure find_sal(c_id customers.id%type); end cust_sal; / 当上述代码在sql提示符执行时，它产生了以下结果：

package created.

包主体 包体已经在包定义和其他私人声明中声明的各种方法，这是从代码隐藏在包外的代码。

create package body语句用于创建包体。下面的代码片段显示了包体声明上面创建的cust_sal包。在前面学习中，我们已经在数据库中创建了一个customers表 - pl/sql变量 这一章节。

create or replace package body cust_sal as procedure find_sal(c_id customers.id%type) is c_sal customers.salary%type; begin select salary into c_sal from customers where id = c_id; dbms_output.put_line('salary: '|| c_sal); end find_sal; end cust_sal; / 当上述代码在sql提示符执行时，它产生了以下结果：

package body created. 使用包元素 访问包元素（变量，过程或函数）的语法如下：

package_name.element_name;

想想，我们已经在上面的数据库模式中创建的包，下面的程序是使用cust_sal包的find_sal方法：

declare code customers.id%type := &cc_id; begin cust_sal.find_sal(code); end; / 当上面的代码在sql提示符下执行，它提示输入客户id，当输入一个id，它会显示相应的薪酬如下：

enter value for cc_id: 1 salary: 3000

例子： 下面的程序提供了一个更为完整的方案。我们将使用存储在数据库中的customers表的以下记录：

select * from customers;

±—±---------±----±----------±---------+ | id | name | age | address | salary | ±—±---------±----±----------±---------+ | 1 | ramesh | 32 | ahmedabad | 3000.00 | | 2 | khilan | 25 | delhi | 3000.00 | | 3 | kaushik | 23 | kota | 3000.00 | | 4 | chaitali | 25 | mumbai | 7500.00 | | 5 | hardik | 27 | bhopal | 9500.00 | | 6 | komal | 22 | mp | 5500.00 | ±—±---------±----±----------±---------+ 包规范定义： create or replace package c_package as – adds a customer procedure addcustomer(c_id customers.id%type, c_name customers.name%type, c_age customers.age%type, c_addr customers.address%type, c_sal customers.salary%type);

– removes a customer procedure delcustomer(c_id customers.id%type); –lists all customers procedure listcustomer;

end c_package; / 当上面的代码在sql提示符下执行，它会创建上述包，并显示以下结果：

创建包的主体部分： create or replace package body c_package as procedure addcustomer(c_id customers.id%type, c_name customers.name%type, c_age customers.age%type, c_addr customers.address%type, c_sal customers.salary%type) is begin insert into customers (id,name,age,address,salary) values(c_id, c_name, c_age, c_addr, c_sal); end addcustomer;

procedure delcustomer(c_id customers.id%type) is begin delete from customers where id = c_id; end delcustomer;

procedure listcustomer is cursor c_customers is select name from customers; type c_list is table of customers.name%type; name_list c_list := c_list(); counter integer :=0; begin for n in c_customers loop counter := counter +1; name_list.extend; name_list(counter) := n.name; dbms_output.put_line(‘customer(’ ||counter|| ‘)’||name_list(counter)); end loop; end listcustomer; end c_package; / 上面的例子中使用嵌套表，我们将在下一章讨论。当上述代码在sql提示符执行时，它产生了以下结果：

package body created. 使用包： 下面的程序使用声明并在包c_package中定义方法。

declare code customers.id%type:= 8; begin c_package.addcustomer(7, ‘rajnish’, 25, ‘chennai’, 3500); c_package.addcustomer(8, ‘subham’, 32, ‘delhi’, 7500); c_package.listcustomer; c_package.delcustomer(code); c_package.listcustomer; end; / 当上述代码在sql提示符执行时，它产生了以下结果：

customer(1): ramesh customer(2): khilan customer(3): kaushik customer(4): chaitali customer(5): hardik customer(6): komal customer(7): rajnish customer(8): subham customer(1): ramesh customer(2): khilan customer(3): kaushik customer(4): chaitali customer(5): hardik customer(6): komal customer(7): rajnish

pl/sql procedure successfully completed

集合是一个有序组具有相同的数据类型的元素。每个元素进行标识的唯一标表示其在集合中的位置。

pl/sql提供了三种集合类型： 索引表或关联数组 嵌套表 可变大小的数组或变长数组

每种类型的集合的以下特征：

集合类型 元素数量 下标类型 密集或稀疏 在哪里创建 可以是对象类型属性 关联数组(或索引表) 无界 字符串或整数 两种都可以 只有在pl/sql块 no 嵌套表 无界 整数 开始密集，可以变得稀疏 无论是在pl/sql块或模式级别 yes 可变大小的数组(变长数组) 有界 整数 总是密集 无论是在pl/sql块或模式级别 yes

这两种类型的pl/sql表即，索引表和嵌套表，都具有相同的结构以及行使用下标符号访问。 然而，这两种类型的表有不同一个方面： 嵌套表可以存储在数据库中的列， 但索引表不能。

索引表 一个索引表（也叫关联数组）是一组键 - 值对。每个键是唯一的，并且用于定位对应的值。键可以是整数或字符串。

使用以下语法创建一个索引表。在这里，我们正在创建一个索引表命名表名， 它的键是subscript_type类型以及对应值为 element_type 类型

type type_name is table of element_type [not null] index by subscript_type;

table_name type_name; 例子： 下面的示例演示如何创建一个表来存储整数值和键名称，它打印的名称相同的列表。

declare type salary is table of number index by varchar2(20); salary_list salary; name varchar2(20); begin – adding elements to the table salary_list(‘rajnish’) := 62000; salary_list(‘minakshi’) := 75000; salary_list(‘martin’) := 100000; salary_list(‘james’) := 78000;

– printing the table name := salary_list.first; while name is not null loop dbms_output.put_line ('salary of ’ || name || ’ is ’ || to_char(salary_list(name))); name := salary_list.next(name); end loop; end; / 当上述代码在sql提示符执行时，它产生了以下结果：

salary of rajnish is 62000 salary of minakshi is 75000 salary of martin is 100000 salary of james is 78000

pl/sql procedure successfully completed. 例子： 一个索引表的元素也可以是任何数据库表字段％type或任何数据库表%rowtype。 下面的例子说明了这个概念。我们将使用存储在数据库中customers表：

select * from customers;

±—±---------±----±----------±---------+ | id | name | age | address | salary | ±—±---------±----±----------±---------+ | 1 | ramesh | 32 | ahmedabad | 2000.00 | | 2 | khilan | 25 | delhi | 1500.00 | | 3 | kaushik | 23 | kota | 2000.00 | | 4 | chaitali | 25 | mumbai | 6500.00 | | 5 | hardik | 27 | bhopal | 8500.00 | | 6 | komal | 22 | mp | 4500.00 | ±—±---------±----±----------±---------+ declare cursor c_customers is select name from customers;

type c_list is table of customers.name%type index by binary_integer; name_list c_list; counter integer :=0; begin for n in c_customers loop counter := counter +1; name_list(counter) := n.name; dbms_output.put_line(‘customer(’||counter|| ‘):’||name_list(counter)); end loop; end; / 当上述代码在sql提示符执行时，它产生了以下结果：

customer(1): ramesh customer(2): khilan customer(3): kaushik customer(4): chaitali customer(5): hardik customer(6): komal

pl/sql procedure successfully completed

嵌套表 嵌套表是就像一个一维数组的元素的数量。然而，嵌套表不同于在以下几方面的数组： 数组具有元素的声明的数量，但嵌套表不用声明大小。嵌套表的大小可以动态地增加。 数组总是密集的，也就是说，它总是有连续的下标。嵌套数组初始是致密，但是删除它的元素时就会变成稀疏。 使用以下语法创建一个嵌套表： type type_name is table of element_type [not null]; table_name type_name; 这个声明是类似声明的索引表中，但没有index by子句。 嵌套表可以被存储在数据库中的列，所以它可用于简化在那里加入一个较大的表中的单个列，使用sql操作。关联数组不能存储在数据库中。 例子： 下面的例子说明使用嵌套表：

declare type names_table is table of varchar2(10); type grades is table of integer;

names names_table; marks grades; total integer; begin names := names_table(‘kavita’, ‘pritam’, ‘ayan’, ‘rishav’, ‘aziz’); marks:= grades(98, 97, 78, 87, 92); total := names.count; dbms_output.put_line(‘total ‘|| total || ’ students’); for i in 1 … total loop dbms_output.put_line(‘student:’||names(i)||’, marks:’ || marks(i)); end loop; end; / 当上述代码在sql提示符执行时，它产生了以下结果：

total 5 students student:kavita, marks:98 student:pritam, marks:97 student:ayan, marks:78 student:rishav, marks:87 student:aziz, marks:92

pl/sql procedure successfully completed. 例子： 嵌套表的元素也可以是任何数据库表字段％type和任何数据库表或％rowtype。下面的例子说明了这个概念。我们将使用存储在数据库中的customers表：

select * from customers;

±—±---------±----±----------±---------+ | id | name | age | address | salary | ±—±---------±----±----------±---------+ | 1 | ramesh | 32 | ahmedabad | 2000.00 | | 2 | khilan | 25 | delhi | 1500.00 | | 3 | kaushik | 23 | kota | 2000.00 | | 4 | chaitali | 25 | mumbai | 6500.00 | | 5 | hardik | 27 | bhopal | 8500.00 | | 6 | komal | 22 | mp | 4500.00 | ±—±---------±----±----------±---------+

declare cursor c_customers is select name from customers;

type c_list is table of customers.name%type; name_list c_list := c_list(); counter integer :=0; begin for n in c_customers loop counter := counter +1; name_list.extend; name_list(counter) := n.name; dbms_output.put_line(‘customer(’||counter||’):’||name_list(counter)); end loop; end; / 当上述代码在sql提示符执行时，它产生了以下结果：

customer(1): ramesh customer(2): khilan customer(3): kaushik customer(4): chaitali customer(5): hardik customer(6): komal

pl/sql procedure successfully completed. 集合方法 pl/sql提供，使集合更容易使用内置集合方法。下表列出了这些方法和用途：

s.n. 方法名称 用途 1 exists(n) 返回true，如果一个集合中的第n个元素的存在;否则返回false 2 count 返回集合当前包含的元素数量 3 limit 检查集合的最大尺寸 4 first 返回在使用整数下标集合的第一个（最小的）索引号 5 last 返回在使用整数下标集合的最后一个（最大的）索引号 6 prior(n) 返回集合中索引n前面的索引号 7 next(n) 返回索引n的下一个索引号 8 extend 追加1个空元素到集合 9 extend(n) 追加n个空元素到集合 10 extend(n,i) 追加的第i个元素的集合的n个拷贝 11 trim 移除集合的末尾一个元素 12 trim(n) 移除一个集合末尾的n个元素 13 delete 移除一个集合中的所有元素，设置计数为0 14 delete(n) 移除关联数组用数字键或嵌套表中的第n个元素。如果关联数组有一个字符串键，对应于该键的值的元素将被删除。如果n为空，delete（n）什么都不做 15 delete(m,n) 除关联数组或嵌套表范围m…n所有元素。如果m大于n或如果m大于或n是零，delete（m，n）什么也不做

集合异常 下表提供了集合异常和引发，当它们：

集合异常 引发情况 collection_is_null 尝试在一个原子空(null)的集合操作 no_data_found 下标指定被删除的元素，或关联数组的一个不存在的元素 subscript_beyond_count 下标超过了一个集合的元素数 subscript_outside_limit 下标超出了允许的范围 value_error 下标为空(null)或不可转换的键类型。如果该键被定义为pls_integer范围，下标是在该范围之外可能会出现这样的异常

数据库事务是一个工作的原子单元，其可以由一个或多个相关的sql语句组成。 所谓的原子性就是数据库的修改所带来的构成事务的sql语句可以集体被提交，即永久到数据库或从数据库中(撤消)回滚。

一个成功执行的sql语句和提交的事务不一样。即使一个sql语句执行成功， 除非包含该语句的事务被提交，但也可以回滚和声明(s)的所有更改可以撤消。

开始/结束事务 事务都有开始和结束。事务开始时有下列事件之一：

连接到数据库后执行的第一个sql语句。 在事务完成之后发出每一个新的sql语句完成。 事务结束时的下列事件之一发生： commit或发出rollback语句。

ddl语句，如create table语句，则发出;因为在这种情况下，commit被自动执行。 一个dcl语句，比如一个grant语句发出; 因为在这种情况下，commit被自动执行。

用户从数据库断开。 从sql* plus用户退出通过发出exit指令，commit自动执行。 sql* plus异常终止，自动执行rollback。

一个dml语句失败; 在这种情况下自动执行撤消dml语句rollback。

提交事务 事务是通过发出sql命令commit永久生效。commit命令的一般语法是：

commit; 示例,

insert into customers (id,name,age,address,salary)values (1, ‘ramesh’, 32, ‘ahmedabad’, 2000.00 ); insert into customers (id,name,age,address,salary)values (2, ‘khilan’, 25, ‘delhi’, 1500.00 ); insert into customers (id,name,age,address,salary)values (3, ‘kaushik’, 23, ‘kota’, 2000.00 ); insert into customers (id,name,age,address,salary)values (4, ‘chaitali’, 25, ‘mumbai’, 6500.00 ); insert into customers (id,name,age,address,salary)values (5, ‘hardik’, 27, ‘bhopal’, 8500.00 ); insert into customers (id,name,age,address,salary)values (6, ‘komal’, 22, ‘mp’, 4500.00 ); commit; 回滚事务 对数据库所做的不提交更改可以使用rollback命令撤消。

rollback命令的一般语法是：

rollback [to savepoint < saveyiibai_name>]; 当一个事务因一些前所未有的情况中止，如系统故障，则整个事务由于提交自动回滚。 如果不使用保存点，然后简单地使用下面的语句来回滚所有的变化：

rollback; 保存点 保存点是某种标志，帮助分裂一个长事务分成更小的单位设置了一些检查点。 由一个长事务中设置保存点，可以根据需要回滚到一个检查点。这是通过发出savepoint命令来完成。

保存点命令的一般语法是：

savepoint < saveyiibai_name >; 示例:

insert into customers (id,name,age,address,salary)values (7, ‘rajnish’, 27, ‘hp’, 9500.00 ); insert into customers (id,name,age,address,salary)values (8, ‘riddhi’, 21, ‘wb’, 4500.00 ); savepoint sav1;

update customers set salary = salary + 1000; rollback to sav1;

update customers set salary = salary + 1000 where id = 7; update customers set salary = salary + 1000 where id = 8; commit; 在这里，rollback to sav1; 声明回滚变化到一点，在那里标志着保存点sav1，之后将开始新的变化。

自动事务控制 要执行一个自动commit在每一个insert，update或delete命令执行时，可以设置autocommit环境变量：

set autocommit on; 可以关闭使用以下命令自动提交模式：

set autocommit off;

---

提供日期和时间相关的数据类型：

日期时间(datetime)数据类型 间隔数据类型

datetime数据类型有： date timestamp timestamp with time zone timestamp with local time zone

间隔数据类型是： interval year to month interval day to second

日期时间字段值和间隔数据类型 这两个日期时间和间隔数据类型包括字段。这些字段的值确定的数据类型的值。下表列出了时间和间隔的字段及其可能的值。

字段名称 有效日期时间值 有效的间隔值 字段名称 有效日期时间值 有效的间隔值 year -4712 to 9999 (除外0年) 任何非0整数 month 1月12日 0 - 11 day 01 - 31 (受month和year的数值，按日历的区域设置的规则) 任何非0整数 hour 00 - 23 0 - 23 minute 00 - 59 0 - 59 second 00 to 59.9(n), 其中，9（n）是时间小数秒精度,在9（n）的部分是不适用date。 0 to 59.9(n), 其中，9（n）是区间小数秒精度 timezone_hour -12 - 14 (适应范围夏令时更改)date 或 timestamp不适用. 不适用 timezone_minute 00 - 59 date 或 timestamp不适用. 不适用 timezone_region date 或 timestamp不适用. 不适用 timezone_abbr date 或 timestamp不适用. 不适用

下面是日期时间数据类型：

date - 它存储在字符和数字数据类型的日期和时间信息。它是由上世纪，年，月，日，时，分，秒的信息。它被指定为： timestamp - 它是日期数据类型的扩展。它的年，月，日的日期数据类型的存储，以及小时，分钟和秒值。它是用于存储精确的时间值有用。 timestamp with time zone - 它是时间戳的变体，其中包括一个时区区域名称或时区中它的值偏移。时区偏移量是本地的时间和utc之间的差值（以小时和分钟）。此数据类型是用于收集和计算跨越地理区域的最新信息有用。 timestamp with local time zone - 它是时间戳(timestamp )的另一种变型，其中包括一个时区中它的值偏移。

下表列出了日期时间函数（其中，x有日期时间值）：

s.n 函数名称 & 描述 1 add_months(x, y); 增加y个月到x 2 last_day(x); 返回该月的最后一天 3 months_between(x, y); 返回x和y之间的月数 4 next_day(x, day); 返回x后第二天的日期时间 5 new_time; 返回从由用户指定的一个时区的时间/天的值 6 round(x [, unit]); x舍入; 7 sysdate(); 返回当前日期时间。 8 trunc(x [, unit]); 截断x

s.n 函数名称 & 描述 1 current_timestamp() 返回包含当前会话时间以及会话时区的时区中的时间戳（ timestamp with time zone） 2 extract(( { year | month | day | hour | minute | second } | { timezone_hour | timezone_minute } | { timezone_region | } timezone_abbr ) from x) 从x提取并返回年，月，日，时，分，秒，或时间 3 from_tz(x, time_zone) 通过time_zone指定with timezone一个timestamp时间戳x和时区转换 4 localtimestamp() 返回包含在会话时区的本地时间时间戳 5 systimestamp() 返回含有当前数据库的时间以及数据库时区的时区中的时间戳(timestamp with time zone) 6 sys_extract_utc(x) with timezone x的timestamp转换为包含日期和时间utc时间戳 7 to_timestamp(x, [format]) 将字符串x转换为一个timestamp 8 to_timestamp_tz(x, [format]) 字符串x转换到with timezone时间戳

示例: 下面的代码段示出了使用上述函数功能：

select sysdate from dual; 输出: 08/31/2014 5:25:34 pm

select to_char(current_date, ‘dd-mm-yyyy hh:mi:ss’) from dual; 输出: 31-08-2014 05:26:14

select add_months(sysdate, 5) from dual; 输出: 01/31/2014 5:26:31 pm

select localtimestamp from dual; 输出: 8/31/2014 5:26:55.347000 pm

间隔数据类型和函数 以下是间隔数据类型：

interval year to month - 它存储了一段时间的使用年份和月份日期时间字段 interval day to second - 它存储了一段时间中的天，小时，分钟和秒

select interval ‘001-2’ year(3) to month,-- 1-2 interval ‘123’ year(3), – 123-0 sysdate+(interval ‘1001’ year(4)) , – 1001-0 sysdate+(interval ‘001-2’ year(3) to month) — 1-2 ,interval ‘300’ month(3), interval ‘25’ year --25-0 ,interval ‘4’ year ,interval ‘4-0’ year to month --4-0 ,interval ‘50’ month,interval ‘4-2’ year to month --4-2 ,interval ‘5-3’ year to month + interval ‘20’ month,interval ‘6-11’ year to month from dual ;

select interval ‘5’ year, interval ‘5’ month, interval ‘5’ day, interval ‘5’ hour, interval ‘5’ minute, interval ‘5’ second, interval ‘1 12:30:01’ day to second ,interval ‘3 12:30:06.7’ day to second(1) —3 12:30:6.700000000 ,interval ‘1 2:31:15.1250’ day(1) to second(4) ---- 1 2:31:15.125000000 second(2,9) from dual ;

select numtoyminterval(100,‘year’) ,numtoyminterval(100,‘month’) —只能到年，月 ,numtodsinterval(100,‘day’) — 可以到 日，时，分，秒 ,numtodsinterval(100,‘hour’) ,numtodsinterval(100,‘minute’) ,numtodsinterval(100,‘second’) from dual;

select extract(year from systimestamp) year ,extract(month from systimestamp) month ,extract(day from systimestamp) day ,extract(hour from systimestamp) hour ,extract(minute from systimestamp) minute ,extract(second from systimestamp) second ,extract(timezone_hour from systimestamp) th ,extract(timezone_minute from systimestamp) tm ,extract(timezone_region from systimestamp) tr ,extract(timezone_abbr from systimestamp) ta from dual ;

year month day hour minute second th tm tr ta

---

2018 9 13 6 0 4.665942 8 0 unknown unk

间隔函数：

s.n 函数名称 & 描述 1 numtodsinterval(x, interval_unit); 整数x转换为一个间隔（day to second） 2 numtoyminterval(x, interval_unit); x到时间间隔（year to month）数的转换 3 to_dsinterval(x); 将字符串x时间间隔（day to second）到秒转换 4 to_yminterval(x); 将字符串x时间间隔（year to month）转换为整数

select to_yminterval(‘10001010-11’) —to_yminterval的参数要求格式必须是’xx-xx’ ,to_date(‘20110101’,‘yyyymmdd’) ,to_date(‘20110101’,‘yyyymmdd’)+to_yminterval(‘01-11’) from dual ;

select sysdate,to_dsinterval(‘01 00:00:00’)+sysdate as dd from dual select to_char(date ‘2010-02-12’, ‘d’) week_dayth,–周第几天(1-7)，星期天=1，星期一=2,星期二=3，星期三=4,星期四=5，星期五=6,星期六=7 to_char(date ‘2010-02-12’, ‘dd’) month_dayth,–月第几天 to_char(date ‘2010-02-12’, ‘ddd’) year_dayth,–年第几天 to_char(date ‘2010-02-12’, ‘day’) weekdayname,–英文星期名 to_char(date ‘2010-02-12’, ‘w’) month_weekth,–月第几周(0-4) to_char(date ‘2010-02-12’, ‘ww’) year_weekth–年第几周(0-53) from dual;

extract(data from datevalue)–返回date的某一部份内容 如果datevalue为date类型，则data可以是(year、month、day) 如果datevalue为timestamp类型，则data可以是(year、month,day、hour、minute、second)

select to_date(‘20110101’,‘yyyymmdd’), to_yminterval(‘02-08’) from dual;

pl/sql允许定义一个对象类型，这有助于在oracle的数据库中设计的面向对象。 对象类型可以包装复合类型。使用对象允许实现数据的具体结构现实世界中的对象和方法操作它。 对象有属性和方法。属性是一个对象的属性，并用于存储对象的状态;和方法被用于模拟其行为。

使用create[or replace] type语句中创建的对象。下面是一个例子，创建包含一些属性的简单的地址对象：

create or replace type address as object (house_no varchar2(10), street varchar2(30), city varchar2(20), state varchar2(10), pincode varchar2(10) ); / 当上述代码在sql提示符执行时，它产生了以下结果：

type created. 让我们来创建一个多个客户对象，包装的属性和方法，拥有面向对象的感觉：

create or replace type customer as object (code number(5), name varchar2(30), contact_no varchar2(12), addr address, member procedure display ); / 当上述代码在sql提示符执行时，它产生了以下结果：

type created. 实例化对象 定义对象类型提供了一个蓝图对象。要使用这个对象，需要创建这个对象的实例。 可以访问属性，使用实例名称和接入操作符，如下对象的方法(.)：

declare residence address; begin residence := address(‘103a’, ‘m.g.road’, ‘jaipur’, ‘rajasthan’,‘201301’); dbms_output.put_line('house no: '|| residence.house_no); dbms_output.put_line('street: '|| residence.street); dbms_output.put_line('city: '|| residence.city); dbms_output.put_line('state: '|| residence.state); dbms_output.put_line('pincode: '|| residence.pincode); end; / 当上述代码在sql提示符执行时，它产生了以下结果：

house no: 103a street: m.g.road city: jaipur state: rajasthan pincode: 201301

pl/sql procedure successfully completed.

成员方法 成员方法是用于操纵对象属性。提供的成员方法的声明，同时声明的对象类型。 对象主体限定的代码成员方法。使用create type body语句创建的对象体。

构造函数返回一个新的对象作为其值的功能。 每个对象都有一个系统定义的构造方法。构造方法的名称是相同的对象类型。例如：

residence := address(‘103a’, ‘m.g.road’, ‘jaipur’, ‘rajasthan’,‘201301’); 比较方法被用于比较的对象。有两种方法来比较对象：

映射方法：映射方法它的值取决于属性的值，以这样的方式实现函数。 例如，一个客户对象，如果客户代码是相同的两个客户，可以认为是相同的一个。所以这两个对象之间的关系将取决于代码的值。 顺序方法：顺序方法实现一些内部逻辑比较两个对象。 例如，对于矩形(rectangle)对象，如果其两侧都大，则表示矩形(rectangle)大于另一个矩形(rectangle)。

使用映射方法 让我们试着去了解上面使用下面的矩形对象的概念：

create or replace type rectangle as object (length number, width number, member function enlarge( inc number) return rectangle, member procedure display, map member function measure return number ); / 当上述代码在sql提示符执行时，它产生了以下结果：

type created. 创建类型体：

create or replace type body rectangle as member function enlarge(inc number) return rectangle is begin return rectangle(self.length + inc, self.width + inc); end enlarge;

member procedure display is begin dbms_output.put_line('length: '|| length); dbms_output.put_line('width: '|| width); end display;

map member function measure return number is begin return (sqrt(lengthlength + widthwidth)); end measure; end; / 当上述代码在sql提示符执行时，它产生了以下结果：

type body created. 现在，使用矩形（rectangle）对象及其成员函数：

declare r1 rectangle; r2 rectangle; r3 rectangle; inc_factor number := 5; begin r1 := rectangle(3, 4); r2 := rectangle(5, 7); r3 := r1.enlarge(inc_factor); r3.display;

if (r1 > r2) then – calling measure function r1.display; else r2.display; end if; end; / 当上述代码在sql提示符执行时，它产生了以下结果：

length: 8 width: 9 length: 5 width: 7

pl/sql procedure successfully completed. 使用顺序的方法 现在，相同的效果可以使用顺序方法来实现。让我们用一个顺序方法重新创建矩形（rectangle）对象：

create or replace type rectangle as object (length number, width number, member procedure display, order member function measure(r rectangle) return number ); / 当上述代码在sql提示符执行时，它产生了以下结果：

type created. 创建型类体：

create or replace type body rectangle as member procedure display is begin dbms_output.put_line('length: '|| length); dbms_output.put_line('width: '|| width); end display;

order member function measure(r rectangle) return number is begin if(sqrt(self.lengthself.length + self.widthself.width)> sqrt(r.lengthr.length + r.widthr.width)) then return(1); else return(-1); end if; end measure; end; / 当上述代码在sql提示符执行时，它产生了以下结果：

type body created. 使用矩形（rectangle）对象及其成员函数：

declare r1 rectangle; r2 rectangle; begin r1 := rectangle(23, 44); r2 := rectangle(15, 17); r1.display; r2.display; if (r1 > r2) then – calling measure function r1.display; else r2.display; end if; end; / 当上述代码在sql提示符执行时，它产生了以下结果：

length: 23 width: 44 length: 15 width: 17 length: 23 width: 44

pl/sql procedure successfully completed. 继承pl/sql对象： pl/sql允许从现有的基础对象创建对象。为了实现继承，基本对象应被声明为not final。默认值是final。

下面的程序说明了继承pl/sql对象。让我们创建一个名为tabletop，这是从rectangle对象继承。另一个对象是由基本矩形（rectangle）对象创建：

create or replace type rectangle as object (length number, width number, member function enlarge( inc number) return rectangle, not final member procedure display) not final / 当上述代码在sql提示符执行时，它产生了以下结果：

type created. 创建基本类型主体：

create or replace type body rectangle as member function enlarge(inc number) return rectangle is begin return rectangle(self.length + inc, self.width + inc); end enlarge;

member procedure display is begin dbms_output.put_line('length: '|| length); dbms_output.put_line('width: '|| width); end display; end; / 当上述代码在sql提示符执行时，它产生了以下结果：

type body created. 创建 tabletop 子对象：

create or replace type tabletop under rectangle ( material varchar2(20); overriding member procedure display ) / 当上述代码在sql提示符执行时，它产生了以下结果：

type created. 创造型体的 tabletop 子对象 ：

create or replace type body tabletop as overriding member procedure display is begin dbms_output.put_line('length: '|| length); dbms_output.put_line('width: '|| width); dbms_output.put_line('material: '|| material); end display; / 当上述代码在sql提示符执行时，它产生了以下结果：

type body created. 使用 tabletop 对象及其成员函数：

declare t1 tabletop; t2 tabletop; begin t1:= tabletop(20, 10, ‘wood’); t2 := tabletop(50, 30, ‘steel’); t1.display; t2.display; end; / 当上述代码在sql提示符执行时，它产生了以下结果：

length: 20 width: 10 material: wood length: 50 width: 30 material: steel

pl/sql procedure successfully completed. pl/sql抽象对象 not instantiable 子句允许声明一个抽象的对象。不能用一个抽象的对象因为它是抽象的; 必须要创建一个子类型或子类型，以对象使用其功能。

示例,

create or replace type rectangle as object (length number, width number, not instantiable not final member procedure display) not instantiable not final / 当上述代码在sql提示符执行时，它产生了以下结果：

type created.