• PLSQL面向对象


    ```sql
    --定义可被SQL语句调用的子程序
    create or replace function getempdept(
          p_empno emp.empno%type
    )return varchar2
    as v_dname dept.dname%TYPE;
    begin
      select b.dname into v_dname from emp a,dept b
      where a.deptno=b.deptno and a.empno=p_empno;
      return v_dname;
    exception
       when no_data_found then
         return null;
         end;
      
    select empno 员工编号,getempdept(empno) 部门名称 from emp;
     
    --嵌套子程序重载
    declare
           procedure getSalary(p_empno in numberis
             begin dbms_output.put_line('员工编号为'||p_empno); end;
           procedure getSalary(p_empname in varchar2is
             begin dbms_output.put_line('员工名称为'||p_empname); end;
           procedure getSalary(p_empno in number,p_empname in varchar2is
             begin dbms_output.put_line('员工编号为'||p_empno||'员工名称为:'||p_empname); end;
     begin
         getsalary(7369);
         getsalary('史密斯');
         getsalary(7369,'史密斯');
         end;          
     
     --自治事务使用示例  在主事务中开启一个独立的事务
     create   table emp_history as select * from emp where 1=3;
     select * from emp_history;
     declare 
            procedure TestAutonomous(p_empno number)  AS
            pragma autonomous_transaction;       --标记为自治事务  
        begin
          insert into emp_history select * from emp where empno=p_empno;
          commit;
       end TestAutonomous;
      begin    
          insert into emp_history(empno,ename,sal) values(1011,'测试',1000);
          TestAutonomous(7369);
          rollback;
          end;    
    select * from emp;
     insert into emp select * from emp_copy;
     delete from emp_history;
     select * from emp_history;
    --在PL/SQL 中实现递归阶乘   
    declare
      v_result integer;
      function fac(n positive)
          return integer is
        begin
           if n=1 then
              dbms_output.put_line('1');
              return 1;  
          else
            dbms_output.put(n||'*');
            return n*fac(n-1);       
            end if; 
          end fac;
        begin
          v_result:=fac(10);
          dbms_output.put_line('结果是:'||v_result);
          end;   
    excel 阶乘函数 fact  
    select floor(2345.67from dual;
    FLOOR(2345.67)   
    create table staff (condtion varchar2(30));
    select * from staff;
    select * from emp;
    --使用递归查找职员列表示例
    declare 
       procedure find_staff(mgr_no number, tier number :=1)
         is boss_name varchar2(10);  --定义老板名称
         cursor c1 (boss_no number)
         is select empno,ename from emp where mgr=boss_no;
       begin
         select ename into boss_name from emp where empno=mgr_no;
         if tier =1
           then 
             insert into staff values(boss_name||'是老板');
         end if;
         for ee in c1(mgr_no)
          loop
            insert into staff values(boss_name||'管理 '
            || ee.ename||' 在层次 '||to_char(tier));
          find_staff(ee.empno,tier+1);  
            end loop; 
            commit;    
         end find_staff;  
       begin
         find_staff(7566);
        end; 
     
    create or replace procedure find_staff(mgr_no number, tier number :=1)
         is boss_name varchar2(10);  --定义老板名称
         cursor c1 (boss_no number)
         is select empno,ename from emp where mgr=boss_no;
       begin
         select ename into boss_name from emp where empno=mgr_no;
         if tier =1
           then 
             insert into staff values(boss_name||'是老板');
         end if;
         for ee in c1(mgr_no)
          loop
            insert into staff values(boss_name||'管理 '
            || ee.ename||' 在层次 '||to_char(tier));
          find_staff(ee.empno,tier+1);  
            end loop; 
            commit;    
         end find_staff;
    delete  from staff;   
     create table staff(emplist varchar2(30));
    select * from staff;    
    --查找所有表与emp表具有依赖的对象
    select name,type from user_dependencies where referenced_name='EMP';
    --查询间接依赖  1.执行app../utldtree.sql  2 exec deptree_fill('TABLE',''SCOTT,''EMP);
    select nested_level,name,type from deptree 
    where type in ('procedure','function');    --sqllus cmd
    --查看对象的有效性,当子程序依赖的
    alter table emp add emp_desc varchar2(200null;
    alter table  emp drop column emp_desc;
    select object_name,object_type,status from user_objects 
    where object_name in ('emp');  --查询调用子程序的依赖关系
    --重新编译子程序
    alter procedure testsubprog complete;
    --子程序权限管理
    create user userb identified by userb;
    grant resource,connect to userb;
    --SCOTT方案
    grant execute on find_staff  to userb;
    --创建一个同义词视图,分配userb查询权限  DBA模式创建同义词**
    create public synonym emp for scott.emp;
    grant select any table to userb;
    grant create synonym to scott;
    begin
      scott.find_staff(7369);
    end;
    select * from scott.emp;
    select * from staff;
    delete  from staff;
    create table staff(emplist varchar2(1000));
    --修改scott方案下的find_staff子程序,使之按调用者权限处理
      authid current_user  is
     
    --包重载
    create or replace package emp_action_pkg_overload is 
           procedure newdept(
             p_deptno dept.deptno%type,
             p_dname dept.dname%type,
             p_loc  dept.loc%type
             );
            procedure newdept(
              p_deptno dept.deptno%type,
              p_dname dept.dname%type
              ); 
              function getraisedsalary(p_empno emp.empno%type)
                return number;
              function getraisedsalary(p_ename emp.ename%type)
                return number;  
    end emp_action_pkg_overload;
    --包含重载子程序包体实现
    create or replace package body emp_action_pkg_overload is
           procedure newdept(
             p_deptno dept.deptno%type,
             p_dname dept.dname%type,
             p_loc dept.loc%type
             )as
             v_deptcount number;
             begin
               select count(*into v_deptcount from dept 
               where deptno =p_deptno;
               if v_deptcount > 0
               then
                 raise_application_error(-20002,'出现了相同的员工记录');
                 end if;  
                 insert into dept(deptno,dname,loc) values(p_deptno,p_dname,p_loc);
              end newdept;   
             
              procedure newdept(
                p_deptno dept.deptno%type,
                p_dname dept.dname%type
                ) as
                v_deptcount number;
                begin
                  select count(*into v_deptcount from dept
                  where deptno =p_deptno;
                  if v_deptcount>0
                    then
                      raise_application_error(-20002,'出现了相同的员工记录');
                     end if;
                     insert into dept(deptno,dname,loc) 
                     values(p_deptno,p_dname,'中国'); 
                  end newdept;
                  function getraisedsalary (p_empno emp.empno%type)
                    return number
                    is v_job emp.job%type;
                    v_sal emp.sal%type;
                    v_salaryratio number(10,2);
                  begin          
                    select job,sal into v_job,v_sal from emp where 
                    empno=p_empno;
                    case v_job
                      when 'CLERK'  then
                        v_salaryratio :=1.09;
                       when 'SALESMAN' then
                         v_salaryratio :=1.11;
                        when 'MANAGER' then
                          v_salaryratio :=1;
                      end case;
                      if v_salaryratio <>1
                        then 
                          return round(v_sal*v_salaryratio,2); 
                      else
                        return v_sal;
                        end if;
                    exception
                       when no_data_found then
                         return 0;
                    end getraisedsalary;                  
                    function getraisedsalary (p_ename emp.ename%type)
                    return number
                    is v_job emp.job%type;
                    v_sal emp.sal%type;
                    v_salaryratio number(10,2);
                  begin
                    select job,sal into v_job,v_sal from emp where 
                    ename=p_ename;
                    case v_job
                      when 'CLERK'  then
                        v_salaryratio :=1.09;
                       when 'SALESMAN' then
                         v_salaryratio :=1.11;
                        when 'MANAGER' then
                          v_salaryratio :=1;
                      end case;
                      if v_salaryratio <>1
                        then 
                          return round(v_sal*v_salaryratio,2); 
                      else
                        return v_sal;
                        end if;
                    exception
                       when no_data_found then
                         return 0;
                    end getraisedsalary;                  
                  function checkdeptno(p_deptno dept.deptno%type) return number
                    as
                     v_counter number(2);
                   begin
                     select count(*into v_counter from dept where deptno=p_deptno;
                     return v_counter;
                     end;                
                    end emp_action_pkg_overload;              
    declare
        v_sal number(10,2);
      begin
        emp_action_pkg_overload.newdept(43,' 样品部','东京');
         emp_action_pkg_overload.newdept(44,' 纸品部');
         v_sal:=emp_action_pkg_overload.getraisedsalary(7369);
         v_sal:=emp_action_pkg_overload.getraisedsalary('SMITH')    
        end;   
    
    
    ​
      create or replace type employee_obj as object (
    empno number(4),
    ename varchar2(20),
    job varchar2(20),
    sal number(10,2),
    comm number(10,2),
    deptno number(4),
    --定义对象类型方法
    MEMBER PROCEDURE Change_sal(p_empno number,p_sal number),
    member procedure change_comm(p_empno number,p_comm number),
    member procedure change_deptno(p_empno number,p_deptno number),
    member function get_sal(p_empno number) return number,
    member function get_comm(p_empno number) return number,
    member function get_deptno(p_empno number) return integer
    ) NOT FINAL  --指定该类可以被继承,如果指定final,表示该类无法被继承
    create or replace type body employee_obj
    as member procedure change_sal (p_empno number,p_sal number)
    is begin update emp set sal=p_sal where empno =p_empno; end;
    --定义对象成员方法,更改员工提成
    member procedure change_comm (p_empno number,p_comm number)
    is begin update emp set comm = p_comm where empno =p_empno;end;
    --定义对象成员方法,更改员工部门
    member procedure change_deptno (p_empno number,p_deptno number)
    is begin update emp set deptno =p_deptno where empno =p_empno;end;
    --定义对象成员方法,获取员工薪资
    member function get_sal(p_empno number) return number
    is  v_sal number (10,2); begin select sal into v_sal from emp
    where empno= p_empno; return v_sal; end;
    --获取员工提成
    member function get_comm(p_empno number) return number
    is v_comm number(10,2); begin select comm into v_comm from
    emp where empno = p_empno;return v_comm; end;
    --获取员工部门
    member function get_deptno (p_empno number) return integer
    is v_deptno int; begin select deptno into v_deptno from emp
    where empno =p_empno; return v_deptno; end;
    
    
    
    
    declare
    v_emp employee_obj;
    v_sal v_emp.sal%type;
    begin
        v_emp:=employee_obj(7890,'赵五','销售人员',5000,200,20);
        v_sal :=v_emp.sal;
        dbms_output.put_line(v_emp.ename||'的薪资是:'|| v_sal);
      end;
    
    
    --使用member和static成员法
    create or replace type employee_method as object(
          empno number(4),
          sal number(10,2),
          comm number(10,2),
          deptno number(4),
          --实例方法,可以访问对象本身的属性
          member procedure change_sal,
          member function get_sal return number,
          --静态方法,不能访问对象本身的属性,只能访问静态数据
          static procedure change_deptno(p_empno number,p_deptno number),              
          static function get_sal(p_empno number) return number 
    ) not final;
    --定义employee_method对象类型体
    create or replace type body employee_method
    as  member procedure change_sal is
    begin self.sal :=self.sal*1.12;end;
    member function get_sal return number is
    begin return sal; end;
    static procedure change_deptno(p_empno number,p_deptno number)
    is  begin  update emp set deptno = p_deptno where empno=p_empno;end;
    static function get_sal(p_empno number) return number
    is v_sal number(10,2);
    begin
      select sal into v_sal from emp where empno=p_empno;
      return v_sal; end; end;
      
      --member和static 方法使用示例
      member方法:基于对象实例而不是对象类型调用
      static方法:静态方法独立与对象实例,不能在对象主体中引用对象属性
      declare v_emp employee_method;
      begin v_emp:=employee_method(7999,5000,200,20);
      v_emp.change_sal;
      dbms_output.put_line('员工编号为:'||v_emp.empno||'的薪资为:'||
      v_emp.get_sal);
      
      --下面调用static方法更新emp表中员工编号为7369的部门为20
      employee_method.change_deptno(7369,20);
      dbms_output.put_line('员工编号为7369的薪资为:'||
      employee_method.get_sal(7369));end;
    定义构造函数
    当定义了一个对象类型之后,系统会提供一个接收与每个属性相对应的
    参数构造函数.因此在多数情况下,都不需要自己编写构造函数
    自定义构造函数的目的
    1.为对象提供初始化功能,可以通过构造函数进行统一初始化
    2.可以在构造函数中为某些属性提供默认值,
    3 避免更改调用构造函数的应用代码
    --自定义构造函数示例
    create or replace type salary_obj as object(
    percent  number(10,4),
    sal  number(10,2),
    --自定义构造函数
    constructor function salary_obj(p_sal number) return self as result)
    instantiable  --可实例化对象
    final;    --不可以继承
    create or replace type body salary_obj as
    constructor function salary_obj(p_sal number) return self as result
    as begin self.sal :=p_sal; self.percent:=1.12; return;end;
    end;
    declare
      v_salobj1 salary_obj;
      v_salobj2 salary_obj;
    begin
      v_salobj1 := salary_obj(1.12,3000);
      v_salobj2 :=salary_obj(2000);
      end;
      
      MAP方法:该函数会将实例根据调用规则返回DATE,NUMBER,VARCHAR2类型的标量类型
    定义了MAP函数以后,PLSQL会隐式通过调用MAP函数在多个对象间排序
    --定义MAP函数示例
    create or replace type employee_map as object(
          empno number(4),
          sal number(10,2),
          comm number(10,2),
          deptno number(4),
          map member function convert return real  --定义一个map方法,real实数类型 科学计数法
    ) not final;
    --声明一个以map关键字开头的成员函数,该函数返回REAL类型
    --convert函数对对象进行由高到低的排序
    create or replace type body employee_map as
          map member function convert return real is
          begin
            return sal +comm;  --返回标量类型的值
            end;
            end;
    --创建employee_map类型的对象表
    create table emp_map_tab of employee_map;
    insert into emp_map_tab values(7123,3000,200,20);
    insert into emp_map_tab values(7124,2000,800,20);
    insert into emp_map_tab values(7125,5000,800,20);
    insert into emp_map_tab values(7129,3000,400,20);
    --order by 1 表示的是第一栏
    select value(r) val,r.sal+r.comm from emp_map_tab r order by 1;
    
    Order方法之能对两个对象之间进行比较,返回必须是数值类型 
    返回结果正数,负数或零,  只有2个参数self和另一个比较的类型
    --定义order函数示例
    create or replace type employee_order as OBJECT(
    empno number(4),
    sal number(10,2),
    comm number(10,2),
    deptno number(4),
    order member function match(r employee_order) return integer
    ) not final;
    create or replace type body employee_order as
          order member function match(r employee_order)
          return integer is begin
          if ((self.sal+self.comm)<(r.sal+r.comm)) then return -1;
        elsif((self.sal+self.comm)>(r.sal+r.comm)) then return 1;
        else return 0;
        end if;
        end match; end;
        
    declare
        emp1 employee_order:=employee_order(7112,3000,200,20);
        emp2 employee_order:=employee_order(7112,3000,200,20);
    begin
      if emp1>emp2 then
          dbms_output.put_line('员工1的薪资加提成比员工2大!');
      elsif emp1<emp2 then
          dbms_output.put_line('员工1的薪资加提成比员工2小!');
      else
        dbms_output.put_line('员工1的薪资加提成与员工2相等!');
        end if;  
      end;  
      
    --使用order成员方法进行排序
    create table emp_order_tab of employee_order;
    insert into emp_order_tab values(7123,3000,200,20);
    insert into emp_order_tab values(7124,2000,800,20);
    insert into emp_order_tab values(7129,3000,800,20);  
    insert into emp_order_tab values(7125,5000,400,20);      
    select value(r) val,r.sal+r.comm from emp_order_tab r order by 1;
    map
    (1)将同一类型的对象实例映射成(number,date,varchar),之后进行比较。
    (2) map成员函数不接受形参;
    
    order
    (1)两个对象实例进行比较
    (2)一个对象类型只能有一个map或order ,且不能同时有。
    
    --使用嵌套对象类型
    --定义地址对象类型
    CREATE OR REPLACE TYPE address_type AS OBJECT
    (
      street_addr1 VARCHAR2(25),
      street_addr2 VARCHAR(25),
      city         VARCHAR2(30),
      state        VARCHAR2(2),
      zip_code     NUMBER,
      MEMBER FUNCTION toString RETURN VARCHAR2,
      MAP MEMBER FUNCTION mapping_function RETURN VARCHAR2
    )
    CREATE OR REPLACE TYPE BODY address_type AS
      MEMBER FUNCTION tostring RETURN VARCHAR2 IS
      BEGIN
        IF (street_addr2 IS NOT NULLTHEN
          RETURN street_addr1 || chr(10|| street_addr2 || chr(10|| city || ',' || state || ' ' || zip_code; --chr(10)换行
        ELSE
          RETURN street_addr1 || CHR(10|| city || ',' || state || ' ' || zip_code;
        END IF;
      END;
      MAP MEMBER FUNCTION mapping_function RETURN VARCHAR2 IS
      BEGIN
        RETURN TO_CHAR(NVL(zip_code,
                           0),
                       'fm00000'--fm00000 去掉前后空格
        || LPAD(NVL(city,
                    ''),
                30|| LPAD(NVL(street_addr1,
                                ''),
                            25|| LPAD(NVL(street_addr2,
                                            ''),
                                        25);
      END;END;
      
     
     --定义一个对象规范,该规范中包含order方法
     create or replace type employee_addr as object(
            empno  number(4),
            sal number(10,2),
            comm number(10,2),
            deptno number(4),
            addr address_type,
            member function get_emp_info return varchar2
     ) not final;
     create or replace type body employee_addr as 
             member function  get_emp_info return varchar2
             is begin 
                return '员工'||self.empno||'的地址为'||self.addr.toString;
             end;  end;  
      
     
     declare
             o_address address_type;
             o_emp employee_addr;
     begin
             o_address:=address_type('玉兰一街','二巷','深圳','DG',523343);
             o_emp:=employee_addr(7369,5000,800,20,o_address);
             dbms_output.put_line('员工信息为'||o_emp.get_emp_info);
     end;
    --对象的继承
    create or replace type person_obj as object(
           person_name varchar(20),
           gender varchar2(2),
           birthdate date,
           address varchar2(50),
           member function get_info return varchar2 --返回员工信息
    )not final;
    create or replace type body person_obj as
    member function get_info return varchar2 is
    begin 
      return '姓名:'||person_name|| ',家庭住址:'||address;
      endend;
    create or replace type employee_personobj under  person_obj(
        empno number(6), sal number(10,2),job varchar2(10),
        member function get_emp_info return varchar2,
        --定义重载方法
        overriding member function get_info return varchar2
    );
    create or replace type body employee_personobj as
       member function get_emp_info return varchar2 is begin
           return '员工编号:' ||self.empno ||'员工名称:'||self.person_name||
           '职位:'||self.job; end; 
            --实现重载方法  
           overriding member function get_info return varchar2 as
           begin
             return '员工编号:' ||self.empno||'员工名称:'||self.person_name||
             '职位:'||self.job;
             end;      
           end;
           
    declare
           o_emp employee_personobj;
          begin
            o_emp:=employee_personobj('张小五','F',
               to_date('1983-01-01','yyyy-mm-dd'),'中信',7981,5000,'Programmer');
               dbms_output.put_line(o_emp.get_info);
               dbms_output.put_line(o_emp.get_emp_info);
            end;
    --管理对象表
    create table emp_obj_table of employee_personobj;
    select * from emp_obj_table
    drop type employee_personobj;--绑定表之后无法删除对象
    create table emp_addr_table of employee_addr;
    sql>set desc depth all LINENUM ON   --展开层次结构
    desc emp_addr_table;
    insert into emp_obj_table values('张小五','F',to_date('1983-01-01','yyyy-mm-dd'),'中信',
          7981,5000,'Programmer' );
          
    insert into emp_addr_table values (7369,5000,800,20,
        address_type('玉兰一街','二巷','深圳','DG',523343));
    --检索对象表  value函数
    select value(e) from emp_obj_table e;
    declare
           o_emp employee_personobj;
    begin
       select value(e) into o_emp from emp_obj_table e where e.person_name='张小五';
       dbms_output.put_line(o_emp.person_name||'的职位是:'||o_emp.job);
      end;
      
      
      create type address as object(
             street varchar2(35),
             city varchar2(15),
             state char(2),
             zip_code integer
      );
      
    create table addresses of address;
    create type person as object(
           person_name varchar2(15),
           birthday date,
           home_address ref address, --使用ref关键字,指定属性为指向另一个对象表的对象
           phone_number varchar2(15)
    );
    create table persons of person;
    insert into addresses values(address('玉兰','深圳','GD','523345'));
    insert into addresses values(address('黄甫','广州','GD','523000'));
    insert into persons values (person('王小五', to_date('1983-01-01','yyyy-mm-dd'),
    (select REF(a) from addresses a where street='玉兰'),'16899188'));
    select person_name,deref(home_address) as home from persons
       
    --更新对象表
    update emp_obj_table empobj set empobj.gender='M'
    where empobj.person_name='张小五';
    --删除对象表
    delete from emp_obj_table where person_name='张小五';
    --定义emp_tbl_obj对象类型
    create or replace type emp_tbl_obj as object(
           empno number(6),
           ename varchar2(10),
           job varchar2(18),
           mgr number(4),
           hiredate date,
           sal number(7,2),
           comm number(7,2),
           deptno number(2),
           member function get_emp_info 
           return varchar2
    ) instantiable  not final;
    create or replace type body emp_tbl_obj as
     member function get_emp_info return varchar2 is
      begin 
        return '员工编号:' || self.empno||'员工名称:'||self.ename||'职位:'
         ||self.job;
        endend;
        
        
        
    create view emp_view of emp_tbl_obj with object identifier(empno)
    as select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno
    from emp e;
    --使用对象类型的视图
    declare
         o_emp emp_tbl_obj;
    begin
      select value(e) into o_emp from emp_view e where empno=7369;
      DBMS_OUTPUT.put_line('员工'||o_emp.ename||' 的薪资为'||o_emp.sal);
      dbms_output.put_line(o_emp.get_emp_info);
      end;     
    --查看对象类型   attribute对象属性的个数,final指定对象是否可继承
    select type_name,attributes,final,typecode from user_types
    where type_name like 'EMP%' and typecode='OBJECT';
    --修改对象类型
    alter type employee_personobj add attribute mgr number(6cascade;
    --删除sal属性
    alter type employee_personobj drop attribute sal cascade;
    desc employee_personobj;
    --修改对象类型的成员方法
    alter type employee_personobj drop member function get_emp_info
    return varchar2 cascade;
    alter type employee_personobj add member function 
    get_employee return varchar2 cascade;
    create or replace type body employee_persvarchar2 is....end;
    onobj as
     member function get_emmloyee return 
    如果从基类删除一个方法,必须修改覆盖被删除方法的子类
    用alter type的casade选择判断是否有子类被影响
    (1)先从子类删除方法 (2)从基类删除方法,然后用不带overriding关键字的
    alter type把它重新添加进去
    
    ```
    
    
    
    
                   
  • 相关阅读:
    Java基础
    HTML笔记
    Java简介
    win8 图标 汇总 希望做Windows 8 应用的朋友用得着
    amCharts 的完整使用及破解[我弄过的]
    找了几个地方:发现关于SQL 的基本使用sql日期精确推算
    使用powerdesigner生成Pdm
    windows 8 Metro 设置选中ListViewItem 的样式
    关于DataTable与IList和List泛型集合的相互转换在网上总结
    C++ RString (原生字符串)
  • 原文地址:https://www.cnblogs.com/Remedy/p/8747702.html
Copyright © 2020-2023  润新知