分布式事务也可以用啊,JTA
那你就用数据库中间件, cobar
---------------------oracle--------------------------------
解锁用户--alter user boxoa account unlock
CREATE TABLESPACE FPS DATAFILE 'F:\workSoft\oracle_client\workSpace\FPS.dbf' SIZE 200M autoextend on;
drop tablespace FPS including contents 删除表空间
CREATE USER system IDENTIFIED BY CN1001 //用户
ALTER USER SYSTEM IDENTIFIED BY CN1002 //修改密码
DROP USER LS CASCADE; //删除用户
GRANT CONNECT,RESOURCE,DBA TO LS; //受权限
REVOKE CONNECT,RESOURCE,DBA from LS; //删除权限
CREATE USER yongtaioa IDENTIFIED BY yongtaioa0 ACCOUNT UNLOCK;
GRANT CONNECT TO yongtaioa WITH ADMIN OPTION; GRANT CONNECT TO yongtaioa WITH ADMIN OPTION; GRANT resource TO yongtaioa WITH ADMIN OPTION;
----------------\\\\\\\\\\\\\\\\---------
drop user boxoa cascade;
drop TABLESPACE FPS;
CREATE TABLESPACE FPS DATAFILE 'F:\workSoft\oracle_client\workSpace\FPS.dbf' SIZE 200M autoextend on;
CREATE USER boxoa
IDENTIFIED BY a123456
DEFAULT TABLESPACE FPS;
grant dba to boxoa;
grant connect to boxoa;
grant resource to boxoa;
-----------------------------------------
修改表名:alter table t_user rename to t_user2;
修改表中字段名:alter table t_user2 rename column username to username2;
添加字段:alter table t_user2 add checkowen char(164);
修改字段类型:alter table t_user2 modify usersex number;
[提前条件:]表中必须没有数据。
删除字段:alter table t_user2 drop column usersex;
select * from table_name
update table_name set user_name ='ssss' where user_id='ni000'
delete from table_name where user_id ='ni000'
添加表数据 1. insert into table_name (userid , name ) values("001","hh")
2.insert into table_name (userid,name ) select userid,name from table_name where userid='001'
(case (SELECT M.ORDERSTATUS FROM UCP_WFCHECKHIST M WHERE M.BUSIID = A.BUSIID AND M.ORDERSTATUS IN (30,40)) when 30
then '通过' when 40 then '不通过' else '' end) CHECKSTATUD
细节 1. 时间格式SQL substr(CHAR(a.close_date),1,10) = #close_date#
2. case when 的用法: CASE WHEN UE.ORGID5 IS NULL THEN 0 ELSE UE.ORGID5 END,
3. 字符连接: REASON_CODE,REASON_CODE||' '||reason_notes as reason_notes
4. 去空格: '%'|| trim('$operatorid$')||'%'
5. db2序号:ROW_NUMBER() OVER() AS ROWNUM
6.创建索引 create index "idx_supdoc_sheet" 《索引名称》 on "ds_act_supportdocupload" 《表名称》 ( "sheet_id" 《字段名称》 asc );
###############################################################################################
关键字
1、distinct 去除重复数据
2、union all 并关系
3、exists
以select * from A where exists(select * from B where A.a=B.a)为例,
exists表示,对于A中的每一个记录,如果,在表B中有记录,其属性a的值与表A这个记录的属性a的值相同,则表A的这个记录是符合条件的记录,
如果是NOT exists,则表示如果表B中没有记录能与表A这个记录连接,则表A的这个记录是符合条件的记录
连接SQL
select * from a,b where a.id=b.id
这个语法是内连接的另外一种写法,其执行结果与inner join 一样
内连接 只连接匹配的行
左外连接 包含左边表的全部行(不管右边的表中是否存在与它们匹配的行),以及右边表中全部匹配的行
右外连接 包含右边表的全部行(不管左边的表中是否存在与它们匹配的行),以及左边表中全部匹配的行
全外连接 包含左、右两个表的全部行,不管另外一边的表中是否存在与它们匹配的行。
(H)(theta)连接 使用等值以外的条件来匹配左、右两个表中的行
交叉连接 生成笛卡尔积-它不使用任何匹配或者选取条件,而是直接将一个数据源中的每个行与另一个数据源的每个行都一一匹配
在INFORMIX中连接表的查询
如果FROM子句指定了多于一个表引用,则查询会连接来自多个表的行。连接条件指定各列之间(每个表至少一列)进行连接的关系。因为正在比较连接条件中的列,所以它们必须具有一致的
数据类型。
###############################################################################################
oracle 数据库
select t.table_name,t.column_name from user_tab_columns t where t.column_name like '%CURRENTSTATE%';
// 1:更改事务状态
conn.setAutoCommit(false);
conn.commit();
conn.rollback();
ResultSetMetaData metaData = rs.getMetaData(); 获取该字段的表名,字段长度,字段类型
oracle 触发器
--------------
1.。。。。。。
create or replace trigger process_instance_trigger //创建触发器名称
after update on bpm_rtm_process_instance //触发条件(bpm_rtm_process_instance表操作update的时候出发)
for each row
begin
if :NEW.instance_status='ACH' then
delete from WFWORKITEM_ING1 where PROCESSINSTID=:OLD.PROCESSINSTID;
end if;
end;
2.。。。。。。。
create or replace trigger workitm_trigger
after insert or update or delete on WFWORKITEM
for each row
begin
if inserting then
insert into WFWORKITEM_ING1 (WORKITEMID, WORKITEMNAME, WORKITEMTYPE, WORKITEMDESC, CURRENTSTATE, PARTICIPANT, PARTINAME, PRIORITY, ISTIMEOUT, LIMITNUM,
LIMITNUMDESC, CREATETIME, STARTTIME, ENDTIME, FINALTIME, REMINDTIME, ACTIONURL, PROCESSINSTID, ACTIVITYINSTID, STATESLIST, TIMEOUTNUM, TIMEOUTNUMDESC, PROCESSINSTNAME,
ACTIVITYINSTNAME, PROCESSDEFID, PROCESSDEFNAME, PROCESSCHNAME, ACTIVITYDEFID, ASSISTANT, ASSISTANTNAME, BIZSTATE, ALLOWAGENT, ROOTPROCINSTID, ACTIONMASK, URLTYPE,
EXTEND1, EXTEND2, EXTEND3, EXTEND4, EXTEND5, EXTEND6, EXTEND7)
values(:NEW.WORKITEMID,:NEW.WORKITEMNAME, :NEW.WORKITEMTYPE, :NEW.WORKITEMDESC, :NEW.CURRENTSTATE, :NEW.PARTICIPANT, :NEW.PARTINAME, :NEW.PRIORITY, :NEW.ISTIMEOUT,
:NEW.LIMITNUM, :NEW.LIMITNUMDESC, :NEW.CREATETIME, :NEW.STARTTIME, :NEW.ENDTIME, :NEW.FINALTIME, :NEW.REMINDTIME, :NEW.ACTIONURL, :NEW.PROCESSINSTID,
:NEW.ACTIVITYINSTID, :NEW.STATESLIST, :NEW.TIMEOUTNUM, :NEW.TIMEOUTNUMDESC, :NEW.PROCESSINSTNAME, :NEW.ACTIVITYINSTNAME, :NEW.PROCESSDEFID, :NEW.PROCESSDEFNAME,
:NEW.PROCESSCHNAME, :NEW.ACTIVITYDEFID, :NEW.ASSISTANT, :NEW.ASSISTANTNAME, :NEW.BIZSTATE, :NEW.ALLOWAGENT, :NEW.ROOTPROCINSTID, :NEW.ACTIONMASK, :NEW.URLTYPE,
:NEW.EXTEND1, :NEW.EXTEND2, :NEW.EXTEND3, :NEW.EXTEND4, :NEW.EXTEND5, :NEW.EXTEND6, :NEW.EXTEND7);
elsif deleting then
delete from WFWORKITEM_ING1 where WORKITEMID=:OLD.WORKITEMID;
else
update WFWORKITEM_ING1 set WORKITEMNAME=:NEW.WORKITEMNAME,CURRENTSTATE=:NEW.CURRENTSTATE where WORKITEMID=:OLD.WORKITEMID;
end if;
end;
--------------
11、oracle 导入导出
imp boxoa/a123456@orcl full =y file=f:\boxoa.dmp
imp boxoa/a123456@orcl file=f:\boxoa.dmp tables=(wfworkitem,bpm_rtm_process_instance,bpm_mdl_activity)
exp boxoa/a123456@orcl file=f:\boxoa.dmp tables=(wfworkitem,bpm_rtm_process_instance,bpm_mdl_activity)
exp/imp已经很好用了,但是唯一的确定是速度太慢,如果1张表的数据有个百千万的,常常导入导出就长时间停在这个表 这,但是从Oracle 10g开始提供了称为数据
泵新的工具expdp/impdp,它为Oracle数据提供高速并行及大数据的迁移。
imp/exp可以在客户端调用,但是expdp/impdp只能在服务端,因为在使用expdp/impdp以前需要在数据库中创建一个 Directory
create directory test as 'D:\bank\20120113';
grant read, write on directory test to boxoa
然后就可以开始导入导出
expdp boxoa/123456@orcl directory=test dumpfile=user.dmp 导出用户的数据
expdp piner/piner directory=dump_test dumpfile=table.dmp tables=test1,test2 导出表数据
impdp boxoa/123456 directory=test dumpfile=oa_20120113_after.DMP 导入该用户数据
impdp piner/piner directory=dump_test dumpfile=table.dmp 导出表数据
12、decode(a.smctag,0,'缺货',1,'有贷') smctag
13、查询数据库连接池回话
select * from v$session
select a.SID,a.STATUS, b.sql_text from v$session a,v$sqlarea b where a.sql_hash_value=b.hash_value
exec p_kill_user_sid_session(807)
select a.sid,a.STATUS,b.SQL_TEXT,a.LAST_CALL_ET,b.FIRST_LOAD_TIME,b.ELAPSED_TIME,b.PARSE_CALLS
from v$session a,v$sqlarea b where a.SQL_HASH_VALUE= b.HASH_VALUE
select * from v$session
select a.sid,a.STATUS,b.SQL_TEXT from v$session a,v$sqlarea b where a.SQL_HASH_VALUE= b.HASH_VALUE
exec P_KILL_USER_SESSION(85)
14. 数据库 转码
oracle: select a.*, decode(userid,'120164','a') auserid from ac_operator a
db2 : select case userid when '120164' then 'a' else userid end as auserid from ac_operator(oracle 也可以用)
15.
从Root往树末梢递归
select * from TBL_TEST
start with id=1
connect by prior id = pid
order by pid
从末梢往树ROOT递归
select * from TBL_TEST
start with id=5
connect by prior pid = id
order by id
16、 报表
select du.s_date,du.pa ,count(q.pawncode),nvl(sum(CHARGE),0) from (
select '典当' as typename, pa.branch_name_, pa.applydate,pa.pawncode,pa.SUMCHARGE as CHARGE
from bpm_biz_pa_pawnapproval pa, wfprocessinst u
where u.processinstid = pa.processinstid_
and u.currentstate = 7
and pa.data_type_ = 'new'
union all
select '续当' as typename, cpa.branch_name_, cpa.applydate,cpa.pawncode,cpa.CONTINUECHARGE as charge
from bpm_biz_pa_conpawnapproval cpa, wfprocessinst u
where u.processinstid = cpa.processinstid_
and u.currentstate = 7
and cpa.data_type_ = 'new'
)q
right join (select da.s_date,a.pa from (
select to_date('201209','yyyymm')+(rownum-1) s_date from dual
connect by rownum<=last_day(to_date('201209','yyyymm')) - to_date('201209','yyyymm')+1
) da ,(select '典当' as pa from dual
union
select '续当' as pa from dual
union
select '赎当' as pa from dual) a ) du on du.s_date=q.applydate and du.pa=q.typename
where s_date>=to_date('2012-9-20','yyyy-mm-dd')
group by du.s_date,du.pa order by du.s_date,du.pa;
17 oracle 显示一个月的所有天数
效率高:
Sql代码
select to_date('200809','yyyymm')+(rownum-1) s_date from dual
connect by rownum<=last_day(to_date('200809','yyyymm')) - to_date('200809','yyyymm')+1
select to_date('200809','yyyymm')+(rownum-1) s_date from dual
connect by rownum<=last_day(to_date('200809','yyyymm')) - to_date('200809','yyyymm')+1
效率低:
Sql代码
select
to_date('200902','yyyymm')+(rownum-1) s_date
from all_tables
where rownum <=
(
select last_day(to_date('200902','yyyymm'))sf_date('200902','yyyymm')+1 from dual
)
18 sql语句
>>>>>>>1、 (to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') -decode(bcpa.enddate,null,bpa.enddate,bcpa.enddate) ) as timesum,
>>>>>>>2、(case when (to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') -decode(bcpa.enddate,null,bpa.enddate,bcpa.enddate) )>0 then (to_date(to_char(sysdate,'yyyy-
mm-dd'),'yyyy-mm-dd') -decode(bcpa.enddate,null,bpa.enddate,bcpa.enddate) ) else 0 end) as timesum,
19. mysql 导入 netstat -ln | grep mysql --socket=/var/lib/mysql/mysql.sock
---------------------------------------------------------
cd mysql/bin
mysql -u baomro -p baomro0
mysql>use baomro(database_name)
mysql>source d:\datafilename.sql
mysql-导出
mysqldump -h -u baomro -p -v baomro(数据库名称) > baomro.20120903.sql
mv baomro_yanshi.20130105.sql /ftp/samba/temp/
mv jiahuacmssp20130227.sql /ftp/samba/temp/baomro_data/
mysql 创建 数据库
root用户操作: CREATE DATABASE IF NOT EXISTS baomro default charset utf8 COLLATE utf8_general_ci;
insert into mysql.user(Host,User,Password) values("localhost","baomro",password("baomro0"));
flush privileges;
grant all PRIVILEGES on crcm.* to crcm@'localhost' identified by 'crcm0';
grant all PRIVILEGES on cnsdb.* to crcm@'%' identified by 'crcm0';
GRANT Select ON samp_db.member TO bill@localhost INDETIFIED BY "rock"
-------------------------------------------------------
CREATE DATABASE IF NOT EXISTS mpspider default charset utf8 COLLATE utf8_general_ci; grant all PRIVILEGES on mpspider.* to mpspider@'%' identified by
'mpspider0';grant all PRIVILEGES on mpspider.* to mpspider@'localhost' identified by 'mpspider0';
20、exists 的用法
select a.* from jc_shop_diss a where exists ( select 1 from jc_shop_product c where c.product_id=a.product_id and c.merch_key=2)
21. 在java 实体前添加 【transient】 hibernate不会对此实体做操作。
22,表的主键被其他表引用查询
-----mysql
select * from KEY_COLUMN_USAGE
where
COLUMN_NAME='xx_id';
------oracle
SELECT
a.table_name, a.column_name
FROM
user_cons_columns a,
user_constraints b
WHERE
a.constraint_name = b.constraint_name AND
b.constraint_type = 'R' AND
a.column_name = 'ID'
23、mysql 替换 语句
update questions set qtitle = replace(qtitle,'"+oldWord+"','"+newWord+"'),qtext = replace(qtext,'"+oldWord+"','"+newWord+"')
24、mysql 拼接字符
update sys_organizationalstructure b, sys_organizationalstructure as a
set a.pathorg =concat( b.pathorg,'.',a.id)
where a.parent=b.id and a.level=5
26、select * from sys_stores where sqrt(
(
((121.50817640000002-longitude)*PI()*12656*cos(((31.2991177+latitude)/2)*PI()/180)/180)
*
((121.50817640000002-longitude)*PI()*12656*cos (((31.2991177+latitude)/2)*PI()/180)/180)
)
+
(
((31.2991177-latitude)*PI()*12656/180)
*
((31.2991177-latitude)*PI()*12656/180)
)
)<2
25、数据库多行合并一行
----oracle 9i 一下
SELECT n_sec_code, TRANSLATE (LTRIM (text, '/'), '*/', '*,') researcherList
FROM (SELECT ROW_NUMBER () OVER (PARTITION BY n_sec_code ORDER BY n_sec_code,
lvl DESC) rn,
n_sec_code, text
FROM (SELECT n_sec_code, LEVEL lvl,
SYS_CONNECT_BY_PATH (c_researcher_code,'/') text
FROM (SELECT n_sec_code, c_researcher_code as c_researcher_code,
ROW_NUMBER () OVER (PARTITION BY n_sec_code ORDER BY n_sec_code,c_researcher_code) x
FROM m_researcher_stock_rel
ORDER BY n_sec_code, c_researcher_code) a
CONNECT BY n_sec_code = PRIOR n_sec_code AND x - 1 = PRIOR x))
WHERE rn = 1
ORDER BY n_sec_code;
-----oralce 10G以上
select 'create or replace view as select '|| wm_concat(column_name) || ' from dept'from user_tab_columns where table_name='DEPT'
---mysql
select id,group_concat(name) from aa group by id;
26、Oracle Group By 用法之 —— Rollup
---
elect job,deptno,sum(sal) total_sal from emp group by rollup(job,deptno);
JOB DEPTNO TOTAL_SAL
--------- --------- ---------
ANALYST 20 6000
ANALYST 6000
CLERK 10 1300
27、Oracle Group By 用法之 —— Cube
ORACLE中的KEEP()使用方法
28、 mysql 数据库读写分离
主服务器上进行的操作
授权给从数据库服务器从机192.168.10.131
mysql> GRANT REPLICATION SLAVE ON *.* to 'fa'@'192.168.199.249' identified by ‘fa123!@#’;
查询主数据库状态
Mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 | 261 | | |
+------------------+----------+--------------+------------------+
注释:
没有开启二进制日志
mysql>show variables like '%log_bin%'; 如果是off表示你没有开启
开启日志服务:
在my.ini 或my.cnf 文件添加log-bin 行 linux可能为my.cnf,其实只需要在[mysqld]项,把log-bin 前面的# 注释掉即可
从机:
执行同步SQL语句
mysql> change master to
master_host=’192.168.10.130’,
master_user=’rep1’,
master_password=’password’,
master_log_file=’mysql-bin.000005’,
master_log_pos=261;
正确执行后启动Slave同步进程
mysql> start slave;
注释:SET GLOBAL server_id=149
主从同步检查
mysql> show slave status\G
其中Slave_IO_Running 与 Slave_SQL_Running 的值都必须为YES,才表明状态正常。
那你就用数据库中间件, cobar
---------------------oracle--------------------------------
解锁用户--alter user boxoa account unlock
CREATE TABLESPACE FPS DATAFILE 'F:\workSoft\oracle_client\workSpace\FPS.dbf' SIZE 200M autoextend on;
drop tablespace FPS including contents 删除表空间
CREATE USER system IDENTIFIED BY CN1001 //用户
ALTER USER SYSTEM IDENTIFIED BY CN1002 //修改密码
DROP USER LS CASCADE; //删除用户
GRANT CONNECT,RESOURCE,DBA TO LS; //受权限
REVOKE CONNECT,RESOURCE,DBA from LS; //删除权限
CREATE USER yongtaioa IDENTIFIED BY yongtaioa0 ACCOUNT UNLOCK;
GRANT CONNECT TO yongtaioa WITH ADMIN OPTION; GRANT CONNECT TO yongtaioa WITH ADMIN OPTION; GRANT resource TO yongtaioa WITH ADMIN OPTION;
----------------\\\\\\\\\\\\\\\\---------
drop user boxoa cascade;
drop TABLESPACE FPS;
CREATE TABLESPACE FPS DATAFILE 'F:\workSoft\oracle_client\workSpace\FPS.dbf' SIZE 200M autoextend on;
CREATE USER boxoa
IDENTIFIED BY a123456
DEFAULT TABLESPACE FPS;
grant dba to boxoa;
grant connect to boxoa;
grant resource to boxoa;
-----------------------------------------
修改表名:alter table t_user rename to t_user2;
修改表中字段名:alter table t_user2 rename column username to username2;
添加字段:alter table t_user2 add checkowen char(164);
修改字段类型:alter table t_user2 modify usersex number;
[提前条件:]表中必须没有数据。
删除字段:alter table t_user2 drop column usersex;
select * from table_name
update table_name set user_name ='ssss' where user_id='ni000'
delete from table_name where user_id ='ni000'
添加表数据 1. insert into table_name (userid , name ) values("001","hh")
2.insert into table_name (userid,name ) select userid,name from table_name where userid='001'
(case (SELECT M.ORDERSTATUS FROM UCP_WFCHECKHIST M WHERE M.BUSIID = A.BUSIID AND M.ORDERSTATUS IN (30,40)) when 30
then '通过' when 40 then '不通过' else '' end) CHECKSTATUD
细节 1. 时间格式SQL substr(CHAR(a.close_date),1,10) = #close_date#
2. case when 的用法: CASE WHEN UE.ORGID5 IS NULL THEN 0 ELSE UE.ORGID5 END,
3. 字符连接: REASON_CODE,REASON_CODE||' '||reason_notes as reason_notes
4. 去空格: '%'|| trim('$operatorid$')||'%'
5. db2序号:ROW_NUMBER() OVER() AS ROWNUM
6.创建索引 create index "idx_supdoc_sheet" 《索引名称》 on "ds_act_supportdocupload" 《表名称》 ( "sheet_id" 《字段名称》 asc );
###############################################################################################
关键字
1、distinct 去除重复数据
2、union all 并关系
3、exists
以select * from A where exists(select * from B where A.a=B.a)为例,
exists表示,对于A中的每一个记录,如果,在表B中有记录,其属性a的值与表A这个记录的属性a的值相同,则表A的这个记录是符合条件的记录,
如果是NOT exists,则表示如果表B中没有记录能与表A这个记录连接,则表A的这个记录是符合条件的记录
连接SQL
select * from a,b where a.id=b.id
这个语法是内连接的另外一种写法,其执行结果与inner join 一样
内连接 只连接匹配的行
左外连接 包含左边表的全部行(不管右边的表中是否存在与它们匹配的行),以及右边表中全部匹配的行
右外连接 包含右边表的全部行(不管左边的表中是否存在与它们匹配的行),以及左边表中全部匹配的行
全外连接 包含左、右两个表的全部行,不管另外一边的表中是否存在与它们匹配的行。
(H)(theta)连接 使用等值以外的条件来匹配左、右两个表中的行
交叉连接 生成笛卡尔积-它不使用任何匹配或者选取条件,而是直接将一个数据源中的每个行与另一个数据源的每个行都一一匹配
在INFORMIX中连接表的查询
如果FROM子句指定了多于一个表引用,则查询会连接来自多个表的行。连接条件指定各列之间(每个表至少一列)进行连接的关系。因为正在比较连接条件中的列,所以它们必须具有一致的
数据类型。
###############################################################################################
oracle 数据库
select t.table_name,t.column_name from user_tab_columns t where t.column_name like '%CURRENTSTATE%';
// 1:更改事务状态
conn.setAutoCommit(false);
conn.commit();
conn.rollback();
ResultSetMetaData metaData = rs.getMetaData(); 获取该字段的表名,字段长度,字段类型
oracle 触发器
--------------
1.。。。。。。
create or replace trigger process_instance_trigger //创建触发器名称
after update on bpm_rtm_process_instance //触发条件(bpm_rtm_process_instance表操作update的时候出发)
for each row
begin
if :NEW.instance_status='ACH' then
delete from WFWORKITEM_ING1 where PROCESSINSTID=:OLD.PROCESSINSTID;
end if;
end;
2.。。。。。。。
create or replace trigger workitm_trigger
after insert or update or delete on WFWORKITEM
for each row
begin
if inserting then
insert into WFWORKITEM_ING1 (WORKITEMID, WORKITEMNAME, WORKITEMTYPE, WORKITEMDESC, CURRENTSTATE, PARTICIPANT, PARTINAME, PRIORITY, ISTIMEOUT, LIMITNUM,
LIMITNUMDESC, CREATETIME, STARTTIME, ENDTIME, FINALTIME, REMINDTIME, ACTIONURL, PROCESSINSTID, ACTIVITYINSTID, STATESLIST, TIMEOUTNUM, TIMEOUTNUMDESC, PROCESSINSTNAME,
ACTIVITYINSTNAME, PROCESSDEFID, PROCESSDEFNAME, PROCESSCHNAME, ACTIVITYDEFID, ASSISTANT, ASSISTANTNAME, BIZSTATE, ALLOWAGENT, ROOTPROCINSTID, ACTIONMASK, URLTYPE,
EXTEND1, EXTEND2, EXTEND3, EXTEND4, EXTEND5, EXTEND6, EXTEND7)
values(:NEW.WORKITEMID,:NEW.WORKITEMNAME, :NEW.WORKITEMTYPE, :NEW.WORKITEMDESC, :NEW.CURRENTSTATE, :NEW.PARTICIPANT, :NEW.PARTINAME, :NEW.PRIORITY, :NEW.ISTIMEOUT,
:NEW.LIMITNUM, :NEW.LIMITNUMDESC, :NEW.CREATETIME, :NEW.STARTTIME, :NEW.ENDTIME, :NEW.FINALTIME, :NEW.REMINDTIME, :NEW.ACTIONURL, :NEW.PROCESSINSTID,
:NEW.ACTIVITYINSTID, :NEW.STATESLIST, :NEW.TIMEOUTNUM, :NEW.TIMEOUTNUMDESC, :NEW.PROCESSINSTNAME, :NEW.ACTIVITYINSTNAME, :NEW.PROCESSDEFID, :NEW.PROCESSDEFNAME,
:NEW.PROCESSCHNAME, :NEW.ACTIVITYDEFID, :NEW.ASSISTANT, :NEW.ASSISTANTNAME, :NEW.BIZSTATE, :NEW.ALLOWAGENT, :NEW.ROOTPROCINSTID, :NEW.ACTIONMASK, :NEW.URLTYPE,
:NEW.EXTEND1, :NEW.EXTEND2, :NEW.EXTEND3, :NEW.EXTEND4, :NEW.EXTEND5, :NEW.EXTEND6, :NEW.EXTEND7);
elsif deleting then
delete from WFWORKITEM_ING1 where WORKITEMID=:OLD.WORKITEMID;
else
update WFWORKITEM_ING1 set WORKITEMNAME=:NEW.WORKITEMNAME,CURRENTSTATE=:NEW.CURRENTSTATE where WORKITEMID=:OLD.WORKITEMID;
end if;
end;
--------------
11、oracle 导入导出
imp boxoa/a123456@orcl full =y file=f:\boxoa.dmp
imp boxoa/a123456@orcl file=f:\boxoa.dmp tables=(wfworkitem,bpm_rtm_process_instance,bpm_mdl_activity)
exp boxoa/a123456@orcl file=f:\boxoa.dmp tables=(wfworkitem,bpm_rtm_process_instance,bpm_mdl_activity)
exp/imp已经很好用了,但是唯一的确定是速度太慢,如果1张表的数据有个百千万的,常常导入导出就长时间停在这个表 这,但是从Oracle 10g开始提供了称为数据
泵新的工具expdp/impdp,它为Oracle数据提供高速并行及大数据的迁移。
imp/exp可以在客户端调用,但是expdp/impdp只能在服务端,因为在使用expdp/impdp以前需要在数据库中创建一个 Directory
create directory test as 'D:\bank\20120113';
grant read, write on directory test to boxoa
然后就可以开始导入导出
expdp boxoa/123456@orcl directory=test dumpfile=user.dmp 导出用户的数据
expdp piner/piner directory=dump_test dumpfile=table.dmp tables=test1,test2 导出表数据
impdp boxoa/123456 directory=test dumpfile=oa_20120113_after.DMP 导入该用户数据
impdp piner/piner directory=dump_test dumpfile=table.dmp 导出表数据
12、decode(a.smctag,0,'缺货',1,'有贷') smctag
13、查询数据库连接池回话
select * from v$session
select a.SID,a.STATUS, b.sql_text from v$session a,v$sqlarea b where a.sql_hash_value=b.hash_value
exec p_kill_user_sid_session(807)
select a.sid,a.STATUS,b.SQL_TEXT,a.LAST_CALL_ET,b.FIRST_LOAD_TIME,b.ELAPSED_TIME,b.PARSE_CALLS
from v$session a,v$sqlarea b where a.SQL_HASH_VALUE= b.HASH_VALUE
select * from v$session
select a.sid,a.STATUS,b.SQL_TEXT from v$session a,v$sqlarea b where a.SQL_HASH_VALUE= b.HASH_VALUE
exec P_KILL_USER_SESSION(85)
14. 数据库 转码
oracle: select a.*, decode(userid,'120164','a') auserid from ac_operator a
db2 : select case userid when '120164' then 'a' else userid end as auserid from ac_operator(oracle 也可以用)
15.
从Root往树末梢递归
select * from TBL_TEST
start with id=1
connect by prior id = pid
order by pid
从末梢往树ROOT递归
select * from TBL_TEST
start with id=5
connect by prior pid = id
order by id
16、 报表
select du.s_date,du.pa ,count(q.pawncode),nvl(sum(CHARGE),0) from (
select '典当' as typename, pa.branch_name_, pa.applydate,pa.pawncode,pa.SUMCHARGE as CHARGE
from bpm_biz_pa_pawnapproval pa, wfprocessinst u
where u.processinstid = pa.processinstid_
and u.currentstate = 7
and pa.data_type_ = 'new'
union all
select '续当' as typename, cpa.branch_name_, cpa.applydate,cpa.pawncode,cpa.CONTINUECHARGE as charge
from bpm_biz_pa_conpawnapproval cpa, wfprocessinst u
where u.processinstid = cpa.processinstid_
and u.currentstate = 7
and cpa.data_type_ = 'new'
)q
right join (select da.s_date,a.pa from (
select to_date('201209','yyyymm')+(rownum-1) s_date from dual
connect by rownum<=last_day(to_date('201209','yyyymm')) - to_date('201209','yyyymm')+1
) da ,(select '典当' as pa from dual
union
select '续当' as pa from dual
union
select '赎当' as pa from dual) a ) du on du.s_date=q.applydate and du.pa=q.typename
where s_date>=to_date('2012-9-20','yyyy-mm-dd')
group by du.s_date,du.pa order by du.s_date,du.pa;
17 oracle 显示一个月的所有天数
效率高:
Sql代码
select to_date('200809','yyyymm')+(rownum-1) s_date from dual
connect by rownum<=last_day(to_date('200809','yyyymm')) - to_date('200809','yyyymm')+1
select to_date('200809','yyyymm')+(rownum-1) s_date from dual
connect by rownum<=last_day(to_date('200809','yyyymm')) - to_date('200809','yyyymm')+1
效率低:
Sql代码
select
to_date('200902','yyyymm')+(rownum-1) s_date
from all_tables
where rownum <=
(
select last_day(to_date('200902','yyyymm'))sf_date('200902','yyyymm')+1 from dual
)
18 sql语句
>>>>>>>1、 (to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') -decode(bcpa.enddate,null,bpa.enddate,bcpa.enddate) ) as timesum,
>>>>>>>2、(case when (to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') -decode(bcpa.enddate,null,bpa.enddate,bcpa.enddate) )>0 then (to_date(to_char(sysdate,'yyyy-
mm-dd'),'yyyy-mm-dd') -decode(bcpa.enddate,null,bpa.enddate,bcpa.enddate) ) else 0 end) as timesum,
19. mysql 导入 netstat -ln | grep mysql --socket=/var/lib/mysql/mysql.sock
---------------------------------------------------------
cd mysql/bin
mysql -u baomro -p baomro0
mysql>use baomro(database_name)
mysql>source d:\datafilename.sql
mysql-导出
mysqldump -h -u baomro -p -v baomro(数据库名称) > baomro.20120903.sql
mv baomro_yanshi.20130105.sql /ftp/samba/temp/
mv jiahuacmssp20130227.sql /ftp/samba/temp/baomro_data/
mysql 创建 数据库
root用户操作: CREATE DATABASE IF NOT EXISTS baomro default charset utf8 COLLATE utf8_general_ci;
insert into mysql.user(Host,User,Password) values("localhost","baomro",password("baomro0"));
flush privileges;
grant all PRIVILEGES on crcm.* to crcm@'localhost' identified by 'crcm0';
grant all PRIVILEGES on cnsdb.* to crcm@'%' identified by 'crcm0';
GRANT Select ON samp_db.member TO bill@localhost INDETIFIED BY "rock"
-------------------------------------------------------
CREATE DATABASE IF NOT EXISTS mpspider default charset utf8 COLLATE utf8_general_ci; grant all PRIVILEGES on mpspider.* to mpspider@'%' identified by
'mpspider0';grant all PRIVILEGES on mpspider.* to mpspider@'localhost' identified by 'mpspider0';
20、exists 的用法
select a.* from jc_shop_diss a where exists ( select 1 from jc_shop_product c where c.product_id=a.product_id and c.merch_key=2)
21. 在java 实体前添加 【transient】 hibernate不会对此实体做操作。
22,表的主键被其他表引用查询
-----mysql
select * from KEY_COLUMN_USAGE
where
COLUMN_NAME='xx_id';
------oracle
SELECT
a.table_name, a.column_name
FROM
user_cons_columns a,
user_constraints b
WHERE
a.constraint_name = b.constraint_name AND
b.constraint_type = 'R' AND
a.column_name = 'ID'
23、mysql 替换 语句
update questions set qtitle = replace(qtitle,'"+oldWord+"','"+newWord+"'),qtext = replace(qtext,'"+oldWord+"','"+newWord+"')
24、mysql 拼接字符
update sys_organizationalstructure b, sys_organizationalstructure as a
set a.pathorg =concat( b.pathorg,'.',a.id)
where a.parent=b.id and a.level=5
26、select * from sys_stores where sqrt(
(
((121.50817640000002-longitude)*PI()*12656*cos(((31.2991177+latitude)/2)*PI()/180)/180)
*
((121.50817640000002-longitude)*PI()*12656*cos (((31.2991177+latitude)/2)*PI()/180)/180)
)
+
(
((31.2991177-latitude)*PI()*12656/180)
*
((31.2991177-latitude)*PI()*12656/180)
)
)<2
25、数据库多行合并一行
----oracle 9i 一下
SELECT n_sec_code, TRANSLATE (LTRIM (text, '/'), '*/', '*,') researcherList
FROM (SELECT ROW_NUMBER () OVER (PARTITION BY n_sec_code ORDER BY n_sec_code,
lvl DESC) rn,
n_sec_code, text
FROM (SELECT n_sec_code, LEVEL lvl,
SYS_CONNECT_BY_PATH (c_researcher_code,'/') text
FROM (SELECT n_sec_code, c_researcher_code as c_researcher_code,
ROW_NUMBER () OVER (PARTITION BY n_sec_code ORDER BY n_sec_code,c_researcher_code) x
FROM m_researcher_stock_rel
ORDER BY n_sec_code, c_researcher_code) a
CONNECT BY n_sec_code = PRIOR n_sec_code AND x - 1 = PRIOR x))
WHERE rn = 1
ORDER BY n_sec_code;
-----oralce 10G以上
select 'create or replace view as select '|| wm_concat(column_name) || ' from dept'from user_tab_columns where table_name='DEPT'
---mysql
select id,group_concat(name) from aa group by id;
26、Oracle Group By 用法之 —— Rollup
---
elect job,deptno,sum(sal) total_sal from emp group by rollup(job,deptno);
JOB DEPTNO TOTAL_SAL
--------- --------- ---------
ANALYST 20 6000
ANALYST 6000
CLERK 10 1300
27、Oracle Group By 用法之 —— Cube
ORACLE中的KEEP()使用方法
28、 mysql 数据库读写分离
主服务器上进行的操作
授权给从数据库服务器从机192.168.10.131
mysql> GRANT REPLICATION SLAVE ON *.* to 'fa'@'192.168.199.249' identified by ‘fa123!@#’;
查询主数据库状态
Mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 | 261 | | |
+------------------+----------+--------------+------------------+
注释:
没有开启二进制日志
mysql>show variables like '%log_bin%'; 如果是off表示你没有开启
开启日志服务:
在my.ini 或my.cnf 文件添加log-bin 行 linux可能为my.cnf,其实只需要在[mysqld]项,把log-bin 前面的# 注释掉即可
从机:
执行同步SQL语句
mysql> change master to
master_host=’192.168.10.130’,
master_user=’rep1’,
master_password=’password’,
master_log_file=’mysql-bin.000005’,
master_log_pos=261;
正确执行后启动Slave同步进程
mysql> start slave;
注释:SET GLOBAL server_id=149
主从同步检查
mysql> show slave status\G
其中Slave_IO_Running 与 Slave_SQL_Running 的值都必须为YES,才表明状态正常。