人生倒计时
- 今日已经过去小时
- 这周已经过去天
- 本月已经过去天
- 今年已经过去个月
oracle初级系列教程《三》
【二十四、oracle pl/sql 变量】
===================================================================
一、变量介绍
在编写pl/sql程序时，可以定义变量和常量；在pl/sql程序中包括有：
1)、标量类型(scalar)
2)、复合类型(composite) --用于操作单条记录
3)、参照类型(reference) --用于操作多条记录
4)、lob(large object)
二、标量(scalar)——常用类型
1)、在编写pl/sql块时，如果要使用变量，需在定义部分定义变量。
pl/sql中定义变量和常量的语法如下：
identifier [constant] datatype [not null] [:=| default expr]
identifier: 名称
constant：指定常量。需要指定它的初始值，且其值是不能改变的
datatype：数据类型
not null：指定变量值不能为null
:= 给变量或是常量指定初始值
default 用于指定初始值
expr ：指定初始值的pl/sql表达式，可以是文本值、其它变量、函数等。
2)、标量定义的案例
1.定义一个变长字符串
v_ename varchar2(10);
2.定义一个小数,范围-9999.99~9999.99
v_sal number(6,2);
3.定义一个小数并给一个初始值为5.4，:=是pl/sql的赋值号
v_sal2 number(6,2):=5.4;
4.定义一个日期类型的数据
v_hiredate date;
5.定义一个布尔变量，不能为空，初始值为false
v_valid boolean not null default false;
三、标量(scalar)——使用标量
在定义好变量后，就可以使用这些变量。这里需要说明的是pl/sql块为变量赋值不同于其它的编程语言，需要在等号前面加冒号(:=)。
下面以输入员工号，显示雇员姓名、工资、个人所得税(税率为0.03)为例。说明变量的使用，看看如何编写。
代码：
set serveroutput on; --打开输出选项
DECLARE
--税率为0.03
C_TAX_RATE NUMBER(3, 2) :=0.03;
--雇员姓名
V_ENAME VARCHAR2(5);
--工资
V_SAL NUMBER(7, 2);
--个人所得税
V_TAX_SAL NUMBER(7, 2);
BEGIN
--执行
SELECT ENAME, SAL INTO V_ENAME, V_SAL FROM EMP WHERE EMPNO=&empno; --7369
--计算所得税
V_TAX_SAL := V_SAL * C_TAX_RATE;
--输出
DBMS_OUTPUT.PUT_LINE('雇员姓名：' || V_ENAME || '工资：' || V_SAL || ' 交税：' || V_TAX_SAL);
END;
/
四、标量(scalar)——使用%type类型
对于上面的pl/sql块有一个问题：就是如果员工的姓名超过了5个字符的话，就会有“ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小”错误，为了降低pl/sql程序的维护工作量，可以使用%type属性定义变量，这样它会按照数据库列来确定你定义的变量的类型和长度。
我们看看这个怎么使用：标识符名 表名.列名%type;
比如上例的v_ename，这样定义： v_ename emp.ename%type;
代码：
set serveroutput on; --打开输出选项
DECLARE
--税率为0.03
C_TAX_RATE NUMBER(3, 2) :=0.03;
--雇员姓名
V_ENAME emp.ename%TYPE;--推荐使用%type类型
--工资
V_SAL NUMBER(7, 2);
--个人所得税
V_TAX_SAL NUMBER(7, 2);
BEGIN
--执行
SELECT ENAME, SAL INTO V_ENAME, V_SAL FROM EMP WHERE EMPNO=&empno; --7777
--计算所得税
V_TAX_SAL := V_SAL * C_TAX_RATE;
--输出
DBMS_OUTPUT.PUT_LINE('雇员姓名：' || V_ENAME || '工资：' || V_SAL || ' 交税：' || V_TAX_SAL);
END;
/
五、复合变量(composite)——介绍
用于存放多个值的变量。主要包括这几种：
1)、pl/sql记录
2)、pl/sql表
3)、嵌套表
4)、varray
六、复合类型——pl/sql记录
类似于高级语言中的结构体，需要注意的是，当引用pl/sql记录成员时，必须要加记录变量作为前缀(记录变量.记录成员)如下：
代码：
set serveroutput on; --打开输出选项
DECLARE
--定义一个pl/sql记录类型emp_record_type，
--类型包含3个数据NAME, SALARY, TITLE。说白了，就是一个类型可以存放3个数据，主要是为了方便管理
TYPE EMP_RECORD_TYPE IS RECORD(
NAME EMP.ENAME%TYPE,
SALARY EMP.SAL%TYPE,
TITLE EMP.JOB%TYPE);
--定义了一个sp_record变量，这个变量的类型是emp_record_type
SP_RECORD EMP_RECORD_TYPE;
BEGIN
SELECT ENAME, SAL, JOB INTO SP_RECORD FROM EMP WHERE EMPNO = 7788;
DBMS_OUTPUT.PUT_LINE('员工名:' || SP_RECORD.NAME || '工资：' || SP_RECORD.SALARY);
END;
/
七、复合类型——pl/sql表
相当于高级语言中的数组，但是需要注意的是在高级语言中数组的下标不能为负数，而pl/sql是可以为负数的，并且表元素的下标没有限制。实例如下：
代码
方法一(推荐)：
set serveroutput on; --打开输出选项
DECLARE
--定义了一个pl/sql表类型sp_table_type，该类型是用于存放EMP.ENAME%TYPE
--INDEX BY VARCHAR2(20)表示下标是字符串
TYPE SP_TABLE_TYPE IS TABLE OF EMP.ENAME%TYPE INDEX BY VARCHAR2(20);
--定义了一个sp_table变量，这个变量的类型是sp_table_type
SP_TABLE SP_TABLE_TYPE;
BEGIN
SELECT ENAME, sal INTO SP_TABLE('ename'), SP_TABLE('sal') FROM EMP WHERE EMPNO = 7788;
DBMS_OUTPUT.PUT_LINE('员工名:' || SP_TABLE('ename')||'工资：'||SP_TABLE('sal'));
END;
/
方法二：
set serveroutput on; --打开输出选项
DECLARE
--定义了一个pl/sql 表类型sp_table_type，该类型是用于存放EMP.ENAME%TYPE
--index by binary_integer表示下标是整数
TYPE SP_TABLE_TYPE IS TABLE OF EMP.ENAME%TYPE INDEX BY BINARY_INTEGER; --注意binary_integer如果换为integer就会报错，知道的朋友欢迎告诉我下
--定义了一个sp_table变量，这个变量的类型是sp_table_type
SP_TABLE SP_TABLE_TYPE;
BEGIN
SELECT ENAME,sal INTO SP_TABLE(-1),SP_TABLE(-2) FROM EMP WHERE EMPNO = 7788;
DBMS_OUTPUT.PUT_LINE('员工名:' || SP_TABLE(-1)||'工资：'||SP_TABLE(-2));
END;
/
说明：
sp_table_type是pl/sql表类型
emp.ename%type 指定了表的元素的类型和长度
sp_table 为pl/sql表变量
sp_table(0) 则表示下标为0的元素
注意：如果把select ename into sp_table(-1) from emp where empno = 7788；变成select ename into sp_table(-1) from emp;则运行时会出现错误，错误如下：ORA-01422:实际返回的行数超出请求的行数
解决方法是：使用参照变量(这里不讲)
八、复合变量——嵌套表(nested table)
复合变量——变长数组(varray)
省略
九、参照变量——介绍
参照变量是指用于存放数值指针的变量。通过使用参照变量，可以使得应用程序共享相同对象，从而降低占用的空间。在编写pl/sql程序时，可以使用游标变量(ref cursor)和对象类型变量(ref obj_type)两种参照变量类型。推荐使用游标变量。
十、参照变量——ref cursor游标变量
使用游标时，当定义游标时不需要指定相应的select语句，但是当使用游标时（open 时）需要指定select语句，这样一个游标与一个select语句结合了。实例如下：
1.请使用pl/sql编写一个块，可以输入部门号，并显示该部门所有员工姓名和他的工资。
2.在1的基础上，如果某个员工的工资低于200元，就添加100元。
代码：
SET serveroutput ON;
DECLARE
--定义游标
TYPE sp_emp_cursor IS REF CURSOR;
--定义一个游标变量
sp sp_emp_cursor;
--定义变量
v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;
BEGIN
OPEN sp FOR SELECT e.ename, e.sal FROM emp e WHERE e.deptno=10;
--方法一 loop循环
/*
LOOP
FETCH sp INTO v_ename, v_sal;
EXIT WHEN sp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('名字：' || V_ENAME || ' 工资：' || V_SAL);
END LOOP;*/
--方法二 while循环
/*
WHILE 1=1 LOOP
FETCH sp INTO v_ename, v_sal;
EXIT WHEN sp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('名字：' || V_ENAME || ' 工资：' || V_SAL);
END LOOP;*/
--方法三 for循环
FOR cur IN (SELECT e.ename, e.sal FROM emp e WHERE e.deptno=10) LOOP
DBMS_OUTPUT.PUT_LINE('名字：' || cur.ename || ' 工资：' || cur.sal);
END LOOP;
END;
/
【二十五、oracle pl/sql进阶--控制结构(分支，循环，控制)】
===================================================================
一、pl/sql的进阶--控制结构
在任何计算机语言(c，java，pascal)都有各种控制语句(条件语句，循环结构，顺序控制结构...)，在pl/sql中也存在这样的控制结构。
在本部分学习完成后，希望大家达到：
1.使用各种if语句
2.使用循环语句
3.使用控制语句——goto和null(goto语句不推荐使用)；
二、条件分支语句
pl/sql中提供了三种条件分支语句if—then，if–then–else，if–then–else if–then。
这里我们可以和java语句进行一个比较。
1)、简单的条件判断if–then
问题：编写一个过程，可以输入一个雇员名，如果该雇员的工资低于2000，就给该员工工资增加10%。
代码：
SET serveroutput ON;
CREATE OR REPLACE PROCEDURE SP_PRO6(SPNAME VARCHAR2) IS
--定义
V_SAL EMP.SAL%TYPE;
BEGIN
--执行
SELECT SAL INTO V_SAL FROM EMP WHERE ENAME = SPNAME;
--判断
IF V_SAL < 2000 THEN
UPDATE EMP SET SAL = SAL + SAL * 0.1 WHERE ENAME = SPNAME;
COMMIT;
END IF;
END;
/
--调用存储过程
exec SP_PRO6('ALLEN');
2)、二重条件分支 if–then–else
问题：编写一个过程，可以输入一个雇员名，如果该雇员的补助不是0就在原来的基础上增加100；如果补助为0就把补助设为200；
代码：
CREATE OR REPLACE PROCEDURE SP_PRO6(SPNAME VARCHAR2) IS
--定义
V_COMM EMP.COMM%TYPE;
BEGIN
--执行
SELECT COMM INTO V_COMM FROM EMP WHERE ENAME = SPNAME;
--判断
IF V_COMM <> 0 THEN
UPDATE EMP SET COMM = COMM + 100 WHERE ENAME = SPNAME;
ELSE
UPDATE EMP SET COMM = COMM + 200 WHERE ENAME = SPNAME;
END IF;
COMMIT;
END;
/
--调用存储过程
exec SP_PRO6('ALLEN');
3)、多重条件分支 if–then–ELSIF–then
问题：编写一个过程，可以输入一个雇员编号，如果该雇员的职位是PRESIDENT就给他的工资增加1000，如果该雇员的职位是MANAGER 就给他的工资增加500，其它职位的雇员工资增加200。
代码：
CREATE OR REPLACE PROCEDURE SP_PRO6(SPNO NUMBER) IS
--定义
V_JOB EMP.JOB%TYPE;
BEGIN
--执行
SELECT JOB INTO V_JOB FROM EMP WHERE EMPNO = SPNO;
IF V_JOB = 'PRESIDENT' THEN
UPDATE EMP SET SAL = SAL + 1000 WHERE EMPNO = SPNO;
ELSIF V_JOB = 'MANAGER' THEN
UPDATE EMP SET SAL = SAL + 500 WHERE EMPNO = SPNO;
ELSE
UPDATE EMP SET SAL = SAL + 200 WHERE EMPNO = SPNO;
END IF;
COMMIT;
END;
/
--调用存储过程
exec SP_PRO6(7499);
三、循环语句–loop
是pl/sql中最简单的循环语句，这种循环语句以loop开头，以end loop结尾，这种循环至少会被执行一次。
案例：现有一张表users，表结构如下：
用户vid | 用户名 uname
CREATE TABLE USERS(
vid NUMBER(5),
uname VARCHAR2(30)
);
请编写一个过程，可以输入用户名，并循环添加10个用户到users表中，用户编号从1开始增加。
代码：
CREATE OR REPLACE PROCEDURE SP_PRO6(SPNAME VARCHAR2) IS
--定义 :=表示赋值
V_NUM NUMBER := 1;
BEGIN
LOOP
INSERT INTO USERS VALUES (V_NUM, SPNAME);
--判断是否要退出循环
EXIT WHEN V_NUM = 10;
--自增
V_NUM := V_NUM + 1;
END LOOP;
COMMIT;
END;
/
--调用存储过程
EXEC SP_PRO6('ALLEN');
四、循环语句–while循环
基本循环至少要执行循环体一次，而对于while循环来说，只有条件为true时，才会执行循环体语句，while循环以while...loop开始，以end loop 结束。
案例：现有一张表users，表结构如下：
用户vid | 用户名 uname
问题：请编写一个过程，可以输入用户名，并循环添加10个用户到users表中，用户编号从11开始增加。
代码：
CREATE OR REPLACE PROCEDURE SP_PRO6(SPNAME VARCHAR2) IS
--定义 :=表示赋值
V_NUM NUMBER := 11;
BEGIN
WHILE V_NUM <= 20 LOOP
--执行
INSERT INTO USERS VALUES (V_NUM, SPNAME);
V_NUM := V_NUM + 1;
END LOOP;
COMMIT;
END;
/
--调用存储过程
EXEC SP_PRO6('ALLEN');
五、循环语句–for循环
基本for循环的基本结构如下
代码：
CREATE OR REPLACE PROCEDURE SP_PRO6 IS--注意如果无参记得不要加()
BEGIN
FOR I IN REVERSE 1 .. 10 LOOP --REVERSE反转函数，表示I从10到1递减，去掉REVERSE表示I从1到10递增
INSERT INTO USERS VALUES (I, 'shunping');
END LOOP;
END;
/
--调用存储过程
EXEC SP_PRO6;
我们可以看到控制变量i，在隐含中就在不停地增加。
六、顺序控制语句–goto、null
1)、goto语句
goto语句用于跳转到特定符号去执行语句。注意由于使用goto语句会增加程序的复杂性，并使得应用程序可读性变差，所以在做一般应用开发时，建议大家不要使用goto语句。
基本语法如下goto lable，其中lable是已经定义好的标号名
代码：
set serveroutput on;
DECLARE
I INT := 1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE('输出i=' || I);
IF I = 1 THEN
GOTO END_LOOP;
END IF;
I := I + 1;
END LOOP;
<<END_LOOP>>
DBMS_OUTPUT.PUT_LINE('循环结束');
END;
/
2)、null语句
null语句不会执行任何操作，并且会直接将控制传递到下一条语句。使用null语句的主要好处是可以提高pl/sql的可读性。
代码：
SET serveroutput ON;
DECLARE
V_SAL EMP.SAL%TYPE;
V_ENAME EMP.ENAME%TYPE;
BEGIN
SELECT ENAME, SAL INTO V_ENAME, V_SAL FROM EMP WHERE EMPNO = &NO;
IF V_SAL < 3000 THEN
UPDATE EMP SET COMM = SAL * 0.1 WHERE ENAME = V_ENAME;
dbms_output.put_line('1111');
ELSE
NULL;
dbms_output.put_line('2222');--不会被执行
END IF;
END;
/
【二十六、oracle pl/sql 分页】
===================================================================
一、无返回值的存储过程
古人云：欲速则不达，为了让大家伙比较容易接受分页过程编写，我还是从简单到复杂，循序渐进的给大家讲解。首先是掌握最简单的存储过程，无返回值的存储过程。 案例：现有一张表book，表结构如下：书号、书名、出版社。
CREATE TABLE book(
ID NUMBER(4),
book_name VARCHAR2(30),
publishing VARCHAR2(30)
);
请写一个过程，可以向book表添加书，要求通过java程序调用该过程。
代码：
--注意：in->表示这是一个输入参数，默认为in --out->表示一个输出参数
CREATE OR REPLACE PROCEDURE ADD_BOOK(ID IN NUMBER,
NAME IN VARCHAR2,
PUBLISHING IN VARCHAR2) IS
BEGIN
INSERT INTO BOOK VALUES (ID, NAME, PUBLISHING);
COMMIT;
END;
/
java程序调用该存储过程的代码
代码：
package junit.test;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
/**
* 调用一个无返回值的存储过程
*
* @author jiqinlin
*
*/
public class ProcedureTest {
public static void main(String[] args) {
try {
// 1.加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
// 2.得到连接
Connection ct = DriverManager.getConnection(
"jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "oracle");
// 3.创建CallableStatement
CallableStatement cs = ct.prepareCall("call ADD_BOOK(?,?,?)");
//给?赋值
cs.setInt(1, 1);
cs.setString(2, "java");
cs.setString(3, "java出版社");
// 4.执行
cs.execute();
//5、关闭
cs.close();
ct.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
二、有返回值的存储过程(非列表)
案例：编写一个存储过程，可以输入雇员的编号，返回该雇员的姓名。
--输入和输出的存储过程
CREATE OR REPLACE PROCEDURE SP_PROC(SPNO IN NUMBER, SPNAME OUT VARCHAR2) IS
BEGIN
SELECT ENAME INTO SPNAME FROM EMP WHERE EMPNO = SPNO;
END;
/
java程序调用该存储过程的代码
代码：
package junit.test;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
/**
* 调用一个无返回值的存储过程
*
* @author jiqinlin
*
*/
public class ProcedureTest {
public static void main(String[] args) {
try {
// 1.加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
// 2.得到连接
Connection ct = DriverManager.getConnection(
"jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "oracle");
// 3.创建CallableStatement
CallableStatement cs = ct.prepareCall("{call sp_proc(?,?)}");
//给第一个?赋值
cs.setInt(1,7788);
//给第二个?赋值
cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);
//4、执行
cs.execute();
//取出返回值,要注意？的顺序
String name=cs.getString(2);
System.out.println("编号7788的名字："+name);
//5、关闭
cs.close();
ct.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
案例扩张：编写一个过程，可以输入雇员的编号，返回该雇员的姓名、工资和岗位。
代码：
--输入和输出的存储过程
CREATE OR REPLACE PROCEDURE SP_PROC(SPNO IN NUMBER,
SPNAME OUT VARCHAR2,
SPSAL OUT NUMBER,
SPJOB OUT VARCHAR2) IS
BEGIN
SELECT ENAME, SAL, JOB INTO SPNAME, SPSAL, SPJOB FROM EMP WHERE EMPNO = SPNO;
END;
/
java程序调用该存储过程的代码
代码：
package junit.test;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
/**
* 调用一个无返回值的存储过程
*
* @author jiqinlin
*
*/
public class ProcedureTest {
public static void main(String[] args) {
try {
// 1.加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
// 2.得到连接
Connection ct = DriverManager.getConnection(
"jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "oracle");
// 3.创建CallableStatement
CallableStatement cs = ct.prepareCall("{call sp_proc(?,?,?,?)}");
//给第一个?赋值
cs.setInt(1,7788);
//给第二个?赋值
cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);
//给第三个？赋值
cs.registerOutParameter(3,oracle.jdbc.OracleTypes.DOUBLE);
//给第四个？赋值
cs.registerOutParameter(4,oracle.jdbc.OracleTypes.VARCHAR);
//4、执行
cs.execute();
//取出返回值,要注意？的顺序
String name=cs.getString(2);
double sal=cs.getDouble(3);
String job=cs.getString(4);
System.out.println("编号7788的名字："+name+"，职位："+job+"，薪水："+sal+"");
//5、关闭
cs.close();
ct.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
三、有返回值的存储过程(列表[结果集])
案例：编写一个存储过程，输入部门号，返回该部门所有雇员信息。
该题分析如下：由于oracle存储过程没有返回值，它的所有返回值都是通过out参数来替代的，列表同样也不例外，但由于是集合，所以不能用一般的参数，必须要用pagkage了。所以要分两部分：
1)、建立一个包，在该包中我们定义类型test_cursor，它是个游标。
CREATE OR REPLACE PACKAGE TESTPACKAGE AS
TYPE TEST_CURSOR IS REF CURSOR;
END TESTPACKAGE;
/
2)、建立存储过程。
代码：
CREATE OR REPLACE PROCEDURE SP_PROC(SPNO IN NUMBER,
P_CURSOR OUT TESTPACKAGE.TEST_CURSOR) IS
BEGIN
OPEN P_CURSOR FOR
SELECT * FROM EMP WHERE DEPTNO = SPNO;
END SP_PROC;
/
3)、如何在java 程序中调用该过程
代码：
package junit.test;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
/**
* 调用一个无返回值的存储过程
*
* @author jiqinlin
*
*/
public class ProcedureTest {
public static void main(String[] args) {
try {
// 1.加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
// 2.得到连接
Connection ct = DriverManager.getConnection(
"jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "oracle");
// 3.创建CallableStatement
CallableStatement cs = ct.prepareCall("{call sp_proc(?,?)}");
//给第一个?赋值
cs.setInt(1,10);
//给第二个?赋值
cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);
//4、执行
cs.execute();
//得到结果集
ResultSet rs = (ResultSet) cs.getObject(2);
while (rs.next()) {
System.out.println(rs.getInt(1) + " " + rs.getString(2));
}
//5、关闭
rs.close();
cs.close();
ct.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
四、编写分页过程
有了上面的基础，相信大家可以完成分页存储过程了。
要求，请大家编写一个存储过程，要求可以输入表名、每页显示记录数、当前页。返回总记录数，总页数，和返回的结果集。
--ROWNUM用法
SELECT o.*, ROWNUM RN FROM (SELECT * FROM EMP) o WHERE ROWNUM <= 10;
----oracle分页sql语句；在分页时，大家可以把下面的sql语句当做一个模板使用
SELECT *
FROM (SELECT o.*, ROWNUM RN FROM (SELECT * FROM EMP) o WHERE ROWNUM <= 10)
WHERE RN >= 6;
1)、开发一个包
建立一个包，在该包中定义类型为test_cursor的游标。
代码：
--建立一个包
CREATE OR REPLACE PACKAGE TESTPACKAGE AS
TYPE TEST_CURSOR IS REF CURSOR;
END TESTPACKAGE;
/
--开始编写分页的过程
CREATE OR REPLACE PROCEDURE FENYE(TABLENAME IN VARCHAR2,
PAGESIZE IN NUMBER, --每页显示记录数
PAGENOW IN NUMBER, --页数
MYROWS OUT NUMBER, --总记录数
MYPAGECOUNT OUT NUMBER, --总页数
P_CURSOR OUT TESTPACKAGE.TEST_CURSOR) IS --返回的记录集
--定义部分
--定义sql语句字符串
V_SQL VARCHAR2(1000);
--定义两个整数
V_BEGIN NUMBER := (PAGENOW - 1) * PAGESIZE + 1;
V_END NUMBER := PAGENOW * PAGESIZE;
BEGIN
--执行部分
V_SQL := 'select * from (select t1.*, rownum rn from (select * from ' || TABLENAME || ') t1 where rownum<=' || V_END || ') where rn>=' || V_BEGIN;
--把游标和sql关联
OPEN P_CURSOR FOR V_SQL;
--计算myrows和myPageCount
--组织一个sql语句
V_SQL := 'select count(*) from ' || TABLENAME;
--执行sql,并把返回的值，赋给myrows；
EXECUTE ImMEDIATE V_SQL INTO MYROWS; --它解析并马上执行动态的SQL语句或非运行时创建的PL/SQL块.动态创建和执行SQL语句性能超前，
--EXECUTE IMMEDIATE的目标在于减小企业费用并获得较高的性能，较之以前它相当容易编码.
--尽管DBMS_SQL仍然可用，但是推荐使用EXECUTE IMMEDIATE,因为它获的收益在包之上。
--计算myPageCount
--if myrows%Pagesize=0 then 这样写是错的
IF MOD(MYROWS, PAGESIZE) = 0 THEN
MYPAGECOUNT := MYROWS/PAGESIZE;
ELSE
MYPAGECOUNT := MYROWS/PAGESIZE + 1;
END IF;
--关闭游标
--CLOSE P_CURSOR; --不要关闭，否则java调用该存储过程会报错
END;
/
java调用分页代码
代码：
package junit.test;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
/**
* 调用一个无返回值的存储过程
*
* @author jiqinlin
*
*/
public class ProcedureTest {
public static void main(String[] args) {
try {
// 1.加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
// 2.得到连接
Connection ct = DriverManager.getConnection(
"jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "oracle");
// 3.创建CallableStatement
CallableStatement cs = ct.prepareCall("{call fenye(?,?,?,?,?,?)}");
cs.setString(1, "emp"); //表名
cs.setInt(2, 5); //每页显示记录数
cs.setInt(3, 1);//页数
// 注册总记录数
cs.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER); //总记录数
// 注册总页数
cs.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER); //总页数
// 注册返回的结果集
cs.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR); //返回的记录集
// 4、执行
cs.execute();
// 得到结果集
// 取出总记录数 /这里要注意，getInt(4)中4，是由该参数的位置决定的
int rowNum = cs.getInt(4);
int pageCount = cs.getInt(5);
ResultSet rs = (ResultSet) cs.getObject(6);
// 显示一下，看看对不对
System.out.println("rowNum=" + rowNum);
System.out.println("总页数=" + pageCount);
while (rs.next()) {
System.out.println("编号：" + rs.getInt(1) +
" 名字：" + rs.getString(2) +
" 工资：" + rs.getFloat(6));
}
// 5、关闭
//rs.close();
cs.close();
ct.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
【二十七、oracle 例外】
===================================================================
一、例外分类
oracle将例外分为预定义例外、非预定义例外和自定义例外三种。
1)、预定义例外用于处理常见的oracle错误。
2)、非预定义例外用于处理预定义例外不能处理的例外。
3)、自定义例外用于处理与oracle错误无关的其它情况。
下面通过一个小案例演示如果不处理例外看会出现什么情况？
编写一个存储过程，可接收雇员的编号，并显示该雇员的姓名。
sql代码如下：
代码：
SET SERVEROUTPUT ON;
DECLARE
V_ENAME EMP.ENAME%TYPE;
BEGIN
SELECT ENAME INTO V_ENAME FROM EMP WHERE EMPNO = &GNO;
DBMS_OUTPUT.PUT_LINE('名字：' || V_ENAME);
END;
/
随便输入不存在的编号，回车，会抛出如下异常：
ORA-01403: 未找到数据
ORA-06512: 在line 6
例外捕获的sql代码如下：
代码：
SET SERVEROUTPUT ON;
DECLARE
V_ENAME EMP.ENAME%TYPE;
BEGIN
SELECT ENAME INTO V_ENAME FROM EMP WHERE EMPNO = &GNO;
DBMS_OUTPUT.PUT_LINE('名字：' || V_ENAME);
EXCEPTION
WHEN no_data_found THEN
DBMS_OUTPUT.PUT_LINE('编号未找到！');
END;
/
随便输入不存在的编号，回车，会友情提示：编号未找到！
二、处理预定义例外
预定义例外是由pl/sql所提供的系统例外。当pl/sql应用程序违反了oracle规定的限制时，则会隐含的触发一个内部例外。pl/sql为开发人员提供了二十多个预定义例外。我们给大家介绍常用的例外。
1)、case_not_found预定义例外
在开发pl/sql块中编写case语句时，如果在when子句中没有包含必须的条件分支，就会触发case_not_found例外：
代码：
SET SERVEROUTPUT ON;
CREATE OR REPLACE PROCEDURE SP_PRO6(SPNO NUMBER) IS
V_SAL EMP.SAL%TYPE;
BEGIN
SELECT SAL INTO V_SAL FROM EMP WHERE EMPNO = SPNO;
CASE
WHEN V_SAL < 1000 THEN
UPDATE EMP SET SAL = SAL + 100 WHERE EMPNO = SPNO;
WHEN V_SAL < 2000 THEN
UPDATE EMP SET SAL = SAL + 200 WHERE EMPNO = SPNO;
END CASE;
EXCEPTION
WHEN CASE_NOT_FOUND THEN
DBMS_OUTPUT.PUT_LINE('case语句没有与' || V_SAL || '相匹配的条件');
END;
/
--调用存储过程
SQL> EXEC SP_PRO6(7369);
case语句没有与4444相匹配的条件
2)、cursor_already_open预定义例外
当重新打开已经打开的游标时，会隐含的触发cursor_already_open例外
代码：
DECLARE
CURSOR EMP_CURSOR IS
SELECT ENAME, SAL FROM EMP;
BEGIN
OPEN EMP_CURSOR; --声明时游标已打开，所以没必要再次打开
FOR EMP_RECORD1 IN EMP_CURSOR LOOP
DBMS_OUTPUT.PUT_LINE(EMP_RECORD1.ENAME);
END LOOP;
EXCEPTION
WHEN CURSOR_ALREADY_OPEN THEN
DBMS_OUTPUT.PUT_LINE('游标已经打开');
END;
/
3)、dup_val_on_index预定义例外
在唯一索引所对应的列上插入重复的值时，会隐含的触发例外
代码：
BEGIN
INSERT INTO DEPT VALUES (10, '公关部', '北京');
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE('在deptno列上不能出现重复值');
END;
/
4)、invalid_cursorn预定义例外
当试图在不合法的游标上执行操作时，会触发该例外
例如：试图从没有打开的游标提取数据，或是关闭没有打开的游标。则会触发该例外
代码：
DECLARE
CURSOR EMP_CURSOR IS
SELECT ENAME, SAL FROM EMP;
EMP_RECORD EMP_CURSOR%ROWTYPE;
BEGIN
--open emp_cursor; --打开游标
FETCH EMP_CURSOR INTO EMP_RECORD;
DBMS_OUTPUT.PUT_LINE(EMP_RECORD.ENAME);
CLOSE EMP_CURSOR;
EXCEPTION
WHEN INVALID_CURSOR THEN
DBMS_OUTPUT.PUT_LINE('请检测游标是否打开');
END;
/
5)、invalid_number预定义例外
当输入的数据有误时，会触发该例外
比如：数字100写成了loo就会触发该例外
代码：
SET SERVEROUTPUT ON;
BEGIN
UPDATE EMP SET SAL = SAL + 'AAA';
EXCEPTION
WHEN INVALID_NUMBER THEN
DBMS_OUTPUT.PUT_LINE('输入的数字不正确');
END;
/
6)、no_data_found预定义例外
下面是一个pl/sql 块，当执行select into没有返回行，就会触发该例外
代码：
SET serveroutput ON;
DECLARE
V_SAL EMP.SAL%TYPE;
BEGIN
SELECT SAL INTO V_SAL FROM EMP WHERE ENAME = 'ljq';
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('不存在该员工');
END;
/
7)、too_many_rows预定义例外
当执行select into语句时，如果返回超过了一行，则会触发该例外。
代码：
DECLARE
V_ENAME EMP.ENAME%TYPE;
BEGIN
SELECT ENAME INTO V_ENAME FROM EMP;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('返回了多行');
END;
/
8)、zero_divide预定义例外
当执行2/0语句时，则会触发该例外
9)、value_error预定义例外
当在执行赋值操作时，如果变量的长度不足以容纳实际数据，则会触发该例外value_error
其它预定义例外(这些例外不是在pl/sql里触发的，而是在用oracle时触发的，所以取名叫其它预定义例外)
1、login_denied
当用户非法登录时，会触发该例外
2、not_logged_on
如果用户没有登录就执行dml操作，就会触发该例外
3、storage_error
如果超过了内存空间或是内存被损坏，就触发该例外
4、timeout_on_resource
如果oracle在等待资源时，出现了超时就触发该例外
三、非预定义例外
非预定义例外用于处理与预定义例外无关的oracle错误。使用预定义例外只能处理21个oracle 错误，而当使用pl/sql开发应用程序时，可能会遇到其它的一些oracle错误。比如在pl/sql块中执行dml语句时，违反了约束规定等等。在这样的情况下，也可以处理oracle的各种例外，因为非预定义例外用的不多，这里我就不举例了。
四、处理自定义例外
预定义例外和自定义例外都是与oracle错误相关的，并且出现的oracle 错误会隐含的触发相应的例外；而自定义例外与oracle 错误没有任何关联，它是由开发人员为特定情况所定义的例外.
问题：请编写一个pl/sql 块，接收一个雇员的编号，并给该雇员工资增加1000元，如果该雇员不存在，请提示。
代码：
CREATE OR REPLACE PROCEDURE EX_TEST(SPNO NUMBER) IS
BEGIN
UPDATE EMP SET SAL = SAL + 1000 WHERE EMPNO = SPNO;
END;
/
--调用存储过程，
EXEC EX_TEST(56);
这里，编号为56 是不存在的，刚才的报异常了，为什么现在不报异常呢？
因为刚才的是select语句
怎么解决这个问题呢？ 修改代码，如下：
代码：
--自定义例外
CREATE OR REPLACE PROCEDURE EX_TEST(SPNO NUMBER) IS
--定义一个例外
MYEX EXCEPTION;
BEGIN
--更新用户sal
UPDATE EMP SET SAL = SAL + 1000 WHERE EMPNO = SPNO;
--sql%notfound 这是表示没有update
--raise myex;触发myex
IF SQL%NOTFOUND THEN RAISE MYEX;
END IF;
EXCEPTION
WHEN MYEX THEN DBMS_OUTPUT.PUT_LINE('没有更新任何用户');
END;
/
现在再测试一次：
SQL> exec ex_test(56);
没有更新任何用户
【二十八、oracle 视图】
===================================================================
一、介绍
视图是一张虚拟表，其内容由查询定义，同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。(视图不是真实存在磁盘上的)
二、视图与表的区别
1、表需要占用磁盘空间，视图不需要
2、视图不能添加索引(所以查询速度略微慢点)
3、使用视图可以简化，复杂查询
4、视图的使用利于提高安全性
比如：不同用户查看不同视图
三、创建/修改视图
1、创建视图
create view 视图名 as select 语句 [with read only]
2、创建或修改视图
create or replace view 视图名 as select 语句 [with read only]
3、删除视图
drop view 视图名
注意：当表结构国语复杂，请使用视图吧！
【二十九、oracle 触发器】
===================================================================
一、触发器简介
触发器的定义就是说某个条件成立的时候，触发器里面所定义的语句就会被自动的执行。因此触发器不需要人为的去调用，也不能调用。然后，触发器的触发条件其实在你定义的时候就已经设定好了。这里面需要说明一下，触发器可以分为语句级触发器和行级触发器。详细的介绍可以参考网上的资料，简单的说就是语句级的触发器可以在某些语句执行前或执行后被触发。而行级触发器则是在定义的了触发的表中的行数据改变时就会被触发一次。
具体举例：
1、 在一个表中定义的语句级的触发器，当这个表被删除时，程序就会自动执行触发器里面定义的操作过程。这个就是删除表的操作就是触发器执行的条件了。
2、 在一个表中定义了行级的触发器，那当这个表中一行数据发生变化的时候，比如删除了一行记录，那触发器也会被自动执行了。
二、触发器语法
触发器的语法：
create [or replace] tigger 触发器名 触发时间 触发事件
on 表名
[for each row]
begin
pl/sql语句
end
其中：
触发器名：触发器对象的名称。由于触发器是数据库自动执行的，因此该名称只是一个名称，没有实质的用途。
触发时间：指明触发器何时执行，该值可取：
before：表示在数据库动作之前触发器执行;
after：表示在数据库动作之后触发器执行。
触发事件：指明哪些数据库动作会触发此触发器：
insert：数据库插入会触发此触发器;
update：数据库修改会触发此触发器;
delete：数据库删除会触发此触发器。
表 名：数据库触发器所在的表。
for each row：对表的每一行触发器执行一次。如果没有这一选项，则只对整个表执行一次。
触发器能实现如下功能：
功能：
1、 允许/限制对表的修改
2、 自动生成派生列，比如自增字段
3、 强制数据一致性
4、 提供审计和日志记录
5、 防止无效的事务处理
6、 启用复杂的业务逻辑
举例
1)、下面的触发器在更新表tb_emp之前触发，目的是不允许在周末修改表：
代码：
create or replace trigger auth_secure before insert or update or DELETE
on tb_emp
begin
IF(to_char(sysdate,'DY')='星期日') THEN
RAISE_APPLICATION_ERROR(-20600,'不能在周末修改表tb_emp');
END IF;
END;
/
2)、使用触发器实现序号自增
创建一个测试表：
create table tab_user(
id number(11) primary key,
username varchar(50),
password varchar(50)
);
创建一个序列：
create sequence my_seq increment by 1 start with 1 nomaxvalue nocycle cache 20;
创建一个触发器：
代码：
CREATE OR REPLACE TRIGGER MY_TGR
BEFORE INSERT ON TAB_USER
FOR EACH ROW--对表的每一行触发器执行一次
DECLARE
NEXT_ID NUMBER;
BEGIN
SELECT MY_SEQ.NEXTVAL INTO NEXT_ID FROM DUAL;
:NEW.ID := NEXT_ID; --:NEW表示新插入的那条记录
END;
向表插入数据:
insert into tab_user(username,password) values('admin','admin');
insert into tab_user(username,password) values('fgz','fgz');
insert into tab_user(username,password) values('test','test');
COMMIT;
查询表结果：SELECT * FROM TAB_USER;
3)、当用户对test表执行DML语句时，将相关信息记录到日志表
代码
--创建测试表
CREATE TABLE test(
t_id NUMBER(4),
t_name VARCHAR2(20),
t_age NUMBER(2),
t_sex CHAR
);
--创建记录测试表
CREATE TABLE test_log(
l_user VARCHAR2(15),
l_type VARCHAR2(15),
l_date VARCHAR2(30)
);
创建触发器：
代码：
--创建触发器
CREATE OR REPLACE TRIGGER TEST_TRIGGER
AFTER DELETE OR INSERT OR UPDATE ON TEST
DECLARE
V_TYPE TEST_LOG.L_TYPE%TYPE;
BEGIN
IF INSERTING THEN
--INSERT触发
V_TYPE := 'INSERT';
DBMS_OUTPUT.PUT_LINE('记录已经成功插入，并已记录到日志');
ELSIF UPDATING THEN
--UPDATE触发
V_TYPE := 'UPDATE';
DBMS_OUTPUT.PUT_LINE('记录已经成功更新，并已记录到日志');
ELSIF DELETING THEN
--DELETE触发
V_TYPE := 'DELETE';
DBMS_OUTPUT.PUT_LINE('记录已经成功删除，并已记录到日志');
END IF;
INSERT INTO TEST_LOG
VALUES
(USER, V_TYPE, TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss')); --USER表示当前用户名
END;
/
--下面我们来分别执行DML语句
INSERT INTO test VALUES(101,'zhao',22,'M');
UPDATE test SET t_age = 30 WHERE t_id = 101;
DELETE test WHERE t_id = 101;
--然后查看效果
SELECT * FROM test;
SELECT * FROM test_log;
运行结果如下：
3)、创建触发器，它将映射emp表中每个部门的总人数和总工资
代码：
--创建映射表
CREATE TABLE dept_sal AS
SELECT deptno, COUNT(empno) total_emp, SUM(sal) total_sal
FROM scott.emp
GROUP BY deptno;
--创建触发器
CREATE OR REPLACE TRIGGER EMP_INFO
AFTER INSERT OR UPDATE OR DELETE ON scott.EMP
DECLARE
CURSOR CUR_EMP IS
SELECT DEPTNO, COUNT(EMPNO) AS TOTAL_EMP, SUM(SAL) AS TOTAL_SAL FROM scott.EMP GROUP BY DEPTNO;
BEGIN
DELETE DEPT_SAL; --触发时首先删除映射表信息
FOR V_EMP IN CUR_EMP LOOP
--DBMS_OUTPUT.PUT_LINE(v_emp.deptno || v_emp.total_emp || v_emp.total_sal);
--插入数据
INSERT INTO DEPT_SAL
VALUES
(V_EMP.DEPTNO, V_EMP.TOTAL_EMP, V_EMP.TOTAL_SAL);
END LOOP;
END;
--对emp表进行DML操作
INSERT INTO emp(empno,deptno,sal) VALUES('123','10',10000);
SELECT * FROM dept_sal;
DELETE EMP WHERE empno=123;
SELECT * FROM dept_sal;
显示结果如下：
4)、创建触发器，用来记录表的删除数据
代码：
--创建表
CREATE TABLE employee(
id VARCHAR2(4) NOT NULL,
name VARCHAR2(15) NOT NULL,
age NUMBER(2) NOT NULL,
sex CHAR NOT NULL
);
--插入数据
INSERT INTO employee VALUES('e101','zhao',23,'M');
INSERT INTO employee VALUES('e102','jian',21,'F');
--创建记录表(包含数据记录)
CREATE TABLE old_employee AS SELECT * FROM employee;
--创建触发器
CREATE OR REPLACE TRIGGER TIG_OLD_EMP
AFTER DELETE ON EMPLOYEE
FOR EACH ROW --语句级触发，即每一行触发一次
BEGIN
INSERT INTO OLD_EMPLOYEE VALUES (:OLD.ID, :OLD.NAME, :OLD.AGE, :OLD.SEX); --:old代表旧值
END;
/
--下面进行测试
DELETE employee;
SELECT * FROM old_employee;
5)、创建触发器，利用视图插入数据
代码：
--创建表
CREATE TABLE tab1 (tid NUMBER(4) PRIMARY KEY,tname VARCHAR2(20),tage NUMBER(2));
CREATE TABLE tab2 (tid NUMBER(4),ttel VARCHAR2(15),tadr VARCHAR2(30));
--插入数据
INSERT INTO tab1 VALUES(101,'zhao',22);
INSERT INTO tab1 VALUES(102,'yang',20);
INSERT INTO tab2 VALUES(101,'13761512841','AnHuiSuZhou');
INSERT INTO tab2 VALUES(102,'13563258514','AnHuiSuZhou');
--创建视图连接两张表
CREATE OR REPLACE VIEW tab_view AS SELECT tab1.tid,tname,ttel,tadr FROM tab1,tab2 WHERE tab1.tid = tab2.tid;
--创建触发器
CREATE OR REPLACE TRIGGER TAB_TRIGGER
INSTEAD OF INSERT ON TAB_VIEW
BEGIN
INSERT INTO TAB1 (TID, TNAME) VALUES (:NEW.TID, :NEW.TNAME);
INSERT INTO TAB2 (TTEL, TADR) VALUES (:NEW.TTEL, :NEW.TADR);
END;
/
--现在就可以利用视图插入数据
INSERT INTO tab_view VALUES(106,'ljq','13886681288','beijing');
--查询
SELECT * FROM tab_view;
SELECT * FROM tab1;
SELECT * FROM tab2;
6)、创建触发器，比较emp表中更新的工资
代码：
--创建触发器
set serveroutput on;
CREATE OR REPLACE TRIGGER SAL_EMP
BEFORE UPDATE ON EMP
FOR EACH ROW
BEGIN
IF :OLD.SAL > :NEW.SAL THEN
DBMS_OUTPUT.PUT_LINE('工资减少');
ELSIF :OLD.SAL < :NEW.SAL THEN
DBMS_OUTPUT.PUT_LINE('工资增加');
ELSE
DBMS_OUTPUT.PUT_LINE('工资未作任何变动');
END IF;
DBMS_OUTPUT.PUT_LINE('更新前工资 ：' || :OLD.SAL);
DBMS_OUTPUT.PUT_LINE('更新后工资 ：' || :NEW.SAL);
END;
/
--执行UPDATE查看效果
UPDATE emp SET sal = 3000 WHERE empno = '7788';
运行结果如下：
7)、创建触发器，将操作CREATE、DROP存储在log_info表
代码：
--创建表
CREATE TABLE log_info(
manager_user VARCHAR2(15),
manager_date VARCHAR2(15),
manager_type VARCHAR2(15),
obj_name VARCHAR2(15),
obj_type VARCHAR2(15)
);
--创建触发器
set serveroutput on;
CREATE OR REPLACE TRIGGER TRIG_LOG_INFO
AFTER CREATE OR DROP ON SCHEMA
BEGIN
INSERT INTO LOG_INFO
VALUES
(USER,
SYSDATE,
SYS.DICTIONARY_OBJ_NAME,
SYS.DICTIONARY_OBJ_OWNER,
SYS.DICTIONARY_OBJ_TYPE);
END;
/
--测试语句
CREATE TABLE a(id NUMBER);
CREATE TYPE aa AS OBJECT(id NUMBER);
DROP TABLE a;
DROP TYPE aa;
--查看效果
SELECT * FROM log_info;
--相关数据字典-----------------------------------------------------
SELECT * FROM USER_TRIGGERS;
--必须以DBA身份登陆才能使用此数据字典
SELECT * FROM ALL_TRIGGERS;SELECT * FROM DBA_TRIGGERS;
--启用和禁用
ALTER TRIGGER trigger_name DISABLE;
ALTER TRIGGER trigger_name ENABLE;
