Oracle触发器查询统计本表
Jun 07, 2016 pm 05:25 PMOracle触发器查询统计本表,这次由于项目需要,实际动手演练了一把,达到了需要的效果,大致是插入数据后,统计表内和当前插入数
Oracle触发器查询统计本表
这次由于项目需要,实际动手演练了一把,达到了需要的效果,大致是插入数据后,,统计表内和当前插入数据时间相同的个数。
采用行级触发器+语句级触发器。
--包
CREATE OR REPLACE PACKAGE UPDATECASE AS
TYPE T_DATE IS TABLE OF date INDEX BY BINARY_INTEGER;
V_UPDATETIME T_DATE;
V_ROW BINARY_INTEGER := 0;
end;
--行级触发器
create or replace trigger Tri_student_row
after insert or update or delete
on student
FOR EACH ROW
BEGIN
updatecase.V_ROW := updatecase.V_ROW + 1;
if DELETING then
updatecase.V_UPDATETIME(updatecase.V_ROW) := :OLD.CREATETIME;
else
updatecase.V_UPDATETIME(updatecase.V_ROW) := :NEW.CREATETIME;
end if;
END;
--语句级触发器
create or replace trigger Tri_student
after insert or update or delete on student
declare
studentcount number(38,5);
BEGIN
FOR V_LOOP IN 1 .. UPDATECASE.V_ROW LOOP
select count(*)
into studentcount from student t
where t.createtime = UPDATECASE.V_UPDATETIME(V_LOOP);
insert into personcount(comedate,count)values(UPDATECASE.V_UPDATETIME(V_LOOP),studentcount);
end loop;
UPDATECASE.V_ROW := 0;
END;

Hot Article

Hot tools Tags

Hot Article

Hot Article Tags

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

How does Go language implement the addition, deletion, modification and query operations of the database?

Detailed tutorial on establishing a database connection using MySQLi in PHP

Comparison of similarities and differences between MySQL and PL/SQL

How does Hibernate implement polymorphic mapping?

iOS 18 adds a new 'Recovered' album function to retrieve lost or damaged photos

An in-depth analysis of how HTML reads the database

Analysis of the basic principles of MySQL database management system

Tips and practices for handling Chinese garbled characters in databases with PHP
