set serveroutput on;
--------------------基本语法---------------------------
declare
v_price number(10,2);
v_usenum number;
v_usenum2 number(10,2);
v_money number(10,2);
begin
v_price:=2.45;
v_usenum:=9213;
v_usenum2:=round(v_usenum/1000,2);
v_money:=v_price*v_usenum2;
dbms_output.put_line(‘金额：‘||v_money);
end;
declare
v_price number(10,2);
v_usenum number;
v_usenum2 number(10,2);
v_money number(10,2);
v_num0 number;
v_num1 number;
begin
v_price:=2.45;
select usenum into v_usenum from t_account
where year=2012 and month=‘01‘ and owneruuid=1;
v_usenum2:=round(v_usenum/1000,2);
v_money:=v_price*v_usenum2;
dbms_output.put_line(‘字数‘||v_usenum||‘金额：‘||v_money);
end;
--------------------属性类型---------------------------
--属性类型（引用型 表名.列名%type）
declare
v_price number(10,2);
v_usenum t_account.usenum%type;
v_usenum2 number(10,2);
v_money number(10,2);
v_num0 t_account.num0%type;
v_num1 t_account.num1%type;
begin
v_price:=2.45;
select usenum into v_usenum from t_account
where year=2012 and month=‘01‘ and owneruuid=1;
v_usenum2:=round(v_usenum/1000,2);
v_money:=v_price*v_usenum2;
dbms_output.put_line(‘字数‘||v_usenum||‘金额：‘||v_money);
end;
--属性类型（记录型 表名.列名%rowtype）
declare
v_price number(10,2);
v_usenum2 t_account.usenum%type;
v_money number(10,2);
v_account t_account%rowtype;
begin
v_price:=2.45;
select*into v_account from t_account
where year=2012 and month=‘01‘ and owneruuid=1;
v_usenum2:=round(v_account.usenum/1000,2);
v_money:=v_price*v_usenum2;
dbms_output.put_line(‘字数‘||v_account.usenum||‘金额：‘||v_money);
end;
--------------------异常---------------------------
--no_data_found(没返回行)--
declare
v_price number(10,2);
v_usenum2 t_account.usenum%type;
v_money number(10,2);
v_account t_account%rowtype;
begin
v_price:=2.45;
select*into v_account from t_account
where year=2012 and month=‘01‘ and owneruuid=200;
v_usenum2:=round(v_account.usenum/1000,2);
v_money:=v_price*v_usenum2;
dbms_output.put_line(‘字数‘||v_account.usenum||‘金额：‘||v_money);
exception
when no_data_found then
dbms_output.put_line(‘没有找到数据‘);
end;
--too_many_rows(结果集超过一行)--
declare
v_price number(10,2);
v_usenum2 t_account.usenum%type;
v_money number(10,2);
v_account t_account%rowtype;
begin
v_price:=2.45;
select*into v_account from t_account
where year=2012 and month=‘01‘;
v_usenum2:=round(v_account.usenum/1000,2);
v_money:=v_price*v_usenum2;
dbms_output.put_line(‘字数‘||v_account.usenum||‘金额：‘||v_money);
exception
when no_data_found then
dbms_output.put_line(‘没有找到数据‘);
when too_many_rows then
dbms_output.put_line(‘返回多行数据‘);
end;
--------------------条件判断---------------------------
declare
v_price1 number(10,2);
v_price2 number(10,2);
v_price3 number(10,2);
v_usenum2 t_account.usenum%type;
v_money number(10,2);
v_account t_account%rowtype;
begin
v_price1:=2.45;
v_price2:=3.45;
v_price3:=4.45;
select*into v_account from t_account
where year=2012 and month=‘01‘ and owneruuid=1;
v_usenum2:=round(v_account.usenum/1000,2);
if v_usenum2<=5 then
v_money:=v_price1*v_usenum2;
elsif v_usenum2>5 and v_usenum2<=10 then
v_money:=v_price1*5+v_price2*(v_usenum2-5);
else
v_money:=v_price1*5+v_price2*5+v_price3*(v_usenum2-10);
end if;
dbms_output.put_line(‘字数‘||v_account.usenum||‘金额：‘||v_money);
end;
--------------------循环---------------------------
--无条件循环--
declare
v_num number;
begin
v_num:=1;
loop
dbms_output.put_line(v_num);
v_num:=v_num+1;
if v_num>100 then
exit;
end if;
end loop;
end;
--有条件循环--
declare
v_num number;
begin
v_num:=1;
while v_num<=100
loop
dbms_output.put_line(v_num);
v_num:=v_num+1;
end loop;
end;
--for循环--
declare
v_num number;
begin
for v_num in 1..100
loop
dbms_output.put_line(v_num);
end loop;
end;
--------------------游标（存放sql语句的执行结果）---------------------------
select*from t_pricetable where ownertypeid=1;
declare
--声明游标--
cursor cur_pricetable is select*from t_pricetable where ownertypeid=1;
v_pricetable t_pricetable%rowtype;
begin
--打开游标--
open cur_pricetable;
loop
--提取游标--
fetch cur_pricetable into v_pricetable;
--退出循环游标--
exit when cur_pricetable%notfound;
dbms_output.put_line(‘价格:‘||v_pricetable.price||‘吨位：‘||v_pricetable.minnum||‘-‘||v_pricetable.maxnum);
end loop;
--关闭游标--
close cur_pricetable;
end;
--带参数的游标--
declare
--声明游标--
cursor cur_pricetable(v_ownertype number) is select*from t_pricetable where ownertypeid=v_ownertype;
v_pricetable t_pricetable%rowtype;
begin
--打开游标--
open cur_pricetable(2);
loop
--提取游标--
fetch cur_pricetable into v_pricetable;
--退出循环游标--
exit when cur_pricetable%notfound;
dbms_output.put_line(‘价格:‘||v_pricetable.price||‘吨位：‘||v_pricetable.minnum||‘-‘||v_pricetable.maxnum);
end loop;
--关闭游标--
close cur_pricetable;
end;
--for循环带参数游标--
declare
--声明游标--
cursor cur_pricetable(v_ownertype number) is select*from t_pricetable where ownertypeid=v_ownertype;
begin
for v_pricetable in cur_pricetable(1)
loop
dbms_output.put_line(‘价格:‘||v_pricetable.price||‘吨位：‘||v_pricetable.minnum||‘-‘||v_pricetable.maxnum);
end loop;
end;
--------------------存储函数(自定义函数)(用于查询比较多)---------------------------
create or replace function fn_getaddress
(v_id number)
return VARCHAR2
is
v_name varchar2(30);
begin
select name into v_name from t_address where id=v_id;
return v_name;
end;
--存储函数测试--
select fn_getaddress(3) from dual;
--存储函数应用--
select id,name,fn_getaddress(addressid) from t_owners;
create sequence seq_owners start with 11;
--------------------存储过程(可以返回多个值，通过传出参数返回值)---------------------------
--不带传出参数的存储过程--
create or replace procedure pro_owners_add
(v_name varchar2,v_addressid number,v_housenumber varchar2,v_watermeter varchar2,v_ownertypeid number)
is
begin
insert into t_owners values(seq_owners.nextval,v_name,v_addressid,v_housenumber,v_watermeter,sysdate,v_ownertypeid);
commit;
end;
--调用不带传出参数的存储过程--
call pro_owners_add(‘mada‘,2,‘2233‘,‘66777‘,1);
begin
pro_owners_add(‘maxida‘,2,‘2233‘,‘66777‘,1);
end;
--带传出参数的存储过程--
create or replace procedure pro_owners_add
(v_name varchar2,v_addressid number,v_housenumber varchar2,v_watermeter varchar2,v_ownertypeid number,v_id out number)
is
begin
--对传出参数赋值--
select seq_owners.nextval into v_id from dual;
--新增业主记录--
insert into t_owners values(v_id,v_name,v_addressid,v_housenumber,v_watermeter,sysdate,v_ownertypeid);
commit;
end;
--调用传出参数的存储过程--
call pro_owners_add(‘mada‘,2,‘2233‘,‘66777‘,1);
declare
v_id number;
begin
pro_owners_add(‘xxx‘,2,‘2233‘,‘223232‘,1,v_id);
dbms_output.put_line(v_id);
end;
select*from t_owners
--------------------触发器--------------------------
--前置触发器（可以修改想要插入的值）--
create or replace trigger tri_account_num1
before
update of num1
on t_account
for each row
declare
begin
--通过伪记录变量修改usenum字段的值
:new.usenum:=:new.num1-:new.num0;
end;
--后置触发器--
--当用户修改了业主信息表的数据时，记录修改前与修改后的值--
--业主名称修改日志--
create table t_owners_log(
updatetime date,
ownerid number,
oldname varchar2(30),
newname varchar2(30)
)
create or replace trigger tri_owners_log
after
update of name
on t_owners
for each row
declare
begin
--向日志表插入记录
insert into t_owners_log values(sysdate,:new.id,:old.name,:new.name);
end;
update t_owners set name=‘liuhua‘ where id=5
select*from t_owners