Oracle之使用PL/SQL编程(定义、块的五种分类、常规变量、列类型和行类型、DML结果收参练习(insert操作、update操作、delete操作)、异常处理、流程控制练习(If语句、Case语句、loop语句、while语句、for语句))
1. PL/SQL编程的定义
PL/SQL是Oracle在标准SQL语言上的过程性扩展,允许嵌入SQL语句,定义变量和常量允许过程语言结构(条件分支语句和循环语句)允许使用异常来处理Oracle错误 可以用于创建存储过程、触发器和程序包等,也可以用于处理业务 规则、数据库事件或给SQL命令的执行添加程序逻辑
2. 块的分类(五种)
- 无名块或匿名块(anonymous):动态构造,只能执行一次,可调用其它程序,但不能被其它程序调用。
- 命名块(named):是带有名称的匿名块,这个名称就是标签。
- 子程序(subprogram):存储在数据库中的存储过程、函数等。当在数据库上建立好后可以在其它程序中调用它们。
- 触发器(Trigger):当数据库发生操作时,会触发一些事件,从而自动执行相应的程序。
- 程序包/包(package):存储在数据库中的一组子程序、变量定义。在包中的子程序可以被其它程序包或子程序调用。但如果声明的是局部子程序,则只能在定义该局部子程序的块中调用该局部子程序。
3. 常规变量,常量练习
-- CONSTANT 定义常量值
declare
v_number1 number :=13;
v_number2 constant number :=3.14;
begin
dbms_output.put_line(v_number1 * v_number2);
end;
-- DML结果装载入PLSQL变量
declare
v_salary employees.salary%type;
begin
select salary into v_salary from employees where employee_id=198;
DBMS_OUTPUT.put_line('v_salary:'||v_salary);
end;
4. 列类型和行类型练习
-- 变量与指定的列的类型一致 采用%TYPE
DECLARE
v_num1 employees.salary%type := 13.234;
v_num2 employees.manager_id%type := 2;
BEGIN
DBMS_OUTPUT.put_line(v_num1 / v_num2);
END;
-- %ROWTYPE表示数据类型是一行数据
DECLARE
v_emp employees%rowtype;
BEGIN
select * into v_emp from employees where employee_id = 100;
dbms_output.put_line('部门编号:'||v_emp.department_id ||
' 员工姓名:'|| v_emp.first_name);
END;
5. DML结果收参练习(insert操作、update操作、delete操作)
--insert操作,接收返回值
declare
v_depId departments.department_id%type;
v_depName departments.department_name%type;
v_managerId departments.manager_id%type;
begin
insert into departments(department_id,department_name,manager_id,location_id)
values(600,'shanghai',200,1700)
returning department_id,department_name,manager_id
into v_depId,v_depName,v_managerId;
dbms_output.put_line(v_depId || ' ' || ' ' || v_depName || ' ' || v_managerId);
end;
--update操作,接收返回值
DECLARE
v_depId departments.department_id%type;
v_info varchar2(40);
BEGIN
update departments set department_name='北京' where department_id=600
returning department_id,department_name || ' '|| manager_id
into v_depId,v_info;
dbms_output.put_line(v_depId || ' '||' '|| v_info);
END;
--delete操作,接收返回值
DECLARE
v_depId departments.department_id%type;
v_info varchar2(40);
BEGIN
delete from departments where department_id=600
returning department_id,department_name || ' '|| manager_id
into v_depId,v_info;
dbms_output.put_line(v_depId || ' '||' '|| v_info);
END;
6. 通过用户输入的员工号,查询一行记录,添加异常处理
--通过用户输入的员工号,查询一行记录
DECLARE
v_input_empNum number :='&input_empNum';
v_emp employees%rowtype ;
BEGIN
select * into v_emp from employees where employee_id = v_input_empNum;
dbms_output.put_line(v_input_empNum);
dbms_output.put_line(v_emp.first_name);
--异常处理
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('未找到该员工');
END;
7. 流程控制练习(If语句、Case语句、loop语句、while语句、for语句)
-- If语句格式
IF <布尔表达式> THEN
PL/SQL 和 SQL语句
ELSE
其它语句
END IF;
-- if示例
declare
v_temp number :='&input_temp';
begin
if v_temp >37 then
dbms_output.put_line('进行隔离');
else
dbms_output.put_line('自行观察');
end if;
end;
-- Case语句格式
CASE 条件表达式
WHEN 条件表达式结果1 THEN
语句段1
WHEN 条件表达式结果2 THEN
语句段2
......
WHEN 条件表达式结果n THEN
语句段n
[ELSE 条件表达式结果]
END;
-- Case示例
declare
v_score number :='&input_score';
v_result varchar2(32);
begin
v_result :=
case
when v_score<100 and v_score>90 then '优秀'
when v_score between 81 and 90 then '良好'
when v_score between 60 and 80 then '及格'
else'数据异常'
end;
dbms_output.put_line(v_result);
end;
三种循环方法
-- loop语句格式
LOOP
要执行的语句;
EXIT WHEN <条件语句>; --条件满足,退出循环语句
END LOOP;
--loop示例
DECLARE
v_num number := 1;
BEGIN
loop
DBMS_OUTPUT.put_line('当前num值:' || v_num);
v_num := v_num+1;
exit when v_num = 10;
end loop;
END;
-- while语句格式
WHILE <布尔表达式> LOOP
要执行的语句;
END LOOP;
-- while示例
DECLARE
v_num number := 1;
BEGIN
while v_num < 10 loop
DBMS_OUTPUT.PUT_LINE('当前v_num值:'|| v_num);
v_num :=v_num+1;
end loop;
END;
-- for语句格式 (推荐)
FOR 循环计数器 IN [ REVERSE ] 下限 .. 上限 LOOP
要执行的语句;
END LOOP ;
IN 每次循环加一;
IN REVERSE 每次循环减一;
EXIT 退出循环;
--for 推荐示例
DECLARE
BEGIN
for i in 1..101 loop
DBMS_OUTPUT.put_line('当前值:'|| i);
end loop;
END;
DECLARE
BEGIN
for i in reverse 1 .. 10 loop
if i= 5 then
DBMS_OUTPUT.put_line('退出循环');
exit;
end if;
DBMS_OUTPUT.put_line('当前值:' || i);
end loop;
END;
8. 数组的输出采用for循环方式,打印出来
-- 数组 数组的输出采用for循环方式,打印出来
declare
--定义一个数组
type type_array is array(3) of varchar2(32);
--指定一个变量为数组
v_type_array type_array;
begin
--数组赋值
v_type_array := type_array('China','American','England');
--数组打印
for i in 1 .. v_type_array.count loop
dbms_output.put_line(v_type_array(i));
end loop;
end;