–将方法和过程用包定义
create or replace package pkg_emp
as
–输入员工编号查询出员工信息
procedure pro_findInfo(
in_empno emp2.empno%type,
out_name out emp2.ename%type,
out_sal out emp2.sal%type
);
–根据部门编号修改本部门员工工资
procedure pro_editInfo(
in_emp_record emp2%rowtype,
out_flag out boolean
);
–输入新员工信息并保存到数据库
procedure pro_addInfo(
in_emp_new_record emp2%rowtype
);
–统计工资信息
function fun_sum(
num_a number,
num_b number
) return number;
end pkg_emp;

–实现包
create or replace package body pkg_emp
as
–输入员工编号查询出员工信息
procedure pro_findInfo(
in_empno emp2.empno%type,
out_name out emp2.ename%type,
out_sal out emp2.sal%type
)
as
begin
select ename, sal into out_name, out_sal from emp2 where empno = in_empno;
end pro_findInfo;

--根据部门编号修改本部门员工工资
procedure pro_editInfo(
  in_emp_record emp2%rowtype,
  out_flag out boolean
)
is         
begin
  update emp2 set sal = in_emp_record.sal where deptno = in_emp_record.deptno;
  out_flag := true;
  /*exception
    when no_data_found then
      out_flag := false;
  commit;*/
  if (sql%rowcount < 1) then
    out_flag := false;
  else
    out_flag := true;
    commit;
  end if;
end pro_editInfo;

--输入新员工信息并保存到数据库
procedure pro_addInfo(
  in_emp_new_record emp2%rowtype
)
as
  temp_sql varchar2(200);
begin
  temp_sql := 'insert into emp2(empno, ename, sal, comm, deptno) values(:1, :2, :3, :4, :5)';
  execute immediate temp_sql using in_emp_new_record.empno, in_emp_new_record.ename,
        in_emp_new_record.sal, in_emp_new_record.comm, in_emp_new_record.deptno;
  commit;
end;

--统计工资信息
function fun_sum(
  num_a number,
  num_b number
) return number
is
begin
  return num_a + num_b;
end fun_sum;

end pkg_emp;

–测试1
declare
out_name emp2.ename%type;
out_sal emp2.sal%type;
begin
pkg_emp.pro_findInfo(7369, out_name, out_sal);
dbms_output.put_line(out_name);
dbms_output.put_line(out_sal);
end;

–测试2
select * from emp2;
declare
in_emp_record emp2%rowtype;
flag boolean;
begin
in_emp_record.deptno := &部门编号;
in_emp_record.sal := &员工工资;
pkg_emp.pro_editInfo(in_emp_record, flag);
if (flag = false) then
dbms_output.put_line(‘no’);
else
dbms_output.put_line(‘yes’);
end if;
end;

–测试3
declare
new_emp_record emp2%rowtype;
begin
new_emp_record.empno := &员工编号;
new_emp_record.ename := &姓名;
new_emp_record.sal := &工资;
new_emp_record.comm := &奖金;
new_emp_record.deptno := &部门编号;
pkg_emp.pro_addInfo(new_emp_record);
end;

–测试4
declare
sum_emp number;
begin
select pkg_emp.fun_sum(sal, nvl(comm, 0)) into sum_emp from emp2
where empno = &员工编号;
dbms_output.put_line(‘员工总工资:’ || sum_emp);
end;
版权维护转载hoojo博客的。

————————抽象数据类型———–
–创建地址类型，一定要加as object，还可以在类型中加过程或方法
create or replace type address as object (
province varchar2(10), –省份属性
city varchar2(10) –市属性
) not final; –not final表示该类型可以有子类型

–定义一个子类型
–under address说明这个类型继承至address类型
create or replace type detailAddress under address (
street varchar2(20) –街道属性 第3个成员
)；

–创建员工信息表，最后一列是detailAddress类型
drop table empInfo
create table empInfo (
eName varchar2(20) , –员工姓名
eSex char(2), –性别
eAge int, –年龄
eAddress detailAddress –员工地址
);

–增加数据,只能用构造方法
insert into empInfo values(‘aaa’, ‘男’, 28, detailAddress(‘湖北’, ‘襄樊’, ‘八一路’));
insert into empInfo values(‘bbb’, ‘男’, 26, detailAddress(‘湖北’, ‘武汉’, ‘永清街’));
insert into empInfo values(‘ccc’, ‘女’, 29, detailAddress(‘湖北’, ‘武汉’, ‘光谷’));

–查询
select from empInfo;
select from empInfo where eSex = ‘男’;
select * from empInfo e where e.eAddress.city = ‘武汉’; –如果查询条件包含属性必须用表的别名
–更新有2种方式：
–第一种方式：整体更新
update empInfo e set e.eAddress = detailAddress(‘湖北’, ‘武汉’, ‘武昌’) where e.eName = ‘ccc’;
–第二种方式：只更新抽象类型的某一列
update empInfo e set e.eAddress.city = ‘武汉’ where e.eName = ‘ccc’;

–删除
delete from empInfo e where e.eAddress.city = ‘武汉’;

–为抽象数据类型的属性建立索引
create index idxemp on empInfo(eAddress.city);
–删除
drop table empInfo;
drop type address force; –强制删除抽象类型

——————————-抽象数据类型结束———————-

——————对象表，表中的每一行就是一个对象———————–
–创建抽象数据类型person,并作为基类型
create or replace type person as object (
pName varchar2(20), –姓名
pSex char(2), –性别
pAge int –年龄
) not final;

–创建子类型student，继承person
–后面不要加as object
create or replace type student under person (
stuId int
);

–创建对象表stuInfo
create table stuInfo of student;
–为对象表创建主键约束
alter table stuInfo add constraint pk_stuInfo primary key(stuId);
–插入数据，当普通表插入
insert into stuInfo values(‘aaa’, ‘男’, 29, 1001);
–插入数据，用构造方法
insert into stuInfo values(student(‘bbb’, ‘男’, 26, 1002));
insert into stuInfo values(student(‘ccc’, ‘女’, 29, 1003));
–查询，当普通表用
select * from stuInfo where stuId = 1002;

–更新和删除都用普通的sql语句即可
update stuInfo set pAge = 29 where pName = ‘ccc’;
delete from stuInfo where stuId = 1001;
rollback;

–ref(表别名)函数用来返回对象的OID，也就是对象标识符，对象表也有rowid
select ref(s) from stuInfo s;
select rowid, ref(s) OIDS from stuInfo s;

–创建学生分数表，注意外键
create table stuScore (
stu ref student, –stu这一列的值必须出现在stuInfo表中，且stu这一列存的对象的OID而不是对象本身
score int –分数
);
–向分数表插入数据，只能用select，不能用普通的values
–错误的做法：insert into stuscore values(select ref(s) from stuInfo where stuId = 1001, 90)
–正确的做法：
insert into stuscore select ref(s), 90 from stuInfo s where stuId = 1001;
insert into stuscore select ref(s), 80 from stuInfo s; –插入3行数据
insert into stuscore select ref(s), 70 from stuInfo s where stuId = 1003;

–查询
select * from stuScore;
–deref(列名)函数可以把OID还原为对象，主键列显示有问题
select deref(s.stu), score from stuScore s where s.stu.stuId = 1001;

–修改，以下2个都可以
update stuScore set score=100 where stu = (select ref(s) from stuInfo s where stuId = 1001);
update stuScore s set score = 99 where s.stu.stuId = 1001;
–删除，以下3个都可以
delete from stuScore where stu = (select ref(s) from stuInfo s where stuId = 1001);
delete from stuScore s where s.stu.stuId = 1001;
delete from stuScore where stuId = 1001;
———————————-对象表结束———————-

———————————-对象视图———————–
–对象视图的作用：把已经存在的关系表转换为对象表来使用，原表没有变
–首先要创建一个和原表一样的类型
–然后创建视图
create table aaa
(a int);
create type aaaa as object
(a int);
create or replace view view_stu of aaaa with object oid(a)
as
select * from aaa;

select * from view_stu;
–增删改查都和对象表一样
——————————-对象视图结束———————–

——————————–抽象类型，包含过程和方法————-
create or replace type ADDRESS as object (
province varchar2(10), –省份
city varchar2(10), –市,后面的,不能少
member function get_pro return varchar2, –函数，后面接,而不是;
member function get_city return varchar2,
member procedure set_pro(pro varchar2), –过程
member procedure set_city(cy varchar2)
);

create or replace type body ADDRESS–后面不能加 as object
as –后面不能加begin

member function get_pro return varchar2
is
begin
return province;
end get_pro;

member function get_city return varchar2
is
begin
return city;
end;

member procedure set_pro(pro varchar2)
is
begin
province := pro;
end;
member procedure set_city(cy varchar2)
is
begin
city := cy;
end;
end;

–测试上面的成员函数和过程
declare
addr address;
begin
addr := address(‘湖北’, ‘武汉’);
dbms_output.put_line(addr.get_city);
end;

–drop table stuInfo;
create table stuInfo (
stuId int primary key,
addr address
);

declare
addr address;
begin
addr := address(‘湖北’, ‘武汉’);
insert into stuInfo values(1, addr);
addr.set_city(‘郑州’);
addr.set_pro(‘河南’);
insert into stuInfo values(2, addr);
end;

select * from stuInfo;
–删除类型
drop type address force;
————————–抽象类型，包含过程和方法 结束————-
—————————-可变数组——————————
–就是一个可以存储多个值的有最大长度的数组，数组的成员可以是任意类型
–建立一个可变数组类型,长度是10，存放的数据类型是number(4)
create or replace type arrType as varray(10) of number(4);

create or replace type scoreType as object (
subName varchar2(10),
score int
);
–创建一个长度为10的可变数组，存放数据类型是scorType
create or replace type arrScoreType as varray(10) of scoreType;
–创建学生信息表
–drop table stuInfo;
create table stuInfo (
stuId int primary key,
score arrScoreType –可变数组，最多10个成员
);
–插入数据,用可变数组的构造函数
insert into stuInfo values(1, arrScoreType(
scoreType(‘sql’, 50), scoreType(‘C#’, 80), scoreType(‘java’, 90)));
insert into stuInfo values(2, arrScoreType(
scoreType(‘sql’, 60), scoreType(‘C#’, 85), scoreType(‘java’, 95), scoreType(‘html’, 60)));
insert into stuInfo values(3, arrScoreType(
scoreType(‘sql’, 70), scoreType(‘java’, 93)));
–查询
select from stuInfo; –查询结果是集合
–如何才能查询出可变数组里的数据呢？思路是：用table函数把集合转化为表，然后再从这个表查询数据
select from table(select s.score from stuInfo s where s.stuId = 2);
–table函数里面只能是一个可变数组

select s.stuId, t.* from stuInfo s,
table(select score from stuInfo where stuId = s.stuId) t
where s.stuId = 2;
–更新，整个可变数组一起更新，不能只更新数组的某个元素
update stuInfo set score = arrScoreType(
scoreType(‘sql’, 50), scoreType(‘C#’, 80)) where stuId = 1;
–删除，按主键删除
—————————–可变数组结束———————————
drop type scoreType force;
drop type arrScoreType force;
drop table stuInfo;

—————————–嵌套表—————————
–创建抽象类型
create or replace type scoreType as object (
subName varchar2(10),
score int
);
–创建嵌套表类型
create or replace type nestTable is table of scoreType;
–创建包含嵌套表的学生信息表
create table stuInfo (
stuId int,
score nestTable –其实存的是引用，实际数据存在abc表中
) nested table score store as abc;
–nested table score store as abc意思是：stuInfo这个表中的score这一列是嵌套表类型，嵌套表实际是存在abc这个表中

–增删和可变数组一样
insert into stuInfo values(3, nestTable(
scoreType(‘sql’, 70), scoreType(‘java’, 93)));
–查询，思路：把嵌套表先查出来，然后把嵌套表和stuInfo进行联合查询
select from table(select ss.score from stuInfo ss where stuId = 3);
select s.stuId, t. from stuInfo s, table(select ss.score from stuInfo ss where stuId = s.stuId) t
where s.stuId = 3;
–更新
update table(select ss.score from stuInfo ss where stuId=3) t
set t.score = 80 where t.subName = ‘sql’;
–删除
delete from table(select ss.score from stuInfo ss where stuId = 3) t
where t.subname=’sql’;

—-可变数组和嵌套表的异同—————-
相同点：
1、都是抽象类型
2、都可以作为表中某列的数据类型（record和快表是不能作为列的数据类型的）
不同点：
1、可变数组本身就存放在原表中，而嵌套表存放在另外的表中
2、可变数组有大小限制，而嵌套表没有
3、可变数组更新时必须更新整个可变数组，而嵌套表更新时可以只更新嵌套表中的部分记录
版权维护转载hoojo博客的。

–创建函数
create or replace function add_sal(sSal number)
return number
is
begin
if (sSal > 5000) then
return sSal + 51;
elsif (sSal > 3000) then
return sSal + 111;
else
return sSal + 222;
end if;
end;

select sal, add_sal(sal) from emp;

–触发器
create table deptLog(
uName varchar2(20),
action varchar2(20),
dTime date
);

–创建触发器
–for each row 可以触发多条，当你的语言影响多少条记录就会触发多少次
create or replace trigger trig_dept2
after insert or delete or update on dept2/ for each row/
begin
if inserting then
insert into deptLog values(user, ‘insert’, sysdate);
elsif updating then
insert into deptLog values(user, ‘update’, sysdate);
elsif deleting then
insert into deptLog values(user, ‘delete’, sysdate);
end if;
end;

select from dept2;
select from deptLog;
insert into dept2 values(55, ‘SOFTWARE’, ‘cic’);
update dept2 set loc = ‘go’ where deptno in(30);
delete dept2 where deptno = 55;

–触发器
create or replace trigger trig_emp
after update on dept for each row
begin
update emp set emp.deptno =: new.deptno where emp.deptno = :old.deptno;
end;

update dept set deptno = 11 where deptno = 10;
select * from emp;

—语句级触发器
create or replace trigger trg_ins_dept2
before insert
on dept2
begin
if user not in(‘SCOTT’) then
raise_application_error(-20001, ‘只有SCOTT才能修改该表!’);
end if;
end;

禁用、启用触发器
alter trigger tgr_Name disable | enable;
alter table tableName disable all trigger | enable all trigger;
版权维护转载hoojo博客的。

–演示隐式游标,系统自动声明，自动打开，自动使用并且自动关闭
begin
update emp set sal = 1000;
dbms_output.put_line(‘影响的行数：’ || sql%rowcount);
end;

rollback;

/游标的使用方法：
第一步：声明游标
第二步：打开游标
第三步：使用游标进行循环操作
第四步：关闭游标/

–普通游标，游标本身就是一个变量
declare
–下面的这行代码声明了一个游标
cursor mycur is select * from emp where deptno = 20;
emprow emp%rowtype;
begin
open mycur; –打开游标
loop
fetch mycur into emprow; –把游标所指的纪录放到变量中
exit when (mycur%notfound); –当游标没有指向行时退出循环
dbms_output.put_line(‘名字：’ || emprow.ename || ‘薪水：’ || emprow.sal);
end loop;
close mycur; –关闭游标
end;

–简单游标，列操作
declare
empname emp.ename%type;
empsal emp.sal%type;
cursor mycur is select ename,sal from emp where deptno = 30;
begin
open mycur;
loop
fetch mycur into empname,empsal;
exit when mycur%notfound;
dbms_output.put_line(‘姓名:’ || empname || ‘工资’ || empsal);
end loop;
end;

–简单游标，列操作
declare
cursor c
is
select * from dept;
vDept_row_record c%rowtype;
begin
open c;
fetch c into vDept_row_record;
dbms_output.put_line(vDept_row_record.dname);
close c;
end;

–when循环游标
declare
cursor c
is
select * from dept;
vDept_row_record c%rowtype;
begin
open c;
loop
fetch c into vDept_row_record;
exit when(c%notfound);
dbms_output.put_line(vDept_row_record.dname);
end loop;
close c;
end;

–while循环游标
declare
cursor c
is
select * from dept;
vDept_row_record c%rowtype;
begin
open c;
fetch c into vDept_row_record;
while (c%found) loop
dbms_output.put_line(vDept_row_record.dname);
fetch c into vDept_row_record;
end loop;
close c;
end;

–for循环游标
declare
cursor c
is
select * from dept;
vDept_row_record c%rowtype;
begin
for vDept_row_record in c loop
dbms_output.put_line(vDept_row_record.dname);
end loop;
end;

–带参游标
declare
cursor c(sSal emp.sal%type, sEmpno emp.empno%type)
is
select * from emp where sal >= sSal and empno > sEmpno;
begin
for record_data in c(2500, 6666) loop
dbms_output.put_line(record_data.ename);
end loop;
end;

–update游标
declare
cursor c(sSal emp2.sal%type)
is
select * from emp2 where sal >= sSal for update;
begin
for record_data in c(2500) loop
if (record_data.sal < 3000) then
update emp2 set sal = sal + 3 where current of c;
dbms_output.put_line(record_data.ename);
elsif (record_data.sal = 5000) then
update emp2 set sal = sal - 3 where current of c;
dbms_output.put_line(record_data.ename);
end if;
end loop;
end;

–引用游标不能使用循环游标的语法
–引用游标不能进行删除和修改
–引用游标是一个数据类型，使用该类型必须声明变量

–弱类型引用游标，就是不指定游标将要提取的数据行的类型
declare
type my_cur_type is ref cursor;
mycur my_cur_type;–声明变量
which varchar2(10);
deptrow dept%rowtype;
emprow emp%rowtype;
begin
which := ‘&请选择dept还是emp’;
if (which = ‘dept’) then
open mycur for select from dept;
loop
fetch mycur into deptrow;
exit when (mycur%notfound);
dbms_output.put_line(deptrow.deptno || ‘ ‘ || deptrow.dname);
end loop;
elsif (which = ‘emp’) then
open mycur for select from emp;
loop
fetch mycur into emprow;
exit when (mycur%notfound);
dbms_output.put_line(emprow.empno || ‘ ‘ || emprow.ename);
end loop;
end if;
close mycur;
end;

–强类型引用游标，就是指定游标将要提取的数据行的类型 ，只能是record或%rowtype类型
–比如：return number是错的，return emp.ename%type也是错的
declare
type mycurtype is ref cursor return emp%rowtype;
mycur mycurtype;–声明变量
emprow emp%rowtype;
begin
open mycur for select * from emp;
loop
fetch mycur into emprow;
exit when mycur%notfound;
dbms_output.put_line(emprow.empno || ‘ ‘ || emprow.ename);
end loop;
close mycur;
end;
版权维护转载hoojo博客的。

–create or replace 创建或替换，如果存在就替换，不存在就创建
create or replace procedure p
is
cursor c
is
select * from dept2 for update;
begin
for row_record in c loop
if (row_record.deptno = 30) then
update dept2 set dname = substr(dname, 0, length(dname) - 3) where current of c;
end if;
end loop;
end;

exec p;

begin
p;
end;

–带参存储过程
–in 输入参数，不带in out 默认输入参数
–out 输出参数
–in out 同时带的是输入输入参数
create or replace procedure p2(
a in number,
b number,
s_result out number,
s_temp in out number
)
is
begin
if (a > b) then
s_result := a;
else
s_result := b;
end if;
s_temp := s_temp + 3;
end;

–调用存储过程
declare
v_a number := 4;
v_b number := 6;
v_result number;
v_temp number := 5;
begin
p2(v_a, v_b, v_result, v_temp);
dbms_output.put_line(v_a);
dbms_output.put_line(v_b);
dbms_output.put_line(v_result);
dbms_output.put_line(v_temp);
end;

—删除一个表的过程
create or replace procedure drop_table(tname varchar2)
as
total int := 0;
begin
select count() into total from user_tables
where table_name = upper(tname);
if total >= 1 then
execute immediate ‘drop table ‘||tname; –此处必须用动态sql
end if;
end;
select from user_tables;

–递归存储过程
create or replace procedure pro_emp(sEmpno emp.empno%type, sLevel integer)
is
cursor c is select * from emp where mgr = sEmpno;
prefixStr varchar(255);
begin
for i in 1..sLevel loop
prefixStr := prefixStr || ‘—-‘;
end loop;

for row_data in c loop
dbms_output.put_line(prefixStr || row_data.ename);
pro_emp(row_data.empno, sLevel + 1);
end loop;
end;

select * from emp;
begin
pro_emp(7839, 0);
end;
版权维护转载hoojo博客的。