1. &与&&的区别:
一个&的意思 是每次碰到这个变量,都问问你是啥
两个&,那么只问你一次,以后这个变量都按这个值处理
2.声明常量
eg:
3. if语句
4.case语句
a.case
case selector
when expression 1 then statement 1;
when expression 2 then statement 2;
...
when expression n then statement n;
else statement n+1;
end case;
eg:
b.搜索式case语句
c.case表达式:与case语句的差别是:end case改为end即可
5.nullif与coalesce
nullif(expression1,expression2):如果expression1等于expression2,则nullif返回null。如果expression1不等于expression2,nullif函数返回expression1;
nullif函数存在一个限制:不能把字面值null赋予expression1.
nvl()将空值转换为指定的值nvl(exp1,exp2),第一个为null,取第二个
nvl2()将空值转换为指定的值nvl2(exp1,exp2,exp3) ,第一个为null,取第二个,第二个也为null,取第三个
coalesce(expression1,expression2,.....,expressionn)
6.迭代控制--------loop
a. loop(方式一:通过exit语句)
b.loop(方式二:通过exit when 语句)
7.迭代控制--------while
a.普通while语句
b.含exit的while语句
c.含exit when的while语句
8.迭代控制------数值型for循环
其中:reverse为从大到小,默认为从小到大,另: loop_counter不能被其他语句使用,它只在循环时作为一个计数器
a. 普通的数值型for循环
b.含exit语句的数值型for循环
c.含exit语句的数值型for循环
9.迭代控制------continue语句
只有当处于循环中时,continue和continue when语句才会有效,当处于循环之外时,会带来语法错误。为避免这个错误,可以使用return语句。
continue和continue when语句适用于所有类型的循环
a. 与exit类似的:
b.与exit when类似:
continue when continue_condition;
10.显式游标--低级
处理显式游标的步骤:
a.声明游标:在内存中建立游标的初始化环境
eg:
declare cursor c_student is select firstname||' '||last_name name from student
注:声明记录类型: vr_student c_student%rowtype .与游标c_student的类型一致
b.打开游标:打开被声明的游标,并分配内存
eg:
open c_student
c.检索游标:从被声明与打开的游标检索数据
eg:
方式一:
方式二:
d.关闭游标:释放所分配的内存
close c_student;
显式游标属性:
11.显式游标----高级
a.传参
eg:
传参方式:
open c_zip('ny')
或者
for r_zip in c_zip('ny')
loop
.....
b. for update:当希望使用游标来更新数据库的表时,只能使用for update字句。目的是锁定希望更新的数据库表中数据行
语法: for update of <item_name>
eg:
1.declare cusrsor c_course is select course_no,cost from course for update
2.declare cusrsor c_course is select course_no,cost from course for update of phone 只更新表course中的phone字段
c.where current of 与 for current of
当希望更新最新检索的数据行时,可以使用for current of。只能在for update of 游标中使用where current of 。where current of字句的好处是,免于在upadate语句中使用where字句。
eg:
13.引用游标
12.触发器
create [or replace] trigger trigger_name
{before|after} triggering_event on table_name
[referencing new|old as new_record|old_record]
[for each row]
[follows another_trigger]
[enable/disable]
[when condition]
declare
declaration statements
begin
executable statements
exception
exception-handling statements
end;
:new为引用最新的列值
:old为引用以前的列值
以上两个变量只有在使用了for each row的时候才有效,update可以使用:new与:old,insert只可以使用:new,delete只可以使用:old
----
两类:
行触发器和语句触发器
使用instead of 触发器:定义与数据库视图的
eg:
13.复合触发器
eg:
14.集合
a.联合数组
type type_name is table of element_type [not null] index by element_type;
table_name type_name;
eg:
b.嵌套数组
type type_name is table of element_type [not null];
table_name type_name;
注:必须先初始化嵌套表
eg:
c.集合的方法
如果某特定元素存在于集合中,则exists会返回true。该方法可用于避免subscript_outside_limit异常
count返回集合中元素的数量
extend会扩展集合的规模
delete会删除集合中所有元素,指定范围的元素,或者特定元素。注意,pl/sql会保存删除元素的占位符
first 和 last 会返回集合中第一个和最后一个元素的下标。注意,如果嵌套表的第一个元素被删除,则first方法会返回大于1的值。如果嵌套表删除
一个中间元素,则last方法的返回值会大于count方法的返回值
prior和next会返回指定集合下标的前序和后续下标。
trim会从集合的末尾删除一个,或者指定数量的元素。注意,pl/sql不会保存被删除元素的占位符。
eg:
d.变长数组
type type_name is {varray | varying array} (size_limit) of element_type [not null];
varray_name type_name;
注:类似于嵌套表,当变长数组被声明时,自动设置为null。必须在引用单个元素之前,初始化变长数组。
不能对变长数组使用delete方法
eg:
e.多层集合
eg:
15.记录
a.基于表的记录 使用%rowtype属性可以创建基于表和基于游标的记录
eg:
b.基于游标的记录
注:基于游标的记录依赖于特定的游标,不能在游标之前声明
eg:
c.用户定义的记录
语法如下:
type type_name is record
(filed_name1 datatype1 [not null] [ := default expression],
filed_name2 datatype2 [not null] [ := default expression],
...
filed_namen datatypen [not null] [ := default expression]);
record_name type_name;
d.记录兼容性
i>.对于用户定义的记录
eg:
解释:虽然name_type1与name_type2结构一样但是不能直接将name_rec1的值赋给name_rec2(即不可:name_rec2=name_rec1),应该通过但值赋值(即:name_rec2.first_name=name_rec1.first_name)
ii>.对于基于表与游标的记录
对于基于表与游标的记录赋予用户定义类型,只要两者具有相同的结构即可
e.嵌套记录 记录中含有记录或者集合
eg:
f.记录的集合
eg:
16.动态sql
语法结构:
execute immediate dynamic_sql_string
[into defined_variable1,defined_variable2,...]
[using [in | out |in out] bind_argument1,bind_argument2,...]
[{returning | return} field1,field2,... into bind_argument1,bind_argument2,...]
注:如果不指定using的参数的任何模式,using子句中所列出的所有绑定参数都是in模式的。
当execute immediate语句包含using和returning into子句时,using子句只指定in参数
动态sql语句的结尾不应该是分号(;),类似地,动态pl/sql语句块的结尾不能是右斜线(/)
17.批量sql
a. forall语句
forall loop_counter in bounds_clause
sql_statement [save exception];
其中,bounds_clause是下面形式之一:
lower_limit..upper_limit
indices of collection_name between lower_limit..upper_limit
values of collection_name
注:indices of子句所引用的集合也许是稀疏的,即有些元素可能已经被删除
如果values of子句中所使用的集合是联合数组,它必须使用pls_integer和binary_integer进行索引
values of子句中所使用集合的元素必须是pls_integer或者binary_integer
当values of子句所引用的集合是空时,forall语句会异常
eg:
i>.save exception选项
此选项能够实现:即使当对应的sql语句导致异常,forall语句仍旧能够继续执行。所产生的异常被存储在名为sql%bulk_exceptions的游标属性中。sql%bulk_exception游标属性是一个记录集合,有两个字段组成:error_index和error_code。error_index字段会存储发生异常的forall语句的迭代编号,error_code会存储对应于所抛出异常的oracle错误代码,可以根据sqlerrm函数查询
错误码对应的错误信息
eg:
由于删除1,5,7三个元素,故forall会迭代7次
iii>.values of 选项 values of 可指明forall语句中循环计数器的值来自于所指定集合中元素的值
b.bulk collect语句 bulk collect会检索多行数据,这些数据行存储在集合变量中
eg:
17.存储过程
语法如下:
create [or replace] procedure name
[(parameter[,parameter,...])]
as
[local declarations]
begin
executable statements
[exception
exception handlers]
end [name];
18.函数
语法如下:
create [or replace] function function_name
[parameter...]
return datatype
is
begin
<body>
return [return_value]
end;
19.包
a.包规范语法:
b.包体语法:
eg:
20.使用restrict_references编译指令实现纯度等级
语法如下:
pragma restrict_references( function_name,wnds [,wnps] [,rnds] [,rnps])
注:该函数必须存储在数据库中,而不是存储在oracle工具的库中
函数必须是行函数,而不是列函数或分组函数
对于所有函数(不管是否在sql语句中使用),参数必须属于in模式
eg:
wnds:write no database state,不会修改数据库表
wnps:write no package state,不会修改任何包变量的值
rnps:read no package state,不会读取任何包变量
rnds:read no database state,不会读取任何数据库表
21.在包中重载:
规则如下:
a.形参必须在参数数量、顺序或者数据类型等方面存在区别
b.不能重载独立模块的名称
c.只有return数据类型存在差别的函数不能重载
22.对象类型
a.语法如下:
create [or replace] type type_name as object
(attribute_name1 attribute_type,
attribute_name2 attribute_type,
...
attribute_namen attribute_type,
[method1 specification],
[method2 specification],
...
[methodn specification],
);
[create [or replace] type body type_name as
method1 body;
method2 body;
...
methodn body;]
end;
eg1:
调用:
declare
v_test_type test_type;
begin
select test_type(no,name,null) into v_test_type from student;
end;
另对于未初始化对象,对象实例及其属性都是null,在使用默认构造器对该对象实例进行初始化之后,就不再是null,尽管单个值可能仍旧是null,eg:v_test_type :=v_test_type(null,null,null)
eg2:
create or replace type test_tab_type is table of test_type;
b.对象类型方法
对象类型有个内置参数self,这个参数表示对象类型的特定实例。
对象比较的两种方式:映射方法与排序方法(二选一)
构造器方法,成员方法,静态方法
eg:
create or replace type zipcode_obj_type as object
( zip varchar2(5),city varchar2(20),state varchar2(2),
--构造器方法,需要两个参数
constructor function zipcode_obj_type
( self in out nocopy zipcode_obj_type,zip varchar2)
return self as result,
--成员方法
member procedure get_zipcode_info
(out_zip out varchar2,out_city out varchar2),
---静态方法
static procedure display_zipcode_info
(in_zip_obj in zipcode_obj_type),
--映射方法
map member function zipcode return varchar2,
--排序方法
order member function zipcode1(zip_obj zipcode_obj_type) return integer
);
/
create or replace type body zipcode_obj_type as
constructor function zipcode_obj_type
(self in out nocopy zipcode_obj_type,zip varchar2)
return self as result
is
begin
self.zip:=zip;
select city into self.city where zip=self.zip;
return;
exception
when no_data_found then
return;
end;
member procedure get_zipcode_info
(out_zip out varchar2,out_city out varchar2)
is
begin
out_zip := self.zip;
out_city :=self.city;
end;
static procedure display_zipcode_info
(in_zip_obj in zipcode_obj_type)
is
begin
dbms_output.put_line('zip: '||in_zip_obj.zip);
end;
map member function zipcode return varchar2
is
begin
return (zip)
end;
order member function zipcode1(zip_obj zipcode_obj_type) return integer
is
begin
if zip<zip_obj.zip then return -1;
elsif zip=zip_obj.zip then return 0;
elsif zip>zip_obj.zip then return 1;
end if;
end;
end;
/
备注:
1,调用 map member function zipcode的方式为:if v_zip_obj1 >v_zip_obj2 then ... end if;
2,调用 order member function zipcode1的方式为 v_result :=v_zip_obj1.zipcode1(v_zip_obj2);