---对象基本声明、实现、使用
--对象类型,类似与JAVA中的类,通俗的讲,就是捆绑了相关函数和过程的记录类型。
---对象声明 --create type 创建一个对象类型的规范部分 create or replace type hello_object as object ( obj_name varchar2(100), constructor function hello_object return self as result, constructor function hello_object(obj_name varchar2 /*:='world'*/) return self as result, member function to_string return varchar2 ) instantiable not final; -- obj_name :实例变量 -- constructor function:声明 构造函数,构造函数可以重载,但名字必须更类名称一致 -- self as result : 构造函数总是返回一个对象实例,java中的this? -- member procedure/function:定义成员函数,成员函数只能用于对象实例 -- not final 表示可以被继承 -- instantiable:表示可实例化,如果指定NOT INSTANTIABLE ,则这种类型只能做一个基类 --每个成员之间,使用逗号分隔 --构造函数要尽可能多的给当前对象任何一个属性赋值
--定义对象类型后,就可以创建对象体,跟创建包体规则一样
--create type body 创建对象体 create or replace type body hello_object as --默认构造方法 constructor function hello_object return self as result is v_obj hello_object := hello_object('generic object'); begin self := v_obj; return; end hello_object; --重写构造方法 constructor function hello_object(obj_name varchar2) return self as result is begin self.obj_name := obj_name; return; -- 返回一个副本,即对象实例的当前内存引用 end hello_object; --成员函数 member function to_string return varchar2 is begin return 'hello,' || self.obj_name ; end to_string; end; /
--不懂Java?self只不过是在编写成员方法时,用来引用调用当前对象的一个手段,可以用SELF指代对象自己,也可以
--用句点法来引用对象的属性或者方法
--缺省情况下,SELF是函数的IN 变量,是过程和构造函数的IN OUT变量 。怎么理解?
--可以把SELF作为第一个形参来改变缺省模式
--如果另外一个带参数的构造函数,使用了默认的入参,则这种构造函数,跟默认构造函数会有冲突
--当调用无入参的构造函数构建对象时,会出现多个可调用的构造函数,引发混乱报错
--使用对象类型
select hello_object().to_string() from dual; select hello_object('world').to_string() from dual; declare v_obj hello_object := hello_object(); begin dbms_output.put_line(v_obj.obj_name); dbms_output.put_line(v_obj.to_string()); end; / --对象在运行时创建并丢弃,这种对象就叫做瞬态对象;有瞬态的就有持久型对象 create table sample_object(persistent hello_object); --创建表,字段是对象类型 insert into sample_object values( hello_object()); insert into sample_object values( hello_object('world')); select * from sample_object ; select t.persistent.obj_name from sample_object t ; --两种调用方式 select t.persistent.to_string() from sample_object t ; select treat(t.persistent as hello_object).to_string() from sample_object t ; drop table sample_object;
--实现getter和setter --getter是一个方法,可以达到对象内部获取一些信息,setter也是一个方法,可以发送信息到对象内部 --设置实例变量 create or replace type hello_object as object ( obj_name varchar2(100), constructor function hello_object return self as result, constructor function hello_object(obj_name varchar2/* :='world'*/) return self as result, member function to_string return varchar2, member function get_name return varchar2, member procedure set_name(i_name in varchar2) ) instantiable not final; create or replace type body hello_object as --默认构造方法 constructor function hello_object return self as result is v_obj hello_object := hello_object('generic object'); begin self := v_obj; return; end hello_object; --重写构造方法 constructor function hello_object(obj_name varchar2) return self as result is begin self.obj_name := obj_name; return; -- 返回一个副本,即对象实例的当前内存引用 end hello_object; --成员函数 member function to_string return varchar2 is begin return 'hello,' || self.obj_name ; end to_string; member function get_name return varchar2 is begin return self.obj_name; end get_name; member procedure set_name(i_name in varchar2)is begin self.obj_name := i_name; end set_name; end; / declare v_obj hello_object := hello_object(); begin dbms_output.put_line(v_obj.get_name()); v_obj.set_name('test'); dbms_output.put_line(v_obj.get_name()); v_obj.obj_name :='ssdddee'; dbms_output.put_line(v_obj.obj_name); end; /
--实现静态方法
create or replace type hello_object as object ( obj_name varchar2(100), constructor function hello_object return self as result, constructor function hello_object(obj_name varchar2/* :='world'*/) return self as result, member function to_string return varchar2, member function get_name return varchar2, member procedure set_name(i_name in varchar2), static procedure print ) instantiable not final; create or replace type body hello_object as --默认构造方法 constructor function hello_object return self as result is v_obj hello_object := hello_object('generic object'); begin self := v_obj; return; end hello_object; --重写构造方法 constructor function hello_object(obj_name varchar2) return self as result is begin self.obj_name := obj_name; return; -- 返回一个副本,即对象实例的当前内存引用 end hello_object; --成员函数 member function to_string return varchar2 is begin return 'hello,' || self.obj_name ; end to_string; member function get_name return varchar2 is begin return self.obj_name; end get_name; member procedure set_name(i_name in varchar2)is begin self.obj_name := i_name; end set_name; static procedure print is begin dbms_output.put_line('welcome to learn object'); end print ; end; /
--使用static来声明一个静态方法或函数
--静态方法允许像使用标准包一样使用对象类型。可以使用静态函数和过程来打印常量和消息
--但不能使用其访问实例变量。静态函数可以返回一个实例化类
--静态方法中不能使用SELF,因为对于静态方法而言没有当前对象
begin
hello_object.print();
end;
/
--比较对象
--先来看一个例子
declare v_obj1 hello_object := hello_object(); v_obj2 hello_object := hello_object(); begin if v_obj1 = v_obj2 then dbms_output.put_line('equal'); else dbms_output.put_line('not equal'); end if; end; / --怎样比较两个对象呢?比如数值变量,我们可以直接比较数值来判断大小,对象却没有那么方便 --但我们可以给对象指定比较的规则,规定在什么情况下相等,什么情况下哪个比较大,哪个比较小 --使用MAP或者ORDER方法,可以来定义比较规则
--Map函数比较
--Map 函数不接受形参,只返回CHAR、DATE ,NUMBER或VARCHAR2的标量类型 create or replace type map_comp is object ( cmp_data varchar2(20), constructor function map_comp(cmp_data varchar2) return self as result, map member function equal return varchar2 ); create or replace type body map_comp is constructor function map_comp(cmp_data varchar2) return self as result is begin self.cmp_data := cmp_data; return ; end map_comp; map member function equal return varchar2 is begin return self.cmp_data; end equal; end ; / declare v_data1 map_comp := map_comp('a'); v_data2 map_comp := map_comp('a'); v_data3 map_comp := map_comp('b'); begin if v_data1 = v_data2 then dbms_output.put_line('equal'); else dbms_output.put_line('not equal'); end if; if v_data1 = v_data3 then dbms_output.put_line('equal'); else dbms_output.put_line('not equal'); end if; end; /
--作业:给定上述对象的对象列表,使用冒泡排序法进行排序
--ORDER 方法比较 --order 函数运行将任何数据类型定义为形参。通过将形参定义为相同的对象类型,可以模拟JAVA中的 --对象比较方法,这样可以把某个对象的副本传递给另一个对象,然后比较这两个对象 create or replace type order_comp is object ( first_name varchar2(100), second_name varchar2(100), constructor function order_comp(first_name varchar2,second_name varchar2) return self as result, order member function equal(i_obj order_comp) return number, member function to_string return varchar2 ) instantiable not final; --没有指定NOT FINAL 默认是FINAL的 create or replace type body order_comp is constructor function order_comp(first_name varchar2, second_name varchar2) return self as result is begin self.first_name := first_name; self.second_name := second_name; return; end order_comp; order member function equal(i_obj order_comp) return number is begin if self.first_name > i_obj.first_name then return -1; elsif self.first_name = i_obj.first_name and self.second_name > i_obj.second_name then return -1; elsif self.first_name = i_obj.first_name and self.second_name = i_obj.second_name then return 0 ; else return 1 ; end if; end equal; member function to_string return varchar2 is begin return self.first_name||'-'||self.second_name; end to_string; end; / --如果self < i_obj order函数返回任意负数 -1 --如果SELF = i_obj 返回 0 --如果SELF > i_obj 返回 任意正数 1 declare v_data1 order_comp := order_comp('a','c'); v_data2 order_comp := order_comp('a','d'); v_data3 order_comp := order_comp('b','s'); begin if v_data1< v_data2 then dbms_output.put_line('yes'); else dbms_output.put_line('no'); end if; end; / ---map 和order在同一个对象类型中不能并存,只能使用一个 --当有大量的对象需要排序和比较时,比如在SQL语句中,ORACLE建议使用MAP --ORACLE并不关系方法的名字,我们可以任意起名 --子类型也可以有MAP方法,不过只有当基类也有这个方法时才行 --子类型不能有ORDER方法;我们必须聪明地把所有比较都放在基类中
---继承和多态
--跟JAVA中类可以被继承外,对象类型也可以被继承
--在其它语言中,比如JAVA都有一个基类型,比如Object,在ORACLE里没有定义一个主的基类
drop type order_subcomp; ---under 标识子类型的关键字 create or replace type order_subcomp under order_comp ( salucation varchar2(20), constructor function order_subcomp(first_name varchar2, second_name varchar2, salucation varchar2) return self as result, overriding member function to_string return varchar2 )instantiable final; create or replace type body order_subcomp is constructor function order_subcomp(first_name varchar2, second_name varchar2, salucation varchar2) return self as result is begin self.first_name := first_name; self.second_name := second_name; self.salucation := salucation; return; end order_subcomp; overriding member function to_string return varchar2 is begin return(self as order_comp) .to_string() || ',' || self.salucation; end to_string; end; /
---使用(self as supertype) 来调用父类的方法,11g及之后可以这样使用
--11g之前,这是不可能的
declare v_data1 order_subcomp := order_subcomp('a','c','s'); v_data2 order_subcomp := order_subcomp('a','d','t'); v_data3 order_subcomp := order_subcomp('b','s','y'); v_parent order_comp ; begin if v_data1< v_data2 then dbms_output.put_line('small'); else dbms_output.put_line('not small'); end if; v_parent := v_data1; dbms_output.put_line(v_data1.to_string()); dbms_output.put_line(v_parent.to_string()); end; / --声明子类型时,父类型中的属性不用列出,因其自动继承 --子类型构建,将变量分配给父类型属性 --子类型可以访问父类型的构造函数 --子类型不能覆写父类型的构造函数 --对象的子类型不能覆写对象的MAP和ORDER函数
--我们来看下多态
--创建基类型
create or replace type ma_product is object ( product_id number, product_name varchar2(100), product_price number, not instantiable member procedure show_discount ) not instantiable not final ; --创建子类型 create or replace type ma_book under ma_product ( book_author varchar2(100), book_pages number, constructor function ma_book(product_id number, product_name varchar2, product_price number, book_author varchar2, book_pages number) return self as result, overriding member procedure show_discount ) instantiable not final; create or replace type body ma_book is constructor function ma_book(product_id number, product_name varchar2, product_price number, book_author varchar2, book_pages number) return self as result is begin self.product_id := product_id; self.product_name := product_name; self.product_price := product_price; self.book_author := book_author; self.book_pages := book_pages; return; end ma_book; overriding member procedure show_discount is begin dbms_output.put_line(self.product_name || ' 作者是' || self.book_author || ',共' || self.book_pages || '页'); end show_discount; end; / --创建子类型 create or replace type ma_computer under ma_product ( cpu_size number, brand varchar2(100), constructor function ma_computer(product_id number, product_name varchar2, product_price number, brand varchar2, cpu_size number) return self as result, overriding member procedure show_discount ) instantiable not final; create or replace type body ma_computer is constructor function ma_computer(product_id number, product_name varchar2, product_price number, brand varchar2, cpu_size number) return self as result is begin self.product_id := product_id; self.product_name := product_name; self.product_price := product_price; self.brand := brand; self.cpu_size := cpu_size; return; end ma_computer; overriding member procedure show_discount is begin dbms_output.put_line(self.product_name || ' 品牌是' || self.brand || ',CPU大小' || self.cpu_size || 'M'); end show_discount; end; / declare type list_t is table of ma_product; product_list list_t; v_product1 ma_book := ma_book(1, 'plsql实战训练', 25, 'testma', 55); v_product2 ma_book := ma_book(1, 'plsql实战训练2', 30, 'testma12', 56); v_product3 ma_computer := ma_computer(1, '联想笔记本', 3000, '联想', 1024); v_product4 ma_computer := ma_computer(1, '清华同方笔记本', 1999, '清华同方', 2048); begin product_list := list_t(v_product1, v_product3, v_product2, v_product4); for i in 1 .. product_list.count loop product_list(i) .show_discount(); end loop; end; /
--对象集合
--对象类型和集合对象类型之间的唯一区别是,对象只保存单个对象类型,而集合
--保存一个对象类型数组或嵌套表
ma_order_items create or replace type order_item is object ( order_id varchar2(32), product_no number, product_name varchar2(100), product_size varchar2(20), product_num number , product_ori_price number , product_new_price number ); declare v order_item := order_item('','','','','','','');--不能使用ORDER_ITEM()进行初始化 begin v.order_id := sys_guid(); dbms_output.put_line(v.order_id); end; / create or replace type order_item_table is table of order_item; create or replace type order_objects is object ( order_table order_item_table, constructor function order_objects(order_table order_item_table) return self as result, constructor function order_objects return self as result, member function get_size return number, member function get_table return order_item_table, static function get_order_items(i_low number, i_high number) return order_item_table ) instantiable not final; create or replace type body order_objects is constructor function order_objects(order_table order_item_table) return self as result is begin self.order_table := order_table; return; end order_objects; constructor function order_objects return self as result is cursor cur_item is select * from ma_order_items; c number := 1; v_item order_item; begin self.order_table := order_item_table(); for v in cur_item loop v_item := order_item(v.ID_MA_ORDERS, v.product_no, v.product_name, v.product_size, v.product_num, v.product_ori_price, v.product_new_price); self.order_table.extend; self.order_table(c) := v_item; c := c + 1; end loop; return; end order_objects; member function get_size return number is begin return self.order_table.count; end get_size; member function get_table return order_item_table is begin return self.order_table; end get_table; static function get_order_items(i_low number, i_high number) return order_item_table is cursor cur_item is select * from ma_order_items t where t.product_no between i_low and i_high; c number := 1; v_item order_item; v_order_table order_item_table; begin v_order_table := order_item_table(); for v in cur_item loop v_item := order_item(v.ID_MA_ORDERS, v.product_no, v.product_name, v.product_size, v.product_num, v.product_ori_price, v.product_new_price); v_order_table.extend; v_order_table(c) := v_item; c := c + 1; end loop; return v_order_table; end get_order_items; end; / select * from table(order_objects().get_table());
---对象表
--在ORACLE里,可以把对象保存到数据库中
create table product_objects of ma_product (constraint pk_ma_product primary key(product_id)); --这个语句创建了一个叫做ma_product的对象表,每一行都是一个ma_product对象 --一般来说,对象的每个属性都对应着表中的一列 select * from product_objects ;
--需注意的是ma_product是不可实例化的,这个表中的每一行实际是类似于ma_book或ma_computer的子类型 insert into product_objects values (ma_book(1, 'plsql实战训练', 25, 'testma', 55)); insert into product_objects values (ma_book(2, 'plsql实战训练2', 30, 'testma12', 56)); insert into product_objects values (ma_computer(3,'联想笔记本',3000,'联想',1024)); insert into product_objects values (ma_computer(4,'清华同方笔记本',1999,'清华同方',2048)); select * from product_objects;
---所属子类型的属性哪去了呢?
--oracle 把子类型专有的属性放在了product_objects的隐藏列中
--从对象编程角度来看,这种方法既保留了商品的抽象性,有能够在需要的时候暴露子类型的而外信息
--对象标识符
--ORALE可以用主键值或这系统生成的值(SYS_NC_OID$)来产生对象标识符
--系统生成的OID:不透明,可以数据库全局唯一,不可变;
---VALUE函数 --从数据库中提取一个对象,可以使用VALUE函数 select value(p) from product_objects p ; --value只接受一个参数,这个参数必须是当前FROM子句中的表别名,返回的是一个用于定义表的类型对象; declare v_product ma_product; cursor cur_product is select value(p) from product_objects p; begin open cur_product; loop fetch cur_product into v_product; exit when cur_product%notfound; v_product.show_discount(); end loop; end; / --还可以直接访问属于基类的属性 select value(p).product_id from product_objects p ; --能否直接访问子类型的属性呢? select value(p).book_author from product_objects p ; ---如果一个对象表基于的对象类型没有子类,就可以利用传统的SQL语句对于所有的列执行选择,插入,更新,删除操作 select product_id from product_objects p ; update product_objects p set p.product_id = 6 where p.product_id = 1; --对于那些因为是子类型而被当作隐藏的列,就不能使用传统的关系DML操作,,必须使用对象DML方法 update product_objects p set p = ma_book(1, 'plsql实战训练', 25, 'testma', 55) where p.product_id = 6; --要想更新某个子类型特有列值,唯一的好办法是更新整个对象
--TREAT 函数
--怎样去访问对象表中子类型的特有属性呢?
DECLARE V_BOOK ma_book; V_PRODUCT ma_product := ma_book(1, 'plsql实战训练', 25, 'testma', 55); BEGIN v_book := treat(v_product as ma_book);---从父类型向子类型转换,向下转换或者缩小 v_book.show_discount(); END; / --TREAT函数的语法 treat(object_instance as subtype)[.{attribute|method(args...)}]; --怎么去感知某个父类型是某个子类型呢? OBJECT IS OF ([ONLY] TYPENAME) --ONLY:如果一个对象属于指定的类型或者任意一个子类型,不带ONLY的话,返回TRUE --如果使用了ONLY,这个表达式不会检查子类型,只有对象完全匹配时才返回TRUE declare v_product ma_product; cursor cur_product is select value(p) product from product_objects p; begin for v in cur_product loop case when v.product is of(ma_book) then dbms_output.put_line(treat(v.product as ma_book).book_author); when v.product is of(ma_computer) then dbms_output.put_line(treat(v.product as ma_computer).brand); else dbms_output.put_line('unknown object'); end case; end loop; end; / ---如果想修改MA_PRODUCT,怎么办 --比如删除对象类型 drop type ma_product validate; drop type typename [force | validate] --在MA_PRODUCT新增一个属性? alter type ma_product add attribute publication_date date cascade including table data ; --删除类型ma_book的方法 alter type ma_book drop constructor function ma_book(product_id number, product_name varchar2, product_price number, book_author varchar2, book_pages number) return self as result cascade;
----------------------------------------------------
declare p_numbers varchar2(100) :='tydbser'; type t_table is table of char index by pls_integer; v_tab t_table; v_tmp char(1); Result varchar2(100); begin for i in 1 .. nvl(length(p_numbers), 0) loop v_tab(i) := substr(p_numbers, i, 1); end loop; for p in 1 .. nvl(length(p_numbers), 0) - 1 loop for q in reverse p .. nvl(length(p_numbers), 0)-1 loop ---一定要从冒泡轨迹的起点开始比较。 if v_tab(q) <= v_tab(q+1) then v_tmp := v_tab(q); v_tab(q) := v_tab(q+1); v_tab(q+1) := v_tmp; end if; end loop; end loop; for i in 1 .. nvl(length(p_numbers), 0) loop Result := Result || v_tab(i); end loop; dbms_output.put_line(Result); end ; declare v_data1 map_comp := map_comp('a'); v_data2 map_comp := map_comp('a'); v_data3 map_comp := map_comp('b'); v_data4 map_comp; begin if v_data1 = v_data2 then dbms_output.put_line('equal'); else dbms_output.put_line('not equal'); end if; if v_data1 = v_data3 then dbms_output.put_line('equal'); else dbms_output.put_line('not equal'); end if; if v_data1 <= v_data2 then v_data4 := v_data1; v_data1 := v_data2; v_data2:= v_data4; -- dbms_output.put_line(v_data1); -- dbms_output.put_line(v_data2); -- dbms_output.put_line(v_data4); end if; end; / --定义对象 create or replace type bhgx_obj is object ( letter varchar2(1), constructor function bhgx_obj return self as result, constructor function bhgx_obj(letter varchar2) return self as result, member function toString return varchar2, order member function equal(v_obj bhgx_obj) return number ) instantiable not final; CREATE OR REPLACE TYPE let_obj IS TABLE OF bhgx_obj; --创建对象体 create or replace type body bhgx_obj as --默认构造方法 constructor function bhgx_obj return self as result is v_obj bhgx_obj := bhgx_obj(''); begin self := v_obj; return; end bhgx_obj; --重写构造方法 constructor function bhgx_obj(letter varchar2) return self as result is begin self.letter := letter; return; -- 返回一个副本,即对象实例的当前内存引用 end bhgx_obj; --成员函数 member function toString return varchar2 is begin return self.letter ; end toString; order member function equal(v_obj bhgx_obj) return number is begin if self.letter > v_obj.letter then return 1 ; elsif self.letter < v_obj.letter then return -1 ; elsif self.letter = v_obj.letter then return 0 ; else return 0 ; end if; end equal; end; --创建包 create or replace package pkg_data_order is function order_letter(v_l let_obj) return let_obj ; end pkg_data_order; --创建包体 CREATE OR REPLACE PACKAGE BODY pkg_data_order IS function order_letter(v_l let_obj) return let_obj as v_t bhgx_obj; v_letts let_obj:=v_l; begin FOR i IN 1..v_letts.COUNT LOOP FOR j IN 1..(v_letts.COUNT-i) LOOP if v_letts(j).equal(v_letts(j+1))>0 then v_t := v_letts(j) ; v_letts(j) := v_letts(j+1); v_letts(j+1) := v_t; end if; END LOOP; END LOOP; return v_letts ; end; end pkg_data_order; --调用 declare v_p let_obj:=let_obj() ; begin v_p.extend ; v_p(1):=bhgx_obj('z'); v_p.extend ; v_p(2):=bhgx_obj('b'); v_p.extend ; v_p(3):=bhgx_obj('d'); v_p.extend ; v_p(4):=bhgx_obj('m'); v_p.extend ; v_p(5):=bhgx_obj('a'); v_p.extend ; v_p(6):=bhgx_obj('k'); v_p.extend ; v_p(7):=bhgx_obj('c'); v_p.extend ; v_p(8):=bhgx_obj('q'); v_p := pkg_data_order.order_letter(v_p) ; FOR i IN 1..v_p.COUNT LOOP dbms_output.put_line(v_p(i).toString()); END LOOP; end; -------- CREATE OR REPLACE procedure test_bubble(str IN VARCHAR2) IS type v_type is varray(1000) of varchar2(100); var v_type; temp varchar2(100); flag boolean; results VARCHAR2(4000); BEGIN select substr(str,level,1) bulk collect into var from dual connect by level <=length(str); <<outer_scope>> for i in 1 .. var.count-1 loop flag := false; for j in reverse i .. var.count-1 loop if var(j+1) < var(j) then temp := var(j+1); var(j+1) := var(j); var(j) := temp; flag := true; end if; end loop; if (not flag) then exit outer_scope; end if; end loop; for i in var.first .. var.last loop results :=concat(results,var(i)); end loop; dbms_output.put_line('out_results='||results); END test_bubble; --测试过程 declare begin test_bubble('zlmbwacgiofrskne'); end; ----------- create or replace type map_comp is object( cmp_data varchar2(1), constructor function map_comp(cmp_data varchar2) return self as result, map member function equal return varchar2, member function to_string return varchar2 )instantiable not final; create or replace type body map_comp is constructor function map_comp(cmp_data varchar2) return self as result is begin self.cmp_data := cmp_data; return; end map_comp; map member function equal return varchar2 is begin return self.cmp_data; end equal; member function to_string return varchar2 is begin return self.cmp_data; end to_string; end; --赋值和排序 declare type ind_obj_type is table of map_comp index by pls_integer; v_ind_obj ind_obj_type; v_aaa map_comp; begin --初始化,随机赋值 for i in 1 .. 15 loop v_ind_obj(i) := map_comp(chr(97 + dbms_random.value(1, 25))); end loop; --打印排序前对象中的字母 dbms_output.put('排序前: '); for i in 1 .. v_ind_obj.count loop dbms_output.put(v_ind_obj(i).to_string() || ' '); end loop; dbms_output.put_line(''); --冒泡排序 for i in 1 .. v_ind_obj.count loop for j in i + 1 .. v_ind_obj.count loop if v_ind_obj(i) > v_ind_obj(j) then v_aaa := v_ind_obj(j); v_ind_obj(j) := v_ind_obj(i); v_ind_obj(i) := v_aaa; end if; end loop; end loop; --打印排序后 dbms_output.put('排序后: '); for i in 1 .. v_ind_obj.count loop dbms_output.put(v_ind_obj(i).to_string() || ' '); end loop; dbms_output.put_line(''); exception when others then dbms_output.put_line(sqlerrm); end;