sql(4)

在含有子查询的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