create table students
(
ID int,
userName varchar(100),
userPass varchar(100),
userAge int
)
insert into students values(1,'jack','jjjaa',23);
insert into students values(2,'rose','jjjaa',21);
insert into students values(3,'lucy','jjjaa',22);
insert into students values(4,'Tony','jjjaa',24);
commit;
create or replace procedure SP_Update_Age
(
uName in varchar,--note,here don't have length ,sql have lenth ,not in oracle.
Age in int
)
as
begin
update students set UserAge = UserAge + Age where userName = uName;
commit;
end SP_Update_Age;
select * from students
DECLARE
BEGIN
SP_UPDATE_AGE('jack',1);
END;
/
create or replace procedure Test(x in out number)
is
begin
if x<0 then
begin
x:= 0 - x;
end;
elsif x > 0 then --noted here elsif
begin
x:= x ;
end;
else
x:= 0;
end if;
end Test;
复制代码
set serveroutput on; --没这句话,看不到dmbs_output信息。
declare
num number;
begin
num:= -1;
test(num);
dbms_output.put_line( 'num = ' || num );
end;
/******************************
num = 1
PL/SQL procedure successfully completed.
*******************************/
set serveroutput on;
DECLARE
x NUMBER := 100;
BEGIN
FOR i IN 1..10 LOOP --noted here
IF MOD(i,2) = 0 THEN -- i is even
dbms_output.put_line( 'i: '||i||' is even ' );
ELSE
dbms_output.put_line('i: '|| i||' is odd' );
END IF;
x := x + 100;
dbms_output.put_line('x value: '|| x);
END LOOP;
COMMIT;
END;
/*************************
i: 1 is odd
x value: 200
i: 2 is even
x value: 300
i: 3 is odd
x value: 400
i: 4 is even
x value: 500
i: 5 is odd
x value: 600
i: 6 is even
x value: 700
i: 7 is odd
x value: 800
i: 8 is even
x value: 900
i: 9 is odd
x value: 1000
i: 10 is even
x value: 1100
PL/SQL procedure successfully completed.
*************************/