select os.* from blis_order o,blis_orderservice os where o.orderid=os.orderid and o.ordertypeid not in (4, 8, 10, 11) and o.status in ('New','Accepted','Pending-apPRoval','Pending-effective','Pending-correction') and snp.status='Active' and b.entityid=1
2.去掉重复(互异)的字段distinct
select distinct op.name,op. from blis_chargeactivation ca,blis_orderparameter op where op.mastertype='charge' and ca.chargeactivationid=op.masterid and ca.parentcode like '%NBR Storage Charge%'
3.某个字段不可为null
select os.orderserviceid,os.orderofferid,o.ordertypeid,o.status from Blis_Order o, Blis_Orderservice os where o.orderid=os.orderid and os.orderofferid is not null
4.删除满足某个条件的记录
delete from blis_bstoffermigplan bsf where bsf.keyid='110206'
5.取name_a字段,放入字段别名name_b
select bsf.keyid subcode,bsf.bstoffermigplanid from blis_bstoffermigplan bsf
update offermigplan.db_table_name set entityid=?,keyid=?,subioncode=?,ordertypeid=?,type=?,templatecode=?,currencycode=?,exceptioncount=?,lastexception=?,att1=?,att2=?,att3=?,att4=?,att5=?,offercode=?,status=?,createdby=?,lastmodifiedby=?,lastmodifieddate=sysdate where bstoffermigplanid=?
12.插入数据量比较大的类型clob
13.查询日期
下列两条语句功能相同
select * from blis_account acc where to_char(acc.lastmodifieddate,'YYYYMMDD')>'20050101'
select * from blis_account acc where acc.lastmodifieddate>to_date('2005-01-01','yyyy-mm-dd')
select * from blis_usageaccess where chargeactivationid in (select chargeactivationid from blis_usageaccess group by chargeactivationid having count(*) >1)
select nvl(ob.bookingvalue，0) bookingvalue from blis_order o, blis_orderbooking ob where o.orderid=ob.orderid and o.orderid =125034 and ob.bookingtypeid = 215 and ob.status = 'Active'
select * from user_col_comments ucc where ucc.column_name = 'column_name'
比如： select * from user_col_comments ucc where ucc.column_name = ‘ORDERID’ 就会查出一系列有ORDERID字段的表。
17.遍历两字段排列
select (pf.offername || ' '|| cur.name) offercode from blis_packageoffer pf,blis_currency cur where cur.status='Active' and pf.status='Active'
结果如下：
offercode
a1 b1
a1 b2
a2 b1
a2 b2
18.条件判断
case when pc.provisioningby = 'BPS' then 'True' else 'False' end
select sos.Sosorderserviceid, st.sosprovisionticketid, (case when pc.provisioningby = 'BPS' then 'True' else 'False' end) isConnector from blis_sosprovisionticket st, blis_sosorderform sof, blis_sosorderservice sos, blis_packagecomponent pc where sof.sosorderformid = sos.sosorderformid and sos.sosorderserviceid = st.sosorderserviceid and sos.status = 'Active' and st.status = 'Active' and pc.tagname(+) = st.servicetag and pc.provisioningby and sof.sosorderformid = 104789
19. pc.tagname(+) =st.servicetag
当pc.tagname存在值，st.servicetag不存在值的话，记录也可以检索出来。
20.让表可以手工编辑
select rowid,st.* from blis_sosprovisionticket st where st.sosprovisionticketid=102508
查看所有表: select * from dba_all_tables 查看所有用户: select * from all_users 查看所有DBA用户:select * from dba_users 创建role :create role BLIS_ADMIN_ROLE; 创建新用户:create user username identified by passWord 授予表空间使用权：grant resource to username 授予创建表权限：grant create table to username 授予连接数据库的权限：grant create session to username 查看所有表空间: select * from dba_tablespaces 把任何表授予某用户: grant create any table to BLIS_ADMIN_ROLE; 授予某用户检索功能:grant create any index to BLIS_ADMIN_ROLE; 授予某用户对某表有检索，插入，更新，删除功能:grant select, insert, update, delete on BLIS_ACCAGENCYCOMMISSION to BLIS_ADMIN_ROLE;
