SQL语句PART8
PairWise subquery:
e.g.:
select * from wf_docsort where (ndocsortid,nmoduleinfoid) in (select ndocsortid, nmoduleinfoid from wf_docsort where instr(cname,'文')>0)
the above sql is the same function as:
select * from wf_docsort where ndocsortid = (select ndocsortid from wf_docsort where instr(cname,'xx')>0) and nmoduleinfoid = (select nmoduleinfoid from wf_docsort where instr(cname,'xx')>0)
A scalar subquery expression is a subquery that: returns exactly one column value from one row. Scalar subqueries can be used in: 1) The condition and expression part of DECODE and CASE 2) All clauses of SELECT except GROUP BY 3) The SET clause and WHERE clause of an UPDATE statement
e.g.:
select * from wf_docsort
results:
1 1 收文 SHOUWEN
2 1 发文 FAWEN
3 1 交办 JIAOBAN
4 2 值班报告 REPORT
5 3 督察督办 SUPERVISAL
6 2 值班快报 DAILYREPORT
7 2 我的事情 NOTIFY
8 4 提案 RESOLUTION
9 4 建议 SUGGESTION
select NDOCSORTID, NMODULEINFOID,CNAME,KEY,(
case when nmoduleinfoid=(
select nmoduleinfoid from wf_moduleinfo where nmoduleinfoid=1
) then 'JEAN1' else (case when nmoduleinfoid=(select nmoduleinfoid from wf_moduleinfo
&n
相关文档：
1、触发器的概念
触发器也是一种带名的PL/SQL块。触发器类似于过程和函数，因为它们都是拥有声明、执行和异常处理过程的带名PL/SQL块。与包类似，触发器必须存储在数据库中并且不能被块进行本地化声明。
对于触发器而言，当触发事件发生的时候就会显式地执行该触发器，并且触发器不接受参数。
创建触发器的语法� ......
4、对象依赖性
CREATE OR REPLACE TYPE Obj1 AS OBJECT (
f1 NUMBER,
f2 VARCHAR2(10),
f3 DATE
);
/
CREATE OR REPLACE TYPE Obj2 AS OBJECT (
f1 DATE,
f2 CHAR(1)
);
/
CREATE OR REPLACE TYPE Obj3 AS OBJECT (
a Obj1,
b Obj2
);
/
OBJ3依赖于OBJ ......
Merge statement
function benefits: 1) provides the ability to conditionally update, insert or delete data into a database table. 2) performs an update if the row exists, and an insert if it is a new row. --> 1) avoids seperate updates, 2) increase performance and ease of use. 3) is useful in dat ......
CREATE VIEW MYVIEW
AS
SELECT * from bjxxdiweb_database2007.dbo.bm_tongji
UNION ALL
SELECT * from aa.DBO.chen
select * into aa..chen from bjxxdiweb_database2007.dbo.bm_tongji where 1=2
说明：数据库A的表的字段名必须和数据库B的表的字段名相同，包括数据类型等。 ......
