pl/sql基础
结构化查询语言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';