/*
创建表空间
语法:
表空间: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;
-- 为表添加一个字段testPass
alter 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 emp
union
select * from emp;
-- union all 查询两个表中所有的记录
select * from emp
union all
select * from emp;
--intersect 查询两张表中所有相同的记录
create table emp1 as select * from emp;
select * from emp1
intersect
select * from emp;
-- Minus 去掉两张表重复的数据，返回不重复的数据记录
select * from emp
minus
select * from emp1;
/***********事务处理[保证数据库中数据的一致性]，{ACID,原子性，一致性，隔离性，持久性}********/
select * from dept
insert 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;
-- 使用while
declare
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_programa
is
v_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)
is
v_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)
is
begin
select 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 number
is
v_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 emp
begin
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_op
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(-20002,'周末不能操作员工信息');
end if;
end;
-- 删除触发器
drop trigger trig_op;
-- 定义条件谓词触发器
create or replace trigger trig_op_predicate
before insert or update or delete on emp
begin
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_row
before insert or update or delete on emp for each row
begin
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_after
after insert or update or delete on emp for each row
begin
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_dept
after insert on dept for each row
begin
-- 在新添加的部门里面添加员工
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_package
is
procedure add_dept(deptno varchar2,deptname varchar2,loc varchar2);
procedure del_dept(dno number);
end;
-- 创建包体
create or replace package body dpet_package
is
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');
