好像view是可以写的,什么 instead of 什么的,具体记不清了,看一下语法帮助不就行了吗。 反正我没试过。
Oracle的语法帮助在哪里能够找到?
我没看太懂你的题目,但是VIEW中可以写触发器,下面是个例子,全当参考吧--触发表 CREATE TABLE TTRIG (ID CHAR(5),VALUE NUMBER(10,2)); --触发器操纵表 CREATE TABLE TTR (ID CHAR(5),VALUE NUMBER(10,2));CREATE OR REPLACE TRIGGER tr_bf_TTRIG BEFORE INSERT OR UPDATE OR DELETE ON TTRIG FOR EACH ROW BEGIN IF INSERTING THEN INSERT INTO ttr VALUES(:new.ID,:new.VALUE); ELSIF UPDATING THEN UPDATE ttrig SET ID = :new.ID WHERE ID = :old.ID; ELSE DELETE FROM ttr WHERE ID = :old.ID; END IF; END tr_bf_TTRIG; /--INSTEAD OF触发器 --创建一个视图 CREATE OR REPLACE VIEW TTR_VIEW AS SELECT A.ID,A.VALUE VALUE_A,B.VALUE AS VALUE_B FROM TTRIG A,TTR B WHERE A.ID = B.ID;--创建INSTEAD OF触发器,注意update和delete应该使用old CREATE OR REPLACE TRIGGER delete_TTR_VIEW INSTEAD OF DELETE ON TTR_VIEW FOR EACH ROW BEGIN DELETE FROM TTRIG WHERE ID = :old.ID; DELETE FROM TTR WHERE ID = :old.ID; END delete_TTR_VIEW; /
反正我没试过。
CREATE TABLE TTRIG (ID CHAR(5),VALUE NUMBER(10,2));
--触发器操纵表
CREATE TABLE TTR (ID CHAR(5),VALUE NUMBER(10,2));CREATE OR REPLACE TRIGGER tr_bf_TTRIG
BEFORE INSERT OR UPDATE OR DELETE ON TTRIG
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO ttr VALUES(:new.ID,:new.VALUE);
ELSIF UPDATING THEN
UPDATE ttrig SET ID = :new.ID
WHERE ID = :old.ID;
ELSE
DELETE FROM ttr
WHERE ID = :old.ID;
END IF;
END tr_bf_TTRIG;
/--INSTEAD OF触发器
--创建一个视图
CREATE OR REPLACE VIEW TTR_VIEW AS
SELECT A.ID,A.VALUE VALUE_A,B.VALUE AS VALUE_B
FROM TTRIG A,TTR B
WHERE A.ID = B.ID;--创建INSTEAD OF触发器,注意update和delete应该使用old
CREATE OR REPLACE TRIGGER delete_TTR_VIEW
INSTEAD OF DELETE ON TTR_VIEW
FOR EACH ROW
BEGIN
DELETE FROM TTRIG WHERE ID = :old.ID;
DELETE FROM TTR WHERE ID = :old.ID;
END delete_TTR_VIEW;
/
1、集合操作符(union、union all、minus)
2、聚集函数(sum、avg等)
3、group by、connect by或start with子句
4、distinct操作符
5、连接
如果是不定个数的话,要做张表,然后对表操作不更好吗?为什么要用视图?