PL/SQL
必须在SQL*Plus中输入SET SERVEROUTPUT ON,才能看到输出的数据。
条件逻辑:
IF condition1 THEN
Statements1
ELSIF condition2 THEN
Statements2
ELSE
Statements3
END IF;
循环有三种:
1. 简单循环:
LOOP
Statements
END LOOP;
要结束简单循环,EXIT语句立即结束循环,EXIT WHEN 语句在指定条件出现时结束循环。
count :=0;
LOOP
counter := counter+1;
exit when counter=5;
end LOOP;
2. WHILE 循环
WHILE condition LOOP
Statements
END LOOP;
例子:
counter :=0;
WHILE counter <6 LOOP
counter :=counter +1;
end loop;
3.FOR循环
语法:
FOR loop_variable IN [REVERSE] lower_bound.. upper_bound LOOP
statements
END LOOP;
loop_variable 指定循环变量。可以将已经存在的变量用作循环变量,也可以让循环语句自己创建循环变量(当所指定的变量不存在时会发生这种情况)。
例子:循环变量count2 并没有显示声明,而FOR循环再这种情况下会自动创建一个INTEGER变量。
FOR count2 IN 1..5 LOOP
DBMS_OUTPUT.PUT_LINE(count2);
END LOOP;
如果使用了REVERSE,循环变量counter2会从5开始,在每次循环中递减1,最终达到1。
游标
当SELECT语句从数据库中返回的记录多于一条时,就可以使用游标
在SQL*Plus中,可以通过如下的命令运行脚本:
@c:\sql\脚本名称
游标与FOR 循环
利用FOR循环的功能,可以增强在游标中访问记录的能力。当使用FOR 循环时,可以不显示地打开和关闭游标----FOR循环会自动执行这些操作。
Begin
For 变量名字 in 游标名字
变量名字.列名字
END LOOP;
创建过程
IN | OUT| IN OUT 定义了参数的模式。每一个参数都可以选择下列模式之一:
IN 是参数的默认模式。这种模式定义的参数在程序运行的时候已经具有值,在过程体中这个值不会改变。
OUT模式定义的参数只在过程体内部赋值。
IN OUT 模式定义的参数当过程运行时可能已经具有值,但是在过程体中也可以修改。
调用存储过程:Call 存储过程名称
从user_procedures视图中可以获得有关过程的信息。从all_procedures视图中可以获得可以访问的所有过程的信息。
删除过程
DROP PROCEDURE 存储过程名称
查看过程中的错误
如果在创建过程或函数时数据库报告出错,可以通过SHOW ERRORS 命令查看其中的错误。
然后使用EDIT命令可以修改这个错误,然后输入 /重新运行。
函数与过程很相似,唯一区别是函数必须向调用它的运距返回一个值。存储过程和函数有时合起来称为存储子程序(stored subprogram)。
返回值是在RETURN子句中指定的一个PL/SQL类型的值。
创建函数
Create or replace function circle_area(
v_a in number
) return number as
定义变量
BEGIN
函数体
Return v_area;
End ;
从user_procedures视图中可以获得有关函数的信息。
删除函数:
Drop function 函数名
包
包通常由两部分组成:规范和包体
规范里的过程和函数是公有对象
包体里的过程和函数是私有对象
规范中列出的过程和函数可被外部访问,但是只在包体中包含的过程和函数只能被包自身访问。
删除包:DROP PACKAGE 包名称
触发器可以在SQL语句运行前和之后激活。
行级触发器
语句级触发器
创建触发器
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF] trigger_event
ON table_name
[FOR EACH ROW [WHEN trigger_condition]]
BEGIN
Trigger_body
END trigger_name;
创建对象类型,CREATE [OR REPLACE] TYPE语句用于创建对象类型。
注意在对象类型名的末尾添加_typ。这事标准命名。
Create type product_typ as object(
Id number,
days_valid number,
Member function get_sell_by_date return date
);
由于product_type中包含了方法的声明,所有必须为product_typ创建对象体(body)。
Create type body product_typ as
Member function get_sell_by_date return date is
v_sell_by_date date;
begin
select days_valid +(sysdate) into v_sell_by_date from dual;
return v_sell_by_date ;
end;
end;
set describe depth 可以设置describe 显示信息的深度
用对象类型定义列对象和对象表
对象类型可以用来定义表中的列,这种列称为列对象。
下面这个例子创建表products,其中包含一个product_typ列对象。
Create table products(
Product product_typ,
Quantity_in_stock number
);
也可以使用对象类型定义整个表,这种表称为对象表。
Create table object_products of product_typ;
注意OF子句用于将每个表标识为对象表
对象表中的每个对象都具有唯一的对象标识符(OID)
Create table purchases(
Id number primary key,
Customer ref person_typ scope is object_customers,
Product ref product_typ scope is object_products
);
Scope is 子句将对象引用限制在特定表中的对象上,例如product 列限定在指向object_products表中的对象。
向包含列队象的表中插入一条记录时,必须使用构造函数为该对象提供属性值
insert into products (
product ,
quantity_in_stock) values(
product_typ(1,10),
50
);
查询记录
可以从表中查询单个列对象,要实现这种功能,必须为查询对象的表提供别名。
下面这个例子从products 表中查询单个product列对象。
Select p.product
From product p
Where p.product.id=1;
也可以通过表别名调用get_sell_by_date()
Select p.product.get_sell_by_date()
From products p;
修改products 表中国的记录
Update products p
Set p.product.description=’30 oz bag of pasta’
Where p.product.id=1;
从products表中删除记录
DELECT FROM products p
Where p.product.id=2;
向purchases表中插入记录
对象表中的每一条记录都有唯一的对象标识符,这个标识符可保存在REF列中。可通过REF()函数访问这个对象标识符,并将返回的对象标识符保存在REF列中。下面这个例子想purchases表中插入一条记录。注意REF()函数用于从object_customers和object_products表中读取记录的对象标识符:
Insert into purchases(
Id,
Customer,
product) values(
1,(select ref(oc) from object_customers oc where oc.id=1),
(select ref(op) from object_products op where op.id=1)
);
在PL/SQL中使用对象
类型继承
对于被继承的类而言,它的定义中必须使用NOT FINAL子句
Create type person_typ as object(
Id number) not final;
默认状态是FINAL
为了让新类型从已有的类型中继承属性和方法,可以在定义新类型时使用UNDER子句。
Create type business_perosn_typ under person(
Title varchar2(20),
Company varchar2(20));
NOT INSTANTIABLE
对象类型可以标记为NOT INSTANTIABLE ,这样可以防止创建该类型的对象实例。
用户可以定义自己的构造函数,用于初始化对象的属性。可以定义自己的构造函数来实现一个设置,比如编程设置某个对象的一个或者多个属性的默认值。
Create or replace type person_typ2 as object(
Id number,
Constructor function person_typ2(
P_id number) return self as result
);
构造函数的签名不包含构造函数的实际代码体;代码包含在下面的语句中:
Create or replace type body person_typ2 as
Constructor function person_typ2(
P_id number) return self as result is
Begin
Self.id:=p_id;
Return ;
End;
);
集合有三种类型:
变长数组,只有一个维度,可以存储有序的元素集合,每个元素有一个索引。
嵌套表:它是嵌套在另一个表中的表。
关联数组:它是一个键值对集合。
变长数组:使用变长数组存储一个有序元素集合,每个元素有一个索引,该索引对应元素在数组中的位置。变长数组存在大小限制,但可以动态更改。
创建变长数组类型:
Create type varray_address_typ as varray(2) of varchar2(50);
使用变长数组类型定义表列:
Create table customers_with_array(
Id integer primary key,
first_name va rchar2(10),
last_name varchar2(10),
addresses array_address_typ
);
嵌套表是任意数量元素的一个有序集合,所有元素是同一数据类型。
Create type address_typ as object(
Street varchar2(10),
City varchar2(10));
Create type nested_table_address_typ as table of address_typ;
使用嵌套表类型定义表列
Create table customers_with_nested_table(
Id integer primary key,
First_name varchar2(10),
last_name varchar2(10),
addresses nested_table_address_typ
)
Nested table
Addresses
Store as
Nested_addresses;
操作变长数组:
Create or replace package varray_package as
Type ref_cursor _typ is ref cursor;
Function get_customers return ref_cursor_typ;
Procedure insert_customer(
P_id in customers_with_varray.id%type,
P_first_name in customers_with_varray. P_first_name %type );
Create or replace package body varray_package as
Function get_customers return ref_cursor_typ is
Begin
Open customers_ref_cursor for
Select * from customers_with_varray;
Return customers_ref_cursor;
End get_customers;
Procedure insert_customer(
P_id in customers_with_varray.id%type,
P_first_name in customers_with_varray. P_first_name %type ) is
Begin
Insert into customers_with_varray
Values(p_id,p_first_name);
Commit;
Exception
When others then
Rollback;
End insert_customer;
End varray_package
必须在SQL*Plus中输入SET SERVEROUTPUT ON,才能看到输出的数据。
条件逻辑:
IF condition1 THEN
Statements1
ELSIF condition2 THEN
Statements2
ELSE
Statements3
END IF;
循环有三种:
1. 简单循环:
LOOP
Statements
END LOOP;
要结束简单循环,EXIT语句立即结束循环,EXIT WHEN 语句在指定条件出现时结束循环。
count :=0;
LOOP
counter := counter+1;
exit when counter=5;
end LOOP;
2. WHILE 循环
WHILE condition LOOP
Statements
END LOOP;
例子:
counter :=0;
WHILE counter <6 LOOP
counter :=counter +1;
end loop;
3.FOR循环
语法:
FOR loop_variable IN [REVERSE] lower_bound.. upper_bound LOOP
statements
END LOOP;
loop_variable 指定循环变量。可以将已经存在的变量用作循环变量,也可以让循环语句自己创建循环变量(当所指定的变量不存在时会发生这种情况)。
例子:循环变量count2 并没有显示声明,而FOR循环再这种情况下会自动创建一个INTEGER变量。
FOR count2 IN 1..5 LOOP
DBMS_OUTPUT.PUT_LINE(count2);
END LOOP;
如果使用了REVERSE,循环变量counter2会从5开始,在每次循环中递减1,最终达到1。
游标
当SELECT语句从数据库中返回的记录多于一条时,就可以使用游标
在SQL*Plus中,可以通过如下的命令运行脚本:
@c:\sql\脚本名称
游标与FOR 循环
利用FOR循环的功能,可以增强在游标中访问记录的能力。当使用FOR 循环时,可以不显示地打开和关闭游标----FOR循环会自动执行这些操作。
Begin
For 变量名字 in 游标名字
变量名字.列名字
END LOOP;
创建过程
IN | OUT| IN OUT 定义了参数的模式。每一个参数都可以选择下列模式之一:
IN 是参数的默认模式。这种模式定义的参数在程序运行的时候已经具有值,在过程体中这个值不会改变。
OUT模式定义的参数只在过程体内部赋值。
IN OUT 模式定义的参数当过程运行时可能已经具有值,但是在过程体中也可以修改。
调用存储过程:Call 存储过程名称
从user_procedures视图中可以获得有关过程的信息。从all_procedures视图中可以获得可以访问的所有过程的信息。
删除过程
DROP PROCEDURE 存储过程名称
查看过程中的错误
如果在创建过程或函数时数据库报告出错,可以通过SHOW ERRORS 命令查看其中的错误。
然后使用EDIT命令可以修改这个错误,然后输入 /重新运行。
函数与过程很相似,唯一区别是函数必须向调用它的运距返回一个值。存储过程和函数有时合起来称为存储子程序(stored subprogram)。
返回值是在RETURN子句中指定的一个PL/SQL类型的值。
创建函数
Create or replace function circle_area(
v_a in number
) return number as
定义变量
BEGIN
函数体
Return v_area;
End ;
从user_procedures视图中可以获得有关函数的信息。
删除函数:
Drop function 函数名
包
包通常由两部分组成:规范和包体
规范里的过程和函数是公有对象
包体里的过程和函数是私有对象
规范中列出的过程和函数可被外部访问,但是只在包体中包含的过程和函数只能被包自身访问。
删除包:DROP PACKAGE 包名称
触发器可以在SQL语句运行前和之后激活。
行级触发器
语句级触发器
创建触发器
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF] trigger_event
ON table_name
[FOR EACH ROW [WHEN trigger_condition]]
BEGIN
Trigger_body
END trigger_name;
创建对象类型,CREATE [OR REPLACE] TYPE语句用于创建对象类型。
注意在对象类型名的末尾添加_typ。这事标准命名。
Create type product_typ as object(
Id number,
days_valid number,
Member function get_sell_by_date return date
);
由于product_type中包含了方法的声明,所有必须为product_typ创建对象体(body)。
Create type body product_typ as
Member function get_sell_by_date return date is
v_sell_by_date date;
begin
select days_valid +(sysdate) into v_sell_by_date from dual;
return v_sell_by_date ;
end;
end;
set describe depth 可以设置describe 显示信息的深度
用对象类型定义列对象和对象表
对象类型可以用来定义表中的列,这种列称为列对象。
下面这个例子创建表products,其中包含一个product_typ列对象。
Create table products(
Product product_typ,
Quantity_in_stock number
);
也可以使用对象类型定义整个表,这种表称为对象表。
Create table object_products of product_typ;
注意OF子句用于将每个表标识为对象表
对象表中的每个对象都具有唯一的对象标识符(OID)
Create table purchases(
Id number primary key,
Customer ref person_typ scope is object_customers,
Product ref product_typ scope is object_products
);
Scope is 子句将对象引用限制在特定表中的对象上,例如product 列限定在指向object_products表中的对象。
向包含列队象的表中插入一条记录时,必须使用构造函数为该对象提供属性值
insert into products (
product ,
quantity_in_stock) values(
product_typ(1,10),
50
);
查询记录
可以从表中查询单个列对象,要实现这种功能,必须为查询对象的表提供别名。
下面这个例子从products 表中查询单个product列对象。
Select p.product
From product p
Where p.product.id=1;
也可以通过表别名调用get_sell_by_date()
Select p.product.get_sell_by_date()
From products p;
修改products 表中国的记录
Update products p
Set p.product.description=’30 oz bag of pasta’
Where p.product.id=1;
从products表中删除记录
DELECT FROM products p
Where p.product.id=2;
向purchases表中插入记录
对象表中的每一条记录都有唯一的对象标识符,这个标识符可保存在REF列中。可通过REF()函数访问这个对象标识符,并将返回的对象标识符保存在REF列中。下面这个例子想purchases表中插入一条记录。注意REF()函数用于从object_customers和object_products表中读取记录的对象标识符:
Insert into purchases(
Id,
Customer,
product) values(
1,(select ref(oc) from object_customers oc where oc.id=1),
(select ref(op) from object_products op where op.id=1)
);
在PL/SQL中使用对象
类型继承
对于被继承的类而言,它的定义中必须使用NOT FINAL子句
Create type person_typ as object(
Id number) not final;
默认状态是FINAL
为了让新类型从已有的类型中继承属性和方法,可以在定义新类型时使用UNDER子句。
Create type business_perosn_typ under person(
Title varchar2(20),
Company varchar2(20));
NOT INSTANTIABLE
对象类型可以标记为NOT INSTANTIABLE ,这样可以防止创建该类型的对象实例。
用户可以定义自己的构造函数,用于初始化对象的属性。可以定义自己的构造函数来实现一个设置,比如编程设置某个对象的一个或者多个属性的默认值。
Create or replace type person_typ2 as object(
Id number,
Constructor function person_typ2(
P_id number) return self as result
);
构造函数的签名不包含构造函数的实际代码体;代码包含在下面的语句中:
Create or replace type body person_typ2 as
Constructor function person_typ2(
P_id number) return self as result is
Begin
Self.id:=p_id;
Return ;
End;
);
集合有三种类型:
变长数组,只有一个维度,可以存储有序的元素集合,每个元素有一个索引。
嵌套表:它是嵌套在另一个表中的表。
关联数组:它是一个键值对集合。
变长数组:使用变长数组存储一个有序元素集合,每个元素有一个索引,该索引对应元素在数组中的位置。变长数组存在大小限制,但可以动态更改。
创建变长数组类型:
Create type varray_address_typ as varray(2) of varchar2(50);
使用变长数组类型定义表列:
Create table customers_with_array(
Id integer primary key,
first_name va rchar2(10),
last_name varchar2(10),
addresses array_address_typ
);
嵌套表是任意数量元素的一个有序集合,所有元素是同一数据类型。
Create type address_typ as object(
Street varchar2(10),
City varchar2(10));
Create type nested_table_address_typ as table of address_typ;
使用嵌套表类型定义表列
Create table customers_with_nested_table(
Id integer primary key,
First_name varchar2(10),
last_name varchar2(10),
addresses nested_table_address_typ
)
Nested table
Addresses
Store as
Nested_addresses;
操作变长数组:
Create or replace package varray_package as
Type ref_cursor _typ is ref cursor;
Function get_customers return ref_cursor_typ;
Procedure insert_customer(
P_id in customers_with_varray.id%type,
P_first_name in customers_with_varray. P_first_name %type );
Create or replace package body varray_package as
Function get_customers return ref_cursor_typ is
Begin
Open customers_ref_cursor for
Select * from customers_with_varray;
Return customers_ref_cursor;
End get_customers;
Procedure insert_customer(
P_id in customers_with_varray.id%type,
P_first_name in customers_with_varray. P_first_name %type ) is
Begin
Insert into customers_with_varray
Values(p_id,p_first_name);
Commit;
Exception
When others then
Rollback;
End insert_customer;
End varray_package