行列变换、PL/SQL游标加锁_plsql 游标行锁-优快云博客

本文介绍如何使用SQL进行数据的行转列及列转行操作，并演示了两种PL/SQL中的记录加锁方法。通过具体实例展示了如何在Oracle数据库中实现这些功能。

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;