精华内容
下载资源
问答
  • 原文链接:https://blog.csdn.net/hp961218/article/details/80002256错误提示:ORA-01403:未找到数据产生原因:当查询不到数据时,且把查询结果注入到定义的变量里面解决方法: 1.跳出此次存储过程: 可以写个错误提示:...

    原文链接:https://blog.csdn.net/hp961218/article/details/80002256

    错误提示:ORA-01403: 未找到数据

    产生原因:当查询不到数据时,且把查询结果注入到定义的变量里面

    解决方法:

                1.跳出此次存储过程: 可以写个错误提示:

                     when not_data_found then --后面一般接错误的数据提示消息

                2.不跳出存储过程: 

                    由于错误的根本原因是数据不存在,导致的空值问题,所以可以根据问题的原理得到对应的解决方法。设置一个变量tempSize,类型为number,每次在查询注入前,执行一次 select count(1) into tempSize from table,然后判断tempSize是否大于1,如果大于1再执行查询赋值给其他变量,这样子就一定不会报找不到数据错误,因为count(1)就算找不到 值为0 ,可以赋值给tempSize,这样子就避免了空值问题.

    分析:俩种方法都能解决空值问题,不过第二张方法更好,不用跳出存储过程,实际上也是,因为存储过程大多数是用于更新数据用的,不能因为一个数据找不到,导致大量数据无法更新,所以推荐使用第二种方法解决

    对方法二的解释:

    如:需要查询student表的sid,并赋给一个变量,之后进行一系列操作

    select s.sid into studentId from student s where 条件;

    这条语句没有找到指定的数据,导致在into的时候报错。可以将上述语句改成:

    select count(*) into e_count from student s where 条件;
    if e_count = 1 then
      select s.sid into studentId from student s where 条件;
    end if;
    拿到studentId后需要进行的操作

     

     

     

     

     

    展开全文
  • oracle存储过程出现ORA-01403: 未找到数据 问题解决方法

    oracle存储过程出现ORA-01403: 未找到数据 问题解决方法

    参考文章:

    (1)oracle存储过程出现ORA-01403: 未找到数据 问题解决方法

    (2)https://www.cnblogs.com/xielong/p/10716078.html


    备忘一下。


    展开全文
  • 方法:可以在select into前做个判断是否为空,在进行插入   1.设置变量 x 2. select (1) into x from xx; if x!=0 then  select a into b from xx;

    方法:可以在select into前做个判断是否为空,在进行插入

     

    1.设置变量 x

    2.

    select (1) into x from xx;

    if x!=0 then

      select a into b from xx;

    展开全文
  • Oracle存储过程

    万次阅读 多人点赞 2019-07-01 14:52:17
    Oracle存储过程详解 procedure 1.创建Oracle存储过程 prodedure create or replace procedure test(var_name_1 in type,var_name_2 out ty pe) as --声明变量(变量名 变量类型) begin --存储过程的执行体 end ...

    Oracle存储过程详解 procedure

    1.创建Oracle存储过程 prodedure

    create or replace procedure test(var_name_1 in type,var_name_2 out ty
    pe) as
    --声明变量(变量名 变量类型)
    begin
    --存储过程的执行体
    end test;
    打印出输入的时间信息
    create or replace procedure test(workDate in Date) is
    begin
    dbms_output.putline(The input date is:||to_date(workDate, yyyy-mm-d
    d));
    end test;

    2.变量赋值

    create or replace procedure test(workDate in Date) is
    x number(4,2);
    begin
    x := 1;
    end test;

    3.判断语句

    if 比较式 then begin end; end if;
    
    create or replace procedure test(x in number) is
    begin
    if x >0 then
    begin
    x := 0 - x;
    end;
    end if;
    if x = 0 then
    begin
    x: = 1;
    end;
    end if;
    end test;

    4.for循环

    For ... in ... LOOP
    --执行语句
    end LOOP;
    (1)循环遍历游标
    create or replace procedure test() as
    Cursor cursor is select name from student;
    name varchar(20);
    begin
    for name in cursor LOOP
    begin
    dbms_output.putline(name);
    end;
    end LOOP;
    end test;
    (2)循环遍历数组
    create or replace procedure test(varArray in myPackage.TestArray) a
    s
    --(输入参数varArray 是自定义的数组类型,定义方式见标题6)
    i number;
    begin
    i := 1; --存储过程数组是起始位置是从1开始的,与java、 C、 C++等语言
    不同。因为在Oracle 中本是没有数组的概念的,数组其实就是一张
    --表(Table),每个数组元素就是表中的一个记录,所以遍历数组时就相当于从表
    中的第一条记录开始遍历
    for i in 1..varArray.count LOOP
    dbms_output.putline(The No. || i ||record in varArray is: ||varArray
    (i));
    end LOOP;
    end test;

    5.while 循环

    while 条件语句 LOOP
    begin
    end;
    end LOOP;
    E.g
    create or replace procedure test(i in number) as
    begin
    while i < 10 LOOP
    begin
    i:= i + 1;
    end;
    end LOOP;
    end test;

    6.数组

    首先明确一个概念: Oracle中本是没有数组的概念的, 数组其实就是一张表(Ta
    ble),每个数组元素就是表中的一个记录。
    使用数组时,用户可以使用Oracle已经定义好的数组类型,或可根据自己的需
    要定义数组类型。
    (1)使用Oracle自带的数组类型

    x array; --使用时需要需要进行初始化
    e.g:
    create or replace procedure test(y out array) is
    x array;
    begin
    x := new array();
    y := x;
    end test;

    (2)自定义的数组类型 (自定义数据类型时,建议通过创建Package的方式实现,
    以便于管理) (自定义使用参见标题4.2)

     create or replace package myPackage is
    -- Public type declarations type info is record( name va
    rchar(20), y number);
    type TestArray is table of info index by binary_integer; --此处
    声明了一个TestArray 的类型数据,其实其为一张存储Info 数据类型的Table
    而已,及TestArray 就是一张表,有两个字段,一个是
    name, 一个是y。 需要注意的是此处使用了Index by binary_integer 编制该T
    able 的索引项,也可以不写,直接写成: type TestArray is
    table of info,如果不写的话使用数组时就需要进行初始化: varArray myPac
    kage.TestArray; varArray := new myPackage.TestArray();
    end TestArray;
    

    7.关于package

     

    ORACLE PACKAGE 包 是一组相关过程、函数、变量、常量#SinaEditor_Temp_FontName、类型和游标等PL/SQL程序设计元素的组合。包具有面向对象设计的特点,是对这些PL/SQL程序设计元素的封装。一个包由两个分开的部分组成:  

    (1)包package声明或定义:包定义部分是创建包的规范说明,声明包内数据类型、变量、常量、游标等元素。这部分也是为使用者提供了透明的接口。  

    (2)包体packpage body:包体是包定义部分的具体实现。  

    (3)将有联系的对象打成包,方便使用  

    (4)包中对象包括储存过程,函数,游标,自定义类型和变量,可以在PL_SQL块中应用这些对象.  

    定义包头

    create or replace package <Package_name> is  
      type <TypeName> is <Datatype>;--定义类型  
      -- Public constant declarations  
      <ConstantName> constant <Datatype> := <Value>;--声明常量  
      -- Public variable declarations  
      <VariableName> <Datatype>;  --数据类型  
      -- Public function and procedure declarations  
      function <FunctionName>(<Parameter> <Datatype>) return <Datatype>; --函数  
    end <Package_name>;  

    定义包体

    create or replace package body <Package_name> is  
      -- Private type declarations  
      type <TypeName> is <Datatype>;  
      -- Private constant declarations  
      <ConstantName> constant <Datatype> := <Value>  
      -- Private variable declarations  
      <VariableName> <Datatype>;  
      -- Function and procedure implementations  
      function <FunctionName>(<Parameter> <Datatype>) return <Datatype> is --函数的具体内容  
        <LocalVariable> <Datatype>;  
      begin  
       <Statement>;  
        return(<Result>);  
      end;  
    begin  
      -- Initialization--初始化包体,每次调用时被初始化  
      <Statement>;  
    end <Package_name>;  

    只有当包头编辑成功后才能编辑包体.其中的函数名与过程名须和包头中的函数过程一样.  

     包说明和包体必须有相同的名字  

     包的开始没有begin语句,与存储过程和函数不同。  

     在包的说明部分定义函数和过程的名称和参数,具体实现在包体中定义。  

     在包内声明常量、变量、类型定义、异常、及游标时不使用declare。  

     包内的过程和函数的定义不要create or replace语句。  

     包声明和包体两者分离。  

      包头(Package)与包体(Package body)的应用  

    包的作用: 根据出生年月返回年龄function Getage,返回工资function Getsalary  

    --创建环境 

    Create Table T_PsnSalary  --工资表  
    (  
    Fpsncode varchar(4) default '',  --个人代码  
    Fpsndesc varchar(20) default '',  --描述  
    FpsnBirth varchar(20) default '', --生日  
    FpsnSalary number(8,2)            --工资  
    );  

     --添加数据 

    Insert into T_PsnSalary(Fpsncode,Fpsndesc,FpsnBirth,FpsnSalary) Values('C001','张三','1986.01.10',1100);  
    Insert into T_PsnSalary(Fpsncode,Fpsndesc,FpsnBirth,FpsnSalary) Values('C002','李四','1980.10.10',3000);  
    Insert into T_PsnSalary(Fpsncode,Fpsndesc,FpsnBirth,FpsnSalary) Values('C003','王五','1996.12.10',800);  
    commit;  

    --创建包头 

    create or replace package package_demo is  
     function Getage(birthst varchar,birthend varchar) return integer;  
     function Getsalary(VFpsncode varchar) return number;  
    end package_demo;  

    --创建包体

    create or replace package body package_demo is  
      function Getage(birthst varchar,birthend varchar) return integer ---得到年龄函数  
      is  
        V_birth integer;  
        ToDateEnd Date;  
        Toyear number(4);  
        Tomonth number(4);  
        Fromyear number(4);  
        Frommonth number(4);  
      begin  
        if (birthend='') or (birthend is null) then   
          select sysdate into ToDateEnd from dual;         ---得到系统时间  
        end if;  
        Toyear := to_number(to_char(ToDateEnd,'YYYY'));    ---得到最后年月  
        Tomonth := to_number(to_char(ToDateEnd,'MM'));  
        Fromyear := to_number(substr(birthst,1,4));        ---计算的年月  
        Frommonth := to_number(substr(birthst,6,2));  
        if Tomonth-Frommonth>0 then  
          V_birth:=Toyear-fromyear;  
        else  
          V_birth:=Toyear-fromyear-1;  
        end if;  
        return(V_birth);  
      end Getage;  
      
      function getSalary(VFpsncode varchar) return number  ---返回工资情况  
      is  
        V_psnSalary number(8,2);  
      begin  
        Select FpsnSalary into V_psnSalary from T_PsnSalary where Fpsncode = VFpsncode;  
        return(V_psnSalary);  
      end getSalary;   
    end package_demo;  

    --包的调用

    select a.*,package_demo.Getage(Fpsnbirth,'')age from T_psnsalary a;  --调用包得到年龄功能  
    select package_demo.getsalary('C001') from dual;                     --代码得到工资  

    8.游标的使用 Oracle 中Cursor是非常有用的, 用于遍历临时表中的查询结果。
    其相关方法和属性也很多,现仅就常用的用法做一二介绍

    (1)Cursor型游标(不能用于参数传递)
    create or replace procedure test() is
    cusor_1 Cursor is select std_name from student where ...; --Curso
    r 的使用方式1 cursor_2 Cursor;
    begin
    select class_name into cursor_2 from class where ...; --Cursor的使
    用方式2
    可使用For x in cursor LOOP .... end LOOP; 来实现对 Cursor的遍历
    end test;
    (2)SYS_REFCURSOR型游标, 该游标是Oracle以预先定义的游标, 可作出参数进
    行传递
    create or replace procedure test(rsCursor out SYS_REFCURSOR) is
    cursor SYS_REFCURSOR; name varhcar(20);
    begin
    OPEN cursor FOR select name from student where ... --SYS_REFCURSOR只
    能通过OPEN方法来打开和赋值
    LOOP
    fetch cursor into name --SYS_REFCURSOR只能通过 fetch into来打
    开和遍历 exit when cursor%NOTFOUND; --SYS_R
    EFCURSOR中可使用三个状态属性:
    ---%NOTFOUND(未找到记录信息) %FOUND(找到记录信息)
    ---%ROWCOUNT(然后当前游标所指向的行位置)
    dbms_output.putline(name);
    end LOOP;
    rsCursor := cursor;
    end test;

    下面写一个简单的例子来对以上所说的存储过程的用法做一个应用:
    现假设存在两张表,一张是学生成绩表(studnet),字段为: stdId,math,artic
    le,language,music,sport,total,average,step
    一张是学生课外成绩表(out_school),
    字段为:stdId,parctice,comment
    通过存储过程自动计算出每位学生的总成绩和平均成绩, 同时, 如果学生在课外
    课程中获得的评价为 A,就在总成绩上加20分。

    create or replace procedure autocomputer(step in number) is
    rsCursor SYS_REFCURSOR;
    commentArray myPackage.myArray;
    math number;
    article number;
    language number;
    music number;
    sport number;
    total number;
    average number;
    stdId varchar(30);
    record myPackage.stdInfo;
    i number;
    begin
    i := 1;
    get_comment(commentArray); --调用名为get_comment()的存储过程获取学生
    课外评分信息
    OPEN rsCursor for select stdId,math,article,language,music,sport from
    student t where t.step = step;
    LOOP
    fetch rsCursor into stdId,math,article,language,music,sport; exit whe
    n rsCursor%NOTFOUND;
    total := math + article + language + music + sport;
    for i in 1..commentArray.count LOOP
    record := commentArray(i);
    if stdId = record.stdId then
    begin
    if record.comment = 'A' then
    begin
    total := total + 20;
    go to next; --使用go to跳出for循环
    end;
    end if;
    end;
    end if;
    end LOOP;
    average := total / 5;
    update student t set t.total=total and t.average = average where t.
    stdId = stdId;
    end LOOP;
    end;
    end autocomputer;
    --取得学生评论信息的存储过程
    create or replace procedure get_comment(commentArray out myPackage.my
    Array) is
    rs SYS_REFCURSOR;
    record myPackage.stdInfo;
    stdId varchar(30);
    comment varchar(1);
    i number;
    begin
    open rs for select stdId,comment from out_school
    i := 1;
    LOOP
    fetch rs into stdId,comment; exit when rs%NOTFOUND;
    record.stdId := stdId;
    record.comment := comment;
    recommentArray(i) := record;
    i:=i + 1;
    end LOOP;
    end get_comment;
    --定义数组类型myArray
    create or replace package myPackage is begin
    type stdInfo is record(stdId varchar(30),comment varchar(1));
    type myArray is table of stdInfo index by binary_integer;
    end myPackage;

    字符函数——返回字符值
    这些函数全都接收的是字符族类型的参数(CHR除外)并且返回字符值.
    除了特别说明的之外,这些函数大部分返回 VARCHAR2 类型的数值.
    字符函数的返回类型所受的限制和基本数据库类型所受的限制是相同的。
    字符型变量存储的最大值:
    VARCHAR2数值被限制为 2000字符(ORACLE 8 中为4000 字符)
    CHAR数值被限制为 255字符(在 ORACLE8中是 2000)
    long类型为 2GB
    Clob类型为 4GB
    1、 CHR
    语法: chr(x)
    功能: 返回在数据库字符集中与X拥有等价数值的字符。 CHR和ASCII是一对反函数。 经过
    CHR转换后的字符再经过ASCII转换又得到了原来的字
    符。
    使用位置:过程性语句和SQL语句。
    2、 CONCAT
    语法: CONCAT( string1,string2)
    功能:返回string1,并且在后面连接string2。
    使用位置:过程性语句和SQL语句。
    3、 INITCAP
    语法: INITCAP( string)
    功能:返回字符串的每个单词的第一个字母大写而单词中的其他字母小写的string。单词
    是用.空格或给字母数字字符进行分隔。不是字母的
    字符不变动。
    使用位置:过程性语句和SQL语句。
    4、 LTRIM
    语法: LTRIM( string1,string2)
    功能:返回删除从左边算起出现在string2中的字符的string1。 String2被缺省设置为单
    个的空格。数据库将扫描string1,从最左边开始。当
    遇到不在string2中的第一个字符,结果就被返回了。 LTRIM的行为方式与RTRIM很相似。
    使用位置:过程性语句和SQL语句。
    5、 NLS_INITCAP
    语法: NLS_INITCAP( string[,nlsparams])
    功能: 返回字符串每个单词第一个字母大写而单词中的其他字母小写的string, nlsparams
    指定了不同于该会话缺省值的不同排序序列。如果不指定参数,则功能和INITCAP相同。 N
    lsparams可以使用的形式是:
    ‘ NLS_SORT=sort’
    这里sort制订了一个语言排序序列。
    使用位置:过程性语句和SQL语句。
    6、 NLS_LOWER
    语法: NLS_LOWER( string[,nlsparams])
    功能:返回字符串中的所有字母都是小写形式的string。不是字母的字符不变。
    Nlsparams参数的形式与用途和NLS_INITCAP中的nlsparams参数是相同的。
    如果nlsparams没有被包含,那么NLS_LOWER所作的处理和
    LOWER相同。
    使用位置;过程性语句和SQL语句。
    7、 NLS_UPPER
    语法: nls_upper( string[,nlsparams])
    功能: 返回字符串中的所有字母都是大写的形式的string。 不是字母的字符不变。 nlspara
    ms参数的形式与用途和NLS_INITCAP中的相同。如果
    没有设定参数,则NLS_UPPER功能和UPPER相同。
    使用位置:过程性语句和SQL语句。
    8、 REPLACE
    语法: REPLACE( string, search_str[,replace_str])
    功能: 把string中的所有的子字符串search_str用可选的replace_str替换, 如果没有指
    定replace_str,所有的string中的子字符串
    search_str都将被删除。 REPLACE是TRANSLATE所提供的功能的一个子集。
    使用位置:过程性语句和SQL语句。
    9、 RPAD
    语法: RPAD( string1,x[,string2])
    功能: 返回在X字符长度的位置上插入一个string2中的字符的string1。 如果string2的
    长度要比X字符少,就按照需要进行复制。如果string2
    多于X字符, 则仅string1前面的X各字符被使用。 如果没有指定string2, 那么使用空格
    进行填充。 X是使用显示长度可以比字符串的实际长度
    要长。 RPAD的行为方式与LPAD很相似,除了它是在右边而不是在左边进行填充。
    使用位置:过程性语句和SQL语句。
    10、 RTRIM
    语法: RTRIM( string1,[,string2])
    功能: 返回删除从右边算起出现在string1中出现的字符string2. string2被缺省设置
    为单个的空格.数据库将扫描string1,从右边开始.当遇
    到不在string2中的第一个字符,结果就被返回了RTRIM的行为方式与LTRIM很相似.
    使用位置:过程性语句和SQL语句。
    11、 SOUNDEX
    语法: SOUNDEX( string)
    功能: 返回string的声音表示形式.这对于比较两个拼写不同但是发音类似的单词而言
    很有帮助.
    使用位置:过程性语句和SQL语句。
    12、 SUBSTR
    语法: SUBSTR( string,a[,b])
    功能: 返回从字母为值a开始b个字符长的string的一个子字符串.如果a是0,那么它
    就被认为从第一个字符开始.如果是正数,返回字符是从左
    边向右边进行计算的.如果b是负数,那么返回的字符是从string的末尾开始从右向左进行
    计算的.如果b不存在,那么它将缺省的设置为整个字符
    串.如果b小于1,那么将返回NULL.如果a或b使用了浮点数,那么该数值将在处理进行以前
    首先被却为一个整数.
    使用位置:过程性语句和SQL语句。
    13、 TRANSLATE
    语法: TRANSLATE(string,from_str,to_str)
    功能: 返回将所出现的from_str中的每个字符替换为to_str中的相应字符以后的
    string. TRANSLATE是REPLACE所提供的功能的一个超集.
    如果from_str比to_str长,那么在from_str中而不在to_str中而外的字符将从string
    中被删除,因为它们没有相应的替换字符. to_str不能为空
    .Oracle把空字符串认为是NULL,并且如果TRANSLATE中的任何参数为NULL,那么结果也是N
    ULL.
    使用位置:过程性语句和SQL语句。
    14、 UPPER
    语法: UPPER( string)
    功能: 返回大写的string.不是字母的字符不变.如果string是CHAR数据类型的,那么结果
    也是CHAR类型的.如果string是VARCHAR2类型的,那么
    结果也是VARCHAR2类型的.
    使用位置: 过程性语句和SQL语句。
    字符函数——返回数字
    这些函数接受字符参数回数字结果.参数可以是CHAR或者是VARCHAR2类型的.尽管实际下许
    多结果都是整数值,但是返回结果都是简单的NUMBER
    类型的,没有定义任何的精度或刻度范围.
    16、 ASCII
    语法: ASCII( string)
    功能: 数据库字符集返回string的第一个字节的十进制表示.请注意该函数仍然称作为ASC
    II.尽管许多字符集不是7位ASCII.CHR和ASCII是互为
    相反的函数.CHR得到给定字符编码的响应字符. ASCII得到给定字符的字符编码.
    使用位置: 过程性语句和SQL语句。
    17、 INSTR
    语法: INSTR( string1, string2[a,b])
    功能: 得到在string1中包含string2的位置. string1时从左边开始检查的,开始的位
    置为a,如果a是一个负数,那么string1是从右边开始进行
    扫描的.第b次出现的位置将被返回. a和b都缺省设置为1,这将会返回在string1中第一
    次出现string2的位置.如果string2在a和b的规定下没有
    找到,那么返回0.位置的计算是相对于string1的开始位置的,不管a和b的取值是多少.
    使用位置: 过程性语句和SQL语句。
    18、 INSTRB
    语法: INSTRB( string1, string2[a,[b]])
    功能: 和INSTR相同,只是操作的对参数字符使用的位置的是字节.
    使用位置: 过程性语句和SQL语句。
    19、 LENGTH
    语法: LENGTH( string)
    功能: 返回string的字节单位的长度.CHAR数值是填充空格类型的,如果string由数据
    类型CHAR,它的结尾的空格都被计算到字符串长度中间.
    如果string是NULL,返回结果是NULL,而不是0.
    使用位置: 过程性语句和SQL语句。
    20、 LENGTHB
    语法: LENGTHB( string)
    功能: 返回以字节为单位的string的长度.对于单字节字符集LENGTHB和LENGTH是一样
    的.
    使用位置: 过程性语句和SQL语句。
    21、 NLSSORT
    语法: NLSSORT( string[,nlsparams])
    功能: 得到用于排序string的字符串字节.所有的数值都被转换为字节字符串,这样在不同
    数据库之间就保持了一致性. Nlsparams的作用和
    NLS_INITCAP中的相同.如果忽略参数,会话使用缺省排序.

    9.Oracle存储过程包含三部分:过程声明,执行过程部分,存储过程异常。

    (1)无参存储过程语法

    create or replace procedure NoParPro  
     as  //声明  
     ;  
     begin // 执行  
     ;  
     exception//存储过程异常  
     ;  
     end;

     

    (2)带参存储过程实例

    create or replace procedure queryempname(sfindno emp.empno%type)   
    as 
       sName emp.ename%type;  
       sjob emp.job%type;  
    begin 
           ....  
    exception  
           ....  
    end;

    (3)带参数存储过程含赋值方式

    create or replace procedure runbyparmeters    
        (isal in emp.sal%type,   
         sname out varchar,  
         sjob in out varchar)  
     as  
        icount number;  
     begin 
          select count(*) into icount from emp where sal>isal and job=sjob;  
          if icount=1 then 
            ....  
          else 
           ....  
         end if;  
    exception  
         when too_many_rows then 
         DBMS_OUTPUT.PUT_LINE('返回值多于1行');  
         when others then 
         DBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS过程中出错!');  
    end;

    其中参数IN表示输入参数,是参数的默认模式。

    OUT表示返回值参数,类型可以使用任意Oracle中的合法类型。

    OUT模式定义的参数只能在过程体内部赋值,表示该参数可以将某个值传递回调用他的过程

    IN OUT表示该参数可以向该过程中传递值,也可以将某个值传出去。

    10.存储过程中游标的定义和使用

    准备环境

    CREATE TABLE EMP(
     EMPNO NUMBER(4) NOT NULL,  
     ENAME VARCHAR2(10),  
     JOB VARCHAR2(9),  
     MGR NUMBER(4),  
     HIREDATE DATE,  
     SAL NUMBER(7, 2),  
     COMM NUMBER(7, 2),  
     DEPTNO NUMBER(2)
    );  
    
    INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902, SYSDATE, 800, NULL, 20); 
    INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, SYSDATE, 1600, 300, 30); 
    INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN',7698, SYSDATE, 1250, 500, 30); 
    INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER', 7839,SYSDATE, 2975, NULL, 20); 
    INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698, SYSDATE, 1250, 1400, 30); 
    INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839, SYSDATE, 2850, NULL, 30); 
    INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839, SYSDATE, 2450, NULL, 10); 
    INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566, SYSDATE, 3000, NULL, 20); 
    INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL, SYSDATE, 5000, NULL, 10); 
    INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698, SYSDATE, 1500, 0, 30); 
    INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788, SYSDATE, 1100, NULL, 20); 
    INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698, SYSDATE, 950, NULL, 30); 
    INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566, SYSDATE, 3000, NULL, 20); 
    INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782, SYSDATE, 1300, NULL, 10); 
    COMMIT;
    

    案例一 使用游标查询部门编号为10的所有人姓名和薪水

    create or replace procedure test2 is
    begin
     declare
       type c is ref cursor;    
       emp_sor c;      
       cname emp.ename%type;     
       csal emp.sal%type;
    begin
      open emp_sor for select ename,sal from emp where deptno=10;       
      loop        
        fetch emp_sor into cname,csal;  --取游标的值给变量。             
        dbms_output.put_line('ename:'||cname||'sal'||csal);        
        exit when emp_sor%notfound;        
       end loop;         
       close emp_sor;     
    end;
    end test2;

    案例二  直接定义游标

    create or replace procedure test3 is
    begin
     declare
       cursor emp_sor  is select ename,sal from emp where deptno=10;
       cname emp.ename%type;
       csal emp.sal%type;
    begin
      open emp_sor;
      loop
        fetch emp_sor into cname,csal;  --取游标的值给变量。
        dbms_output.put_line('ename:'||cname||'sal'||csal);
        exit when emp_sor%notfound;
       end loop;
       close emp_sor;
    end;
    end test3;

     

    案例三  使用记录变量来接受游标指定的表的数据

    create or replace procedure test4 is
    begin
     declare
       cursor emp_sor is
         select ename, sal from emp where deptno = 10;
       --使用记录变量来接受游标指定的表的数据
       type emp_type is record(
         v_ename emp.ename%type,
         v_sal   emp.sal%type);
       --用emp_type声明一个与emp_type类似的记录变量。该记录有两列,与emp表的ename,sal同类型的列。
       emp_type1 emp_type;
     begin
       open emp_sor;
       loop
         fetch emp_sor into emp_type1; --取游标的值给变量。
         dbms_output.put_line(emp_type1.v_ename || ',' || emp_type1.v_sal);
         exit when emp_sor%notfound;
       end loop;
       close emp_sor;
     end;
    end test4;

    案例四  for循环从游标中取值

    create or replace procedure test5 is
    begin
     declare
       cursor emp_sor is select a.ename from emp a;
       type ename_table_type is table of varchar2(20);
       ename_table ename_table_type;
     begin
      ----用for游标取值
       open emp_sor;
       ---通过bulk collect减少loop处理的开销,使用Bulk Collect提高Oracle查询效率
       ---Oracle8i中首次引入了Bulk Collect特性,该特性可以让我们在PL/SQL中能使用批查询,批查询在----某--些情况下能显著提高查询效率。
       --采用bulk collect可以将查询结果一次性地加载到collections中。
       --而不是通过cursor一条一条地处理。
       --可以在select into,fetch into,returning into语句使用bulk collect。
       --注意在使用bulk collect时,所有的into变量都必须是collections
         fetch emp_sor bulk collect into ename_table;
         for i in 1 ..ename_table.count loop
           dbms_output.put_line(ename_table(i));
       end loop;
       close emp_sor;
     end;
    end test5;

    案例五  用for取值,带隐式游标会自动打开和关闭

    create or replace procedure test6 is
    begin
     declare
       cursor emp_sor is select a.ename from emp a;
       type emp_table_type is table of varchar(20);
       begin 
       for emp_record in emp_sor
         loop
           dbms_output.put_line('第'||emp_sor%rowcount||'雇员名:'||emp_record.ename);
         end loop;
     end;
    end test6;

    案例六  判断游标是否打开

    
    create or replace procedure test7 is
    begin
     declare
       cursor emp_sor is select a.ename from emp a;
       type emp_table_type is table of varchar(20);
       emp_table emp_table_type;
     begin
      --用for取值,判断游标是否打开
       if not emp_sor%isopen then
         open emp_sor;
       end if;
       fetch emp_sor bulk collect into emp_table;
       dbms_output.put_line(emp_sor%rowcount);
       close emp_sor;
     end;
    end test7;

    案例七  使用游标变量取值

    create or replace procedure test8 is
    begin
      --使用游标变量取值
     declare
       cursor emp_sor is select a.ename,a.sal from emp a;
       emp_record emp_sor%rowtype;
     begin
      open emp_sor;
      loop
        fetch emp_sor into emp_record;
        exit when emp_sor%notfound;
         --exit when emp_sor%notfound放的位置不一样得到的结果也不一样。如果放到dbms_....后,
         --结果会多显示一行数据,即查询结果的最后一行显示了两次。
        dbms_output.put_line('序号'||emp_sor%rowcount||'名称:'||emp_record.ename||'薪水:'||emp_record.sal);
      end loop;
      close emp_sor;
     end;
    end test8;

    案例八 带参数的游标,在打开游标的时候传入参数

    
    create or replace procedure test9 is
    begin
      --带参数的游标,在打开游标的时候传入参数
     declare
       cursor emp_sor(no number) is select a.ename from emp a where a.deptno=no;
       emp_record emp_sor%rowtype;
     begin
      open emp_sor(10);
      loop
        fetch emp_sor into emp_record;
        exit when emp_sor%notfound;
        dbms_output.put_line('序号'||emp_sor%rowcount||'名称:'||emp_record.ename);
      end loop;
      close emp_sor;
     end;
    end test9;

     案例九  使用游标做更新操作

    
    create or replace procedure test10 is
    begin
      --使用游标做更新、删除操作,必须在定义游标的时候加上for update
      --当然也可以用for update nowait
     declare
       cursor emp_sor is select a.ename,a.sal from emp a for update;
       cname emp.ename%type;
       csal emp.sal%type;
     begin
      open emp_sor;
      loop
        fetch emp_sor into cname,csal;
        exit when emp_sor%notfound;
        dbms_output.put_line('名称:'||cname||','||'薪水:'||csal);
        if csal < 2000 then
          update emp set sal = sal+200 where current of emp_sor;
        end if;
      end loop;
      close emp_sor;
      --要查看更新后的数据,必须得重新打开游标去查询
      open emp_sor;
      loop
        fetch emp_sor into cname,csal;
         exit when emp_sor%notfound;
         dbms_output.put_line('名称:'||cname||','||'new薪水:'||csal);
      end loop;
      close emp_sor;
     end;
    end test10;

    案例十  使用游标做删除操作

    
    create or replace procedure test11 is
    begin
      --使用游标做更新、删除操作,必须在定义游标的时候加上for update
     declare
       cursor emp_sor is select a.empno from emp a for update;
       pempno emp.empno%type;
     begin
      open emp_sor;
      loop
        fetch emp_sor into pempno;
        exit when emp_sor%notfound;
        dbms_output.put_line('旧的empno:'||pempno);
        if pempno = 2009 then
           delete emp where current of emp_sor;
        end if;
      end loop;
      close emp_sor;
      --要查看删除后的数据,必须得重新打开游标去查询
      open emp_sor;
      loop
        fetch emp_sor into pempno;
        exit when emp_sor%notfound;
        dbms_output.put_line('新的empno:'||pempno);
      end loop;
      close emp_sor;
     end;
    end test11;

    案例十一 直接使用游标而不用去定义

    
    create or replace procedure test12 is
     begin
       for emp_record in(select empno,sal,deptno from emp)
         loop
           dbms_output.put_line('员工编号:'||emp_record.empno||',薪水:'||emp_record.sal||',部门编号'||emp_record.deptno);
         end loop;
    end test12;

    案例十二 带sql 的统计查询

    
    create or replace procedure test13 is
    begin
     declare
       type test_cursor_type is ref cursor;
       test_cursor test_cursor_type;
       v_name user_tables.TABLE_NAME%type;
       v_count number;
       str_sql varchar2(100);
     begin
      open test_cursor for select table_name from user_tables;
      loop
        fetch test_cursor into v_name;
        if v_name is not null then
          str_sql := 'select count(*) from '|| v_name;
          execute immediate str_sql into v_count;
        end if;
        exit when test_cursor%notfound;
        dbms_output.put_line(v_name||','||v_count);
      end loop;
      close test_cursor;
     end;
    end test13;

     

    当我们写完存储过程之后,我们可以在 command window下执行,oracle默认是不显示输出的, 
    所以我们要 set serveroutput on 命令来显示输出结果,然后exec test1()即可输出结果。

     

    以上内容比较杂乱 ,有的是借鉴别人的 ,为了对自己不熟悉的地方做个记录 一并归入到了本文章中,希望你看了后能有所收获!

    如果你有好的案例可以发给我,有错误的地方敬请指正!

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    展开全文
  • Oracle 存储过程导出大量数据

    千次阅读 2017-05-03 18:01:43
    现场提了一个需求,要执行一条很复杂的SQL,由于是省级的系统,同样的SQL,只是一个条件不一样,需要执行60次,每次查出的数据有上百万,在网上搜索了一个方法是eagle写的: 1.main.sql脚本: [oracle@jumper utl_...
  • oracle存储过程语法

    千次阅读 2018-10-29 16:39:33
    前两天无意见看见了一个非常适合学习Oracle附上链接:...Oracle存储过程基本语法 存储过程   1 CREATE OR REPLACE PROCEDURE 存储过程名   2 IS   3 BEGIN   4 NULL;   5 END;  行1:   CREAT...
  • Delphi7下用dbExpress调用Oracle存储过程(返回数据集)的一个简单示例和调试过程关键字:Delphi7 dbExpress Oracle 存储过程 游标一、问题缘起: 昨天晚上在网络上偶遇jcc老兄,谈起一个月前的一个帖子。...
  • ORACLE - 未找到任何数据

    千次阅读 2017-06-02 17:12:52
    ORACLE - 未找到任何数据问题描述: 存储过程中当使用 SELECT ID INTO V_ID FROM TABLE WHERE 条件 ;没有返回值时,当前语句会抛出异常,并终止当前存储过程! 解决方案:BEGIN SELECT FID INTO v_groupID FROM t...
  • oracle存储过程--数据结构(上)

    千次阅读 2013-07-12 17:57:52
    这篇主要想说的是plsql语言的自定义数据类型。 和编程语言java类比,我们刚入门的时候,最初学习的就是基础数据类型, 然后学习一个main函数,编写代码熟悉java关键字和逻辑控制语句,然后 ...存储过程中也是pl
  • oracle 存储过程或函数的调试过程

    万次阅读 2014-03-30 15:21:49
    第一步: 选择调试模式 第二步: 输入测试数据 第三步: 点击开始调试器 第四步: 输入要显示的变量名 第五步: 第六步: 逐步点击单步调试,并查看变量的...不断重复前六步,直到函数或存储过程调试完成。
  • oracle存储过程入门教程

    千次阅读 2013-03-14 20:10:37
    oracle存储过程入门教程一.概述 Oracle存储过程开发的要点是:一.概述 Oracle存储过程开发的要点是: 使用Notepad文本编辑器,用Oracle PL/SQL编程语言写一个存储过程; 在Oracle数据库中创建一个存储过程; 在...
  • oracle存储过程语法与使用

    千次阅读 2017-07-11 18:50:04
    oracle 存储过程的基本语法1.基本结构 1 CREATE OR REPLACE PROCEDURE 存储过程名  2 IS  3 BEGIN  4 NULL;  5 END;行1: CREATE OR REPLACE PROCEDURE 是一个SQL语句通知Oracle数据库去创建一个叫做skeleton...
  • Oracle 存储过程实例集锦

    千次阅读 2016-07-13 13:23:17
    一、如何创建存储过程procedure 1、创建一个存储过程用于保存已上架商品的数量 CREATE OR REPLACE PROCEDURE getGoodCount IS goodCount int; BEGIN SELECT COUNT(*
  • Oracle存储过程入门学习

    千次阅读 2018-12-12 16:06:39
    1,Oracle存储过程和自定义函数概述 指存储在数据库中供所有用户程序调用的子程序叫存储过程或存储函数。 存储过程 和 存储函数 的相同点和不同点: 1)相同点:都是完成特定功能的程序; 2)不同点:存储过程不...
  • 最近为公司项目数据库通过存储过程做归档,需要用到自定义数组 百度结果中很多写的都不是很清晰,可变长数组定义好后,如果使用。 在此做个记录: 定义: type id_array is table of number(15) index by binary_...
  • Oracle存储过程学习

    千次阅读 2018-06-17 10:27:33
    一、存储过程的定义存储过程是一组为了完成特定功能的SQL 语句块,经编译后存储在数据库中,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。二、使用存储过程的优点1、存储过程和函数以...
  • oracle存储过程实战练习

    千次阅读 2016-11-14 23:44:01
    总结一下今天学到的oracle之procedure ... procedure即oracle存储过程  语法:  CREATE [OR REPLACE] PROCEDURE procedure_name  [(parameter1 [{IN|OUT|IN OUT}] datatype  [{:=|DEFAULT} expressi
  • Oracle存储过程及参数理解

    千次阅读 2018-04-28 15:39:36
    一、过程 (存储过程) 过程是一个能执行某个特定操作的子程序。使用CREATE OR REPLACE创建或者替换保存在数据库中的一个子程序。示例1:声明存储过程,该过程返回dept表行数DECLARE PROCEDURE getDeptCount AS ...
  • Oracle 存储过程 使用示例

    万次阅读 2011-01-17 16:22:00
    因为工作的需要,最近一直在写存储过程。工作了3年,一直都是做管理,也没有正儿八经的去写过存储过程,这次正好可以好好练习一下。... Oracle 存储过程定义和优点与函数区别 http://blog.csdn.net/tianlesoftware
  • 在PB中用oracle存储过程返回记录集做数据源来生成数据窗口 首先oracle存储过程写法与MSSQL不一样,差别比较大。如果是返回数据集的存储过程则需要利用oracle的包来定义游标。 但是若按正规的oracle写法,存储...
  • oracle存储过程创建和结构

    千次阅读 2017-06-14 22:03:11
    存储过程结构  1 CREATE OR REPLACE PROCEDURE 存储过程... CREATE OR REPLACE PROCEDURE 是一个SQL语句通知Oracle数据库去创建一个叫做skeleton存储过程, 如果存在就覆盖它; 行2:  IS关键词表明后面将跟随一个
  • oracle存储过程超级详解

    千次阅读 2016-08-10 16:50:05
    Oracle 存储过程总结 1、创建存储过程 create or replace procedure test(var_name_1 in type,var_name_2 out ty pe) as --声明变量(变量名 变量类型) begin --存储过程的执行体 end test; 打印出输入的时间信息 E.g...
  • Oracle存储过程汇总

    千次阅读 2018-07-25 11:01:21
    这里我的需求是要用一张临时表来存我计算的数据,因为我在拿到外部给的1个商品的时候,搜索出来可能是多个省份的值,属于我计算的时候就有1对多的原始数据,我要存储这些原始数据所以需要创建一个存储过程用的临时表...
  • Oracle存储过程的定义及断点调试

    千次阅读 2019-08-05 19:13:22
    存储过程  所用函数说明  功能:求和与差  原形:GetSumAndSub(p1 in number , p2 in number ,m out number , b out number)   参数:m=p1+p2 b=p1-p2  1.先建存储过程   左边的浏览窗口选择 procedures ,...
  • Oracle存储过程以及调用

    千次阅读 2018-08-16 17:50:53
    刚开始学习储存过程,写下自己的一点总结,参考了这位大神的博客 贴上地址:https://blog.csdn.net/Harry_ZH_Wang/article/details/70975647 首先建一个表,名为t_2018jjr 表结构: 一、无参数的 create or ...
  • Java中通过jdbc调用oracle存储过程

    万次阅读 2018-05-17 00:09:39
    Java调用Oracle存储过程步骤主要分为三类: (1)编写Oracle存储过程 (2)编写数据库获取连接工具类 (3)编写简单应用调用存储过程 1、Oracle中的存储过程程序 1.在Oracle SQL Developer中创建存储过程 在scott...
  • Oracle存储过程基本语法介绍

    千次阅读 2016-05-31 18:49:27
    Oracle存储过程基本语法 存储过程   1 CREATE OR REPLACE PROCEDURE 存储过程名   2 IS   3 BEGIN   4 NULL;   5 END;  行1:   CREATE OR REPLACE PROCEDURE 是一个SQL语句通知Oracle数据库去...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 77,220
精华内容 30,888
关键字:

oracle存储过程未找到数据