oracle技巧

去空方法：nvl(列名,默认值)

select nvl(name,'ii') as studentname from student ;
select nvl(age,99) as studentage from student;

 

pl创建临时表空间：

create temporary tablespace test_temp tempfile 'E:/oracle/product/10.2.0/oradata/testserver/test_temp01.dbf' size 32m autoextend on next 32m maxsize 2048m extent management local;
select username,default_tablespace,temporary_tablespace from user_users；

 

 

 

客户端：

--一、建立表空间
--一般为三个
-- workflow01：固定表空间,workflow02:固定表空间,workflowtemp:临时表空间
--删除表空间
drop tablespace WORKFLOW01 including contents;
drop tablespace WROKFLOWTEMP including contents;

CREATE TABLESPACE "WORKFLOW01"
LOGGING DATAFILE 'D:\oracle\product\11.2.0\oanet\WORKFLOW01.ora' SIZE 500m autoextend on
next 32m maxsize 1024m
extent management local;
commit;


CREATE TABLESPACE "WORKFLOW02"
LOGGING
DATAFILE 'E:\oracle\oracle10g\WORKFLOW02.ora' SIZE 500m autoextend on
next 32m maxsize 2048m
extent management local;
commit;

 

---------------临时表空间

CREATE TEMPORARY TABLESPACE "WORKFLOWTEMP" TEMPFILE
'D:\oracle\product\11.2.0\oanet\WROKFLOWTEMP.ora' SIZE 50M EXTENT
MANAGEMENT LOCAL UNIFORM SIZE 1M;

 

--二、建立用户 如导向库TEST_DBWIZARD，其他类似

drop user "dbwizard" cascade;

CREATE USER "dbwizard" PROFILE "DEFAULT"
IDENTIFIED BY "11" DEFAULT TABLESPACE "WORKFLOW01"
TEMPORARY TABLESPACE "WORKFLOWTEMP"
ACCOUNT UNLOCK;

GRANT CONNECT TO "dbwizard";
-- 授予管理员角色
GRANT DBA TO "dbwizard";
-- 授予系统的权限
GRANT SELECT ANY
TABLE TO "dbwizard" WITH ADMIN OPTION;
GRANT UNLIMITED TABLESPACE TO "dbwizard";
ALTER USER "dbwizard" DEFAULT ROLE ALL;

-----------------------------------------------------------------
drop user "base" cascade;
CREATE USER "base" PROFILE "DEFAULT" IDENTIFIED BY "11" DEFAULT
TABLESPACE "WORKFLOW01" TEMPORARY TABLESPACE
"WORKFLOWTEMP" ACCOUNT UNLOCK;

GRANT CONNECT TO "base";
-- 授予管理员角色
GRANT DBA TO "base";
-- 授予系统的权限
GRANT SELECT ANY
TABLE TO "base" WITH ADMIN OPTION;
GRANT UNLIMITED TABLESPACE TO "base";
ALTER USER "base" DEFAULT ROLE ALL;
----------------------------------------------------------------------

 

 

 

public List<Map<String, Object>> getApproveMsgToMap() {
// String sql ="select * from GCACCINSPECT t";
// String sql ="select * from ENVELOPE t";//测试envelope
// String sql ="select * from LETTERCONTENT t";//测试审批
StringBuffer sql =new StringBuffer();
sql.append(" ");
sql.append("select distinct inst.approve_status as approvestatus,");
sql.append(" o.unit_name as unitname,");
sql.append(" 'GCEXMainBody_OrgToWin' as GCEXMainBodyCodeId,");
sql.append(" inst.control_seq as SpecSysProjCodeId,");
sql.append(" o.approve_item as GCProjectText,");
sql.append(" TO_CHAR(o.begin_date, 'yyyy-mm-dd hh24:mi:ss') as DateTimeOfGCAplCreate,");
sql.append(" o.win_charge_man as GCUserNameOfGCAplCreate,");
//sql.append(" o.unit_code as GXUniCode_OrganOfOfGCAplCreate,"); //承办
//sql.append(" o.unit_name as GCOrganNameOfGCAplCreate,"); ////--?//承办
sql.append(" decode(o.is_enterprise,");
sql.append(" '0',");
sql.append(" 'GscPubUserType_NaturalPerson',");
sql.append(" '1',");
sql.append(" 'GscPubUserType_JuridicalPerson',");
sql.append(" '2',");
sql.append(" 'GscPubUserType_PartyGov',");
sql.append(" 'GscPubUserType_NaturalPerson') as GCPubUserTypeCodeId,");
sql.append(" o.cust_name as GCObj_UserName,");
sql.append(" o.cust_name as GCObj_OrganName,"); ////--?
sql.append(" CASE");
sql.append(" WHEN (SELECT distinct cust_contact_person");
sql.append(" FROM lzcity_approve_control_info");
sql.append(" WHERE control_seq = o.control_seq");
sql.append(" and rownum < 2) IS NOT NULL THEN");
sql.append(" o.cust_contact_person");
sql.append(" ELSE");
sql.append(" (SELECT distinct inf.cust_contact_person");
sql.append(" FROM LZCITY_APPROVE_CUSTOMER_INFO inf");
sql.append(" WHERE inf.cust_id = o.cust_id");
sql.append(" and rownum < 2)");
sql.append(" END as GCAplDeputyName,");
sql.append(" ne.approve_name as GCApproveProjectName,");////--?
sql.append(" '1' as GCProjIsCanOpen,");
sql.append(" 'GscObjectStatus_OK' as GCObjectStatusCodeId,");
sql.append(" 'GscProjProcess_HereInGscAcc' as GCProjProcessCodeId,");
sql.append(" decode(inst.approve_status,");
sql.append(" '4',");
sql.append(" 'GscProjAccType_Accept',");
sql.append(" '8888',");
sql.append(" 'GscProjAccType_NotAccept',");
sql.append(" 'GscProjAccType_Accept') as GCProjAccTypeCodeId,");
sql.append(" o.accept_man as GCUserNameOfGCAccCreate,");
sql.append(" TO_CHAR(o.accept_date, 'yyyy-mm-dd hh24:mi:ss') as DateTimeOfGCAccCreate,");
sql.append(" '1' as GCProjMustDatumIsTxt, ");
sql.append(" ne.gzitem_code as GXUniCode_App,");//--审批目录标准编码
sql.append(" decode(o.approve_dealwith_way,'2','GscApproveUndertakeMethod_Promises','GscApproveUndertakeMethod_Instant') as GCApproveUndertakeMethodCodeId,");//--承办方式 2是承诺件 其他即办
sql.append(" decode(ne.item_type,'1','GscApproveType_GovApprove','2','GscApproveType_NotGovApprove','GscApproveType_GovApprove') as GCApproveTypeCodeId,");//--
sql.append(" o.finish_timelimit as GCApprovePromiseLimitDayNum,");
sql.append(" o.sel_rec_stuff_ids ");
sql.append(" from APPR_EXCHANGE_INST inst ");
sql.append(" left join lzcity_approve_control_info o ");
sql.append(" on inst.control_seq = o.control_seq ");
sql.append(" left join LZCITY_APPROVE_ITM_DEFINE ne"); //--APPROVE_NAME 审批事项名称
sql.append(" on ne.item_code=o.item_code"); //--
sql.append(" inner join LZCITY_APPROVE_UNIT_INFO oi ");
sql.append(" on oi.unit_code = o.unit_code ");
sql.append(" WHERE 1 = 1 ");
sql.append(" and (inst.feedback_status is null or inst.feedback_status = '0') ");
sql.append(" and (inst.approve_status = 4 or inst.approve_status = 8888) ");
return queryInfos(sql.toString(), null, conn);
}

 

 

append的另一种技巧：

StringBuffer sb = new StringBuffer();
sb.append("select t.control_seq as ID, t.cust_name SERVICETARGETNAME,")
.append("t.cust_cer_id as SERVICETARGETID,")//身份证号码
.append("t.begin_date as APPLYDATE,")//申请时间
.append("'11' as SERVICETARGETIDTYPE,")//证/件类型
.append("t.CUST_DIVISION as DIVISIONCODE,")//审核单位行政区划 lzcity_approve_control_info表中的CUST_DIVISION字段
.append("nvl(t.cust_mobile,t.cust_contact_way) as SERVICETARGETPHONE,")//手机号码
.append("'' ONLINE_ACCEPT_WINDOWID,")//审核机构窗口id
// .append("'95efe203-f14e-4484-8efd-a7a82401adcb' as SERVICESUBJECTID ")//事项编号（中智）
.append("im.code as SERVICESUBJECTID")
.append(" from lzcity_approve_control_info t,lzcity_approve_customer_info c ,")
.append(" approve_item_map im,") // 事项编号对应表
.append("(select t1.*,t2.* from appr_exchange_inst t1 left outer join ")
.append("appr_exchange_sended t2 ")
.append("on t1.seq=t2.inst_seq and t2.send_sys_id='"+ this.config.getSysId()+ "') i,lzcity_approve_unit_info u ")
.append("where t.control_seq=i.control_seq and i.exchange_type in (0) and ")
.append("(i.send_sjbbh is null or i.send_sjbbh<i.sjbbh)")
.append(" and t.unit_code = u.unit_code")
.append(" and i.sys_id is null")
.append(" and c.cust_id = t.cust_id")
.append(" and t.approve_type = im.item_code_old")
.append(" and im.sys_id = '"+this.config.getSysId()+"'")
// .append(" and t.apply_type='2' and t.apply_src=3 "); //原来为3.//番禺现场去掉apply_src=3的过虑条件
.append(" and t.apply_type='2'") //番禺现场去掉apply_src=3的过虑条件
.append(" and t.CUST_DIVISION is not null "); //番禺现场添加cust_division不为空的条件。
System.out.println("------查询语句："+sb.toString());