在含有子查询的SQL语句中,要特别注意减少对表的查询.
Update 多个Column 例子:
低效:
UPDATE EMP
SET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES),
SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES)
WHERE EMP_DEPT = 0020;
高效:
UPDATE EMP
SET (EMP_CAT, SAL_RANGE)
= (SELECT MAX(CATEGORY) , MAX(SAL_RANGE) FROM EMP_CATEGORIES) WHERE EMP_DEPT = 0020;
declare
v_1 varchar2(10);
v_2 varchar2(30);
str varchar2(100);
begin
v_1 := 'bbb';
v_2 := 'aaaaaaaaaaaaa';
str := 'insert into mytest(name,address) values(:1,:2)';
execute immediate str using v_1,v_2;
commit;
exception
when others then
raise;
end;
-------------------------------------------------------------------------------------------
#DBLink用法
drop database link s244;
create database link s244
connect to scott identified by tiger
using
'(DESCRIPTION =
(
ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.69.10 )(PORT = 1521))
)
(
CONNECT_DATA = (SERVICE_NAME = lbs)
)
)';
select * from emp@S244;
-------------------------------------------------------------------------------------------
#merge语法
merge into t_a a using t_b b
on (a.msid=b.msid)
when matched then
update set a.areacode = b.areacode #不需要;
when not matched then
insert(msid,bill_month,areacode) values(b.msid,'200702',b.areacode);
-------------------------------------------------------------------------------------------
强制启动方式
当不能关闭数据库时,可以用startup force来完成数据库的关闭
startup force
alter user [username] password expired 下次登录时提示修改密码
#查看oracle的服务ID
echo $ORACLE_SID