1.
declare
v_name varchar2(20);
begin
v_name:='myname';
dbms_output.put_line(v_name);
end;
/
2.
declare
v_name number:=0;
begin
v_name:=2/v_name;
dbms_output.put_line(v_name);
exception
when others then
dbms_output.put_line('errors');
end;
/
--變量聲明的規則
1.變量名不能使用保留字，如from、select等
2.第一個字元必須是字母
3.變量名最多包含30個字元
4.不要與資料庫的表或者列同名
5.每一行隻能聲明一個變量
--常用變量類型
1.binary_integer：整數，主要用來計數而不是用來表示字段類型
2.number：數字類型
3.char：定長字元串
4.varchar2：變長字元串
5.date：日期
6.long：長字元串，最長2GB
7.boolean：布爾型，可以取值為true、false和null
constant==final 定義常量的
number（7，3）7位長度 小數點後面3位
declare
--v_count number(7,3):=4000.000;
v_count boolean :=false;
begin
dbms_output.put_line('v_count value' || v_count);
end;
--%type執行個體
declare
--v_count number(7,3):=4000.000;
v_count code_hpzl.dm%type;
v_name v_count%type:=03555555;
begin
dbms_output.put_line('v_count value' || v_count);
end;
--table變量類型 (數組)
declare
type type_table_code_hpzl_dm is table of code_hpzl.dm%type index by binary_integer;
v_count type_table_code_hpzl_dm;
begin
v_count(0):='11';
v_count(-1):='22';
dbms_output.put_line('v_count value' || v_count(0) ||',' ||v_count(-1));
end;
--record變量類型(類似于類)
declare
type type_record_hpzl is record
(
dm code_hpzl.dm%type,
--nr code_hpzl.nr%type
);
v_hpzl type_record_hpzl;
begin
v_hpzl.dm:='4545';
--v_hpzl.nr:='mjmj';
dbms_output.put_line(v_hpzl.dm ||',' ||v_hpzl.nr);
end;
--%rowtype聲明record變量 直接将表的字段綁定 不管以後表的字段如何改變 都可以執行程式
declare
v_hpzl code_hpzl%rowtype;
begin
v_hpzl.dm:='01';
v_hpzl.nr:='mjmj';
dbms_output.put_line(v_hpzl.dm ||',' ||v_hpzl.nr);
end;
--sql語句的運用(select隻能取出一條記錄 <=1 遊标除外)帶exception的情況
declare
v_dm code_hpzl.dm%type;
v_nr code_hpzl.nr%type;
begin
select dm,nr into v_dm,v_nr from code_hpzl where dm='9898';
dbms_output.put_line(v_dm ||',' ||v_nr);
exception
when others then
dbms_output.put_line('沒有記錄');
end;
-------
declare
v_hpzl code_hpzl%rowtype;
begin
select * into v_hpzl from code_hpzl where dm='01';
dbms_output.put_line(v_hpzl.dm ||',' ||v_hpzl.nr);
exception
when others then
dbms_output.put_line('沒有記錄');
end;
-----
declare
v_dm code_hpzl.dm%type:='99';
v_nr code_hpzl.nr%type:='韬韬愛婧婧';
begin
insert into code_hpzl values(v_dm,v_nr);
commit;
end;
-----
declare
v_dm code_hpzl.dm%type:='99';
v_count number;
v_nr code_hpzl.nr%type:='韬韬愛婧婧forever';
begin
--select nr into v_nr from code_hpzl where dm=v_dm;
--update code_hpzl set nr=v_nr where dm between '24' and v_dm;
--dbms_output.put_line(sql%rowcount || '條記錄被影響'); 影響了多少條記錄sql%rowcount
select count(*) into v_count from code_hpzl;
dbms_output.put_line(sql%rowcount || '條記錄被影響');
commit;
end;
--DDL
begin
execute immediate 'create table t (name varchar2(20) default ''aaa'')';
end;
--if
declare
v_dm code_hpzl.dm%type;
begin
select dm into v_dm from code_hpzl where nr='韬韬愛婧婧forever';
if(v_dm < 10) then
dbms_output.put_line('low');
elsif(v_dm < 90) then--注意elsif
dbms_output.put_line('middle');
else --沒有then
dbms_output.put_line('high');
end if;
end;
--dowhile
declare
i binary_integer:=0;
begin
loop
dbms_output.put_line(i);
i := i+1;
exit when(i=10);
end loop;
end;
--while
declare
i binary_integer:=0;
begin
while i<10 loop
dbms_output.put_line(i);
i := i+1;
end loop;
end;
--for ++
begin
for k in 1 .. 10 loop
dbms_output.put_line(k);
end loop;
end;
--for --
begin
for k in reverse 1 .. 10 loop
dbms_output.put_line(k);
end loop;
end;
--錯誤處理
declare
v_name number(3);
begin
select dm into v_name from code_hpzl;
exception
when too_many_rows then
dbms_output.put_line('資料太多了');
when others then
dbms_output.put_line('errors');
end;
declare
v_name number(3);
begin
select dm into v_name from code_hpzl where dm='999';
exception
when no_data_found then
dbms_output.put_line('沒有找到資料');
when others then
dbms_output.put_line('errors');
end;
--遊标
declare
cursor c is select * from code_hpzl;
v_hpzl c%rowtype;
begin
open c;
fetch c into v_hpzl;
dbms_output.put_line(v_hpzl.nr);
close c;
end;
--遊标周遊 do-while實作
declare
cursor c is select * from code_hpzl;
v_hpzl c%rowtype;
begin
open c;
loop
fetch c into v_hpzl;
exit when (c%notfound);
dbms_output.put_line(v_hpzl.nr);
end loop;
close c;
end;
--遊标周遊 while實作
declare
cursor c is select * from code_hpzl;
v_hpzl c%rowtype;
begin
open c;
fetch c into v_hpzl;
while c%found loop
dbms_output.put_line(v_hpzl.nr);
fetch c into v_hpzl;
end loop;
close c;
end;
--遊标周遊 FOR實作
declare
cursor c is select * from code_hpzl;
begin
for v_hpzl in c loop
dbms_output.put_line(v_hpzl.nr);
end loop;
end;
--帶參數的遊标
declare
cursor c(v_dm code_hpzl.dm%type,v_nr code_hpzl.nr%type) is select dm,nr from code_hpzl where dm=v_dm and nr=v_nr;
begin
for v_hpzl in c('01','大型汽車') loop
dbms_output.put_line(v_hpzl.nr);
end loop;
end;
--可更新的遊标
declare
cursor c is select * from code_hpzl for update;
begin
for v_hpzl in c loop
if(v_hpzl.dm='24') then
update code_hpzl set nr='韬韬愛婧婧forever' where current of c;
elsif(v_hpzl.dm='99') then
delete from code_hpzl where current of c;
end if;
end loop;
commit;
end;
-----------------------------------------------------------------------
--存儲過程
create or replace procedure p is
cursor c is select * from code_hpzl for update;
begin
for v_hpzl in c loop
if(v_hpzl.dm='24') then
update code_hpzl set nr='123' where current of c;
end if;
end loop;
commit;
end;
create or replace procedure p(v_a in number,v_b number,v_c out number,v_d in out number) is
begin
if(v_a >v_b) then
v_c := v_a;
else
v_c := v_b;
end if;
v_d := v_d + 1;
end;
declare
v_a number := 20;
v_b number := 10;
v_c number;
v_d number;
begin
p(v_a,v_b,v_c,v_d);
dbms_output.put_line(v_c);
dbms_output.put_line(v_d);
end;
drop proedure p;
--函數
create or replace function p (v_a number) return number is
begin
if(v_a>15) then
return 0.01;
elsif(v_a>20) then
return 0.15;
else
return 0.20;
end if;
end;
![SQL優化SQL語句優化的目的[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)