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;
这一类型的转换必须借助于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 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。语句后面跟";"
2、包 包说明(package specification)，包头,存放关于包的内容的信息，定义包的用户可见的过程、 函数，数据类型和变量
create or replace package tt_aa as
v1 varchar2(10);
v2 varchar2(10);
v3 number;
v4 boolean;
procedure proc1(x number);
procedure proc2(y varchar2);
procedure proc3(z number);
function my_add(x number,y number) return number;
end; 包主体（package body)是可选的
create or replace package body tt_aa as
procedure proc1(x number) as
begin
v1:=to_char(x);
end;
procedure proc2(y varchar2) as
begin
v2:=y;
end;
procedure proc3(z number) as
begin
v1:=z;
end;
procedure proc4(x number,y number) return number as
begin
return x+y;
end;
end;
调用
begin
tt_aa.proc1(6);
dbms_output.put_line(to_char(tt_aa.my_add(1,3));
end;
3、动态sql(使用dbms_sql)
create or replace procedure my_execute(sql_string in varchar2) as
v_cursor number;
v_numrows interger;
begin
v_cursor:=dbms_sql.open_cursor;
dbms_sql.parse(v_cursor,sql_string,dbms_sql.v7);
v_numrows:=dbms_sql.execute(v_cursor);
dbms_sql.close_cursor(v_cursor);
end;
则可以
sql>exec my_execute('select * from tab');
sql>exec my_execute('insert into test value'||'('||'''ddd'''||')');
sql>exec my_execute('commit');
对于查询方面的可以如下方式： 比如想用游标查询一个表，但是这个表是分月的，每个月可能表名都会改变。
create or replace procedure proc_test as
v_curid integer;
v_result integer;
v_strSql varchar2(255);
v_userid okcai.userid%type;
v_username okcai.username%type;
begin
v_strSql := 'select * from okcai_'||to_char(sysdate,'yyyymm');
v_curid := dbms_sql.open_cursor;
dbms_sql.parse(v_curid,v_strSql,dbms_sql.v7);
dbms_sql.define_column(v_curid,1,v_userid);
dbms_sql.define_column(v_curid,2,v_username,10); --必须指定大小
v_result := dbms_sql.execute(v_curid);
loop
if dbms_sql.fetch_rows(v_curid) = 0 then
exit; --没有了，退出循环
end if;
dbms_sql.column_value(v_curid,1,v_userid);
dbms_sql.column_value(v_curid,2,v_username);
dbms_output.put_line(v_userid);
dbms_output.put_line(v_username);
end loop;
dbms_sql.close(v_curid);
end;
4、用EXECUTE IMMEDIATE
<1>. 在PL/SQL运行DDL语句
begin
execute immediate 'set role all';
end;
<2>. 给动态语句传值(USING 子句)
declare
l_depnam varchar2(20) := 'testing';
l_loc varchar2(10) := 'Dubai';
begin
execute immediate 'insert into dept values (:1, :2, :3)'
using 50, l_depnam, l_loc;
commit;
end;
<3>. 从动态语句检索值(INTO子句)
declare
l_cnt varchar2(20);
begin
execute immediate 'select count(1) from emp'
into l_cnt;
dbms_output.put_line(l_cnt);
end;
<4>. 动态调用例程.例程中用到的绑定变量参数必须指定参数类型.黓认为IN类型,其它类型必须显式指定
declare
l_routin varchar2(100) := 'gen2161.get_rowcnt';
l_tblnam varchar2(20) := 'emp';
l_cnt number;
l_status varchar2(200);
begin
execute immediate 'begin ' || l_routin || '(:2, :3, :4); end;'
using in l_tblnam, out l_cnt, in out l_status;
if l_status != 'OK' then
dbms_output.put_line('error');
end if;
end;
<5>. 将返回值传递到PL/SQL记录类型;同样也可用%rowtype变量
declare
type empdtlrec is record (empno number(4),
ename varchar2(20),
deptno number(2));
empdtl empdtlrec;
begin
execute immediate 'select empno, ename, deptno ' ||
'from emp where empno = 7934'
into empdtl;
end;
<6>. 传递并检索值.INTO子句用在USING子句前
declare
l_dept pls_integer := 20;
l_nam varchar2(20);
l_loc varchar2(20);
begin
execute immediate 'select dname, loc from dept where deptno = :1'
into l_nam, l_loc
using l_dept ;
end;
<7>. 多行查询选项.对此选项用insert语句填充临时表，用临时表进行进一步的处理,也可以用REF cursors纠正此缺憾.
declare
l_sal pls_integer := 2000;
begin
execute immediate 'insert into temp(empno, ename) ' ||
' select empno, ename from emp ' ||
' where sal > :1'
using l_sal;
commit;
end;
<8>. 完成update的returning功能
update可以用returning返回修改以后的值。比如:
UPDATE employees
SET job_id =’SA_MAN’, salary = salary + 1000, department_id = 140
WHERE last_name = ’Jones’
RETURNING salary*0.25, last_name, department_id
INTO :bnd1, :bnd2, :bnd3; 用execute immediate来完成的时候，可以用
declare
l_sal pls_integer;
begin
execute immediate 'update employees SET salary = salary + 1000 where last_name=''okcai'' RETURNING INTO :1'
returning into v_sql;
commit;
end;
5、用ref cursor来完成动态游标的功能
declare
type ct is ref cursor;
cc ct;
v_notify acc_woff_notify%rowtype;
begin
open cc for 'select * from acc_woff_notify';
loop
fetch cc into v_notify;
exit when cc%notfound;
dbms_output.put_line(v_notify.done_code);
end loop;
close cc;
end;
7.存储过程使用table类型
<1>.字符串数组
declare
type regionType is table of varchar2(3) index by binary_integer;
v_listRegion regionType;
i number(2):=0;
begin
v_listRegion(1):='571';
v_listRegion(2):='572';
v_listRegion(3):='573';
v_listRegion(4):='574';
v_listRegion(5):='575';
v_listRegion(6):='576';
v_listRegion(7):=null;
i := 1;
while i<= v_listRegion.last loop
dbms_output.put_line( v_listRegion(i) );
i := v_listRegion.next(i);
end loop;
end;
<2>.rowtype数组
declare
type CmUserType is table of cm_user%rowtype index by binary_integer;
v_listUser CmUserType;
i number(5):=0;
r_user cm_user%rowtype;
begin
i := 1;
for r_user in (select * from cm_user where rownum<=5) loop
v_listUser(i):= r_user;
i := i + 1;
end loop;
i := 1;
while i<= v_listUser.last loop
dbms_output.put_line( v_listUser(i).bill_id );
i := v_listUser.next(i);
end loop;
end;
<3>. record数组
declare
type recCmUserType is record (bill_id cm_user.bill_id%type,cust_name varchar2(25));
type CmUserType is table of recCmUserType index by binary_integer;
v_listUser CmUserType;
i number(5):=0;
r_user cm_user%rowtype;
begin
i := 1;
for r_user in (select * from cm_user where rownum<=5) loop
v_listUser(i).bill_id:= r_user.bill_id;
v_listUser(i).cust_name:= '客户'||i;
i := i + 1;
end loop;
i := 1;
while i<= v_listUser.last loop
dbms_output.put_line( v_listUser(i).bill_id );
dbms_output.put_line( v_listUser(i).cust_name );
i := v_listUser.next(i);
end loop;
end;
8、存储函数和过程
查看函数和过程的状态
SQL>select object_name,status from user_objects where object_type='FUNCTION';
SQL>select object_name,status from user_objects where object_type='PROCEDURE';
查看函数和过程的源代码
SQL>set long 1000
SQL>set pagesize 0
SQL>set trimspool on
SQL>select text from all_source where owner=user and name=upper('&plsql_name');
9、触发器
查看触发器
set long 50000;
set heading off;
set pagesize 2000;
10. 加密ORACLE的存储过程 用wrap命令,如: 下列存储过程内容放在AA.SQL文件中
create or replace procedure testCCB(i in number) as
begin
dbms_output.put_line('输入参数是'||to_char(i));
end;
SQL>wrap iname=a.sql;
PL/SQL Wrapper: Release 8.1.7.0.0 - Production on Tue Nov 27 22:26:48 2001
Copyright (c) Oracle Corporation 1993, 2000. All Rights Reserved.
Processing a.sql to a.plb 提示a.sql转换为a.plb，这就是加密了的脚本，执行a.plb即可生成加密了的存储过程 运行a.plb
SQL> @a.plb ;
11.怎么样利用游标更新数据
cursor c1 is
select * from tablename
where name is null for update [of column]
...
update tablename set column = ...
where current of c1;
