我的oracle笔记一（sql语句方面）

44. not in的替代。
   一般not in的效率比较低。特别是数据量大的时候，几乎不能执行。
   用下面几种方式可以替换写法
   比如要查询在fee_rev_info表中已经销户的用户（不在cm_user中的）(不过下面的例子不是很好，因为bill_id是cm_user的唯一索引）
   select * from fee_rev_info where bill_id not in (select bill_id from cm_user)  
   <1> 用not exists
   select * from fee_rev_info a where not exists (select ''''p'''' from cm_user b where b.bill_id = a.bill_id)
   <2> 用外连接(+)
 select a.* from fee_rev_info a,cm_user b
 where a.bill_id = b.bill_id (+)
 and b.bill_id is null
   <3> 用hash_aj
   select /*+HASH_AJ*/* from fee_rev_info where bill_id not in (select bill_id from cm_user)  

45.怎么样查询特殊字符，如通配符%与_
假如数据库中有表 STATIONTYPE,STATION_571 STATION_572 ...
select * from tab  where tname like ''''STATION_%''''
会显示 STATIONTYPE,STATION_571 ... 可以用下面的语句
select * from tab  where tname like ''''STATION\_%'''' escape''''\''''

46.如果存在就更新，不存在就插入可以用一个语句实现吗
9i已经支持了，是Merge，但是只支持select子查询，
如果是单条数据记录，可以写作select .... from dual的子查询。
语法为：
MERGE INTO table
USING data_source
ON (condition)
WHEN MATCHED THEN update_clause
WHEN NOT MATCHED THEN insert_clause;

如
MERGE INTO cm_user_credit 
USING (select * from dual) ON (user_id =1302514690 )
when MATCHED then update set credit_value = 1000
when NOT MATCHED then insert (user_id,acc_id,bill_id,plan_id,region_code,credit_value) values(1302514690,1305032158,''''13857141218'''',10070247,''''571'''',1000);

47.怎么实现一条记录根据条件多表插入
9i以上可以通过Insert all语句完成，仅仅是一个语句，如：
INSERT ALL
WHEN (id=1) THEN
INTO table_1 (id, name)
values(id,name)
WHEN (id=2) THEN
INTO table_2 (id, name)
values(id,name)
ELSE
INTO table_other (id, name)
values(id, name)
SELECT id,name
FROM a;

如果没有条件的话，则完成每个表的插入，如
INSERT ALL
INTO table_1 (id, name)
values(id,name)
INTO table_2 (id, name)
values(id,name)
INTO table_other (id, name)
values(id, name)
SELECT id,name
FROM a;

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
     &nb

上一页  [1] [2] [3] [4]  下一页

Copyright ＠ 2007-2012 敏韬网(敏而好学，文韬武略--MinTao.Net)（学习笔记） Inc All Rights Reserved.
闵涛 E_mail:admin@mintao.net(欢迎提供学习资源)

鄂公网安备 42011102001154号

站长：MinTao ICP备案号：鄂ICP备11006601号-18