PL/SQL编程学习笔记

显示执行结果

1	set serveroutput on;

INSERT

DECLARE v_dept dept%ROWTYPE; 
BEGIN v_dept.deptno:=50; 
v_dept.loc:='BEIJING'; 
v_dept.dname:='DEV'; 
INSERT INTO dept VALUES v_dept;
END;

UPDATE

DECLARE v_dept dept%ROWTYPE; 
BEGIN 
v_dept.deptno:=50; 
v_dept.loc:='TIANJIN'; 
v_dept.dname:='SALES'; 
UPDATE dept SET ROW=v_dept WHERE deptno=50; 
END;

DELECT

DECLARE v_emp emp%ROWTYPE; 
BEGIN 
SELECT * INTO v_emp FROM emp WHERE empno=7844; 
DELETE FROM emp WHERE deptno=v_emp.deptno; 
END;

基础语句(SELECT)

DECLARE --声明部分
v_name VARCHAR2(30);
BEGIN  --执行部分
SELECT ENAME INTO v_name FROM emp WHERE empno=7788;
dbms_output.put_line('员工的名字：'||v_name);
EXCEPTION --异常处理
WHEN NO_DATA_FOUND THEN --当找不到DATA时
dbms_output.put_line('找不到这个员工');
END; --结束
/ --执行

dbms_output == System.out
put_line == println
|| == +
:= 赋值符号
执行结果：

%TYPE的使用

自动匹配对应行的数据类型以及长度

DECLARE
v_name emp.ename%TYPE; --自动给v_name匹配ename的类型
BEGIN
SELECT ename INTO v_name FROM emp WHERE empno=7788;
dbms_output.put_line('员工的名字：'||v_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('找不到这个员工');
END;

执行结果：

%ROWTYPE的使用

TYPE只能一行一行地匹配类型，当我们查找多个数据时要写很多，不是很方便，所以这里我们使用%ROWTYPE来一次性匹配表里所有的行地类型

DECLARE
v_emp emp%ROWTYPE;
BEGIN
SELECT * INTO v_emp FROM emp WHERE empno=7788;
dbms_output.put_line('员工的名字：'||v_emp.ename|| '，薪水：'||v_emp.sal);
END;

执行结果：

游标(类似java中的迭代器)

游标的作用：游标通常用于处理多行记录的事务
游标里面存储了查询到的结果集，相当于一个工作区域
游标的分类：显式游标、隐式游标、REF游标

隐式游标

在PL/SQL中执行DML SQL时会自动创建隐式游标

显式游标

显式游标可用于处理返回多行数据的查询

REF游标

用于在处理运行时才能确定的动态SQL查询的结果

使用游标的步骤

声明游标
- CURSOR cur_emp IS SELECT * FROM emp;
打开游标
- OPEN cur_emp;
提取游标数据
- FETCH cur_emp INTO v_emp;
关闭游标
- CLOSE cur_emp;

游标（简单循环）

游标的作用：可以用于处理多行事务

DECLARE
CURSOR cur_emp IS SELECT * FROM emp; --声明游标
v_emp  emp%ROWTYPE;
BEGIN
OPEN cur_emp; --执行游标
LOOP -- 简单循环
FETCH cur_emp INTO v_emp; --提取游标中的数据放到变量中
EXIT WHEN cur_emp%NOTFOUND; --%NOTFOUND游标地属性，循环推出的条件
dbms_output.put_line('员工名字：'||v_emp.ename||'，薪水：'||v_emp.sal);
END LOOP; --循环停止
CLOSE cur_emp; --关闭游标
END;

执行结果：

WHILE循环

DECLARE
CURSOR cur_emp IS SELECT * FROM emp;
v_emp  emp%ROWTYPE;
BEGIN
  OPEN cur_emp;--执行游标
  FETCH cur_emp INTO v_emp;--WHILE循环之前，必须先FETCH一次
  WHILE cur_emp%FOUND LOOP --进入循环
    dbms_output.put_line('员工名字：'||v_emp.ename||'，薪水：'||v_emp.sal);
    FETCH cur_emp INTO v_emp; --提取游标中的数据
  END LOOP; --结束循环
  CLOSE cur_emp; --关闭游标
END;

执行结果：

FOR循环

DECLARE
CURSOR cur_emp IS SELECT * FROM emp;
cur cur_emp%ROWTYPE;
BEGIN
FOR cur IN cur_emp LOOP
dbms_output.put_line('员工名字：'||cur.ename||'，薪水：'||cur.sal);
END LOOP;
END;
/

执行结果：

函数(命名块)

函数可以重复使用
函数实际上是封装在服务器上一段PLSQL代码片段。
当函数没有参数时，可以不用加函数名后面的括号。

函数的语法

CREATE [OR REPLACE] FUNCTION function_name 
(parameter1_name [mode] datatype 
    [DEFAULT|:=value]
[, parameter2_name [mode] datatype 
    [DEFAULT|:=value],…])
RETURN return_datatype 
AS|IS
     /*Declarative section is here */
BEGIN
    /*Executable section is here*/ 
EXCEPTION
    /*Exception section is here*/ 
END [function_name];

函数的定义

1
2
3

CREATE OR REPLACE FUNCTION f1(v_count NUMBER) --定义函数的名称以及数据类型
RETURN NUMBER   --定义函数的返回类型
AS 	--相当于DECLARE，后面接定义的变量

函数的使用

1	SELECT f1('数据') from 表名;

通过这种方式可以简单多次使用函数中所封装的内容

异常处理

实例

DECLARE
v_name VARCHAR2(30);
BEGIN
SELECT ENAME INTO v_name FROM emp;
dbms_output.put_line('员工的名字：'||v_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('找不到这个员工');
WHEN OTHERS THEN --出现其他错误时
dbms_output.put_line('XXXXX');
END;

执行结果：

预定义异常(有名称有编号的异常)

oracle官方已经写好的异常，在出现异常时我们可以直接使用，如：
1. NO_DATA_FOUND ：没有找到数据
2. TOO_MANY_ROWS：一个SELECT INTO语句匹配了多行数据
3. ZERO_DIVIDE：除数为0

非预定义异常(有编号没有名称的异常)

有一些异常，官方给他们分配了编号，但是没有为他们关联处理的方法，所以这是我们就需要自己定义一个异常，并与他的编号进行关联，才可以进行异常的处理。

用户定义异常(啥都没有的异常)

用户定义异常编号范围：-20999~-20000
用户定义异常是指，有些操作并不会产生Oracle错误，但是从业务规则角度考虑，认为是一
种错误。例如，执行UPDATE操作没有更新任何行时，不会引发Oracle错误，也不会产生异
常，但是，有时需要开发人员为此操作产生一个异常，以便进行处理，即用户定义异常。
用户自定义异常要对异常进行捕捉并抛出(RAISE)

检验字符串是否可以转换成数值型(函数+异常处理)

to_number函数：类型转换函数可以将字符串转换成数字

CREATE OR REPLACE FUNCTION f1(v_num VARCHAR2) --定义函数
RETURN NUMBER  --定义返回类型
AS
	ex_char2number EXCEPTION; --声明一个非预定义异常
	PRAGMA EXCEPTION_INIT (ex_char2number,-6502); --连接异常与异常处理
BEGIN
	IF to_number(v_num)>=0 THEN
		RETURN 1
	ELSE
		RAISE ex_char2number; --捕捉并抛出异常
	END IF;
EXCEPTION
	WHEN ex_char2number THEN--处理异常
		RETURN 0;
END;

存储过程(命名块)

存储过程的语法与函数类似
创建存储过程可以理解为相当于函数中的FUNCTION function_name 更换成PROCEDURE procedure_name
两者其他部分都差不多

存储过程的语法

定义：

CREATE [OR REPLACE] PROCEDURE procedure_name
(parameter1_name [mode] datatype 
    [DEFAULT|:=value]
[, parameter2_name [mode] datatype 
    [DEFAULT|:=value],…])
AS|IS
   /*Declarative section is here */
BEGIN
   /*Executable section is here*/ 
EXCEPTION
   /*Exception section is here*/ 
END[procedure_name];

调用

1
2
3

BEGIN 
	procedure_name(parameter_list); 
END;

例子

CREATE OR REPLACE PROCEDURE proc_transfer(v_empno NUMBER)
AS
  v_sal emp.sal%TYPE;
BEGIN
  SELECT sal INTO v_sal FROM emp WHERE empno=v_empno;
  dbms_output.put_line('工资：'||v_sal);
END;

定义一个存储过程，用于查询员工的工资

使用：

1
2
3

BEGIN 
  proc_transfer(7788); 
END;

结果：

分页查询(SQL)

实例

1
2
3

SELECT *
FROM (SELECT rownum rn,t.* FROM (SELECT *FROM emp ORDER BY sal) t WHERE rownum<=10) 
WHERE rn>=5;

分页存储过程

CREATE OR REPLACE PROCEDURE proc_page(
    v_tableName IN VARCHAR2,
    v_rowsPerPage IN NUMBER,
    v_currentPage IN NUMBER,
    v_totalPage OUT NUMBER,
    v_data OUT SYS_REFCURSOR--游标变量，游标：缓冲区，存了SQL语句所查询的内容
)
AS
v_endRow NUMBER;
v_startRow NUMBER;
v_sql VARCHAR2(300);
v_totalRows NUMBER;
BEGIN
v_endRow:=v_rowsPerPage*v_currentPage;
v_startRow:=(v_currentPage-1)*v_rowsPerPage+1;
v_sql:='SELECT * FROM (SELECT rownum rn,t.* FROM (SELECT * FROM '||v_tableName||') t WHERE rownum<='||v_endRow||') WHERE rn>='||v_startRow;
OPEN v_data FOR v_sql;--游标和动态SQL绑定，已经执行了
v_sql:='SELECT COUNT(*) FROM '||v_tableName;
EXECUTE IMMEDIATE v_sql INTO v_totalRows;
IF MOD(v_totalRows,v_rowsPerPage)=0 THEN
	v_totalPage:=v_totalRows/v_rowsPerPage;
ELSE
	v_totalPage:=(v_totalRows/v_rowsPerPage)+1;
END IF;
END;
/