pl/sql&&存储过程

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;