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);beginv_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 s244connect to scott identified by tigerusing '(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 bon (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 forcealter user [username] password expired 下次登录时提示修改密码 #查看oracle的服务IDecho $ORACLE_SID
?
