oracle存储过程入门练习

[sql]代码库

select count(*) u_total from USERS;

select user_name as userName from users where user_id = '1'; --变量赋值 

----------------------------------
/*总人数*/
create or replace procedure procedure_liang(u_total out number) 
as
  --u_total number(1);
begin
  select count(*) into u_total from USERS;
  --DBMS_OUTPUT.put_line('总人数：'||u_total);
  if u_total = 6 then
    DBMS_OUTPUT.put_line('总人数：' || u_total);
  end if;

end;

-------------------------------------
/*带参数in和out的存储过程*/
create or replace procedure get_username(userId in varchar2,userName out varchar2)
as
begin
  select user_name into userName from users where user_id = userId; --变量赋值 
  DBMS_OUTPUT.put_line('姓名：'||userName);
exception
when no_data_found then 
raise_application_error(-20001,'ID不存在!');
end;

--------------------------------------
/*调用有返回值的存储过程*/
create or replace procedure get_other_procedure(userId out varchar2,userName out varchar2)
as
begin
  procedure_liang(userId);
  --select user_name into userName from users where user_id = userId; --变量赋值 
  get_username(userId,userName);
  
  DBMS_OUTPUT.put_line('用户id：'||userId);
  DBMS_OUTPUT.put_line('姓名：'||userName);
exception
when no_data_found then 
raise_application_error(-20001,'ID不存在!');
end;

--------------------------------------
/*查询list数据*/

create or replace procedure procedure_list(userId in varchar2,cur_out out sys_refcursor)
as
begin
  open cur_out for select * from users where user_id < userId; --变量赋值 
  --DBMS_OUTPUT.put_line('list：'||cur_out);
exception
when no_data_found then 
raise_application_error(-20001,'ID不存在!');
end;

---------------------------------------

[代码运行效果截图]

[源代码打包下载]