cursor_update.txt

orale培训教材包括了所有的sql说明和实例

--在一个for update的cursor中更新多个字段。
--使ename可更改
alter table emp  modify ename varchar2(100);


DECLARE
	CURSOR c1 IS SELECT empno,sal
		FROM emp
		--WHERE comm IS NULL
		FOR UPDATE OF comm;
		--要求对comm备注为空的纪录更新。

	v_comm NUMBER(10,2);
BEGIN
	FOR r1 IN c1 LOOP
		IF r1.sal <500 THEN
			v_comm :=r1.sal *0.25;
		ELSIF r1.sal <1000 THEN
	   		v_comm :=r1.sal *0.20;
		ELSIF r1.sal <3000 THEN
			v_comm :=r1.sal *0.15;
		ELSE
			v_comm :=r1.sal *0.12;
		END IF;
		/*UPDATE USE WHERE CURRENT OF CLAUSE：
		  用WHERE CURRENT OF更新emp的对应纪录*/
		UPDATE emp
		SET comm =v_comm
		,sal = sal*0.1
		,ename = ename||'a'
		--更新ename,虽然ename不在cursor中
		--在comm列中天入对应工资的25%,20%,15%,12%
		WHERE CURRENT OF c1;
	END LOOP;
END;
--
 select ename,job ,comm,sal from emp



/*------------------------------使用多次UPDATE emp----------
		SET  job=job || 'a'
		
		WHERE CURRENT OF c1;
*/
DECLARE
	CURSOR c1 IS SELECT empno,sal
		FROM emp
		FOR UPDATE OF comm;
		--要求对comm备注为空的纪录更新。

	v_comm NUMBER(10,2);
BEGIN
	FOR r1 IN c1 LOOP
		IF r1.sal <500 THEN
			v_comm :=r1.sal *0.25;
		ELSIF r1.sal <1000 THEN
	   		v_comm :=r1.sal *0.20;
		ELSIF r1.sal <3000 THEN
			v_comm :=r1.sal *0.15;
		ELSE
			v_comm :=r1.sal *0.12;
		END IF;
		/*UPDATE USE WHERE CURRENT OF CLAUSE：
		  用WHERE CURRENT OF更新emp的对应纪录*/
		UPDATE emp
		SET comm =v_comm
		--在comm列中天入对应工资的25%,20%,15%,12%
		WHERE CURRENT OF c1;
		
		if r1.sal >30 then
		UPDATE emp
		SET  ename = ename||'a'
		
		WHERE CURRENT OF c1;
		end if;
	END LOOP;
END;


select ename,job ,comm,sal from emp;
/*------------------------------使用delete------------------
		delete from emp
		
		WHERE CURRENT OF c1;
*/

DECLARE
	CURSOR c1 IS SELECT empno,sal
		FROM emp
		FOR UPDATE OF comm;
		--要求对comm备注为空的纪录更新。

	v_comm NUMBER(10,2);
BEGIN
	FOR r1 IN c1 LOOP
		IF r1.sal <500 THEN
			v_comm :=r1.sal *0.25;
		ELSIF r1.sal <1000 THEN
	   		v_comm :=r1.sal *0.20;
		ELSIF r1.sal <3000 THEN
			v_comm :=r1.sal *0.15;
		ELSE
			v_comm :=r1.sal *0.12;
		END IF;
		/*UPDATE USE WHERE CURRENT OF CLAUSE：
		  用WHERE CURRENT OF更新emp的对应纪录*/
		UPDATE emp
		SET comm =v_comm
		--在comm列中天入对应工资的25%,20%,15%,12%
		WHERE CURRENT OF c1;
		
		if r1.sal >30 then
		delete from emp
		
		WHERE CURRENT OF c1;
		end if;
	END LOOP;
END;
select ename,job ,comm,sal from emp;







---------------lock------------------------------------------------------
--插入数据
insert into emp (empno,ename,comm)
values(99,'99',99);

		update 
		
		WHERE CURRENT OF c1;
*/

DECLARE
	CURSOR c1 IS SELECT empno,sal
		FROM emp
		where empno!=99
		FOR UPDATE OF comm;
		--对99号以外的
		--comm备注为空的纪录更新。

	v_comm NUMBER(10,2);
BEGIN
	FOR r1 IN c1 LOOP
		IF r1.sal <500 THEN
			v_comm :=r1.sal *0.25;
		ELSIF r1.sal <1000 THEN
	   		v_comm :=r1.sal *0.20;
		ELSIF r1.sal <3000 THEN
			v_comm :=r1.sal *0.15;
		ELSE
			v_comm :=r1.sal *0.12;
		END IF;
		/*UPDATE USE WHERE CURRENT OF CLAUSE：
		  用WHERE CURRENT OF更新emp的对应纪录*/
		UPDATE emp
		SET comm =v_comm
		--在comm列中天入对应工资的25%,20%,15%,12%
		WHERE CURRENT OF c1;

	END LOOP;
END;


--ok

--FOR UPDATE OF comm;时在另一个sqlplus中，update不在of 句中的字段
update emp set ename='00' where empno=7369;
--locked
update emp set comm=100 where empno=7369;

--更新不在cursor对应集中的纪录
update emp set comm=100 where empno=99;
--已更新 1 行