精华内容
下载资源
问答
  • 存储过程怎么打印中位数
    万次阅读 多人点赞
    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 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()即可输出结果。

     

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

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

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    更多相关内容
  • bfptr算法(即中位数中位数算法)

    万次阅读 多人点赞 2018-08-25 22:35:16
    BFPRT算法是解决从n个数中选择第k大或第k小的这个经典问题的著名算法,但很多人并不了解其细节。本文将首先介绍求解这个第k小数字问题的几个思路,然后重点介绍在最坏情况下复杂度仍然为O(n)的BFPRT算法。 一 ...

    BFPRT算法是解决从n个数中选择第k大或第k小的数这个经典问题的著名算法,但很多人并不了解其细节。本文将首先介绍求解这个第k小数字问题的几个思路,然后重点介绍在最坏情况下复杂度仍然为O(n)的BFPRT算法。

    一 基本思路

    关于选择第k小的数字有许多方法,其效率和复杂度各不一样,可以根据实际情况进行选择。

    1. 将n个数排序(比如快速排序或归并排序),选取排序后的第k个数,时间复杂度为O(nlogn)。使用STL函数sort可以大大减少编码量。
    2. 将方法1中的排序方法改为线性时间排序算法(如基数排序或计数排序),时间复杂度为O(n)。但线性时间排序算法使用限制较多,不常使用。
    3. 维护一个k个元素的最大堆,存储当前遇到的最小的k个数,时间复杂度为O(nlogk)。这种方法同样适用于海量数据的处理。
    4. 部分的选择排序,即把最小的放在第1位,第二小的放在第2位,直到第k位为止,时间复杂度为O(kn)。实现非常简单。
    5. 部分的快速排序(快速选择算法),每次划分之后判断第k个数在左右哪个部分,然后递归对应的部分,平均时间复杂度为O(n)。但最坏情况下复杂度为O(n^2)。
    6. BFPRT算法,修改快速选择算法的主元选取规则,使用中位数的中位数作为主元,最坏情况下时间复杂度为O(n)

    二 快速选择算法

    快速选择算法就是修改之后的快速排序算法,前面快速排序的实现与应用这篇文章中讲了它的原理和实现。

    其主要思想就是在快速排序中得到划分结果之后,判断要求的第k个数是在划分结果的左边还是右边,然后只处理对应的那一部分,从而达到降低复杂度的效果。

    在快速排序中,平均情况下数组被划分成相等的两部分,则时间复杂度为T(n)=2*T(n/2)+O(n),可以解得T(n)=nlogn。
    在快速选择中,平均情况下数组也是分成相等的两部分,但是只处理其中一部分,于是T(n)=T(n/2)+O(n),可以解得T(n)=O(n)。

    但是两者在最坏情况下的时间复杂度均为O(n^2),出现在每次划分之后左右总有一边为空的情况下。为了避免这个问题,需要谨慎地选取划分的主元,一般的方法有:

    1. 固定选择首元素或尾元素作为主元。
    2. 随机选择一个元素作为主元。
    3. 三数取中,选择三个数的中位数作为主元。一般是首尾数,再加中间的一个数或者随机的一个数。

    ============================================================

    通常,我们需要在一大堆数中求前K大的数,或者求前K小的。比如在搜索引擎中求当天用户点击次数排名前10000的热词;在文本特征选择中求IF-IDF值按从大到小排名前K个的等等问题,都涉及到一个核心问题,即TOP-K问题

    通常来说,TOP-K问题可以先对所有数进行快速排序,然后取前K大的即可。但是这样做有两个问题。

    (1)快速排序的平均复杂度为,但最坏时间复杂度为,不能始终保证较好的复杂度。

    (2)我们只需要前K大的,而对其余不需要的数也进行了排序,浪费了大量排序时间。

    除这种方法之外,堆排序也是一个比较好的选择,可以维护一个大小为K的堆,时间复杂度为

    我们的目的是求前K大的或者前K小的元素,实际上有一个比较好的算法,叫做BFPTR算法,又称为中位数的中位数算法,它的最坏时间复杂度为,它是由Blum、Floyd、Pratt、Tarjan、Rivest 提出。

    该算法的思想是修改快速选择算法的主元选取方法,提高算法在最坏情况下的时间复杂度。我们先来看看快速排序是如何进行的。

    一趟快速排序的过程如下

    (1)先从序列中选取一个数作为基准数。

    (2)将比这个数大的数全部放到它的右边,把小于或者等于它的数全部放到它的左边。

    一趟快速排序也叫做Partion,即将序列划分为两部分,一部分比基准数小,另一部分比基准数大,然后再进行分治过程,因为每一次Partion不一定都能保证划分得很均匀,所以最坏情况下的时间复杂度不能保证总是为

    对于Partion过程,通常有两种方法:

    (1)两个指针从首尾向中间扫描(双向扫描)

     这种方法可以用挖坑填数来形容,比如

     

        

     

        初始化:i = 0; j = 9; pivot = a[0];

        现在a[0]保存到了变量pivot中了,相当于在数组a[0]处挖了个坑,那么可以将其它的数填到这里来。从j开始向前找一个小于或者等于pivot的数,即将a[8]填入a[0],但a[8]又形成了一个新坑,再从i开始向后找一个大于pivot的数,即a[3]填入a[8],那么a[3]又形成了一个新坑......

        就这样,直到i==j才停止,最终得到结果如下

     

        

     

        上述过程就是一趟快速排序

     

    代码:

    #include <iostream>
    #include <string.h>
    #include <stdio.h>
    #include <algorithm>
    #include <time.h>
    
    using namespace std;
    const int N = 10005;
    
    int Partion(int a[], int l, int r)
    {
    	int i = l;
    	int j = r;
    	int pivot = a[l];
    	while (i < j)
    	{
    		while (a[j] >= pivot && i < j)
    			j--;
    		a[i] = a[j];
    		while (a[i] <= pivot && i < j)
    			i++;
    		a[j] = a[i];
    	}
    	a[i] = pivot;
    	return i;
    }
    
    void QuickSort(int a[], int l, int r)
    {
    	if (l < r)
    	{
    		int k = Partion(a, l, r);
    		QuickSort(a, l, k - 1);
    		QuickSort(a, k + 1, r);
    	}
    }
    
    int a[N];
    
    int main()
    {
    	int n;
    	while (cin >> n)
    	{
    		for (int i = 0; i < n; i++)
    			cin >> a[i];
    		QuickSort(a, 0, n - 1);
    		for (int i = 0; i < n; i++)
    			cout << a[i] << " ";
    		cout << endl;
    	}
    	return 0;
    }

    (2)两个指针一前一后逐步向前扫描(单向扫描)

    代码:

    #include <iostream>  
    #include <string.h>  
    #include <stdio.h>  
       
    using namespace std;  
    const int N = 10005;  
       
    int Partion(int a[], int l, int r)  
    {  
        int i = l - 1;  
        int pivot = a[r];  
        for(int j = l; j < r; j++)  
        {  
            if(a[j] <= pivot)  
            {  
                i++;  
                swap(a[i], a[j]);  
            }  
        }  
        swap(a[i + 1], a[r]);  
        return i + 1;  
    }  
       
    void QuickSort(int a[], int l, int r)  
    {  
        if(l < r)  
        {  
            int k = Partion(a, l, r);  
            QuickSort(a, l, k - 1);  
            QuickSort(a, k + 1, r);  
        }  
    }  
       
    int a[N];  
       
    int main()  
    {  
        int n;  
        while(cin >> n)  
        {  
            for(int i = 0; i < n; i++)  
                cin >> a[i];  
            QuickSort(a, 0, n - 1);  
            for(int i = 0; i < n; i++)  
                cout << a[i] << " ";  
            cout << endl;  
        }  
        return 0;  
    }  

    实际上基于双向扫描的快速排序要比基于单向扫描的快速排序算法快很多。接下来,我们学习BFPTR算法的原理。

    BFPTR算法中,仅仅是改变了快速排序Partion中的pivot值的选取,在快速排序中,我们始终选择第一个元素或者最后一个元素作为pivot,而在BFPTR算法中,每次选择五分中位数的中位数作为pivot,这样做的目的就是使得划分比较合理,从而避免了最坏情况的发生。算法步骤如下:

    (1)将输入数组的个元素划分为组,每组5个元素,且至多只有一个组由剩下的个元素组成。

    (2)寻找个组中每一个组的中位数,首先对每组的元素进行插入排序,然后从排序过的序列中选出中位数。

    (3)对于(2)中找出的个中位数,递归进行步骤(1)和(2),直到只剩下一个数即为这个元素的中位数,找到中位数后并找到对应的下标

    (4)进行Partion划分过程,Partion划分中的pivot元素下标为

    (5)进行高低区判断即可。

    本算法的最坏时间复杂度为,值得注意的是通过BFPTR算法将数组按第K小(大)的元素划分为两部分,而这高低两部分不一定是有序的,通常我们也不需要求出顺序,而只需要求出前K大的或者前K小的。

    另外注意一点,求第K大就是求第n-K+1小,这两者等价。TOP K问题在工程中有重要应用,所以很有必要掌握。

    代码:

    #include <iostream>  
    #include <string.h>  
    #include <stdio.h>  
    #include <time.h>  
    #include <algorithm>  
       
    using namespace std;  
    const int N = 10005;  
       
    int a[N];  
       
    //插入排序  
    void InsertSort(int a[], int l, int r)  
    {  
        for(int i = l + 1; i <= r; i++)  
        {  
            if(a[i - 1] > a[i])  
            {  
                int t = a[i];  
                int j = i;  
                while(j > l && a[j - 1] > t)  
                {  
                    a[j] = a[j - 1];  
                    j--;  
                }  
                a[j] = t;  
            }  
        }  
    }  
       
    //寻找中位数的中位数  
    int FindMid(int a[], int l, int r)  
    {  
        if(l == r) return a[l];  
        int i = 0;  
        int n = 0;  
        for(i = l; i < r - 5; i += 5)  
        {  
            InsertSort(a, i, i + 4);  
            n = i - l;  
            swap(a[l + n / 5], a[i + 2]);  
        }  
       
        //处理剩余元素  
        int num = r - i + 1;  
        if(num > 0)  
        {  
            InsertSort(a, i, i + num - 1);  
            n = i - l;  
            swap(a[l + n / 5], a[i + num / 2]);  
        }  
        n /= 5;  
        if(n == l) return a[l];  
        return FindMid(a, l, l + n);  
    }  
       
    //寻找中位数的所在位置  
    int FindId(int a[], int l, int r, int num)  
    {  
        for(int i = l; i <= r; i++)  
            if(a[i] == num)  
                return i;  
        return -1;  
    }  
       
    //进行划分过程  
    int Partion(int a[], int l, int r, int p)  
    {  
        swap(a[p], a[l]);  
        int i = l;  
        int j = r;  
        int pivot = a[l];  
        while(i < j)  
        {  
            while(a[j] >= pivot && i < j)  
                j--;  
            a[i] = a[j];  
            while(a[i] <= pivot && i < j)  
                i++;  
            a[j] = a[i];  
        }  
        a[i] = pivot;  
        return i;  
    }  
       
    int BFPTR(int a[], int l, int r, int k)  
    {  
        int num = FindMid(a, l, r);    //寻找中位数的中位数  
        int p =  FindId(a, l, r, num); //找到中位数的中位数对应的id  
        int i = Partion(a, l, r, p);  
       
        int m = i - l + 1;  
        if(m == k) return a[i];  
        if(m > k)  return BFPTR(a, l, i - 1, k);  
        return BFPTR(a, i + 1, r, k - m);  
    }  
       
    int main()  
    {  
        int n, k;  
        scanf("%d", &n);  
        for(int i = 0; i < n; i++)  
            scanf("%d", &a[i]);  
        scanf("%d", &k);  
        printf("The %d th number is : %d\n", k, BFPTR(a, 0, n - 1, k));  
        for(int i = 0; i < n; i++)  
            printf("%d ", a[i]);  
        puts("");  
        return 0;  
    }  
       
    /** 
    10 
    72 6 57 88 60 42 83 73 48 85 
    5 
    */  

    关于本算法最坏时间复杂度为的证明可以参考《算法导论》9.3节,即112页,有详细分析。

     

    原文链接:https://blog.csdn.net/wyq_tc25/article/details/51801885

    展开全文
  • MySQL存储过程中的3种循环

    千次阅读 2018-01-09 19:30:00
    MySQL存储过程中的3种循环,存储过程的基本语法,ORACLE与MYSQL的存储过程/函数的使用区别,退出存储过程方法   在MySQL存储过程的语句中有三个标准的循环方式:WHILE循环,LOOP循环以及REPEAT循环。还有一种非标准...

    MySQL存储过程中的3种循环,存储过程的基本语法,ORACLE与MYSQL的存储过程/函数的使用区别,退出存储过程方法

     

    在MySQL存储过程的语句中有三个标准的循环方式:WHILE循环,LOOP循环以及REPEAT循环。还有一种非标准的循环方式:GOTO,不过这种循环方式最好别用,很容易引起程序的混乱,在这里就不错具体介绍了。

    这几个循环语句的格式如下:

    • WHILE……DO……END WHILE
    • REPEAT……UNTIL END REPEAT
    • LOOP……END LOOP
    • GOTO

        下面首先使用第一种循环编写一个例子。

    复制代码
    mysql> create procedure pro10()
        -> begin
        -> declare i int;
        -> set i=0;
        -> while i<5 do
        ->     insert into t1(filed) values(i);
        ->     set i=i+1;
        -> end while;
        -> end;//
    复制代码

    Query OK, 0 rows affected (0.00 sec)
        在这个例子中,INSERT和SET语句在WHILE和END WHILE之间,当变量i大于等于5的时候就退出循环。使用set i=0;语句是为了防止一个常见的错误,如果没有初始化,i默认变量值为NULL,而NULL和任何值操作的结果都是NULL。
        执行一下这个存储过程并产看一下执行结果:
    mysql> delete from t1//
    Query OK, 0 rows affected (0.00 sec)
    mysql> call pro10()//
    Query OK, 1 row affected (0.00 sec)
    mysql> select * from t1//
    +——-+
    | filed |
    +——-+
    |     0 |
    |     1 |
    |     2 |
    |     3 |
    |     4 |
    +——-+
    5 rows in set (0.00 sec)
        以上就是执行结果,有5行数据插入到数据库中,证明存储过程编写正确无误^_^。

        再来看一下第二个循环控制指令 REPEAT……END REPEAT。使用REPEAT循环控制语句编写下面这个存储过程:

    复制代码
    mysql> create procedure pro11()
        -> begin
        -> declare i int default 0;
        -> repeat
        ->     insert into t1(filed) values(i);
        ->     set i=i+1;
        ->     until i>=5
        -> end repeat;
        -> end;//
    复制代码

    Query OK, 0 rows affected (0.00 sec)
        这个REPEAT循环的功能和前面WHILE循环一样,区别在于它的执行后检查是否满足循环条件(until i>=5),而WHILE则是执行前检查(while i<5 do)。
        不过要注意until i>=5后面不要加分号,如果加分号,就是提示语法错误。
        编写完成后,调用一下这个存储过程,并查看结果:
    mysql> delete from t1//
    Query OK, 5 rows affected (0.00 sec)

    mysql> call pro11()//
    Query OK, 1 row affected (0.00 sec) #虽然在这里显示只有一行数据受到影响,但是下面选择数据的话,还是插入了5行数据。

    mysql> select * from t1//
    +——-+
    | filed |
    +——-+
    |     0 |
    |     1 |
    |     2 |
    |     3 |
    |     4 |
    +——-+
    5 rows in set (0.00 sec)
    一行就是执行结果,实际的作用和使用while编写的存储过程一样,都是插入5行数据。

    再来看一下第三个循环控制语句LOOP……END LOOP。编写一个存储过程程序如下:

    复制代码
    mysql> create procedure pro12()
        -> begin
        -> declare i int default 0;
        -> loop_label: loop
        ->     insert into t1(filed) values(i);
        ->     set i=i+1;
        ->     if i>=5 then
        ->         leave loop_label;
        ->     end if;
        -> end loop;
        -> end;//
    复制代码
    Query OK, 0 rows affected (0.00 sec)
    从上面这个例子可以看出,使用LOOP编写同样的循环控制语句要比使用while和repeat编写的要复杂一些:在循环内部加入了IF……END IF语句,在IF语句中又加入了LEAVE语句,LEAVE语句的意思是离开循环,LEAVE的格式是:LEAVE 循环标号。
        编写完存储过程程序后,来执行并查看一下运行结果:
    mysql> delete from t1//
    Query OK, 5 rows affected (0.00 sec)

     

    mysql> call pro12//
    Query OK, 1 row affected (0.00 sec) #虽然说只有一行数据受影响,但是实际上是插入了5行数据。

    mysql> select * from t1//
    +——-+
    | filed |
    +——-+
    |     0 |
    |     1 |
    |     2 |
    |     3 |
    |     4 |
    +——-+
    5 rows in set (0.00 sec)
        执行结果和使用WHILE、LOOP编写的循环一样,都是往标中插入5行值。

       Labels   标号和 END Labels 结束标号
       在使用loop的时候,使用到的labels标号,对于labels可以用到while,loop,rrepeat等循环控制语句中。而且有必要好好认识一下lables!!
    mysql> create procedure pro13()
        -> label_1:begin
        -> label_2:while 0=1 do leave label_2;end while;
        -> label_3:repeat leave label_3;until 0=0 end repeat;
        -> label_4:loop leave label_4;end loop;
        -> end;//
    Query OK, 0 rows affected (0.00 sec)
        上面这里例子显示了可以在BEGIN、WHILE、REPEAT或者LOOP语句前使用语句标号,语句标号只能在合法的语句前使用,所以LEAVE label_3意味着离开语句标号名为label_3的语句或符合语句。
        其实,也可以使用END labels来表示标号结束符。
    mysql> create procedure pro14()
        -> label_1:begin
        -> label_2:while 0=1 do leave label_2;end while label_2;
        -> label_3:repeat leave label_3;until 0=0 end repeat label_3;
        -> label_4:loop leave label_4;end loop label_4;
        -> end label_1;//
    Query OK, 0 rows affected (0.00 sec)
        上面就是使用了标号结束符,其实这个结束标号并不是十分有用,而且他必须和开始定义的标号名字一样,否则就会报错。如果要养成一个良好的编程习惯方便他人阅读的话,可以使用这个标号结束符。

    ITERATE 迭代        
         如果是在ITERATE语句,即迭代语句中的话,就必须使用LEAVE语句。ITERATE只能出现在LOOP,REPEAT和WHILE语句中,它的意思是“再次循环”,例如:
    mysql> create procedure pro15()

     

        -> begin
        -> declare i int default 0;
        -> loop_label:loop
        ->     if i=3 then
        ->         set i=i+1;
        ->         iterate loop_label;
        ->     end if;
        ->     insert into t1(filed) values(i);
        ->     set i=i+1;
        ->     if i>=5 then
        ->        leave loop_label;
        ->     end if;
        ->   end loop;
        -> end;//
    Query OK, 0 rows affected (0.00 sec)
        iterate语句和leave语句一样,也是在循环内部使用,它有点类似于C/C++语言中的continue。
        那么这个存储程序是怎么运行的的?首先i的值为0,条件判断语句if i=3 then判断为假,跳过if语段,向数据库中插入0,然后i+1,同样后面的if i>=5 then判断也为假,也跳过;继续循环,同样插入1和2;在i=3的时候条件判断语句if i=3 then判断为真,执行i=i+1,i值为4,然后执行迭代iterate loop_label;,即语句执行到iterate loop_label;后直接跳到if i=3 then判断语句,执行判断,这个时候由于i=4,if i=3 then判断为假,跳过IF语段,将4添加到表中,i变为5,条件判断if i>=5 then判断为真,执行leave loop_label;跳出loop循环,然后执行end;//,结束整个存储过程。
        综上所述,数据库中将插入数值:0,1,2,4。执行存储过程,并查看结果:|
    mysql> delete from t1//
    Query OK, 5 rows affected (0.00 sec)

     

    mysql> call pro15//
    Query OK, 1 row affected (0.00 sec)

    mysql> select * from t1//
    +——-+
    | filed |
    +——-+
    |     0 |
    |     1 |
    |     2 |
    |     4 |
    +——-+
    4 rows in set (0.00 sec)

    和我们上面分析的结果一样,只插入了数值0,1,2,4。

     

    存储过程如同一门程序设计语言,同样包含了数据类型、流程控制、输入和输出和它自己的函数库。


    --------------------基本语法--------------------

    一.创建存储过程
    create procedure sp_name()
    begin
    .........
    end

    二.调用存储过程
    1.基本语法:call sp_name()
    注意:存储过程名称后面必须加括号,哪怕该存储过程没有参数传递

    三.删除存储过程
    1.基本语法:
    drop procedure sp_name//

    2.注意事项
    (1)不能在一个存储过程中删除另一个存储过程,只能调用另一个存储过程

    四.其他常用命令

    1.show procedure status
    显示数据库中所有存储的存储过程基本信息,包括所属数据库,存储过程名称,创建时间等

    2.show create procedure sp_name
    显示某一个MySQL存储过程的详细信息


    --------------------数据类型及运算符--------------------
    一、基本数据类型:

    二、变量:

    自定义变量:DECLARE   a INT ; SET a=100;    可用以下语句代替:DECLARE a INT DEFAULT 100;

    变量分为用户变量系统变量,系统变量又分为会话和全局级变量

    用户变量:用户变量名一般以@开头,滥用用户变量会导致程序难以理解及管理

    1、 在mysql客户端使用用户变量
    mysql> SELECT 'Hello World' into @x;
    mysql> SELECT @x;

    mysql> SET @y='Goodbye Cruel World';
    mysql> select @y;

    mysql> SET @z=1+2+3;
    mysql> select @z;


    2、 在存储过程中使用用户变量

    mysql> CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(@greeting,' World');
    mysql> SET @greeting='Hello';
    mysql> CALL GreetWorld( );


    3、 在存储过程间传递全局范围的用户变量
    mysql> CREATE PROCEDURE p1( )   SET @last_procedure='p1';
    mysql> CREATE PROCEDURE p2( ) SELECT CONCAT('Last procedure was ',@last_procedure);
    mysql> CALL p1( );
    mysql> CALL p2( );

     

    三、运算符:
    1.算术运算符
    +     加   SET var1=2+2;       4
    -     减   SET var2=3-2;       1
    *      乘   SET var3=3*2;       6
    /     除   SET var4=10/3;      3.3333
    DIV   整除 SET var5=10 DIV 3; 3
    %     取模 SET var6=10%3 ;     1

    2.比较运算符
    >            大于 1>2 False
    <            小于 2<1 False
    <=           小于等于 2<=2 True
    >=           大于等于 3>=2 True
    BETWEEN      在两值之间 5 BETWEEN 1 AND 10 True
    NOT BETWEEN 不在两值之间 5 NOT BETWEEN 1 AND 10 False
    IN           在集合中 5 IN (1,2,3,4) False
    NOT IN       不在集合中 5 NOT IN (1,2,3,4) True
    =             等于 2=3 False
    <>, !=       不等于 2<>3 False
    <=>          严格比较两个NULL值是否相等 NULL<=>NULL True
    LIKE          简单模式匹配 "Guy Harrison" LIKE "Guy%" True
    REGEXP       正则式匹配 "Guy Harrison" REGEXP "[Gg]reg" False
    IS NULL      为空 0 IS NULL False
    IS NOT NULL 不为空 0 IS NOT NULL True

    3.逻辑运算符

    4.位运算符
    |   或
    &   与
    << 左移位
    >> 右移位
    ~   非(单目运算,按位取反)

    注释:

    mysql存储过程可使用两种风格的注释
    双横杠:--

    该风格一般用于单行注释
    c风格:/* 注释内容 */ 一般用于多行注释

    --------------------流程控制--------------------
    一、顺序结构
    二、分支结构

    if
    case

    三、循环结构
    for循环
    while循环
    loop循环
    repeat until循环

    注:
    区块定义,常用
    begin
    ......
    end;
    也可以给区块起别名,如:
    lable:begin
    ...........
    end lable;
    可以用leave lable;跳出区块,执行区块以后的代码。

    可以提前退出存储过程(函数直接return)。

    begin和end如同C语言中的{ 和 }。

    --------------------输入和输出--------------------

    mysql存储过程的参数用在存储过程的定义,共有三种参数类型,IN,OUT,INOUT
    Create procedure|function([[IN |OUT |INOUT ] 参数名 数据类形...])

    IN 输入参数
    表示该参数的值必须在调用存储过程时指定,在存储过程中修改该参数的值不能被返回,为默认值

    OUT 输出参数
    该值可在存储过程内部被改变,并可返回

    INOUT 输入输出参数
    调用时指定,并且可被改变和返回

    IN参数例子:
    CREATE PROCEDURE sp_demo_in_parameter(IN p_in INT)
    BEGIN
    SELECT p_in; --查询输入参数
    SET p_in=2; --修改
    select p_in;--查看修改后的值
    END;

    执行结果:
    mysql> set @p_in=1
    mysql> call sp_demo_in_parameter(@p_in)

    mysql> select @p_in;

    以上可以看出,p_in虽然在存储过程中被修改,但并不影响@p_id的值

    OUT参数例子
    创建:
    mysql> CREATE PROCEDURE sp_demo_out_parameter(OUT p_out INT)
    BEGIN
    SELECT p_out;/*查看输出参数*/
    SET p_out=2;/*修改参数值*/
    SELECT p_out;/*看看有否变化*/
    END;

    执行结果:
    mysql> SET @p_out=1
    mysql> CALL sp_demo_out_parameter(@p_out)

    mysql> SELECT @p_out;

    INOUT参数例子:
    mysql> CREATE PROCEDURE sp_demo_inout_parameter(INOUT p_inout INT)
    BEGIN
    SELECT p_inout;
    SET p_inout=2;
    SELECT p_inout;
    END;

    执行结果:
    set @p_inout=1
    call sp_demo_inout_parameter(@p_inout) //

    select @p_inout;

     

     

    附:函数库
    mysql存储过程基本函数包括:字符串类型,数值类型,日期类型

    一、字符串类
    CHARSET(str) //返回字串字符集
    CONCAT (string2 [,… ]) //连接字串
    INSTR (string ,substring ) //返回substring首次在string中出现的位置,不存在返回0
    LCASE (string2 ) //转换成小写
    LEFT (string2 ,length ) //从string2中的左边起取length个字符
    LENGTH (string ) //string长度
    LOAD_FILE (file_name ) //从文件读取内容
    LOCATE (substring , string [,start_position ] ) 同INSTR,但可指定开始位置
    LPAD (string2 ,length ,pad ) //重复用pad加在string开头,直到字串长度为length
    LTRIM (string2 ) //去除前端空格
    REPEAT (string2 ,count ) //重复count次
    REPLACE (str ,search_str ,replace_str ) //在str中用replace_str替换search_str
    RPAD (string2 ,length ,pad) //在str后用pad补充,直到长度为length
    RTRIM (string2 ) //去除后端空格
    STRCMP (string1 ,string2 ) //逐字符比较两字串大小,
    SUBSTRING (str , position [,length ]) //从str的position开始,取length个字符,
    注:mysql中处理字符串时,默认第一个字符下标为1,即参数position必须大于等于1
    mysql> select substring(’abcd’,0,2);
    +———————–+
    | substring(’abcd’,0,2) |
    +———————–+
    |                       |
    +———————–+
    1 row in set (0.00 sec)

    mysql> select substring(’abcd’,1,2);
    +———————–+
    | substring(’abcd’,1,2) |
    +———————–+
    | ab                    |
    +———————–+
    1 row in set (0.02 sec)

    TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //去除指定位置的指定字符
    UCASE (string2 ) //转换成大写
    RIGHT(string2,length) //取string2最后length个字符
    SPACE(count) //生成count个空格

    二、数值类型

    ABS (number2 ) //绝对值
    BIN (decimal_number ) //十进制转二进制
    CEILING (number2 ) //向上取整
    CONV(number2,from_base,to_base) //进制转换
    FLOOR (number2 ) //向下取整
    FORMAT (number,decimal_places ) //保留小数位数
    HEX (DecimalNumber ) //转十六进制
    注:HEX()中可传入字符串,则返回其ASC-11码,如HEX(’DEF’)返回4142143
    也可以传入十进制整数,返回其十六进制编码,如HEX(25)返回19
    LEAST (number , number2 [,..]) //求最小值
    MOD (numerator ,denominator ) //求余
    POWER (number ,power ) //求指数
    RAND([seed]) //随机数
    ROUND (number [,decimals ]) //四舍五入,decimals为小数位数]

    注:返回类型并非均为整数,如:

    (1)默认变为整形值
    mysql> select round(1.23);
    +————-+
    | round(1.23) |
    +————-+
    |           1 |
    +————-+
    1 row in set (0.00 sec)

    mysql> select round(1.56);
    +————-+
    | round(1.56) |
    +————-+
    |           2 |
    +————-+
    1 row in set (0.00 sec)

    (2)可以设定小数位数,返回浮点型数据

    mysql> select round(1.567,2);
    +—————-+
    | round(1.567,2) |
    +—————-+
    |           1.57 |
    +—————-+
    1 row in set (0.00 sec)

    SIGN (number2 ) //返回符号,正负或0
    SQRT(number2) //开平方

    三、日期类型

    ADDTIME (date2 ,time_interval ) //将time_interval加到date2
    CONVERT_TZ (datetime2 ,fromTZ ,toTZ ) //转换时区
    CURRENT_DATE ( ) //当前日期
    CURRENT_TIME ( ) //当前时间
    CURRENT_TIMESTAMP ( ) //当前时间戳
    DATE (datetime ) //返回datetime的日期部分
    DATE_ADD (date2 , INTERVAL d_value d_type ) //在date2中加上日期或时间
    DATE_FORMAT (datetime ,FormatCodes ) //使用formatcodes格式显示datetime
    DATE_SUB (date2 , INTERVAL d_value d_type ) //在date2上减去一个时间
    DATEDIFF (date1 ,date2 ) //两个日期差
    DAY (date ) //返回日期的天
    DAYNAME (date ) //英文星期
    DAYOFWEEK (date ) //星期(1-7) ,1为星期天
    DAYOFYEAR (date ) //一年中的第几天
    EXTRACT (interval_name FROM date ) //从date中提取日期的指定部分
    MAKEDATE (year ,day ) //给出年及年中的第几天,生成日期串
    MAKETIME (hour ,minute ,second ) //生成时间串
    MONTHNAME (date ) //英文月份名
    NOW ( ) //当前时间
    SEC_TO_TIME (seconds ) //秒数转成时间
    STR_TO_DATE (string ,format ) //字串转成时间,以format格式显示
    TIMEDIFF (datetime1 ,datetime2 ) //两个时间差
    TIME_TO_SEC (time ) //时间转秒数]
    WEEK (date_time [,start_of_week ]) //第几周
    YEAR (datetime ) //年份
    DAYOFMONTH(datetime) //月的第几天
    HOUR(datetime) //小时
    LAST_DAY(date) //date的月的最后日期
    MICROSECOND(datetime) //微秒
    MONTH(datetime) //月
    MINUTE(datetime) //分

    注:可用在INTERVAL中的类型:DAY ,DAY_HOUR ,DAY_MINUTE ,DAY_SECOND ,HOUR ,HOUR_MINUTE ,HOUR_SECOND ,MINUTE ,MINUTE_SECOND,MONTH ,SECOND ,YEAR
    DECLARE variable_name [,variable_name...] datatype [DEFAULT value]; 
    其中,datatype为mysql的数据类型,如:INT, FLOAT, DATE, VARCHAR(length)

    例:

    DECLARE l_int INT unsigned default 4000000; 
    DECLARE l_numeric NUMERIC(8,2) DEFAULT 9.95; 
    DECLARE l_date DATE DEFAULT '1999-12-31'; 
    DECLARE l_datetime DATETIME DEFAULT '1999-12-31 23:59:59';
    DECLARE l_varchar VARCHAR(255) DEFAULT 'This will not be padded';

     ORACLE与MYSQL的存储过程/函数的使用区别

    • 编号类别ORACLEMYSQL注释
      1创建存储过程语句不同create or replace procedure P_ADD_FAC(
         id_fac_cd  IN ES_FAC_UNIT.FAC_CD%TYPEis
      DROP PROCEDURE IF EXISTS `SD_USER_P_ADD_USR`;
      create procedure P_ADD_FAC(
             id_fac_cd  varchar(100))

      1.在创建存储过程时如果存在同名的存储过程,会删除老的存储过程. 
        oracle使用create or replace.
        mysql使用先删除老的存储过程,然后再创建新的存储过程.
      2. oracle 存储过程可以定义在package中,也可以定义在Procedures中. 如果定义在包中,一个包中可以包含多个存储过程和方法.如果定义在Procedures中,存储过程中不可以定义多个存储过程. 
         Mysql  存储过程中不可以定义多个存储过程. 
      3. oracle中字符串类型可以使用varchar2.  
         Mysql 需要使用varchar
      4. Oracle中参数varchar长度不是必须的,
         Mysql中参数varchar长度是必须的, 比如varchar(100) 
      2创建函数语句不同CREATE OR REPLACEFUNCTION F_ROLE_FACS_GRP(
           ii_role_int_key IN SD_ROLE.ROLE_INT_KEY%TYPE
          ) RETURN VARCHAR2
      DROP FUNCTION IF EXISTS `SD_ROLE_F_ROLE_FACS_GRP`;
      CREATE  FUNCTION `SD_ROLE_F_ROLE_FACS_GRP`(
       ii_role_int_key INTEGER(10)
      RETURNS varchar(1000) 
      1.在创建函数时如果存在同名的函数,会删除老的函数.  
        oracle使用create or replace.
        mysql使用先删除老的函数,然后再创建新的函数.
      2. oracle 函数可以定义在package中,也可以定义在Functions中. 如果定义在包中,一个包中可以包含多个存储过程和函数.如果定义在Functions中,每个函数只能定义一个函数.
         Mysql  Functions不可以定义多个函数. 
      3.  oracle返回值用return. 
          Mysql返回值用returns. 
      3传入参数写法不同procedure P_ADD_FAC(
         id_fac_cd  IN ES_FAC_UNIT.FAC_CD%TYPE)
      create procedure P_ADD_FAC(
           (in) id_fac_cd  varchar(100))

      1. oracle存储过程参数可以定义为表的字段类型.
         Mysql存储过程不支持这种定义方法.需要定义变量的实际类型和长度.
      2. oracle 参数类型in/out/inout写在参数名后面. 
         Mysql  参数类型in/out/inout写在参数名前面.
      3. oracle 参数类型in/out/inout 都必须写.
         Mysql  参数类型如果是in,则可以省略. 如果是out或inout则不能省略.
      注意: mysql中指定参数为IN, OUT, 或INOUT 只对PROCEDURE是合法的。(FUNCTION参数总是被认为是IN参数) RETURNS字句只能对FUNCTION做指定,对函数而言这是强制的。它用来指定函数的返回类型,而且函数体必须包含一个RETURN value语句。  
      function func_name(
                 gw_id  in(out)  varchar2 )
      create function func_name(
             gw_id varchar100))
      4包的声明方式create or replace package/package body package name拆分成多个存储过程或函数oracle可以创建包,包中可以包含多个存储过程和方法. 
      mysql没有没有包这个概念,可以分别创建存储过程和方法. 每个存储过程或方法都需要放在一个文件中. 
      例1: 方法命名 
      oracle 中SD_FACILITY_PKG.F_SEARCH_FAC 
      to mysql SD_FACILITY_F_SEARCH_FAC 
      例2: 过程命名
      oracle 中SD_FACILITY_PKG.P_ADD_FAC
      to mysql SD_FACILITY_P_ADD_FAC 
      5存储过程返回语句不一样return;LEAVE proc; (proc 代表最外层的begin end)oracle存储过程和方法都可以使用return退出当前过程和方法. 
      Mysql存储过程中只能使用leave退出当前存储过程.不可以使用return. 
      Mysql方法可以使用return退出当前方法. 
      6存储过程异常处理不一样EXCEPTION
          WHEN OTHERS THEN
          ROLLBACK ;
          ov_rtn_msg := c_sp_name||'('|| li_debug_pos ||'):'||
              TO_CHAR(SQLCODE)||': '||SUBSTR(SQLERRM,1,100);
      DECLARE EXIT HANDLER FOR  SQLEXCEPTION 
       BEGIN
          ROLLBACK ;
          set ov_rtn_msg = concat(c_sp_name,'(', li_debug_pos ,'):',
              TO_CHAR(SQLCODE),': ',SUBSTR(SQLERRM,1,100));
       END;
      oracle : 内部异常不需要定义,在存储过程或函数末尾写上EXCEPTION后,后面的部分即为异常处理的部分.  oracle可以定义自定义异常,自定义异常需要使用raise关键字抛出异常后,才可以在EXCEPTION中捕获.

      mysql: mysql内部异常也需要先定义,在定义的同时也需要实现异常的功能. 
                目前mysql不支持自定义异常. 
      7过程和函数的声明变量的位置不同声明变量在begin…end体之前声明变量在begin...end体内,begin之后其他任何内容之前 
      8NO_DATA_FOUND异常处理 EXCEPTION
              WHEN NO_DATA_FOUND THEN
                  oi_rtn_cd := 1;
                  ov_rtn_msg := SD_COMMON.P_GET_MSG('DP-CBM-01100a-016',
                                                       li_sub_rtn_cd,
                                                       lv_sub_rtn_msg
                                                       );
      使用FOUND_ROWS()代替NO_DATA_FOUND. 详见注释.oracle中: 
      NO_DATA_FOUND是游标的一个属性. 
      当select没有查到数据就会出现 no data found 的异常,程序不会向下执行.

      Mysql: 
      没有NO_DATA_FOUND这个属性.但可是使用FOUND_ROWS()方法得到select语句查询出来的数据.如果FOUND_ROWS()得到的值为0,就进入异常处理逻辑. 
      9在存储过程中调用存储过程方式的不同Procedure_Name(参数);Call Procedure_Name(参数);MYSQL存储过程调用存储过程,需要使用Call pro_name(参数).  
      Oracle调用存储过程直接写存储过程名就可以了. 
      10抛异常的方式不同RAISE Exception_Name;见备注详见<<2009002-OTMPPS-Difficult Questions-0001.doc>>中2.5 Mysql异常处理部分
       
    • 异常处理

    • MySQL的GET DIAGNOSTICS语句

      这是一个把我困扰已久的问题,今天偶然间解决了。

      以前用Oracle时经常会用到的三个东西:sql%rowcount、SQLCODE、SQLERRM
      sql%rowcount用于记录最近一条DML语句修改的记录条数,就如你在sqlplus下执行delete from之后提示已删除xx行一样。
      SQLCODE和SQLERRM是Oracle的异常处理函数,常被用于得到完整错误提示信息,方便错误时处理。

      那么问题来了,MySQL有没有相似的功能呢?以前查了很久也没有找到好的解决办法,然而在5.6.4以后,MySQL提供了GET DIAGNOSTICS语法,那么我的问题也随之迎刃而解。

      简单讲GET DIAGNOSTICS能提供以下两种信息:
      语句信息,例如错误信息号或者语句影响的行数。
      错误信息,例如错误号和错误消息。

      如果一条语句产生了三种错误,诊断区域包含的语句和错误信息类似这样:

       

      Statement information: row count ... other statement information items ... Condition area list: Condition area 1: error code for condition 1 error message for condition 1 ... other condition information items ... Condition area 2: error code for condition 2: error message for condition 2 ... other condition information items ... Condition area 3: error code for condition 3 error message for condition 3 ... other condition information items ...

      使用GET DIAGNOSTICS需要注意的是,它或者包含语句信息,或者包含错误信息,但一个GET DIAGNOSTICS不会同时包含语句信息和错误信息,所以需要用两个GET DIAGNOSTICS来获得语句信息和错误信息。

      获得语句信息:
      GET DIAGNOSTICS @p1 = NUMBER, @p2 = ROW_COUNT;

      获得错误信息:
      GET DIAGNOSTICS CONDITION 1 @p3 = RETURNED_SQLSTATE, @p4 = MESSAGE_TEXT;

      语句信息条目名称有:
      NUMBER 
      | ROW_COUNT

      错误信息条目名称有:
      CLASS_ORIGIN 
      | SUBCLASS_ORIGIN
      | RETURNED_SQLSTATE
      | MESSAGE_TEXT
      | MYSQL_ERRNO
      | CONSTRAINT_CATALOG
      | CONSTRAINT_SCHEMA
      | CONSTRAINT_NAME
      | CATALOG_NAME
      | SCHEMA_NAME
      | TABLE_NAME
      | COLUMN_NAME
      | CURSOR_NAME

      为了确保获得正确的主错误信息,必须使用类似如下的语句:
      GET DIAGNOSTICS @cno = NUMBER;
      GET DIAGNOSTICS CONDITION @cno @errno = MYSQL_ERRNO;

      最后来看一个使用GET DIAGNOSTICS完整例子(摘自官方文档)
    • 复制代码
      BEGIN 
      -- Declare variables to hold diagnostics area information 
      DECLARE code CHAR(5) DEFAULT '00000'; 
      DECLARE msg TEXT; 
      DECLARE rows INT; 
      DECLARE result TEXT; 
      
      -- Declare exception handler for failed insert 
      DECLARE CONTINUE HANDLER FOR SQLEXCEPTION 
      BEGIN 
      GET DIAGNOSTICS CONDITION 1 code = RETURNED_SQLSTATE, msg = MESSAGE_TEXT; 
      END; 
      -- Perform the insert 
      INSERT INTO t1 (int_col) VALUES(value); 
      
      -- Check whether the insert was successful 
      IF code = '00000' THEN 
      GET DIAGNOSTICS rows = ROW_COUNT; 
      SET result = CONCAT('insert succeeded, row count = ',rows); 
      ELSE 
      SET result = CONCAT('insert failed, error = ',code,', message = ',msg); 
      END IF; 
      -- Say what happened 
      SELECT result; 
      END
    展开全文
  • sql 存储过程

    千次阅读 2018-11-11 21:16:54
    一、存储过程概念 二、存储过程的创建 三. 修改和删除存储过程 四、存储过程的种类 一、存储过程概念 1.概念 存储过程是存放在数据库服务器上的预先定义与编译好的T-SQL语句集合,是一个独立的数据库对象。 2...

    目录

    一、存储过程概念

    二、存储过程的创建

    三. 修改和删除存储过程

    四、存储过程的种类


    一、存储过程概念

    1.概念

    存储过程是存放在数据库服务器上的预先定义与编译好的T-SQL语句集合,是一个独立的数据库对象。

    2.特点

    实现了模块化编程。
    存储过程具有对数据库立即访问的功能。
    使用存储过程可以加快程序的运行速度。
    使用存储过程可以减少网络流量。
    使用存储过程可以提高数据库的安全性。
    存储过程由应用程序激活,而不是由系统自动执行
    存储过程可以接受输入参数和返回值。
     

    二、存储过程的创建

    存储过程的定义主要包括两部分:一是过程名及参数的说明;二是过程体的说明。创建存储过程的语句一般形式如下:

    CREATE procedure<存储过程名>
           [<参数列表>]
           AS
           <过程体> 
    

    说明:
    ⑴ 参数列表:由一个或多个参数说明组成,每个参数说明包括
                参数名和参数的数据类型。当然,存储过程可以
                没有任何参数。 
                参数格式为: 参数名 数据类型 [=缺省值] [OUTPUT]
    ⑵ 过程体:是实现存储过程功能的一组T-SQL语句,可以包含任意多的SQL语句。
        ①但sql语句中不能使用CREATE(VIEW、TRIGER、DEFAULT、RULE、PROCEDURE等)语句,
          同时要慎重使用其他的CREATE、DROP等语句。
        ②为了使存储过程的设计更方便,功能更强大。可使用流程控制语句,主要有以下几种:
        · 赋值语句。可将SQL表达式的值赋值给局部变量。
        · 分支语句。用于设计分支程序。如: IF语句、CASE语句等。
        · 循环语句。如:WHILE等语句。
        · 调用存储过程语句CALL,或EXECUTE和从存储过程返回语句RETURN。
        ③在存储体中除了可以使用流程控制语句外,还可以使用游标。
     

    例1 创建一个不带参数的存储过程,完成查询每位学生的选课情况及其成绩。

    use exp6;
    go
    CREATE procedure Proc1 
    AS
        SELECT Student.Sno,Sname,Course.Cno,Cname,Grade  
             FROM Student,Course,cj
             WHERE Student.Sno=cj.Sno AND Course.Cno=cj.Cno
    GO
    
    execute Proc1 --执行存储过程

    最后一句运行结果:

     

    例2  创建一个带参数的存储过程,完成查询指定课程的选修情况。

    go
    CREATE PROCEDURE Proc2 @x CHAR(2)
    AS
    	SELECT Student.Sno,Sname,Course.Cno,Cname,Grade  
    	FROM Student,Course,Cj
    	WHERE Student.Sno=Cj.Sno AND Course.Cno=Cj.Cno and Cj.Cno=@x 
    	ORDER by Cj.Sno
    GO
    -- 2号课程选修情况
    execute Proc2 2

    最后一句运行结果:

    例3  创建一个带参数并有返回值的存储过程,完成输出指定学号的学生的‘2’号课程的成绩,并将结果,赋给一输出参数。 

    go
    CREATE PROCEDURE Proc3 
        @x CHAR(10),@vgrade INT OUTPUT --输出参数
    AS 
        SELECT @vgrade=Grade FROM cj
             WHERE Sno=@x AND Cno='2'
        RETURN --只有一个输出参数 当然返回他了
    go
    
    --执行
    declare @v1 char(9),@v2 smallint
    select @v1='2000201'  --赋值
    --查询学号为@v1的2号课程的成绩 并将结果赋给输出参数@v2
    exec Proc3 @v1,@v2 output --output不能省略 形参实参位置都要 
    print @v2
    
    --运行结果:
    40

    执行存储过程:EXEC[UTE] <存储过程名> [ [过程参数变量=] {值| 变量 [OUTPUT] …}    --exec和execute都行

    例4,(返回多个参数)输入参数指定出版社名称,输出参数包含书总册数(类别数),和总价值

    go
    create procedure proc2 
    	@publish varchar(40),@sumprice decimal output,@sum int output
    as	
    	select @sumprice=sum(price) from Book where publish=@publish
    	select @sum=count(*) from Book where publish=@publish
    go
    -- drop proc proc2
    declare @publish2 varchar(40),@sum2 int,@sumprice2 decimal
    set @publish2='清华大学出版社'
    exec proc2 @publish2,@sumprice2 output,@sum2 output --output不能少
    select @publish2 '出版社',@sum2 '总册数',@sumprice2 '总价值'
    
    -- 执行结果
    出版社	       总册数	总价值
    清华大学出版社	3	 105

     

    三. 修改和删除存储过程

    ⑴修改存储过程:
       语句格式: ALTER PROC[EDURE] <存储过程名> {同定义}

    ⑵删除存储过程:DROP PROCEDURE 
       语句格式: DROP PROC[EDURE] <存储过程名> 
       eg:drop proc Proc3

     

     

    四、存储过程的种类

    1.SQL Server提供的存储过程
        SQL Server提供了很多现成的、实用的存储过程供用户使用
        常用的存储过程分为5类: 
            系统存储过程(sp_)
            扩展存储过程(xp_)
            远程存储过程
            局部存储过程
            临时存储过程 

    ⑴系统存储过程(sp_)
            SQL Server系统存储过程是为管理员而提供的,
        SQL Server安装时在master数据库中创建并由系统管理员拥有。
        使用户可以很容易地从系统表中取出信息,管理数据库,并执行
        涉及更新系统表的其他任务。系统存储过程命令均以sp_打头,
        其作用进行数据库管理。
            SQL Server提供了许多系统存储过程以方便检索和操纵存放
        在系统表中的信息,系统存储过程可以在任意一个数据库中执行。

    例如,常用的系统存储过程有:

    sp_ helpdb(database_name):              返回指定数据库信息
    sp_help(object):			返回指定数据库对象的信息
    sp_addlogin:				建立SQL Server用户帐号
    sp_datatype_info:			返回由当前环境支持的数据类型的信息
    sp_monitor:				按一定格式显示的系统全局变量的当前值
    

     

    ⑵ 扩展存储过程(xp_)
            扩展存储过程提供一种类似于存储过程的方式,它们是动态装入和执行的动态
        连接库(DLL)内的函数,无缝地扩展SQL Server功能。SQL Serve之外的动作可以
        很容易地触发,外部信息返回到SQL Server。另外,扩展存储过程支持返回状态码和输出。
            注意:必须从master数据库执行扩展存储过程。
            用户可以创建自己的扩展存储过程。
            例如,下面是一些的扩展存储过程:

    xp_cmdshell:  作为一个操作系统外壳执行指定命令串,并以文本形式返回任何输出。
    xp_logevent:  在SQL Server日志文件或WindowsNT事件查看器中记录用户定义的信息。
    xp_msver:     返回SQL Server版本信息及各种环境信息。

    ⑶ 远程存储过程
        远程存储过程是从连接到不同服务器的远程服务器或客户机调用的存储过程。
    ⑷ 局部存储过程
        局部存储过程在各个用户数据库中创建。只能由创建它的用户调用。
    ⑸ 临时存储过程
        临时存储过程可是局部的,名字前的前缀是"#";也可是全局的,名字前的前缀是"##"。
        临时存储过程存放在tempdb数据库中。
        局部临时存储过程在单个用户会话中使用,该用户退出时,自动被删除。
        全局临时存储过程所有用户都可以使用,当最后一个用户退出时,自动被删除。
     

     

    展开全文
  • MySQL-存储过程(数据类型、函数)

    千次阅读 2019-05-25 03:33:45
    主要介绍存储过程的创建,注意事项,数据类型,常用函数。
  • 从数据流中获取中位数

    万次阅读 2020-02-29 11:55:55
    从数据流中获取中位数需求描述需求分析C++代码如下python代码 需求描述   有一个动态的数据流,如何比较快的获得数据流的中位数。这个过程中,数据流可能会有新的数据加入。中位数定义为元素个数为奇数的序列的...
  • 存储过程和函数的操作

    千次阅读 2021-11-22 20:40:24
    在MySQL软件关于数据的操作,包括插入数据记录操作(CREATE,INSERT)、查询数据记录操作(SELECT),更新数据记录棟作(UPDATE)和删除数据记录操作(DELETE)。第9章已经详细介绍了关于数据的操作,本章将详细介绍...
  • 存储过程: CREATE DEFINER=`xxx(数据库用户名)`@`localhost` PROCEDURE `insert_rdno`(in min int(10), in max int(10)) BEGIN DECLARE i int default 0; DECLARE a int default 0; set autocommit = 0; ...
  • 练习7:创建存储过程和函数

    千次阅读 2019-09-02 17:49:24
    案例:创建一个名称为sch的数据表,表结构如表1所示,将表2的数据插入sch表。 字段名 数据类型 主键 外键 非空 唯一 自增 id int(10) 是 否 是 是 否 name varchar(50) 否 否 是 否 否 glass ...
  • Q都等于0 一旦置R = 0,S = 1,R不变的情况下,无论S如何改变,Q都等于1 我们观察后面加粗的句子,不难看出,1个二进制被“锁在”了器件,这也就是所谓的锁存器,它在功能上,实现了存储1个二进制。...
  • 获取三个数中的最大值:CREATE PROCEDURE proc_max @a INT, @b INT, @c INT AS BEGIN DECLARE @max INT; IF @a>@b SET @max=@a; ELSE SET @max=@b; IF @c>@max SET @max=@c; PRINT '三个数中
  • 存储过程常见语法

    万次阅读 2018-12-23 16:39:37
    2、存储过程中可以包含逻辑控制语句和数据操纵语句,它可以接受参数、输出参数、返回单个或多个结果集以及返回值。 3、由于存储过程在创建时即在数据库服务器上进行了编译并存储在数据库中,所以存储过程运行要比...
  • 详解计算机内部存储数据的形式—二进制 前言 要想对程序的运行机制形成一个大致印象,就要了解信息(数据)在计算机内部是以怎样的形式来表现的,又是以怎样的方法进行运算的。在 C 和 Java 等高级语言编写的 程序...
  • sql使用存储过程得到随机数

    千次阅读 2017-01-20 09:44:44
    WHILE @L 随机产生每个随的位数 BEGIN SET @RN = @RN + CHAR(ROUND(RAND() * 9 + 48,0)) SET @L = @L + 1 END   --加入时间 例如:20170120: Select @RN=CONVERT(varchar(100), GETDATE(),...
  • 该不该用存储过程

    千次阅读 2018-01-23 15:02:55
    而是关于:"业务逻辑是不是要封装在存储过程中实现,这样子php、java等就是调用存储过程"。   业务逻辑,通俗说就是:比如要取数据的操作,取出会员编号为x的数据,原来我们一般是封装成函数,或者直接编写sql...
  • 数据库存储过程

    万次阅读 多人点赞 2017-12-02 19:07:18
    SQL语句需要先编译然后执行,而存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储在数据库,用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数)来调用执行它。...
  • 图解MySql命令行创建存储过程

    千次阅读 2016-09-02 18:53:35
    一 操作实例 首先登录mysql;..."当做存储过程中的代码; 调用一下,成功了; 创建一个带输出参数的存储过程,返回book表的记录;创建成功; 如下的调用语句失败; 先在...
  • 小数在内存是如何存储的?

    千次阅读 2021-07-29 01:21:46
    小数在内存是如何存储的?文本关键字:小数、float、double、浮点数、精度一、IEEE 754(二进制浮点数算术标准)在学习进制转换时,我们了解到:我们经常使用的十进制是转换为二进制进行存储的,只需要按照顺序将...
  • mysql 存储过程详解

    千次阅读 2016-05-11 17:46:16
    Mysql储存过程是一组为了完成特定功能的SQL语句集,经过编译之后存储在数据库,当需要使用该组SQL语句时用户只需要通过指定储存过程的名字并给定参数就可以调用执行它了,简而言之就是一组已经写好的命令,需要...
  • (因为篇幅和时间限制,本文先介绍存储过程方式,后续再介绍另外两种方式)   二、 mysql存储过程使用介绍   1、mysql存储过程基本语法 (1)创建存储过程: 如: CREATE PROCEDURE ...
  • 数据库存储过程详解

    万次阅读 2017-08-23 22:46:55
    存储过程的优缺点  ◆优点:  执行速度更快。存储过程只在创造时进行编译,而一般SQL语句每执行一次就编译一次,所以使用存储过程执行速度更快。  存储过程用于处理复杂的操作时,程序的可读性更强、网络的...
  • 什么是存储过程 如果你接触过其他的编程语言,那么就好理解了,存储过程就像是方法一样。 竟然他是方法那么他就有类似的方法名,方法要传递的变量和返回结果,所以存储过程存储过程名有存储过程参数也有返回值。 ...
  • 本文属于《理解性能的奥秘——应用程序慢,SSMS快》系列 接上文:理解性能的奥秘——应用程序慢,SSMS快(1)——简介 本文介绍SQL Server如何编译存储过程并使用计划缓存。如果你的应用程序完全没有用到...
  • Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储在数据库,用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数)来调用执行它。 一个存储过程是一个可编程的函数,它在数据库创建...
  • Oracle存储过程基本语法

    万次阅读 2018-08-03 15:12:23
    oracle 存储过程基本语法 存储过程如同一门程序设计语言,同样包含了数据类型、流程控制、输入和输出和它自己的函数库。 --------------------基本语法-------------------- 一.创建存储过程 create procedure ...
  • mysql存储过程详解实例

    万次阅读 多人点赞 2016-12-23 10:23:17
    mysql存储过程详解实例
  • 文章目录大小端、%u、%d打印的规则、数据在内存存储数据在内存存储整形存储无符号整型、无符号整型的打印char类型的打印浮点型存储大小端模式判断机器大小端方法 数据在内存存储 整形存储 一个变量的...
  • mysql存储过程详细教程

    万次阅读 多人点赞 2016-10-12 11:48:54
    记录MYSQL存储过程中的关键语法: DELIMITER // 声明语句结束符,用于区分; CEATE PROCEDURE demo_in_parameter(IN p_in int) 声明存储过程 BEGIN …. END 存储过程开始和结束符号 SET @p_in=1 变量赋值 ...
  • 1:先看看数据库的emp表的数据类型 2:错误的姿势 3:正确的姿势 4:总结-解决问题的过程
  • MySql存储过程,传数组参数

    千次阅读 2018-09-17 10:38:40
    项目遇到批量数据的处理,使用hibernate的出来太慢,修改为存储过程。 第一版修改为应用循环调存储过程,发现效果不理想。可能是频繁的打开session和关闭。 第二版想法为,把所有参数传入到存储过程,循环参数为...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 580,187
精华内容 232,074
关键字:

存储过程怎么打印中位数