back.txt

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

In addition, when a function is called from within a query or DML statement, the function cannot: 

Have OUT or IN OUT parameters 

Commit or roll back the current transaction, create or roll back to a savepoint, or alter the session or the system. DDL statements implicitly commit the current transaction, so a user-defined function cannot execute any DDL statements. 

Write to the database, if the function is being called from a SELECT statement. However, a function called from a subquery in a DML statement can write to the database. 

Write to the same table that is being modified by the statement from which the function is called, if the function is called from a DML statement. 

  
 
 Except for the restriction on OUT and IN OUT parameters, Oracle enforces these restrictions not only for the function called directly from the SQL statement, but also for any functions that function calls, and on any functions called from the SQL statements executed by that function or any function it calls.  
 

DECLARE
  V_LOOP NUMBER;

       SAL
----------
       800
      1600
      1250
      2975

       SAL
----------
      1250
      2850
      2450
      3000

       SAL
----------
      5000
      1500
      1100
       950

       SAL
----------
      3000
      1300

       SAL
----------
       800
      1600
      1250
      2975

       SAL
----------
      1250
      2850
      2450
      3000

       SAL
----------
      5000
      1500
      1100
       950

       SAL
----------
      3000
      1300

========
       SAL
----------
       800
      1600
      1250
      2975

       SAL
----------
      1250
      2850
      2450
      3000

       SAL
----------
      5000
      1500
      1100
       950

       SAL
----------
      3000
      1300


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
		--在comm列中天入对应工资的25%,20%,15%,12%
		WHERE CURRENT OF c1;
		
		if length(job)<9 then
		UPDATE emp
		SET  job=job || 'a'
		--在comm列中天入对应工资的25%,20%,15%,12%
		WHERE CURRENT OF c1;
		end if;
	END LOOP;
END;