ORACLE第7天.txt
游标--- 处理多行记录
存储过程
函数
ORACLE中的程序包
PACKAGE
用途: <1>模块化
<例子> --公司的员工的管理
1.增加一个员工
2.员工离职
用存储过程和函数来实现
1.增加一个员工
create sequence seq1 start with 7935;
create or replace function insert_emp(
enm emp.ename%type, --员工的名字
ejob varchar2, --职务
mgr number, --上级
ehiredate date,--参加工作时间
esal number, --工资
ecomm number, --津贴
dno number)
return number
as
a number;
begin
--工号来自序列
select seq1.nextval into a
from dual;
--增加了一个员工
insert into emp values (
a,enm,ejob,mgr,ehiredate,esal,ecomm,dno);
return a;
end;
2.员工离职
create or replace procedure remove_emp(eno emp.empno%type) as
begin
delete from emp where empno = eno;
end;
把这2个功能设计成一个package(包)
包-- 说明部分+ 实现部分
建立说明部分:
create or replace package company_gl
as
--加员工
function insert_emp( enm emp.ename%type, --员工的名字ejob varchar2, --职务
mgr number, --上级
ehiredate date,--参加工作时间
esal number, --工资
ecomm number, --津贴
dno number) return number;
--员工离职
procedure remove_emp(eno emp.empno%type);
end;
--实现部分
create or replace package body company_gl
as
function insert_emp(
enm emp.ename%type, --员工的名字
ejob varchar2, --职务
mgr number, --上级
ehiredate date,--参加工作时间
esal number, --工资
ecomm number, --津贴
dno number)
return number
as
a number;
begin
--工号来自序列
select seq1.nextval into a
from dual;
--增加了一个员工
insert into emp values (
a,enm,ejob,mgr,ehiredate,esal,ecomm,dno);
return a;
end;
procedure remove_emp(eno emp.empno%type) as
begin
delete from emp where empno = eno; end;
end;
怎么用这个PACKAGE????
<1>包中存储过程
--7369的员工离开公司
execute company_gl.remove_emp(7369);
<2>使用函数
--新增加一个员工
declare
no number;
begin
no := company_gl.insert_emp(
'张三','CLERK',7499,sysdate,
1500,200,10);
dbms_output.put_line('员工号='||no);
end;
<2>包中的变量是全局变量
create or replace package my_pack
as
nn number; --放员工号
procedure get_empno(enm varchar2); end;
create or replace package body my_pack
as
procedure get_empno(enm varchar2)
as
begin
select empno into nn from emp
where ename = enm;
end;
end;
--独立存储
create or replace procedure get_name as
nm varchar2(20);
begin
--my_pack包中的nn是全局的
select ename into nm from emp
where empno = my_pack.nn;
dbms_output.put_line('员工姓名='||nm);
end;
<3>返回结果集合
create or replace package emp_pack
as
--动态游标
type curemp is ref cursor return emp%rowtype;
--返回引用游标
function get_emp_record(ejob varchar2)
return curemp;
end;
create or replace package body emp_pack
as
function get_emp_record(ejob varchar2)
return curemp
as
v_emp curemp;
begin
open v_emp for select * from emp
where job = ejob;
return v_emp;
end;
end;
select emp_pack.get_emp_record('MANAGER') from dual;
<4>包好处效率高
JA V A 连接ORACLE数据库
JDBC -- <1>纯JA V A驱动
<2>本地驱动
把EMP表中的员工信息取出来显示
<1>使用纯JA V A驱动
classes12.jar 路径D:\oracle\ora90\jdbc\lib
<2>本地驱动(OCI驱动)
<3>增删改查
--存储过程
create or replace procedure p1(eno number,enm out varchar2) as
begin
select ename into enm from
emp where empno = eno;
end;
--函数
create or replace function f1(eno number) return varchar2 as
enm varchar2(20);
begin
select ename into enm from
emp where empno = eno;
return enm;
end;
--包中的存储
create or replace package test_pack
as
procedure getname(eno number,enm out varchar2); function f_get_name(eno number) return
varchar2;
end;
create or replace package body test_pack
as
procedure getname(eno number,enm out varchar2)
as
begin
select ename into enm from emp
where empno = eno;
end;
function f_get_name(eno number) return
varchar2 as
n varchar2(20);
begin
select ename into n from emp
where empno = eno;
return n;
end;
end;
--编程
PL/SQL语言
存储过程和函数*********
模块化-程序包
触发器
<1>加强约束条件
<2>实现关联操作在一个表中修改数据导致另一个表数据的修改
--ORACLE中的触发器
用途:加强约束条件的
--希望在表中插入的数据的日期大于系统时间
Create table test(
xh number(2) primary key,
hdate date check (hdate > sysdate)
);
数据库无法建立的
Create table test(
xh number(2) primary key,
hdate date check (hdate > to_date('20050501','yyyymmdd'))
);
这个可以建
建立触发器的语法
create or replace trigger <触发器名>
after/before insert/update/delete on <表名>
for each row
begin
exception
end;
--触发器中的PL/SQL块(DML / tcl)可以写什么样的SQL语句呢DML语句别的都不能写(COMMIT/rollback都不能写)
after/before 以后/以前
insert/update/delete 触发的SQL语句
for each row 行级- 语句级
--sql语句和触发器的Pl/SQL形成一个整体的事务
--当表emp中加入员工的时候，如果这个员工的部门
在部门表中没有，则在部门表中dept插入该部门信息,
要求部门编号一定要大于77
--可以使用when的选项来改善触发器的效率
create or replace trigger emptr
before insert on emp --insert语句触发
for each row --行级触发器
when (new.deptno >77 and new.ename='JOHN') --条件(满足条件才触发) declare
n_count number;
begin
--先检查加入的员工的部门是否存在
--:new代表新插入到EMP中的那条记录EMP%rowtype
--if :new.deptno > 77 then
select count(*) into n_count
from dept where deptno = :new.deptno;
--如果不存在就在部门表中加入该部门
if n_count = 0 then
insert into dept values (:new.deptno,
'人事','北京');
end if;
-- end if;
end;
--触发语句
insert into emp(empno,ename,deptno) values (9000,'mike',50);
--for each row
--日志表
create table log(
id number ,
ndate date
);
--触发器
create or replace trigger emptr2
after update on emp
for each row
begin
--update
--改后的值:new.empno :new.ename
--改前的值:old.empno :old.ename
--insert
--只有:new
--delete
--只有:old
insert into log values (:new.empno,sysdate); end;
--修改1条记录,触发器触发几次??(1次) update emp set sal=2000 where empno=7369;
--改14条记录，触发器工作几次？？(14次) update emp set sal=1000 ; 14次
--语句级
create or replace trigger emptr2
after update on emp
begin
insert into log values (222,sysdate);
end;
--一句话，改14条记录，触发器工作几次？？？？1次update emp set sal=1000;
--行级根据sql语句的影响记录的行数来决定触发的次数--语句级根据sql语句的个数来决定触发的次数
-- 使用最多的是行级触发器
--什么语句触发的触发器可以用
--inserting(boolean值) insert语句
--updating(boolean值) update语句
--deleting(boolean值) delete语句
--使用触发器的时候容易犯的错误
--变异表
--不能够在触发器对触发表做select操作
create or replace trigger em1
after delete on emp
for each row
declare
n number;
begin
select count(*) into n from emp;
end;
--如何防止错误删除???
delete from dept;
--触发器
create or replace trigger tr_del
before delete on dept
for each row
begin
--内部引起异常
raise_application_error(-20001,'不能删除');
end;
alter trigger tr_del disable; --使触发器不工作
alter trigger tr_del enable; --使触发器工作
--希望在表中插入的数据的日期大于系统时间
create or replace trigger tr_test
after insert or update of hdate
or delete on test
for each row
begin
if inserting then --insert语句触发
dbms_output.put_line('insert');
--插入的每一行用:new表示(test%rowtype)
if :new.hdate <= sysdate then
--不能进行数据插入
raise_application_error(-20001,'日期小于系统时间'); end if;
elsif updating then --update语句触发
dbms_output.put_line('update');
if :new.hdate <= sysdate then
--不能进行数据插入
raise_application_error(-20001,'日期小于系统时间'); end if;
elsif deleting then --delete语句触发
dbms_output.put_line('delete');
end if;
exception
when others then
raise_application_error(-20002,'日期小于系统时间'); end;
--希望在表中插入的数据的日期大于系统时间Create table test(
xh number(2) primary key,
hdate date );
create or replace trigger tr_test
after insert on test
for each row
begin
if :new.hdate <= sysdate then
raise_application_error(-20001,'小于系统时间');
end if;
end;
insert into test values (1,to_date('20050401','yyyymmdd'));
insert into test values (1,sysdate+1);
--ORACLE中的内置程序包
DBMS_JOB 自动作业调度
能不能写一个存储过程每隔一定时间自动运行(定时器) dbms_job.submit() 提交作业到系统指定它什么时候运行
--DBMS_JOB 作业调度包(指定PL/SQL过程定时自动执行)
--初始化参数init.ora
job_queue_processes = 1 --作业队列进程的个数(最大36个) job_queue_interval = 60 --进程每隔多少时间扫描作业队列(60s)
processes=150 --并发用户的数量
open_cursors =300 --存储过程中使用游标的数量
--DBMS_JOB包中的方法
procedure submit(job out binary_integer,
what in varchar2,
next_date in date default sysdate,
interval in varchar2 default null,
no_parse in boolean default false);
procedure change(job in binary_integer,
what in varchar2,
next_date in date,
interval in varchar2);
procedure what(job in binary_integer,
what in varchar2);
procedure next_date(job in binary_integer,
next_date in date);
procedure interval(job in binary_integer,
interval in varchar2);
--实际例子
create sequence temp_seq
start with 1;
create table temp_table(
num_col number,
char_col varchar2(50));
create or replace procedure tempinsert is
begin
insert into temp_table(num_col,char_col)
values (temp_seq.nextval,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));
commit;
end;
--注册每10秒运行一次
declare
v_job number;
begin
dbms_job.submit(v_job,'tempinsert();',sysdate,'sysdate + (10 / (24 * 60 * 60))'); commit;
end;
--删除作业
dbms_job.remove(job in binary_integer);
--查看作业信息
select * from user_jobs;
select * from dba_jobs;
内置程序包大约有25个都是sys拥有