1.包的定义:
create or replace package mypkg
is
procedure pkg_findSalByEmpno(eno in emp.empno%type,v_sal out emp.sal%type);
procedure pkg_findJobByEmpno(eno in emp.empno%type,v_job out emp.job%type);
function pkg_fun_findsalbyEmpno(eno emp.empno%type)return emp.sal%type;
end;
2.包的实现
实现时,所有的过程与函数的参数,返回值,名字等都必须与定义时完全一致;
实现时,包的名字必须与定义时的名字一致;
create or replace package body mypkg
is
procedure pkg_findSalByEmpno(eno in emp.empno%type,v_sal out emp.sal%type)
is
begin
select sal into v_sal from emp where empno=eno;
end;
procedure pkg_findJobByEmpno(eno in emp.empno%type,v_job out emp.job%type)
is
begin
select job into v_job from emp where empno=eno;
end;
function pkg_fun_findsalbyEmpno(eno emp.empno%type)return emp.sal%type
is
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno=eno;
return v_sal;
end;
end;
使用测试
set serveroutput on;
declare
mysal emp.sal%type;
fun_sal emp.sal%type;
begin
mypkg.pkg_findsalbyempno(7369,mysal);
dbms_output.put_line(mysal);
fun_sal:=mypkg.pkg_fun_findsalbyempno(7369);
dbms_output.put_line(fun_sal);
end;
********************************************************************************************
视图
省,市,区
简化查询的sql
创建视图,需要有create view权限,普通用户是不具有该权限的
此时需要登录system账户,然后给scott授权(dba),然后再次进入scott
grant dba to scott;
create or replace view 视图的名字 as sql语句;
例子1:查询所有员工信息,同时需要查询每一个员工所在部门的名字
create or replace view empdeptinfo as
select e.empno,e.ename,e.sal,d.dname
from emp e,dept d where e.deptno=d.deptno(+)
select * from empdeptinfo;
update emp set ename='zs' where empno=1001;
commit;
修改emp表中的数据之后,emp表里面的数据被更改;同时视图empdeptinfo里面的数据也被更改了;
select * from emp;
现在能通过视图来修改数据吗?
update empdeptinfo set ename='lisi' where empno=1001;
commit;
通过视图修改了数据之后,视图的数据被更改;同时emp表的数据也被更改了;
我们不希望通过视图来对数据做增,删,改操作:
create or replace view empdeptinfo as
select e.empno,e.ename,e.sal,d.dname
from emp e,dept d where e.deptno=d.deptno(+)
with read only;
视图修改为只读视图之后,将不能通过视图对数据进行增,删,改操作
update empdeptinfo set ename='zs' where empno=1001;
commit;
虽然不能通过视图修改数据了,但是依然可以通过原始表来操作数据;而视图只用来查询
update emp set ename='zs' where empno=1001;
commit;
************************************************************
市-->parentid----级联
create table address(
addressid number(8) primary key,
add_name varchar2(50),
add_no varchar2(50),
parent_no varchar2(50)
);
create sequence add_seq;
insert into address values(add_seq.nextval,'中国','1000000','-1');
insert into address values(add_seq.nextval,'广东省','1010000','1000000');
insert into address values(add_seq.nextval,'四川省','1020000','1000000');
insert into address values(add_seq.nextval,'深圳市','1010100','1010000');
insert into address values(add_seq.nextval,'广州市','1010200','1010000');
insert into address values(add_seq.nextval,'东莞市','1010300','1010000');
insert into address values(add_seq.nextval,'宝安区','1010101','1010100');
insert into address values(add_seq.nextval,'福田区','1010102','1010100');
insert into address values(add_seq.nextval,'龙岗区','1010103','1010100');
select * from address;
需要查询龙岗区的所有信息,同时包括龙岗区所在的市和省?
select a.add_name,b.add_name,c.add_name
from address a,address b,address c
where a.parent_no=b.add_no and b.parent_no=c.add_no
and a.add_name='龙岗区'
创建视图
create or replace view myaddress as
select a.add_name areaName,b.add_name cityName,c.add_name provinceName
from address a,address b,address c
where a.parent_no=b.add_no and b.parent_no=c.add_no
with read only;
根据视图来查询
select * from myaddress where areaName='龙岗区'
select * from myaddress where provinceName='广东省'
另一种语法(递归)
select a.* from address a start with a.addressid=3 connect by prior a.add_no= a.parent_no
emp->直接领导
select e.* from emp e start with e.empno=7369 connect by prior e.mgr=e.empno
**************************************************************************************************
触发器
语法:
create or replace trigger 名字 触发时机 触发事件
on 表名 for each row
begin
触发之后要完成的操作;
end;
名字,是自定义的;
触发时机:什么时候触发
before : 在。。。之前
after : 在...之后
触发事件: 发生什么操作的时候触发
insert : 增加时触发
update : 修改时触发
delete : 删除时触发
select * from stu;
insert into stu(stuname) values('zs');
commit;
针对表stu,创建触发器,实现主键自增
select * from stu;
1.创建序列
create sequence stu_seq start with 1004;
2.创建触发器
create or replace trigger stuAddPkTrigger
before insert on stu for each row
declare
v_id stu.stuid%type;
begin
select stu_seq.nextval into v_id from dual;
:new.stuid:=v_id;
end;
触发器创建好了之后,再次增加时,可以不写主键了
insert into stu(stuname) values('zs');