pl/sql基础

dipan8982

于 2019-01-20 21:55:00 发布

阅读量87

点赞数

原文链接：http://www.cnblogs.com/Soper/p/10292147.html

版权

结构化查询语言pls/ql的基本单位是块，结构由三部分组成，声明部分、执行部分和异常处理部分。

[DECLARE]          ---声明部分，包括变量、常量、类型等
  BEGIN            ---执行开始语言
     ---------     ---执行语句
   [EXCEPTION]     ---异常处理部分
  END;             ---执行结束

例如：表info(id,name,price)

DECLARE   
 v_result   number(10,2);    
  BEGIN                                        
     select id into v_result from info where name='饮料';   
     dbms_output.put_line('结果为：'||v_result);
   exception 
     WHEN no_data_found THEN
       dbms_output.put_line('没有找到数据');
  END;

1.定义变量与常量

---%type类型

DECLARE  
  v_productid      info.id%TYPE;    --%TYPE声明变量，类型同表info.id的一致
  v_productname    VARCHAR2(10);
  v_produceprice   NUMBER(10,2);
  v_desperation    CONSTANT v_productname%TYPE :='测试'; --利用%TYPE 引用v_productname的类型，并声明一个常量
  v_date           DATE :=SYSDATE;  
  BEGIN                                       
     select id,name,price into v_productid,v_productname, v_produceprice
      from info where name='饮料'; 
      DBMS_OUTPUT.put_line('结果为：'||v_productid);
      DBMS_OUTPUT.put_line('结果为：'||v_productname);
      DBMS_OUTPUT.put_line('结果为：'||v_produceprice);
      DBMS_OUTPUT.put_line('结果为：'||v_desperation);
      DBMS_OUTPUT.put_line('结果为：'||v_date);
   EXCEPTION 
     when no_data_found then
       dbms_output.put_line('没有找到数据');
  END;

---record类型

DECLARE  
TYPE product_rec IS RECORD          --记录类型的声明
(
  v_productid      info.id%TYPE,   --%TYPE声明变量，类型同表info.id的一致
  v_productname    VARCHAR2(10),
  v_produceprice   NUMBER(10,2)
);
 v_product product_rec;
  BEGIN                                       
     select id,name,price into v_product
      from info where name='饮料'; 
      DBMS_OUTPUT.put_line('结果为：'||v_product.v_productid);
      DBMS_OUTPUT.put_line('结果为：'||v_product.v_productname);
      DBMS_OUTPUT.put_line('结果为：'||v_product.v_produceprice);

   EXCEPTION 
     when no_data_found then
       dbms_output.put_line('没有找到数据');
  END;

--%ROWPYTE

DECLARE  
 v_product info%ROWTYPE;
  BEGIN                                       
     select * into v_product
      from info where name='饮料'; 
      DBMS_OUTPUT.put_line('结果为：'||v_product.id);
      DBMS_OUTPUT.put_line('结果为：'||v_product.name);
      DBMS_OUTPUT.put_line('结果为：'||v_product.price);

   EXCEPTION 
     when no_data_found then
       dbms_output.put_line('没有找到数据');
  END;

2.条件控制语句

---IF..ELSE..

DECLARE  
  v_result number(10);
  BEGIN                                       
     v_result :=1;
     IF v_result>10 THEN
      DBMS_OUTPUT.put_line('大于10：'||v_result);
     ELSIF v_result=10 THEN
      DBMS_OUTPUT.put_line('等于10：'||v_result);
     ELSIF v_result<10 THEN
      DBMS_OUTPUT.put_line('小于10：'||v_result);
      END IF;      
  END;

---CASE

DECLARE  
  v_result number(10);
  BEGIN                                       
     v_result :=1;
     CASE v_result
       WHEN  '10' THEN
        DBMS_OUTPUT.put_line('等于10：'||v_result);
       WHEN  '11' THEN
        DBMS_OUTPUT.put_line('等于11：'||v_result);
       WHEN  '12' THEN
        DBMS_OUTPUT.put_line('等于12：'||v_result);
      ELSE
        DBMS_OUTPUT.put_line('其他：'||v_result); 
        END CASE;    
  END;

DECLARE  
  v_result number(10);
  BEGIN                                       
     v_result :=1;
     CASE 
       WHEN v_result>10 THEN
        DBMS_OUTPUT.put_line('大于10：'||v_result);
       WHEN v_result=10 THEN
        DBMS_OUTPUT.put_line('等于10：'||v_result);
       WHEN v_result<10THEN
        DBMS_OUTPUT.put_line('小于10：'||v_result);
      ELSE
        DBMS_OUTPUT.put_line('其他：'||v_result); 
        END CASE;    
  END;

3.循环控制语句

---loop

declare
v_num number(10):=1;
begin  
  <<basic_loop>>            --可以省略，结束时候直接 exit；
  loop
    dbms_output.put_line('结果是：'||v_num);
    v_num:=v_num+1;
    if v_num>100 then          --可以使用 exit basic_loop when v_num>10;
      exit basic_loop;
     end if;
  end loop;
end;

declare
sum_i int:=1;
i int:=1;
begin
 loop
  i:=i+1;
  sum_i :=sum_i +i;
 exit when i =100;
end loop;
dbms_output.put_line('前100个自然数的和是：'||sum_i);
end;

---for

declare
sum_i int:=0;
begin
 for i in reverse 1..1000 loop -- reverse反向  表示i从100递减
  sum_i:= sum_i+i;
 end loop;
dbms_output.put_line('前100个自然数的和是：'||sum_i);
end;

---while

declare
sum_i int :=0;
i int :=0;
begin
 while i<1000 loop
  i:=i+1;
  sum_i:=sum_i+i;
 end loop;
 dbms_output.put_line('前100个自然数的和是：'||sum_i);
end;

4.异常处理

预定义异常

exception
 when too_many_rows then                          
  dbms_output.put_line('返回记录超过一行');
 when no_data_found then                         
  dbms_output.put_line('无数据记录');

自定义异常

declare
v_price info.price%TYPE;
primary_iterant exception;  --定义一个异常变量
pragma exception_init(primary_iterant,-00001);  --关联错误号和异常变量名
begin
 select price into v_price from info where id = 1;
     if v_price < 1 then 
        raise primary_iterant;
     end if;
exception
 when primary_iterant then
  dbms_output.put_line('价格不能小于1！');
end;

5函数编写（商品数量低于50 的打75折，高于50的打9折）

create or replace function pric     --or replace 覆盖同名函数
(v_pric in number,v_qnty in number)
return number     --函数的返回类型
is
begin
  if v_qnty<50 then
    return(v_pric *0.75);
    else
      return(v_pric*0.9);
   end if;
 end;

调用方法

1
declare 
avgl number;
begin
  avgl:=pric(100,40);
    dbms_output.put_line(avgl);
 end;
2
select pric(100,40) from tab;

--删除函数
drop function get_avg_pay;

查看函数

col name format a15              --格式化字段长度
col text   format a80
select name,line,text from user_source where name ='pric';

转载于:https://www.cnblogs.com/Soper/p/10292147.html