连接上一篇程序员不得不看Oracle干货
1.PL/SQL
pl/sql:块结构语言，是sql（Structured Query Language）语言的一种扩展，结合了oracle过程语言（procedural language）进行使用。
pl/sql块由三部分构成：声明部分、执行部分、异常部分。
PL/SQL结构
[DECLARE] --声明变量等； BEGIN --程序主要部分，一般用来执行过程语句或SQL语句； [EXCEPTION] --异常处理； END; |
1.1运算符
＝ | 等于 | 比较运算符 |
<>,!=,~=,^= | 不等于 | |
< | 小于 | |
> | 大于 | |
<= | 小于或等于 | |
>= | 大于或等于 | |
+ | 加号 | 算术运算符 |
- | 减号 | |
* | 乘号 | |
/ | 除号 | |
:= | 赋值号 | 赋值运算符 |
=> | 关系号 | 关系号 |
.. | 范围运算符 | 范围运算符 |
|| | 字符连接符 | 连接运算符 |
is null | 是空值 | 逻辑运算符 |
between and | 介于两者之间 | |
in | 在一系列值中间 | |
and | 逻辑与 | |
or | 逻辑或 | |
not | 取反 |
1.2变量与常量
数据类型：
常用标准类型：CHAR(CHARATER,NCHAR),VARCHAR2,NUMBER(P,S),DATE,BOOLEAN等。
属性类型：%TYPE 与 %ROWTYPE
%TYPE:可以用来定义数据变量的类型与已定义的数据变量（表中的列）一致。
%ROWTYPE：与某一数据库表的结构一致(修改数据库表结构，可以实时保持一致）；访问方式声明为rowtype的 变量名.字段名。
1.2.1基本类型
声明
【变量声明】 <变量名> 类型[:=初始值]; 【示例】 name varchar2(20) := 'itcast'; 【常量声明】 <变量名> CONSTANT 类型:=初始值; 【示例】 pi constant number(5,3):=3.14; |
运用
/*定义常量或变量、赋值使用示例*/ DECLARE p_empno constant number(4):=7369; p_ename varchar2(10); p_sal number(7,2); p_comm number(7,2); BEGIN --赋值方式一：使用select into给变量赋值 select ename,sal into p_ename,p_sal from emp where empno =p_empno; --赋值方式二：使用赋值操作符“:=”给变量赋值 p_comm:=500; --输出相关信息，DBMS_OUTPUT.PUT_LINE为具有输出功能的函数 dbms_output.put_line('员工号:'|| p_empno||',姓名:'|| p_ename||',工资:'|| p_sal||',奖金:'|| p_comm); END; 【注意】 dbms_output是oracle提供的输出对象 put_line是其一个方法，用于输出一个字符串 new_line是其一个方法，用于输出新的一行（换行） |
1.2.2%type类型
声明
【声明】 变量名称 表名.字段%type; 【示例:】 --表示变量name的类型和emp.ename的类型相同 name emp.ename%type; |
运用
/*定义常量或变量、赋值使用示例*/ DECLARE p_empno constant number(4):=7369; p_ename emp.ename%type; p_sal emp.sal%type; p_comm emp.comm%type; BEGIN --赋值方式一：使用select into给变量赋值 select ename,sal into p_ename,p_sal from emp where empno = p_empno; --赋值方式二：使用赋值操作符“:=”给变量赋值 p_comm:=500; --输出相关信息，DBMS_OUTPUT.PUT_LINE为具有输出功能的函数 dbms_output.put_line('员工号:'|| p_empno||',姓名:'|| p_ename||',工资:'|| p_sal||',奖金:'|| p_comm); END; |
1.2.3%rowtype类型
声明
【声明】 变量名称 表%rowtype; 【示例:】 --表示变量test的类型为emp表的行类型;也有 .empno; .ename; .sal ;等属性 test emp%rowtype; |
运用
/*定义常量或变量、赋值使用示例*/ DECLARE p_empno constant number(4):=7369; emp_info emp%rowtype; p_comm emp.comm%type; BEGIN --赋值方式一：使用select into给变量赋值 select * into emp_info from emp where empno = p_empno; --赋值方式二：使用赋值操作符“:=”给变量赋值 p_comm:=500; --输出相关信息，DBMS_OUTPUT.PUT_LINE为具有输出功能的函数 dbms_output.put_line('员工号:'|| p_empno||',姓名:'|| emp_info.ename ||',工资:'|| emp_info.sal ||',奖金:'|| p_comm); END; |
1.3控制语句
1.3.1条件语句
【语法】
IF <条件1> THEN 语句 [ELSIF <条件2> THEN 语句] . . . [ELSIF <条件n> THEN 语句] [ELSE 语句] END IF; |
【示例】
/* 根据员工的工资判断其工资等级（工资大于等于5000为A级，工资大于等于4000为B级，工资大于等于3000为C级，工资大于等于2000为D级，其它为E级） */ DECLARE p_empno number(4):=7566; p_sal emp.sal%type; BEGIN --用变量代替条件语句中的真值 select sal into p_sal from emp where empno = p_empno; IF p_sal >= 5000 THEN dbms_output.put_line('员工号为：' || p_empno || '的员工的工资级别为：A级'); ELSIF p_sal >= 4000 THEN dbms_output.put_line('员工号为：' || p_empno || '的员工的工资级别为：B级'); ELSIF p_sal >= 3000 THEN dbms_output.put_line('员工号为：' || p_empno || '的员工的工资级别为：C级'); ELSIF p_sal >= 2000 THEN dbms_output.put_line('员工号为：' || p_empno || '的员工的工资级别为：D级'); ELSE dbms_output.put_line('员工号为：' || p_empno || '的员工的工资级别为：E级'); END IF; END; |
1.3.2循环语句
1、LOOP
LOOP 语句; EXIT WHEN <条件>; END LOOP; |
【示例】
/* 计算1-10的总和 */ DECLARE p_sum number(4):=0; p_num number(2):=1; BEGIN LOOP p_sum := p_sum + p_num; p_num := p_num + 1; EXIT WHEN p_num > 10; END LOOP; dbms_output.put_line('1-10的总和为：' || p_sum); END; |
2、WHILE LOOP
WHILE <条件> LOOP 语句; END LOOP; |
【示例】
/* 计算1-10的总和 */ DECLARE p_sum number(4):=0; p_num number(2):=1; BEGIN WHILE p_num <= 10 LOOP p_sum := p_sum + p_num; p_num := p_num + 1; END LOOP; dbms_output.put_line('1-10的总和为：' || p_sum); END; |
3、FOR
FOR <循环变量> IN [REVERSE] 下限..上限 LOOP 语句; END LOOP; 【说明】..两点表示范围，1..4表示时将从1到4进行循环，起始（例如 1）写前边，REVERSE表示反转，循环时变成从4到1进行。 |
【示例】
/* 计算1-10的总和 */ DECLARE p_sum number(4):=0; p_num number(2):=1; BEGIN FOR p_num IN 1..10 LOOP p_sum := p_sum + p_num; END LOOP; dbms_output.put_line('1-10的总和为：' || p_sum); END; |
1.3.3顺序语句
指定顺序执行的语句；主要包括 null语句。null语句：是一个可执行语句，相当于一个占位符或不执行操作的空语句。主要用来提高程序语句的完整性和程序的可读性。
/* 输出1-10的数字但跳过数字4 */ DECLARE flag number(2):=0; BEGIN WHILE flag < 10 LOOP flag := flag + 1; if flag = 4 then null;-- 占位，不能去掉 else dbms_output.put_line(flag); end if; END LOOP; END; |
1.4异常处理
1.4.1异常语法
EXCEPTION WHEN <异常类型> THEN 语句; WHEN OTHERS THEN 语句; |
常配套使用的函数：
SQLCODE函数：返回错误代码，
SQLERRM函数：返回错误信息
例如输出异常信息： DBMS_OUTPUT.PUT_LINE('其它异常，代码号：'||SQLCODE||'，异常描述:'||SQLERRM);
1.4.2预定义异常
预定义异常指PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发(由oracle自动引发)。
常见的预定义异常：
CURSOR_ALREADY_OPEN 试图"OPEN"一个已经打开的游标
DUP_VAL_ON_INDEX 试图向有"UNIQUE"中插入重复的值
INVALID_CURSOR 试图对以关闭的游标进行操作
INVALID_NUMBER 在SQL语句中将字符转换成数字失败
LOGIN_DENIED 使用无效用户登陆
NO_DATA_FOUND 没有找到数据时
NOT_LOGIN_ON 没有登陆Oracle就发出命令时
PROGRAM_ERROR PL/SQL存在诸如某个函数没有"RETURN"语句等内部问题
STORAGE_ERROR PL/SQL耗尽内存或内存严重不足
TIMEOUT_ON_RESOURCE Oracle等待资源期间发生超时
TOO_MANY_ROWS "SELECT INTO"返回多行时
VALUE_ERROR 当出现赋值错误
ZERO_DIVIDE 除数为零
【示例】
/* 预定义异常捕获并处理 */ DECLARE p_result number(2); BEGIN p_result := 1/0; dbms_output.put_line('没有异常！'); EXCEPTION WHEN ZERO_DIVIDE THEN dbms_output.put_line('除数不能为0！代码为：'|| sqlcode || '，异常信息为：' || sqlerrm); WHEN OTHERS THEN dbms_output.put_line('其它异常！代码为：'|| sqlcode || '，异常信息为：' || sqlerrm); END; |
1.4.3自定义异常
自定义异常：程序在运行过程中，根据业务等情况，认为非正常情况，可以自定义异常。对于这种异常，主要分三步来处理：
1、定义相关异常；在声明部分定义相关异常，
格式：<自定义异常名称> EXCEPTION;
2、抛出异常；在出现异常部分抛出异常，
格式：RAISE <异常名称>；
3、处理异常；在异常处理部分对异常进行处理，
格式：when <自定义异常名称> then ...，
处理异常也可以使用RAISE_APPLICATION_ERROR(ERROR_NUMBER,ERROR_MESSAGE)存储过程进行处理，
其中参数ERROR_NUMBER取值为-20999~-20000的负整数，参数ERROR_MESSAGE为异常文本消息。
【示例】
/* 判断emp中相应empno对应用户的奖金是否低于500，如果低于则抛出并处理自定义异常 */ DECLARE p_comm emp.comm%type; --自定义异常，名称为comm_exception comm_exception EXCEPTION; BEGIN select nvl(comm,0) into p_comm from emp where empno=7499; if p_comm >= 500 then dbms_output.put_line('奖金大于等于500。'); else RAISE comm_exception; end if; EXCEPTION WHEN comm_exception THEN RAISE_APPLICATION_ERROR(-20001,'奖金低于500，太少了！'); --dbms_output.put_line('奖金低于500！'); WHEN OTHERS THEN dbms_output.put_line('其它异常！代码为：'|| sqlcode || '，异常信息为：' || sqlerrm); END; |
2游标
2.1显式游标
游标是映射在结果集中一行数据上的位置实体，使用游标，便可以访问结果集中的任意一行数据了，将游标放置到某行后，即可对该行数据进行操作；从上向下依次迭代结果集。
2.1.1游标语法
【定义语法】 CURSOR <游标名> IS <SELECT 语句> ; 【操作】 OPEN <游标名> --打开游标 FETCH <游标名> INTO 变量1,变量2,变量3,....变量n,; 或者 FETCH <游标名> INTO 行对象; --取出游标当前位置的值 CLOSE <游标名> --关闭游标 【属性】 %NOTFOUND --如果FETCH语句失败，则该属性为"TRUE"，否则为"FALSE"; %FOUND --如果FETCH语句成果，则该属性为"TRUE"，否则为"FALSE"; %ROWCOUNT --返回游标当前行的行数; %ISOPEN --如果游标是开的则返回"TRUE"，否则为"FALSE"; |
2.1.2游标使用
1、使用游标显示员工表中所有的员工姓名、工作和工资
declare cursor cur_emp is select ename,job,sal from emp; p_ename emp.ename%type; p_job emp.job%type; p_sal emp.sal%type; begin --打开游标 open cur_emp; loop --取游标数据，从上往下移动一行 fetch cur_emp into p_ename, p_job, p_sal; --如果下移后没有数据，则退出 exit when cur_emp%notfound; --如果存在数据，则处理 dbms_output.put_line('姓名为：' || p_ename || '，工作为：' || p_job || '，工资为：' || p_sal); end loop; --关闭游标 close cur_emp; end; |
2、使用游标显示指定部门下的所有的员工姓名、工作和工资
代参数的游标 【定义】 CURSOR <游标名>(参数列表) IS <SELECT 语句>; 【示例】 declare cursor cur_emp(dno emp.deptno%type) is select ename,job,sal from emp where deptno=dno; r_cur_emp cur_emp%rowtype; begin --打开游标 open cur_emp(20); loop --取游标数据，从上往下移动一行 fetch cur_emp into r_cur_emp; --如果下移后没有数据，则退出 exit when cur_emp%notfound; --如果存在数据，则处理 dbms_output.put_line('姓名为：' || r_cur_emp.ename || '，工作为：' || r_cur_emp.job || '，工资为：' || r_cur_emp.sal); end loop; --关闭游标 close cur_emp; end; --参考：使用while循环实现 declare cursor cur_dept_emps(dno emp.deptno%type) is select ename,job,sal from emp where deptno=dno; emp_info cur_dept_emps%rowtype; begin open cur_dept_emps(20); fetch cur_dept_emps into emp_info; while cur_dept_emps%found loop dbms_output.put_line('员工姓名为：'||emp_info.ename||'，工作为：'||emp_info.job||'，工资为：'||emp_info.sal); fetch cur_dept_emps into emp_info; end loop; close cur_dept_emps; end; --参考：使用for循环实现 declare cursor cur_dept_emps(dno emp.deptno%type) is select ename,job,sal from emp where deptno=dno; emp_info cur_dept_emps%rowtype; begin for emp_info in cur_dept_emps(20) loop if cur_dept_emps%found then dbms_output.put_line('员工姓名为：'||emp_info.ename||'，工作为：'||emp_info.job||'，工资为：'||emp_info.sal); end if; end loop; end; |
3、使用游标按员工的工种涨工资,总裁800，经理600，其他人员300
declare cursor cur_emp is select empno,job from emp; p_empno emp.empno%type; p_job emp.job%type; begin --打开游标 open cur_emp; loop --取游标数据，从上往下移动一行 fetch cur_emp into p_empno, p_job; --如果下移后没有数据，则退出 exit when cur_emp%notfound; --如果存在数据，则处理 if 'PRESIDENT'= p_job then update emp set sal = sal + 800 where empno = p_empno; elsif 'MANAGER' = p_job then update emp set sal = sal + 600 where empno = p_empno; else update emp set sal = sal + 300 where empno = p_empno; end if; end loop; --关闭游标 close cur_emp; --提交修改 commit; end; |
2.2隐式游标
当执行一个SQL语句时，Oracle会自动创建一个隐式游标，隐式游标主要处理DML语句，该游标的名称是sql。隐试游标不能进行"OPEN" ,"CLOSE","FETCH"这些操作。
属性:
%NOTFOUND --如果DML语句没有影响到任何一行时，则该属性为"TRUE"，否则为"FALSE";
%FOUND --如果DML语句影响到一行或一行以上时，则该属性为"TRUE"，否则为"FALSE";
%ROWCOUNT --返回游标当最后一行的行数;
【示例】
/* 通过更新语句判断隐式游标的存在 */ begin update emp set comm=comm + 300 where empno = 7369; if sql%notfound then dbms_output.put_line('empno对应的员工不存在'); else dbms_output.put_line('empno对应的员工数为：' || sql%rowcount); end if; end; |
3存储过程与存储函数
3.1存储过程
存储过程是命名的pl/sql程序块，封装数据业务操作，具有模块化、可重用、可维护、更安全特点；并且可以被程序调用。一般有4类型的存储过程，分别为不带参数、带输入参数、带输出参数、带输入输出参数。
3.1.1语法
【语法】 CREATE [OR REPLACE] PROCEDURE <过程名>[(参数列表)] IS|AS [局部变量声明] BEGIN 可执行语句 [EXCEPTION 异常处理语句] END [<过程名>]; OR REPLACE：如果系统已存在该存储过程，将被替换 参数列表：参数不需要声明长度，可选 参数变量的类型:in 为默认类型,表示输入; out 表示只输出;in out 表示即输入又输出; 【调用方式】 在PL/SQL块中直接使用过程名; 在PL/SQL程序外使用 exec[ute] <过程名>[(参数列表)]; |
3.1.2无参存储过程
-- 授予itcast创建存储过程的权限 grant create procedure to itcast; /* 使用无参存储过程，注意无参存储过程创建时不能使用() */ create or replace procedure pro_helloWorld as begin dbms_output.put_line('Hello World.'); end; -- 方式一：调用存储过程，可加可不加() begin pro_helloWorld; end; -- 方式二：调用存储过程，可加可不加() exec pro_helloWorld; |
3.1.3有输入参数存储过程
/* 使用有输入参存储过程 */ create or replace procedure pro_add_emp( p_empno in emp.empno%type, p_ename in varchar2, p_sal number ) as begin --将输入参数对应的数据插入emp表 insert into emp(empno, ename,sal) values(p_empno, p_ename, p_sal); end; / -- 调用存储过程，向emp表插入新数据 begin pro_add_emp(2001,'itcast2001',3000); pro_add_emp(2002,'itcast2002',2000); pro_add_emp(2003,'itcast2003',4000); end; |
3.1.4有输出参数存储过程
/* 使用有输出参存储过程，计算1到10的总和并通过参数返回 */ create or replace procedure pro_1to10_sum( p_sum out number ) as tem_sum number(4):=0; begin for i in 1..10 loop tem_sum := tem_sum + i; end loop; p_sum := tem_sum; end; / -- 调用存储过程 declare p_sum number(4); begin pro_1to10_sum(p_sum); dbms_output.put_line('1至10的和为：'|| p_sum); end; |
3.1.5有输入输出参数存储过程
/* 使用有输入、输出参存储过程；根据empno查询该员工号对应的员工的姓名和工资 */ create or replace procedure pro_query_enameAndSal_by_empno( s_empno emp.empno%type, s_ename out emp.ename%type, s_sal out emp.sal%type ) as begin select ename,sal into s_ename, s_sal from emp where empno= s_empno; end; / -- 调用存储过程 declare p_ename emp.ename%type; p_sal emp.sal%type; begin --pro_query_enameAndSal_by_empno(7369, p_ename, p_sal); pro_query_enameAndSal_by_empno(7369, s_sal => p_sal, s_ename => p_ename); dbms_output.put_line('员工号为7369的员工名称为：'|| p_ename||'，其工资为：'|| p_sal); end; |
3.1.6程序中调用存储过程
package cn.itcast; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import oracle.jdbc.OracleTypes; public class TestProcedure { public static void main(String[] args) { Connection conn = null; CallableStatement call = null; try { Class.forName("oracle.jdbc.OracleDriver"); String url = "jdbc:oracle:thin:@localhost:1521:orcl"; conn = DriverManager.getConnection(url, "itcast", "itcast"); call = conn.prepareCall("{call pro_query_enameAndSal_by_empno(?,?,?)}"); //设置输入型参数 call.setInt(1, 7369); //注册输出型参数 call.registerOutParameter(2, OracleTypes.VARCHAR); call.registerOutParameter(3, OracleTypes.NUMBER); //调用存储过程 call.execute(); //获取返回值 String ename = call.getString(2);//员工名称 double sal = call.getDouble(3);//员工工资 System.out.println("员工号为7369的员工名称为：" + ename + "，工资为：" + sal); } catch (Exception e) { e.printStackTrace(); } finally { try { if(call != null){ call.close(); } if(conn != null){ conn.close(); } } catch (SQLException e) { e.printStackTrace(); } } } } |
3.1.7删除存储过程
【语法】 DROP PROCEDURE <过程名>; 【示例】 drop procedure pro_1to10_sum; |
3.2存储函数
存储函数与过程不同的是，存储函数有return语句；一般情况下如果在需要一个返回值时可使用存储函数。
3.2.1语法
CREATE [OR REPLACE] FUNCTION <函数名>[(参数列表)] RETURN 数据类型 IS|AS [局部变量声明] BEGIN 可执行语句 [EXCEPTION 异常处理语句] RETURN 返回值; END [<函数名>]; 变量的类型:in 为默认类型,表示输入; out 表示只输出;in out 表示即输入又输出; 【使用方式】 直接在select中使用和其它系统函数使用方式一样； 在PL/SQL块中调用使用； |
3.2.2无参存储函数
/* 使用无参存储函数；注意创建时函数名称不能使用() 但是在调用时候可加可不加() */ create or replace function fun_helloWorld return varchar2 as begin return 'Hello World'; end; / -- 方式1：调用存储函数 select fun_helloWorld() from dual; -- 方式2：调用存储函数 declare str varchar2(20); begin str :=fun_helloWorld; dbms_output.put_line(str); end; |
3.2.3有输入参数存储函数
/* 使用存储函数：根据员工号，查询并返回该员工的年薪 */ create or replace function fun_get_annualSal_by_empno(p_empno emp.empno%type) return number as p_sal emp.sal%type; p_comm emp.comm%type; begin select sal,comm into p_sal, p_comm from emp where empno=p_empno; return 12*p_sal + nvl(p_comm,0); end; / -- 调用存储函数 select fun_get_annualSal_by_empno(7369) from dual; |
3.2.4有输入输出参数存储函数
/* 使用具有输入输出参数的存储函数：根据员工号，查询并返回该员工的年薪,姓名，奖金 */ create or replace function fun_get_annualSal_by_empno2( p_empno emp.empno%type, p_ename out emp.ename%type, p_comm out emp.comm%type ) return number as p_sal emp.sal%type; begin select ename,sal,nvl(comm,0) into p_ename,p_sal, p_comm from emp where empno=p_empno; return 12*p_sal + p_comm; end; / -- 调用存储函数 declare p_annualSal number(10,2); p_ename emp.ename%type; p_comm emp.comm%type; begin p_annualSal := fun_get_annualSal_by_empno2(7499,p_ename,p_comm); dbms_output.put_line('员工姓名为：'||p_ename||',奖金为：'||p_comm||'，年薪为：'||p_annualSal); end; |
3.2.5程序中调用存储函数
package cn.itcast; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import oracle.jdbc.OracleTypes; public class TestFunction { public static void main(String[] args) { Connection conn = null; CallableStatement call = null; try { Class.forName("oracle.jdbc.OracleDriver"); String url = "jdbc:oracle:thin:@localhost:1521:orcl"; conn = DriverManager.getConnection(url, "itcast", "itcast"); call = conn.prepareCall("{? = call fun_get_annualSal_by_empno2(?,?,?)}"); //注册存储函数返回值 call.registerOutParameter(1, OracleTypes.DOUBLE); //设置输入参数，员工号 call.setInt(2, 7499); //注册输出参数，员工姓名 call.registerOutParameter(3, OracleTypes.VARCHAR); //注册输出参数，奖金 call.registerOutParameter(4, OracleTypes.DOUBLE); call.execute(); System.out.println("员工姓名为：" + call.getString(3) + "，奖金为：" + call.getDouble(4) + "，年薪为：" + call.getDouble(1)); } catch (Exception e) { e.printStackTrace(); } finally { try { if(call != null){ call.close(); } if(conn != null){ conn.close(); } } catch (SQLException e) { e.printStackTrace(); } } } } |
3.2.6删除存储函数
【语法】 DROP FUNCTION <函数名>; 【示例】 drop function fun_helloWorld; drop function fun_get_annualSal_by_empno; drop function fun_get_annualSal_by_empno2; |
3.3存储过程与存储函数的区别
1、返回值的区别,函数一定要有1个返回值或有多个通过输出参数的返回值,而存储过程是通过输出参数返回的,可以有多个或者没有；
2、调用的区别,函数可以在sql语句中直接调用,而存储过程必须单独调用；
3、函数一般情况下是用来计算并返回一个计算结果，而存储过程一般是用来完成特定的数据操作（比如修改、插入数据库表或执行某些DDL语句等等）