存储过程练习

---例子1， 无参数过程
create or replace PROCEDURE HelloProc
is
 begin
   dbms_output.put_line('Hello World!');
 end;
 begin
 HelloProc();
 end;
--例子2 ，有一个输入参数
create or replace procedure resetpdeUser_password(loginname_in IN varchar2) is
begin 
    update s_users set C_PASSWORD='123' where c_loginname=loginname_in;
end resetpdeUser_password;

drop procedure reset_password   ;
declare
   loginname1 varchar2(20);
  begin
 loginname1 :='lfzhou';
 resetpdeUser_password(loginname1);
end;
--3.
create or replace procedure proc_getUserName(loginname_in varchar,name_out out varchar)
as
 begin
      select c_name into name_out from s_users where c_loginname=loginname_in;
 end;
--执行方式1
declare 
  l_loginame_in varchar(20);
  name_out varchar(40);
begin
     l_loginame_in:='lfzhou';
     proc_getUserName(loginname_in=>l_loginame_in,name_out => name_out);
     dbms_output.put_line(l_loginame_in||' 的中文名是:'||name_out);
end;
--执行方式2
declare  
  name_out2 varchar(40);
begin
     proc_getUserName('lfzhou', name_out2);
     dbms_output.put_line(' 的中文名是1:'||name_out2);
end;
 --例子4  
create or replace procedure proc_adminUserInfo
as
cursor userList_cur is select c_name,c_email from s_users where c_com_id='10' and i_admin='0';
userName s_users.c_name%type ;
email s_users.c_email%type ;
begin
     open userList_cur;
     dbms_output.put_line('start');
     ---while userList_cur%Found loop
     loop
          fetch userList_cur into userName,email;
           dbms_output.put_line(userName||'的邮箱是'||email);
        exit when    userList_cur%Notfound;
      end loop;          
     close userList_cur;
end proc_adminUserInfo;
begin
proc_adminUserInfo();
end;