PLSQL编程基础

一 PL/SQL简介

　　1 SQL:结构化的查询语句

　　2 PL/SQL优点与特性:

　　　　提高运行效率==>>提高运行效率的其他方式(存储过程,分页,缓存,索引)

　　　　模块化设计

　　　　允许定义标识符(变量,游标,常量,异常等)

　　　　过程化(融入了第三代语言的特点,具有过程化)

　　　　兼容性好(可在oracle提供的应用工具中使用)

　　　　可处理错误(提高程序健壮性,避免异常问题,简化错误处理)

　　 3 语言基础:

　　　　支持:select语句,dml(数据操作语句),事务控制语句

　　　　不支持:ddl(数据定义语句) 如:创建表,字段,存储过程,数据库等

　　 4 块

　　　　分类:无名块,匿名块,有名块(将块放在存储过程/函数中)

　　　　定义无名块:

declare
   定义:变量/常量/游标/例解等
begin
   执行:PL/SQL,SQL语句
[exception 异常处理:处理运行错误](可选部分)
end;

　　5 Oracle中定义标识符

　　　　常量:c_name;

　　　　变量:v_name; 变量赋值:(v_name varchar2(30) :='张三')(:=)

　　　　游标:cus_name;

　　　　异常:e_name;

　　6 数据类型

　　　　标量类型(矢量):int,binary_double,binary_float,binary_integer, float,integer,numric,number,char,character,long, long raw,nchar,rowid,string,varchar,varchar2,bollean,date,timestamp(定义日期和时间数据)

　　　　属性类型(存储更多的数据):%type,%rowtype(行类型),record(自定义记录类型),table(表类型),varray(动态数组类型)

实例:record自定义记录类型

declare   
     type r_name is record(
            v_name dept.
            dname%type,
            v_loc dept.loc%type
        );  
  rec_v r_name;  
  select dname,loc into rec_v.v_name,rec_v.v_loc from dept where deptno=10 ;
end;

　　　　参数类型:ref cursor,ref object_type

　　　　复合类型:befile,blog,clob,nclob

　　7 控制结构

　　　　条件分支语句:

　　　　语法：

  if 条件 then 处理
    elsif 条件 then 处理
    else 处理
  end if;

　　　　实例：

declare
  v_name varchar2(20); 
 begin 
  select scott.emp.ename into v_name from scott.emp where scott.emp.empno=7369;
  if v_name='SMITH' then dbms_output.put_line('SMITH');
  else dbms_output.put_line('员工姓名:'||v_name);
  end if;
 end;

　　case语句:执行多重条件分支操作

　　　语法：

case 条件
    when 表达式 then 处理
    when 表达式 then 处理
    when 表达式 then 处理
    else 处理--==>>default
end case;

　　　实例：

declare
 v_no scott.emp.deptno%type;
      begin
        v_no :=&deptno;
        case v_no
          when 10 then update scott.emp set scott.emp.comm=100 where scott.emp.deptno=v_no;
          when 20 then update scott.emp set scott.emp.comm=80 where scott.emp.deptno=v_no;
          when 30 then update scott.emp set scott.emp.comm=60 where scott.emp.deptno=v_no;
          else dbms_output.put_line('该部门不存在');
        end case;
       end;

　　　　实例2：使用多重条件

declare 
      v_sal scott.emp.sal%type;
      v_name scott.emp.ename%type;
begin
  select scott.emp.ename,scott.emp.sal into v_name,v_sal from scott.emp where scott.emp.empno=&empno;
  case
    when v_sal<2000 then update scott.emp set scott.emp.comm=100 where scott.emp.ename=v_name;
    when v_sal<3000 then update scott.emp set scott.emp.comm=80 where scott.emp.ename=v_name;
    when v_sal<4000 then update scott.emp set scott.emp.comm=50 where scott.emp.ename=v_name;
  end case;
end;

View Code

　　循环语句：

　　　　基本循环:loop 变化量 exit when 退出条件

　　　　　　实例：

declare
     i int :=0;
begin 
     dbms_output.put_line(i);
     loop i:=i+1;
         exit when i=100;
     end loop;
end;

View Code

　　　　while 条件 loop 变化 end loop;

　　　　　　实例：

declare
     i int :=0;
begin
    while i<100 
    loop
        dbms_output.put_line(i);
        i :=i+1;
    end loop;
end;

View Code

　　　　for 循环变量 in [reverse] lower_bound..upper_bound loop 处理 end loop

　　　　　　实例：

declare 
     i int :=10;
begin
    for i in 10..1000 loop
        dbms_output.put_line(i);
    end loop;
end;

View Code

　　8 异常处理
　　　　分类:预定义异常,非预定义异常,自定义异常
　　　　异常处理:
　　　　　　exception
   　　　　　　when 异常 then 处理
          　　　 when 异常 then 处理
   　　　　　　when 异常 then 处理
   　　　　　　when others then 处理

　　　　　　实例：

 --未处理
 declare
   v_name scott.emp.ename%type;
   begin
      select scott.emp.ename into v_name from scott.emp where scott.emp.empno =&empno;
      dbms_output.put_line(v_name);
   end; 
 --已处理
  declare
   v_name scott.emp.ename%type;
   begin
      select scott.emp.ename into v_name from scott.emp where scott.emp.empno =&empno;
      dbms_output.put_line(v_name);
      exception 
        when no_data_found then 
             dbms_output.put_line('该员工信息不存在');
   end;

View Code

　　　　处理预定义异常(由系统提供)
   　　　　case_not_found:case 语句中在when子句中没有包含必须的条件分支且无else子句
   　　　　cursor_already_open:打开已经打开的游标
   　　　　invalid_number:字符转化为数字错误
   　　　　too_many_rows:返回超过一行数据
   　　　　zero_divide:除数为0触发
   　　　　no_data_found:无数据时触发

　　　　　实例：

declare
   e_inter exception;--非预定异常
   e_emp exception;--自定义异常
   pragma exception_init(e_inter,-2999);
begin
  update scott.emp set scott.emp.deptno=40 where scott.emp.empno=1;
    if sql%notfound then
       raise e_emp;--显示触发自定义异常
    end if;
  exception
    when e_inter then dbms_output.put_line('该部门不存在');
    when e_emp then dbms_output.put_line('该员工不存在');
end;

View Code

　　9 游标(操作多行数据)
　　　　　显示游标:查询结果操作一行需要一个显示游标
　　　　　　　定义:declare cursor cus_name is 对应的select语句
　　　　　　　打开:open cus_name;
　　　　   　　提取数据:
  　　　　　　　　　　提取一行数据:fetch cursor into 接收数据的变量
  　　　　　　　　　　提取多行数据:fetch cus_name into bulk collect into 接收游标结果的集合变量
　　　　　　　关闭游标:close cus_name;
　　　　显示游标属性:
　　　　　　1)%isopen:判断游标是否打开
　　　　　　2)%found:是否提取了数据
　　　　　　3)%not found:与found相反
　　　　　　4)rowcount:返回到当前行数为止已提取到的实际行数
　　　　　　实例：

declare
  cursor cus_emp
is
  select scott.emp.ename,scott.emp.sal from scott.emp where scott.emp.deptno=20;
  v_name scott.emp.ename%type;
  v_sal scott.emp.sal%type;
begin
  open cus_emp;
  loop
    fetch cus_emp into v_name,v_sal;
    exit when cus_emp%notfound;
    dbms_output.put_line(v_name||':'||v_sal);
  end loop;
  close cus_emp;
end;


 --输出结果
SMITH:800
JONES:2975
SCOTT:3000
ADAMS:1100
FORD:3000

View Code

　　　　参数游标 declare cursor cus_name(parameter_name type) is select_statement;

　　　　　　实例：显示特定部门所有员工的姓名和工资

declare
  cursor cus_name(cNo number)
is
  select scott.emp.ename,scott.emp.sal from scott.emp where deptno=cNo;
  v_name scott.emp.ename%type;
  v_sal scott.emp.sal%type;
begin
  if not cus_name %isopen then open cus_name(30);
  end if;
  loop
    fetch cus_name into v_name,v_sal; --提取数据
    exit when cus_name%notfound;
    dbms_output.put_line(v_name||':'||v_sal);
  end loop;
  close cus_name;
end;




--输出结果
ALLEN:1600
WARD:1250
MARTIN:1250
BLAKE:2850
TURNER:1500
JAMES:950

View Code

　　　　使用游标更新或删除数据

　　　　　　语法:

　　　　　　　　declare cursor_name(parameter_name type) is select_statement for update[of column_reference][nowait]
　　　　　　　　for update子句:在游标结果集上加共享锁
　　　　　　　　of:省略则代表对全表加锁反之对指定列加锁
　　　　　　　　nowait:立即加锁

　　　　　　实例：将工资低于2500的增加150

declare
  cursor cus_name
is
  select scott.emp.ename,scott.emp.sal from scott.emp for update of scott.emp.sal;  --在sal列上添加共享锁
  v_name scott.emp.ename%type;
  v_sal scott.emp.sal%type;
begin
  if not cus_name %isopen then open cus_name;
  end if;
  loop
    fetch cus_name into v_name,v_sal;
    exit when cus_name%notfound;
    if v_sal<2500 then update scott.emp set scott.emp.sal=scott.emp.sal+150 where current of cus_name;
    end if;
  end loop;
  close cus_name;
end;

View Code

　　　　游标的for循环

　　　　　　实例：显示部门编号为10的所有员工姓名

declare
 cursor cus_name
is
 select scott.emp.ename from scott.emp where scott.emp.deptno=10;

begin
  for emp_record in cus_name loop
    dbms_output.put_line('第'||cus_name%rowcount||'员工'||emp_record.ename);
  end loop;
end;





--结果
第1员工SMITH
第2员工JONES
第3员工SCOTT
第4员工ADAMS
第5员工FORD

View Code

　　　　使用游标循环时可直接在for中进行子查询

　　　　　　实例：

declare
 cursor cus_name
is
begin
  for emp_record in (
 select scott.emp.ename from scott.emp where scott.emp.deptno=10;
)loop
    dbms_output.put_line('第'||cus_name%rowcount||'员工'||emp_record.ename);
  end loop;
end;

View Code