oracle 笔记

oracle 笔记

pl/sql编程的基础:
 a. 简单的存储过程->深入[怎么样分页]
 b. pl/sql的几种变量[标量,复合变量]
 c. 记录和游标的使用
 d. pl/sql的规范

第一部分: 在pl/sql中访问oracle

 概述: pl/sql是oracle在标准的sql语言上的扩展,通过在pl/sql中嵌入sql语句，可以访问oracle数据库.注意在pl/sql中只能直接嵌入select、dml语句(insert , update,delete)和事务控制语句(commit,rollback,savepoint),而不能嵌入ddl语句(create,alter,drop)和dcl语句(grant,revoke)

 这部分学完后应当达到的水平:
  
 1.在pl/sql块中检索单行数据
 2.在pl/sql块中嵌入dml(insert , update,delete)语句
 3.使用sql游标[!!!!]
 4.在pl/sql块中嵌入tcl语句

1.1检索单行数据
 
 语法: select select_list into {variable_name[,variable_name]...} from table where condition; 

 通过在pl/sql块中嵌入select语句，可以将数据库数据检索到变量中。
 这里需要注意的是当在pl/sql中使用select into 语句时，该语句必须返回一条数据，并且只能返回一条数据。否则会有错误抛出.

 1.1.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;
 /

 1.1.2使用记录变量接受数据

 declare
  type emp_record_type is record(
  ename emp.ename%type,sal emp.sal%type);

  emp_record emp_record_type;
 begin
  select ename,sal into emp_record from emp where empno=&no;
  dbms_output.put_line('员工名字:'|| emp_record.ename);
  dbms_output.put_line('薪水:'|| emp_record.sal);
 end;
 /

 使用这种方法可以将返回的数据放到一个记录中去，其实就像是一个结构体一样。

 总结如下: 在pl/sql编程可以根据实际的情况，选择使用标量或者记录变量来保存你的数据

1.2操作数据

 1.2.1插入数据

 基本语法: insert into <table> [(column[,column,...])] values(value,[,value,...])

 

 实例1：在pl/sql块中使用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,'dog');--执行插入
 end;
 /

 实例2：在pl/sql块中使用子查询插入数据

 declare
  v_deptno emp.deptno%type:=&no;--部门号
 begin
  insert into employee select * from emp where deptno=v_deptno;
 end;
 /

 这里我们使用了子查询来完成这件事.

 1.2.2更新数据
 
 实例1;使用表达式更新
 --什么意思:
 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;
 /

 
 实例2:

 请大家思考: 如何根据输入的员工的姓名更新与该员工岗位相同的所有员工的工资和补助;
 比如:输入SCOTT的姓名后,将与scott岗位相同的所有员工的工资和补助更新为与scott相同

 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;
 /

 1.2.3删除数据

 在pl/sql块中，删除数据是使用delete语句来完成的，删除数据的语法与sql*plus中没有不同，只是在提供数值时可以使用pl/sql变量.
 
 在删除数据时，如果不指定where子句，那么会删除表中所有的数据

 实例1
 declare
  v_deptno dept.deptno%type:=&no;
 begin
  delete from dept where deptno=v_deptno;
 end;
 /
 

 实例2
 
 在where子句中不但可提供变量或是数据，也可以使用子查讯来删除数据

 --输入一个用户名,删除该用户所在部门的所有员工
 declare
 v_ename emp.ename%type:='&name';
 begin
  delete from emp where
  deptno=(select deptno from emp where ename=v_ename);
 end;
 /

 这样如果输入scott就会将scott所在的部门的所有员工全部删除

 1.2.4sql游标

 当执行select,insert,update,delete语句时，oracle会为这些sql语句分配相应的上下文区(context area),并且oracle使用上下文执行相应的sql语句.而游标就是指向上下文的指针.

 在oracle中，游标有两种  隐含游标和显示游标,隐含游标又叫sql游标，专门用于处理select into ,insert,update,delete语句.为了取得dml语句的结果，必须使用sql游标属性，sql游标包括 sql%found,sql%notfound,sql%rowcount,sql%isopen四种属性

 1)sql%isopen

 用于确定sql游标是否打开.

 2)sql%found

 确定sql语句执行是否成功。当sql语句有作用时，其属性值为true;否则为false;

 案例

 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('suc');
  else
  dbms_output.put_line('fail');
  end if;
 end;
 /

 当输入的deptno不存在时，就会打印出fail,

 3)sql%notfound

 用于确定sql语句执行是否成功.

 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('fail');
  else
  dbms_output.put_line('suc');
  end if;
 end;
 /

 4)sql%rowcount

 用于返回sql语句所作用的总行数,示例如下:

 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;
 /
  
 1.3事务控制语句

 在编写pl/sql程序时，不仅可以直接嵌入select 和dml语句，还可以直接嵌入事务控制语句.在oracle中事务控制语句包括
commit,rollback,savepoint三种语句.

 实例1，在pl/sql块中使用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;
 /

 如果出现例外，那么该事务将会回滚

 
第二部分编写控制结构

 在任何计算机语言(c,java,cobol)都有各种控制语句(条件语句，循环结构，顺序控制结构..)在pl/sql中也存在这样的控制结构

 在本部分学习完毕后，希望大家达到 :
 1)使用各种if语句
 2）使用循环语句
 3）使用控制语句---goto 和 null;

 2.1条件分支语句

 在pl/sql中提供了三种条件分支语句 if -- then, if --then---else, if---then---elsif---else

 2.1.1简单的条件判断

 if----then

 declare
  v_sal number(6,2);
  v_ename emp.ename%type:='&name';
 begin
  select sal into v_sal from emp
  where ename=v_ename;
  if v_sal<2000 then
  update emp set sal=v_sal+200
  where ename=v_ename;
  end if;

 end;
 /
   

 2.1.2二重条件分支

 if----then---else

 declare
  v_comm number(6,2);
  v_no emp.empno%type:=&no;
 begin
  select comm into v_comm from emp
  where empno=v_no;
  if v_comm<>0 then
  update emp set comm=v_comm+10
  where empno=v_no;
  else
  update emp set comm=200
  where empno=v_no;
  end if;
 end;
 /

 如果补助不是0就在原来的基础上增加100;如果补助为0就把补助设为200;

 2.1.3多重条件分支

 if---then---elsif

 undefine no
 declare
  v_job varchar2(10);
  v_sal number(6,2);
 begin
  select job,sal into v_job,v_sal
  from emp where empno=&&no;
  if v_job='PRESIDENT' then
  update emp set sal=v_sal+1000 where empno=&no;
  elsif v_job='MANAGER' then
  update emp set sal=v_sal+500 where empno=&no;
  else
  update emp set sal=v_sal+100 where empno=&no;
  end if;
 end;
 /
 java:
  if(){
  }
  else if(){
  }
  else if(){
  }
  else{
  }

 2.2循环语句

 循环语句包括基本循环，while循环，和for循环

 2.2.1基本循环

 这是pl/sql中最简单的循环语句,这种循环语句以loop开头，以 end loop 结尾

 这种循环至少会被执行一次

 sql>create table temp(cola number);
 declare
 i number:=1;
 begin
 loop
  exit when i=10;--退出条件
  insert into temp values(i);
  i:=i+1;
 end loop;
 end;
 /

 2.2.2while循环

 基本循环至少要执行循环体一次，而对于while循环来说，只有条件为true时，才会执行循环体语句,while循环以while..loop 开始，以end loop结束

 declare
 i number:=1;
 begin
  while i<=10 loop
  insert into temp values(i);
  i:=i+1;
  end loop;
 end;
 /

 while(){
  //....
 }
 2.2.3for循环

 我们在看看for循环语句

 begin
  for i in reverse 1..10 loop
  insert into temp values(i);
  end loop;
 end;
 /
 for(){
 }
 我们可以看到控制变量i，在隐含中就在不停的增加

 

 2.2.4嵌套循环和标号

 这个相当与高级语言的多层循环，我们不做介绍

 for(int i=0;i<10;i++){
  
  for(int j=0;j<23;j++){
   
  }
 }
 2.3顺序控制语句

 顺序控制语句包括goto ,null

 2.3.goto

 goto语句用于跳转到特定标号去执行语句.注意由于使用goto语句会增加程序的复杂性，并使得应用程序可以读性变差，所以在做一般应用开发时，建议大家不要使用goto语句

 基本语法如下 goto lable_name

 其中 lable_name是已经定义好的标号名,

 案例如下

 declare
 i int :=1;
 begin 
  loop
  insert into temp values(i);
  if i=10 then
  goto end_loop;
  end if;
  i:=i+1;
  end loop;
 <<end_loop>>
 dbms_output.put_line('循环结束');
 end;
 /

 for(){
  for(){
   for(){
    goto here
   }
  }
 }
 here
 2.3.2null

 null 语句不会执行任何操作，并且会直接将控制传递到下一条语句。使用null语句的主要好处是可以提高pl/sql的可读性，

 案例如下
 declare
  v_sal emp.sal%type;--定义工资
  v_ename emp.ename%type;--定义名字
  begin
  select ename,sal into v_ename,v_sal
  from emp where empno=&no;
  if v_sal<3000 then
  update emp set comm=sal*0.1 where ename=v_ename;
  else
  null;
  end if;
  end;
  /

 

第五部分 处理例外

 概述：当开发pl/sql应用程序时，为了提高应用程序的健壮性.开发人员应当对可能发生的例外做相应的处理

 5.1例外介绍

 例外(exception) 是一种pl/sql标识符,如果运行pl/sql快时出现错误或是警告，则会触发例外.

 5.1.1例外的分类

 oracle将例外分为预定义例外，非预定义例外和自定义例外三种。

 预定义例外用于处理常见的oracle错误
 非预定义例外用于处理预定义例外不能处理的例外
 自定义例外用于处理与oracle错误无关的其它情况

 （1）例外传递，如果不处理例外我们看看会出现什么情况

 declare
  v_ename emp.ename%type;
 begin
  select ename into v_ename from emp
  where empno=&no;
  dbms_output.put_line('员工名:'||v_ename);
 end;
 /

 (2)处理例外

 其结构如下：

 declare
 .........
 
 begin
 ..... 触发例外
               |
    |
 exception |
 .....普作例外并处理
 
 end;

 declare
  v_ename emp.ename%type;
 begin
  select ename into v_ename from emp
  where empno=&no;
  dbms_output.put_line('员工名:'||v_ename);
 exception
  when no_data_found then
  dbms_output.put_line('员工号不正确，请确认');
 end;
 /

 5.2处理预定义例外

 预定义例外是由pl/sql所提供的系统例外。当pl/sql应用程序违反了oracle规定的限制时，则会隐含的触发一个内部例外。pl/sql为开发人员提供了二十多个预定义例外。我们给大家介绍常用的例外

 5.2.1常用预定义例外
 
 1）access_into_null

 当开发对象类型应用时，在引用对象属性前，必须首先初始化对象。如果没有初始化对象，直接为对象属性赋值。就会隐含的触发pl/sql例外access_into_null.

 如

 create type emp_type as object
 (name varchar2(10),sal number(6,2));
 /

 declare
  emp emp_type;
 begin
  emp.name:='test';
 exception
  when access_into_null then
  dbms_output.put_line('首先初始化对象emp');
 end;
 /

 2)case_not_found

 在开发pl/sql块中编写case语句时，如果在when子句中没有包含必须的条件分支，就会触发case_not_found的例外

 undef no
 declare
  v_sal emp.sal%type;
 begin
  select sal into v_sal from emp where empno=&&no;
  case
  when v_sal<1000 then
  update emp set sal=sal+100 where empno=&no;
  when v_sal<2000 then
  update emp set sal=sal+200 where empno=&no;
  when v_sal<3000 then
  update emp set sal=sal+300 where empno=&no;
  end case;
 exception
  when case_not_found then
  dbms_output.put_line('case语句没有与'||v_sal||'相匹配的条件');
 end;
 /

 3)cursor_already_open

 当重新打开已经打开的游标时，会隐含的触发例外cursor_already_open

 例如

 declare
  cursor emp_cursor is select ename,sal from emp;
 begin
  open emp_cursor;
  for emp_record1 in emp_cursor loop
  dbms_output.put_line(emp_record1.ename);
  end loop;
 exception
  when cursor_already_open then
  dbms_output.put_line('游标已经打开');
 end;
 /

 这里open emp_cursor已经显示的将游标打开了，而在执行游标for循环时，会隐含的再次打开游标，因此就会抛出例外

 
 4)dup_val_on_index

 当在唯一索引所对应的列上插入重复的值时，会隐含的触发例外dup_val_on_index例外

 begin
  update dept set deptno=&new_no where deptno=&old_no;
 exception
  when dup_val_on_index then
  dbms_output.put_line('在deptno列上不能出现重复值');
 end;
 /

 5)invaild_cursor

 当试图在不合法的游标上执行操作时，会触发该例外

 例如：试图从没有打开的游标提取数据，或是关闭没有打开的游标。则会触发该例外

 declare
  cursor emp_cursor is select ename,sal from emp;
  emp_record emp_cursor%rowtype;
 begin
  --open emp_cursor;
  fetch emp_cursor into emp_record;
  dbms_output.put_line(emp_record.ename);
  close emp_cursor;
 exception
  when invalid_cursor then
  dbms_output.put_line('请检测游标是否打开');
 end;
 /

 6)invalid_number
  当输入的数据有误时，会触发该例外

  比如: 数字100 写成了1oo就会触发该例外

 begin
  update emp set=sal+'1oo';
 exception
  when invalid_number then
  dbms_output.put_line('输入的数字不正确');
 end;
 /

 7)no_date_found

  当执行select into 没有返回行，就会触发该例外

  declare
   v_sal emp.sal%type;
  begin
   select sal into v_sal from emp
   where ename='&name';
  exception
   when no_data_found then
   dbms_output.put_line('不存在该员工');
  end;
  /
 8)too_many_rows
  当执行select into 语句时，如果返回超过了一行,则会触发该例外.

  declare
   v_ename emp.ename%type;
  begin
   select ename into v_ename from emp where sal=&sal;
  exception
   when too_many_rows then
   dbms_output.put_line('返回了多行');
  end;
  /

 9)zero_divide

  当进行运算时，如果使用数字除0,就会触发该例外

  declare
   num1 int:=100;
   num2 int:=0;
   num3 number(6,2);
  begin
   num3:=num1/num2;
  exception
   when zero_divide then
   dbms_output.put_line('分母不能为零');
  end;
  /

 10)value_error

  当在执行赋值操作时，如果变量的长度不足以容纳实际数据,则会触发该例外value_error，普组

  declare
   v_ename varchar2(5);
  begin
   select ename into v_ename from emp where empno=&no1;
   dbms_output.put_line(v_ename);
  exception
   when value_error then
   dbms_output.put_line('变量尺寸不足');
  end;
  /
 
 输入7934

 5.2.2其它预定义例外

 1）login_denide
  
 当用户非法登陆时，会触发该例外

 2）not_logged_on

 如果用户没有登陆就执行dml操作，就会触发该例外
 
 3）storage_error
 
 如果超出了内存空间或是内存被损坏，就触发该例外

 4）timeout_on_resource

 如果oracle在等待资源时，出现了超时就触发该例外

 5.3处理非预定义例外

 非预定义例外用于处理与预定义例外无关的oracle错误.使用预定义例外只能处理21个oracle错误,而当使用pl/sql开发应用程序时，可能会遇到其它的一些oracle错误. 比如在pl/sql块中执行dml语句时，违反了约束规定等等，在这样的情况下，也应当要处理oracle的各种例外

 begin
  update emp set deptno=&dno where empno=&eno;
 end;

 输入dno=11
 输入empno=7788

 这样的情况怎样处理

 为了提高pl/sql程序的健壮性，应该在pl/sql应用程序中合理的处理这些错误，其步骤如下:

 定义例外----->关联例外和错误---->引用例外

 declare
  e_my exception;
  pragma exception_init(e_my,-2291);
 begin
  update emp set deptno=&dno where empno=&eno;
 exception
  when e_my then
  dbms_output.put_line('该部门不存在');
 end;
 /

 当不能不存在时，就会触发oracle的-2291这个错误

 5.4处理自定义例外

 预定义例外和自定义例外都是与oracle错误相关的,并却出现的oracle错误会隐含的触发相应的例外;而自定义例外与oracle错误没有任何关联,它是由开发人员为特定情况所定义的例外

 如下面的案例:

 declare
  e_my exception;
  pragma exception_init(e_my,-2291);
 begin
  update emp set deptno=&dno where empno=&eno;
 exception
  when e_my then
  dbms_output.put_line('该部门不存在');
 end;
 /

 输入dno 10
 输入eno 1111;

 可以看到尽管员工不存在，但是却没有任何的输出.

 自定义例外的过程如下:

 定义例外----->显示的触发例外---->引用例外

 
 declare
  e_my exception;
  pragma exception_init(e_my,-2291);
  e_no_employee exception; --添加的代码
 begin
  update emp set deptno=&dno where empno=&eno;
  if sql%notfound then
  raise e_no_employee;--显示的触发该例外
  end if;
 exception
  when e_my then
  dbms_output.put_line('该部门不存在');
  when e_no_employee then
  dbms_output.put_line('员工不存在');
 end;
 /