script_77.txt

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

---------- emp_data.txt ----------
/*
 * 范例名称：数据准备
 * 文件名称：emp_data.txt
 */

--目前使用scott中的emp,dept数据.不用重建emp




---------- implicursor.txt ----------
/*
 * 范例名称：隐式cursor(Implicit Cursor)
 * 文件名称：implicursor.txt
 */

--Implicit Cursor TEST.USE %FOUND,%ROWCOUNT,%NOTFOUND
begin

  update sm_emp 
  set name = '张飞2' , 
  salary = 99 where empid = '0000000009';
  
  --使用隐式光标，判断是否更新成功。
  if SQL%NOTFOUND then
    --如果还没有相应id的纪录，insert.
    dbms_output.put_line('sorry .没有雇员 0000000009 .insert');
    insert into sm_emp  values ('0000000009','张飞',99,null);
  end if;
  

end; 

--test
SELECT * FROM sm_emp; 
  
connect scott/tiger
 
---------- cursor_loop.txt ----------
/*
 * 范例名称：Cursor and Loop
 * 文件名称：cursor_loop.txt
 */

--test cursor for loop
DECLARE
CURSOR c_emp IS
	SELECT ename,sal
	FROM emp
	ORDER BY ename;	
	--定义游标

	v_tot_sal NUMBER (10,2);
BEGIN
	v_tot_sal :=0;

	--游标for循环,求出雇员的工资总数。
	FOR r_emp IN c_emp LOOP

		DBMS_OUTPUT.PUT_LINE ('Name:' || r_emp.ename ||'salary:' || r_emp.sal);
		v_tot_sal :=v_tot_sal +r_emp.sal;
	END LOOP;
	DBMS_OUTPUT.PUT_LINE 
	('Total salary is'||v_tot_sal);

END;

--test cursor for loop:光标不必显式打开,以上block没有cursor 的open语句。

--test: record在for loop外不可以引用
DECLARE
CURSOR c_emp IS
	SELECT ename,sal
	FROM emp
	ORDER BY ename;
	v_tot_sal NUMBER (10,2); 
BEGIN
	v_tot_sal :=0;
	FOR r_emp IN c_emp LOOP
		DBMS_OUTPUT.PUT_LINE ('Name:' || r_emp.ename ||'salary:' || r_emp.sal);
		v_tot_sal :=v_tot_sal +r_emp.sal;
	END LOOP;
	DBMS_OUTPUT.PUT_LINE 
	('last person is  ' || r_emp.ename );

END;
--err!在for以外引用record.

 
 


---------- lock.txt ----------
/*
 * 范例名称：锁
 * 文件名称：lock.txt
 */

--创建实验表1
--第一个SQL*Plus窗口：sqlplus(1)
create table a(a number);
insert into a values(1);
insert into a values(2);
commit;

--lock1:数据库设置lock的原因
--sqlplus(1)
update a set a=10 where a=2;
--没有commit;

--新打开一个SQL*Plus窗口
--（第二个sqlplus(2)）
select * from a;
--可以看到a=2的纪录
update a set a=11 where a=2;
commit;
--此时结果如何呢？sqlplus(1)还未commit，
--如果允许sqlplus(2)更新并commit则sqlplus(1)的用户会发现他刚更改的纪录不见了。
--因此数据库设置lock


--lock2：
--sqlplus(1)
select * from a for update;



--（第二个SQL*Plus（2））
update a set a=111 where a=1;
--lock住。因为a=10这条纪录在sqlplus(1) select * from a for update;锁定范围内。

-此时，系统停顿状态，等待解锁，
-只要在第一个窗口发出roll;或commit;命令，即可解除锁定状态。

--第一个SQL*Plus窗口
--只锁定部分纪录
select * from a where a =1 for update;


--（第二个SQL*Plus）
update a set a=9 where a=10;
--ok.因为a=10不在锁定结果集内。

update a set a=12 where a=1;
--另一个sqlplus 
update a set a=10;
-此时，系统停顿状态，等待解锁。因为，企图更改a =1 的纪录
-只要在第一个窗口发出roll;或commit;命令，即可解除锁定状态。
--原因是dml并发更改相同数据


--Lock3:行独占锁
--sqlplus(1)
create table a(a number);
insert into a values(1);
insert into a values(2);
commit;

update a set a=10 where a=2;
--没有commit;

--新打开一个SQL*Plus窗口
--（第二个sqlplus(2)）
select * from a;
--可以看到a=2的纪录
update a set a=11 where a=1;
commit;
--此时结果如何呢？sqlplus(1)还未commit.
update ok.因为，a=1不在sqlplus(1)更新语句作用之内。


---------- exception.txt ----------
/*
 * 范例名称：Exceptions:PL/SQL的错误处理机制
 * 文件名称：exception.txt
 */

--exception的处理机质:出现exception直接跳到exception处理段。
set serveroutput on
DECLARE
        num_a NUMBER := 6;
        num_b NUMBER;
BEGIN
        num_b := 12;
	--num_b := 0; 
        num_a := num_a / num_b;
        num_b := 7;
	--正常执行数出结果
	dbms_output.put_line(' Value of num_a ' || num_a);
        dbms_output.put_line(' Value of num_b ' || num_b);
EXCEPTION
        WHEN ZERO_DIVIDE
	--当发生.../0时，执行如下代码
	 /*......*/
THEN
              dbms_output.put_line('Trying to divide by zero');
              dbms_output.put_line(' Value of num_a ' || num_a);
              dbms_output.put_line(' Value of num_b ' || num_b);
END;


--赋值错误EXCEPTION 

DECLARE
    X NUMBER:=10;
BEGIN
    X:= 'yyyy';  --Error Here
EXCEPTION   WHEN VALUE_ERROR THEN
    DBMS_OUTPUT.PUT_LINE('EXCEPTION HANDED');
    --输出日志 sysdate,user ,frompc......
    --insert into a values('value_err EXCEPTION HANDED');
    --insert into a values(x);
    --get_name('0000000001');


END;

--------------------exception使用日志------------------
create table sm_log
(sm_user varchar2(10) not null,
 s_date date not null,
 err_msg varchar2(100) );

DECLARE
    X NUMBER:=10;
BEGIN
    X:= 'yyyy';  --Error Here
EXCEPTION   WHEN VALUE_ERROR THEN
    DBMS_OUTPUT.PUT_LINE('EXCEPTION HANDED');
    --输出日志
    insert into sm_log values
    (user,sysdate,'value_err EXCEPTION HANDED');
    
    --get_name('0000000001');
END;

select sm_user,to_char(s_date,'yyyy/mm/dd HH24:mi:ss'),err_msg from sm_log;


---------- named_exception.txt ----------
/*
 * 范例名称：预定义EXCEPTION
 * 文件名称：named_exception.txt
 */


--先建立SM_EMP:CreateTable_sm_emp.txt
DELETE FROM sm_emp WHERE name='TOM';


 SET SERVEROUTPUT ON
--named exception test
DECLARE 

		v_name VARCHAR2(10);
		n_sal  NUMBER(8,2);
	BEGIN
	
		SELECT name , salary  INTO v_name , n_sal FROM sm_emp
		WHERE name='TOM';
		DBMS_OUTPUT.PUT_LINE('TOM : SALARY :' || n_sal);
		
	EXCEPTION 
		
		--注意：当多个exception在exception段中出现，只执行一个，之后直接跳到end。
		WHEN NO_DATA_FOUND THEN
			DBMS_OUTPUT.PUT_LINE('NO EMPLOYEE NAMED TOM');
		WHEN TOO_MANY_ROWS THEN
			DBMS_OUTPUT.PUT_LINE('TOO MANY  TOM');
		WHEN OTHERS  THEN
			DBMS_OUTPUT.PUT_LINE('ERROR!');	
	END;

--insert TOM
INSERT INTO sm_emp VALUES ('001','TOM',999.99,'62543678');

--再次运行named exception test block

--insert TOM again
INSERT INTO sm_emp VALUES ('002','TOM',999.99,'62543678');

--第三次运行named exception test block


  
---------- user_defined.txt ----------
/*
 * 范例名称：自定义异常处理
 * 文件名称：user_defined.txt
 */

INSERT INTO sm_emp VALUES ('003','TOM3',999.99,NULL);
--用户定义的exception
DECLARE 
  
    CURSOR c_emp  IS SELECT * FROM sm_emp;
    loseTelno  EXCEPTION;
    
BEGIN
    
    --OPEN c_emp;
    FOR r_emp IN c_emp  LOOP 
	--当出现telno IS NULL，自定义例外。
    	IF r_emp.telno IS NULL  THEN
    		RAISE loseTelno;  --AN EMPLOYEE WITHOUT  TELNO
    	END IF;
    END LOOP;
    
    
EXCEPTION

	WHEN loseTelno  THEN
		DBMS_OUTPUT.PUT_LINE('loseTelno Error!' || r_emp.name || 'is lost');     
END;
--Error:PLS-00201: 必须说明标识符 'R_EMP.NAME':为什么？
--记住：cursor for loop 使用的record只在cursor for loop 内有效。

--改进
DECLARE 
  
    CURSOR c_emp  IS SELECT * FROM sm_emp;
    loseTelno  EXCEPTION;
    v_name varchar(10);
    
BEGIN
    
    FOR r_emp IN c_emp  LOOP 
    	IF r_emp.telno IS NULL  THEN
    		v_name := r_emp.name;
    		RAISE loseTelno;  --AN EMPLOYEE WITHOUT  TELNO
    	END IF;
    END LOOP;
    
EXCEPTION

	WHEN loseTelno  THEN
	
		DBMS_OUTPUT.PUT_LINE('loseTelno Error!' || v_name || 'is lost');     
END;








---------------------------------扩展内容-----------------------------
---------- cursor_dml.txt ----------
/*
 * 范例名称：Cursor and DML
 * 文件名称：cursor_dml.txt
 */
 select job ,comm,sal from emp;


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

	v_comm NUMBER(10,2);
BEGIN
	--用了一个光标for循环，逐条更新纪录
	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
		WHERE CURRENT OF c1;
		--在comm列中填入对应工资的25%,20%,15%,12%
		
	END LOOP;
END;

--验证
  select job ,comm,sal from emp





--nested_new.txt作为扩展内容-----------------
---------- nested_new.txt ----------
/*
 * 范例名称：Nested Blocks
 * 文件名称：nested_new.txt
 */

<<OUTER_BLOCK>>
DECLARE
	A varchar2(20);
	B NUMBER;
BEGIN
		--A, B可以在OUTER_BLOCK块中访问
		<<SUB_BLOCK>>
		DECLARE
			C NUMBER;
            		C1 VARCHAR2(40);
			B varchar2(20) :='aaa';
			--B再一次被定义 为varchar2(20)。
			
		BEGIN
			--C只可以在子块中访问
			--A可以在此块中访问
			C1 :=A;
			
			
            --c :=B;--有这句正确吗？
			--引用外层block的B
			C :=OUTER_BLOCK.B;
		END SUB_BLOCK;
	--C不可以在这里访问
END OUTER_BLOCK;