六、PLSQL基础-数据库-半夏微凉

六、PLSQL基础

一、PL/SQL介绍

1.PL/SQL的结构

[DECLARE] -- 声明开始关键字

/*这里是声明部分，包括pl/sql中的变量，常量，以及类型等*/

BEGIN --执行部分开始的标志

/*这里是执行的部分，是整个pl/sql块的主体部分，该部分在pl/sql块中必须存在，可以是sql语句或者是程序流程控制语句等*/

[EXCEPTION] --异常开始部分的关键字

/*这里是异常处理部分，当出现异常时程序流程可以进入此处*/

END; --执行结束的标志

例： set serveroutput on
DECLARE
v_number number(8,2);
BEGIN
select t.shulian into v_number from t_avg t where t.name='苹果1号';
dbms_output.put_line('查询的水果种类数量为：'||v_number);
EXCEPTION
when no_data_found then
dbms_output.put_line('没有这种水果');
when too_many_rows then
dbms_output.put_line('查询水果有多条数据，请检查');
END;
/

2.PL/SQL的基本规则

3.PL/SQL中的注释

（1）单位注释：使用“__”两个短划线，可以注释掉后面的语句。

（2）多行注释：使用“/*...*/”,可以注释掉这两部分包含的部分。

二、PL/SQL变量的使用

1.变量声明语法结构

variable_name datatype
[
[not null]
{:= | default} exception
];

2.常量声明语法结构

constant_name constant datatype

[not null]

{:= | default} exception ;

3.标量类型的变更

（1）数值类型。
number
pls_integer
binary_intener
simple_integer
（2）字符类型。
char
varchar(2)
nchar
nvarchar(2)
long
（3）布尔类型。(BOOLEAN)
true
false
null
（4）日期类型。
date
timestamp
（5）使用%type方式定义变量类型。

例：
set serveroutput on
DECLARE
v_number t_avg.shulian%type;
v_name t_avg.name%type;
v_id t_avg.id%type;
v_time date:=sysdate;
BEGIN
select t.shulian,t.id,t.name into v_number,v_id,v_name from t_avg t where t.name='苹果'; --给变量赋值
dbms_output.put_line('查询的水果名称为：'||v_name||'，ID为：'||v_id||'，数量为：'||v_number);
dbms_output.put_line('查询的时间为：'||v_time);
EXCEPTION
when no_data_found then
dbms_output.put_line('没有这种水果');
when too_many_rows then
dbms_output.put_line('查询水果有多条数据，请检查');
END;
/

4.复合类型的变量

1）.pl/sql的“记录类型”

（1）第一种声明语法：

TYPE type_name IS RECORD

(field_name datatype
[[not null]{:=|default}expression]...

（2）利用%rowtype声明记录类型数据

set serveroutput on
declare
v_jieguo t_avg%rowtype;
begin
select * into v_jieguo from t_avg where t_avg.name='苹果';
dbms_output.put_line('苹果的参数为：'||v_jieguo.id);
end;
/

2）.pl/sql索引表类型（关联数组）

TYPE type_name IS TABLE OF
{column_type |
variable_name%type |
table_name.column_name%type|
table_name%rowtype
}
[not null]
INDEX BY {pls_integer | binary_integer | varchar2(v_size)}

set serveroutput on
declare
type t_sy_lentim is table of number(8) index by varchar2(20);
v_jieguo t_sy_lentim;
begin
v_jieguo('test') :=1;
v_jieguo('test1') :=-1;
dbms_output.put_line('苹果的参数为：='|| v_jieguo('test'));
dbms_output.put_line('苹果的参数为：='|| v_jieguo('test1'));
dbms_output.put_line('first参数为:='|| v_jieguo.first); --变量名称.first 得到第一个或最后一个键值。
dbms_output.put_line('first参数为;='|| v_jieguo(v_jieguo.first));
end;
/
DECLARE
TYPE varray_type IS VARRAY(10) OF NUMBER;
varray varray_type := varray_type(1, 2, 3, 4, 5, 6); --声明6个元素
TYPE varray_string is VARRAY(10) OF VARCHAR2(5);
test_string varray_string := varray_string('A','B');
BEGIN
DBMS_OUTPUT.PUT_LINE('varray.COUNT = ' || varray.COUNT);
DBMS_OUTPUT.PUT_LINE('varray.LIMIT = ' || varray.LIMIT);
DBMS_OUTPUT.PUT_LINE('varray.FIRST = ' || varray.FIRST);
DBMS_OUTPUT.PUT_LINE('varray.LAST = ' || varray.LAST);
varray.EXTEND(2, 4); --扩展两个元素
DBMS_OUTPUT.PUT_LINE('varray.LIMIT = ' || varray.LIMIT);
DBMS_OUTPUT.PUT_LINE('varray.LAST = ' || varray.LAST);
DBMS_OUTPUT.PUT_LINE('varray(' || varray.LAST || ') = ' ||
varray(varray.LAST));
varray.TRIM(2); -- Trim last two elements （移除最后两个元素）
DBMS_OUTPUT.PUT_LINE('varray.LAST = ' || varray.LAST);
DBMS_OUTPUT.PUT_LINE('varray.LAST = ' || test_string(1));
END;
结果：
varray.COUNT = 6
varray.LIMIT = 10
varray.FIRST = 1
varray.LAST = 6
varray.LIMIT = 10
varray.LAST = 8
varray(8) = 4
varray.LAST = 6
varray.LAST = A

三、表达式

1.数值表达式。加减乘除乘方（**）。

2.关系表达式。=,<,>,<=,>=,!=,<>

3.逻辑表达式。not,or,and

四、PL/SQL结构控制

1.IF条件控制语句

（1）IF...结构

IF condition THEN
statements;
END IF;

（2）IF...ELSE...结构

IF condition THEN
statements;
ELSE
statements;

END IF;

（3）IF...ELSIF...结构

IF condition1 THEN
statements;
ELSIF condition2 THEN
statements;
...
[ELSE statements;]
END IF;

（4）嵌套使用IF语句。

set serveroutput on;
DECLARE
t_lentim t_avg%rowtype;
BEGIN
select * into t_lentim from t_avg t where t.name='桃';
if t_lentim.jiage>=10 then
dbms_output.put_line('高价产品，价格为：'||t_lentim.jiage);
if t_lentim.shulian>30 then
dbms_output.put_line(t_lentim.name||'数量为：'||t_lentim.shulian||'大于30，不需要进货');
else
dbms_output.put_line(t_lentim.name||'数量为：'||t_lentim.shulian||'小于30，需要进货');
end if ;
elsif t_lentim.jiage<10 then
dbms_output.put_line('低价产品，价格为：'||t_lentim.jiage);
if t_lentim.shulian<30 then
dbms_output.put_line(t_lentim.name||'数量为：'||t_lentim.shulian||'大于30，不需要进货');
else
dbms_output.put_line(t_lentim.name||'数量为：'||t_lentim.shulian||'小于30，需要进货');
end if;
end if;
dbms_output.put_line(t_lentim.name||'数量为:'||t_lentim.shulian||'价格是:'||t_lentim.jiage);
EXCEPTION
when no_data_found then
dbms_output.put_line('没有这种水果');
END;
/

2.CASE条件控制语句

（1）.简单的case语句。

[<<label_name>>]

CASE case_operand --一个表达式
WHEN when_operand THEN statement;
[WHEN when_operand THEN statement;]...
[ELSE statement [statement]]...;
END CASE [label_name];

（2）.搜索式的CASE语句

[<<label_name>>]
CASE
WHEN boolean_expression THEN statement;
[WHEN boolean_expression THEN statement;]...
[ELSE statement [statement]]...;
END CASE [label_name];

set serveroutput on;
declare
v_lentim t_avg.shulian%type;
begin
select t.shulian into v_lentim from t_avg t where t.name='桃子';
case
when v_lentim>0 and v_lentim<10 then
dbms_output.put_line('水果数量少于10，严重缺货');
when v_lentim>10 and v_lentim<20 then
dbms_output.put_line('水果数量介于10到20之间，比较缺货');
when v_lentim>20 and v_lentim<30 then
dbms_output.put_line('水果数量介于20到30之间，一般缺货');
when v_lentim>30 and v_lentim<50 then
dbms_output.put_line('水果数量介于30到50之间，不缺货');
when v_lentim>50 then
dbms_output.put_line('水果数量大于50，货源充足');
else
dbms_output.put_line('水果数量处于临界值，水果的数量为'||v_lentim);
end case;
dbms_output.put_line('结束case');
exception
when no_data_found then
dbms_output.put_line('没有这种水果');
end;
/

3.LOOP循环控制语句

（1）基本LOOP语句。

[<<label_name>>]
LOOP
statement...
END LOOP [label_name]

1）用if语句与exit的组合来结束loop循环。

set serveroutput on;
declare
v_lentim number(8):=1;
begin
<<loop_circle>>
loop
dbms_output.put_line('当前v_lentim的值为：'||v_lentim);
v_lentim:=v_lentim+1;
   if v_lentim>5 then
dbms_output.put_line('退出！v_lentim的值为：'||v_lentim);
   exit loop_circle;
   end if;
end loop;
dbms_output.put_line('结束loop循环');
end;
/

2）用exit...when语句来结束循环。

set serveroutput on;
declare
v_lentim number(8):=1;
begin
<<loop_circle>>
loop
dbms_output.put_line('当前v_lentim的值为：'||v_lentim);
v_lentim:=v_lentim+1;
exit loop_circle when v_lentim>5;
end loop;
dbms_output.put_line('退出！当前v_lentim的值为：'||v_lentim);
dbms_output.put_line('结束loop循环');
end;
/

2.WHILE...LOOP语句

[<<label_name>>]
WHILE boolean_expression
LOOP
statement...
END LOOP [label_name];

set serveroutput on;
declare
v_lentim number(8):=1;
begin
dbms_output.put_line('20以内能被3整除的v_lentim的值为： ');
while v_lentim<20
loop
if mod(v_lentim,3)=0 then
dbms_output.put_line(v_lentim||' ');
end if;
v_lentim :=v_lentim+1;
end loop;
dbms_output.put_line('退出！当前v_lentim的值为：'||v_lentim);
dbms_output.put_line('结束loop循环');
end;
/

当v_lentim的值大于20时，loop循环可能没能连一次执行的机会都没有，下面使用布尔类型变量来解这一问题。

set serveroutput on;
declare
v_lentim number(8):=1;
v_bol BOOLEAN:=true;
begin
dbms_output.put_line('20以内能被3整除的v_lentim的值为： ');
while v_bol
loop
if mod(v_lentim,3)=0 then
dbms_output.put_line(v_lentim||' ');
end if;
if v_lentim>=20 then
v_bol:=FALSE;
end if;
v_lentim :=v_lentim+1;
end loop;
dbms_output.put_line('退出！当前v_lentim的值为：'||v_lentim);
dbms_output.put_line('结束loop循环');
end;
/

3.FOR...LOOP语句

[<<label_name>>]

FOR index_name IN

[REVERSE] --使用后循环方式由上标界到下标界，默认为下标界到上标界。

lower_bound..upper_bound

LOOP

statement...

END LOOP[label_name];

set serveroutput on;
declare
v_lentim number(8):=0;
begin
dbms_output.put_line('1~20之间的整数和为： ');
<<for_loop>>
for inx in 1..20
loop
v_lentim:=v_lentim+inx;
end loop for_loop;
dbms_output.put_line(v_lentim);
dbms_output.put_line('for_loop循环结束');
end;
/

五、PL/SQL中使用DML和DDL语言。

1.DML语句的使用

使用SELECT...INTO语句时,必须要返回一条数据,并且只能返回一条数据。如果有错误必须声明exception。
no_date_found; --select into没有返回数据
too_many_rows; --select into返回多条数据
where子句使用注意事项:

使用的变量名不能与列名相同,否则触发TOO_MANY_ROWS例外.

2.DDL语句的使用

DDL语句想在PL/SQL块中使用，需要使用命令（EXECUTE IMMEDIATE）来执行。

set serveroutput on;
declare
v_lentim_create varchar2(200);
begin
v_lentim_create:='create table test1
(column_name1 varchar2(10) primary key,
colume_name2 varchar2(10),
colume_name3 date)';
execute immediate v_lentim_create;
end;
/

六、PL/SQL中的异常

1.处理异常的语法

2.预定义异常

3.非预定义异常

4.自定义异常

七.PL/SQL函数编写

1.函数的结构语法

CREATE FUNCTION pric (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;
/
SELECT t.id,t.name,t.shulian,t.jiage,pric(t.jiage,t.shulian) FROM t_avg t;
输出结果：

CREATE OR REPLACE FUNCTION avg_pric(v_type in varchar2,
v_pric in out varchar2) RETURN NUMBER IS
v_shuliang number;
BEGIN
IF v_pric is null then
v_pric := 0;
END IF;
SELECT AVG(jiage), MIN(shuliang)
INTO v_pric,v_shuliang
FROM t_avg
WHERE type = v_type
and jiage > v_pric;
RETURN v_shuliang;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('没有对应的数据');
WHEN TOO_MANY_ROWS THEN
dbms_output.put_line('对应数据过多，请确认');
when value_error then
dbms_output.put_line('数值错误，请重新输入');
END;
/
----------------------
set serveroutput on
DECLARE
v_type varchar2(10) :='01';
v_pric varchar2(10) :='33';
v_shuliang varchar2(10);
BEGIN
v_shuliang :=avg_pric(v_type,v_pric);
dbms_output.put_line('平均价格'||v_pric);
dbms_output.put_line('最低数量是'||v_shuliang);
END;
/

半夏微凉

六、PLSQL基础

评论回复