48.如何实现行列转换
<1>、固定列数的行列转换
如
student subject grade
—————————
student1 语文 80
student1 数学 70
student1 英语 60
student2 语文 90
student2 数学 80
student2 英语 100
…
转换为
语文 数学 英语
student1 80 70 60
student2 90 80 100
…
语句如下：
select student,sum(decode(subject,’语文’, grade,null)) “语文”,
sum(decode(subject,’数学’, grade,null)) “数学”,
sum(decode(subject,’英语’, grade,null)) “英语”
from table
group by student
<2>、不定列行列转换
如
c1 c2
————–
1 我
1 是
1 谁
2 知
2 道
3 不
…
转换为
1 我是谁
2 知道
3 不
这一类型的转换必须借助于PL/SQL来完成，这里给一个例子
CREATE OR REPLACE FUNCTION get_c2(tmp_c1 NUMBER)
RETURN VARCHAR2
IS
Col_c2 VARCHAR2(4000);
BEGIN
FOR cur IN (SELECT c2 FROM t WHERE c1=tmp_c1) LOOP
Col_c2 := Col_c2||cur.c2;
END LOOP;
Col_c2 := rtrim(Col_c2,1);
RETURN Col_c2;
END;
/
SQL> select distinct c1 ,get_c2(c1) cc2 from table;即可
–例子：
create table okcai_1
(
user_id varchar2(10),
user_number varchar2(10),
user_num number(8)
)
user_id user_number user_num
———————
1 123 2
1 456 5
1 789 6
2 11 2
2 22 3
2 33 4
2 44 5
2 55 6
2 66 7
2 77 8
3 1234 1
3 5678 2
方式一：
create or replace function get_col(
p_userId number,
p_col number
) return varchar
as
v_tmp varchar2(255);
begin
select user_number||chr(9)||user_num into v_tmp
from
(select user_number,user_num,rownum row_id
from okcai_1
where user_id = p_userId) a
where row_id = p_col;
return ltrim(v_tmp);
–return v_tmp;
end;
然后
select distinct user_id,get_col(user_id,1),get_col(user_id,2),get_col(user_id,3) …. from okcai_1
方式二：
create or replace function get_col(
p_userId number,
p_col number
) return varchar
as
v_tmp varchar2(255);
begin
select user_number||chr(9)||user_num into v_tmp
from
(select user_number,user_num,rownum row_id
from okcai_1
where user_id = p_userId) a
where row_id = p_col;
return ltrim(v_tmp);
–return v_tmp;
end;
select distinct user_id,get_col_new(user_id) from okcai_1;
49.怎么设置存储过程的调用者权限
普通存储过程都是所有者权限，如果想设置调用者权限，请参考如下语句
create or replace
procedure …()
AUTHID CURRENT_USER
As
begin
…
end;
50.Oracle有哪些常见关键字
详细信息可以查看v$reserved_words视图
51.怎么查看数据库参数
<1> show parameter 参数名
如通过show parameter spfile可以查看9i是否使用spfile文件
其中参数名是可以匹配的。
比如show parameter cursor ,则会显示跟cursor相关的参数
<2>
select * from v$parameter
<3>
除了这部分参数，Oracle还有大量隐含参数，可以通过如下语句查看:
SELECT NAME
,VALUE
,decode(isdefault, ‘TRUE’,’Y’,’N’) as “Default”
,decode(ISEM,’TRUE’,’Y’,’N’) as SesMod
,decode(ISYM,’IMMEDIATE’, ‘I’,
‘DEFERRED’, ‘D’,
‘FALSE’, ‘N’) as SysMod
,decode(IMOD,’MODIFIED’,’U’,
‘SYS_MODIFIED’,’S’,’N’) as Modified
,decode(IADJ,’TRUE’,’Y’,’N’) as Adjusted
,description
FROM ( –GV$SYSTEM_PARAMETER
SELECT x.inst_id as instance
,x.indx+1
,ksppinm as NAME
,ksppity
,ksppstvl as VALUE
,ksppstdf as isdefault
,decode(bitand(ksppiflg/256,1),1,’TRUE’,’FALSE’) as ISEM
,decode(bitand(ksppiflg/65536,3),
1,’IMMEDIATE’,2,’DEFERRED’,’FALSE’) as ISYM
,decode(bitand(ksppstvf,7),1,’MODIFIED’,’FALSE’) as IMOD
,decode(bitand(ksppstvf,2),2,’TRUE’,’FALSE’) as IADJ
,ksppdesc as DESCRIPTION
FROM x$ksppi x
,x$ksppsv y
WHERE x.indx = y.indx
AND substr(ksppinm,1,1) = ‘_’
AND x.inst_id = USERENV(‘Instance’)
)
ORDER BY NAME
52.怎样建立基于函数索引
8i以上版本，确保
Query_rewrite_enabled=true
Query_rewrite_integrity=trusted
Compatible=8.1.0以上
Create index indexname on table (function(field));
53.怎么样移动表或表分区
[A]移动表的语法
Alter table tablename move
[Tablespace new_name
Storage(initial 50M next 50M
pctincrease 0 pctfree 10 pctused 50 initrans 2) nologging]
移动分区的语法
alter table tablename move (partition partname)
[update global indexes]
之后之后必须重建索引
Alter index indexname rebuild
如果表有Lob段，那么正常的Alter不能移动Lob段到别的表空间，而仅仅是移动了表段，可以采用如下的方法移动Lob段
alter table tablename move
lob(lobsegname) store as (tablespace newts);
54.怎么样修改表的列名
[A]9i以上版本可以采用rname命令
ALTER TABLE UserName.TabName
RENAME COLUMN SourceColumn TO DestColumn
9i以下版本可以采用create table …… as select * from SourceTable的方式。
另外，8i以上可以支持删除列了
ALTER TABLE UserName.TabName
SET UNUSED (ColumnName) CASCADE CONSTRAINTS
ALTER TABLE UserName.TabName
DROP (ColumnName) CASCADE CONSTRAINTS
55.case的用法
在sql语句中
CASE test_value
WHEN expression1 THEN value1
[[WHEN expression2 THEN value2] […]]
[ELSE default_value]
END
比如1
SELECT last_name, job_id, salary
CASE job_id
WHEN ‘IT_PROG’ THEN 1.10*salary
WHEN ‘ST_CLERK’ THEN 1.15*salary
WHEN ‘SA_REP’ THEN 1.20*salary
ELSE salary END “REVISED_SALARY”
FROM employees
比如2
select
case
when real_charge>=20000 and real_charge<30000 then 5000
when real_charge>=30000 and real_charge<40000 then 9000
when real_charge>=40000 and real_charge<50000 then 10000
when real_charge>=50000 and real_charge<60000 then 14000
when real_charge>=60000 and real_charge<70000 then 18000
when real_charge>=70000 and real_charge<80000 then 19000
when real_charge>=80000 and real_charge<90000 then 24000
when real_charge>=90000 and real_charge<100000 then 27000
when real_charge>=100000 and real_charge<110000 then 27000
when real_charge>=110000 and real_charge<120000 then 29000
when real_charge>=120000 then 36000
else
0
end ,acc_id,user_id,real_charge from okcai_jh_charge_200505
在存储过程中
case v_strGroupClassCode
when ‘1’ then
v_nAttrNum := v_nAttrNum + 300;
v_strAttrFlag := ‘1’||substr(v_strAttrFlag,2,7);
when ‘2’ then
v_nAttrNum := v_nAttrNum + 200;
v_strAttrFlag := ‘2’||substr(v_strAttrFlag,2,7);
else
NULL;
end case;
注意的是存储过程和sql语句有的细微差别是用end case，而不是end。语句后面跟”;”