plsql(二）

最新推荐文章于 2022-04-21 16:58:54 发布

上漂的雪

最新推荐文章于 2022-04-21 16:58:54 发布

阅读量951

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/qiaotokong/article/details/12222545

版权

数据库专栏收录该内容

11 篇文章 0 订阅

订阅专栏

一动态sql:把一个字符串对应sql 当做sql语句来执行
1.DDL的动态sql

declare
         sqlstr   varchar2(100);
     begin
         sqlstr:=
	'create  table  testdsql(id  number)';
	dbms_output.put_line(sqlstr);
	execute  immediate  sqlstr;
     end

2.DML 的动态sql
/*为了解决字符串拼接的问题使用占位符
简化拼接占位符冒号开头使用using 替代
占位符号*/

      declare
          var_id    number:=1001;
         var_name  varchar2(30):='test1111';
         sqlstr    varchar2(100);
      begin 
          sqlstr:='insert into testdsql9527 
          values(:b0,:b1)';
          dbms_output.put_line(sqlstr);
          execute  immediate sqlstr using 
          var_id,var_name;
          commit;
     end;

3.select 语句的动态sql
能且只能返回一个结果

      declare
          sqlstr  varchar2(100);
          var_name  s_emp.first_name%type;
      begin
          sqlstr:='select first_name from s_emp
         where id=1';
         execute immediate sqlstr 
         into var_name;
         dbms_output.put_line(var_name);
      end;

------------------------------------------------
二游标:用来处理多行数据的一个数据类型
一条sql语句可以认为是匿名游标
a.游标的语法
1.声明游标
cursor 游标名 is sql语句;
2.打开游标
open 游标名;
3.提取数据
非滚动游标提取数据必须是顺序的
fetch 游标名 into 变量名;
4.关闭游标
close 游标名;

/*把s_emp 表中所有的数据 放入一个游标中
  输出前两条的 id  first_name salary */
  declare
      cursor  empcursor is select * from s_emp;
      /*根据sql语句定义一个变量*/
      var_emp  empcursor%rowtype;
  begin
      open    empcursor;
      fetch   empcursor into var_emp;
      dbms_output.put_line(var_emp.id||':'||
      var_emp.first_name||':'||var_emp.salary);
      fetch   empcursor into var_emp;
      dbms_output.put_line(var_emp.id||':'||
      var_emp.first_name||':'||var_emp.salary);
      close   empcursor; 
  end;

b.遍历游标中的所有数据
1.游标的属性
%found 是否发现新数据
游标必须处于打开状态如果没有
打开游标使用则返回非法游标。
游标至少被fetch过一次如果没
fetch 则返回NULL值。
发现了新数据就返回true
否则返回false
%notfound 是否没有发现新数据
游标必须处于打开状态否则是
非法游标。
没有fetch 则返回NULL值。
发现了新数据就返回false
没有发现新数据就返回true
%isopen 游标是否打开
打开就返回true 否则返回false
打开的游标不能再打开
关闭的游标不能再关闭
%rowcount 游标指针偏移量

2.结合notfound属性和简单循环遍历游标

    declare
        cursor empcursor is select * from s_emp;
	var_emp empcursor%rowtype;
    begin
        open   empcursor;
        loop
       		fetch  empcursor into var_emp;
/*退出循环的条件是发现不了新数据*/
		exit  when  empcursor%notfound;
		dbms_output.put_line(var_emp.id||':'||
       		 var_emp.first_name||':'||var_emp.salary);
	end loop;
        close  empcursor;
    end;

3.结合while 循环和 found属性

    declare
        cursor empcursor is select * from s_emp;
	var_emp empcursor%rowtype;
    begin
        open   empcursor;
	fetch  empcursor into var_emp;
        while  empcursor%found loop
	dbms_output.put_line(var_emp.id||':'||
        var_emp.first_name||':'||var_emp.salary);
	fetch  empcursor into var_emp;
	end loop;
        close  empcursor;
    end;

4.思考for循环如何遍历上面的游标
智能循环(自动打开游标自动提取数据
自动关闭游标)

    declare
        cursor empcursor is select * from s_emp;
    begin       
        for var_emp in empcursor loop
        dbms_output.put_line(var_emp.id||':'||
        var_emp.first_name||':'||var_emp.salary);
        end loop;      
    end;

----------------------------------------------
c 带参游标
1.语法
cusor 游标名(参数名参数类型,参数名
参数类型) is sql语句;
需要在打开游标时传入实参.
2.把id>n 所有的员工信息放入一个带参游标中

    /* plsql中的参数不能加任何长度修饰
       但是可以使用%type*/
    declare
        cursor  empcursor(var_id s_emp.id%type)
        is  select * from s_emp where id>var_id;
        var_emp  empcursor%rowtype;
    begin
        /* 打开游标时传入实参 */
        open    empcursor(10);
        loop
        fetch  empcursor into var_emp;
        exit when  empcursor%notfound;
        dbms_output.put_line(var_emp.id||
         ':'||var_emp.salary);
        end loop;
	close   empcursor;
    end;

3.for 循环可以遍历带参游标

   declare
        cursor  empcursor(var_id s_emp.id%type)
        is  select * from s_emp where id>var_id;
    begin
          for    var_emp in empcursor(15) loop
          dbms_output.put_line(var_emp.id||
          ':'||var_emp.salary);
          end loop;
    end;

-----------------------------------------
d 参考游标引用游标
ref cursor
a.使用步骤
1.定义参考游标类型
type 参考游标类型名 is ref cursor;
2.使用类型定义变量
游标名参考游标类型;
3.打开参考游标把游标关联到一个字符串上
open 游标名 for sql字符串;
b.把字符串'select * from s_emp' 关联到游标

      declare
          type  empcursortype is ref cursor;
 	empcursor  empcursortype;
 /*定义一个变量接收游标中的数据*/
          var_emp    s_emp%rowtype;
 /*和游标关联的字符串*/
          sqlstr     varchar2(100); 
      begin
          sqlstr:='select * from s_emp';
          /*打开游标时 把游标关联到字符串*/
 	open   empcursor  for sqlstr;
          loop
              fetch  empcursor into var_emp;
   	     exit when empcursor%notfound;
  	     dbms_output.put_line(var_emp.id||
   	     ':'||var_emp.salary);
 	end loop;
 	close  empcursor;
    end;

c.字符串中有占位符 :b0

          sqlstr:='select * from s_emp where id>:b0';
          /*打开游标时 把游标关联到字符串*/
 	open   empcursor  for sqlstr using var_id;

三 exception 运行时错误
1.如何使用系统预定义异常

  declare
  begin
  exception
      when  预定义异常名  then
          /*相应的异常处理*/
  end;

常见异常

ACCESS_INTO_NULL

未定义对象
CASE_NOT_FOUND
CASE 中若未包含相应的 WHEN ，并且没有设置 ELSE 时
COLLECTION_IS_NULL
集合元素未初始化
CURSER_ALREADY_OPEN ####
游标已经打开
DUP_VAL_ON_INDEX ####
唯一索引对应的列上有重复的值
INVALID_CURSOR ####
在不合法的游标上进行操作
INVALID_NUMBER ####
内嵌的 SQL 语句不能将字符转换为数字
NO_DATA_FOUND ####
使用 select into 未返回行，或应用索引表未初始化的元素时
TOO_MANY_ROWS ####
执行 select into 时，结果集超过一行
ZERO_DIVIDE ####
除数为 0
SUBSCRIPT_BEYOND_COUNT
元素下标超过嵌套表或 VARRAY 的最大值
SUBSCRIPT_OUTSIDE_LIMIT
使用嵌套表或 VARRAY 时，将下标指定为负数
VALUE_ERROR
赋值时，变量长度不足以容纳实际数据
LOGIN_DENIED
PL/SQL 应用程序连接到 oracle 数据库时，提供了不正确的用户名或密码
NOT_LOGGED_ON
PL/SQL 应用程序在没有连接 oralce 数据库的情况下访问数据
PROGRAM_ERROR
PL/SQL 内部问题，可能需要重装数据字典＆ pl./SQL 系统包
ROWTYPE_MISMATCH
宿主游标变量与 PL/SQL 游标变量的返回类型不兼容
SELF_IS_NULL ####
使用对象类型时，在 null 对象上调用对象方法
STORAGE_ERROR
运行 PL/SQL 时，超出内存空间
SYS_INVALID_ID
无效的 ROWID 字符串
TIMEOUT_ON_RESOURCE
Oracle 在等待资源时超时
例子：

  declare
      var_name   s_emp.first_name%type;
  begin
      select  first_name into var_name
          from s_emp where id>1;
      dbms_output.put_line('app continue!');
  exception
      when   too_many_rows  then
      dbms_output.put_line('too many emps');
      when   no_data_found  then
      dbms_output.put_line('no found emp id=-1');
  end;

2.用户自定义异常
1.定义用户异常
异常变量名 exception;
2.根据条件抛出异常
raise 异常变量名;
3.捕获异常
when 异常变量名 then
4.处理异常

  declare
      too_many_emps    exception;
  begin
      if  1=1  then
          raise  too_many_emps;
      end if;
  exception    
      when  too_many_emps then
      dbms_output.put_line('user exception');
      /*其它异常 一定是出现在异常处理的最后*/
      when  others    then
      dbms_output.put_line('other exception');
  end;

四存储过程与函数

a 存储过程

1.如何建立存储过程

    create  or replace  procedure  
    过程名(参数  参数的类型,参数 参数的类型)
    is
       /*申明区*/
    begin
       /*执行区*/    
    end;

2.设计一个存储过程传入一个整数参数
根据参数从1 输出到这个参数的值

    create   or  replace  procedure 
    getnumn(var_n in number)
    is
    begin
        for var_i in 1..var_n  loop
   dbms_output.put_line(var_i);
end loop;
    end;

3.查看存储过程准备调用
desc 存储过程名;
desc getnumn;
---------------- ------- ------ --------
VAR_N NUMBER IN
参数名字参数的类型参数的模式参数的缺省值
参数的模式
in 负责给存储过程传入值默认的模式
out 负责给存储过程传出值
in out 即负责传入值又负责传出值
4.调用存储过程
call getnumn(5);
exec getnumn(5);
/*在匿名块或者有名块中调用存储过程*/
begin
getnumn(5);
end;

5 查看过程源码 select text from user_source where name='GETMAX'; --名字要大写

b 函数

函数和存储过程的区别
1.关键字不同 procedure function
2.函数有返回值类型和返回值
3.存储过程可以直接在plsql中调用
函数必须组成表达式才能调用
--设计一个函数传入两个整数参数返回最大值

create  or replace function getmaxfun
(x in number,y in number)return  number   
is
begin
    if  x<y  then
        return  y;
    end if;
        return  x;
end;

查看函数

desc  getmaxfun;

使用函数

select getmaxfun(1,2) from dual;

或者

declare
    var_res   number;
begin
    var_res:=getmaxfun(1,10086);
    dbms_output.put_line(var_res);
end;

参数的默认值

    create  or  replace  function  printNum(var_n
    in  number:=10)return  number 
    is 
    begin
        return  var_n;
    end;

    select  printNum()  from dual;
    select  printNum(90)  from dual;