oracle note2 存储过程

declare
x varchar2(10):='ssss';
begin
--x:='This is..';
dbms_output.put_line('x''s value is' ||x);
end;
/
===============================
SQL> set serveroutput on
-----------------if------------------------------
declare
a number;
b nvarchar2(10);
begin
a:=2;
if a=1 then b:='a';
elsif a=2 then b:='b';
else b:='c';
end if;
dbms_output.put_line('b''s value is '||b);
end;
/
------------
SQL> c /anumber/a number
------------case-------------------------------------
declare
a number;
b nvarchar2(10);
begin
a:=4;
case
when a=1 then b:='a';
when a=2 then b:='b';
else b:='others';
end case;
dbms_output.put_line('b''s value is '||b);
end;
/
------------------loop--------------------------------
declare
a number;
begin
a:=0;
loop
a:=a+1;
if a>=3 then
exit;
end if;
dbms_output.put_line('inside:'||a);
end loop;
dbms_output.put_line('outside:'||a);
end;
---------------------exit when ----------------------
declare
a number;
begin
a:=0;
loop
a:=a+1;
exit when a=5;
dbms_output.put_line('inside:'||a);
end loop;
dbms_output.put_line('outside:'||a);
send;
-------------------while ---------------------------
declare
a number;
begin
a:=0;
while a<=3 loop
a:=a+1;
dbms_output.put_line('inside:'||a);
end loop;
dbms_output.put_line('outside:'||a);
end;
/
---------------------------------for---------------
for 对应的计数器不需要定义
begin
for i in 1..5 loop
dbms_output.put_line('i is '||i);
end loop;
dbms_output.put_line('out for');
end;
/
begin
for i in reverse 1..5 loop
dbms_output.put_line('i is '||i);
end loop;
dbms_output.put_line('out for');
end;
/
-----------------------GOTO------------------------------
declare
a number;
begin
a:=0;
<<repeat>>
a:=a+1;
dbms_output.put_line('inrepeat:'||a);
if a<3 then goto repeat;
end if;
dbms_output.put_line('outrepreat:'||a);
end;
/
-------------------------exception--------------------------
declare
v_name varchar2(10);
e exception;
begin
select name into v_name from table1 where id = 5;
if v_name<>'name' then raise e;
end if;
dbms_output.put_line(v_name);
exception
when e then dbms_output.put_line('wrong');
when no_data_found then dbms_output.put_line('no date');
end;
/
-----------------------------record-----------------------------
declare
type myrecord is record(
v_id number,
v_name varchar(10),
v_age number);
real_record myrecord;
begin
select id,name,age into real_record from table1 where id = 1;
dbms_output.put_line(real_record.v_id||','||real_record.v_name||','||real_record.v_age);
end;
/
declare
myrec table1%rowtype;
begin
select * into myrec from table1 where id = 1;
dbms_output.put_line(myrec.id||','||myrec.name||','||myrec.sex||','||myrec.age);
end;
/

------------------------------cursor--------------------
declare
cursor mycur is select * from table1;
myrecord table1%rowtype;
begin
open mycur;
fetch mycur into myrecord;
while mycur%found loop
dbms_output.put_line(myrecord.id||','||myrecord.name||','||myrecord.sex||','||myrecord.age);
fetch mycur into myrecord;
end loop;
close mycur;
end;
/
declare
cursor mycur(v_id number) is select * from table1 where id=v_id;
myrecord table1%rowtype;
begin
open mycur(1);
fetch mycur into myrecord;
while mycur%found loop
dbms_output.put_line(myrecord.id||','||myrecord.name||','||myrecord.sex||','||myrecord.age);
fetch mycur into myrecord;
end loop;
close mycur;
end;
/
--------------------------------------------
begin
for i in 1..100 loop
insert into testchange values(i,i,i,i,i,i,i,i,i,i,i,i,i);
end loop;
end;
-------------------------------------------------------
declare
v_name table1.name%type;
cursor cur_my is select name from table1;
begin
open cur_my;
fetch cur_my into v_name;
while cur_my%found loop
dbms_output.put_line('the record num is '||cur_my%rowcount);
fetch cur_my into v_name;
end loop;
close cur_my;
end;

declare
v_name table1.name%type;
cursor cur_my is select name from table1;
begin
open cur_my;
fetch cur_my into v_name;
while cur_my%found loop
dbms_output.put_line('the record num is '||cur_my%rowcount);
fetch cur_my into v_name;
end loop;
close cur_my;
end;
/
declare
cursor cur is
select name from table1 for update;
v_name varchar2(10);
begin
open cur
fetch cur into v_name;
while cur%found loop
update table1 set name||'_up' where current of cur;
fetch cur into v_name;
end loop;
close cur;
end;
/
declare
cursor cur is
select name from table1 for update;
v_name varchar2(10);
begin
open cur;
fetch cur into v_name;
while cur%found loop
update table1 set name=name||'_up' where current of cur;
fetch cur into v_name;
end loop;
close cur;
end;
/
------------------------------------------------------------