10年读pl/sql编程指南敲下了上面大部分例子(备用)

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;