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;

　　循环语句：

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

　　　　　　实例：

declare

     i int :=0;

begin

     dbms_output.put_line(i);

     loop i:=i+1;

         exit when i=100;

     end loop;

end;

　　　　while 条件 loop 变化 end loop;

　　　　　　实例：

declare

     i int :=0;

begin

    while i<100

    loop

        dbms_output.put_line(i);

        i :=i+1;

    end loop;

end;

　　　　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;

　　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;

　　　　处理预定义异常(由系统提供)
   　　　　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;

　　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

　　　　参数游标 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

　　　　使用游标更新或删除数据

　　　　　　语法:

　　　　　　　　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;

　　　　游标的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

　　　　使用游标循环时可直接在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;

巴特西

PLSQL编程基础

最新文章

热门文章