SQL 如何使用CHR（10）使用Oracle SQL开始新行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37374866/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use CHR (10) to start a new line using Oracle SQL
提问by James
I'm trying to use CHR (10) to start a new line in my oracle SQL view. Where should I put it and how should it be formatted? I've seen it used in example before I just can't get it to work.
我正在尝试使用 CHR (10) 在我的 oracle SQL 视图中开始一个新行。我应该把它放在哪里以及它应该如何格式化？在我无法让它工作之前，我已经看到它在示例中使用过。
SELECT
CUSTOMER.FIRST_NAME ||' '||
CUSTOMER.SURNAME ||' sold their '||
EXTRACT (YEAR FROM CAR.FIRST_REG_DATE) ||' '||
CAR.MAKE ||' '||
CAR.MODEL ||' with '||
CAR.CURRENT_MILEAGE ||' miles for ￡'||
CAR.PURCHASE_PRICE ||' which was then bought on '||
CAR.SOLD_DATE ||' for ￡'||
CAR.SOLD_PRICE ||' making a profit of ￡'||
(CAR.SOLD_PRICE - CAR.PURCHASE_PRICE) ||' for staff member '||
STAFF.FIRST_NAME ||' '||
STAFF.SURNAME
回答by cableload
Wherever you want the line break, use CHR(10)
无论您想要换行，都可以使用 CHR(10)
So for a string 'I want a line break. This goes on the second line'
所以对于一个字符串'我想要一个换行符。这是第二行'
You want something like this
你想要这样的东西
select 'I want a line break.' || CHR(10) || 'This goes on the second line' from ...
回答by Ajay Kumar Aadhi
create or replace FUNCTION GET_XMER_CODE (P_shipment_num IN VARCHAR2,P_ITEM_id VARCHAR2)
RETURN VARCHAR2
AS
CURSOR C_MAIN IS
select xmer_code from
mtl_material_transactions mmt,
xxinv_rca_tbl xrt
where xrt.TRANSACTION_ID=mmt.ATTRIBUTE15
--and xrt.RCA_NUMBER=mmt.ATTRIBUTE10
and mmt.ATTRIBUTE15=P_shipment_num--'RCA-31152'
and xrt.ITEM_CODE=(Select segment1 from mtl_system_items_b where inventory_item_id=P_ITEM_id and rownum=1)
and mmt.INVENTORY_ITEM_ID=P_ITEM_id;
V_XMER_DATA VARCHAR2(5000):= null;
BEGIN
FOR C_REC IN C_MAIN
LOOP
V_XMER_DATA := V_XMER_DATA|| ','|| CHR(10) ||C_REC.XMER_CODE ;
END LOOP;
RETURN ltrim(rtrim(V_XMER_DATA,'`enter code here`,'),',');
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('XMER CODE'||sqlerrm);
END;
