ORACLE

declare cursor emp_cursor is select empname,salary from employee for update;
emp_record emp_cursor%rowtype;
begin
open emp_cursor;
loop
fetch emp_cursor into emp_record;
exit when emp_cursor%notfound;
if emp_record.salary<2000 then
update employee set salary=salary*1.1 where current of emp_cursor;
end if;
end loop;
end;
declare
v_name varchar2(10);
begin
select empname into v_name from employee where id=&no;
dbms_output.put_line('雇员'||v_name);
end;
--嵌套的命名块
declare
v_no number(8);
v_name varchar2(10);
begin
begin
select id into v_no from employee where empname='&name';
end;
select deptname into v_name from dept where id=v_no;
dbms_output.put_line('部门名称'||v_name);
end;
--子程序
--（1）存储过程
create or replace procedure update_sal(v_name varchar2,v_no number) is
begin
update employee set salary=v_no where empname=v_name;
end;
exec update_sal('张三',2000);
--(2)建立函数
create function get_grade(no number)
return number is gra number(8);
begin
select grade into gra from student_course where id=no;
return gra;
end;
get_grade(6);
drop function get_grade;
select get_grade(6) from dual;
--包
create package emp_pkg is
begin
procedure update_sal(v_name varchar2,v_vo number);
function get_grade(no number)return number;
end;
--触发器
create trigger emp_tri
after update of id on dept for each row
begin
update employee set deptno=:new.id
where deptno=:old.id;
end;
drop trigger emp_tri;
update dept set id=2 where id=10;
--oracle中的标量变量
--使用标量变量
declare
v_name varchar2(10);
v_sal number(8);
begin
select empname,salary into v_name,v_sal from employee where id=&no;
dbms_output.put_line('员工姓名：'||v_name);
dbms_output.put_line('员工工资：'||v_sal);
end;
--使用%type属性
declare
v_name employee.empname%type;
v_sal employee.salary%type;
begin
select empname,salary into v_name,v_sal from employee where id=&no;
dbms_output.put_line('员工姓名：'||v_name);
dbms_output.put_line('员工工资：'||v_sal);
end;
--记录类型
declare type emp_record_type is record(
name employee.empname%type,
salary employee.salary%type,
id employee.id%type);
emp_record emp_record_type;
begin
select empname,salary,id into emp_record from employee where id=&no;
dbms_output.put_line('员工姓名：'||emp_record.name);
dbms_output.put_line('员工工资：'||emp_record.salary);
end;
--表类型
declare type ename_table_type is table of employee.empname%type
index by binary_integer;
ename_table ename_table_type;
begin
select empname into ename_table(-1) from employee where id=2;
dbms_output.put_line('员工姓名：'||ename_table(-1));
end;
--参照变量，




--select简单查询语句(scott用户)
--（1） 确定表结构
desc dept
--（2）检索所有列
select * from dept;
-- (3)检索特定列
select ename,sal,job from emp;
-- (4)检索日期列，使用TO_CHAR()函数转换日期格式。
select ename,TO_CHAR(hiredate,'YYYY-MM-DD') from emp;
--(5)取消重复行
select distinct deptno,job from emp;
--(6)使用表达式
select ename,sal*12 from emp;
--(7)使用列别名
SELECT ename AS "姓名",sal AS "工资" FROM emp;
--(8)处理null
SELECT ename,sal,comm,sal+comm FROM emp;
SELECT ename,sal,comm,sal+nvl(comm,0) FROM emp;
SELECT ename,sal,comm,nvl2(comm,sal+comm,sal) FROM emp;
--(9)连接字符串
SELECT (ename||' is a '||job) "组合" FROM emp;
-- where子句的使用
--(1)注意字符串的大小写
SELECT ename,sal FROM emp WHERE lower(ename)=lower('scott');
--(2)注意子句中日期值的转换(使用TO_DATE()函数)
SELECT ename,sal,hiredate FROM emp WHERE hiredate>TO_DATE('1982-01-01','YYYY-MM-DD');
--(3)使用between and
SELECT ename,sal,hiredate FROM emp WHERE sal BETWEEN 1000 AND 2000;
--(4)使用like查询（用通配符_,%,以及escape转译）
SELECT ename,sal,hiredate FROM EMP WHERE lower(ename) like lower('s')||'%';
SELECT ename,sal,hiredate FROM emp WHERE ename like '_A%';
SELECT ename,sal,hiredate FROM emp WHERE ename like '%a_%' escape 'a';--转译的是a之后的'_'
--(5)使用in操作符
SELECT ename,sal,hiredate FROM emp WHERE sal in(800,1250);
--(6)WHERE条件中使用is null(oracle中的null 用<>,=判断，总是返回false)
SELECT ename,sal,hiredate FROM emp WHERE mgr is null;
select deptno,job from emp;
--(7)使用逻辑操作符(not,and,or按照优先级排)
--(8)使用order by 排序
SELECT ename,sal,hiredate,comm FROM emp order by comm;--升序排列中，如果排序字段是null,会被排在最后
SELECT ename,sal,hiredate,comm FROM emp order by sal asc,comm desc;
SELECT ename FROM emp ORDER BY sal;
SELECT ename,sal*12 as "年薪" FROM emp ORDER BY "年薪";
SELECT ename,sal*23 as "年薪" FROM emp ORDER BY 2 DESC;--使用列编号进行排序
--DML语句，数据操纵语句
--(1)全列插入和指定列插入。
--(2)使用default默认值插入，(如果设置了，插入默认值，否则插入null)
--(3)使用子查询插入数据
INSERT INTO emp(empno,ename,sal,deptno) select id,empname,salary,deptno from HHH.employee;
INSERT /*+APPEND*/ INTO emp(empno,ename,sal,deptno) select id,empname,salary,deptno from HHH.employee; --直接装载(数据量大时效率高于上面)
--(4)使用All操作符执行多表插入(在每个条件子句上都会执行into后的子查询)
INSERT ALL
WHEN deptno=10 THEN INTO dept10
WHEN deptno=20 THEN INTO dept20
WHEN deptno=30 THEN INTO dept30
WHEN deptno=40 THEN INTO dept40
WHEN job='clerk' THEN INTO clerk
ELSE INTO OTHER
SELECT * FROM emp;
--(5)使用first操作符执行多表插入(如果该条语句符合先觉条件，完成插入语句，在后面的条件语句中将不会被重复插入)
INSERT FIRST
WHEN deptno=10 THEN INTO dept10
WHEN deptno=20 THEN INTO dept20
WHEN deptno=30 THEN INTO dept30
WHEN deptno=40 THEN INTO dept40
WHEN job='clerk' THEN INTO clerk
ELSE INTO OTHER
SELECT * FROM emp;
--update，delete语句
--(1)使用默认值修改数据
update emp set job=DEFAULT where ename='scott';
--(2)使用子查询修改字段
UPDATE emp SET (job,sal,comm)=(SELECT job,sal,comm FROM emp WHERE ename='SMITH') where ename='SCOTT';
--(3)复制表数据，删除表数据
--(4)使用truncate截断表
--(5)删除主表数据要注意外键约束
--事务控制语句
--(1)设置只读事务，就像oracle中的时间点查询一样，但是不能执行dml语句。
set transaction read only;
--(2)设置顺序事务
set transaction isolation level serializable;
--数据分组。
--首先介绍函数
min,max,sum,avg,count,variance(方差),stddev(标准偏差)--只有count函数不会忽略null值
--(1)函数的使用
SELECT COUNT(empno) from emp;
--(2)去除重复
SELECT COUNT(DISTINCT empno) from emp;
--(3)group by 和having
SELECT deptno,job,avg(sal),empno FROM emp group by deptno,job,empno order by avg(sal),empno;
SELECT deptno,job,AVG(sal),MAX(sal) FROM emp GROUP BY deptno,job HAVING AVG(sal)>2000;
select count(empno),deptno,empno from emp group by deptno,empno;
--(4)使用rollup 和cube
SELECT deptno,job,AVG(sal) FROM emp WHERE job is not null GROUP BY ROLLUP (deptno,job);
SELECT deptno,job,SUM(sal) FROM emp WHERE job is not null GROUP BY CUBE (deptno,job);
select grouping(deptno),deptno,sum(sal) from emp group by rollup(deptno);
--(5)使用grouping函数
select grouping(deptno),grouping(job),deptno,job,sum(sal) from emp group by rollup(deptno,job);
select decode(grouping(deptno),'all deptno',deptno) as div,deptno,sum(sal) from emp group by rollup(deptno);
--(6)使用grouping sets
select deptno,job,sum(sal) from emp group by grouping sets(deptno,job);


--连接查询语句
--(1)笛卡尔积问题(查询多表指定了无效的连接条件)
SELECT * FROM emp,dept;
SELECT dept.deptno,dname,ename FROM emp,dept where dname='SALES';--等于在笛卡尔积后面添加了条件。
--(2)使用表别名，主从表的=连接查询，使用and指定其他条件
SELECT ename,sal,dname from emp,dept where emp.deptno=dept.deptno and dept.deptno=10;
--(3)使用不等，进行查询
SELECT a.ename,a.sal,b.losal,b.hisal,b.grade FROM emp a,salgrade b WHERE a.sal BETWEEN b.losal AND b.hisal;
--(4)使用自连接(使用别名,注意主外键的问题)
SELECT manager.ename FROM emp manager,emp worker where worker.mgr=manager.empno and worker.ename='BLAKE';
--(5)使用内连接，左外连接，右连接，全连接 (+)完成左外连接
SELECT a.ename,b.dname FROM emp a,dept b where a.deptno=b.deptno and b.deptno=10;--属于内连接
SELECT a.ename,b.dname FROM emp a inner join dept b on a.deptno=b.deptno and b.deptno=10;
SELECT ename,dname FROM emp a natural join dept;--自动执行的内连接
SELECT dname,ename,dept.deptno FROM dept left join emp on dept.deptno=emp.deptno and dept.deptno=10;--左外连接
SELECT dname,ename,dept.deptno FROM dept right join emp on dept.deptno=emp.deptno and dept.deptno=10;--右外连接
SELECT dname,ename,dept.deptno FROM dept full join emp on dept.deptno=emp.deptno and dept.deptno=10;--完全外连接
SELECT dname,ename,dept.deptno FROM dept,emp WHERE dept.deptno(+)=emp.deptno and dept.deptno(+)=10;--右外连接
SELECT dept.dname,emp.ename FROM dept,emp WHERE dept.deptno=emp.deptno(+) and emp.deptno(+)=10;--左外连接
--子查询
--(1)单行子查询(=,>,<,>=,<=,<>)
SELECT ename,sal,deptno FROM emp WHERE deptno=(SELECT deptno FROM emp WHERE ename='SCOTT');
--(2)多行子查询(in,all,any)
SELECT ename,sal,job,deptno FROM emp where job in(SELECT job FROM emp WHERE deptno=10);
SELECT ename,sal,job,deptno FROM emp WHERE sal>all (SELECT sal FROM emp WHERE deptno=20);
SELECT ename,sal,job,deptno FROM emp WHERE sal>any (SELECT sal FROM emp WHERE deptno=30);
--(3)多列子查询
SELECT ename,sal,job,deptno FROM emp WHERE (deptno,job)=(SELECT deptno,job FROM emp WHERE ename='SMITH');--单行多列
SELECT ename,sal,comm,job,deptno FROM emp WHERE (sal,nvl(comm,0)) in (SELECT sal,nvl(comm,0) FROM emp WHERE deptno=30);--成对比较
SELECT ename,sal,comm,job,deptno FROM emp WHERE sal in(select sal from emp where deptno=30) and nvl(comm,0) in (select nvl(comm,0) from emp where deptno=30);--非成对比较
--其他子查询
--(1)相关子查询使用exists谓词来实现
SELECT ename,job,sal,deptno FROM emp where EXISTS(SELECT 1 FROM dept WHERE dept.deptno=emp.deptno AND dept.loc='NEW YORK');
--(2)from子句中子查询
select a.empno,a.ename,a.job,a.sal,a.deptno from emp a,(select deptno,avg(sal) ping from emp group by deptno) b where a.deptno=b.deptno and a.sal>b.ping;
select a.empno,a.ename,a.job,a.sal,a.deptno from emp a where a.sal>(select avg(b.sal) from emp b where a.deptno=b.deptno);
--(3)dml中使用子查询
--(4)ddl中使用子查询
CREATE TABLE EMPLOYEE(EMPNO,ENAME,SAL,JOB,DEPTNO) AS SELECT EMPNO,ENAME,SAL,JOB,DEPTNO FROM emp;
--(5)创建视图是使用子查询
CREATE OR REPLACE VIEW dept_10 AS SELECT empno,ename,sal,job,deptno FROM emp;
--(6)创建实体化视图

--合并查询(union,union all,intersect,minus)
--(1)使用union合并查询
SELECT ename,sal,job FROM emp where sal>2500 UNION SELECT ename,sal,job FROM emp WHERE job='MANAGER';
--(2)使用union all
SELECT ename,sal,job FROM emp where sal>2500 UNION ALL SELECT ename,sal,job FROM emp WHERE job='MANAGER';
--(3)使用intersect获取交集
SELECT empno,ename,sal,job FROM emp WHERE sal>2500 INTERSECT SELECT empno,ename,sal,job FROM emp WHERE job='MANAGER';
--(4)使用minus获取差集
SELECT empno,ename,sal,job FROM emp WHERE sal>2500 MINUS SELECT empno,ename,sal,job FROM emp WHERE job='MANAGER';
--其他复杂查询
--(1)层次查询
SELECT a.ename,a.sal,a.job,a.mgr FROM emp a,emp b WHERE a.job<>'CLERK' and a.mgr=b.empno and b.job<>'CLERK';

--(2)使用CASE WHEN THEN
SELECT ename,sal,job,CASE WHEN sal>=3000 THEN 1 WHEN sal>2000 THEN 2 ELSE 3 END grade FROM emp WHERE deptno=20;
--(3)倒叙查询(注意：数据库必须使用undo管理方式，undo_retention限制了undo数据的保留时间)
SELECT ename,sal,job from emp where ename='SMITH';--查看先前记录，之后修改数据
SELECT ename,sal,job FROM emp as of timestamp to_timestamp('2010-08-01 10:00:00','YYYY-MM-DD HH24:MI:SS') WHERE ename='SMITH';--查看记录，五分钟之前修改的，否则不能看到。
--(4)使用with子句重用子查询
SELECT a.dname,sum(b.sal) FROM dept a,emp b where a.deptno=b.deptno group by dname having sum(b.sal)>(
select sum(a.sal)/3 from emp a,dept b where a.deptno=b.deptno
);--两次使用相同子查询
with summary as (select dname,sum(sal) dept_total from emp,dept where emp.deptno=dept.deptno group by dname)
select dname,dept_total from summary where dept_total>(select sum(dept_total)*1/3 from summary);
--习题
--1
--(1)
SELECT dname FROM dept;
--(2)
SELECT ename,(sal+nvl(comm,0))*12 as "年收入" FROM emp;
--(3)
SELECT deptno FROM dept WHERE EXISTS (SELECT 1 from emp where dept.deptno=emp.deptno);
SELECT deptno FROM dept WHERE deptno in(select deptno from emp);
--2
--(1)
SELECT ename,sal FROM emp WHERE sal>2850;
--(2)
SELECT ename,sal FROM emp WHERE sal<1500 or sal>2850;
--(3)
SELECT emp.ename,emp.deptno,dept.dname FROM emp,dept WHERE emp.deptno=dept.deptno and empno=7566;
--(4)
SELECT emp.ename,emp.sal,emp.deptno FROM emp,dept where emp.deptno=dept.deptno and emp.deptno in(10,30) and emp.sal>1500;
--(5)
SELECT ename,job FROM emp WHERE mgr is null;
--(3)
--(1)
SELECT ename,job,hiredate FROM emp WHERE hiredate>to_date('1981-02-01','YYYY-MM-DD') and hiredate<to_date('1981-05-01','YYYY-MM-DD') order by hiredate ;
--(2)
SELECT ename,sal,comm FROM emp WHERE comm is not null order by sal desc,comm desc;
--(4)
INSERT INTO dept VALUES(50,'ADMINISTATOR','BOSTON');
--(5)
INSERT INTO emp(empno,ename,sal,hiredate,deptno) VALUES(1587,'JOHN',1000,to_date('1987-03-05','YYYY-MM-DD'),30);
--(6)
UPDATE emp SET sal=sal*1.1 WHERE deptno=10;
--(7)
DELETE FROM dept WHERE deptno=50;
--(8)
commit;
--(9)
--(1)
SELECT avg(sal),sum(sal),max(sal),min(sal) FROM emp;
--(2)
SELECT COUNT(empno),AVG(sal),job FROM emp group by job;
--(3)
SELECT COUNT(empno),COUNT(comm) FROM emp;
--(4)
select count(b.empno) from (select empno from emp a where (select count(b.empno) from emp b where b.mgr=a.empno)>0) b;
--(5)
SELECT (MAX(sal)-MIN(sal)) as "最大差额" FROM emp;
--(6)
SELECT GROUPING(deptno),GROUPING(JOB),deptno,job,avg(sal),case when GROUPING(deptno)=0 and GROUPING(JOB)=0 then '部门职位平均' when GROUPING(deptno)=0 and GROUPING(JOB)=1 then '部门平均' when GROUPING(deptno)=1 and GROUPING(JOB)=0 then '职位平均' else '平均' end grade FROM emp group by cube(deptno,job);
--(10)
--(1)
SELECT a.ename,a.sal,a.job,b.dname FROM emp a,dept b where a.deptno=b.deptno and a.deptno=20;
--(2)
SELECT a.ename,b.dname,a.comm FROM emp a,dept b where a.deptno=b.deptno and a.comm is not null;
--(3)
SELECT a.ename,a.sal,b.dname FROM emp a,dept b where a.deptno=b.deptno and b.loc='DALLAS';
--(4)
SELECT ename FROM emp where empno=(select mgr from emp where ename='SCOTT');
--(5)
SELECT a.ename,a.sal,b.grade FROM emp a,salgrade b where a.sal>b.losal and a.sal<b.hisal and a.deptno=20;
--(6)
SELECT dname,ename FROM dept left join emp on dept.deptno=emp.deptno and dept.deptno=10;
--(7)
SELECT dname,ename,emp.deptno FROM dept full join emp on dept.deptno=emp.deptno and dept.deptno=10;
--(11)
--(1)
SELECT ename,sal,deptno FROM emp where deptno=(select deptno from emp where ename='BLAKE') AND ename<>'BLAKE';
--(2)
SELECT ename,sal,deptno FROM emp WHERE sal>(select avg(sal) from emp);
--(3)
SELECT a.ename,a.sal,a.deptno FROM emp a,(SELECT deptno,avg(sal) ping FROM emp GROUP BY deptno) b where a.sal>b.ping and a.deptno=b.deptno;
--(4)
SELECT ename,sal,deptno FROM emp WHERE sal>(SELECT max(sal) from emp where job='CLERK');
SELECT ename,sal,deptno FROM emp WHERE sal>ALL(SELECT sal from emp where job='CLERK');
--(5)
SELECT ename,sal,nvl(comm,0) FROM emp WHERE (sal,nvl(comm,0))=(select sal,nvl(comm,0) from emp where ename='SCOTT');
--(12)
--(1)
create view dept_20 as select * from emp where deptno=20;
create view job_clerk as select * from emp where job='CLERK';
--(2)
SELECT ename,sal FROM dept_20 union SELECT ename,sal FROM job_clerk;
--(3)
SELECT ename,sal FROM dept_20 UNION ALL SELECT ename,sal FROM job_clerk;
--(4)
SELECT ename,sal FROM dept_20 INTERSECT SELECT ename,sal FROM job_clerk;
--(5)
SELECT ename,sal,job FROM dept_20 minus SELECT ename,sal,job FROM job_clerk;
--(13)
SELECT level,empno,ename FROM emp start with mgr is null connect by prior emp.empno=emp.mgr;
select level ,empno from emp t where level<=2 start with t.mgr is null connect by prior t.empno=t.mgr ;
--5 Sql函数
--数字函数(abs,ceil,cos,floor,mode(num1,num2),round(n,[m]),sign(n),trunc(n,[m]))
--abs
declare
v_abs number(6,2);
begin
v_abs:=abs(&no);
dbms_output.put_line('绝对值'||v_abs);
end;
--ceil
declare
v_ceil number(6,2);
begin
v_ceil:=ceil(&no);
dbms_output.put_line('绝对值'||v_ceil);
end;
--round执行四舍五入预算
--sign检测数字的正负，大于0返回1，小于零返回-1，等于零返回0
select sign(-2) from dual;
--trunc截取，不执行四舍五入
select trunc(45.926),trunc(45.926,1),trunc(45.926,-2) from dual;
--字符函数
--chr(n)将ascci码转变成字符。
select chr(56) from dual;
--concat与||用法相同，
select concat('hello','world') from dual;
--initcap(char)将每个单词的首字母大写
select initcap('my word') from dual;
--instr(char1,char2,[n,[m]])取得字串在字符串中的位置
select instr('morning','n') from dual;
select instr('morningn','n',1) from dual;
select instr('morningn','n',-1,2) from dual;
--length(char)用于返回字符串的长度
--lower(char)将字符串转换成小写格式，
--upper(char)将字符串转换成大写
--ltrim(char1,[set]),(rtrim,trim)去掉字符串char1左端包含的set中的任何字符
select ltrim('morning','m'),ltrim('morning','or') from dual;
--replace
select replace('adfsfas','a','Q') from dual;
--substr(char,m,n)从第m个，截取n个字符。
select substr('morning',1,3) from dual;

--日期函数
--extract从日期中取得所需的特定时间
select extract(month from sysdate) from dual;
--转换函数
--to_char(),to_date(),to_number()
--集合函数
--其他单行函数
--decode
SELECT deptno,ename,sal,decode(deptno,10,sal*1.2,20,sal*1.1,sal) "new salary" FROM emp ORDER BY deptno;
--nvl,nvl2
SELECT ename,sal,comm,nvl2(comm,sal+comm,sal) salary FROM emp WHERE deptno=30;
--first
SELECT MIN(sal) KEEP (DENSE_RANK FIRST ORDER BY comm DESC) "补助最高级别雇员的最低工资",MAX(sal) KEEP
(DENSE_RANK FIRST ORDER BY comm DESC) "补助最高级别雇员的最高工资" FROM emp;
--GROUP_ID()用于区分分组中的重复行
SELECT deptno,job,avg(sal),group_id() FROM emp GROUP BY deptno,ROLLUP(deptno,job);
--grouping

--分析函数
select deptno,sum(sal) from emp group by deptno union all select 1 deptno,sum(sal) from emp;
--使用rollup进行单个分组的统计，统计各个部门的工资总和以及所有员工的工资总和。
select nvl(deptno,0),sum(sal) from emp group by rollup(nvl(deptno,0));
select deptno,job,sum(sal) from emp group by rollup(deptno,job);--是不会按照job进行统计的。
--按照多个分组进行统计，会统计出各部门公司总和，各职位工资总和以及所有员工工资总和。
select deptno,job,mgr,sum(sal) from emp group by cube(deptno,job,mgr) order by deptno,job,mgr nulls last;
--从上面的结果中我们很容易发现，每个统计数据所对应的行都会出现null，我们如何来区分到底是根据那个字段做的汇总呢，
--这时候，我们的grouping函数就粉墨登场了
select decode(grouping(deptno),1,'deptno',deptno) deptno,decode(grouping(job),1,'job',job) job,sum(sal) salary from emp
group by cube(deptno,job) order by deptno,job nulls last;
--使用over进行连续累加
--查询每个员工工资占总工资数的百分比(按照每个员工工资连续累加)
select deptno,ename,sal,sum(sal) over (order by ename) 连续的和,sum(sal) over () 总和,100*round(sal/sum(sal) over (),4) "份额(%)" from emp;
--查询每个部门的工资，以及所有工资(实现部门工资的累加)
select deptno,ename,sal,sum(sal) over (order by deptno) 部门工资和,sum(sal) over() from emp;
--使用分区函数
select deptno,ename,sal,sum(sal) over (partition by deptno order by deptno,sal) 部门分区并累加,--按照部门分区并且累加
sum(sal) over (partition by deptno) 部门之和,sum(sal) over (order by ename) 员工工资累加,
sum(sal) over () 总和,100*round(sal/sum(sal) over (),4) "员工工资份额(%)",
100*round(sal/sum(sal) over (partition by deptno),4) "部门份额(%)"
from emp;
--综合的，既有分区的，又有不分区的
select deptno,ename,sal,sum(sal) over (partition by deptno order by sal) dept_sum,sum(sal) over (order by deptno,sal) sum from emp;
select deptno,ename,sal,sum(sal) over (partition by deptno order by deptno,sal) dept_sum,sum(sal) over (order by deptno,sal) sum from emp;
--使用rank(),dense_rank()进行查询
select deptno,sum(sal),rank() over (order by sum(sal)) fare_rank from emp group by deptno;

--习题
select ceil(45.3) from dual;
select round(45.92)+trunc(45.92) from dual;
select initcap('good morning') from dual;
select sysdate-to_date('2010-06-12','YYYY-MM-DD') from dual;
--6
declare
v_round number(6);
v_trunc number(6);
begin
v_round:=round(&no);
v_trunc:=trunc(&no);
dbms_output.put_line('四舍五入结果'||v_round);
dbms_output.put_line('整数结果'||v_trunc);
end;
--7
declare
v_lower varchar2(10);
v_upper varchar2(10);
v_initcap varchar2(10);
begin
v_lower:=lower(&aa);
v_upper:=upper(&aa);
v_initcap:=initcap(&aa);
dbms_output.put_line('小写格式'||v_lower);
dbms_output.put_line('大写格式'||v_upper);
dbms_output.put_line('首字母大写'||v_initcap);
end;
--8
declare
v_date date;
begin
v_date:=to_date(&no,'YYYY-MM-DD');
dbms_output.put_line(v_date);
end;
--9
--操纵数据
--Sql游标(显式游标)
--属性(sql%found,sql%notfound,sql%isopen,sql%rowcount)
declare
v_deptno emp.deptno%type:=&no;
begin
update emp set sal=sal*1.1 where deptno=v_deptno;
if sql%found then
dbms_output.put_line('语句执行成功');
else
dbms_output.put_line('失败');
end if;
end;
--sql%rowcount
declare
v_deptno emp.deptno%type:=&no;
begin
update emp set sal=sal*1.1 where deptno=v_deptno;
dbms_output.put_line('修改了'||sql%rowcount||'行');
end;
--事务控制语句
--rollback,savepoint,commit
declare
begin
insert into temp values(1);
savepoint a1;
insert into temp values(2);
savepoint a2;
insert into temp values(3);
savepoint a3;
rollback to a2;
commit;
end;
--习题
--4
declare
v_deptno dept.deptno%type:=&no;
v_deptdname dept.dname%type:=&name;
begin
insert into dept(deptno,dname) values(v_deptno,v_deptdname);
commit;
end;
--5
declare
begin
update dept set loc='NEW YORK' where lower(dname)=lower(&no);
if sql%found then
dbms_output.put_line('更新了'||sql%rowcount||'条数据');
commit;
else
dbms_output.put_line('u');
end if;
end;
--编写控制结构
--条件分支语句
declare
v_job varchar2(10);
v_sal number(6,2);
begin
select job,sal into v_job,v_sal from emp where empno=&no;
if v_job='PRESIDENT' then
update emp set sal=v_sal+1000 where empno=&no;
elsif v_job='MANAGER' then
update emp set sal=v_sal+500 where empno=&no;
else
update emp set sal=v_sal+200 where empno=&no;
end if;
end;

declare
v_job varchar2(10);
v_sal number(6,2);
begin
select job,sal into v_job,v_sal from emp where empno=&no;
if v_job='PRESIDENT' then
update emp set sal=v_sal+1000 where empno=&no;
else if v_job='MANAGER' then
update emp set sal=v_sal+500 where empno=&no;
else
update emp set sal=v_sal+200 where empno=&no;
end if;
end if;
end;
--case 语句
declare
v_sal emp.sal%type;
v_ename emp.ename%type;
begin
select ename,sal into v_ename,v_sal from emp where empno=&no;
case when v_sal<1000 then
update emp set comm=100 where ename=v_ename;
when v_sal<2000 then
update emp set comm=80 where ename=v_ename;
when v_sal<6000 then
update emp set comm=50 where ename=v_ename;
end case;
commit;
end;
--循环语句
--loop
create table temp(cola int);
declare
i int:=1;
begin
loop
insert into temp values(i);
exit when i=10;
i:=i+1;
end loop;
end;
--while
declare
i int:=1;
begin
while i<=10 loop
insert into temp values(i);
i:=i+1;
end loop;
commit;
end;
--for
declare
i int:=1;
begin
for i in reverse 1..10 loop
insert into temp values(i);
end loop;
commit;
end;
--嵌套循环和标号
declare
result int;
begin
<<outer>>--这里是标号
for i in 1..100 loop
<<inner>>--这里是标号
for j in 1..100 loop
result:=i*j;
exit outer when result=1000;
exit when result=500;
end loop inner;
dbms_output.put_line(result);
end loop outer;
dbms_output.put_line(result);
end;
--顺序控制语句
--go to,null
declare
v_sal emp.sal%type;
v_ename emp.ename%type;
begin
select ename,sal into v_ename,v_sal from emp where empno=&no;
if v_sal<3000 then
update emp set comm=sal*0.1 where ename=v_ename;
else null;
end if;
end;
--7.5习题
--1
declare
Sal number:=500;
Comm number;
begin
if Sal<100 then Comm:=0;
elsif Sal<600 then Comm:=Sal*0.1;
elsif sal<1000 then Comm:=Sal*0.15;
else Comm:=Sal*0.2;
end if;
dbms_output.put_line(Comm);
end;
--2
declare
v_sal number:=1000;
v_tax number;
begin
case when v_sal<1500 then v_tax:=v_sal*0.03;
when v_sal<2500 then v_tax:=v_sal*0.04;
when v_sal<3500 then v_tax:=v_sal*0.05;
end case;
dbms_output.put_line(v_tax);
end;
--3
declare
i int:=1;
begin
for i in 1..10 loop
if i=5 then
null;
elsif i=7 then
null;
else
insert into temp values(i);
end if;
end loop;
commit;
end;
--4
begin
update dept set loc=&no where lower(dname)=lower(&name);
if sql%notfound then
dbms_output.put_line('该客户不存在');
rollback;
else
commit;
end if;
end;
--5
declare
v_deptno emp.deptno%type;
begin
v_deptno:=&no;
case v_deptno when 10 then update emp set sal=sal*1.1 where empno=v_deptno;
when 20 then update emp set sal=sal*1.08 where empno=v_deptno;
when 30 then update emp set sal=sal*1.05 where empno=v_deptno;
else
dbms_output.put_line('该部门不存在');
end case;
commit;
end；
--plsql集合
create table demo(
id number(6) primary key,name varchar2(10)
);
--索引表中使用binary_integer,pls_integer
declare
type ename_table_type is table of emp.ename%type
index by binary_integer;
ename_table ename_table_type;
begin
select ename into ename_table(-1) from emp where empno=&no;
dbms_output.put_line(ename_table(-1));
end;
--8.3批量绑定
--forall语句
declare type id_table_type is table of number(6) index by binary_integer;
type name_table_type is table of varchar2(10) index by binary_integer;
id_table id_table_type;
name_table name_table_type;
begin
for i in 1..10 loop
id_table(i):=i;
name_table(i):='name'||to_char(i);
end loop;
forall i in 1..id_table.count
insert into demo values(id_table(i),name_table(i));
end;
--forall中使用部分集合语句
declare
type id_table_type is table of number(6) index by binary_integer;
id_table id_table_type;
begin
for i in 1..10 loop
id_table(i):=i;
end loop;
forall i in 6..id_table.count
delete from demo where id=id_table(i);
end;
--forall中使用indices of子句(用于跳过null集合元素)
declare type id_table_type is table of number(6);
id_table id_table_type;
begin
id_table:=id_table_type(1,null,2,null,3);
forall i in indices of id_table
delete from demo where id=id_table(i);
end;
--forall中使用values of子句(用于从其他集合变量中取得集合下标的值)
create table new_demo as select * from demo where 1=0;
declare type id_table_type is table of demo.id%type;
type name_table_type is table of demo.name%type;
id_table id_table_type;
name_table name_table_type;
type index_pointer_type is table of pls_integer;
index_pointer index_pointer_type;
begin
select * bulk collect into id_table,name_table from demo;
index_pointer:=index_pointer_type(6,8,10);
forall i in values of index_pointer
insert into new_demo values(id_table(i),name_table(i));
end;
--使用sql%bulk_rowcount属性(记录命令更新的行数)
declare type dno_table_type is table of number(3);
dno_table dno_table_type:=dno_table_type(10,20);
begin
forall i in 1..dno_table.count
update emp set sal=sal*1.1 where deptno=dno_table(i);
dbms_output.put_line('第二个元素更新的行数'||sql%bulk_rowcount(2));
end;
--使用bulk collect子句(用于取得批量数据，适用于select into,fetch ino和dml返回子句)
--(1)在select into语句中使用bulk collect子句
declare type emp_table_type is table of emp%rowtype index by binary_integer;
emp_table emp_table_type;
begin
select * bulk collect into emp_table from emp where deptno=&no;
for i in 1..emp_table.count loop
dbms_output.put_line('雇员名：'||emp_table(i).ename);
end loop;
end;
--(2)在DML的返回子句中使用bulk collect子句
declare type ename_table_type is table of emp.ename%type index by binary_integer;
ename_table ename_table_type;
begin
delete from emp where deptno=&no returning ename bulk collect into ename_table;
dbms_output.put_line('雇员名：');
for i in 1..ename_table.count loop
dbms_output.put(ename_table(i)||' ');
end loop;
end;
--游标的使用
--显式游标(属性isopen,found,notfound,rowcount)用于处理select语句返回的多行数据。
declare
cursor emp_cursor is select ename,sal from emp where deptno=10;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
if not emp_cursor%isopen then
open emp_cursor;
end if;
loop
fetch emp_cursor into v_ename,v_sal;
exit when emp_cursor%notfound;
dbms_output.put_line(v_ename||': '||v_sal);
end loop;
dbms_output.put_line('提取的总记录条数：'||emp_cursor%rowcount);
close emp_cursor;
end;
--基于游标定义记录变量
declare
cursor emp_cursor is select ename,sal from emp where deptno=10;
emp_record emp_cursor%rowtype;
begin
if not emp_cursor%isopen then
open emp_cursor;
end if;
loop
fetch emp_cursor into emp_record;
exit when emp_cursor%notfound;
dbms_output.put_line('雇员名：'||emp_record.ename||' 工资：'||emp_record.sal);
end loop;
close emp_cursor;
end;
--在显式游标中使用fetch...bulk collect into提取所有的数据
declare cursor emp_cursor is select ename from emp where deptno=10;
type emp_table_type is table of emp.ename%type;
emp_table emp_table_type;
begin
if not emp_cursor%isopen then
open emp_cursor;
end if;
fetch emp_cursor bulk collect into emp_table;
for i in 1..emp_table.count loop
dbms_output.put_line(emp_table(i));
end loop;
end;
--使用fetch... bulk collect into limit提取多条数据
declare
cursor emp_cursor is select ename from emp;
type emp_table_type is varray(5) of varchar2(10);
emp_table emp_table_type;
v_count int:=0;
rows int:=5;
begin
if not emp_cursor%isopen then
open emp_cursor;
end if;
loop
fetch emp_cursor bulk collect into emp_table limit rows;
for i in 1..(emp_cursor%rowcount-v_count) loop
dbms_output.put(emp_table(i)||' ');
end loop;
dbms_output.new_line;
v_count:=emp_cursor%rowcount;
exit when emp_cursor%notfound;
end loop;
close emp_cursor;
end;
--参数游标
declare
cursor emp_cursor(na number) is select ename from emp where deptno=na;
v_ename emp.ename%type;
begin
if not emp_cursor%isopen then
open emp_cursor(10);
end if;
loop
fetch emp_cursor into v_ename;
exit when emp_cursor%notfound;
dbms_output.put_line(v_ename);
end loop;
close emp_cursor;
COMMIT;
end;
--使用游标更新或删除数据(需要加上for update行级锁，还有where current of name_cursor)
declare
cursor emp_cursor is select ename,sal from emp for update nowait;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
if not emp_cursor%isopen then
open emp_cursor;
end if;
loop
fetch emp_cursor into v_ename,v_sal;
exit when emp_cursor%notfound;
if v_sal<2000 then
update emp set sal=sal+100 where current of emp_cursor;
end if;
end loop;
close emp_cursor;
COMMIT;
end;
--使用of子句在特定表上加行共享锁(此时emp,dept两表的deptno字段都被锁住，其他字段可以修改)
declare
cursor emp_cursor is select ename,sal,dname,emp.deptno from emp,dept where emp.deptno=dept.deptno
for update of emp.deptno;
emp_record emp_cursor%rowtype;
begin
open emp_cursor;
loop
fetch emp_cursor into emp_record;
exit when emp_cursor%notfound;
if emp_record.deptno=30 then
update emp set sal=sal-100 where current of emp_cursor;
update emp set ename='john' where ename='JOHN';
update dept set deptno=45 where deptno=emp_record.deptno;
update emp set deptno=10 where deptno=emp_record.deptno;
end if;
dbms_output.put_line('雇员名:'||emp_record.ename||',工资：'||emp_record.sal||',部门名：'||emp_record.dname);
end loop;
close emp_cursor;
end;
--游标for循环(简化程序代码)
declare
cursor emp_cursor is select ename,sal from emp;
begin
for emp_record in emp_cursor loop
dbms_output.put_line('第'||emp_cursor%rowcount||'个雇员'||emp_record.ename);
end loop;
end;
--在游标for循环中使用子查询(将游标的定义替换成子查询语句)
begin
for emp_record in (select ename,sal from emp) loop
dbms_output.put_line(emp_record.ename);
end loop;
end;
--使用游标变量
--不含return 语句
declare
type emp_cursor_type is ref cursor;
emp_cursor emp_cursor_type;
emp_record emp%rowtype;
begin
open emp_cursor for select * from emp where deptno=10;
loop
fetch emp_cursor into emp_record;
exit when emp_cursor%notfound;
dbms_output.put_line('第'||emp_cursor%rowcount||'个雇员'||emp_record.ename);
end loop;
close emp_cursor;
end;
--使用cursor表达式
declare
type refcursor is ref cursor;
cursor dept_cursor(no number) is select a.dname,cursor(select ename,sal from emp where deptno=a.deptno) from dept a where a.deptno=no;
empcur refcursor;
v_dname dept.dname%type;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
open dept_cursor(&no);
loop
fetch dept_cursor into v_dname,empcur;
exit when dept_cursor%notfound;
dbms_output.put_line('部门名：'||v_dname);
loop
fetch empcur into v_ename,v_sal;
exit when empcur%notfound;
dbms_output.put_line('雇员名：'||v_ename||',工资：'||v_sal);
end loop;
end loop;
close dept_cursor;
end;
--9.7习题
--4
declare
cursor emp_cursor is select ename,sal from emp;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
if not emp_cursor%isopen then
open emp_cursor;
end if;
loop
fetch emp_cursor into v_ename,v_sal;
exit when emp_cursor%notfound;
dbms_output.put_line('雇员名：'||v_ename||',工资：'||v_sal);
end loop;
end;

--10处理例外
--预定义例外
--(1)ACCESS_INTO_NULL ORA-06530 当开发对象类型时没有初始化
--(2)CASE_NOT_FOUND ORA-06592 case when没有匹配项的时候
--(3)COLLECTION_IS_NULL ORA-06531 在集合元素赋值前，未初始化。
--(4)CURSOR_ALREADY_OPEN ORA-06511 重新打开已经打开的游标时。
--(5)DUP_VAL_ON_INDEX ORA-00001 主键重复插入时
--(6)INVALID_CURSOR ORA-01001 在不合法的游标上操作时
--(7)INVALID_NUMBER ORA-01722 字符不能转换成数字时
--(8)NOT_DATA_FOUND ORA-01403 未查询到数据时
--(9)TOO_MANY_ROWS ORA-01422 查询结果多余一行
--(10)ZERO_DIVIDE ORA-01476 0做除数的时候
--自定义例外处理
--简单的例外处理
declare
v_name emp.ename%type;
begin
select ename into v_name from emp where empno=&no;
exception
when no_data_found then
dbms_output.put_line('没有数据');
end;
--自定义的例外处理
declare e_integrity exception;
pragma exception_init(e_integrity,-2291);
begin
update emp set deptno=&no where empno=&eno;
exception
when e_integrity then
dbms_output.put_line('该部门不存在');
end;
--永远不会执行的过程
create or replace procedure dead_code as
x number:=10;
begin
if x=10 then
x:=20;
else
x:=100;
end if;
end;
--设置pl/sql警告信息
alter system set plsql_warnings='ENABLE:ALL';--检测死代码
alter system set plsql_warnings='ENABLE:PERFORMANCE';--检测性能问题
--11.2开发函数
--11.1建立过程
--无参数的输出过程
create or replace procedure out_time
is begin
dbms_output.put_line(systimestamp);
end;
call out_time();
--带有In参数
create or replace procedure add_employee(eno number,name varchar2,sal number,job varchar2 default 'CLERK',dno number)
is
e_integrity exception;
pragma exception_init(e_integrity,-2291);
begin
insert into emp(empno,ename,sal,job,deptno) values(eno,name,sal,job,dno);
exception
when e_integrity then
dbms_output.put_line('该部门不存在');
end;
--带有out的参数
create or replace procedure query_employee(eno number,name out varchar2,salary out number)
is begin
select ename,sal into name,salary from emp where empno=eno;
end;
--带有in out的过程
--查看过程源代码
select text from user_source where name='ADD_EMPLOYEE';
select text from user_source;--查看所有的
--删除过程
drop procedure add_employee;
--建立函数
--无参函数
create or replace FUNCTION get_user
return varchar2
is v_user varchar2(100);
begin
select username into v_user from user_users;
return v_user;
end;
select get_user from dual;
--带有in参数
create or replace function get_sal(name in varchar2)
return number
is
v_sal emp.sal%type;
begin
select sal into v_sal from emp where upper(ename)=upper(name);
return v_sal;
exception
when no_data_found then
raise_application_error(-20000,'该雇员不存在');
end;
--带有out参数
create or replace function get_info(name varchar2,title out varchar2)
return varchar2
is
deptname dept.dname%type;
begin
select a.job,b.dname into title,deptname from emp a,dept b where a.deptno=b.deptno
and upper(a.ename)=upper(name);
return deptname;
exception
when no_data_found then
raise_application_error(-20000,'该雇员不存在');
end;
--带有in out参数
create or replace function result(num1 number,num2 in out number) return number
as
v_result number(6);
v_remainder number;
begin
v_result:=num1/num2;
v_remainder:=MOD(num1,num2);
num2:=v_remainder;
return v_result;
exception
when zero_divide then
raise_application_error(-20000,'不能除0');
end;
--查看函数源代码
select text from user_source where name='RESULT';
--管理子程序
--(1)列出当前用户的子程序
select object_name,created,status,OBJECT_TYPE from user_objects where object_type in('PROCEDURE','FUNCTION');
--(2)列出子程序源代码，
select text from user_source;
select * from USER_SOURCE WHERE TYPE='TRIGGER';--可以根据类型判断
--(3)列出子程序编译错误
show errors;
select text,error from user_errors where name='错误名';
--(4)查看对象的依赖关系
select name,type from user_dependencies where referenced_name='EMP';--确定直接依赖关系
--(5)重新编译子程序
alter procedure out_time compile;
--对象依赖
select * from user_dependencies where referenced_name='EMP';--查询直接依赖的关系。
select * from user_objects where status='INVALID';--查询出无效状态的子程序，进行重编译，避免错误。
alter view dept_10 compile;
alter trigger tr_instead_of_emp_dept compile;
--习题
create or replace function empid_test(no in number,dno out number) return number is v_dno number;
begin
select deptno into v_dno from emp where empno=no;
if v_dno>0 then
return v_empno;
else
return 0;
end if;
end;
--建立包
--建包
create or replace package emp_package is g_deptno number(3):=30;
procedure add_employee(eno number,name varchar2,salary number,dno number default g_deptno);
function get_sal(eno number) return number;
end emp_package;
--建立包体
create or replace package body emp_package is
function validate_deptno(v_deptno number) return boolean
is v_temp int;
begin
select 1 from dept where deptno=v_deptno;
return true;
exception
when no_data_found then
return false;
end;
procedure add_employee(eno number,name varchar2,salary number,dno number default g_deptno)
is
begin
if validate_deptno(dno) then
insert into emp(empno,ename,sal,deptno) values(eno,name,salary,dno);
else
e_integrity(-2291,'该部门不存在');
end if;
end;
is .. begin .. end;
end emp_package;
--查看包源代码
select text from user_source where name='EMP_PACKAGE' and type='PACKAGE';
--删除包
drop package emp_package;
--使用包重载(overload)
create or replace package overload is
function get_sal(eno number) return number;
function get_sal(ename varchar2) return number;
end overload;
--建立重载包体
create or replace package body overload is
function get_sal(eno number) return number is
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno=eno;
return v_sal;
end;
is
function get_sal(ename varchar2) return number is
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno=eno;
reutrn v_sal;
end;
end overload;
--使用包构造过程
create or replace package emp_package is
sal_max number;
sal_min number;
g_deptno number(3):=30;
procedure add_emp(eno number,ename varchar2,sal number,dno number default g_deptno);
end emp_package;--创建包
create or replace package body emp_package is
procedure add_emp(eno number,ename varchar2,sal number,dno number default g_deptno)
is
begin
insert into emp(empno,ename,sal,deptno) values(eno,ename,sal,dno);
end;
begin
select max(sal),min(sal) into sal_max,sal_min from emp;
end;--创建包体
--使用纯度级别(wnds用于限制函数不能修改数据库数据，禁止dml操作；wnps用于限制函数不能修改包变量，rnds用于限制函数不能读取数据库数据，rnps用于限制函数不能读取包变量)
create or replace package purity is
minsal number(6,2);
maxsal number(6,2);
function max_sal return number;
function min_sal return number;
pragma restrict_references(min_sal,wnps);
pragma restrict_references(max_sal,wnps);
end purity;--建立包
create or replace package body purity is
function max_sal return number
is
begin
select max(sal) into maxsal from emp;
return maxsal;
end;
function min_sal return number
is begin
select min(sal) into minsal from emp;
return minsal;
end;
end;--建立包体，因为设置了纯度级别为wnps，所以是不能为maxsal,minsal赋值的。
create or replace package body purity is
function max_sal return number
is
begin
return maxsal;
end;
function min_sal return number
is
begin
return minsal;
end;
begin
select min(sal),max(sal) into minsal,maxsal from emp;
end;
--开发触发器
--1.建立before语句触发器
create or replace trigger tr_sec_emp
before insert or update or delete on emp
begin
if to_char(sysdate,'DY','nls_date_language=AMERICAN')
in ('SAT','SUN') then
raise_application_error(-20000,'不能在休息日修改雇员信息');
end if;
end;--不允许在休息日操作员工信息。
--2.使用条件谓语
create or replace trigger tr_sec_emp
before insert or update or delete on emp
begin
if to_char(sysdate,'DY','nls_date_language=AMERICAN') in ('SAT','SUN') then
case when inserting then
raise_application_error(-20000,'不能在休息日添加员工');
when updating then
raise_application_error(-20000,'不能在休息日修改员工信息');
when deleting then
raise_application_error(-20000,'不能在休息日删除员工');
end case;
end if;
end;
--3.建立after触发器
--用来审计在emp表上执行的dml操作的执行次数，最早执行时间和最近执行时间。
create table audit_table(name varchar2(20),ins int,upd int,del int,starttime date,endtime date);
create or replace trigger tr_audit_emp
after insert or update or delete on emp
declare
v_temp int;
begin
select count(1) into v_temp from audit_table where name='EMP';
if v_temp=0 then
insert into audit_table values('EMP',0,0,0,sysdate,null);
end if;
case when inserting then
update audit_table set ins=ins+1,endtime=sysdate where name='EMP';
when updating then
update audit_table set upd=upd+1,endtime=sysdate where name='EMP';
when deleting then
update audit_table set del=del+1,endtime=sysdate where name='EMP';
end case;
end;
--建立行触发器
--1.before行触发器
create or replace trigger tr_emp_sal
before update of sal on emp for each row
begin
if :new.sal<:old.sal then
raise_application_error(-20000,'工资只涨不降');
end if;
end;
--2.建立after行触发器。
--审计员工的工资变化
create table audit_emp_change(name varchar2(10),oldsal number(6,2),newsal number(6,2),createtime date);
create or replace trigger tr_sal_change
after update of sal on emp for each row
declare
v_temp int;
begin
select count(1) into v_temp from audit_emp_change where name=:old.ename;
if v_temp=0 then
insert into audit_emp_change values(:old.ename,:old.sal,:new.sal,sysdate);
else
update audit_emp_change set oldsal=:old.sal,newsal=:new.sal,createtime=sysdate where name=:old.ename;
end if;
end;
--3.限制行触发器。(为触发器设置限制条件)
create or replace trigger tr_sal_change
after update of sal on emp for each row when (old.job='SALESMAN')
declare
v_temp int;
begin
select count(1) into v_temp from audit_emp_change where name=:old.ename;
if v_temp=0 then
insert into audit_emp_change values(:old.ename,:old.sal,:new.sal,sysdate);
else
update audit_emp_change set oldsal=:old.sal,newsal=:new.sal,createtime=sysdate where name=:old.ename;
end if;
end;
--4.dml触发器
--(1)控制数据安全
--(2)实现数据审计功能。
--(3)实现数据完整性
--限制雇员新工资不低于原来工资，增长不能高出原来的20%
create or replace trigger tr_check_sal
before update of sal on emp for each row
when (new.sal<old.sal or new.sal>1.2*old.sal)
begin
raise_application_error(-20000,'工资只升不降，并且升幅不能超过20%');
end;
--(4)参照完整性(主从表的完整性约束)
--建立instead-of 触发器(为视图添加数据)
--简单视图里，可以删除表数据。
create view emp_se as select * from emp;
select * from emp_se;
delete from emp_se where empno=2;
--复杂视图(若想执行dml操作语句，需要基于复杂视图建立instead-of触发器)
--建立视图
create or replace view emp_dept as select a.deptno,a.dname,b.empno,b.ename from dept a,emp b where a.deptno=b.deptno;
--建立instead-of触发器
create or replace trigger tr_ins_of_emp_dept
instead of insert on emp_dept for each row
declare
v_temp int;
begin
select count(1) into v_temp from dept where deptno=:new.deptno;
if v_temp=0 then
insert into dept(deptno,dname) values(:new.deptno,:new.dname);
end if;
select count(1) into v_temp from emp where empno=:new.empno;
if v_temp=0 then
insert into emp(empno,ename,deptno) values(:new.empno,:new.ename,:new.deptno);
end if;
end;
--向视图中插入数据
insert into emp_dept(empno,ename,deptno,dname) values(1,'ins',5,'add');
insert into emp_dept(empno,ename,deptno,dname) values(2,'ins',6,'add');
--建立系统事件触发器
--(1)常用事件属性函数
select ora_login_user from dual;
select ora_sysevent from dual;
select ora_client_ip_address from dual;
select ora_database_name from dual;
select ora_des_encrypted_password from dual;
select ora_dict_obj_name from dual;
--(2)建立例程启动和关闭触发器(使用dba登录执行语句)
create table event_table(varchar2(30),time date);
create or replace trigger tr_startup
after startup on database
begin
insert into event_table values(ora_sysevent,sysdate);
end;
create or replace trigger tr_shutdown
before shutdown on database
begin
insert into event_table values(ora_sysevent,sysdate);
end;
--(3)建立登录和退出触发器
create table log_table(username varchar2(20),login_time date,logoff_time date,address varchar2(20));
create or replace trigger tr_logon
after logon on database
begin
insert into log_table(username,login_time,address) values(ora_login_user,sysdate,ora_client_ip_address);
end;
create or replace trigger tr_logoff
before logoff on database
begin
insert into log_table(username,logoff_time,address) values(ora_login_user,sysdate,ora_client_ip_address);
end;
--(4)建立DDL触发器
create table event_dll(event varchar2(20),username varchar2(10),owner varchar2(10),objname varchar2(20),objtype varchar2(20),time date);
create or replace trigger tr_ddl
after ddl on scott.schema
begin
insert into event_dll values(ora_sysevent,ora_login_user,ora_dict_obj_owner,ora_dict_obj_name,ora_dict_obj_type,sysdate);
end;
--管理触发器
--(1)查询触发器信息
select * from user_triggers;
--(2)禁止触发器
alter trigger TR_INSTEAD_OF_EMP_DEPT disable;
--(3)激活触发器
alter trigger TR_INSTEAD_OF_EMP_DEPT enable;
--(4)禁止或激活表的所有触发器
alter table emp disable all triggers;
--(5)重新编译触发器
alter trigger tr_name compile;
--(6)删除触发器
drop tirgger tr_name;
--(7)查询用户的触发器
select * from user_triggers where table_name='EMP';
--动态sql
-- 1.处理非查询语句。
--(1)使用execute immediate处理ddl操作
create or replace procedure drop_table(table_name varchar2)
is
sql_statement varchar2(100);
begin
sql_statement:='drop table '||table_name;
execute immediate sql_statement;
end;
--(2)处理不含占位符的dml语句
declare
sql_statement varchar2(100);
begin
sql_statement:='update emp set sal=sal*1.1 where deptno=6';
execute immediate sql_statement;
end;
--(3)处理含有占位符的dml语句(使用using子句)
declare
sql_statement varchar2(100);
begin
sql_statement:='update emp set sal=sal*(1+:percent/100)'||' where deptno=:dno';
execute immediate sql_statement using &1,&2;
end;
--(4)处理含有returning子句的。
declare
salary number(6,2);
sql_statement varchar2(100);
begin
sql_statement:='update emp set sal=sal*(1+:precent/100) '||'where empno=:eno returning sal into :salary';
execute immediate sql_statement using &1,&2 returning into salary;
dbms_output.put_line(salary);
end;
--(5)处理单行查询
declare
emp_record emp%rowtype;
sql_statement varchar2(100);
begin
sql_statement:='select * from emp where empno=:eno';
execute immediate sql_statement into emp_record using &1;
dbms_output.put_line(emp_record.ename);
end;
--2.处理多行查询实例
--(1)多行查询实例
declare
type emp_cursor_type is ref cursor;
emp_cursor emp_cursor_type;
emp_record emp%rowtype;
sql_statement varchar2(100);
begin
sql_statement:='select * from emp where deptno=:dno';
open emp_cursor for sql_statement using &dno;--直接根据动态sql打开游标
loop
fetch emp_cursor into emp_record;
exit when emp_cursor%notfound;
dbms_output.put_line(emp_record.ename);
end loop;
close emp_cursor;
end;
--(2)在动态Sql中使用bulk子句
declare
type emp_ename_type is table of emp.ename%type index by binary_integer;
emp_ename emp_ename_type;
sql_statement varchar2(100);
begin
sql_statement:='select ename from emp where deptno=:dno';
execute immediate sql_statement bulk collect into emp_ename using &dno;
for i in 1..emp_ename.count loop
dbms_output.put_line(emp_ename(i));
end loop;
end;
--(3)在fetch语句中使用bulk子句
declare type empcurtype is ref cursor;
emp_cv empcurtype;
type ename_table_type is table of emp.ename%type index by binary_integer;
ename_table ename_table_type;
sql_stat varchar2(100);
begin
sql_stat:='select ename from emp where job=:title';
open emp_cv for sql_stat using '&job';
fetch emp_cv bulk collect into ename_table;
for i in 1..ename_table.count loop
dbms_output.put_line(ename_table(i));
end loop;
close emp_cv;
end;
--(4)在forall子句中使用bulk子句
declare
type ename_table_type is table of emp.ename%type;