Oracle学习第六天

回顾内容：

1. 怎么创建表空间？
2. 怎么创建用户？怎么给用户权限？

 

本次学习内容讲解：

1. PL/SQL

1）、PL/SQL块的结构。

2）、一般运算符、逻辑运算符。

3）、常量和变量的声明。

4）、标识符命名规则。

5）、注释。

1. PL/SQL数据类型

1. 标量数据类型。
2. LOB数据类型。
3. 属性类型：%TYPE、%ROWTYPE。

1. PL/SQL控制语句

1）、条件控制语句：IF、CASE。

2）、循环控制语句：LOOP、WHILE、FOR。

3）、顺序控制语句：NULL。

1. 异常

1）、预定义异常。

2）、自定义异常。

1. 游标

1）、静态游标（显示和隐式）。

  1、循环游标：LOOP、FOR。

  2、显式游标属性：%FOUND、%NOTFOUND、%ROWCOUNT、%ISOPEN。

  3、使用显示游标删除或更新。

  4、NO_DATA_FOUND和%NOTFOUND区别。

2）、动态游标。

1. 存储过程

1）、子程序组成：声明部分、可执行部分、异常处理部分。

2）、创建存储过程、调用存储过程（按位置、按名称）。

3）、程序过程的参数模式：IN、OUT、IN OUT。

4）、存储过程的访问权限。

5）、删除存储过程。

6）、存储过程调试。

7）、存储过程规范。

一、for读取游标

-- Created on 2014/7/24 by ADMINISTRATOR 
declare 
  -- Local variables here
  CURSOR cursor_emp IS SELECT ename,sal FROM scott.emp ;
begin
  -- Test statements here
  
  --使用for循环
  FOR emp_row IN cursor_emp LOOP
      dbms_output.put_line(emp_row.ename || emp_row.sal || '    ' || cursor_emp%Rowcount);
  END LOOP;
  
end;

二、loop读取游标

declare 
  -- Local variables here
  i integer;
  v_ename scott.emp.ename%TYPE;
  v_sal scott.emp.sal%TYPE;
  CURSOR cursor_emp IS SELECT ename,sal FROM scott.emp;
begin
  -- Test statements here
  
  --打开游标  如果没有打开才打开
  IF NOT cursor_emp%ISOPEN THEN
      OPEN cursor_emp;
  END IF;
  
  --使用loop读取游标
  LOOP
      --读取下一行
      FETCH cursor_emp INTO v_ename,v_sal;
      
            dbms_output.put_line(v_ename || v_sal);
            
      --如果没有行  则退出
      EXIT WHEN cursor_emp%NOTFOUND;
  END LOOP;
  
  --关闭游标
  CLOSE cursor_emp;
  
end;

三、删除表的存储过程：
/**
*删除数据库表
 in_tablename  删除表的名称
 out_err_code  0删除成功   -1没有找到相关的表 -2 存储过程执行异常
 out_err_msg 返回的执行信息
*/
create or replace procedure p_del_table(
       in_tablename IN VARCHAR2,
       out_err_code OUT VARCHAR2,
       out_err_msg OUT VARCHAR2 
)
AS
 v_count NUMBER;
begin
  SELECT COUNT(1) INTO v_count FROM dual WHERE EXISTS (
         SELECT * FROM User_Tables WHERE table_name=upper(in_tablename)
  );
  
  IF v_count > 0 THEN
     out_err_code := 0; 
     out_err_msg :=  '删除成功';
     EXECUTE IMMEDIATE 'DROP TABLE ' || in_tablename;
  ELSE
     out_err_code := -1; 
     out_err_msg :=  '没有找到表';
     DBMS_OUTPUT.put_line('没有找到表');
  END IF;
  
EXCEPTION
   WHEN OTHERS THEN
       out_err_code := -2; 
       out_err_msg :=  '存储过程执行异常';
  
end p_del_table;
/

 

l四、异常：

/*
===========================================================
|         预定义异常
============================================================
*/
DECLARE
   v_ename employee.ename%TYPE;
BEGIN
   SELECT ename INTO v_ename 
   FROM employee 
   WHERE empno=1234;
   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('查询只能返回单行');
   WHEN OTHERS THEN
      dbms_output.put_line('错误号：'||SQLCODE||'错误描述：'||SQLERRM);
END;

/*
===========================================================
|         预定义异常
============================================================
*/
DECLARE 
   v_empno scott.EMP.EMPNO%TYPE ;
   EMPNO_REPEAT EXCEPTION;   --1.定义异常变量
   PRAGMA EXCEPTION_INIT(EMPNO_REPEAT,-1407); --2.关联异常代码  ,1407不能为NULL值
BEGIN
   
   UPDATE scott.EMP SET empno = NULL WHERE empno = 7369;
EXCEPTION
   WHEN EMPNO_REPEAT THEN  --3、捕捉异常定义的异常处理
     DBMS_OUTPUT.put_line('部门编号不能为空');
END;

/*
===========================================================
|         用户自定义异常
============================================================
*/
DECLARE
   v_empno scott.EMP.EMPNO%TYPE := 7000;
   NO_RESULT EXCEPTION;   --1.定义异常变量
BEGIN
   UPDATE scott.EMP SET sal = sal+100 WHERE empno = v_empno;
   
   --2.判断是否有执行UPDATE
   IF SQL%NOTFOUND THEN
      RAISE NO_RESULT; --3.没有执行UPDATE，抛出自定义异常
   END IF;
EXCEPTION
   WHEN NO_RESULT THEN --4.捕获异常
      DBMS_OUTPUT.PUT_LINE('你的数据更新语句失败了!');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;

/*
===========================================================
|         用户自定义异常
============================================================
*/

CREATE OR REPLACE FUNCTION F_TEST(i_code IN NUMBER)
RETURN NUMBER IS RET_CODE NUMBER;
BEGIN
   
    IF i_code IS NULL THEN
       RAISE_APPLICATION_ERROR(-20991,'i_code不能为空'); --1.写入自定义代码和错误信息
    ELSIF i_code > 1 THEN
       RAISE_APPLICATION_ERROR(-20992,'i_codeD太大了'); --1.写入自定义代码和错误信息
    ELSE
       RET_CODE := i_code;
       RETURN RET_CODE;
    END IF;
END F_TEST;

DECLARE
  v_result NUMBER;
  v_code NUMBER;
  CODE_NOT_NULL EXCEPTION;  --定义异常
  CODE_LIMITED EXCEPTION;   --定义异常
  PRAGMA EXCEPTION_INIT(CODE_NOT_NULL,-20991);  --2.初始化异常信息
  PRAGMA EXCEPTION_INIT(CODE_LIMITED,-20992);   --2.初始化异常信息
BEGIN
    --v_result := F_TEST(NULL);
    v_result := F_TEST(2); --3.调用函数 
EXCEPTION        --4.捕获异常
   WHEN CODE_NOT_NULL THEN
     DBMS_OUTPUT.put_line('代码不能为空');
   WHEN CODE_LIMITED THEN
     DBMS_OUTPUT.put_line('数字太大了哦');
   WHEN OTHERS THEN
     DBMS_OUTPUT.put_line('其它异常');
END;

预定义异常：

错误号	异常错误信息名称	说明
ORA-0001	Dup_val_on_index	违反了唯一性限制
ORA-0051	Timeout-on-resource	在等待资源时发生超时
ORA-0061	Transaction-backed-out	由于发生死锁事务被撤消
ORA-1001	Invalid-CURSOR	试图使用一个无效的游标
ORA-1012	Not-logged-on	没有连接到ORACLE
ORA-1017	Login-denied	无效的用户名/口令
ORA-1403	No_data_found	SELECT INTO没有找到数据
ORA-1422	Too_many_rows	SELECT INTO 返回多行
ORA-1476	Zero-divide	试图被零除
ORA-1722	Invalid-NUMBER	转换一个数字失败
ORA-6500	Storage-error	内存不够引发的内部错误
ORA-6501	Program-error	内部错误
ORA-6502	Value-error	转换或截断错误
ORA-6504	Rowtype-mismatch	宿主游标变量与 PL/SQL变量有不兼容行类型
ORA-6511	CURSOR-already-OPEN	试图打开一个已处于打开状态的游标
ORA-6530	Access-INTO-null	试图为null 对象的属性赋值
ORA-6531	Collection-is-null	试图将Exists 以外的集合( collection)方法应用于一个null pl/sql 表上或varray上
ORA-6532	Subscript-outside-limit	对嵌套或varray索引得引用超出声明范围以外
ORA-6533	Subscript-beyond-count	对嵌套或varray 索引得引用大于集合中元素的个数.