A、行列变化
1、建立测试数据:
SQL> create table t(name varchar2(10),type number,phone number);
SQL> insert into t values('duqiang',1,09176...22);
SQL> insert into t values('duqiang',2,0106....22);
SQL> insert into t values('duqiang',3,138...0069);
SQL> insert into t values('yangpei',3,138...0069);
SQL> insert into t values('yangpei',2,0106...322);
SQL> insert into t values('yangpei',1,0917..4322);
SQL> commit;
2、行变列:
SELECT NAME, MAX(decode(TYPE, 1, phone)) AS home, MAX(decode(TYPE, 2, phone)) AS office,
MAX(decode(TYPE, 3, phone)) AS mobile
FROM t
GROUP BY NAME;
1 duqiang 9176...322 ..322 1381....0069
2 yangpei 9176....322 1064...322 138.....0069
3、再变回来:
WITH t1 AS( SELECT NAME, MAX(decode(TYPE, 1, phone)) AS home, MAX(decode(TYPE, 2, phone)) AS office, MAX(decode(TYPE, 3, phone)) AS mobile FROM t GROUP BY NAME) SELECT NAME, lvl, decode(lvl, 1, home, 2, office, 3, mobile) FROM t1, (SELECT LEVEL AS lvl FROM dual CONNECT BY LEVEL <= 3) WHERE decode(lvl, 1, home, 2, office, 3, mobile) IS NOT NULL;
1 duqiang 1 9176444322
2 yangpei 1 9176444322
3 duqiang 2 106444322
4 yangpei 2 106444322
5 duqiang 3 138.....0069
6 yangpei 3 13810.....69
B、PL/SQL加锁的2种办法
declare
-- Local variables here
i integer:=100001;
open_lock sys_refcursor;
begin
select st_no into i from cjj where st_no=100001 for update; --第一种加锁方法
open open_lock for 'select * from cj a,cjj b where a.st_no=b.st_no and a.st_no=100001 for update ';----第二种加锁方法(2个表都锁)
open open_lock for 'select * from cj a,cjj b where a.st_no=b.st_no and a.st_no=100001 for update of a.st_no ';----第二种加锁方法(cj表锁)
open open_lock for 'select * from cj where st_no= :no for update ' using i;----第二种加锁方法(替代变量)
close open_lock;
end;