⑴ 關於ORACLE中sql操作，請大家把下面語句的意思給我詳細的解釋下，謝謝
||是SQL連接符。
chr(13)char(10)是回車換行。
select table_name from user_tables;是查詢user下的所有表。
drop table table_name;是刪除表。
整句話的意思就是刪除user下的所有表。
下一句同理，刪除的是user下的所有視圖。
⑵ oracle 優化SQL問題
試試這樣
select t.acct_month,
a.area_desc,
t.acct_no,
t.acct_name acct_name_all,
case
when length(t.acct_name) > 6 then
substr(t.acct_name, 0, 6) || '...'
else
substr(t.acct_name, 0, 6)
end as acct_name,
t.cust_no,
t.cust_name cust_name_all,
else substr(t.cust_name, 0, 6) end as cust_name,
t.user_no,
t.user_name user_name_all,
case
when length(t.channel_desc) > 8 then
substr(t.channel_desc, 0, 8) || '...'
else
substr(t.channel_desc, 0, 8)
end as channel_desc,
t.user_dinner,
t.user_dinner_desc user_dinner_desc_all,
case
when length(t.user_dinner_desc) > 8 then
substr(t.user_dinner_desc, 0, 8) || '...'
else
substr(t.user_dinner_desc, 0, 8)
end as user_dinner_desc,
t.bus_grp_no,
t.bus_grp_desc bus_grp_desc_all,
case
when length(t.bus_grp_desc) > 8 then
substr(t.bus_grp_desc, 0, 8) || '...'
else
substr(t.bus_grp_desc, 0, 8)
end as bus_grp_desc,
t.link_tel_no,
t.user_address user_address_all,
else substr(t.user_address, 0, 8) end as user_address,
t.acct_income,
t.mou,
t.remark remark_all,
case
when length(t.remark) > 8 then
substr(t.remark, 0, 8) || '...'
else
substr(t.remark, 0, 8)
end as remark,
a.AREA_NO FACT_AREA_ID,
a.DESCRIPTION AREA_DESC,
a.IDX_NO SORT_ID,
a.AREA_NO AREA_ID
from dm.ODS_DETAIL_G_INCOME_USER_M t,
ODSCODE.DMCODE_AREA a
where t.AREA_NO = a.FACT_AREA_ID
and t.AREA_NO = '188'
and a.area_no = '188'
and t.acct_month = '201002'
order by ACCT_INCOME desc
⑶ 我的sql語句不走索引。（求Oracle高手幫助）
有時候不走索引更快些
select sum(decode(sign(a306-1.5),1,1))/sum(1) from a03;
這個語句執行看看.如果返回的值大於0.1 不用索引是正常的.
⑷ oracle中超難的sql，橫向統計，需要大俠們幫幫，讓所有疑問的人都能得到幫助，回答請給出例子
主要目的動態拼出如下sql
select tb.time,
max(case
when tb.id = 1 then
user1
end) as a001,
max(case
when tb.id = 2 then
user1
end) as a002,
max(case
when tb.id = 3 then
user1
end) as a003
from tb
group by tb.time;
--以下為測試部分
create table ta (id int,name varchar2(20));
create table tb (id1 int,time varchar2(20),user1 varchar2(20),id int);
insert into ta values(1,'a001');
insert into ta values(2,'a002');
insert into ta values(3,'a003');
insert into tb values(1,'2011-01-01','u001',1);
insert into tb values(2,'2011-01-01','u002',2);
insert into tb values(3,'2011-01-01','u003',3);
insert into tb values(4,'2011-01-02','u004',1);
insert into tb values(5,'2011-01-02','u001',2);
insert into tb values(6,'2011-01-02','u001',3);
create or replace procere test_p
as
sql_str varchar2(10000);
begin
for rec in ( select * from ta ) loop
sql_str := sql_str||'max(case when tb.id = '||rec.id||' then user1 end) as '|| rec.name ||',' ;
end loop;
sql_str := 'select tb.time, '||rtrim(sql_str,',')||' from tb group by tb.time';
--dbms_output.put_line(sql_str);
execute immediate sql_str;
end;
最後執行test_p 就能達到你想要的效果了。