存储过程 procedure
存储过程
无参存储过程:
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;
执行结果
带输出又带输入的存储过程:(给调用的程序使用)
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;
输出结果: