四.PL/SQL学习

来源：互联网发布：数据预处理之归一化编辑：程序博客网时间：2024/06/05 17:34

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;