SQL语句PART10
oracle tips
Exist的用法：
select gw.ndocid from
(select ndocid from wf_doc_gw_shouwen union select ndocid from wf_doc_gw_fawen) gw
where
not exists (select null from wf_doc_gw_sn sn where sn.ndocid=gw.ndocid)
2。把GW表和SN表里相同的NDOCID显示出来
select gw.ndocid from
(select ndocid from wf_doc_gw_shouwen union select ndocid from wf_doc_gw_fawen) gw
where
exists (select null from wf_doc_gw_sn sn where sn.ndocid=gw.ndocid)
DECODE的语法：DECODE(value,if1,then1,if2,then2,if3,then3,...,else)，
e.g.:
select decode(max(documentid), null, 1, (max(documentid)+1))) from document;
results:
if null, display 1
else display max(documentid)+1
1. export database
cmd
%oracle_home%\bin\exp carmot/carmot@132.159.178.236_igrp2 file=d:\a.dmp
2. oracle enterprise console
connect as sysdba
安全性，右键，建立用户carmot_hunan_1,密码用carmot
给dba权限。
3. import database
cmd
%oracle_home%\bin\imp carmot_hunan_1/carmot@132.159.178.236_igrp2 file=d:\a.dmp fromuser=carmot
** remarks:
carmot_hunan_1/carmot@132.159.178.236_igrp2
用户名/密码@配置在TOAD中的数据库名称
例如: TOAD中数据库的名称是: IGRP2_132.159.178.236, 则为: carmot_hunan_1/carmot@igrp2_132.159.178.236
Example for complicated select:
select distinct first_value(ndocid) over (partition by ndocid order by lv desc) as ndocid,
--first_value(curtitle) over (partition by ndocid order by lv desc) as
curtitle,
first_value(realname) over (partition by ndocid order by lv desc) as realname
from(
select ndocid,curtitle, sys_connect_by_path(realname,' ') realname, level lv from
(select ndocid, curtitle,realname, lag(realname,1,null) over (partition by ndocid order by realname) realname_1
from (select g.ndocid,g.curtitle, u.realname
from wf_doc_gw g, tbuser u
where instr(','||g.cprocuserlist||',',','||u.userid||',')>0)) connect by prior realname=realname_1) order by ndocid
e.g.:
select g.ndocid, g.cproc
相关文档：
PL/SQL: Oracle中的SQL过程化编程语言
1. PL/SQL程序是由块结构构成，格式如下：
[DECLARE
--声明部分
]
BEGIN
-- 主体
[EXCEPTION
-- 异常处理块
]
END;
/ --块的结束
2.变量
简单变量： ......
1、触发器的概念
触发器也是一种带名的PL/SQL块。触发器类似于过程和函数，因为它们都是拥有声明、执行和异常处理过程的带名PL/SQL块。与包类似，触发器必须存储在数据库中并且不能被块进行本地化声明。
对于触发器而言，当触发事件发生的时候就会显式地执行该触发器，并且触发器不接受参数。
创建触发器的语法� ......
Oracle SQL(partI)
Data manipulation language(DML): select, insert, update, delete, merge.
Data definition language(DDL): create, alter, drop, rename, truncate, comment
Data control language(DCL): grant, revoke
Transaction control: commit, rollback, savepoint
Arithmetic Expressions:
+, -, *, / ......
Subquery: (single-row subqueries and multi-rows subqueries).
select select_list
from table
where expr operator (select select_list from table);
single-row subqueries operator: =, >, >=, <, <=, <>
e.g.:
1. select department_id, min(salary) from employees group by department_id ......
GRANT
Name
GRANT -- 定义访问权限
Synopsis GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
ON [ TABLE ] tablename [, ...]
TO { username | GROUP groupname | PUBLIC } [, ...] [ WI ......
