Oracle TriggersとCursorsを高速に学ぶ

-- Create the table
create table dept_log(
	operate_tag varchar2(10),
	operate_time date
);
create table dept(
	dname varchar2(20),
	dno number
);

-- Create a trigger
create or replace trigger tri_dept
before insert or update or delete
on dept
declare
	v_tag varchar2(10);
begin
	if inserting then
		v_tag:='inserting';
	elsif updating then
		v_tag:='modify';
	elsif deleting then
		v_tag:='deleting';
	end if;
	insert into dept_log values(v_tag, sysdate);
end tri_dept;
/

-- Create the department table
create table dept(
    deptno number not null,
    dname varchar(20) not null
);

-- Create the employee table
create table emp(
    emp_no number not null,
    emp_name varchar(20) not null,
    job varchar(20) not null,
    sal number not null,
    deptno number not null
);

--insert data
insert into dept values(10, 'department1');
insert into dept values(20, 'department2');
insert into emp values(1001, 'employee1', 'job1', 5000, 10);
insert into emp values(1002, 'employee2', 'job2', 7200, 10);
insert into emp values(1003, 'employee3', 'job3', 6000, 10);
insert into emp values(1004, 'employee4', 'job4', 5000, 20);
insert into emp values(1005, 'employee5', 'job5', 7000, 20);

-- Create a trigger
create or replace trigger del_dept
before delete on dept
for each row
begin
	delete from emp where deptno = :old.deptno;
end;
/end

create or replace trigger trig_insert
after insert
on account
begin
	if inserting then
		insert into myevent values(1, 'after insert');
	end if;
end;
/

-- Create the table
create table db_log(
	name varchar2(20),
	rtime timestamp
);

-- Create a trigger to log user login
create or replace trigger trigger_startup
after startup
on database
begin
	insert into db_log values('user', sysdate);
end;
/

-- Create a trigger to log the user out
create or replace trigger trigger_shutdown
before shutdown
on database
begin
	insert into db_log values('xiuyan', sysdate);
end;
/

-- Create the table
create table emp(
	empno number,
	ename varchar2(20),
	job varchar2(20),
	sal number,
	deptno number);
create table dept(
	deptno number,
	dname varchar2(20),
	loc varchar2(20));
	
-- insert data
insert into dept values(10,'account','new york');
insert into dept values(20,'salesman','chicago');
insert into dept values(30,'research','dallas');
insert into dept values(40,'operations','boston');
insert into emp values(1001,'mary','account',5000,10);
insert into emp values(2001,'smith','salesman',6000,20);
insert into emp values(3001,'kate','research',7000,30);

begin
	for emp_record in(select empno, ename, sal from emp where job='salesman')
	loop
		dbms_output.put('Employee number:'||emp_record.empno);
		dbms_output.put('; employee name: '||emp_record.ename);
		dbms_output.put_line('; Employee number: '||emp_record.sal);
	end loop;
end;
/

begin
	update emp set sal=sal*(1+0.2) where job='salesman';
	if sql%notfound then
		dbms_output.put_line('No employees need an upward salary adjustment');
	else
		dbms_output.put_line('There are'|| sql%rowcount || 'employees who need an upward pay adjustment');
	end if;
end;
/

declare
	cursor cur_emp is
	select * from emp where deptno = 30;
begin	
	for emp_record in cur_emp
	loop
		dbms_output.put('Employee number:' ||emp_record.empno);
		dbms_output.put('; employee name: '||emp_record.ename);
		dbms_output.put_line('; Employee job:'||emp_record.job);
	end loop;
end;
/

declare
	v_ename varchar2(50);
	v_job varchar2(50);
	cursor cur_emp is
	select ename, job from emp where empno = &empno;
begin
	open cur_emp;
	fetch cur_emp into v_ename, v_job;
	if cur_emp%found then
		dbms_output.put('Employee number:'||v_ename ||',job is:'||v_job );
	else
		dbms_output.put('No data record');
	end if;
end;
/

-- Create the fruit table
create table fruit(
	f_id varchar2(10) not null,
	f_name varchar2(255) not null,
	f_price number (8,2) not null
);

--insert data
insert into fruit values ('a1', 'apple',5.2);
insert into fruit values ('b1','blackberry', 10.2);
insert into fruit values ('bs1','orange', 11.2);
insert into fruit values ('bs2','melon', 8.2);
insert into fruit values ('t1','banana', 10.3);
insert into fruit values ('t2','grape', 5.3);
insert into fruit values ('o2','coconut', 9.2);

create table fruitage as select * from fruit where 2=3;
-- If the condition after WHERE is true, the data is copied along with the table when it is copied.
-- No default will copy the data.

declare 
	v_id fruit.f_id %TYPE;
	v_name fruit.f_name %TYPE;
	v_price fruit.f_price %TYPE;
	cursor frt_cur is
	select f_id, f_name, f_price from fruit where f_price>10;
begin
	open frt_cur;
	loop
		fetch frt_cur into v_id, v_name, v_price;
			if frt_cur%found then
				insert into fruitage values(v_id, v_name, v_price);
			else
				dbms_output.put_line('All data retrieved, total '||frt_cur%ROWCOUNT||'rows');
			exit;
			end if;
	end loop;
	close frt_cur;
end;
/