PL/SQL中使用DML、游标、动态SQL

先申明我不是牛X，所有有错的地方，希望广大读友能提醒俺一下！

1.两个常用异常处理

DECLARE
  v_empno emp.empno%TYPE;
  v_ename emp.ename%TYPE;
BEGIN
  v_empno:=&请输入工号
  SELECT ename INTO v_ename
  FROM emp e WHERE e.empno!=v_empno;
  dbms_output.put_line(v_ename);
 
  EXCEPTION
    WHEN no_data_found THEN
      dbms_output.put_line('没有数据！');
    WHEN too_many_rows THEN
      dbms_output.put_line('行太多！'); 
END;

2.return使用，隐式游标。自动，用于返回单行结果集

  注意：经俺亲手测试，boolean不能打印输出！

DECLARE
  v_new_sal emp.sal%TYPE;
BEGIN
  UPDATE emp e SET sal=sal+1000
  WHERE e.empno=7788
  RETURN sal INTO v_new_sal;
 
  IF(v_new_sal>=8888)THEN
    ROLLBACK;
    raise_application_error(-20009,'需要请示！');
  END IF;
  COMMIT;
 
  IF(SQL%ISOPEN)THEN
    dbms_output.put_line('ok!');
  ELSE
    dbms_output.put_line('not ok!');
  END IF;     
END;

3.获取指定员工信息（是通过先做判断数据是否存在，后在查询）

DECLARE
  v_empno emp.empno%TYPE;
  v_row emp%ROWTYPE;
  v_count NUMBER(1);
BEGIN
  v_empno:=&请输入工号;
  SELECT COUNT(*) INTO v_count
  FROM emp WHERE empno=v_empno;
 
  IF(v_count=1)THEN           --先判断
    SELECT * INTO v_row FROM emp e
    WHERE e.empno=v_empno;
    dbms_output.put_line(v_row.ename||','||v_row.sal);
  ELSE
    NULL;
  END IF;
END;

4.游标for循环（重点）需要对查询中的每条记录逐一处理时

  例子：输出员工不为7566的信息

BEGIN
  FOR s IN (SELECT * FROM emp WHERE empno!=7566)
  LOOP
    dbms_output.put_line(s.empno||','||s.ename||','||s.sal);
  END LOOP;
END;

5.显示游标，使用cursor游标对象

技巧：dofw [declare-open-fetch-while] （重点）

 1）使用cursor游标对象步骤：

①定义游标      declare cursor游标对象名 is （SQL); •

②定义行变量    行变量游标对象名%ROWTYPE;

③打开游标      open游标对象名；

④循环游标      fetch 游标对象名 into 行变量；

while（游标对象名%found）

loop...end loop;

⑤关闭游标  close;

 2)示例：打印各部门信息

DECLARE
  CURSOR my_cur IS (SELECT * FROM dept);
  my_row my_cur%ROWTYPE;
BEGIN
  OPEN my_cur;
  FETCH my_cur INTO my_row;  --先获取数据后游标下移
  WHILE(my_cur%FOUND)
  LOOP
    dbms_output.put_line(my_row.deptno||','||my_row.dname);
    FETCH my_cur INTO my_row;
  END LOOP;
  CLOSE my_cur;
END;

6.在PL/SQL里执行字符串操作

  1）execute语句    execute immediate'DDL语句'/ 'select语句'into变量

示例：创建一张表t_53

DECLARE
  v_sql varchar2(4000);  --在PL/SQL操作列时，数值的长度<=4000byte
BEGIN
  v_sql:='create table t_53 (t_id number(2))';
  EXECUTE IMMEDIATE v_sql;
END;

2）执行多行查询（open..for）

①游标变量（游标变量也是变量）

②变量需要定义类型

③类型需要自定义

示例：查询工资大于3000的所有员工编号与名字

DECLARE
  TYPE my_cur_type IS REF CURSOR;                --类型需要自定义，这里的类型是引用游标
  my_cur_var my_cur_type;                                   --变量需要类型
  my_row emp%ROWTYPE;
BEGIN
  OPEN my_cur_var FOR 'select * from emp where sal>=3000';  --游标变量也是变量
  FETCH my_cur_var INTO my_row;                             --后面与显示游标相同
  WHILE(my_cur_var%FOUND)
  LOOP
    dbms_output.put_line(my_row.empno||','||my_row.ename);
    FETCH my_cur_var INTO my_row;
  END LOOP;
  CLOSE my_cur_var;
END;