oracle基础学习(2)

oracle循环的几种写法:
declare
x number :=5;
begin
--x:=0;
loop
x:=x+1;
/*
if (x=10) then 
exit; 
end if;
*/
exit when x=10;
dbms_output.put_line('x='||x);
end loop;
dbms_output.put_line('outer');
end;
/
------------------------------
declare
x number :=5;
begin
while x<10 loop
x:=x+1;
dbms_output.put_line('x='||x);
end loop;
dbms_output.put_line('outer');
end;
/

-----------------------------
begin
for x in 2..10 loop
	dbms_output.put_line('x='||x);
end loop;
end;
/
-------------------------------
declare
x number:=5;
begin
<>	--标记
x:=x+1;
dbms_output.put_line('x='||x);
if x<10 then 
goto label1;
end if;
end;
/
---------------

异常处理
Exception
when .. then
...
常见系统异常
DUP_VAL_ON_INDEX
NO_DATA_FOUND
TOO_MANY_ROWS
VALUE_ERROR
ZERO_DIVIDE

e.g.:

declare
va varchar2(20);
begin
select A into va from abc where F_NVAR='abc';
dbms_output.put_line(va);
exception
when NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('未找到数据');
end;
-----------------------

declare
va varchar2(20);
vi integer;
begin
select A into va from abc where F_NVAR='中国人民万';
dbms_output.put_line(va);
vi:=1/0;
exception
when NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('未找到数据');
when OTHERS THEN
DBMS_OUTPUT.PUT_LINE('其它问题');
end;
---------------------

declare
va varchar2(20);
vi integer;
e exception;
begin
select A into va from abc where F_NVAR='中国人民万';
dbms_output.put_line(va);
--vi:=1/0;
raise e;
exception
when NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('未找到数据');
when e THEN
DBMS_OUTPUT.PUT_LINE('发生问题A');
when OTHERS THEN
DBMS_OUTPUT.PUT_LINE('其它问题');
end;
-----------------------

复合变量:记录
TYPE type_name IS RECORD(
Variable_name datatype,
Variable_name datatype,
...
);

-----------

declare 
myrec abc%rowtype;
begin
select * into myrec from abc where a='aaa';
dbms_output.put_line(myrec.a||myrec.b||myrec.c);
end;

-------------------
ROW_NUMBER() 函数 与 ROWNUM伪列
select row_number() over (order by a) sa,a,b,c,f_nvar from abc
select * from abc where rownum<10


declare 
myrec abc%rowtype;
begin
select * into myrec from abc where a='AAA1111123' and ROWNUM<2;
dbms_output.put_line(myrec.a||myrec.b||myrec.c||myrec.f_nvar);
end;
-------------------------------------
一个简单的存储过程:
create or replace procedure proc_show_abc_by_a
(
pa varchar2
)
as
fa varchar2(20);
begin
select a into fa from spark.abc where a=pa;
dbms_output.put_line(fa);
end;
/

-------------------------------------
同义词
 create or replace public synonym myabc for spark.abc;
-------------------------------------
序列
create sequence myseq
start with 1
increment by 1
order
nocycle;