create or replace trigger t1
after
insert
on person
declare
begin
dbms_output.put_line('新员工入职');
end;
insert into person values(2,'小红',2);
commit;
select * from person;
set serveroutput on
delete from person where pid=1;
commit;
insert into person values(1,'小明',1);
commit;
---- 行级触发器:
create or replace trigger t2
before
update
on emp
for each row
declare
begin
if :old.sal>:new.sal then
raise_application_error(-20001,'不能给员工降薪');
end if;
update emp set sal=sal-1 column where empno=1001;
commit;
select * from emp;
select * from emp;
conn sys/oracle as SYSDBA;
declare
i number(2) := 10;
s varchar2(10) := '小码';
ena emp.ename%TYPE;
begin
dbms_output.put_line(i);
dbms_output.put_line(s);
select ename into ena from emp where id = 1001;
dbms_output.put_line(ena);
end;
set serveroutput on
declare
i number(3) := ⅈ
begin
if i<18 then
dbms_output.put_line('未成年人');
elsif i<40 then
dbms_output.put_line('中年人');
else
dbms_output.put_line('老年人');
end if;
end;
declare
i number(2) := 1;
begin
while i<11 loop
dbms_output.put_line(i);
i := i+1;
end loop;
end;
declare
i number(2) := 1;
begin
loop
exit when i>10;
dbms_output.put_line(i);
i := i+1;
end loop;
end;
declare
begin
for i in 1..10 loop
dbms_output.put_line(i);
end loop;
end;
drop table emp;
CREATE TABLE emp(
empno NUMBER(10),
ename VARCHAR2(50),
job VARCHAR2(50),
mgr NUMBER(20),
sal DECIMAL(7,2),
COMM DECIMAL(7,2),
deptno NUMBER(20)
);
/*创建工资等级表*/
CREATE TABLE salgrade(
grade NUMBER(30),
losal NUMBER(20),
hisal NUMBER(20)
);
/*创建学生表*/
CREATE TABLE stu(
sid NUMBER(20),
sname VARCHAR2(50),
age NUMBER(20),
gander VARCHAR2(10),
province VARCHAR2(50),
tuition NUMBER(10)
);
conn sys/oracle as sysdba;
show user
INSERT INTO emp VALUES (1009,'曾阿牛','董事长',NULL,50000,NULL,10);
INSERT INTO emp VALUES (1004,'刘备','经理',1009,29750,NULL,20);
INSERT INTO emp VALUES (1006,'关羽','经理',1009,28500,NULL,30);
INSERT INTO emp VALUES (1007,'张飞','经理',1009,24500,NULL,10);
INSERT INTO emp VALUES (1008,'诸葛亮','分析师',1004,30000,NULL,20);
INSERT INTO emp VALUES (1013,'庞统','分析师',1004,30000,NULL,20);
INSERT INTO emp VALUES (1002,'黛绮丝','销售员',1006,16000,3000,30);
INSERT INTO emp VALUES (1003,'殷天正','销售员',1006,12500,5000,30);
INSERT INTO emp VALUES (1005,'谢逊','销售员',1006,12500,14000,30);
INSERT INTO emp VALUES (1010,'韦一笑','销售员',1006,15000,0,30);
INSERT INTO emp VALUES (1012,'程普','文员',1006,9500,NULL,30);
INSERT INTO emp VALUES (1014,'黄盖','文员',1007,13000,NULL,10);
INSERT INTO emp VALUES (1011,'周泰','文员',1008,11000,NULL,20);
INSERT INTO emp VALUES (1001,'甘宁','文员',1013,8000,NULL,20);
commit;
/*插入stu表数据*/
INSERT INTO stu VALUES (1,'王永',23,'男','北京',1500);
INSERT INTO stu VALUES (2,'张雷',25,'男','辽宁',2500);
INSERT INTO stu VALUES (3,'李强',22,'男','北京',3500);
INSERT INTO stu VALUES (4,'宋永合',25,'男','北京',1500);
INSERT INTO stu VALUES (5,'叙美丽',23,'女','北京',1000);
INSERT INTO stu VALUES (6,'陈宁',22,'女','山东',2500);
INSERT INTO stu VALUES (7,'王丽',21,'女','北京',1600);
INSERT INTO stu VALUES (8,'李永',23,'男','北京',3500);
INSERT INTO stu VALUES (9,'张玲',23,'女','广州',2500);
INSERT INTO stu VALUES (10,'啊历',18,'男','山西',3500);
INSERT INTO stu VALUES (11,'王刚',23,'男','湖北',4500);
INSERT INTO stu VALUES (12,'陈永',24,'男','北京',1500);
INSERT INTO stu VALUES (13,'李雷',24,'男','辽宁',2500);
INSERT INTO stu VALUES (14,'李沿',22,'男','北京',3500);
INSERT INTO stu VALUES (15,'王小明',25,'男','北京',1500);
INSERT INTO stu VALUES (16,'王小丽',23,'女','北京',1000);
INSERT INTO stu VALUES (17,'唐宁',22,'女','山东',2500);
INSERT INTO stu VALUES (18,'唐丽',21,'女','北京',1600);
INSERT INTO stu VALUES (19,'啊永',23,'男','北京',3500);
INSERT INTO stu VALUES (20,'唐玲',23,'女','广州',2500);
INSERT INTO stu VALUES (21,'叙刚',18,'男','山西',3500);
INSERT INTO stu VALUES (22,'王累',23,'男','湖北',4500);
INSERT INTO stu VALUES (23,'赵安',23,'男','北京',1500);
INSERT INTO stu VALUES (24,'关雷',25,'男','辽宁',2500);
INSERT INTO stu VALUES (25,'李字',22,'男','北京',3500);
INSERT INTO stu VALUES (26,'叙安国',25,'男','北京',1500);
INSERT INTO stu VALUES (27,'陈浩难',23,'女','北京',1000);
INSERT INTO stu VALUES (28,'陈明',22,'女','山东',2500);
INSERT INTO stu VALUES (29,'孙丽',21,'女','北京',1600);
INSERT INTO stu VALUES (30,'李治国',23,'男','北京',3500);
INSERT INTO stu VALUES (31,'张娜',23,'女','广州',2500);
INSERT INTO stu VALUES (32,'安强',18,'男','山西',3500);
INSERT INTO stu VALUES (33,'王欢',23,'男','湖北',4500);
INSERT INTO stu VALUES (34,'周天乐',23,'男','北京',1500);
INSERT INTO stu VALUES (35,'关雷',25,'男','辽宁',2500);
INSERT INTO stu VALUES (36,'吴强',22,'男','北京',3500);
INSERT INTO stu VALUES (37,'吴合国',25,'男','北京',1500);
INSERT INTO stu VALUES (38,'正小和',23,'女','北京',1000);
INSERT INTO stu VALUES (39,'吴丽',22,'女','山东',2500);
INSERT INTO stu VALUES (40,'冯含',21,'女','北京',1600);
INSERT INTO stu VALUES (41,'陈冬',23,'男','北京',3500);
INSERT INTO stu VALUES (42,'关玲',23,'女','广州',2500);
INSERT INTO stu VALUES (43,'包利',18,'男','山西',3500);
INSERT INTO stu VALUES (44,'威刚',23,'男','湖北',4500);
INSERT INTO stu VALUES (45,'李永',23,'男','北京',1500);
INSERT INTO stu VALUES (46,'张关雷',25,'男','辽宁',2500);
commit;
INSERT INTO salgrade VALUES (1,7000,12000);
INSERT INTO salgrade VALUES (2,12010,14000);
INSERT INTO salgrade VALUES (3,14010,20000);
INSERT INTO salgrade VALUES (4,20010,30000);
INSERT INTO salgrade VALUES (5,30010,99990);
commit;
INSERT INTO dept VALUES (10,'教研部','北京');
INSERT INTO dept VALUES (20,'学工部','上海');
INSERT INTO dept VALUES (30,'销售部','广州');
INSERT INTO dept VALUES (40,'财务部','武汉');
commit;
INSERT INTO `stu` VALUES ('47','送小强','22','男','北京','3500');
INSERT INTO `stu` VALUES ('48','关动林','25','男','北京','1500');
INSERT INTO `stu` VALUES ('49','苏小哑','23','女','北京','1000');
INSERT INTO `stu` VALUES ('50','赵宁','22','女','山东','2500');
INSERT INTO `stu` VALUES ('51','陈丽','21','女','北京','1600');
INSERT INTO `stu` VALUES ('52','钱小刚','23','男','北京','3500');
INSERT INTO `stu` VALUES ('53','艾林','23','女','广州','2500');
INSERT INTO `stu` VALUES ('54','郭林','18','男','山西','3500');
INSERT INTO `stu` VALUES ('55','周制强','23','男','湖北','4500');
declare
cursor c1 is select * from emp;
emprow emp%rowtype;
begin
open c1;
loop
fetch c1 into emprow;
exit when c1%notfound;
dbms_output.put_line(emprow.ename);
end loop;
close c1;
end;
set serveroutput on
declare
cursor c2(eno emp.deptno%TYPE)
is select empno from emp where deptno=eno;
en emp.empno%TYPE;
begin
open c2(10);
loop
fetch c2 into en;
exit when c2%notfound;
update emp set sal=sal+100 where empno=en;
commit;
end loop;
close c2;
end;
select * from emp where deptno=10;
create or replace procedure p1(eno emp.empno%type)
is
begin
update emp set sal=sal+100 where empno=eno;
commit;
end;
set serveroutput on
select * from emp;
declare
begin
p1(1001);
end;
----存储过程和存储函数的参数都不能待长度
----存储函数的返回值类型不能带长度
create or replace function f_yearsal(eno emp.empno%TYPE) return number
is
s number(10);
begin
select sal*12+nvl(comm,0) into s from emp where empno=eno;
return s;
end;
declare
s number(10);
begin
s := f_yearsal(1001);
dbms_output.put_line(s);
end;
create or replace procedure p_yearsal(eno emp.empno%TYPE, yearsal out number)
is
s number(10);
c emp.comm%TYPE;
begin
select sal*12,nvl(comm,0) into s,c from emp where empno=eno;
yearsal := s+c;
end;
set serveroutput on
declare
yearsal number(10);
begin
p_yearsal(1002,yearsal);
dbms_output.put_line(yearsal);
end;
select * from emp;
show user;
drop table salgrade;
create table dept as select * from sys.emp;
select e.ename, d.dname
from emp e, dept d
where e.deptno=d.deptno;