oracle存储过程学习记录

逃离我推掉我的手 2022-12-05 12:22 190阅读 0赞

## 存储过程 procedure ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjk3OTg0MA_size_16_color_FFFFFF_t_70_pic_center]

# 存储过程 #

#### 无参存储过程： ####

create or replace procedure p_hello as
    --声明变量
    begin
      
    dbms_output.put_line('hello world');
    
    end p_hello;

#### 带参数的存储过程： ####

create or replace procedure p_gettUsernameanduserid(i_id in tUser.id%TYPE) as
    --声明变量
     v_name tUser.Name%TYPE;
     v_userid tUser.Userid%TYPE;
    begin
      select name,userid into v_name,v_userid from  tUser where id = i_id;
      
      dbms_output.put_line(v_name||'-'||v_userid);
    
    end ;

plsql执行查询

-- Created on 2020/9/10 by BILL 
    declare 
      -- Local variables here
      v_id tUser.Id%TYPE;
    begin
    -- 查询id为0的用户
      v_id := 0;
      p_gettusernameanduserid(v_id);
      -- Test statements here
      
    end;

执行结果  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjk3OTg0MA_size_16_color_FFFFFF_t_70_pic_center 1]

#### 带输出又带输入的存储过程：（给调用的程序使用） ####

create or replace procedure p_gettUser_userid(i_id  in tUser.id%TYPE, o_userid out tUser.Userid%TYPE) as
    
    begin
      
      select userid into o_userid from tUser where id = i_id;
    
    end;

plsql程序调用：

-- Created on 2020/9/10 by BILL 
    declare 
      -- Local variables here
      -- 这里的参数不需要跟创建的存储过程里面的参数名字一样，可以自己随便定义
      o_userid tuser.userid%TYPE;
    begin
      -- Test statements here
      p_gettuser_userid(0,o_userid);
      dbms_output.put_line(o_userid);
    end;

输出结果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjk3OTg0MA_size_16_color_FFFFFF_t_70_pic_center 2]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjk3OTg0MA_size_16_color_FFFFFF_t_70_pic_center]: /images/20221123/7352751b2a9048dfaf513357f81c4819.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjk3OTg0MA_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221123/d7796a7f524845cc9569b6fa6ec3f39e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjk3OTg0MA_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221123/d842f7a519f0402fa1324c38cb34e890.png