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=)