11-13章

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