学习笔记：12访问oracle

12-1:使用标量接收数据
	DECLARE
	  v_ename emp.ename%TYPE;
	  v_sal   emp.sal%TYPE;
	BEGIN
	  SELECT ename,sal INTO v_ename,v_sal
	  FROM emp WHERE empno=&no;
	  dbms_output.put_line('姓名:'||v_ename);
	  dbms_output.put_line('工资:'||v_sal);
	END;
	/
12-2:使用纪录变量接收数据
	DECLARE
	  TYPE emp_record_type IS RECORD (
	    name emp.ename%TYPE,title emp.job%TYPE);
	  emp_record emp_record_type;
	BEGIN
	  SELECT ename,job INTO emp_record
	  FROM emp WHERE empno=&no;
	  dbms_output.put_line('姓名:'||emp_record.name);
	  dbms_output.put_line('岗位:'||emp_record.title);
	END;
	/
12-3:no_date_found 异常
	DECLARE
	  v_ename emp.ename%TYPE;
	  v_sal   emp.sal%TYPE;
	BEGIN
	  SELECT ename,sal INTO v_ename,v_sal
	  FROM emp WHERE empno=&no;
	  dbms_output.put_line('姓名:'||v_ename);
	  dbms_output.put_line('工资:'||v_sal);
	END;
	/
12-4:too_many_rows异常
	DECLARE
	  v_ename emp.ename%TYPE;
	  v_sal   emp.sal%TYPE;
	BEGIN
	  SELECT ename,sal INTO v_ename,v_sal
	  FROM emp WHERE deptno=&no;
	  dbms_output.put_line('姓名:'||v_ename);
	  dbms_output.put_line('工资:'||v_sal);
	END;
	/
12-5:变量名不能与列名相同
	DECLARE
	  empno NUMBER(6):=7788;
	  v_ename VARCHAR2(10);
	BEGIN
	  SELECT ename INTO v_ename FROM emp
	  WHERE empno=empno;
	END;
	/
12-6:使用values子句插入数据
	DECLARE
	  v_deptno dept.deptno%TYPE;
	  v_dname  dept.dname%TYPE;
	BEGIN
	  v_deptno:=&no;
	  v_dname:='&name';
	  INSERT INTO dept (deptno,dname)
	  VALUES(v_deptno,v_dname);
	END;
	/
12-7:使用子查询插入数据
	 DECLARE
	  v_deptno emp.deptno%TYPE:=&no;
	BEGIN
	  INSERT INTO employee
	  SELECT * FROM emp WHERE deptno=v_deptno;
	END;
	/
12-8:使用表达式更新列值
	DECLARE
	  v_deptno dept.deptno%TYPE:=&no;
	  v_loc dept.loc%TYPE:='&loc';
	BEGIN
	  UPDATE dept SET loc=v_loc
	  WHERE deptno=v_deptno;
	END;
	/
12-9:使用子查询更新列值
	DECLARE
	  v_ename emp.ename%TYPE:='&name';
	BEGIN
	  UPDATE emp SET (sal,comm)=
	  (SELECT sal,comm FROM emp WHERE ename=v_ename)
	  WHERE job=(SELECT job FROM emp WHERE ename=v_ename);
	END;
	/
12-10:使用变量删除数据
	DECLARE
	  v_deptno dept.deptno%TYPE:=&no;
	BEGIN
	  DELETE FROM dept WHERE deptno=v_deptno;
	END;
	/
12-11:使用子查询删除数据
	DECLARE
	  v_ename emp.ename%TYPE:='&name';
	BEGIN
	  DELETE FROM emp
	  WHERE deptno=(SELECT deptno FROM emp
	    WHERE ename=v_ename);
	END;
	/
12-12:sql%found
	DECLARE
	  v_deptno emp.deptno%TYPE:=&no;
	BEGIN
	  UPDATE emp SET sal=sal*1.1
	  WHERE deptno=v_deptno;
	  IF SQL%FOUND THEN
	    dbms_output.put_line('删除了'||SQL%ROWCOUNT||'行');
	  ELSE
	    dbms_output.put_line('该部门不存在雇员');
	  END IF;
	END;
	/
12-13:sql%notfound
	DECLARE
	  v_deptno emp.deptno%TYPE:=&no;
	BEGIN
	  UPDATE emp SET sal=sal*1.1
	  WHERE deptno=v_deptno;
	  IF SQL%NOTFOUND THEN
	    dbms_output.put_line('该部门不存在雇员');
	  ELSE
	    dbms_output.put_line('删除了'||SQL%ROWCOUNT||'行');
	  END IF;
	END;
	/
12-14:sql%rowcount
	DECLARE
	  v_deptno emp.deptno%TYPE:=&no;
	BEGIN
	  UPDATE emp SET sal=sal*1.1
	  WHERE deptno=v_deptno;
	  dbms_output.put_line('修改了'||SQL%ROWCOUNT||'行');
	END;
	/
12-15:commit和rollback
	DECLARE
	  v_sal emp.sal%TYPE:=&salary;
	  v_ename emp.ename%TYPE:='&name';
	BEGIN
	  UPDATE emp SET sal=v_sal WHERE ename=v_ename;
	  COMMIT;
	EXCEPTION
	  WHEN OTHERS THEN
	    ROLLBACK;
	END;
	/
12-16:rollback和savepoint
	BEGIN
	  INSERT INTO temp VALUES(1);
	  SAVEPOINT a1;
	  INSERT INTO temp VALUES(2);
	  SAVEPOINT a2;
	  INSERT INTO temp VALUES(3);
	  SAVEPOINT a3;
	  ROLLBACK TO a2;
	  COMMIT;
	END;
	/