```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把它重新添加进去
```
