ORALCE 应用系统的常用经典实用SQL语句示例.doc

1、模糊查询（like *%） Select A.SALES_ACCOUNT, A.COST_OF_SALES_ACCOUNT, b.inventory_item_id from MTL_SYSTEM_ITEMS_B A where A.SEGMENT1 LIKE 12% and A.ORGANIZATION_ID = 1032、按时间查询表数据：（to_date ）select a.* from PA_EXPENDITURE_ITEMS_ALL a where a.creation_date=to_date (2011-7-27 11:14:09,YYYY-MM-DD HH24:mi:ss) 3、多条件查询表数据：（and, or）select a.* from PA_EXPENDITURE_ITEMS_ALL a where _id175 and a.creation_date=to_date (2011-7-27 11:14:09,YYYY-MM-DD HH24:mi:ss) or a.creation_date=to_date (2011-7-27 11:14:13,YYYY-MM-DD HH24:mi:ss)4、对查询记录排序：(order by) Select b.inventory_item_id,b.segment1,b.description,anization_id from mtl_system_items_b b where anization_id = 107 -组织号可根据实际选择order by b.segment1,b.description5、查表中字段重复记录情况数：（having count.group by）select count(*),a.CATEGORY_CONCAT_SEGS -只能选一个可能相重主键，如有其它字段则需为非主键 from mtl_categories_v a having count(*)1 group by a.CATEGORY_CONCAT_SEGS6、查某表中某字段重复记录： select b.inventory_item_id, b.segment1, b.description, anization_id from mtl_system_items_b b, (select a.description, anization_id from (select msb.description, anization_id, count(0) cnt from mtl_system_items_b msb group by msb.description, anization_id having count(0) 1) a) c -通过查出重复记录，重组为新表C where b.description = c.description and anization_id = anization_id and anization_id = 107 组织号可根据实际选择7、查2关联表中a中有而b中没有的记录（b.*(+) is null）select a.inventory_item_id, b.inventory_item_id, a.segment1, anization_id from mtl_system_items_b a, mtl_item_revisions_b b where a.inventory_item_id = b.inventory_item_id(+) and anization_id = anization_id(+) and b.inventory_item_id is null8、多表多条件组合查询并过滤重复数据（Select distinct）*下例实现查某职位下那些员工未定义费用账户的记录select distinct substr(hao.NAME,1,4) 公司代码, papf.employee_number 员工编码, papf.last_name 员工姓名, 组织名称, pp.NAME 职位, paaf.default_code_comb_id 费用帐户编码, gcck.concatenated_segments 费用帐户名称from per_positions pp, per_all_people_f papf, per_all_assignments_f paaf, gl_code_combinations_kfv gcck, hr_all_organization_units haowhere paaf.person_id(+) = papf.person_idAND paaf.primary_flag=Yand paaf.position_id = pp.position_id (+) and gcck.code_combination_id(+)=paaf.default_code_comb_id and papf.effective_end_datesysdate and paaf.effective_end_datesysdate and paaf.default_code_comb_id is nulland anization_id=anization_idand =44285100001.*分公司市场经营部经理.010201.管理/市场营销/营销管理order by papf.employee_number 9、更新记录和从EXECL表中插入记录（for update）select A.ROWID,a.* from mtl_categories_b a for update点击后才能解锁解锁后才能修改和插入记录-excel 文件格式可参考导出表，但第一行和ROWID行一定要置空，数据关键字不能相同，在第一行点右键复制记录，注意要从记录最后一个字段都包括-在PL/SQL中，光标需停在第二行（第一个数据行）上粘贴键才能出来，这时需将添加行点黑，光标放到数据起始处出现箭头时点击即可。需在英文输入状态下粘贴。10、更新表中多列记录（update * set *,*,*逗号隔开） UPDATE mtl_item_categories_interface a SET A.ORGANIZATION_ID = 103, A.PROCESS_FLAG = 1, A.ORGANIZATION_CODE = CB, A.PROGRAM_ID = NULL, A.REQUEST_ID = NULL, A.PROGRAM_APPLICATION_ID = NULL, A.TRANSACTION_ID = NULL11、成批替代查询字段中某字符“/”为“，”（replace）update mtl_system_items_tl a set a.description =replace(a.description,/,)12、查物料说明中第一个,并将其换成-输出(substr,instr)-查物料说明中第一个,并将其换成-输出select a.segment1, anization_id, a.description, substr(a.description, 1, INSTR(a.description, ,) - 1) | - | substr(a.description, INSTR(a.description, ,) + 1) from mtl_system_items_b a where (a.segment1 like 12% or a.segment1 like 13% or a.segment1 like 15%);13、数据表中加一空列（ALTER TABLE）ALTER TABLE MTL_SYSTEM_ITEM_B ADD NEW CHAR(80)-加一空列14、删除表中记录 -注意删前最好按15中备份 DELETE wsh_trip_stops WHERE stop_id=2372315、复制表中列形成新表 （create tableas select）create table MTL_SYSTEM_ITEM_B_new as select INVENTORY_ITEM_ID,ORGANIZATION_ID-* from MTL_SYSTEM_ITEM_B -复制表MTL_SYSTEM_ITEM_B中的几列数据，Select 列值可为*即所有字段16、建表和删表（create /drop table）create table INV.MTL_SYSTEM_ITEMS_INTERFACE( INVENTORY_ITEM_ID NUMBER, ORGANIZATION_ID NUMBER, LAST_UPDATE_DATE DATE, LAST_UPDATED_BY NUMBER, CREATION_DATE DATE, CREATED_BY NUMBER, LAST_UPDATE_LOGIN NUMBER, SUMMARY_FLAG VARCHAR2(1), ENABLED_FLAG VARCHAR2(1), START_DATE_ACTIVE DATE, END_DATE_ACTIVE DATE, DESCRIPTION VARCHAR2(240), BUYER_ID NUMBER, ACCOUNTING_RULE_ID NUMBER, INVOICING_RULE_ID NUMBER, SEGMENT1 VARCHAR2(40), SEGMENT2 VARCHAR2(40), SEGMENT3 VARCHAR2(40), BOM_ENABLED_FLAG VARCHAR2(1), BUILD_IN_WIP_FLAG VARCHAR2(1), REVISION_QTY_CONTROL_CODE NUMBER, ITEM_CATALOG_GROUP_ID NUMBER, CATALOG_STATUS_FLAG VARCHAR2(1), CHECK_SHORTAGES_FLAG VARCHAR2(1), RETURNABLE_FLAG VARCHAR2(1), DEFAULT_SHIPPING_ORG NUMBER, COLLATERAL_FLAG VARCHAR2(1), TAXABLE_FLAG VARCHAR2(1), QTY_RCV_EXCEPTION_CODE VARCHAR2(25), ALLOW_ITEM_DESC_UPDATE_FLAG VARCHAR2(1), INSPECTION_REQUIRED_FLAG VARCHAR2(1), RECEIPT_REQUIRED_FLAG VARCHAR2(1), MARKET_PRICE NUMBER, HAZARD_CLASS_ID NUMBER, RFQ_REQUIRED_FLAG VARCHAR2(1) tablespace INVD pctfree 10 pctused 70 initrans 10 maxtrans 255 storage ( initial 16K next 512K minextents 1 maxextents unlimited pctincrease 0 );- Create/Recreate indexes create index INV.MTL_SYSTEM_ITEMS_INTERFACE_N1 on INV.MTL_SYSTEM_ITEMS_INTERFACE (INVENTORY_ITEM_ID,ORGANIZATION_ID) tablespace INVX pctfree 10 initrans 11 maxtrans 255 storage ( initial 16K next 32K minextents 1 maxextents unlimited pctincrease 0 );create unique