oracle 静态动态,动态与静态SQL的效率问题-CSDN博客

[php]

SELECT SEG_AMOUNT.SEGMENT1,

SEG_AMOUNT.DESCRIPTION,

FFV.DESCRIPTION FLEX_DESCRIPTION,

(FFV.DESCRIPTION || FFVV.DESCRIPTION) FLEX_VALUE_MEANING,

SEG_AMOUNT.ASSORT,

SEG_AMOUNT.COST,

SEG_AMOUNT.ACTUAL_COST,

(SEG_AMOUNT.COST - SEG_AMOUNT.ACTUAL_COST) COST_DIFF,

DECODE(SEG_AMOUNT.COST,

ROUND((SEG_AMOUNT.COST - SEG_AMOUNT.ACTUAL_COST) /

SEG_AMOUNT.COST * 100,

2)) COST_PER,

SEG_AMOUNT.SALE_NUMBER,

SEG_AMOUNT.QUANTITY,

(SEG_AMOUNT.SALE_NUMBER - SEG_AMOUNT.QUANTITY) QTY_DIFF,

DECODE(SEG_AMOUNT.SALE_NUMBER,

ROUND((SEG_AMOUNT.SALE_NUMBER - SEG_AMOUNT.QUANTITY) /

SEG_AMOUNT.SALE_NUMBER * 100,

2)) QTY_PER,

SEG_AMOUNT.SALE_COST,

SEG_AMOUNT.ACTUAL_MONEY,

(SEG_AMOUNT.SALE_COST - SEG_AMOUNT.ACTUAL_MONEY) MONEY_DIFF,

DECODE(SEG_AMOUNT.SALE_COST,

ROUND((SEG_AMOUNT.SALE_COST - SEG_AMOUNT.ACTUAL_MONEY) /

SEG_AMOUNT.SALE_COST * 100,

2)) MONEY_PER,

SEG_AMOUNT.UNIT_PRICE,

SEG_AMOUNT.SELLING_PRICE,

(SEG_AMOUNT.UNIT_PRICE - SEG_AMOUNT.SELLING_PRICE) UNIT_PRICE_DIFF,

DECODE(SEG_AMOUNT.UNIT_PRICE,

ROUND((SEG_AMOUNT.UNIT_PRICE - SEG_AMOUNT.SELLING_PRICE) /

SEG_AMOUNT.UNIT_PRICE * 100,

2)) UNIT_PRICE_PER,

SEG_AMOUNT.SALE_AMOUNT,

SEG_AMOUNT.SELLING_AMOUNT,

(SEG_AMOUNT.SALE_AMOUNT - SEG_AMOUNT.SELLING_AMOUNT) SALE_AMOUNT_DIFF,

DECODE(SEG_AMOUNT.SALE_AMOUNT,

ROUND((SEG_AMOUNT.SALE_AMOUNT - SEG_AMOUNT.SELLING_AMOUNT) /

SEG_AMOUNT.SALE_AMOUNT * 100,

2)) SALE_AMOUNT_PER

FROM (SELECT B.SEGMENT1,

B.DESCRIPTION,

FLV.ATTRIBUTE1 ASSORT,

TO_CHAR(TRUNC(MMT.TRANSACTION_DATE), ''MM-YYYY'') PERIOD_NAME,

SUM(DECODE(OTTA.ORDER_CATEGORY_CODE,

''RETURN'',

-ABS(MMT.PRIMARY_QUANTITY) /

MUCC.CONVERSION_RATE,

ABS(MMT.PRIMARY_QUANTITY) /

MUCC.CONVERSION_RATE)) QUANTITY,

ROUND(SUM(DECODE(OOL.SHIP_FROM_ORG_ID,

81,

(SELECT DISTINCT RCTL.UNIT_SELLING_PRICE

FROM RA_CUSTOMER_TRX_LINES_ALL RCTL

WHERE RCTL.SALES_ORDER =

TO_CHAR(OOH.ORDER_NUMBER)

AND RCTL.INVENTORY_ITEM_ID =

B.INVENTORY_ITEM_ID

AND RCTL.SALES_ORDER_LINE =

OOL.LINE_NUMBER

AND RCTL.INTERFACE_LINE_ATTRIBUTE6 = TO_CHAR(OOL.LINE_ID)

AND RCTL.ORG_ID = 81

AND RCTL.INTERFACE_LINE_CONTEXT =

''INTERCOMPANY''

AND RCTL.LINE_TYPE = ''LINE''),

MMT.ACTUAL_COST) *

(DECODE(OTTA.ORDER_CATEGORY_CODE,

''RETURN'',

-ABS(MMT.PRIMARY_QUANTITY),

ABS(MMT.PRIMARY_QUANTITY)))),

2) ACTUAL_MONEY,

ROUND(DECODE(SUM(DECODE(OTTA.ORDER_CATEGORY_CODE,

''RETURN'',

-ABS(MMT.PRIMARY_QUANTITY),

ABS(MMT.PRIMARY_QUANTITY))),

SUM(DECODE(OOL.SHIP_FROM_ORG_ID,

81,

(SELECT DISTINCT RCTL.UNIT_SELLING_PRICE

FROM RA_CUSTOMER_TRX_LINES_ALL RCTL

WHERE RCTL.SALES_ORDER =

TO_CHAR(OOH.ORDER_NUMBER)

AND RCTL.INVENTORY_ITEM_ID =

B.INVENTORY_ITEM_ID

AND RCTL.SALES_ORDER_LINE =

OOL.LINE_NUMBER

AND RCTL.INTERFACE_LINE_ATTRIBUTE6 = TO_CHAR(OOL.LINE_ID)

AND RCTL.ORG_ID = 81

AND RCTL.INTERFACE_LINE_CONTEXT =

''INTERCOMPANY''

AND RCTL.LINE_TYPE = ''LINE''),

MMT.ACTUAL_COST) *

(DECODE(OTTA.ORDER_CATEGORY_CODE,

''RETURN'',

-ABS(MMT.PRIMARY_QUANTITY),

ABS(MMT.PRIMARY_QUANTITY)))) /

SUM(DECODE(OTTA.ORDER_CATEGORY_CODE,

''RETURN'',

-ABS(MMT.PRIMARY_QUANTITY) /

MUCC.CONVERSION_RATE,

ABS(MMT.PRIMARY_QUANTITY) /

MUCC.CONVERSION_RATE))),

2) ACTUAL_COST,

SUM(OOL.UNIT_SELLING_PRICE *

(DECODE(OTTA.ORDER_CATEGORY_CODE,

''RETURN'',

-ABS(MMT.PRIMARY_QUANTITY),

ABS(MMT.PRIMARY_QUANTITY)))) SELLING_AMOUNT,

ROUND(DECODE(SUM(DECODE(OTTA.ORDER_CATEGORY_CODE,

''RETURN'',

-ABS(MMT.PRIMARY_QUANTITY),

ABS(MMT.PRIMARY_QUANTITY))),

SUM(OOL.UNIT_SELLING_PRICE *

(DECODE(OTTA.ORDER_CATEGORY_CODE,

''RETURN'',

-ABS(MMT.PRIMARY_QUANTITY),

ABS(MMT.PRIMARY_QUANTITY)))) /

SUM(DECODE(OTTA.ORDER_CATEGORY_CODE,

''RETURN'',

-ABS(MMT.PRIMARY_QUANTITY) /

MUCC.CONVERSION_RATE,

ABS(MMT.PRIMARY_QUANTITY) /

MUCC.CONVERSION_RATE))),

2) SELLING_PRICE,

0 SALE_NUMBER,

0 COST,

0 SALE_COST,

0 SALE_AMOUNT,

0 UNIT_PRICE

FROM MTL_MATERIAL_TRANSACTIONS MMT,

OE_ORDER_LINES_ALL OOL,

MTL_SYSTEM_ITEMS_B B,

OE_ORDER_HEADERS_ALL OOH,

HZ_CUST_ACCOUNTS HCA,

MTL_UOM_CLASS_CONVERSIONS MUCC,

FND_LOOKUP_VALUES FLV,

OE_TRANSACTION_TYPES_ALL OTTA

WHERE (B.SEGMENT1 >= ''' || :P_SEGMENT1_FROM || ''' OR ''' || :P_SEGMENT1_FROM || ''' IS NULL)

AND (B.SEGMENT1 <= ''' || :P_SEGMENT1_TO || ''' OR ''' || :P_SEGMENT1_TO || ''' IS NULL)

AND (SUBSTR(B.SEGMENT1, 1, ' || TO_CHAR(:P_CATE_NUM) || ') = ''' || :P_CATE || ''' OR ''' || :P_CATE || ''' IS NULL)

AND B.ORGANIZATION_ID = 83

AND OOL.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID

AND OOL.INVENTORY_ITEM_ID > 0

AND OOL.ORG_ID = ' || TO_CHAR(:P_ORG_ID) ||

' AND OOL.SCHEDULE_SHIP_DATE >= TO_DATE(''01-'' || ''' || :P_DATE_FROM || ''' || ''00:00:00'', ''DD-MM-YYYY HH24:MI:SS'')

AND OOL.SCHEDULE_SHIP_DATE <= SYSDATE

AND MMT.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID

AND MMT.ORGANIZATION_ID = OOL.SHIP_FROM_ORG_ID

AND MMT.INVENTORY_ITEM_ID > 0

AND MMT.ORGANIZATION_ID > 0

AND MMT.TRANSACTION_DATE >= TO_DATE(''01-'' || ''' || :P_DATE_FROM || ''' || ''00:00:00'', ''DD-MM-YYYY HH24:MI:SS'')

AND MMT.TRANSACTION_DATE <= TO_DATE(''' || TO_CHAR(:P_LAST_DATE , 'DD-MM-YYYY HH24:MI:SS') ||''', ''DD-MM-YYYY HH24:MI:SS'')

AND MMT.TRX_SOURCE_LINE_ID = OOL.LINE_ID

AND MMT.TRX_SOURCE_LINE_ID > 0

AND MMT.TRANSFER_SUBINVENTORY IS NULL

AND OOH.HEADER_ID = OOL.HEADER_ID

AND OOH.HEADER_ID > 0

AND OOH.ORG_ID = OOL.ORG_ID

AND OTTA.TRANSACTION_TYPE_ID = OOH.ORDER_TYPE_ID

AND OTTA.ORG_ID = OOL.ORG_ID

AND FLV.LANGUAGE = USERENV(''LANG'')

AND (NVL('''', FLV.TERRITORY_CODE) = FLV.TERRITORY_CODE OR

FLV.TERRITORY_CODE IS NULL)

AND (FLV.LOOKUP_TYPE = ''CUSTOMER CLASS'')

AND (FLV.VIEW_APPLICATION_ID = 222)

AND (FLV.SECURITY_GROUP_ID = 0)

AND HCA.CUST_ACCOUNT_ID = OOH.SOLD_TO_ORG_ID

AND HCA.CUSTOMER_CLASS_CODE = FLV.LOOKUP_CODE

AND MUCC.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID

AND MUCC.TO_UOM_CODE = ''Ïä''

AND OOL.ORDER_QUANTITY_UOM = MUCC.FROM_UOM_CODE

GROUP BY B.SEGMENT1,

B.DESCRIPTION,

FLV.ATTRIBUTE1,

TO_CHAR(TRUNC(MMT.TRANSACTION_DATE), ''MM-YYYY'')

UNION ALL

SELECT B.SEGMENT1,

B.DESCRIPTION,

TRIM(CBDA.ASSORT) ASSORT,

TRIM(CBDA.PERIOD_NAME) PERIOD_NAME,

0 QUANTITY,

0 ACTUAL_MONEY,

0 ACTUAL_COST,

0 SELLING_AMOUNT,

0 SELLING_PRICE,

CBDA.SALE_NUMBER,

CBDA.COST,

CBDA.SALE_COST,

CBDA.SALE_AMOUNT,

CBDA.UNIT_PRICE

FROM MTL_SYSTEM_ITEMS_B B,

(SELECT *

FROM CUX.CUX_BUDGET_DATA_ALL

WHERE BUDGET_NUMBER = 102

AND ORG_ID =' || TO_CHAR(:P_ORG_ID) ||

' AND PERIOD_NAME >= ''' || :P_DATE_FROM ||

''' AND PERIOD_NAME <= ''' || :P_DATE_TO || ''') CBDA

WHERE (B.SEGMENT1 >= ''' || :P_SEGMENT1_FROM || ''' OR ''' || :P_SEGMENT1_FROM || ''' IS NULL)

AND (B.SEGMENT1 <= ''' || :P_SEGMENT1_TO || ''' OR ''' || :P_SEGMENT1_TO || ''' IS NULL)

AND (SUBSTR(B.SEGMENT1, 1, ' || TO_CHAR(:P_CATE_NUM) || ') = '''|| :P_CATE || ''' OR ''' || :P_CATE || ''' IS NULL)

AND B.SEGMENT1 = CBDA.ITEM_NUMBER

AND B.ORGANIZATION_ID = 83) SEG_AMOUNT,

(SELECT FV.PARENT_FLEX_VALUE_LOW,

SUBSTR(FT.DESCRIPTION,

INSTR(FT.DESCRIPTION, ''.'', 1, 1) + 1,

LENGTH(FT.DESCRIPTION) -

INSTR(FT.DESCRIPTION, ''.'', 1, 1)) DESCRIPTION

FROM FND_FLEX_VALUES_VL FV, FND_FLEX_VALUES_TL FT

WHERE FV.PARENT_FLEX_VALUE_LOW LIKE ''5%''

AND FV.FLEX_VALUE_MEANING = ''0''

AND FT.FLEX_VALUE_MEANING = FV.PARENT_FLEX_VALUE_LOW

AND FT.LANGUAGE = USERENV(''LANG'')) FFV,

(SELECT F.FLEX_VALUE_MEANING, F.DESCRIPTION

FROM FND_FLEX_VALUES_VL F

WHERE F.FLEX_VALUE_SET_ID = 1006791) FFVV

WHERE FFV.PARENT_FLEX_VALUE_LOW = SUBSTR(SEG_AMOUNT.SEGMENT1, 1, 3)

AND FFVV.FLEX_VALUE_MEANING =

SUBSTR(SEG_AMOUNT.SEGMENT1, LENGTH(SEG_AMOUNT.SEGMENT1) - 4, 1)

GROUP BY SEG_AMOUNT.SEGMENT1,

SEG_AMOUNT.DESCRIPTION,

FFV.DESCRIPTION,

(FFV.DESCRIPTION || FFVV.DESCRIPTION),

SEG_AMOUNT.ASSORT,

SEG_AMOUNT.COST,

SEG_AMOUNT.ACTUAL_COST,

SEG_AMOUNT.SALE_NUMBER,

SEG_AMOUNT.QUANTITY,

SEG_AMOUNT.SALE_COST,

SEG_AMOUNT.ACTUAL_MONEY,

SEG_AMOUNT.UNIT_PRICE,

SEG_AMOUNT.SELLING_PRICE,

SEG_AMOUNT.SALE_AMOUNT,

SEG_AMOUNT.SELLING_AMOUNT

[/php]

oracle 静态 动态,动态与静态SQL的效率问题

oracle 静态动态,动态与静态SQL的效率问题