Oracle数据库语句 简单的存储过程+触发器+游标
select *from student;
select *from "course";
select t.*, t.rowid from uep_contract t
DROP TABLE "course";
create table dept1 as
select rownum as num,t.*from dept t
truncate table dept1
drop table dept1
delete dept1
select *from emp e where e.deptno in(201,202)
--修改
select e.*,ROWID from emp e where e.job='CLERK'
select e.*from emp e where e.job='CLERK'
select *from emp e where e.sal=(
select Max(e.Sal) from emp e WHERE e.job='CLERK')
20131030
--复制表结构及数据
create table dept1 as
select rownum as num,t.* from dept t
create table emp1 as
select rownum as num,t.* from emp t
--查询部门创建时间最早的前3条记录
select * from (
select * from dept t order by t.cdate) where rownum<4
--删除表数据
truncate table dept1
select * from dept1
delete (
select d.* from dept1 d
where d.num=1)
commit;
delete from dept1 d where d.num=2
--删除表
drop table dept1
drop table emp1
--删除表数据，需要提交
delete dept1
SELECT e.*,rowid FROM EMP e
select e.*,ROWID from emp e where e.job='CLERK'
--查询当前用户下用户表名包含DEPT字母的所有表
SELECT * FROM user_tables where table_name like '%DEPT%'
--查询201或202部门下的所有人
SELECT * FROM EMP E WHERE E.DEPTNO IN (201,202)
--查询工作岗位是办事员的工资最高的人员名称
SELECT * FROM EMP E WHERE E.SAL=(
SELECT MAX(E.SAL) FROM EMP E WHERE E.JOB='CLERK')
--查询工作岗位是办事员的姓名和年薪
SELECT e.ename as 姓名, nvl(e.sal,0)*12+nvl(e.comm,0) 年薪 FROM EMP E
WHERE E.JOB='CLERK'
--复制表结构及数据
create table emp1 as
select rownum as num,t.* from emp t
select e.*,ROWID from emp1 e
select *from emp1
--查询不存在于DEPT表中所有人员
select * from emp1 e where not exists (select * from dept1 d where d.deptno=e.deptno)
select * from emp1 e where exists (select 1 from dept1 d where d.deptno=e.deptno)
--去除重复数据
select e.*from emp1 e
select distinct e.ename from emp1 e
minus
select d.ename from(
select e.ename,count(*)from emp1 e group by e.ename
having count(*)=1)d
--两表合并数据，去除重复行
select e.* from emp1 e
union
select rownum, a.* from emp a
--两表合并数据
select e.*from emp1 e
union all
select rownum as num, a.* from emp a
--EMP1表数据减去EMP表数据，差集
select e.* from emp1 e
minus
select rownum as num, a.* from emp a
--EMP1表数据与EMP表数据的交集
select e.* from emp1 e
intersect
select rownum as num, a.* from emp a
--复制表结构
create table emp4 as
select * from emp where 1=2
select *from emp4
drop table emp4
--20131031
select t.deptno,t.dname,to_char(t.cdate,'YYYY-mm') cdateyear from dept t
select t.deptno,t.dname,to_date('2013-10-15','YYYY-MM-DD') from dept t
select to_date('2013-10-15','YYYY-MM-DD') from dual
SELECT to_char(to_date('2013-10-15','YYYY-MM-DD'),'YYYY')from dual
SELECT to_char(sysdate,'YYYY')from dual
select sysdate from dual
select sysdate from dept
select distinct t.cdate,t.upperdeptno from dept t
select t.cdate,t.upperdeptno from dept t
select distinct e.ename from emp1 e
--列别名时，可以加as，也可以不加as
select nvl(e.sal,0)*12+nvl(e.comm,0) as 年薪 from emp1 e
select nvl(e.ename,'无名氏') from emp1 e
select nvl(e.hiredate,to_date(to_char(sysdate,'yyyy-mm-dd'),'YYYY-MM-DD')) from emp1 e
select '此员工的姓名为：【' || e.ename || '】' from emp1 e
select '【' || e.ename || '】的岗位是：'|| e.job from emp1 e
select e.ename from emp1 e where e.job='MANAGER' and e.ename like '刘%'
select e.ename from emp1 e where e.job='MANAGER' and e.ename like '%飞%'
--查询雇用日期在2011年前的所有人员信息
select * from emp1 e where to_char(e.hiredate,'yyyy')<'2011'
select * from emp1 e where e.ename like '刘_' or e.ename like '李_'
select * from emp1 e where e.job in ('CLEAR','MANAGER')
select * from emp1 e where e.ename is null
select e.*,rowid from emp1 e
select * from emp1 e where e.ename is not null
select e.empno,e.ename,e.job
from (select * from emp1 e where e.ename like '王_' or e.ename like '李_')e
where to_char(e.hiredate,'yyyy')<'2011'
--查询人员表当中姓王或者姓李的，并且雇佣日期在2011年之前的人员编号、姓名及岗位
select e.empno,e.ename,e.job
from emp1 e
where (e.ename like '王%' or e.ename like '李%')
and to_char(e.hiredate,'yyyy')<'2011'
select * from emp1 e order by nvl(e.sal,0) desc,e.comm desc
--根据别名进行排序
select nvl(e.sal,0)*12+nvl(e.comm,0) as 年薪 from emp1 e order by 年薪 desc
select nvl(e.sal,0)*12+nvl(e.comm,0) as nianxin from emp1 e order by nianxin desc
--根据列位置进行排序
select *from dept
select *from emp
SELECT to_char(deptno) as temp,dname FROM dept
UNION
SELECT empno,ename FROM emp
ORDER BY 1
--UNION的SQL也可以根据别名进行排序
SELECT to_char(deptno) as temp,dname FROM dept
UNION
SELECT empno,ename FROM emp
ORDER BY temp
select max(e.sal),min(e.sal),avg(e.sal),sum(e.sal),count(*) from emp1 e
select e.job,count(*),min(e.empno)
from emp1 e
group by e.job
--查询人员表中姓李的存在重名的人员名称及人数
select e.ename,count(*)
from emp1 e
where e.ename like '华%'
group by e.ename
having count(*)>1
select e.*,rowid from emp1 e
--统计每个部门最高工资，单列分组统计
select e.deptno,max(nvl(e.sal,0))
from emp1 e group by e.deptno
order by max(nvl(e.sal,0)) desc,e.deptno --根据最高工资倒序和部门编号升序排序，
--统计每个部门每个岗位最高工资 ，多列分组统计
select e.deptno,e.job,max(nvl(e.sal,0))
from emp1 e group by e.deptno,e.job
having max(nvl(e.sal,0))>2000 --只显示最高工资大于2000的分组数据
select e.deptno,e.job,max(nvl(e.sal,0))
from emp1 e group by e.deptno,e.job
having max(nvl(e.sal,0))>2000
--相等连接、内连接，查询人员姓名及所属部门名称
select e.ename,d.dname
from emp1 e ,dept1 d
where e.deptno=d.deptno
--查询人员姓名及上级领导姓名
select e.ename,a.ename
from emp1 e ,emp1 a
where e.mgr=a.empno
--左外连接，以左表为主，显示人员表的所有内容
select e.ename,nvl(d.dname,'无所属部门')
from emp1 e LEFT JOIN dept1 d
ON e.deptno=d.deptno
select *from emp1
delete from emp1 where NUM=22
select *from dept1
--左外连接，以左表为主，显示部门表的所有内容
select e.ename,d.dname,d.deptno
from dept1 d LEFT JOIN emp1 e
ON d.deptno=e.deptno
--右外连接，以右表为主，显示部门表的所有内容
select e.ename,nvl(d.dname,'无所属部门')
from emp1 e right JOIN dept1 d
ON e.deptno=d.deptno
--（+）以部门表为主，显示部门表的所有内容，看+号对面
select e.ename,d.dname
from emp1 e ,dept1 d
where e.deptno(+)=d.deptno
--（+）以人员表为主，显示人员表的所有内容
select e.ename,d.dname
from emp1 e ,dept1 d
where e.deptno=d.deptno(+)
--完全外连接，将两个表都不满足条件的数据都查询出来
select e.ename,d.dname,d.deptno
from dept1 d full JOIN emp1 e
ON d.deptno=e.deptno
--子查询，在WHERE语句当中使用子查询
select d.dname from dept1 d
where d.deptno=(select deptno from emp1 e where e.ename ='刘鹏飞')
=
select e.ename,d.dname
from dept1 d ,emp1 e where d.deptno=e.deptno and e.ename ='刘鹏飞'
--子查询，在WHERE语句使用返回多条记录使用IN
select d.dname from dept1 d
where d.deptno in (select deptno from emp1 e where e.ename like '李%')
select *from emp1
--子查询，返回多列数据
SELECT ename,job,sal,deptno FROM emp
WHERE (deptno,job)=(SELECT deptno,job FROM emp WHERE ename='刘鹏飞')
=
SELECT ename,job,sal,deptno FROM emp WHERE ename='刘鹏飞'
--20131101
--在INSERT语句里使用子查询
insert into emp(empno,ename,deptno,job)
select e.empno,e.ename,203,e.job from emp1 e
where e.ename='小王'
--在UPDATE语句里使用子查询，通过查询EMP表里小王的所属部门，修改EMP1表里小王的所属部门
update emp1 e
set e.deptno=(select a.deptno from emp a where a.empno=7950)
where e.empno=7950;
commit; --提交
--在CREATE里使用子查询
create table emp3
as
select * from emp1
--从第3个位置取8个字符
select substr('13088888888',3,8) from dual
--将JOB字段当中的CL字符修改为AL
select replace(e.job,'CL','AL') from emp1 e
--四舍五入，将sal字段四舍五入，保留2位小数
select round(e.sal,2)
from emp1 e where e.empno=7369
--给当前日期加2个月
select ADD_MONTHS(sysdate,2) from dual
select ADD_MONTHS(sysdate,2)
from dual
--给指定的日期加2个月
select ADD_MONTHS(to_date('2013-5-25','yyyy-mm-dd'),2) from dual
--求当前月份最后一天
select last_day(sysdate) from dual;
select to_char(last_day(sysdate),'dd')from dual;
--返回特定日期之后的第一个工作日所对应的日期
select NEXT_DAY(sysdate,'星期一') from dual
--返回当前用户
select user from dual
--查询人员表中最高工资
select max(e.sal) 最高工资 from emp1 e
--非常好用，相当于IF elsif ，如果JOB字段的值为clerk则显示办事员，如果为SALESMAN则显示销售员，否则全部显示领导
select decode(e.job,'CLERK','办事员','SALESMAN','销售员','领导')
from emp1 e
--最基本的插入语句，建议大家要把列名列出来
insert into emp1(num,empno,ename,deptno,job)
values(18,8888,'小李',203,'CLERK')
--插入数据采用默认值
insert into emp1(num,empno,ename,deptno,job,hiredate)
values(19,8888,'小李9',203,default,sysdate)
select *from emp1
--查询所有人员姓名及所属部门名称，还有上级领导的姓名及所属部门名称
select e.ename,d.dname,a.ename,b.dname
from dept1 d, emp1 e ,emp1 a,dept1 b
where d.deptno=e.deptno and e.mgr=a.empno and a.deptno=b.deptno
--分解上面4个表的SQL
--1查询所有人员姓名及所属部门名称
select e.ename,d.dname
from dept1 d, emp1 e
where d.deptno=e.deptno
--2查询所有人员姓名及所属部门名称及所属领导姓名
select e.ename,d.dname,a.ename
from dept1 d, emp1 e,emp1 a
where d.deptno=e.deptno and e.mgr=a.empno
--3查询所有人员姓名及所属部门名称及所属领导姓名和部门名称
select e.ename,d.dname,a.ename,b.dname
from dept1 d, emp1 e,emp1 a,dept1 b
where d.deptno=e.deptno and e.mgr=a.empno and a.deptno=b.deptno
select * from emp1
--更新王刚1的工资为2200
update emp1 e
set e.sal = 2200
where e.ename='王刚'
update emp1 e
set e.sal=e.sal + e.sal * 0.2
where e.ename='王刚'
--多列更新
update emp1 e
set e.sal=e.sal + e.sal * 0.2,e.comm=e.comm+ e.comm * 0.2
where e.ename='王刚1'
insert into emp1(num,empno,ename,deptno)
values(22,8899,'小刘1',203)
--给小刘1的岗位设成默认值
update emp1 e
set e.job=default
where e.ename='小刘'
--重点记忆，在更新数据或删除数据时，一定要先查询数据，确认返回行是否正确。
select * from emp1 e where e.ename= '小刘1'
--通过子查询修改字段值
update emp1 e
set e.sal=(select a.sal from emp1 a where a.ename='王刚2')
where e.ename='小刘'
--删除指定的数据
delete from emp1 e
where e.ename='小刘1'
select *from emp3
create table emp3 as
select rownum as num,t.* from emp t
--删除整个表的数据
delete from emp3
--免提交删除整个表的数据
truncate table emp3
drop table emp3
--创建人员表视图
create view v_emp1
as
select e.empno,e.ename,d.dname,d.upperdeptno,e.mgr,a.ename mgrname
from emp1 e,dept1 d,emp1 a
where e.deptno=d.deptno and e.mgr=a.empno
select *from v_emp1
create view v_emp2
as
select e.empno,e.ename,d.dname,d.upperdeptno,e.mgr,a.ename mgrname
from emp1 e,dept1 d,emp1 a
where e.deptno=d.deptno and e.mgr=a.empno
select *from emp1
select *from dept1
select *from
select *from v_emp1
--视图的应用跟表的应用类似，也可以进行连接
select r.*,d.dname
from v_emp1 r ,dept1 d
where r.upperdeptno=d.deptno
--创建一个复杂视图
--注意：修改表结构会对引用它的视图、触发器有影响。
create or replace view v_emp1_sal
as
select f.*,d.dname from dept1 d,
(select e.deptno,max(e.sal) as hsal,min(e.sal) msal,avg(e.sal) asal
from emp1 e
group by e.deptno) f
where d.deptno=f.deptno
--从当前用户视图字典表查询新建的视图，视图名是大写，所以本SQL语句查询不到数据
SELECT * FROM user_views u where u.view_name like upper('%emp1%')
--将视图名称转为小写，可以查询出数据
SELECT * FROM user_views u where lower(u.view_name) like '%emp1%'
--可以显示是否允许在特定视图列上执行DML操作
SELECT *
FROM user_updatable_columns s
where s.table_name like '%EMP1%'
--20131105
--创建序列
CREATE SEQUENCE deptno_seq START WITH 1 INCREMENT BY 1 MAXVALUE 99 CACHE 10;
create sequence deptno_seq starT with 1 increment by 2 maxvalue 99 cache 10;
--查询deptno_seq的下一个值
select deptno_seq.nextval from dual;
--查询deptno_seq的当前值
select deptno_seq.currval from dual
--在插入语句中使用序列3
insert into emp1(num,empno)values(99,deptno_seq.nextval)
--创建一个同义词
create or replace synonym dlv3emp for dlv3.emp
--使用同义词
select * from dlv3emp
--游标的定义、使用、关闭
declare
cursor zb_cursor is
select ZB_INCODE from TWZ_CGMX_ZBLY
where xh=v_xh;
zb_record zb_cursor%rowtype;
begin
open zb_cursor;
fetch zb_cursor into zb_record;
while zb_cursor%found loop
v_bill_incode:=zb_record.ZB_INCODE;
p_zb_liyong_delete(v_bill_incode);
fetch zb_cursor into zb_record;
end loop;
close zb_cursor;
end;
--存储过程 函数
select to_char(to_date('2013-10-15','YYYY-MM-DD'),'DD') from dual
SELECT to_char(sysdate,'YYYY')from dual
select to_date('2013-10-15','YYYY-MM-DD') from dual
select e.ename,rownum from emp e
where
rownum<7
minus
select e.ename,rownum from emp e
where
rownum<3
select *from(select e.ename ,rownum xuhao from emp e) d where d.xuhao between 3 and 6
select *from emp
select e.ename from emp e
WHERE e.deptno=203
----pl sql 第一题
select distinct e.deptno ,F_YOU(e.deptno) from emp e
where to_char(e.hiredate,'YYYY')>'2011'
select *from emp
update EMP e SET e.SAL=e.sal*1.2
where to_char(e.hiredate,'YYYY')>2011
and e.job='CLERK';
commit;
if to_char(e.hiredate,'YYYY')>2011 then
update EMP e SET e.SAL=e.sal*1.1
elsif
to_char(e.hiredate,'YYYY')<2010 then
update EMP e SET e.SAL=e.sal*1.1
where to_char(e.hiredate,'YYYY')>2011
and e.job='CLERK';
select '''hh'''from dual
select q'('hello')' from dual
select q'{'helo'}'from dual
--创建一个函数
--函数的意义：根据输入参数的部门内码获得部门名称
create or replace function f_get_dept_name(in_deptno number) return varchar2 is
v_dname dept.dname%type;
begin
select b into v_dname from dept t where t.deptno=in_deptno;
return(v_dname);
end f_get_dept_name;
--使用自定义的函数
select e.ename,f_get_dept_name1(e.deptno)
from emp e
select e.ename,f_zyf(e.deptno)
from emp e
select * from dept
--与下面这句SQL返回值相同
select e.ename,d.dname
from emp e ,dept d where e.deptno=d.deptno
select e.*,rowid
from emp e
where e.job='MANAGER'
select nvl(e.sal,0)*12+nvl(e.comm,0)
from emp e
where nvl(e.sal,0)*12+nvl(e.comm,0) > 70000
and e.job='MANAGER';
--创建一个存储过程
--该存储过程的含义：
--将人员表中经理职位并且年薪大于7万的人员信息插入到MANAGER表中，输出参数是插入的条数
create or replace procedure p_manager_update(o_ename_num out number) is
begin
select to_number(count(1)) into o_ename_num
from emp e
where nvl(e.sal,0)*12+nvl(e.comm,0) > 70000
and e.job='MANAGER';
insert into MANAGER(INCODE,ENAME,CREATE_DATE)
select deptno_seq.nextval,e.ename,sysdate
from emp e
where nvl(e.sal,0)*12+nvl(e.comm,0) > 70000
and e.job='MANAGER';
commit;
end p_manager_update;
select *from MANAGER
delete MANAGER
select *from emp t
--在COMMONAD WINDOWS里执行
exec p_manager_update;
exec p_manager_update;
--20131106
--如何使用游标
--如果MANAGER表里已经存在员工，不插入，如果不存在，再插入
create or replace procedure p_manager_update2(o_ename_num out number) is
begin
declare
--向MANAGER插入一条记录就+1，最终值就是输出参数
v_num number(3);
v_empno varchar2(20);
v_ename varchar2(20);
v_emp_num number(3);
begin
v_num:=0;
declare
cursor zb_cursor is
select e.empno,e.ename
from emp e
where nvl(e.sal,0)*12+nvl(e.comm,0) > 70000
and e.job='MANAGER';
zb_record zb_cursor%rowtype;
begin
open zb_cursor;
fetch zb_cursor into zb_record;
while zb_cursor%found loop
--从record中取出员工编号及员工姓名
v_empno:=zb_record.empno;
v_ename:=zb_record.ename;
select count(*) into v_emp_num
from manager m where m.empno=v_empno;
if v_emp_num=0 then
insert into MANAGER(INCODE,ENAME,CREATE_DATE,empno)
values(deptno_seq.nextval,v_ename,sysdate,v_empno);
v_num := v_num+1;
end if;
fetch zb_cursor into zb_record;
end loop;
close zb_cursor;
end;
o_ename_num :=v_num;
commit;
end;
end p_manager_update2;
exec p_manager_update;
select *from MANAGER
DELETE MANAGER
--新生成一张表emp_his
CREATE TABLE emp_his AS SELECT * FROM EMP WHERE 1=2;
SELECT *FROM EMP_HIS
SELECT E.*,ROWID FROM EMP6 E
CREATE TABLE EMP6 AS SELECT * FROM EMP
--创新一个触发器，当删除EMP表前将删除的记录插入到emp_his表中
CREATE OR REPLACE TRIGGER tr_del_emp1
BEFORE DELETE --指定触发时机为删除操作前触发 before insert OR UPDATE OR DELETE
ON emp6 -- ON DEPARTMENT
FOR EACH ROW --说明创建的是行级触发器
--when ename = 'CLEAK';
BEGIN
--将修改前数据插入到日志记录表 del_emp ,以供监督使用。
INSERT INTO emp_his(deptno , empno, ename , job ,mgr , sal , comm , hiredate )
VALUES( :old.deptno, :old.empno, :old.ename , :old.job,:old.mgr, :old.sal, :old.comm, :old.hiredate );
END;
CREATE OR REPLACE TRIGGER TR_emp_sal_comm
before UPDATE OF SALARY,COMMISSIN_PCT OR DELETE
ON hr_employees
for each row
when (old.department_id=80)
begin
CASE
when updating ('salary')then;
if: new.salary<old.salary then
RAISE_APPLICATION_ERROR(-20001,'部门80员工不能降工资');
end if;
when deleting then
RAISE_APPLICATION_ERROR(-20002,'部门80员工不能删除');
end case
end
select E.*,ROWID from emp E
drop trigger TR_zyf2
CREATE OR REPLACE TRIGGER TR_zyf
before UPDATE OF SAL,COMM
OR DELETE
ON emp
for each row
WHEN (OLD.EMPNO=7369)
begin
CASE
when updating ('SAL')then
if :new.SAL<:old.SAL then
RAISE_APPLICATION_ERROR(-20001,'部门7369员工不能降工资');
end if;
when deleting then
RAISE_APPLICATION_ERROR(-20002,'部门7369员工不能删除');
end case;
end;
select E.*,ROWID from emp E
drop trigger TR_zyf2
CREATE OR REPLACE TRIGGER TR_zyf2
before UPDATE OF SAL
ON emp
for each row
begin
if :new.SAL < :old.SAL then
RAISE_APPLICATION_ERROR(-200001,'部门7369员工不能降工资');
end if;
end;
--在cmd下输入如下命令，导出全库数据库
--exp 用户名/密码@网络服务名 file=导出文件存放路径及名称 full=y (表示全库导出)
exp dlv3/dlv3@ydme file=e:\20131106full.dmp full=y
--将数据库中的表table1和table2导出
exp dlv3/dlv3@ydme file=e:\20131106dlv3table.dmp tables=(table1,table2)
--将数据库中dlv3用户与dlv2用户的所有对象导出
exp dlv3/dlv3@ydme file=e:\20131106dlv3dlv2.dmp owner=(dlv3,dlv2)
--将E:\20131106dlv3.dmp 备份文件导入，将dlv3用户下的emp1和dept1表导入到DLV2用户下
imp dlv2/ffffff@ydme fromuser=dlv3 touser=dlv2
tables=(emp1,dept1) log=e:\20131106.log file=E:\20131106dlv3.dmp
--将E:\20131106dlv3.dmp 备份文件导入，将dlv3用户下所有对象导入到DLV2用户下
imp dlv2/ffffff@ydme fromuser=dlv3 touser=dlv2 log=e:\20131106.log file=E:\20131106dlv3.dmp
select e.ename 姓名,t.dname 部门,nvl(e.sal,0) 工资,nvl(e.comm,0) 奖金,nvl(e.sal,0)*12+nvl(e.comm,0) 年薪
from dept t,emp e
where t.deptno=e.deptno
select e.empno,e.ename
from emp1 e
select*
from emp
--各部门最高工资和姓名
select * from emp
select a.empno,a.ename from emp a,
(select e.deptno,max(sal) sal from emp e group by deptno) b
where a.deptno=b.deptno
and a.sal=b.sal
--
select *from emp
select count(*)
from emp e
where e.deptno='201'
or to_char(e.hiredate,'YYYY')<2012;
select *
from emp e
where e.deptno='201'
or to_char(e.hiredate,'YYYY')<2012;
--函数
create or replace function f_get_dept_name(in_deptno number) return varchar2 is
v_dname dept.dname%type;
begin
select t.dname into v_dname from dept t where t.deptno=in_deptno;
return(v_dname);
end f_get_dept_name;
--人名，人名，人名，
create or replace function f_you(in_deptno number) return varchar2 is
Result varchar2(399);
begin
declare
cursor zb_cursor is
select e.ename from emp e WHERE e.deptno=in_deptno;
zb_record zb_cursor%rowtype;
begin
open zb_cursor;
fetch zb_cursor into zb_record;
while zb_cursor%found loop
result:=result||zb_record.ename||',';
fetch zb_cursor into zb_record;
end loop;
close zb_cursor;
end;
return(Result);
end f_you;
--存储 procedures
create or replace procedure p_manager_update2(o_ename_num out number) is
begin
declare
--向MANAGER插入一条记录就+1，最终值就是输出参数
v_num number(3);
v_empno varchar2(20);
v_ename varchar2(20);
v_emp_num number(3);
begin
v_num:=0;
declare
cursor zb_cursor is
select e.empno,e.ename
from emp e
where nvl(e.sal,0)*12+nvl(e.comm,0) > 70000
and e.job='MANAGER';
zb_record zb_cursor%rowtype;
begin
open zb_cursor;
fetch zb_cursor into zb_record;
while zb_cursor%found loop
--从record中取出员工编号及员工姓名
v_empno:=zb_record.empno;
v_ename:=zb_record.ename;
select count(*) into v_emp_num
from manager m where m.ENAME=v_ename;
if v_emp_num=0 then
insert into MANAGER(INCODE,ENAME,CREATE_DATE)
values(deptno_seq.nextval,v_ename,sysdate);
v_num := v_num+1;
end if;
fetch zb_cursor into zb_record;
end loop;
close zb_cursor;
end;
o_ename_num :=v_num;
commit;
end;
end p_manager_update2;
--
create or replace procedure p_manager_update(o_ename_num out number) is
begin
select to_number(count(1)) into o_ename_num
from emp e
where nvl(e.sal,0)*12+nvl(e.comm,0) > 70000
and e.job='MANAGER';
insert into MANAGER(INCODE,ENAME,CREATE_DATE)
select deptno_seq.nextval,e.ename,sysdate
from emp e
where nvl(e.sal,0)*12+nvl(e.comm,0) > 70000
and e.job='MANAGER';
commit;
end p_manager_update;
---
create or replace procedure p_emp(o_ename_num out number) is
begin
declare
--向MANAGER插入一条记录就+1，最终值就是输出参数
v_num number(3);
v_deptno varchar2(20);
v_hiredate date;
begin
v_num:=0;
declare
cursor zb_cursor is
select e.deptno,e.hiredate
from emp e
where e.deptno='201'
or to_char(e.hiredate,'YYYY')<2012;
-- select count(*)into o_ename_num
-- from emp e
-- where e.deptno='201'
-- or to_char(e.hiredate,'YYYY')<2012;
zb_record zb_cursor%rowtype;
begin
open zb_cursor;
fetch zb_cursor into zb_record;
while zb_cursor%found loop
--从record中取出员工编号及员工姓名
v_deptno:=zb_record.deptno;
v_hiredate:=zb_record.hiredate;
if v_deptno='201' then
update EMP e SET e.SAL=e.sal*1.2;
commit;
v_num := v_num+1;
elsif to_char( v_hiredate,'YYYY')<2010 then
update EMP e SET e.SAL=e.sal*1.2;
commit;
v_num := v_num+1;
elsif to_char( v_hiredate,'YYYY')<2012 then
update EMP e SET e.SAL=e.sal*1.1;
commit;
v_num := v_num+1;
end if;
fetch zb_cursor into zb_record;
end loop;
close zb_cursor;
end;
o_ename_num :=v_num;
commit;
end;
end p_emp;
--触发器
-创新一个触发器，当删除EMP表前将删除的记录插入到emp_his表中
CREATE OR REPLACE TRIGGER tr_del_emp1
BEFORE DELETE --指定触发时机为删除操作前触发 before insert OR UPDATE OR DELETE
ON emp6 -- ON DEPARTMENT
FOR EACH ROW --说明创建的是行级触发器
--when ename = 'CLEAK';
BEGIN
--将修改前数据插入到日志记录表 del_emp ,以供监督使用。
INSERT INTO emp_his(deptno , empno, ename , job ,mgr , sal , comm , hiredate )
VALUES( :old.deptno, :old.empno, :old.ename , :old.job,:old.mgr, :old.sal, :old.comm, :old.hiredate );
END;
CREATE OR REPLACE TRIGGER TR_emp_sal_comm
before UPDATE OF SALARY,COMMISSIN_PCT OR DELETE
ON hr_employees
for each row
when (old.department_id=80)
begin
CASE
when updating ('salary')then;
if: new.salary<old.salary then
RAISE_APPLICATION_ERROR(-20001,'部门80员工不能降工资');
end if;
when deleting then
RAISE_APPLICATION_ERROR(-20002,'部门80员工不能删除');
end case
end
set serverout on
declare
v_i number;
begin
P_EMP(o_ename_num =>v_i);
DBMS_OUTPUT.PUT_LINE(v_i);
end;
/
--命令
select e.*,rowid from emp e
--
create or replace procedure p_emp(o_ename_num out number) is
begin
declare
--向MANAGER插入一条记录就+1，最终值就是输出参数
v_num number(3);
v_deptno varchar2(20);
v_ename varchar2(20);
v_hiredate date;
begin
v_num:=0;
declare
cursor zb_cursor is
select e.deptno,e.hiredate,e.ename
from emp e
where e.deptno='201'
or to_char(e.hiredate,'YYYY')<2012;
-- select count(*)into o_ename_num
-- from emp e
-- where e.deptno='201'
-- or to_char(e.hiredate,'YYYY')<2012;
zb_record zb_cursor%rowtype;
begin
open zb_cursor;
fetch zb_cursor into zb_record;
while zb_cursor%found loop
--从record中取出员工编号及员工姓名
v_deptno:=zb_record.deptno;
v_hiredate:=zb_record.hiredate;
v_ename:=zb_record.ename;
if v_deptno='201' then
update EMP e SET e.SAL=e.sal*1.2
where e.ename=v_ename;
commit;
v_num := v_num+1;
elsif to_char( v_hiredate,'YYYY')<2010 then
update EMP e SET e.SAL=e.sal*1.2
where e.ename=v_ename;
commit;
v_num := v_num+1;
elsif to_char( v_hiredate,'YYYY')<2012 then
update EMP e SET e.SAL=e.sal*1.1
where e.ename=v_ename;
commit;
v_num := v_num+1;
end if;
fetch zb_cursor into zb_record;
end loop;
close zb_cursor;
end;
o_ename_num :=v_num;
commit;
end;
end p_emp;
select *from emp
select
select e.*,rownum from emp e order by e.deptno,e.sal
select e.* from emp e
where (select count(1) from emp e2 where e2.deptno=e.deptno and e2.sal>=e.sal)<=2
order by e.deptno,e.sal desc;
select * from
(
select e.*,rownum dd from emp e)
where dd between 3 and 6
select * from emp
select a.empno,a.ename from emp a,
(select e.deptno,max(sal) sal from emp e group by deptno) b
where a.deptno=b.deptno
and a.sal=b.sal