存储过程

-- 存储过程
-- 创建，PL/SQL
create or replace procedure up_test
as
i number;
begin
  i := 1;
  dbms_output.put_line('变量值是:'||i);
end;
/


-- 使用;JAVA代码
exec up_test;


-- 例子，对表操作
create or replace procedure up_test
as
begin
 update userinfo set usermoney = 3000 where userid = 83;
end;
/




exec up_test;
--update userinfo set usermoney = 3000 where userid = 83;


-- 有参数的存储过程,参数不能写数量，只能写类型
create or replace procedure up_test(i number,j varchar2)
as
begin
  dbms_output.put_line('参数一是：'||i);
  dbms_output.put_line('参数二是:'||j);
end;
/


-- 调用带输入参数的存储过程
exec up_test(1,'abc');


-- 借一张表
create
-- 有输入参数，同时有变量
-- 输入一个员工号empno,查询出这个员工的名字
create or replace procedure up_test(i number)
as
v_ename scott.emp.ename%type;
begin
  select scott.emp.ename into v_ename from scott.emp where scott.emp.empno = i;
  dbms_output.put_line('这个号对应的员工是：'||v_ename);
end;
/


-- 调用
exec up_test(7369);


-- 输出存储过程


create or replace procedure up_test(i out number)
as
begin
 i:=200;
end;
/


declare savei number;
begin
 up_test(savei);
 dbms_output.put_line('给我的值是：'||savei);
end;
/


-- 存储过程登录
create or replace procedure up_test(a1 number,i out number)
as
v_empno2 emp.empno%type;
begin
  select empno into v_empno2 from emp where empno =a1;
  i:=1;
  exception
    when no_data_found then
      i:=0;
end;
/


declare savei number;
begin
 up_test(369,savei);
 dbms_output.put_line('状态是：'||savei);
end;
/




或者！！！！！！！！！


create or replace procedure up_test(a_empno number,a_ename varchar2,i out number)
as
v_empno emp.empno%type;
begin
 select count(empno) into v_empno from emp where empno = a_empno and ename = a_ename;
 if v_empno = 1 then
   i:=1;
 else
   i:=0;
 end if;
end;
/


declare savei number;
begin
 up_test(369,'SMITH',savei);
 if savei = 1 then
   dbms_output.put_line('登录成功'||savei);
 else
   dbms_output.put_line('登录失败'||savei);
 end if;
end;
/