• PL/SQL 训练09--面向对象


    ---对象基本声明、实现、使用
    --对象类型,类似与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;
  • 相关阅读:
    Linux基本命令-chmod
    Linux操作系统启动流程
    博客园背景美化
    Windows安装python3.x后,pip list警告!DEPRECATION: The default format will switch to columns in the future.
    Python2.7更新pip:UnicodeDecodeError: 'ascii' codec can't decode byte 0xb7 in position 7: ordinal not in range(128)
    .Net程序员学用Oracle系列(23):视图理论、物化视图
    .Net程序员学用Oracle系列(22):分析函数(OVER)
    .Net程序员学用Oracle系列(21):分组查询(GROUP BY)
    .Net程序员学用Oracle系列(20):层次查询(CONNECT BY)
    .Net程序员学用Oracle系列(19):导出、导入(备份、还原)
  • 原文地址:https://www.cnblogs.com/yhq1314/p/10614765.html
Copyright © 2020-2023  润新知