查看存儲過程
select * from user_source
準備數(shù)據(jù)
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, Age in int )
as
begin
? ? update students set UserAge = UserAge + Age where userName = uName;
? ? commit;
end SP_Update_Age;
調(diào)用存儲過程
begin
? ? SP_UPDATE_AGE('jack',1);
end;
For循環(huán)
DECLARE x int;
BEGIN
? ? FOR i IN 1..10 LOOP
? ? ? ? IF MOD(i,2) = 0 THEN
? ? ? ? ? ? dbms_output.put_line( 'i: '||i||' is even ' );
? ? ? ? ELSE
? ? ? ? ? ? dbms_output.put_line('i: '|| i||' is odd' );
? ? ? ? END IF;
? ? END LOOP;
? ? COMMIT;
END;
while循環(huán)
create or replace Procedure Test2(i in out number)?
as?
begin?
? ? while i < 10 loop?
? ? ? ? begin?
? ? ? ? ? ? i:= i+1;?
? ? ? ? end;?
? ? end loop;?
end Test2;?
動態(tài)執(zhí)行sql
declare v_sql varchar2(2000);?
begin?
? ? v_sql:='insert into test values (sysdate)';?
? ? execute immediate v_sql;?
? ? commit;?
end;?
返回結果集
create or replace procedure sql_test(out_return out sys_refcursor) is
begin
? ? open out_return for 'select * from emp';
end;
commit;
declare
? cur1? SYS_REFCURSOR;
? i? ? ? emp%rowtype;
begin
sql_test(cur1);
? loop
? ? fetch cur1 into i;
? ? exit when cur1%notfound;
? ? dbms_output.put_line('EMPNO:' || i.EMPNO);
? end loop;
? close cur1;
end;
游標
declare
v_cur PKG_TYPES.REFCURSOR;
v_test clob;
vvv number(10);
begin
v_test := 'select 1 from dual';
open v_cur for v_test;
loop
fetch v_cur into vvv;
exit when v_cur%notfound;
dbms_output.put_line(vvv);
end loop;
end;
