PL/SQL 学习
--打开sql*plus的输出功能
/*
set servecroutput on
*/
//只有执行体
begin
dbms_output.put_line('程序执行部分');
end;
//声明与执行体
declare
v_resault number(8,2);
begin
v_resault:=100/6;
dbms_output.put_line('执行结果为:'||v_resault);
end;
insert into productinfo(ProId,ProName,ProPrice,Quantity,Procategory,description,origin) values (3,'iphone4',3000,12,02,'苹果手机','apple')
//声明,执行体,异常处理
--select ........... into 赋值语句,用select后面的字段列表 into变量名称
DECLARE
--变量声明
v_categoryid varchar(12) NOT NULL := 1;
--常量声明
cons constant VARCHAR2(10) NOT NULL DEFAULT 3*4;
BEGIN
select Procategory into v_categoryid from productinfo where proid = '3';--字符类型,日期类型要用单引号括起
dbms_output.put_line('执行结果为:' || v_categoryid);
EXCEPTION
when no_data_found then
dbms_output.put_line('未找到数据');
when too_many_rows then
dbms_output.put_line('对应 数据够多请确认');
END;
--标量类型的变量
DECLARE
v_proid productinfo.proid%TYPE;
v_proname VARCHAR2(20);
v_proprice number(8, 2);
v_quantity number(10);
v_description VARCHAR2(50);
v_simint SIMPLE_INTEGER := 99.99;
v_long LONG := '长度小于32760的字符串';
v_date DATE := SYSDATE;
BEGIN
SELECT ProId, ProName, ProPrice, Quantity, description
INTO v_proid, v_proname, v_proprice, v_quantity, v_description
FROM productinfo
WHERE proid = 3;
DBMS_OUTPUT.PUT_LINE('v_proid=' || v_proid);
DBMS_OUTPUT.PUT_LINE('v_proname=' || v_proname);
DBMS_OUTPUT.PUT_LINE('v_proprice=' || v_proprice);
DBMS_OUTPUT.PUT_LINE('v_quantity=' || v_quantity);
DBMS_OUTPUT.PUT_LINE('v_description=' || v_description);
DBMS_OUTPUT.PUT_LINE('v_simint=' || v_simint);
DBMS_OUTPUT.PUT_LINE('v_long=' || v_long);
END;
-- 复合类型 --------------------记录类型
DECLARE
TYPE product_rec IS RECORD(
v_proid productinfo.proid%TYPE,
v_proname VARCHAR2(20),
v_proprice number(8, 2),
v_quantity number(10),
v_description VARCHAR2(50));
v_product product_rec;
/* v_simint SIMPLE_INTEGER := 99.99;
v_long LONG := '长度小于32760的字符串';
v_date DATE := SYSDATE;*/
BEGIN
SELECT ProId, ProName, ProPrice, Quantity, description
INTO v_product
FROM productinfo
WHERE proid = 3;
DBMS_OUTPUT.PUT_LINE('v_proid=' || v_product.v_proid);
DBMS_OUTPUT.PUT_LINE('v_proname=' || v_product.v_proname);
DBMS_OUTPUT.PUT_LINE('v_proprice=' || v_product.v_proprice);
DBMS_OUTPUT.PUT_LINE('v_quantity=' || v_product. v_quantity);
DBMS_OUTPUT.PUT_LINE('v_description=' || v_product. v_description);
END;
/
--利用%ROWTYPE声明记录类型
DECLARE
v_product productinfo%Rowtype;
BEGIN
select * into v_product from productinfo where proid = 3;
DBMS_OUTPUT.PUT_LINE('v_proid=' || v_product.proid);
DBMS_OUTPUT.PUT_LINE('v_proname=' || v_product.proname);
DBMS_OUTPUT.PUT_LINE('v_proprice=' || v_product.proprice);
end;
-- 复合类型 --------------------变长数组
--声明一个元素类型为VARCHAR2(20)长度为100的数组
Declare
TYPE varr IS VARRAY(100) OF VARCHAR2(20);
v_pro varr := varr('1', '2');
begin
v_pro(1) := 'test';
v_pro(2) := 'DAda';
end;
-- 复合类型 --------------------索引表类型-----太难了
---运算
DECLARE
v_result number(9,4);
begin
v_result :=sqrt(34+100/4);
end;
---PL/SQL的结构控制------------------------------------------------------------------
--控制语句if
declare
v_resault number(10, 4);
begin
v_resault := 12.223;
if (v_resault > 10) then
Dbms_Output.put_line('如果小于十则输出:' || v_resault);
end if;
end;
declare
v_resault number(10, 4);
begin
v_resault := 12.223;
if (v_resault > 10) then
Dbms_Output.put_line('如果大于十则输出:' || v_resault);
else
Dbms_Output.put_line('如果小于等于十则输出:' || v_resault);
end if;
end;
declare
v_resault number(10, 4);
begin
v_resault := 12.223;
if (v_resault > 10) then
Dbms_Output.put_line('如果大于十则输出:' || v_resault);
elsif v_resault < 10 then
Dbms_Output.put_line('如果小于十则输出:' || v_resault);
elsif v_resault = 10 then
Dbms_Output.put_line('如果等于十则输出:' || v_resault);
end if;
end;
--CASE控制语句----------------
--简单case语句
declare
v_procategory VARCHAR2(20);
begin
select PROCATEGORY into v_procategory from productinfo where proid = 3;
<<PROCATEGORY>>case v_procategory
when 01 then
Dbms_Output.put_line('v_procategory:' ||
v_procategory);
when 02 then
Dbms_Output.put_line('v_procategory:' ||
v_procategory);
when 03 then
Dbms_Output.put_line('v_procategory:' ||
v_procategory);
else
Dbms_Output.put_line('无此类型');
end case PROCATEGORY;
end;
--搜索式case语句
declare
v_proprice number(10);
begin
select proprice into v_proprice from productinfo where proid = 3;
<<ssss>>case
when v_proprice > 5500 then
Dbms_Output.put_line('价格大于5500' || v_proprice);
when v_proprice <= 5500 and v_proprice > 3000 then
Dbms_Output.put_line('价格大于3000小于5500' || v_proprice);
when v_proprice <= 3000 then
Dbms_Output.put_line('价格小于等于3000' || v_proprice);
else
Dbms_Output.put_line('错误价格' || v_proprice);
end case ssss;
end;
--LOOP循环控制语句——————————————————————————————
--基本loop---ol/sql语句都要有end
declare
v_number number(8) := 1;
begin
<<basic_loop>>
loop
Dbms_Output.put_line('当前v_number为:' || v_number);
v_number := v_number + 1;
if v_number > 5 then
Dbms_Output.put_line('退出,当前v_number为:' || v_number);
exit basic_loop;
end if;
end loop basic_loop;
end;
--excit............when.............
declare
v_number number(8) := 1;
begin
<<basic_loop>>
loop
Dbms_Output.put_line('当前v_number为:' || v_number);
v_number := v_number + 1;
exit basic_loop when v_number>5;
end loop basic_loop;
Dbms_Output.put_line('退出,当前v_number为:' || v_number);
end;
---while .........loop........类似java中的while语句
declare
v_number number(8) := 1;
begin
<<while_loop>>
while v_number < 20 loop
Dbms_Output.put_line('当前v_number为:' || v_number);
v_number := v_number + 1;
end loop while_loop;
Dbms_Output.put_line('退出,当前v_number为:' || v_number);
end;
--改进----------类似java中的do,。。while
declare
v_number number(8) := 19;
v_bol boolean := true;
begin
<<while_loop>>
while v_bol loop
Dbms_Output.put_line('当前v_number为:' || v_number);
v_number := v_number + 1;
if v_number > 20 then
v_bol := false;
end if;
end loop while_loop;
Dbms_Output.put_line('退出,当前v_number为:' || v_number);
end;
-- for 。。。。。。。。。loop 。。。。。。。
declare
v_number number(8):=0;
begin
<<for_loop>>
FOR ind in reverse 1..20
loop
Dbms_Output.put_line('当前v_number为:' || v_number);
v_number := v_number + ind;
end loop for_loop;
Dbms_Output.put_line('退出,当前v_number为:' || v_number);
end;
--DDL语句的使用
declare
p_createddl varchar2(200);
begin
p_createddl :='create table test_tab
(
id number(7),
name varchar2(20)
) ';
execute immediate p_createddl;
end;
select * from test_tab
--异常处理
------预定义异常-----------------------------------
declare
v_rslt number(10);
begin
v_rslt := 100 / 0;
Dbms_Output.put_line('结果为:' || v_rslt);
exception
when zero_divide then
Dbms_Output.put_line('结果为:' || 100 / 1);
end;
---非预定义异常----------------------
declare
my_exception exception;
pragma exception_init(my_exception,-2291);
v_catg varchar2(20);
begin
v_catg :='65';
update productinfo set productinfo.procategory=v_catg;
exception
when my_exception then
Dbms_Output.put_line('违反完整性约束');
end;
---自定义异常---------------显示抛出
declare
v_proid productinfo.proid%TYPE:='23';
v_qty productinfo.quantity%TYPE;
qty_exception exception;
pragma exception_init(qty_exception,-20001);
begin
select productinfo.quantity into v_qty from productinfo where productinfo.proid=v_proid;
if(v_qty<0) then
raise qty_exception;
end if;
exception
when qty_exception then
Dbms_Output.put_line('产品数量为空');
rollback;
end;