📄 第十章 pl-sql对象类型 - pl-sql用户指南与参考 - whatiswhat.htm
字号:
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>CREATE</STRONG> [<STRONG>OR</STRONG> REPLACE] <STRONG>TYPE</STRONG> type_name<BR> [<STRONG>AUTHID</STRONG> {CURRENT_USER | DEFINER}]<BR> { {<STRONG>IS</STRONG> | <STRONG>AS</STRONG>} OBJECT | UNDER supertype_name }<BR>(<BR> attribute_name datatype[, attribute_name datatype]...<BR> [{MAP | <STRONG>ORDER</STRONG>} MEMBER function_spec,]<BR> [{FINAL| <STRONG>NOT</STRONG> FINAL} MEMBER function_spec,]<BR> [{INSTANTIABLE| <STRONG>NOT</STRONG> INSTANTIABLE} MEMBER function_spec,]<BR> [{MEMBER | STATIC} {subprogram_spec | call_spec}<BR> [, {MEMBER | STATIC} {subprogram_spec | call_spec}]...]<BR>) [{FINAL| <STRONG>NOT</STRONG> FINAL}] [ {INSTANTIABLE| <STRONG>NOT</STRONG> INSTANTIABLE}];<BR>[<STRONG>CREATE</STRONG> [<STRONG>OR</STRONG> REPLACE] <STRONG>TYPE</STRONG> <STRONG>BODY</STRONG> type_name {<STRONG>IS</STRONG> | <STRONG>AS</STRONG>}<BR> { {MAP | <STRONG>ORDER</STRONG>} MEMBER function_body;<BR> | {MEMBER | STATIC} {subprogram_body | call_spec};}<BR> [{MEMBER | STATIC} {subprogram_body | call_spec};]...<BR><STRONG>END</STRONG>;]
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P class=title2>1、PL/SQL类型继承一览</P>
<P>PL/SQL支持单继承模式。我们可以定义对象类型的子类型。这些子类型包括父类型(或超类)所有的属性和方法。子类型还可以包括额外的属性和方法，并可以覆盖超类的方法。
</P>
<P>我们还可以定义子类是否能继承于某个特定的类型。我们也可以定义不能直接初始化的类型和方法，只有声明它们的子类才可以进行初始化操作。
</P>
<P>有些类型属性可以用ALTER TYPE语句动态的改变。当基类发生变化时，无论是用ALTER
TYPE语句还是重新定义基类，子类会自动的应用这些改变的内容。我们可以用TREAT操作符只返回某一个指定的子类的对象。
</P>
<P>从REF和DEREF函数中产生的值可以代表声明过的表或视图类型，或是一个或多个它的子类型。 </P>
<UL>
<LI>PL/SQL类继承举例 </LI></UL>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><EM>-- Create a supertype from which several subtypes will be derived.</EM><BR><BR><STRONG>CREATE</STRONG> <STRONG>TYPE</STRONG> person_typ <STRONG>AS</STRONG> OBJECT(<BR> ssn <STRONG>NUMBER</STRONG>,<BR> NAME <STRONG>VARCHAR2</STRONG>(30),<BR> address <STRONG>VARCHAR2</STRONG>(100)<BR>)<BR><STRONG>NOT</STRONG> FINAL;<BR><BR><EM>-- Derive a subtype that has all the attributes of the supertype,</EM><BR><EM>-- plus some additional attributes.</EM><BR><BR><STRONG>CREATE</STRONG> <STRONG>TYPE</STRONG> student_typ UNDER person_typ(<BR> deptid <STRONG>NUMBER</STRONG>,<BR> major <STRONG>VARCHAR2</STRONG>(30)<BR>)<BR><STRONG>NOT</STRONG> FINAL;<BR><BR><EM>-- Because Student_typ is declared NOT FINAL, you can derive</EM><BR><EM>-- further subtypes from it.</EM><BR><BR><STRONG>CREATE</STRONG> <STRONG>TYPE</STRONG> parttimestudent_typ UNDER student_typ(<BR> numhours <STRONG>NUMBER</STRONG><BR>)<BR>;<BR><BR><EM>-- Derive another subtype. Because it has the default attribute</EM><BR><EM>-- FINAL, you cannot use Employee_typ as a supertype and derive</EM><BR><EM>-- subtypes from it.</EM><BR><BR><STRONG>CREATE</STRONG> <STRONG>TYPE</STRONG> employee_typ UNDER person_typ(<BR> empid <STRONG>NUMBER</STRONG>,<BR> mgr <STRONG>VARCHAR2</STRONG>(30)<BR>)<BR>;<BR><BR><EM>-- Define an object type that can be a supertype. Because the</EM><BR><EM>-- member function is FINAL, it cannot be overridden in any</EM><BR><EM>-- subtypes.</EM><BR><STRONG>CREATE</STRONG> <STRONG>TYPE</STRONG> T <STRONG>AS</STRONG> OBJECT (..., MEMBER <STRONG>PROCEDURE</STRONG> Print(), FINAL MEMBER<BR><STRONG>FUNCTION</STRONG> foo(x <STRONG>NUMBER</STRONG>)...) <STRONG>NOT</STRONG> FINAL;<BR><EM>-- We never want to create an object of this supertype. By using</EM><BR><EM>-- NOT INSTANTIABLE, we force all objects to use one of the subtypes</EM><BR><EM>-- instead, with specific implementations for the member functions.</EM><BR><STRONG>CREATE</STRONG> <STRONG>TYPE</STRONG> Address_typ <STRONG>AS</STRONG> OBJECT(...) <STRONG>NOT</STRONG> INSTANTIABLE <STRONG>NOT</STRONG> FINAL;<BR><EM>-- These subtypes can provide their own implementations of</EM><BR><EM>-- member functions, such as for validating phone numbers and</EM><BR><EM>-- postal codes. Because there is no "generic" way of doing these</EM><BR><EM>-- things, only objects of these subtypes can be instantiated.</EM><BR><STRONG>CREATE</STRONG> <STRONG>TYPE</STRONG> USAddress_typ UNDER Address_typ(...);<BR><STRONG>CREATE</STRONG> <STRONG>TYPE</STRONG> IntlAddress_typ UNDER Address_typ(...);<BR><EM>-- Return REFs for those Person_typ objects that are instances of</EM><BR><EM>-- the Student_typ subtype, and <STRONG>NULL</STRONG> REFs otherwise.</EM><BR><STRONG>SELECT</STRONG> TREAT(<STRONG>REF</STRONG>(p) <STRONG>AS</STRONG> <STRONG>REF</STRONG> student_typ)<BR> <STRONG>FROM</STRONG> person_v p;<BR><EM>-- Example of using TREAT for assignment...</EM><BR><EM>-- Return REFs for those Person_type objects that are instances of</EM><BR><EM>-- Employee_type or Student_typ, or any of their subtypes.</EM><BR><STRONG>SELECT</STRONG> <STRONG>REF</STRONG>(p)<BR> <STRONG>FROM</STRONG> person_v p<BR> <STRONG>WHERE</STRONG> VALUE(p) <STRONG>IS</STRONG> <STRONG>OF</STRONG>(employee_typ, student_typ);<BR><EM>-- Similar to above, but do not allow any subtypes of Student_typ.</EM><BR><STRONG>SELECT</STRONG> <STRONG>REF</STRONG>(p)<BR> <STRONG>FROM</STRONG> person_v p<BR> <STRONG>WHERE</STRONG> VALUE(p) <STRONG>IS</STRONG> <STRONG>OF</STRONG>(ONLY student_typ);<BR><EM>-- The results of REF and DEREF can include objects of Person_typ</EM><BR><EM>-- and its subtypes such as Employee_typ and Student_typ.</EM><BR><STRONG>SELECT</STRONG> <STRONG>REF</STRONG>(p)<BR> <STRONG>FROM</STRONG> person_v p;<BR><STRONG>SELECT</STRONG> DEREF(<STRONG>REF</STRONG>(p))<BR> <STRONG>FROM</STRONG> person_v p;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P class=title2>2、对象类型实例：栈</P>
<P>栈是一个有序集合。栈有一个栈顶和一个栈底。栈中的每一项都只能在栈顶添加或删除。所以，最后一个被加入栈的项会被最先删除。(可以把栈想象成自助餐厅中的盘子。)压栈和退栈操作能够对栈进行后进先出(LIFO)更新。
</P>
<P>栈能应用在很多地方。例如，它们可以用在系统编程中控制中断优先级并对递归进行管理。最简单的栈实现就是使用整数数组，数组的一端代表了栈顶。
</P>
<P>PL/SQL提供了VARRAY数据类型，它能让我们声明变长数组。要声明变长数组属性，必须先定义变长数组类型。但是，我们不能再对象说明中定义类型，所以，我们只能单独的定义变长数组类型，并指定它的最大长度，具体实现如下：
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>CREATE</STRONG> <STRONG>TYPE</STRONG> IntArray <STRONG>AS</STRONG> VARRAY(25) <STRONG>OF</STRONG> <STRONG>INTEGER</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>现在我们可以编写对象类型说明了：</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>CREATE</STRONG> <STRONG>TYPE</STRONG> stack <STRONG>AS</STRONG> OBJECT(<BR> max_size <STRONG>INTEGER</STRONG>,<BR> top <STRONG>INTEGER</STRONG>,<BR> POSITION intarray,<BR> MEMBER <STRONG>PROCEDURE</STRONG> initialize,<BR> MEMBER <STRONG>FUNCTION</STRONG> FULL<BR> <STRONG>RETURN</STRONG> <STRONG>BOOLEAN</STRONG>,<BR> MEMBER <STRONG>FUNCTION</STRONG> empty<BR> <STRONG>RETURN</STRONG> <STRONG>BOOLEAN</STRONG>,<BR> MEMBER <STRONG>PROCEDURE</STRONG> push(n <STRONG>IN</STRONG> <STRONG>INTEGER</STRONG>),<BR> MEMBER <STRONG>PROCEDURE</STRONG> pop(n <STRONG>OUT</STRONG> <STRONG>INTEGER</STRONG>)<BR>);
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>最后，我们可以编写对象类型体：</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>CREATE</STRONG> <STRONG>TYPE</STRONG> <STRONG>BODY</STRONG> stack <STRONG>AS</STRONG><BR> MEMBER <STRONG>PROCEDURE</STRONG> initialize <STRONG>IS</STRONG><BR> <STRONG>BEGIN</STRONG><BR> top := 0;<BR> <EM>/* Call constructor for varray and set element 1 to NULL. */</EM><BR> POSITION := intarray(<STRONG>NULL</STRONG>);<BR> max_size := POSITION.LIMIT; <EM>-- get varray size constraint</EM><BR> POSITION.EXTEND(max_size - 1, 1); <EM>-- copy element 1 into 2..25</EM><BR> <STRONG>END</STRONG> initialize;<BR> MEMBER <STRONG>FUNCTION</STRONG> FULL<BR> <STRONG>RETURN</STRONG> <STRONG>BOOLEAN</STRONG> <STRONG>IS</STRONG><BR> <STRONG>BEGIN</STRONG><BR> <STRONG>RETURN</STRONG>(top = max_size); <EM>-- return TRUE if stack is full</EM><BR> <STRONG>END</STRONG> FULL;<BR> MEMBER <STRONG>FUNCTION</STRONG> empty<BR> <STRONG>RETURN</STRONG> <STRONG>BOOLEAN</STRONG> <STRONG>IS</STRONG><BR> <STRONG>BEGIN</STRONG><BR> <STRONG>RETURN</STRONG>(top = 0); <EM>-- return TRUE if stack is empty</EM><BR> <STRONG>END</STRONG> empty;<BR> MEMBER <STRONG>PROCEDURE</STRONG> push(n <STRONG>IN</STRONG> <STRONG>INTEGER</STRONG>) <STRONG>IS</STRONG><BR> <STRONG>BEGIN</STRONG><BR> <STRONG>IF</STRONG> <STRONG>NOT</STRONG> FULL <STRONG>THEN</STRONG><BR> top := top + 1; <EM>-- push integer onto stack</EM><BR> POSITION(top) := n;<BR> <STRONG>ELSE</STRONG> <EM>-- stack is full</EM><BR> raise_application_error(-20101, <EM>'stack overflow'</EM>);<BR> <STRONG>END</STRONG> <STRONG>IF</STRONG>;<BR> <STRONG>END</STRONG> push;<BR> MEMBER <STRONG>PROCEDURE</STRONG> pop(n <STRONG>OUT</STRONG> <STRONG>INTEGER</STRONG>) <STRONG>IS</STRONG><BR> <STRONG>BEGIN</STRONG><BR> <STRONG>IF</STRONG> <STRONG>NOT</STRONG> empty <STRONG>THEN</STRONG><BR> n := POSITION(top);<BR> top := top - 1; <EM>-- pop integer off stack</EM><BR> <STRONG>ELSE</STRONG><BR> <EM>-- stack is empty</EM><BR> raise_application_error(-20102, <EM>'stack underflow'</EM>);<BR> <STRONG>END</STRONG> <STRONG>IF</STRONG>;<BR> <STRONG>END</STRONG> pop;<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>在成员过程push和pop中，我们使用内置过程raise_application_error来关联用户定义的错误消息。这样，我们就能把错误报告给客户端程序而避免把未控制异常传给主环境。客户端程序捕获PL/SQL异常后，可以在OTHERS异常控制句柄中用SQLCODE和SQLERRM
来确定具体的错误信息。下例中，当异常被抛出时，我们就把对应的错误消息输出： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> ...<BR><STRONG>BEGIN</STRONG><BR> ...<BR><STRONG>EXCEPTION</STRONG><BR> <STRONG>WHEN</STRONG> <STRONG>OTHERS</STRONG> <STRONG>THEN</STRONG><BR> dbms_output.put_line(<STRONG>SQLERRM</STRONG>);<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>另外，程序还可以使用编译指示EXCEPTION_INIT把raise_application_error返回的错误编号影射到命名异常中，如下例所示：</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> stack_overflow <STRONG>EXCEPTION</STRONG>;<BR> stack_underflow <STRONG>EXCEPTION</STRONG>;<BR> <STRONG>PRAGMA</STRONG> EXCEPTION_INIT(stack_overflow, -20101);<BR> <STRONG>PRAGMA</STRONG> EXCEPTION_INIT(stack_underflow, -20102);<BR><STRONG>BEGIN</STRONG><BR> ...<BR><STRONG>EXCEPTION</STRONG><BR> <STRONG>WHEN</STRONG> stack_overflow <STRONG>THEN</STRONG><BR> ...<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P class=title2>3、对象类型实例：售票处</P>
<P>假如有一个连锁电影院，每个影院有三个银幕。每个影院有一个售票处，销售三种不同电影的影票。所有影票的价格都为三美元。定期检查影票的销售情况，然后及时补充影票。
</P>
<P>在定义代表销售处的对象类型之前，我们必须考虑到必要的数据和操作。对于一个简单的售票处来说，对象类型需要票价、当前影票存量和已售影票的收据这些属性。它还需要一些方法：购票、盘存、补充存量和收集收据。
</P>
<P>对于收据，我们可以使用含有三个元素的数组。元素1、2和3各自记录电影1、2和3。要声明一个变长数组属性，我们就必须先像下面这样定义它的类型：
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>CREATE</STRONG> <STRONG>TYPE</STRONG> RealArray <STRONG>AS</STRONG> VARRAY(3) <STRONG>OF</STRONG> <STRONG>REAL</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>现在，我们可以编写对象类型说明： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>CREATE</STRONG> <STRONG>TYPE</STRONG> ticket_booth <STRONG>AS</STRONG> OBJECT(<BR> price <STRONG>REAL</STRONG>,<BR> qty_on_hand <STRONG>INTEGER</STRONG>,<BR> receipts realarray,<BR> MEMBER <STRONG>PROCEDURE</STRONG> initialize,<BR> MEMBER <STRONG>PROCEDURE</STRONG> purchase(movie <STRONG>INTEGER</STRONG>, amount <STRONG>REAL</STRONG>, CHANGE <STRONG>OUT</STRONG> <STRONG>REAL</STRONG>),<BR> MEMBER <STRONG>FUNCTION</STRONG> inventory<BR> <STRONG>RETURN</STRONG> <STRONG>INTEGER</STRONG>,<BR> MEMBER <STRONG>PROCEDURE</STRONG> replenish(quantity <STRONG>INTEGER</STRONG>),<BR> MEMBER <STRONG>PROCEDURE</STRONG> <STRONG>COLLECT</STRONG>(movie <STRONG>INTEGER</STRONG>, amount <STRONG>OUT</STRONG> <STRONG>REAL</STRONG>)<BR>);
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>最后，我们可以编写对象类型体： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>CREATE</STRONG> <STRONG>TYPE</STRONG> <STRONG>BODY</STRONG> ticket_booth <STRONG>AS</STRONG><BR> MEMBER <STRONG>PROCEDURE</STRONG> initialize <STRONG>IS</STRONG><BR> <STRONG>BEGIN</STRONG><BR> price := 3.00;<BR> qty_on_hand := 5000; <EM>-- provide initial stock of tickets</EM><BR> <EM>-- call constructor for varray and set elements 1..3 to zero</EM><BR> receipts := realarray(0, 0, 0);<BR> <STRONG>END</STRONG> initialize;<BR> MEMBER <STRONG>PROCEDURE</STRONG> purchase(movie <STRONG>INTEGER</STRONG>, amount <STRONG>REAL</STRONG>, CHANGE <STRONG>OUT</STRONG> <STRONG>REAL</STRONG>) <STRONG>IS</STRONG><BR> <STRONG>BEGIN</STRONG><BR> <STRONG>IF</STRONG> qty_on_hand = 0 <STRONG>THEN</STRONG><BR> raise_application_error(-20103, <EM>'out of stock'</EM>);<BR> <STRONG>END</STRONG> <STRONG>IF</STRONG>;<BR><BR> <STRONG>IF</STRONG> amount >= price <STRONG>THEN</STRONG><BR> qty_on_hand := qty_on_hand - 1;<BR> receipts(movie) := receipts(movie) + price;<BR> CHANGE := amount - price;<BR> <STRONG>ELSE</STRONG> <EM>-- amount is not enough</EM><BR> CHANGE := amount; <EM>-- so return full amount</EM><BR> <STR⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -