/*  创建表空间  语法:  表空间:create tablespace 表空间名称 datafile 物理文件存放路径 size 文件大小以M为单位  临时表空间:  create temporary tablespace 表空间名称 tempfile 物理文件存放路径 size 文件大小以M为单位*/ create tablespace test_space datafile ‘E:\YC59\Oracle\space\test_space‘ size 10m;create temporary tablespace temp_space tempfile ‘E:\YC59\Oracle\space\temp_space‘ size 10m;-- 修改表空间alter tablespace test_space add datafile ‘E:\YC59\Oracle\space\test_space_1‘ size 10m;-- 修改数据库的默认表空间--alter database default tablespace test_space;alter database default tablespace system;-- 查询数据库的默认表空间select * from database_properties where property_name = ‘DEFAULT_PERMANENT_TABLESPACE‘;-- 删除表空间drop tablespace test_space including contents and datafiles;select * from dual;create tablespace test_sapce datafile ‘E:\YC59\space\AAAA‘ size 10M;alter tablespace test_sapce add datafile ‘E:\YC59\space\AAAA_1‘ size 10M;drop tablespace test_sapce including contents and datafiles;/*****************用户管理*************************//*     创建用户的语法:     create user 用户名 identified by 密码 default tablespace 表空间名称 */create user gerry identified by a123 default tablespace test_sapce;create user zhangsan identified by a123 default tablespace test_sapce;/*     给用户授权:grant 权限名称 to 用户名*/-- 给gerry用户授予登录的权限grant create session to gerry;-- 给gerry用户授予resource角色grant resource to gerry;-- 给gerry用户授予查询scott用户emp表的操作grant select,update,delete,insert on scott.emp to gerry;/*      创建角色*/create role pro_role;drop role pro_role;/*       给角色授权*/grant resource to pro_role;grant create session to pro_role;grant select on scott.emp to pro_role;/*      把创建的角色赋予用户gerry,zhangsan*/grant pro_role to gerry;grant pro_role to zhangsan;/*     回收权限     revoke 权限名称/ 角色名称 from 用户*/revoke select on scott.emp from gerry;revoke resource from gerry;revoke create session from gerry;revoke pro_role from gerry;/*       修改用户的密码,锁定用户，解锁用户*/alter user gerry identified by gerry;-- 锁定用户alter user gerry account lock;-- 解锁用户alter user gerry account unlock;/*      删除用户*/drop user zhangsan cascade;-- 查询当前数据库的控制文件select * from v$controlfile;-- 查询当前数据库对应的数据文件操作记录select type record_size,records_total,records_used from       V$controlfile_Record_Section       where type=‘DATAFILE‘;-- 查询当前用户的表空间信息[必须以dba身份登录]select * from dba_data_files;-- 查询当前用户可以访问的所有基表select * from cat;-- 创建表空间create tablespace test_space datafile ‘E:\YC59\testspace.dbf‘ size 10M;-- 创建用户create user test_user identified by a123 default tablespace test_space;-- 授权于dba角色grant dba to test_user;/* 以上语句必须使用dba角色操作 **/-- 创建表create table TestInfo(       testId number primary key,       testName varchar2(20) not null,       testDesc varchar2(234)       ) tablespace test_space;-- 为表添加一个字段testPassalter table TestInfo add(testPass varchar2(20) not null);alter table TestInfo add(testSex varchar2(20) not null);-- 为testPass字段添加默认值alter table TestInfo modify(testPass varchar(20) default ‘888888‘);-- 为testName添加唯一约束alter table TestInfo      add constraint UQ_TSETNAME unique(testName);-- 为testSex添加检查约束alter table TestInfo      add constraint CK_SEX check(testSex in (‘男‘,‘女‘));/**************以上为回顾内容************************//*      序列：      create sequence 序列名称      start with 初始值      increment by 递增值      minvalue 最小值【初始值不能小于最小值】      maxvalue 最大值      cycle/cache 缓存数;**/create sequence testInfo_seq       start with 10001       increment by 1       minvalue 1       maxvalue 999999       cache 20;-- 查询当前序列的值select testInfo_seq.Currval from dual;-- 必须使用一次序列该语句才有效-- 使用当前序列select testInfo_seq.Nextval from dual;-- 使用创建的序列insert into testInfo(testId,Testname,Testdesc,Testsex)        values (testInfo_seq.Nextval,‘测试11‘,‘测网速‘,‘男‘);commit;-- 删除序列drop sequence testInfo_seq;/*       创建视图:       create <or replace> view 视图名称       as              查询语句;       视图只能用来做查询不能编辑*/--select testId 编号,testName 测试名称,testDesc 描述,testPass 密码,testSex 性别 from testInfo;create or replace view queryTestInfo       as       select testId 编号,testName 测试名称,testDesc 描述,testPass 密码,testSex 性别 from testInfo;-- 使用视图select * from queryTestInfo;-- 删除视图drop view queryTestInfo;/*     同义词：     create or replace [public] synonym 同义词名 for 表名**/-- 创建私有同义词create or replace synonym u for testInfo;select * from u;-- 创建公有的同义词create or replace public synonym u1 for testInfo;select * from u1;-- 其他用户要使用创建的公有同义词必须拥有该表的查询权限grant select on testInfo to scott; -- 查询当前用户使用的默认表空间select * from user_users;-- 创建学生表create table Student1(       stuId int primary key,       stuName varchar2(20) not null,       stuSex char(2) not null,       stuAge integer not null) tablespace test_sapce; -- 指定表存放的表空间create table studentType(       type_Id int primary key,       stuId int not null) tablespace test_sapce; -- 指定表存放的表空间/*  约束类型:  唯一约束：unique  检查约束:check  默认约束:defalut  主键约束:primary key  外键约束:foreign key*/--- 给student表的stuName字段添加唯一约束alter table student1 add constraint UQ_STUNAME unique(stuName);-- 检查唯一约束insert into Student1 values(1001,‘张三‘,‘男‘,10);insert into Student1 values(1002,‘张三‘,‘男‘,10);-- 给stuAge添加检查约束alter table student1 add constraint CK_AGE check(stuAge>0 and stuAge<200);insert into Student1 values(1002,‘张三‘,‘男‘,1010);-- 给stuSex添加默认约束alter table student1 modify(stuSex char(2) default ‘女‘);insert into Student1(stuId,Stuname,Stuage) values(1002,‘张三‘,100);-- 给studetnType表创建外键约束alter table studentType      add constraint FK_STUID foreign key(stuId) references Student1(stuId);      insert into studentType values(1001,10002);select * from student1;select * from studentTYpe;--- 给student1表添加一个QQ字段alter table student1 add(qq number(11) not null);/*  添加数据的语法:insert into 表名<字段1，字段2..... 字段n> values(值1，值2,..... 值n)  修改数据的语法:update 表名 set 字段1=值1，字段2=值2 ..... 字段n = 值n where 条件【修改的条件】  删除数据的语法:delete from 表名 where[删除的条件]  查询数据语法: select 筛选的字段 from 表名 where [筛选的条件]*/create table Grade(       gradeId number primary key,       gradeName varchar2(20) not null)tablespace test_space;create table Course(       courseId number primary key,       courseName varchar2(20) not null)tablespace test_space;create table student(       studentId number primary key,       studentNo varchar2(20) not null,       studentName varchar2(20) not null,       studentSex char(13) not null,       gradeId number not null)tablespace test_space;create table Score(       scoreId number primary key,       courseId number not null,       studentId number not null,       score number(3,1) not null)tablespace test_space;-- 创建外键约束alter table student      add constraint FK_GRADEID foreign key(gradeId) references Grade(gradeId);alter table score add       constraint FK_COURSEID foreign key(courseId) references Course(courseId);alter table score add       constraint FK_STUDENTID foreign key(studentId) references Student(studentId);--- 创建序列create sequence grade_seq start with 1001 increment by 1 cache 50;create sequence coruse_seq start with 1001 increment by 1 cache 50; create sequence student_seq start with 1001 increment by 1 cache 50; create sequence score_seq start with 1001 increment by 1 cache 50; -- 插入测试数据insert into grade values(grade_seq.nextval,‘科技班‘);insert into grade values(grade_seq.nextval,‘三维班‘);insert into grade values(grade_seq.nextval,‘无人班‘);commit;insert into course values(coruse_seq.nextval,‘Java‘);insert into course values(coruse_seq.nextval,‘JSP‘);insert into course values(coruse_seq.nextval,‘PHP‘);commit;insert into student values(student_seq.nextval,‘STU001‘,‘张三‘,‘男‘,1001);insert into student values(student_seq.nextval,‘STU002‘,‘李斯‘,‘男‘,1001);insert into student values(student_seq.nextval,‘STU003‘,‘貂蝉‘,‘女‘,1002);insert into student values(student_seq.nextval,‘STU004‘,‘王昭君‘,‘女‘,1002);commit;-- 张三insert into score values(score_seq.nextval,1001,1001,98.2);insert into score values(score_seq.nextval,1002,1001,78.2);-- 李斯insert into score values(score_seq.nextval,1003,1002,18.2);insert into score values(score_seq.nextval,1002,1002,68.2);insert into score values(score_seq.nextval,1001,1003,48.2);insert into score values(score_seq.nextval,1003,1003,68.2);insert into score values(score_seq.nextval,1001,1004,78.2);insert into score values(score_seq.nextval,1003,1004,88.2);insert into score values(score_seq.nextval,1002,1004,87);commit;select * from grade;select * from course;select * from student;select * from score;-- 连接表（自连，左右外连接，内连接，全连接）select * from grade,course,student -- 全连接-- 查询Scott用户的上司[自连]select manager.ename from scott.emp manager,scott.emp employee       where manager.empno = employee.mgr and employee.ename=‘SCOTT‘;-- 字段值区分大小写-- 左右外连接select * from grade g left join student stu on g.gradeId = stu.gradeid;select * from grade g right join student stu on g.gradeId = stu.gradeid;-- 内联接select * from student stu inner join grade g on g.gradeid = stu.gradeid-- 查询张三学生的所有成绩信息select g.gradename,stu.studentname,c.coursename,s.score       from student stu inner join grade g on stu.gradeid = g.gradeid       inner join score s on s.studentid = stu.studentid       inner join course c on s.courseid = c.courseid where stu.studentname = ‘张三‘;       select g.gradename,stu.studentname,c.coursename,s.score       from grade g,student stu,course c,score s where       stu.gradeid = g.gradeid and       s.studentid = stu.studentid and       s.courseid = c.courseid and       stu.studentname = ‘王昭君‘;       -- 子查询作为结果select * from student where studentId not in(select studentId from score where        courseId = (select courseId from course where courseName=‘Java‘))-- 子查询作为表select * from (select g.gradename 班级,stu.studentname 姓名,c.coursename 课程,s.score 成绩       from grade g,student stu,course c,score s where       stu.gradeid = g.gradeid and       s.studentid = stu.studentid and       s.courseid = c.courseid) newtable where 班级=‘三维班‘-- 使用伪列分页select * from (select student.*,rownum as rowindex from student where rownum<=1) where rowindex>0;/************多表查询的集合操作符**************/-- union 去掉查询结果中重复数据select * from empunionselect * from emp;-- union all 查询两个表中所有的记录select * from empunion allselect * from emp;--intersect 查询两张表中所有相同的记录create table emp1 as select * from emp;select * from emp1intersectselect * from emp;-- Minus 去掉两张表重复的数据，返回不重复的数据记录select * from empminusselect * from emp1;/***********事务处理[保证数据库中数据的一致性]，{ACID,原子性，一致性，隔离性，持久性}********/select * from deptinsert into scott.dept values(70,‘NEQDS‘,‘USA‘);  commit;update dept set dname=‘AAAA‘ where deptno=70;rollback;-- 事务中保存点--create table dept1 as select * from dept;update dept set dname=‘AAAA1‘ where deptno=10;update dept set dname=‘AAAA2‘ where deptno=20;update dept set dname=‘AAAA3‘ where deptno=30;update dept set dname=‘AAAA4‘ where deptno=40;-- 定义保存点savepoint a; -- 回滚保存点之后内容update dept set dname=‘AAAA5‘ where deptno=70;rollback to a; -- 回滚到指定保存点select * from dept;select * from emp;select * from dept;-- 使用Oracle的聚合函数-- 统计在职员工人数select count(*) as 在职人数 from emp;-- 统计在所有员工薪资情况select max(sal) 最高工资,min(sal) 最低工资,avg(sal) 平均工资,sum(sal) 总工资 from emp;-- 统计每一个部门的员工薪资情况select deptno 部门编号,max(sal) 最高工资,min(sal) 最低工资,avg(sal) 平均工资,sum(sal) 总工资        from emp group by deptno-- 排序select * from emp order by empno asc;select * from emp order by empno desc;-- 统计每一个部门的员工薪资平均工资大于2000的部门情况select deptno 部门编号,max(sal) 最高工资,min(sal) 最低工资,avg(sal) 平均工资,sum(sal) 总工资        from emp group by deptno having(avg(sal)>2000);-- having经常和group by联用--去除重复的函数create table test(       testId number primary key,       testName varchar2(20) not null,       testStatus varchar2(20) not null)tablespace users;insert into test values(1,‘test‘,‘准备中‘);insert into test values(2,‘test1‘,‘进行中‘);insert into test values(3,‘test2‘,‘进行中‘);insert into test values(4,‘test3‘,‘以完成‘);insert into test values(5,‘test4‘,‘进行中‘);commit;-- 提取测试存在的状态select distinct(testStatus) from test;-- PL/SQL块异常处理-- 未进行异常处理declare v_ename emp.ename%TYPE; begin   select ename into v_ename  from emp where empno=&eno;   dbms_output.put_line(v_ename);   end;   -- 使用了exception处理的declare v_ename emp.ename%TYPE; begin   select ename into v_ename  from emp where empno=12;   dbms_output.put_line(v_ename);   exception     when no_data_found then       dbms_output.put_line(‘你输入的员工号不正确！‘);     when invalid_number then       dbms_output.put_line(‘字符串不能转换为数字‘);     when too_many_rows then       dbms_output.put_line(‘返回实际行数大于查询行数‘);        end;-- 自定义异常[自定义异常需要显示的触发raise]declare    e_intergerity exception; -- 非预定义异常，系统预定义处理不了的异常交给非预定义异常处理   e_no_employee exception; -- 自定义异常   pragma exception_init(e_intergerity,-20010); -- 关联非预定义异常begin  update emp set deptno=40 where empno=&eno;         if SQL%NOTFOUND then  -- 返回行数存在为false，不存在为true           -- 显示触发自定义异常           raise e_no_employee;         end if;                  exception           when e_no_employee then               dbms_output.put_line(‘没有找到员工信息‘);           when e_intergerity then               dbms_output.put_line(‘部门信息部不存在‘);  end;  --------------------------游标---------------------/*  declare      cursor 游标名称<(参数名 参数类型)>      is      查询语句;  begin    open 游标名称<(参数值)>;           loop             fetch 游标名称 into 变量名1,变量名称2... 变量名n;             exit when 游标名称%NOTFOUND;      end loop;      close 游标名称;    end;*/-- 通过输入的部门名称查询相应的员工姓名(无参数)declare   cursor emp_cursor   is   select ename from emp where deptno=&eno;   v_ename emp.ename%TYPE;begin  open emp_cursor;        loop          fetch emp_cursor into v_ename;          exit when emp_cursor%NOTFOUND;          dbms_output.put_line(v_ename);    end loop;    close emp_cursor;  end;  -- 通过输入的部门名称查询相应的员工姓名(带参数)declare   cursor emp_cursor(cno number)   is   select ename from emp where deptno=cno;   v_ename emp.ename%TYPE;begin  open emp_cursor(&eno);        loop          fetch emp_cursor into v_ename;          exit when emp_cursor%NOTFOUND;          dbms_output.put_line(v_ename);    end loop;    close emp_cursor;  end;  -- 通过游标删除或者更新数据INSERT INTO dept VALUES(50, ‘FINANCE‘, ‘CHICAGO‘);declare    cursor emp_cursor   is    select ename,sal from emp for update of sal;   v_name 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_name,v_sal;          exit when emp_cursor%NOTFOUND;          if v_sal<2500 then            --update emp set sal=v_sal+150 where current of emp_cursor;            delete from emp where current of emp_cursor;          end if;     end loop;  close emp_cursor;  commit; end; -- 游标for循环declare    cursor dept_cursor   is   select dname from dept;   begin     for dept_red in dept_cursor loop       dbms_output.put_line(dept_red.dname);     end loop;end;begin  for dept_record in (select dname from dept)  loop    dbms_output.put_line(dept_record.dname);    end loop;    end;   /*  PL/SQL 块  语法：  declare      begin       -- PL/SQL块和SQL语句       Exception         -- 异常处理       end;*/-- 定义通过员工编号查询员工姓名的SQL块declare   v_name varchar2(20);   begin     select ename into v_name from emp where empno=&eno;     Dbms_Output.put_line(‘姓名:‘ || v_name);     Exception       when no_data_found then         Dbms_Output.put_line(‘请输入正确的员工号！‘);     end;/*     PL/SQL 块中的 := 赋值符号     %Type 获取某个表中字段类型     定义常量使用 constant关键字*/-- 计算员工工资的所得税declare   v_name emp.ename%TYPE;   v_sal emp.sal%TYPE;   v_rate constant number(3,2):=0.03;-- 定义常量   v_tax_rate number(6,2);   begin     select ename,sal into v_name,v_sal from emp where empno=&eno;     v_tax_rate := v_sal*v_rate;     Dbms_Output.put_line(‘姓名:‘ || v_name);     Dbms_Output.put_line(‘工资:‘ || v_sal);     Dbms_Output.put_line(‘所得税:‘ || v_tax_rate);     Exception       when no_data_found then         Dbms_Output.put_line(‘请输入正确的员工号！‘);     end;     /*判断语句分支   */declare   v_deptno emp.deptno%TYPE;  v_sal emp.sal%TYPE;  begin    select deptno,sal into v_deptno,v_sal from emp where ename=trim(‘&ename‘);      if v_deptno = 10 then        update emp set sal = v_sal+100 where deptno = v_deptno;        commit;      elsif v_deptno = 20 then        update emp set sal = v_sal+80 where deptno = v_deptno;        commit;      elsif v_deptno = 30 then        update emp set sal = v_sal+180 where deptno = v_deptno;        commit;      end if;    end;/*    case       when.... then;      when.... then;      when.... then;    end case;*/-- 根据员工的不同薪资更新员工津贴declare   v_sal emp.sal%TYPE;   v_ename emp.ename%TYPE;   v_comm emp.comm%TYPE;   begin     select ename,comm,sal into v_ename,v_comm,v_sal from emp where empno = &eno;       case           when v_sal<2000 then             update emp set comm = v_comm+200 where ename = v_ename;             commit;           when v_sal<3000 then             update emp set comm = v_comm+150 where ename = v_ename;             commit;           when v_sal<4000 then             update emp set comm = v_comm+100 where ename = v_ename;             commit;         end case;         exception         when no_data_found then              Dbms_Output.put_line(‘请输入正确的员工号！‘);                   end;-- 使用record复合类型对象declare   type emp_record_type is record (   v_sal emp.sal%TYPE,   v_ename emp.ename%TYPE,   v_comm emp.comm%TYPE   );   emp_record emp_record_type; -- 定义自定义的数据类型变量   begin     select sal,ename,comm into emp_record from emp where empno = &eno;       case           when emp_record.v_sal<2000 then             update emp set comm = emp_record.v_comm+200 where ename = emp_record.v_ename;             commit;           when emp_record.v_sal<3000 then             update emp set comm = emp_record.v_comm+150 where ename = emp_record.v_ename;             commit;           when emp_record.v_sal<4000 then             update emp set comm = emp_record.v_comm+100 where ename = emp_record.v_ename;             commit;         end case;     end;     select *　from emp;/*     循环:基本循环，while循环，for循环*/-- 使用基本循环结构loop.... end loop;declare    i int := 1;   begin     loop       dbms_output.put_line(i);       i :=i+1;       exit when i=11;       end loop;     end;-- 使用whiledeclare    i int := 1;   begin   while i<11 loop     dbms_output.put_line(i);     i :=i+1;     end loop;   end;   -- for 循环 reverse 倒序输出declare    i number := 1;   begin     for i in reverse 71..99 loop       dbms_output.put_line(i);       insert into dept values(i,‘部门‘||i,‘new‘);       commit;       end loop;     end;     select * from dept;-- 创建存储过程的语法/*   create or replace procedure 存储过程名(参数名称 参数模式[in,out,inout] 参数类型....)   is   PL/SQL block;      注意:过程参数不能指定长度*/-- 创建不带参数的存储过程create or replace procedure no_param_programaisv_name varchar2(20);begin    --select ename into v_name from emp where empno=&eno;    dbms_output.put_line(v_name);    dbms_output.put_line(systimestamp);end;-- 执行存储过程call no_param_programa();begin  no_param_programa;  end;-- 创建带输入参数的存储过程create or replace procedure param_programa(eno number)isv_name varchar2(20);begin  select ename into v_name from emp where empno=eno;  dbms_output.put_line(v_name);end;-- 测试带输入参数的存储过程call param_programa(7521);-- 创建带输出参数的存储过程create or replace procedure out_param_programa(eno number,empname out varchar2)isbeginselect ename into empname from emp where empno=eno;end;-- 测试带输出参数的存储过程declare     v_name varchar2(20);begin    out_param_programa(7521,v_name);    dbms_output.put_line(v_name);end;-- 创建函数/*   create or replace function 函数名(参数名称 参数模式[in,out,inout] 参数类型....)   return dataType[数据类型]   is   定义变量[定义变量不能使用declare关键字]   PL/SQL block;*/-- 创建根据员工号查询所在部门编号create or replace function getDeptByEmpno(eno number,e_name out  varchar2)return numberisv_deptno number;begin  select deptno,ename into v_deptno,e_name from emp where empno=eno;  return v_deptno;end;-- 测试带输入,输出参数的函数declare    v_deptno number;   v_name varchar2(20);begin  v_deptno := getDeptByEmpno(7788,v_name);  dbms_output.put_line(v_deptno || ‘  ‘ || v_name);  end;  -- 只能是输入参数才能作为查询语句一部分  select * from emp where deptno = getDeptByEmpno(7788);    --- 触发器/*    create or replace trigger 触发器名    before/after/instead of on 表名    PL/SQL 块；*/create or replace trigger trigger_edit before insert or delete or update on empbegin  if to_char(sysdate,‘DY‘,‘nls_date_language=AMERICAN‘) in (‘SAT‘,‘SUN‘) then    RAISE_APPLICATION_ERROR(-20002,‘今天是周末，不能操作员工信息‘);  end if;end trigger_edit;select * from emp where  empno=7788;delete from emp where empno=7788;update emp set ename=‘SJSJS‘ where empno=7788;

/*  表触发器  create or replace trigger 触发器名称  before/after insert or update or delete on 表名  PL/SQL block;*/create or replace trigger trig_opbefore insert or update or delete on empbegin  if(to_char(sysdate,‘DY‘,‘nls_date_language=AMERICAN‘) in (‘SAT‘,‘SUN‘))    then      raise_application_error(-20002,‘周末不能操作员工信息‘);  end if;end;-- 删除触发器drop trigger trig_op;-- 定义条件谓词触发器create or replace trigger trig_op_predicatebefore insert or update or delete on empbegin  dbms_output.put_line(‘a‘);  if(to_char(sysdate,‘DY‘,‘nls_date_language=AMERICAN‘) in (‘SAT‘,‘SUN‘))    then      case        when inserting then            raise_application_error(-20001,‘周末时间不能添加员工‘);        when updating then            raise_application_error(-20002,‘周末时间不能更新员工‘);        when deleting then            raise_application_error(-20003,‘周末时间不能删除员工‘);      end case;  end if;end;drop trigger trig_op_predicate;delete from emp where deptno = 10;update emp set deptno=40 where empno=7839;select * from dept;select * from emp;/*  行触发器  create or replace trigger 触发器名称  before/after insert or update or delete on 表名 for each row  PL/SQL block;  注意: :old,:new 只能在行级触发器中使用*/create or replace trigger trig_op_predicate_rowbefore insert or update or delete on emp for each rowbegin  dbms_output.put_line(‘a‘);  if(to_char(sysdate,‘DY‘,‘nls_date_language=AMERICAN‘) in (‘SAT‘,‘SUN‘))    then      case        when inserting then            raise_application_error(-20001,‘周末时间不能添加员工‘);        when updating then            raise_application_error(-20002,‘周末时间不能更新员工‘);        when deleting then            raise_application_error(-20003,‘周末时间不能删除员工‘);      end case;  end if;end;create or replace trigger trig_op_predicate_row_afterafter insert or update or delete on emp for each rowbegin  dbms_output.put_line(‘a‘ || :old.deptno);  if(to_char(sysdate,‘DY‘,‘nls_date_language=AMERICAN‘) not in (‘SAT‘,‘SUN‘))    then      case        when inserting then            dbms_output.put_line(‘添加成功‘);        when updating then            dbms_output.put_line(‘更新成功‘);        when deleting then            dbms_output.put_line(‘删除成功‘);      end case;  end if;end;drop trigger trig_op_predicate_row;--- 在添加的新部门里面添加员工信息-- 创建基于部门表的触发器create or replace trigger insert_deptafter insert on dept for each rowbegin  -- 在新添加的部门里面添加员工  insert into emp values(7564,‘GERRY‘,‘DPET‘,7839,to_date(‘1988-10-21‘,‘yyyy-mm-dd‘),1500,null,:new.deptno);  end;insert into dept values(55,‘NEWDEPT‘,‘canada‘);---------- 程序包-- 创建包头:create or replace package 包名 is 存储过程，函数声明 end;-- 创建包体:create or replace package body 包名 存储或者函数实现细节 end;create or replace package dpet_packageisprocedure add_dept(deptno varchar2,deptname varchar2,loc varchar2);procedure del_dept(dno number);end;-- 创建包体create or replace package body dpet_packageis  procedure add_dept(deptno varchar2,deptname varchar2,loc varchar2)  is  begin    insert into dept values(deptno,deptname,loc);    end;  -- 删除部门过程  procedure del_dept(dno number)  is    v_count number;  begin    select count(*) into v_count from emp where deptno=dno;    if v_count=0 then      delete from dept where deptno = dno;    else      raise_application_error(-20014,‘有员工的部门不能被删除‘);    end if;    end;end;-- 添加部门dpet_package.add_dept(‘60‘,‘DNND‘,‘DDDD‘);