精华内容
下载资源
问答
  • MySQL存储过程与函数的创建管理实验

    千次阅读 多人点赞 2019-12-03 22:39:47
    实验6:存储过程与函数的创建管理实验 一、实验目的: 理解存储过程函数的概念。 掌握创建存储过程函数的方法。 掌握执行存储过程函数的方法。 掌握游标的定义、使用方法。 二、验证性实验 1.某超市的食品...

    实验6:存储过程与函数的创建管理实验

    一、实验目的:

    1. 理解存储过程和函数的概念。
    2. 掌握创建存储过程和函数的方法。
    3. 掌握执行存储过程和函数的方法。
    4. 掌握游标的定义、使用方法。

    二、验证性实验

    1.某超市的食品管理的数据库的Food表,Food表的定义如表所示,
    在这里插入图片描述
    各列有如下数据:
    ‘QQ饼干’,‘QQ饼干厂’,2.5,‘2008’,3,‘北京’
    ‘MN牛奶’,‘MN牛奶厂’,3.5,‘2009’,1,‘河北’
    ‘EE果冻’,‘EE果冻厂’,1.5,‘2007’,2,‘北京’
    ‘FF咖啡’,‘FF咖啡厂’,20,‘2002’,5,‘天津’
    ‘GG奶糖’,‘GG奶糖厂’,14,‘2003’,3,‘广东’

    create database foodinfo;
    
    use foodinfo;
    
    create table food(
    foodid int(4) primary key not null unique auto_increment,
    name varchar(20) not null,
    company varchar(30) not null,
    price float not null,
    product_time year,
    validity_time int(4),
    address varchar(50)
    );
    
    desc food;
    
    
    //机房
    insert into food(name,company,price,product_time,validity_time,address)
    values
    ('QQbingban','QQbingbanchang',2.5,'2008',3,'Beijing'),
    ('MNniunai','MNniunaichang',3.5,'2009',1,'Hebei'),
    ('EEguodong','EEguodongchang',1.5,'2007',2,'Beijing'),
    ('FFkafei','FFkafeichang',20,'2002',5,'Tianjin'),
    ('GGnaitang','GGnaitangchang',14,'2003',3,'Guangdong');
    
    select * from food;
    
    insert into food(name,company,price,product_time,validity_time,address)
    values
    ('QQ饼干','QQ饼干厂',2.5,'2008',3,'北京'),
    ('MN牛奶','MN牛奶厂',3.5,'2009',1,'河北'),
    ('EE果冻','EE果冻厂',1.5,'2007',2,'北京'),
    ('FF咖啡','FF咖啡厂',20,'2002',5,'天津'),
    ('GG奶糖','GG奶糖厂',14,'2003',3,'广东');
    
    select * from food;
    
    
    

    (1)在food表上创建名为Pfood_price_count的存储过程。其中存储过程Pfood_price_count有3个参数。输入参数为price_info1和price_info2,输出参数为count。存储过程的满足:查询food表中食品单价高于price_info1且低于price_info2的食品种数,然后由count参数来输出,并且计算满足条件的单价的总和。
    代码如下:

    delimiter &&
    create procedure pfood_price_count(in price_info1 float,in price_info2 float,out count int)
    reads sql data
    begin
    declare temp float;
    declare match_price cursor for select price from food;
    declare exit handler for not found close match_price;
    set @sum=0;
    select count(*) into count from food
    where price>price_info1 and price<price_info2;
    open match_price;
    repeat
    fetch match_price into temp;
    if temp>price_info1 and temp<price_info2
    then set @sum=@sum+temp;
    end if;
    until 0 end repeat;
    close match_price;
    end &&
    delimiter ;
    

    (2)使用CALL语句来调用存储过程。查询价格在2至18之间的食品种数。代码如下:

    call pfood_price_count(2,18,@count); 
    

    (3)使用SELECT语句查看结果。代码如下:

    select @count,@sum;
    

    其中,count是存储过程的输出结果:sum是存储过程中的变量,sum中的值满足足条件的单价的总和。
    (4)使用DROP语句删除存储过程Pfood_price_count。代码如下:

    drop procedure pfood_price_count; 
    

    (5)使用存储函数来实现(1)的要求。存储函数的代码如下:

    delimiter &&
    create function pfood_price_count1(price_info1 float,price_info2 float)
    returns int reads sql data
    begin
    return(select count(*) from food
    where price>price_info1 and price<price_info2);
    end &&
    delimiter ;
    

    (6)调用存储函数

    select pfood_price_count1(2,18);
    

    (7)删除存储函数

    drop function pfood_price_count1;
    

    注:存储函数只能返回一个值,所以只实现了计算满足条件的食品种数。使用RETURN来将计算的食品种数返回回来。调用存储函数与调用MySQL内部函数的方式是一样的。

    三、设计性实验

    学校教师管理数据库中的teacherInfo表,其表的定义如下表所示,请完成如下操作。
    在这里插入图片描述
    向teacherInfo表中插入记录:
    1001,‘张龙’,‘男’,‘1984-11-08’,‘北京市昌平区’
    1002,‘李梅’,‘女’,‘1970-01-21’,‘北京市海淀区’
    1003,‘王一丰’,‘男’,‘1976-10-30’,‘北京市昌平区’
    1004,‘赵六’,‘男’,‘1980-06-05’,‘北京市顺义区’

    create database teacher;
    
    use teacher;
    
    create table teacherinfo(
    num int(10) primary key not null unique,
    name varchar(20) not null,
    sex varchar(4) not null,
    birthday datetime,
    address varchar(50)
    );
    
    desc teacherinfo;
    
    //机房
    insert into teacherinfo(num,name,sex,birthday,address)
    values
    (1001,'ZL','M','1984-11-08','BeijingChangping'),
    (1002,'LM','W','1970-01-21','BeijingHaiding'),
    (1003,'WYF','M','1976-10-30','BeijingChangping'),
    (1004,'ZN','M','1980-06-05','BeijingShunyi');
    
    select * from teacherinfo;
    
    
    insert into teacherinfo(num,name,sex,birthday,address)
    values
    (1001,'张龙','男','1984-11-08','北京市昌平区'),
    (1002,'李梅','女','1970-01-21','北京市海淀区'),
    (1003,'王一丰','男','1976-10-30','北京市昌平区'),
    (1004,'赵六','男','1980-06-05','北京市顺义区');
    
    select * from teacherinfo;
    

    (1)创建名为teachernfo1的存储过程。要求:存储过程teachernfo1有3个参数。输入参数为teacherid和type,输出参数为info。满足:根据编号(teacherid)来查询teachernfo表中的记录。如果type的值为1时,将姓名(name)传给输出参数info;如果type的值为2时,将年龄传给输出参数info;如果type为其他值,则返回字符串“Error”。

    //使用“DELIMITER &&”将SQL语句的结束符号变成&&
    delimiter &&
    create procedure teachernfo1(in teacherid int,in type int,out info varchar(20))
    reads sql data
    begin
    case type
    when 1 then
    select name into info from teacherinfo where num=teacherid;
    when 2 then
    select year(now())-year(birthday) into info from teacher where num=teacherid;
    else
    select 'Error' into info;
    end case;
    end &&
    delimiter ;
    

    (2)调用存储过程,参数值teacherid为1001,type为1。

    call teacherinfo1(1001,1,@info);
    select @info;
    

    (3)使用DROP PRODECURE语句来删除存储过程

    drop procedure teacherinfo1;
    

    (4) 创建名为teacherinfo2的存储函数。要求:存储过程teacherinfo2有两个参数:teacher id和type。满足:根据编号(teacher id)来查询teacher表中的记录。如果type的值是1时,则返回姓名(name)值;如果type的值是2时,则返回年龄;如果type为其他值,则返回字符串“Error”。

    delimiter &&
    create function teacherinfo2(teacherid int,type int)
    returns varchar(20) reads sql data
    begin
    declare temp varchar(20);
    if type=1 
    then
    select name into temp from teacherinfo where num=teacherid;
    elseif type=2 
    then
    select year(now())-year(birthday) into temp from teacherinfo where num=teacherid;
    else
    set temp='Error';
    end if;
    return temp;
    end &&
    delimiter ;
    

    (5)使用SELECT语句调用teacherinfo2存储函数。

    select teacherinfo2(1002,1);
    select teacherinfo2(1002,2);
    select teacherinfo2(1002,3);
    

    (6)使用DROP FUNCTION语句来删除teacherinfo2存储函数。

    drop function teacherinfo2;
    

    四、观察与思考

    (1) 什么时候适合通过创建存储过程来实现?
    答:函数限制比较多,如不能用临时表,只能用表变量等,而存储过程的限制相对就比较少。
    (2)功能相同的存储过程和存储函数的不同点有哪些?
    答: 存储过程:可以使得对的管理、以及显示关于及其用户信息的工作容易得多。存储过程是 SQL 语句和可选控制流语句的预编译集合,以一个名称存储并作为一个单元处理。存储过程存储在数据库内,可由应用程序通过一个调用执行,而且允许用户声明变量、有条件执行以及其它强大的编程功能。存储过程可包含程序流、逻辑以及对数据库的查询。它们可以接受参数、输出参数、返回单个或多个结果集以及返回值。
    (3)使用游标对于数据检索的好处有哪些?
    答:在数据库中,游标是一个十分重要的概念。游标提供了一种对从表中检索出的数据进行操作的灵活手段,就本质而言,游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标总是与一条SQL 选择语句相关联因为游标由结果集(可以是零条、一条或由相关的选择语句检索出的多条记录)和结果集中指向特定记录的游标位置组成。当决定对结果集进行处理时,必须声明一个指向该结果集的游标。如果曾经用 C 语言写过对文件进行处理的程序,那么游标就像您打开文件所得到的文件句柄一样,只要文件打开成功,该文件句柄就可代表该文件。对于游标而言,其道理是相同的。可见游标能够实现按与传统程序读取平面文件类似的方式处理来自基础表的结果集,从而把表中数据以平面文件的形式呈现给程序。
      我们知道关系数据库管理系统实质是面向集合的,在MS SQL SERVER 中并没有一种描述表中单一记录的表达形式,除非使用where 子句来限制只有一条记录被选中。因此我们必须借助于游标来进行面向单条记录的数据处理。由此可见,游标允许应用程序对查询语句select 返回的行结果集中每一行进行相同或不同的操作,而不是一次对整个结果集进行同一种操作;它还提供对基于游标位置而对表中数据进行删除或更新的能力;而且,正是游标把作为面向集合的数据库管理系统和面向行的程序设计两者联系起来,使两个数据处理方式能够进行沟通。

    展开全文
  • oracle实验8 存储过程与函数的创建

    千次阅读 2019-02-10 10:32:46
    传送门(不用再辛苦百度实验报告啦,一键直达呦) oracle实验1 oracle 基本操作 oracle实验2 oracle数据体系结构 oracle实验3 用户、方案的创建管理 ...oracle实验8 存储过程与函数的创建 orac...

    传送门(不用再辛苦百度实验报告啦,一键直达呦)

    oracle实验1 oracle 基本操作

    oracle实验2 oracle数据体系结构

    oracle实验3 用户、方案的创建与管理

    oracle实验4 表的创建与管理

    oracle实验5 sql基本查询

    oracle实验6 sql高级查询

    oracle实验7 pl/sql编程基础

    oracle实验8 存储过程与函数的创建

    oracle实验9-10 索引与视图,序列和同义词的创建

    oracle大作业下载

    一、实验目的

    1.掌握存储过程与函数的概念

     2.能够熟练创建和调用存储过程与函数。

    二、实验内容

    1. 教材:第八章实验和练习题(全做)
    2. 补充练习题:
      1. 编写函数get_salary,根据emp表中的员工编号,获取他的工资。输入参数为员工编号,如果找到该员工,屏幕显示已找到的信息,函数返回值为该员工的工资。如果找不到,捕获并处理异常,函数返回值为0。函数创建成功后,调用该函数查看效果。
      2.  编写函数get_cnt,根据输入参数部门编号,输出参数输出该部门的人数,返回值是该部门的工资总和。如果如果找不到,捕获并处理异常,函数返回值为0。函数创建成功后,调用该函数查看效果。
      3. 编写存储过程DelEmp,删除emp表中指定员工记录。输入参数为员工编号。如果找到该员工,则删除他的记录,并在屏幕显示该员工被删除。如果没找到,则使用异常处理。存储过程定义成功后,调用该存储过程查看结果。
      4. 编写存储过程QueryEmp,查询指定员工记录;输入参数为员工编号,输出参数是员工的姓名和工资。如果找到该员工,在屏幕显示该员工已经查到。如果没找到,则捕获异常并处理。存储过程定义成功后,调用该存储过程查看结果。

    三、实验环境

    Windows 10, Oracle 11g 

    四、实验步骤

      1.创建存储过程,根据职工编号删除scott.emp表中的相关记录。

    (1)以scott 用户连接数据库,然后为system 用户授予delete 权限。

    语句:

    connect scott/tiger;
    grant delete on emp to system;

     

    截图:

    (2)以system 用户连接数据库,创建存储过程。

    语句:

    复制代码

    connect system/orcl1234;
    
    create or replace procedure delete_emp
     (id scott.emp.empno%type)
     is 
     begin 
       delete from scott.emp where empno=id;
     exception 
        when others then 
          dbms_output.put_line('errors');
     end;

    复制代码

     

    截图:

     

    (3)system 用户调用delete_emp存储过程。

    语句:execute delete_emp(7369);

    截图:

    (4)scott 用户调用delete_emp存储过程。

    语句:

    grant execute on delete_emp to scott;
    connect scott/tiger;
    execute system.delete_emp(7369);

     

    截图:

     

    2.创建存储过程,根据职工编号修改scott.emp表中该职工的其他信息。

    (1)   创建新用户,并授予权限。

    语句:

    复制代码

    connect system/orcl1234;
    
    create user u1 
     identified by abcdef;
    
    grant create session,
     create procedure to u1;
    
    grant select,update on scott.emp to u1;

    复制代码

    截图:

    (2)   以新用户连接数据库,创建存储过程。

    语句:

    复制代码

    connect u1/abcdef; 
    
    CREATE OR REPLACE PROCEDURE update_emp
     (no IN scott.emp.empno%TYPE,--引用emp表中的某字段的数据类型,必须对该表具有select权限
      name IN scott.emp.ename%TYPE DEFAULT NULL,
      job1 IN scott.emp.job%TYPE DEFAULT NULL,
      mgr1 IN scott.emp.mgr%TYPE DEFAULT NULL,
      hiredate1 scott.emp.hiredate%TYPE DEFAULT NULL,
      salary scott.emp.sal%TYPE DEFAULT NULL,
      comm1 scott.emp.comm%TYPE DEFAULT NULL,
      deptno1 scott.emp.deptno%TYPE DEFAULT NULL
     )
     IS
     BEGIN
       if name is not null then
         update scott.emp set ename=name where empno=no;
       end if;
       if job1 is not null then
         update scott.emp set job=job1 where empno=no;
       end if;
       if mgr1 is not null then
         update scott.emp set mgr=mgr1 where empno=no;
       end if;
       if hiredate1 is not null then
         update scott.emp set hiredate=hiredate1 where empno=no;
       end if;
       if salary is not null then
         update scott.emp set sal=salary where empno=no;
       end if;
       if comm1 is not null then
         update scott.emp set comm=comm1 where empno=no;
       end if;
       if deptno1 is not null then
         update scott.emp set deptno=deptno1 where empno=no;
       end if;
     EXCEPTION
       WHEN others THEN
         rollback;
     END;
     /

    复制代码

    截图:

     

     

    (3)   u1调用update_emp 过程。

       语句:   exec update_emp(7369,salary=>2000);

       截图:

     

      

    3.创建存储过程,根据指定的职工编号查询该职工的详细信息。

    (1)创建存储过程。

    语句:

    复制代码

    connect scott/tiger;
      
      create or replace procedure select_emp
      (no in scott.emp.empno%type,
       emp_information out varchar2)
    is
    r scott.emp%ROWTYPE;
    begin 
      select * into r from scott.emp where empno=no;
      emp_information:=emp_information||r.ename||'  '||r.job||'  '||r.sal||'   '||r.mgr||
    '   '||r.hiredate||'   '||r.comm||'   '||r.deptno;
    exception
      when no_data_found then
         emp_information:='No person!';
      when others then 
         emp_information:='Error!';
    End;
    / 

    复制代码

    截图:

      (2)调用存储过程。

    语句:

    复制代码

    set serveroutput on
     declare 
       info varchar2(50);
     begin 
       select_emp(7369,info);
       dbms_output.put_line(info);
     end;
     /

    复制代码

    截图:

     

    4.创建函数,根据给定的部门编号计算该部门所有职工的平均工资。

    (1)创建函数。

    语句:

    复制代码

    create or replace function avg_sal
     (no scott.emp.deptno%type)
     return number
     is
        avgsal number(7,2);
     begin
        select avg(sal) into  avgsal from scott.emp where deptno=no;
        if  avgsal is not null then --因为上面的语句不触发异常,因此用if语句判断是否查询成功
        return  avgsal;
        else
            avgsal:=-1;
            return  avgsal;
        end if;
     end   avg_sal;
     /

    复制代码

    截图:

    (2)调用函数。

    语句:

    begin 
       dbms_output.put_line(avg_sal(&deptno));
     end;

    截图:

     

     

    (选择题)

    1. 以下哪种程序单元必须返回数据?( A )

      A.函数  B.存储过程  C.触发器  D.包

      2.当建立存储过程时,以下哪个关键字用来定义输出型参数?( C  )

      A.IN    B.PROCEDURE  C.OUT    D.FUNCTION

      3.下列哪个语句可以在SQL*Plus中直接调用一个存储过程?( B )

      A.RETURN   B.EXEC  C.SET    D.IN

      4.下面哪些不是存储过程中参数的有效模式?( D )

      A.IN    B.OUT  C.IN OUT    D.OUT IN

      5.函数头部中的RETURN语句的作用是什么?( A )

      A.声明返回的数据类型

      B.调用函数

      C.调用过程

      D.函数头部不能使用RETURN语句

     

    (编程题)

    1. 根据以下要求编写存储过程:输入部门编号,输出scott.emp 表中该部门所有职工的职工编号、姓名、工作岗位。

    (1)授予system用户对scott.emp具有显示的查询权限。

       

    (2)创建存储过程

     语句:

    复制代码

    create or replace procedure pro_depart
      (no in scott.emp.deptno%type)
      is
        cursor c1 is select * from scott.emp where deptno=no;
      begin
        dbms_output.put_line('编号  姓名   工作岗位');
        for rec in c1
         loop
           dbms_output.put_line(rec.empno||'   '||rec.ename||'   '||rec.job);
         end loop;
      end;
    

    复制代码

    截图:

     

    (3)执行存储过程

    语句:  execute pro_depart(20);

    截图:

     

      2.根据以下要求编写函数:将scott.emp 表中工资低于平均工资的职工工资加上200,并返回修改了工资的总人数。

    (1)授予system用户对scott.emp具有修改的权限。

     

    (2)创建函数

    语句:

    复制代码

    conn system/orcl1234;
    
    create or replace function fun_sal
     return number
     is
       cursor c2 is select * from scott.emp for update;
       rows number default 0;
       avg_sal number(7,2);
     begin 
       select avg(sal) into  avg_sal from scott.emp;
     for rec in c2 
       loop 
        if rec.sal< avg_sal then 
         update scott.emp set sal=sal+200 where current of c2;
         rows:=rows+1;
        end if;
       end loop;
     return rows;
     end;

    复制代码

    截图:

     

    (3)调用函数

    语句:

    begin
        dbms_output.put_line('修改了工资的总人数是:  '||fun_sal);
      end;

    截图:

     

     

    (简答题)

      创建与调用存储过程或函数时,应事先授予哪些权限?

     答:1.首先创建存储过程自身需要的权限,即应授予create procedure系统权限。

           2.用户调用其他用户所创建的存储过程时,应事先授予对该过程的execute权限。

           3.如果对某表进行增、删、查、改的操作时,应授予insert、delete、update、select的显示权限。

     

    (补充练习题)

    1. 编写函数get_salary,根据emp表中的员工编号,获取他的工资。输入参数为员工编号,如果找到该员工,屏幕显示已找到的信息,函数返回值为该员工的工资。如果找不到,捕获并处理异常,函数返回值为0。函数创建成功后,调用该函数查看效果。

    (1)创建函数

    语句:

    复制代码

    create or replace function get_salary 
     (no in scott.emp.empno%type)
      return number
     is 
       salary scott.emp.sal%type;
     begin 
       select sal into salary from scott.emp where empno=no;
       return salary;
     exception 
       when others then 
        return 0;
     end;

    复制代码

    截图:

     

    (2)调用函数

    语句:

    begin
        dbms_output.put_line('该员工工资是:'||get_salary(7369));
      end;

    截图:

     

    语句:

    begin
        dbms_output.put_line('该员工工资是:'||get_salary(2000));
      end;

    截图:

     

    2. 编写函数get_cnt,根据输入参数部门编号,输出参数输出该部门的人数,返回值是该部门的工资总和。如果如果找不到,捕获并处理异常,函数返回值为0。函数创建成功后,调用该函数查看效果。

    (1)创建函数

    语句:

    复制代码

    create or replace function get_cnt
     (no in scott.dept.deptno%type,
      cnt out number )
     return number
     is
       salary_sum number(7,2);
     begin 
       select sum(sal) into salary_sum from scott.emp where deptno=no;
       select count(*) into cnt from scott.emp where deptno=no;
       return salary_sum;
     exception
       when others then
         return 0;
     end;

    复制代码

    截图:

     

    (2)调用函数

    语句:

    var salary_sum number;
     var cnt number;
     exec :salary_sum:=get_cnt(30,:cnt);

    截图:

     

    3.编写存储过程DelEmp,删除emp表中指定员工记录。输入参数为员工编号。如果找到该员工,则删除他的记录,并在屏幕显示该员工被删除。如果没找到,则使用自定义异常处理。存储过程定义成功后,调用该存储过程查看结果。

    (1)以scott 用户连接数据库,然后为system 用户授予delete 权限。

    语句:

    connect scott/tiger;
    grant delete on emp to system;

     

    截图:

     

    (2)以system 用户连接数据库,创建存储过程。

    语句:

    复制代码

    connect system/orcl1234;
    
    create or replace procedure DelEmp
      (no scott.emp.empno%type)
      is
        no_emp exception;
        cnt number;
      begin
        select count(*) into cnt from scott.emp where empno=no;
        if cnt=0 then
           raise no_emp;
        end if;
        delete from scott.emp where empno=no;
        dbms_output.put_line(no||'号员工已经被删除完毕!');
      exception
         when no_emp then
           dbms_output.put_line('抱歉!没有找到'||no||'号员工!');
      end;
     /

    复制代码

    截图:

     

    (3)调用存储过程。

    语句:exec DelEmp(2000);

    截图:

     

     

    4. 编写存储过程QueryEmp,查询指定员工记录;输入参数为员工编号,输出参数是员工的姓名和工资。如果找到该员工,在屏幕显示该员工已经查到。如果没找到,则捕获异常并处理。存储过程定义成功后,调用该存储过程查看结果。

    (1)创建过程

    语句:

    复制代码

    CREATE OR REPLACE PROCEDURE QueryEmp
      (no IN scott.emp.empno%TYPE,
      name OUT scott.emp.ename%TYPE,
      salary OUT scott.emp.sal%TYPE)
      IS
      BEGIN
         SELECT ename,sal into name,salary FROM scott.emp WHERE empno=no;
         dbms_output.put_line('找到员工!');
      EXCEPTION
          WHEN NO_DATA_FOUND THEN
                dbms_output.put_line('该职工不存在!');
      END;
     /

    复制代码

    截图:

     

    (2)执行过程

    语句:

    复制代码

    DECLARE
       emp_name scott.emp.ename%TYPE;
       emp_salary scott.emp.sal%TYPE;
     BEGIN
       QueryEmp(7788,emp_name,emp_salary);  --调用存储过程
       IF emp_name IS NOT NULL THEN --如果该职工存在,则输出
         dbms_output.put_line('姓名是:'||emp_name|| ' 工资是:'||emp_salary);
       END IF;
     END;

    复制代码

    亦可:exec QueryEmp(7788,:ename,:sal);

    截图:

     

    展开全文
  • 存储过程存储函数(Oracle)存储过程存储函数存储在数据库中供所有用户程序调用的子程序叫存储过程存储函数存储过程存储函数的区别?存储函数:可以通过return 语句返回函数值。存储过程:不能除此之外...

    存储过程,存储函数(Oracle)

    存储过程和存储函数
    指存储在数据库中供所有用户程序调用的子程序叫存储过程、存储函数。
    存储过程和存储函数的区别?
    存储函数:可以通过return 语句返回函数值。
    存储过程:不能
    除此之外我们可以认为他们是完全一样的。
     
    存储过程
    1、创建存储过程
    用create procedure命令简历存储过程。
    语法:
    create [or replace] procedure 过程名(参数列表)
    as
        PLSQL子程序体;
     
    打印hello word
    复制代码
    --打印hello world
    create or replace procedure sayhelloworld
    as 
      --说明部分
    begin 
      dbms_output.put_line('hello world');
    end;
    /
    复制代码

    编译后:

    2、调用存储过程方法:
    1、exec 过程名
    2、begin
                过程名;
                过程名;
          end;
           /
     
    测试调用存储过程
    复制代码
    --连接数据库
    C:\WINDOWS\system32>sqlplus scott/tiger@192.168.56.101:1521/orcl
    SQL>--调用方式一
    SQL> set serveroutput on
    SQL> exec sayhelloworld;
    hello world
    
    PL/SQL 过程已成功完成。
    
    SQL> --调用方式二:
    SQL> begin
      2      sayhelloworld();
      3      sayhelloworld();
      4  end;
      5  /
    hello world
    hello world
    
    PL/SQL 过程已成功完成。
    复制代码

    带参数的存储过程:

    复制代码
    --给指定员工薪水涨100,并且打印涨前和涨后的薪水
    create or replace procedure raiseSalary(eno in number) --in为输入参数
    as 
      --说明部分
      psal emp.sal%type;
    
    begin
      --得到涨前的薪水
      select sal into psal from emp where empno=eno;
    
      update emp set sal=sal+100 where empno=eno;
    
      --要不要commit?
      --为保证在同一事务中,commit由谁调用谁提交
      dbms_output.put_line('涨前:'||psal||'  涨后:'||(psal+100));
    end;
    /
    复制代码

    测试:

    存储函数
    函数(function)为一命名的存储程序,可带参数,并返回一计算值。函数和过程的结构类似,但必须有一个return子句,用于返回函数值。函数说明要指定函数名、结果值的类型,以及参数类型等。
    存储函数语法:
    create[or replace] functiion 函数名(参数列表) 
    return函数值类型
    as
        PLSQL子程序体;
     
    查询员工年收入
    复制代码
    --查询某个员工的年收入
    create or replace function queryempincome(eno in number)
    return number
    as
      --月薪和奖金
      psal   emp.sal%type;
      pcomm  emp.comm%type;
    begin
      select sal,comm into psal,pcomm from emp where empno=eno;
      --返回年收入
      return psal*12+nvl(pcomm,0);
    end;
    /
    复制代码

    测试:

    过程和函数中的in 和out
    一般来讲,过程和函数的区别在于函数可以有一个返回值;而过程没有返回值。
    但过程和函数都可以通过out指定一个或多个输出参数,我们可以利用out参数,在过程和函数中实现返回多个值。
    什么时候用存储过程/存储函数?
    原则(不是必须的):
        如果只有一个返回值,用存储函数;否则,就用存储过程。
     
    存储过程
    复制代码
    create or replace procedure queryEmpInfo(eno in number,
                                             pname out varchar2,
                                             psal  out number,
                                             pjob  out varchar2)
    as 
    begin
      select ename,sal,empjob into pname,psal,pjob from emp where empno=eno;
    end;
    复制代码

    测试

    使用java程序调用存储过程

    复制代码
    /*
         * 存储过程
         * create or replace procedure queryEmpInfo(eno in number,
         *                                     pename out varchar2,
         *                                     psal out number,
         *                                     pjob out varchar2)
         */
        @Test
        public void testProcedure() {
           // {call <procedure-name>[(<arg1>,<arg2>, ...)]}
           String sql = "{call queryEmpInfo(?,?,?,?)}";
           CallableStatement call = null;
           Connection connection = JDBCUtils.getConnection();
           try {
               call = connection.prepareCall(sql);
              
               //对于in参数,赋值
               call.setInt(1, 7839);
              
               //对于out参数,声明
               call.registerOutParameter(2, OracleTypes.VARCHAR);
               call.registerOutParameter(3, OracleTypes.NUMBER);
               call.registerOutParameter(4, OracleTypes.VARCHAR);
              
               //执行
               call.execute();
              
               //取出结果
               String name = call.getString(2);
               double sal = call.getDouble(3);
               String job = call.getString(4);
               System.out.println(name + "\t" + sal + "\t" + job);
              
           } catch (SQLException e) {
               e.printStackTrace();
           }finally{
               JDBCUtils.release(connection, call, null);
           }
        }
    复制代码

    使用java程序调用存储函数

    复制代码
    /*
         * 存储函数
         * create or replace function queryEmpIncome(eno in number) 
           return number
         */
        @Test
        public void testFunction() {
           // {?= call <procedure-name>[(<arg1>,<arg2>, ...)]}
           String sql = "{?=call queryEmpIncome(?)}";
           Connection conn = null;
           CallableStatement call = null;
           try {
               conn = JDBCUtils.getConnection();
               call = conn.prepareCall(sql);
     
               //对于out参数,赋值
               call.registerOutParameter(1, OracleTypes.NUMBER);
              
               //对于in参数,赋值
               call.setInt(2, 7839);
              
               //执行
               call.execute();
              
               //取出数据
               double income = call.getDouble(1);
               System.out.println(income);
           } catch (Exception e) {
               e.printStackTrace();
           } finally {
               JDBCUtils.release(conn, call, null);
           }
        }
    复制代码
    在out参数中使用光标
    问题:查询某个部门中所有员工的所有信息
    1、申明包结构
    复制代码
    CREATE OR REPLACE 
    PACKAGE MYPACKAGE AS 
    
      type empcursor is ref cursor;
      --创建存储过程,输出参数为自定义类型
      procedure queryEmpList(dno in number,empList out empcursor);
    
    END MYPACKAGE;
    复制代码

    2、创建包体(实现)

    复制代码
    CREATE OR REPLACE
    PACKAGE BODY MYPACKAGE AS
    
      procedure queryEmpList(dno in number,empList out empcursor) AS
      BEGIN
        --实现
        open empList for select * from emp where deptno=dno;
    
      END queryEmpList;
    
    END MYPACKAGE;
    复制代码
    使用java调用带包的存储过程
    复制代码
    public void testCursor() {
           // {call <procedure-name>[(<arg1>,<arg2>, ...)]}
          
           String sql = "{call MYPACKAGE.queryEmpList(?,?)}";
           Connection conn = null;
           CallableStatement call = null;
           ResultSet rs = null;
           try {
               conn = JDBCUtils.getConnection();
               call = conn.prepareCall(sql);
     
               //对于in参数,赋值ֵ
               call.setInt(1, 20);
     
               //对于out参数,赋值
               call.registerOutParameter(2, OracleTypes.CURSOR);
     
               //执行
               call.execute();
     
               // 取出结果
               rs = ((OracleCallableStatement)call).getCursor(2);
               while(rs.next()){
                  String name = rs.getString("ename");
                  double sal = rs.getDouble("sal");
                  System.out.println(name+"\t"+sal);
               }
           } catch (Exception e) {
               e.printStackTrace();
           } finally {
               JDBCUtils.release(conn, call, rs);
           }
        }
    此案例光标没有关闭,原因:当resultSet关闭的时候 光标就close了
    
    展开全文
  • 存储过程(Stored Procedure)和存储函数(Stored Function)是在数据库中定义的一些完成特定...在MySQL中使用存储过程,而不是使用存储在客户端计算机本地的SQL程序的优点如下: 存储过程增加了SQL的功能和灵活性 ...

    存储过程(Stored Procedure)和存储函数(Stored Function)是在数据库中定义的一些完成特定功能的SQL语句集合,其经编译后存储在数据库中。存储过程和存储函数中可包含流程控制语句及各种SQL语句。它们可以接受参数、输出参数、返回单个或者多个结果。

    在MySQL中使用存储过程,而不是使用存储在客户端计算机本地的SQL程序的优点如下:

    • 存储过程增加了SQL的功能和灵活性

    • 存储过程允许模块化程序设计

    • 存储过程能实现较快的执行速度

    • 存储过程能够减少网络流量

    • 存储过程可作为一种安全机制来充分利用

    创建存储过程

    在MySQL中,创建存储过程和存储函数必须具有CREATE ROUTINE权限,并且ALTER ROUTINE和EXECUTE权限被自动授予它的创建者。

    # 创建存储过程
    CREATE PROCEDURE procedure_name([proc_parameter[,...]]) [characteristic[,...]] Routine_body
    procedure_name:
        [IN|OUT|INOUT]param_name type
    characteristic:
        LANGUAGE SQL、[NOT]DETERMINISTIC、{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}、
        SQL SECURITY{DEFINER|INVOKER}、COMMENT'string'
    
    # 调用存储过程
    CALL [dbname.]sp_name([parameter[,...]]);
    

    在创建存储过程时,系统默认指定CONTAINS SQL,表示存储过程中使用了SQL语句。但是,如果存储过程中没有使用SQL语句,最好设置为NO SQL。而且,最好在存储过程的COMMENT部分对存储过程进行简单的注释,以便以后在阅读存储过程的代码时理解代码。

    mysql> use gradem;
    Database changed
    mysql> delimiter //
    mysql> create procedure proc_sc()
        -> reads sql data
        -> begin
        -> select student.sno, sname, sum(degree)/count(degree) from student inner join sc 
            on student.sno=sc.sno;
        -> end //
    Query OK, 0 rows affected (0.09 sec)
    mysql> delimiter;
    mysql> 
    

    MySQL中默认的语句结束符为分号(?。存储过程中的SQL语句需要分号来结束。为了避免冲突,首先用“DELIMITER //”将MySQL的结束符设置为//,最后用“DELIMITER ;”将结束符恢复成分号。

    创建存储函数

    在MySQL中,存储函数的使用方法与MySQL内部函数的使用方法一样。两者唯一的区别在于,存储函数是用户自己定义的,而内部函数是MySQL开发者定义的。

    # 创建存储函数
    CREATE FUNCTION func_name([func_parameter[,...]]) RETURES type [characteristic[,...]] Routine_body
    
    # 调用存储函数
    SELECT [dbname.]func_name([parameter[,...]]);
    

    PROCEDURE可以指定IN、OUT或INOUT类型的参数,而FUNCTION的参数类型默认为IN。RETURNS子句只能包含在FUNCTION中,它用来指定函数的返回类型,而且函数体必须包含一个RETURN value语句。

    查看存储过程和存储函数

    # 查看存储过程和存储函数的状态
    SHOW {PROCEDURE|FUNCTION} STATUS [LIKE 'pattern'];
    或
    SHOW CREATE {PROCEDURE|FUNCTION} sp_name;
    或
    SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME='sp_name';
    

    SHOW STATUS语句只能查看存储过程或存储函数是操作哪一个数据库,以及存储过程或存储函数的名称、类型、谁定义的、创建和修改时间、字符编码等信息。但是,这个语句不能查询存储过程或存储函数的具体定义。如果就需要查看详细定义,就需要使用SHOW CREATE语句。

    删除存储过程和存储函数

    DROP {PROCEDURE|FUNCTION} [IF EXISTS] sp_name;
    

    IF EXISTS子句是MySQL的扩展,如果存储过程或存储函数不存在,则它可以防止发生错误,产生一个用SHOW WARNINGS查看的警告。如果另一个存储过程调用某个已被删除的存储过程,则MySQL将在执行调用进程时显示一条错误消息。

    (最近更新:2019年09月03日)

    展开全文
  • 创建存储过程函数1.1 创建存储过程1.2 创建存储函数1.3 变量的使用1.4 定义条件和处理程序1.5 光标的使用1.6 流程控制的作用1.6.1 IF1.6.2 CASE语句1.6.3 LOOP和LEAVE语句1.6.4 ITERATE 语句1.6.5 REPEAT 语句...
  • MySQL存储过程函数,java调用

    千次阅读 2019-08-18 20:10:30
    (原因是在存储过程函数定义里面会用到分号,如果结束符号跟函数体里面的一样的话,相当于提交语句了,跟定义冲突,需要重新修改一下) (为什么要修改? 有时候我们输入的语句不希望立即执行,但是语句中包含有...
  • 需要创建一个存储过程,实现对于一个数据表中的数据进行统计。不同的字段统计的方式不同,有的是统计最大值,有的是最小值,有的是平均值,还有的是cp95值(即将所有数据从小打大进行排序,选择前95%个值中最大的值...
  • 实验三 存储过程函数

    千次阅读 2019-12-17 20:07:38
    创建一个函数,以员工号为参数,返回该员工的工资。 create or replace function f_num_sal(vempno in emp.empno%type) return number as vsal number; begin select sal into vsal from emp where empno=vempno; ...
  • EF调用存储过程函数

    万次阅读 2016-09-10 22:53:15
    说这个问题前 首先先说下 我使用ef4.1 codefirst的目的. 是因为可以有更纯净的POCO 不再有EDMX这些东西 而不是真正的用 code first 先有代码 再生成数据库.所以 我虽然使用 的是codefirst 但是本质依然是数据库...
  • Batch Normalization:使用tf.layers高级函数构建带有Batch Normalization的神经网络 觉得有用的话,欢迎一起讨论相互学习~Follow Me 参考文献 吴恩达deeplearningai课程 课程笔记 Udacity课程...
  • 实验5.1 存储过程的建立与使用

    千次阅读 2014-05-16 14:24:49
    实验5.1 存储过程的建立与使用 一、实验目的 理解存储过程的概念、作用、建立和调用方法。 二、实验原理 使用CREATE PROCEDURE语句创建存储过程,ALTER PROCEDURE语句修改存储过程,DROP PROCEDURE语句删除...
  • 基本思想是:首先在元素的关键字k和元素的存储位置p之间建立一个对应关系f,使得p=f(k),f称为哈希函数。创建哈希表时,把关键字为k的元素直接存入地址为f(k)的单元; 以后当查找关键字为k的元素时,再利用哈希函数...
  • ST_Geometry 配合使用的 SQL 函数

    千次阅读 2018-05-10 10:47:33
    ST_Geometry 配合使用的 SQL 函数SQL 函数的列表进行以下任意操作时,会创建特定的 SQL 函数和类型:在 Oracle 或 PostgreSQL 数据库中创建地理数据库。在 Oracle 或 PostgreSQL 数据库中安装 ST_Geometry 空间...
  • R的源起 R是S语言的一种实现。S语言是由 AT&T贝尔实验室开发的一种用来进行数据探索、统计分析、作图的解释型语言。最初S语言的实现版本主要是S-PLUS。S-PLUS是一个商业 软件,它基于S语言...R的使用与S-PLUS有很多类
  • 完整StyleGAN笔记:...—————————————————————————————————    第二章 StyleGAN代码解读(下) 2.3 损失函数代码解读   StyleGAN的损失函数写在training/loss.p...
  • 构造函数与函数的关系

    千次阅读 2019-03-09 23:02:25
    构造函数不可以是虚函数,而析构函数可以。
  • 2个解决方案,一个是ORACLE中的函数可以带参数输出的,这个比较吻合SQLSERVER中的存储过程(个人比较意见用 ORACLE中的函数应对SQLSERVER中的存储过 程。 二就是用PROCEDURE 的OUT参数带出结果来解决
  • 存储过程五种使用场景比较

    千次阅读 2018-01-19 01:40:56
    存储过程五种使用场景比较 1. 使用 JDBC API 直接调用存储过程 Java Database Connectivity (JDBC) API 是 J2EE 的一部分,是 Java 语言访问关系数据库的基于标准的首要机制,提供了对数据库访问和缓存管理的直接...
  • 初识C#——构造函数与析构函数

    千次阅读 热门讨论 2014-12-28 14:26:24
     谈到这两个函数,不由自主的就对象的生命周期联系起来了,当我们使用对象时,分为三个步骤,第一,构建阶段,第二,使用阶段,第三,释放阶段。这三个步骤中的第一步和第三步就用到了我刚才所说的构造函数与
  • 举一些treelib库常用的函数,具体的参考 Useful APIs from treelib import Node, Tree tree = Tree() tree.show() # # 取得根节点到每一个叶节点的标识路径,返回值为标识list列表的list列表(二重列表),根...
  • MYSQL数据库实验(存储过程与触发器)

    千次阅读 2018-12-29 11:47:19
    个人配置说明:5.7.24 MySQL Community Server (GPL),环境CentOS 7 1.MYSQL 不支持语句触发(for each statement),只支持行触发(for each row,...理解,实现并逐渐熟悉存储过程使用 存储过程(Stored Procedure)是...
  • 一、利用TensorFlow构建神经网络主要分为三个步骤: 1.使用TensorFlow定义神经网络结构及其参数和定义根据定义的神经...利用前两步建立起来的图构建会话,安排batch数据送往前向传播进行计算以及反向传播过程进行...
  • 子程序(过程函数、方法)

    千次阅读 2015-09-08 09:12:42
    一般程序设计语言包含两种基本的抽象:过程抽象和数据抽象。过程抽象有时也称控制抽象。   子程序在1950年以前就发明了,作为一种抽象那时候并未被完全接受。相反,最初它被看做是一种节省代码的机制,但很快...
  • EXCEL中所有函数使用

    万次阅读 2016-11-18 10:48:05
    EXCEL中所有函数使用 来源: 周虹的日志 http://blog.renren.com/share/311273732/3704260772 EXCEL中所有函数使用 1.求和函数SUM 语法:SUM(number1,number2,...)。 ...
  • 存储是数据库开发中经常使用的操作,可以把许多重复的操作(只是一些参数不同)模块化,编程中的函数类似,但是存储过程的功能大于函数存储过程的定义如下。 1.创建存储过程 创建存储过程的语法如下。 ...
  • 本文详细讲解了利用__attribute__((section()))构建初始化函数表,以及Linux内核各级初始化的原理。作者简介: 廖威雄,2016年本科毕业于暨南大学,目前就职于珠海全志科技股份有限公司从事linux嵌入式系统(Tina ...
  • R语言开发之函数使用技巧

    千次阅读 2018-09-13 17:29:40
    函数又执行其任务并将控制权返回给解释器以及可存储在其他对象中的任何结果。 在R语言中,我们使用关键字function来创建一个函数,来看下R函数定义的基本语法: function_name &lt;- funct...
  • 知识图谱构建过程

    千次阅读 2020-05-07 13:38:16
    在本文中,笔者主要想分享一下自底向上构建知识图谱的全过程,抛砖引玉,欢迎大家交流。 “The world is not made of strings , but is made of things.” ——辛格博士,from Google. 知识图谱,是结构化的语义...
  • Numpy中repeat函数使用

    千次阅读 2018-07-23 14:59:08
    Numpy是Python强大的数学计算库,和Scipy一起构建起Python科学计算生态。在本节下面我们重点介绍下repeat函数的用法,我们在Python中import numpy,help(numpy.repeat),会出现以下界面:     rep...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 286,370
精华内容 114,548
关键字:

存储过程与函数的构建与使用