前一段时间系统学习了Oracle PL/SQL的有关知识,内容有点多,所以觉得自己要总结一下,顺便回顾,大家共同学习。
PL/SQL的概念
PL/SQL是 Procedure Language & Structured Query Language 的缩写。字面意思是过程化语言(PL)和结构化查询语言(SQL),它扩展了SQL语言,是一种过程处理语言。
PL/SQL块结构
PL/SQL程序的块分为三部分:
- 声明部分
- 可执行部分
- 异常处理部分
example1:
DECLARE --声明部分
v_num number(10) := 0;
BEGIN --BEGIN 至 END 为可执行部分
v_num :=2/v_num;
DBMS_OUTPUT.PUT_LINE(v_num);
EXCEPTION --异常处理部分
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('出错!');
END;
Output: 出错!
增例1
--修改一条数据并显示
declare
row_id rowid;
info varchar2(40);
begin
update dept set loc = '长沙' where deptno = 50
returning rowid,dname||':'||to_char(deptno)||':'||loc
into row_id,info;
dbms_output.put_line('rowid:'||row_id);
dbms_output.put_line(info);
end;
其中:returning子句用于检索修改行的信息。
增例2
关于数组的用法
varray数据类型的数组来说,必须经过三个步骤,分别是:定义、声明、初始化。
--数组
declare
--定义array_type为 数组最大长度为5且数组中的元素类型为varchar(20) 的type
type array_type is varray(5) of varchar(20);
--声明array_type类型的v_arr数组
v_arr array_type;
begin
--初始化v_arr
v_arr := array_type('杭州','宁波','温州','绍兴','湖州');
dbms_output.put_line('城市名称:'||v_arr(1) ||'、'
||v_arr(2) ||'、'||v_arr(3) ||'、'||v_arr(4));
dbms_output.put_line('第5个值:'||v_arr(5)); --output:第5个值:湖州
v_arr(5) := '嘉兴';
dbms_output.put_line('第5个值:'||v_arr(5));-- output:第5个值:嘉兴 //已被修改
--v_arr(6) := '金华'; 越界
end;
使用%TYPE %ROWTYPE
属性类型:
- %TYEP:引用变量和数据库列的数据类型,针对特定的属性。
- %ROWTYPE:提供表中一行记录的类型。
example2(%TYPE):
DECLARE
v_empno NUMBER(10);
v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;
BEGIN
v_empno := &empno;--提示用户输入一个empno的值
SELECT ename, sal INTO v_ename, v_sal FROM emp WHERE empno = v_empno;
DBMS_OUTPUT.PUT_LINE( '员工' ||v_ename|| '的工资为:'||v_sal );
END;
Input: 7788
Output: 员工SCOTT的工资为:3000
example3(%ROWTYPE):
DECLARE
v_emp emp%ROWTYPE;
BEGIN
SELECT * INTO v_emp FROM emp WHERE empno = &empno;
DBMS_OUTPUT.PUT_LINE('员工' ||v_ename|| '的工资为:'||v_sal);
END;
Input: 7654
Output: 员工MARTIN的工资为:2000
PL/SQL的流程控制语句
- 控制语句:IF 语句 CASE语句
- 循环语句: FOR语句 LOOP语句 WHILE语句
- 顺序语句:GOTO语句 EXIT语句
IF的语句大致语法:
IF...THEN
...
ELSIF...THEN
...
ELSE
...
END IF;
example4(if语句的使用):
/*给定员工的编号根据员工的薪水如果大于2000则输出high,1000到2000为middle,其他为low*/
DECLARE
v_sal emp.sal%TYPE;
v_empno emp.empno%TYPE;
BEGIN
v_empno := &empno;
SELECT sal INTO v_sal FROM emp WHERE empno = v_empno;
IF v_sal <=1000 THEN
DBMS_OUTPUT.PUT_LINE('low');
ELSIF v_sal <=2000 THEN
DBMS_OUTPUT.PUT_LINE('middle');
ELSE
DBMS_OUTPUT.PUT_LINE('high');
END IF;
END;
Input: 7654
Output: middle
CASE:
CASE...
WHEN...THEN...
...
END;
example5(CASE语句的使用):
BEGIN
CASE '&grade'
WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('优秀');
WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('良好');
WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('中等');
WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE('及格');
WHEN 'E' THEN DBMS_OUTPUT.PUT_LINE('不及格');
ELSE DBMS_OUTPUT.PUT_LINE('没有此等级');
END CASE;
END;
FOR的语法:
FOR 循环计数器 IN 下限..上限 LOOP
要执行的语句;
END LOOP;
LOOP语句的语法:
LOOP
...
EXIT WHEN(...)
END LOOP;
example6(FOR语句 LOOP嵌套):
/*九九乘法表*/
DECLARE
j INT :=0;
BEGIN
LOOP
j := j + 1 ;
FOR i IN j..9 LOOP
DBMS_OUTPUT.PUT(j||'*'||i||'='||i*j||' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE('');
EXIT WHEN(j>=9);
END LOOP;
END;
Output:略。
WHILE语句语法:
WHILE ... LOOP
...
END LOOP;
GOTO语句语法:
GOTO label;
......
<<label>> /*标号是用<< >>括起来的标识符 */
example6(WHILE语句 GOTO语句嵌套):
DECLARE
v_num NUMBER := 1;
BEGIN
WHILE v_num <= 5 LOOP
DBMS_OUTPUT.PUT_LINE('v_num的当前值为:'||v_num );
v_num := v_num + 1;
IF v_num > 5 THEN
GOTO label;
END IF;
END LOOP;
<<label>>
DBMS_OUTPUT.PUT_LINE('v_num的当前值为:'||v_num );
END;
Output:
v_num的当前值为:1
v_num的当前值为:2
v_num的当前值为:3
v_num的当前值为:4
v_num的当前值为:5
v_num的当前值为:6