PL/SQL知识点1

1.PL/SQL概述

1.1简单介绍

• PL/SQL中最基本的执行单元为PL/SQL块

• 语法

  [DECLARE
  变量声明]
  BEGIN
  语句
  [EXCEPTION
  异常处理]
  END;
  

  • DECLARE子句:声明在PL/SQL 块中需要被使用到的变量
  • BEGIN: PL/SQL块运行时需要被执行的语句
  • EXCEPTION:异常处理语句
  • END:表示PL/SQL块结束，后面必须接分号

在sqlplus窗口中先要输入 set serveroutput on,再输入相对应语句，最后要输入’ \ '表示执行，才会输出对应语句。例子如下图：

1.2 PL/SQL变量和数据类型

• 定义

  • identifier [CONSTANT] datatype [NOT NULL] [:= expr | DEFAULT expr]
  • 标识符最多30个字符，以字母开头，可以包含数字、$、_
    加入CONSTANT表示为常量
  • NOT NULL表示不允许为空
  • :=和DEFAULT都可以为变量赋初值

• 分类

  • PL/SQL数据类型支持SQL类型与PL/SQL类型
  • 变量分为:
    • 标量:保存单一值
    • 组合量:保存多个值
    • 参照量:类似于指针

• 数据类型

  • 数值: INTEGER, FLOAT, NUMBER,…
  • 字符: CHAR, VARHCAR, VARCHAR2, STRING,…
  • 日期: DATE, TIMESTAMP, INTERVAL YEAR TO MONTH,…
  • 逻辑: BOOLEAN
  • 组合量: RECORD，Index by TABLE,…
  • 参考量: REF CURSOR
  • 大对象: BLOB，CLOB, …

• 参考类型

  • %TYPE:参考表中列或已定义变量类型，标量
  • %ROWTYPE:参考表中行的类型，组合量

• 例子

--例1
DECLARE
	V_NAME VARCHAR2(20) := 'WANG' ;
	C_P1 CONSTANT NFMBER:= 20;--CONSTANT表示常量
	X INTEGER :=4; .
	Y X%type;
BEGIN
	--C P1:=40;
	Y:= X+4;
	dbms_ output.put_ line (V_ NAME) ; 
	dbms_ output.put_ line(C_ P1) ;
	dbms_ output.put_ line(Y) ;
END;
--例2
DECLARE
	V_ emp emp%rowtype;
BEGIN
	select * into V_emp
	from emp where empno=7839;
	dbms_output.put_line(V_emp.empnol |V_emp.ename) ;
END;

变量作用域：变量只在其定义块中可以使用（可以嵌套使用）

1.3 PL/SQL表达式

赋值运算符

variable **:=** expression|value

串联运算符

使用 || 符号

case表达式

CASE variable
WHEN value1 THEN v1
....
ELSE vn
END;

2.PL/SQL控制结构

2.1 分支结构

类型1

IF condition THEN
​	statement
END IF;

条件成立时，执行语句

类型2

IF condition THEN
​	statement1
ELSE
​	statement2
END IF;

条件成立，执行语句1，否则执行语句2

类型3

IF condition1 THEN
​	statement1
ELSEIF  condition2
​	statement2
...
ELSE
​	statement3
END IF;

类型4

简单case语句

CASE  selector
WHEN value1 THEN v1
...
ELSE vn
END ;

CASE搜索语句

CASE  
WHEN condition THEN statement1
...
ELSE statementn
END CASE;

2.2循环结构

类型1 loop循环

LOOP
	statement
	[EXIT[WHEN condition]]
END LOOP;

类型2 while循环

WHILE condition
LOOP
	statement
END LOOP;

while条件满足执行循环

类型3 for循环

FOR counter IN min_val..max_val
LOOP
	statement
END LOOP;

使用计数器执行循环

continue语句

CONTINUE [label] [WHEN condition];

3 游标

3.1 游标简介

• 本质就是保存执行结果存储区域的指针
• 分成隐式游标，显式游标

3.2 隐式游标

• 概念：不需要定义，PL/SQL环境进行声明和管理，也被称为SQL游标，专门用于处理select…into,insert,delete,update语句
• 属性：
  • SQL%FOUND:FETCH语句是否获取到了结果
  • SQL%ISOPEN:游标是否处于打开的状态
  • SQL%NOTFOUND：FETCH语句是否没有获取到了结果
  • SQL%ROWCOUNT：受语句影响的行数（游标中记录的行数）

例子:

DECLARE
  v_sal emp.sal%type;
BEGIN
  select sal into v_sal
  from emp
  where empno=7839;
  
  --注意这里隐式游标是最近的语句，即select语句，
  --若在这个语句下还有其他语句，则会使用其他语句
  dbms_output.put_line(SQL%ROWCOUNT || 'selected.');
  if SQL%FOUND then
     dbms_output.put_line('data found.');
  else
     dbms_output.put_line('not data found.');
  end if;
END;

3.3 显式游标

• 概念：需要显式定义，需要用户进行声明和管理

• 操作模型：

  • 声明：

  CURSOR cursor_name[(param type,...)] IS select_statement;
  

  • 打开

  OPEN cursor_name[(parameter)];
  

  • 获取数据

  FETCH cursor_name INTO variable;
  

  • 关闭

  CLOSE cursor_name;
  

• 例子

--利用显示游标循环输出
--无参游标
DECLARE
  CURSOR cur is select * from emp;
  v_row cur%rowtype;
BEGIN
  OPEN cur;
  LOOP
  	FETCH cur INTO v_row;
  	EXIT WHEN cur%NOTFOUND;
  	dbms_output.put_line(v_row.ename || ' : ' || v_row.sal);
  END LOOP;
  CLOSE cur;
END;

--有参游标
DECLARE
  CURSOR cur(dno NUMBER) is select * from emp WHERE deptno = dno;
  v_row cur%rowtype;
BEGIN
  OPEN cur(10);
  LOOP
  	FETCH cur INTO v_row;
  	EXIT WHEN cur%NOTFOUND;
  	dbms_output.put_line(v_row.ename || ' : ' || v_row.sal);
  END LOOP;
  CLOSE cur;
END;

--使用循环简化操作游标
DECLARE
  CURSOR cur(dno NUMBER) is select * from emp WHERE deptno = dno;
  v_row cur%rowtype;
BEGIN	
--注意这里省略了打开和关闭操作
  FOR r IN cur(10) LOOP
  	dbms_output.put_line(r.ename || ' : ' || r.sal);
  END LOOP;
END;

• 属性：
  • 游标名%FOUND:FETCH语句是否获取到了结果
  • 游标名%ISOPEN:游标是否处于打开的状态
  • 游标名%NOTFOUND：FETCH语句是否没有获取到了结果
  • 游标名%ROWCOUNT：游标中记录的行数