查看存储过程
select * from user_source
准备数据
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;
调用存储过程
begin
SP_UPDATE_AGE('jack',1);
end;
For循环
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循环
create or replace Procedure Test2(i in out number)
as
begin
while i < 10 loop
begin
i:= i+1;
end;
end loop;
end Test2;
动态执行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;
