```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 number) is begin dbms_output.put_line('员工编号为'||p_empno); end; procedure getSalary(p_empname in varchar2) is begin dbms_output.put_line('员工名称为'||p_empname); end; procedure getSalary(p_empno in number,p_empname in varchar2) is 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.67) from 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(200) null; 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 NULL) THEN 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; end; end; 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; end; end; 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(6) cascade; --删除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把它重新添加进去 ```