function不能在ｓｑｌ中使用

function不能在ｓｑｌ中使用
在ｐｒｏｃｅｄｕｒｅ中定义了一个ｆｕｎｃｔｉｏｎ。
在后面的一个子程序中进行调用（ｓｅｌｅｃｔ语句中进行调用的），脚本如下，其中GET_NOW_RATE即为自己定义的ｆｕｎｃｔｉｏｎ，可是编译一直报错，“函数不能在ｓｑｌ中使用”，单独在外面建立函数就可以，ｗｈｙ？？？

PROCEDURE get_item_price (
p_item_id IN NUMBER,
p_run_rule IN VARCHAR2,
p_vendor_id OUT VARCHAR2,
p_po_number OUT VARCHAR2,
p_item_price OUT NUMBER,
p_status OUT VARCHAR2
)
IS
l_confirmed_flag BOOLEAN := FALSE;
l_confirmed_price NUMBER;
l_confirmed_vendor_id NUMBER;
l_confirmed_po_num VARCHAR2 (240);
l_unconfirmed_flag BOOLEAN := FALSE;
l_unconfirmed_price NUMBER;
l_unconfirmed_vendor_id NUMBER;
l_unconfirmed_po_num VARCHAR2 (240);
BEGIN
FOR c_blanket_price IN
(SELECT /*+ rule*/
pla.item_id, pha.vendor_id,
DECODE (pha.currency_code,
'CNY', pll.price_override,
pll.price_override * NVL(GET_NOW_RATE(pha.CURRENCY_CODE), 1) --取当前汇率
) item_price,
pha.org_id, pha.segment1 po_number
FROM po_headers pha, po_lines pla, po_line_locations pll
WHERE pha.po_header_id = pla.po_header_id
AND pha.po_header_id = pll.po_header_id
AND pla.po_line_id = pll.po_line_id
AND pha.type_lookup_code = 'BLANKET'
AND NVL (pha.closed_code, 'OPEN') = 'OPEN'
AND NVL (pha.cancel_flag, 'N') = 'N'
AND pha.approved_flag = 'Y'
--Begin, Added by Luo Lizhi, Jun 01,2006
AND NVL (pla.closed_code, 'OPEN') = 'OPEN'
AND NVL (pla.cancel_flag, 'N') = 'N'
AND NVL (pll.closed_code, 'OPEN') = 'OPEN'
AND NVL (pll.cancel_flag, 'N') = 'N'
--End, Added by Luo Lizhi, Jun 01,2006
AND TRUNC (NVL (pll.start_date, SYSDATE - 1)) <= TRUNC (SYSDATE)
AND TRUNC (NVL (pll.end_date, SYSDATE + 1)) > TRUNC (SYSDATE)
AND pla.item_id = p_item_id
AND DECODE (pha.currency_code,
'CNY', pll.price_override,
pll.price_override * NVL (GET_NOW_RATE(pha.CURRENCY_CODE), 1)--取当前汇率
) IN (
SELECT DECODE (p_run_rule,
'MAX', MAX (DECODE (pha2.currency_code,
'CNY', pll2.price_override,
pll2.price_override
* NVL (GET_NOW_RATE(pha2.CURRENCY_CODE), 1)--取当前汇率
)
),
'MIN', MIN (DECODE (pha2.currency_code,
'CNY', pll2.price_override,
pll2.price_override
* NVL (GET_NOW_RATE(pha2.CURRENCY_CODE), 1)--取当前汇率
)
)
)
FROM po_headers pha2,
po_lines pla2,
po_line_locations pll2
WHERE pha2.po_header_id = pla2.po_header_id
AND pha2.po_header_id = pll2.po_header_id
AND pla2.po_line_id = pll2.po_line_id
AND pha2.type_lookup_code = 'BLANKET'
AND NVL (pha2.closed_code, 'OPEN') = 'OPEN'
AND NVL (pha2.cancel_flag, 'N') = 'N'
AND pha2.approved_flag = 'Y'
--Begin, Added by Luo Lizhi, Jun 01,2006
AND NVL (pla2.closed_code, 'OPEN') = 'OPEN'
AND NVL (pla2.cancel_flag, 'N') = 'N'
AND NVL (pll2.closed_code, 'OPEN') = 'OPEN'
AND NVL (pll2.cancel_flag, 'N') = 'N'
--End, Added by Luo Lizhi, Jun 01,2006
AND TRUNC (NVL (pll2.start_date, SYSDATE - 1)) <=
TRUNC (SYSDATE)
AND TRUNC (NVL (pll2.end_date, SYSDATE + 1)) >
TRUNC (SYSDATE)
AND pla2.item_id = pla.item_id
AND pha2.org_id = pha.org_id))
LOOP
l_confirmed_vendor_id := c_blanket_price.vendor_id;
l_confirmed_price := c_blanket_price.item_price;
l_confirmed_po_num := c_blanket_price.po_number;
l_confirmed_flag := TRUE;
EXIT;
END LOOP;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/302844/viewspace-85957/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/302844/viewspace-85957/