数据库学习11-PLSQL语法

1.pl/sql
  1.1  pl/sql是什么；
       pl/sql(Procedural Language extensions to the Strutured Query Language)过程化语言和结构化查询语言结合的编程语言；
  1.2  pl/sql和sql的不同；
       1.2.1  pl/sql支持更多的数据类型和操作符；
       1.2.2  可以过程化逻辑控制编程；
  1.3  pl/sql怎么过程化；
       1.3.1 条件控制
       1.3.2 循环控制
       1.3.3 顺序
2.pl/sql块
  2.1  pl/sql块是什么；
       2.1.1 pl/sql是块结构语言，pl/sql程序由逻辑块组成；
       2.1.2 逻辑块包括函数、过程、匿名块；
  2.2  pl/sql匿名块和格式;
       没有名字，格式：
       [declare declarations] --声明部分（不是必须）
       begin 
           --executable statments;  （必须）可执行部分 数据操作语句、事务控制语句
       [exception handlers]   --异常处理部分（不是必须的）
       end;   --（必须）
3.介绍pl/sql
  3.1  和sql一样不区分大小写
  3.2  变量和常量的声明
       3.2.1 变量的声明和初始化；
             变量名 数据类型[(范围)] := 初始值;
             变量名 数据类型[(范围)] default 初始值;
       3.2.2 常量的声明
             常量名 constant 数据类型[(范围)] := 初始值;
  3.3  数据类型和操作符
       3.3.1 数据类型说明 个数，开始；
             3.3.1.1 数字类型 number
             3.3.1.2 字符类型 char,varchar,varchar2
             3.3.1.3 时间类型 date,timestamp
             3.3.1.4 大文本类型 clob,blob
             3.3.1.5 布尔类型 boolean(true,false,null)
             3.3.1.6 属性类型 %type,%rowtype
       3.3.2 操作符说明 (赋值:=,乘方**,范围..,标识<< >>,连接||,--,/**/,关系运算符=,<>,!=,>,<,>=，<=)；
  3.4  打印语句；
       set serveroutput on开始  以'/'结束来执行语句块;
       DBMS_OUTPUT.PUT_LINE('comment');
  3.5  过程控制 (&varname)；
       3.5.1 条件 
       3.5.2 循环
       3.5.3 顺序
4.异常
  4.1  预定义异常
  4.2  自定义异常
       4.2.1 raise 声明异常出现在什么情况
       4.2.2 raise_application_error() -- (-20000 .. -20999);创建一个用户定义的错误信息提示






预定义异常：
异常名                        说明
--------------------------------------------------------------------------------
ACCESS_INTO_NULL          未初始化对象时出现
CASE_NOT_FOUND            在CASE语句中的选项与输入的数据不匹配时出现
COLLECTION_IS_NULL       给尚未初始化的表或数组赋值时出现
CURSOR_ALREADY_OPEN  试图重新打开已打开的游标时出现，重新打开游标
                                         前必须先关闭
DUP_VAL_ON_INDEX        试图将重复的值存储在使用唯一索引的列中时出现
INVALID_CURSOR             执行非法游标运算时出现，比如打开一个尚未打开
                                         的游标
INVALID_NUMBER            将字符串转换为数字时出现
LOGIN_DENIED                输入的用户名或密码无效时出现
NO_DATA_FOUND            表中不存在请求的行时出现或者引用已删除的元素时
STORAGE_ERROR             内存损坏或PL/SQL耗尽内存时出现
TOO_MANY_ROWS            执行select into语句后返回多行时出现
VALUE_ERROR                  产生大小限制错误时出现

ZERO_DIVIDE                   除数为零时出现

/*
  回顾
declare
  --声明部分
begin
  --可执行部分
exception
 
end;

*/
--变量
declare
  a number default 1;  --初始化第一种方式
  --a number := 1;     --初始化第二中方式
  var_col emp.ename%type;
  var_col2 NUMBER;
  var_const CONSTANT VARCHAR2(10); --常量
begin
  a := 2;  --给变量赋值
  select ename,sal into var_col,var_col2 from emp 
    WHERE empno = 7788;
    
end;

--顺序、条件、循环结构
/* 
 if 条件表达式 then
 elsif 条件表达式 then
 else  
 end if;
 */
/*
  case 变量
    when 值1 then 
    when 值2 then 
    ...
    else
  end case;
*/
/*
  循环体
  loop 
  end loop;
  
  第一种循环：
  loop 
    exit when 表达式;
  end;  
  第二种:
  while 表达式 
  loop
  end loop;
  第三种：
  for 变量 in 范围 
  loop
  end loop;
*/
DECLARE
  salary NUMBER;
  e_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO e_count FROM emp;
  FOR n IN 1..e_count LOOP
    SELECT sal INTO salary FROM 
     (SELECT ROWNUM rm,t.* FROM emp t)  
      WHERE rm = n;
    dbms_output.put_line(salary);  
  END LOOP;
END;

declare
  d_id number;
  d_name dept.dname%type;
  e_name emp.ename%type;
  d_count number;
  e_count number;
begin
  select count(*) into d_count from dept;
  for i in 1..d_count loop
     select deptno,dname into d_id,d_name 
       from (select rownum num,t.* from dept t)
     where num = i;
     dbms_output.put_line('部门名称：'||d_name);
     select count(*) into e_count from emp
        where deptno=d_id;
      dbms_output.put('部门人员：');
     for j in 1..e_count loop
       select ename into e_name from 
         (select rownum num,t.* from emp t 
            where deptno = d_id)
       where num = j;
       if j<e_count then 
          dbms_output.put(e_name||',');
       else
          dbms_output.put(e_name);
       end if;
     end loop;
     dbms_output.put_line('');
     dbms_output.put_line('');
  end loop;
end;

/*
  6. 增加指定部门员工的工资10%。
    然后打印所有的最新工资，
    以及所有工资之和。
*/
DECLARE
  e_sal NUMBER;
  e_id NUMBER;
  d_id NUMBER;
  c NUMBER;
  s_sum NUMBER DEFAULT 0;
BEGIN
  d_id := &abc;
  SELECT COUNT(*) INTO c FROM emp 
    WHERE deptno = d_id;  --记录数
  FOR i IN 1..c LOOP
    SELECT empno,sal INTO e_id,e_sal FROM 
      (SELECT ROWNUM num,t.* FROM emp t WHERE deptno=d_id)
    WHERE num = i; 
    dbms_output.put_line(e_id||'更新前的薪水是：'||e_sal);
    e_sal := e_sal*1.1;
    dbms_output.put_line(e_id||'更新后的薪水是：'||e_sal);
    UPDATE emp SET sal = e_sal WHERE empno = e_id;
    s_sum := e_sal+s_sum;
  END LOOP;
    dbms_output.put_line('薪水总和是：'||s_sum);
  COMMIT;
END;

--异常
DECLARE
  a NUMBER;
BEGIN
  SELECT sal INTO a FROM emp 
     WHERE ename = 'nimei';
  dbms_output.put_line(a);
EXCEPTION 
  WHEN no_data_found THEN
    dbms_output.put_line('未找到数据');
END;

DECLARE
  re NUMBER;
BEGIN
   re := 10/0;
EXCEPTION 
   WHEN zero_divide THEN
     dbms_output.put_line('除数不能为0');
END;

BEGIN
 --正常的程序代码 try
EXCEPTION 
  WHEN zero_divide THEN
     dbms_output.put_line('除数不能为0');
  WHEN no_data_found THEN
    dbms_output.put_line('未找到数据');
  WHEN OTHERS THEN
    dbms_output.put_line('所有异常');
END;

DECLARE
  re NUMBER;
BEGIN
   re := 10/0;
EXCEPTION 
   WHEN OTHERS THEN  
     dbms_output.put_line('异常');
END;

--自定义异常 
DECLARE
  n NUMBER;
  my_exception EXCEPTION; --定义一个异常
BEGIN
  n := &hhh;
  IF n = 0 THEN 
    RAISE my_exception;  --抛出异常的情景
  END IF;
EXCEPTION
  WHEN my_exception THEN
    dbms_output.put_line('手贱....');
END;  
--根据编号查询工作岗位job,
--如果是查询的是Persident,就抛出系统错误
--‘老总不出面’ 
DECLARE
 e_job VARCHAR2(15);
 per_exception EXCEPTION;
BEGIN
  SELECT job INTO e_job FROM emp 
    WHERE empno=ⅈ
  IF upper(e_job) = 'PRESIDENT' THEN
     RAISE per_exception;
  END IF;
EXCEPTION 
  WHEN per_exception THEN
    raise_application_error(-20123,'老总不出面');
END;

--catch异常，不输出打印语句，抛出系统错误
DECLARE
  re NUMBER;
BEGIN
   re := 10/0;
EXCEPTION 
   WHEN OTHERS THEN  
     --dbms_output.put_line('异常');
     raise_application_error(-20100,'系统崩溃'); -- "-20000~-20999"
END;

--动态执行
BEGIN
  EXECUTE IMMEDIATE
     'CREATE TABLE tab(t NUMBER)';
END;

BEGIN
  EXECUTE IMMEDIATE
     'drop TABLE tab cascade constraints';
END;

BEGIN
  EXECUTE IMMEDIATE
  'SELECT * FROM emp WHERE empno = :1'
  USING '7788';
END;