--函数
--函数用于返回特定的数据,当建立函数时,在函数头部必须包含return子句,
--而在函数体内必须包含return语句返回的数据。
--我们可以使用create function 来建立函数,实际案例:
--函数案例
--输入雇员的姓名,返回该雇员的年薪
create function sp_fun(spName varchar2) return
number is yearSal number(7,2);
begin
--执行部分
select sal*12+nvl(comm,0)*12 into yearSal from emp where ename=spName;
return yearSal;
end;
/
--在sqlplus中调用函数
--sql>var income number
--sql>call annual_income('SCOTT') into:income;
--sql>print income
--同样我们可以在Java程序中调用该函数
--select annual_income('SCOTT') from dual;//这样
--可以通过rs.getInt(1)得到返回的结果
var income number;
call sp_fun('SCOTT') into:income;
--java程序调用函数
/*
try{
//1、加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//2、得到连接
Connection ct = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL", "scott", "hyd123HYD");
System.out.println("数据连接成功");
//3、创建CallableStatement
CallableStatement cs = ct.prepareCall("{?=call sp_fun(?)}");
//4、给?赋值
cs.setString(2, "SCOTT");
//5、给输出值加入类型
cs.registerOutParameter(1, java.sql.Types.FLOAT);
//6、执行
cs.execute();
//7、接收
int a=cs.getInt(1);
System.out.println("函数调用的结果是:"+a);
//7、关闭
cs.close();
ct.close();
}catch(Exception e){
e.printStackTrace();
}
*/
/*
包
包用于在逻辑上组合过程和函数,它由包规范和包体两部分组成。
1、我们可以使用create package来创建包:
实例:
create package sp_package is
procedure update_sal(name varchar2,newsal number);
function annual_income(name varchar2) return number;
end;
包的规范只包含了过程和函数的说明,但是没有过程和函数的实现代码,
包体用于实现包规范中的过程和函数。
2、建立包体可以使用 create package body 命令
*/
create package sp_package is
procedure update_sal(name varchar2,newsal number); --声明该包有一个过程和函数
function annual_income(name varchar2) return number;
end;
--建立包体
create or replace package body sp_package is
procedure update_sal(name varchar2,newsal number)
is
begin
update emp set sal=newsal where ename=name;
end;
function annual_income(name varchar2)
return number is
annual_salary number;
begin
select sal*12+nvl(comm,0) into annual_salary from emp where ename=name;
return annual_salary;
end;
end;
/*
如何调用包的过程或是函数
当调用包的过程或是函数时,在过程和函数前需要带有包名,如果要访问其他方案的包,
还需要在包名前加方案名。
如:SQL>call sp_package.update_sal('SCOTT',1500);
特别说明
包是pl/sql中非常重要的部分,我们在使用过程分页时,会更能体会
*/
call sp_package.update_sal('SCOTT',1500);
select * from emp where ename='SCOTT';
/*
触发器是指隐含的执行的存储过程。当定义触发器时,必须要指定触发的事件
和触发的操作,常用的触发事件包括insert,update,delete语句,而触发操作
实际上是一个pl/sql块。可以使用create trigger来建立触发器。
触发器内容多,一般不会主动去调用,就是指用户登录触发一件事情,或者用户动了另一张表
去修改另外一张表,我们就可以得到主外键,一个外键删除就把主键也删除,联动的触发。
*/
/*
在编写pl/sql程序时,可以定义变量和常量;
在pl/sql程序中包括有:
1、标量类型(scalar)
2、复合类型(composite)
3、参照类型(reference)
4、lob(large object)
标量(scalar)-常用类型
在编写pl/sql块时,如果要使用变量,需在定义部分定义变量。
pl/sql中定义变量和常量的语法如下:
identifier [constant] datatype [not null] [:=| default expr]
identifier:指定常量。需要指定它的初始值,且其值是不能改变的
datatype:数据类型
not null:指定变量值不能为null
:= 给变量或是常量指定初始值
default 用于指定初始值
expr 指定初始值的pl/sql表达式,可是文本值,其他变量,函数等
标量定义的案例
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)为例。
说明变量的使用,看看如何编写。
*/
declare
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=&no;
--计算所得税
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字符的话,就会有错误,为了降低pl/sql程序
的维护工作量,可以使用%type属性定义变量,这样它会按照数据库列来确定你定义的
变量的类型和长度,
我们看看这个怎么使用:
标识符名 表名.列名%type;
*/
select * from emp;
--7654,ename有6个字符,而只限制了5个,会报错(字符串缓冲区太小)
--怎么解决呢?
declare
c_tax_rate number(3,2):=0.03;
--用户名
v_ename emp.ename%type;--可变的,安全,不会有溢出
v_sal emp.sal%type;
v_tax_sal number(7,2);
begin
--执行
select ename,sal into v_ename,v_sal from emp where empno=&no;
--计算所得税
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记录成员时,
必须要加记录变量作为前缀(记录变量,记录成员)如下:
declare
type emp_record_type is record(
name emp.ename%type,
salary emp.sal%type,
title emp.job%type);
sp_record emp_record_type;
begin
select ename,sal,job into sp_record
from emp where empno=7788;
dbms_output.put_line('员工名:'|| emp_record.name);
end;
*/
declare --声明
--定义一个pl/sql记录类型emp_record_type
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);
dbms_output.put_line('薪水:'|| sp_record.salary);
dbms_output.put_line('工作:'|| sp_record.title);
end;
/*
复合类型--pl/sql表
相当于高级语言中的数组,但是需要注意的是在高级语言中数组的下标
不能为负数,而pl/sql是可以为负数的,并且表元素的下标没有限制。
示例如下:
declare
type sp_table_type is table of emp.ename%type
index by binary_integer;
sp_table sp_table_type;
begin
select ename into sp_table(0) from emp where empno=7788;
dbms_output.put_line('员工名:'|| sp_table(0));
end;
说明:
sp_table_type 是pl/sql表类型
emp.ename%type 指定了表的元素的类型和长度
sp_table 为pl/sql表变量
sp_table(0) 则表示下标为0的元素
*/
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;
--定义了sp_table变量,类型是sp_table_type
sp_table sp_table_type;
begin
select ename into sp_table(0) from emp where empno=7788;
dbms_output.put_line('员工名:'|| sp_table(0));
end;
--sp_table(0)这里下标可以为-1,sp_table(-1)
--问题,如果是select ename into sp_table(0) from emp;
--就会显示:实际返回的行数超出请求的行数
--问题:如果把where条件去掉会怎样呢?
--使用参照变量
/*
参照变量
是指用于存放数值指针的变量,通过使用参照变量,可以使得应用程序共享相同对象,
从而降低占用的空间。在编写pl/sql程序时,可以使用游标变量(ref cursor)和对象
类型变量(ref obj_type)两种参照变量类型
参照变量--ref cursor游标变量
使用游标时,当定义游标时不需要指定相应的select语句,但是当使用游标时(open)
需要指定Select语句,这样一个游标就与一个Select语句结合了。实例如下:
1、请使用pl/sql编写一个块,可以输入部门号,并显示该部门所有员工姓名和他的工资。
2、在1的基础上,如果某个员工的工资低于200元,就增加100元。
*/
declare
--定义游标(定义类型)sp_emp_cursor
type sp_emp_cursor is ref cursor;
--定义一个游标变量
test_cursor sp_emp_cursor;
--定义变量
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
--执行
--把test_cursor和一个select结合
--open test_cursor for select ename,sal from emp;
open test_cursor for select ename,sal from emp where deptno=&no;
--循环取出
loop
fetch test_cursor into v_ename,v_sal;
--判断是否test_cursor为空
exit when test_cursor%notfound;
dbms_output.put_line('名字:'||v_ename||'薪水:'||v_sal);
end loop;
end;
/*
1、掌握pl/sql的高级用法(能编写分页过程模块,下订单过程模块。。)
2、会处理Oracle常见的例外
3、会编写Oracle各种触发器
4、理解视图的概念并能灵活使用视图
Pl/sql的进阶---控制结构
在任何计算机语言(c,java,pascal)都有各种控制语句(条件语句,循环结构
顺序控制结构。。)在pl/sql中也存在这样的控制结构。
在本部分学习完毕后,希望大家达到:
1、使用各种if语句
2、使用循环语句
3、使用控制语句——goto语句和 null ;
条件分支语句
pl/sql中提供了三种条件语句 if--then,if--then--else,if--then--elseif--else
这里我们可以和Java语句进行一个比较
简单的条件判断 if--then
?编写一个过程,可以输入一个雇员名,如果该雇员的工资低于2000,就给该雇员增加10%
*/
create or replace procedure sp_pro4(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*1.1 where ename=spName;
end if;
end;
select * from emp;
exec sp_pro4('SCOTT');
/*
二重条件分支 if--then--else
?编写一个过程,可以输入一个雇员名,如果该雇员的补助不是0就在原来的基础上增加100,
如果补助为0就把补助设为200;
*/
create or replace procedure sp_pro5(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;
end;
/*
多重条件分支 if--then--elsif--else
?编写一个过程,可以输入一个雇员编号,如果该雇员的职位是PRESIDENT
就给他的工资增加1000,如果该雇员的职位是MANAGER就给他的工资增加500,
其他职位的雇员增加200.
*/
create or replace procedure sp_pro5(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 job=job+1000 where empno=spNo;
elsif v_job='MANAGER' then
update emp set job=job+500 where empno=spNo;
else
update emp set job=job+200 where empno=spNo;
end if;
end;
/*
循环语句-loop
是pl/sql中最简单的循环语句,这种循环语句以loop开头,以end loop结尾
这种循环至少被执行一次。
案例:现有一张表users,表结构包括用户id,用户名
请编写一个过程,可输入用户名,并循环添加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;
end;
/*
循环语句-while循环
基础循环至少要执行循环体一次,而对于while循环来说,只有条件为true
时,才会执行循环体语句,while循环以while,loop开始,以end loop结束
案例:现有一张表users,表结构包括用户id,用户名
请编写一个过程,可输入用户名,并循环添加10个用户到users表中,
用户编号从11开始添加
*/
create or replace procedure sp_pro7(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;
end;
/*
循环语句 ---for循环
基本for循环的基本结构如下
begin
for i in reverse 1..10 loop
insert into users values(i,'顺平');
end loop;
end;
/
我们可以看到控制变量i,在隐含中就在不停的增加
*/
/*
顺序控制语句 ---goto,null
1、goto语句
goto语句用于跳转到特定标号去执行语句,注意由于使用goto语句会增加
程序的复杂性,并使得应用程序可以读性变差,所以在做一般应用开发时,
建议大家不要使用goto语句。
基本语法如下:goto lable,其中lable是已经定义好的标号名,
*/
declare
i int :=1;
begin
loop
dbms_output.put_line('输出i='||i);
if i=10 then
goto end_loop;
end if;
i := i+1;
end loop;
<<end_loop>>
dbms_output.put_line('循环结束');
end;
/*
顺序控制语句 ---goto,null
1、null语句
null语句不会执行任何操作,并且会直接将控制传递到下一条语句,使用null
语句的主要好处是可以提高pl/sql的可读性。
*/
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;
else
null;
end if;
end;
/*
编写分页过程
分页是任何一个网站(bbs,网上商城,blog)都会使用到的技术,
因而学习pl/sql编程开发就一定要掌握该技术。
无返回值的存储过程
古人云:欲速则不达,为了让大家比较容易接受分页过程编写,我还是从
简单到复杂,循序渐进的给大家讲解,首先是掌握最简单的存储过程,无返回值的
存储过程。
案例:现有一张表book,表结构为书号,书名,出版社
请编写一个过程,可以向book表添加书,要求通过java程序调用该过程。
*/
--1、建表
create table book(bookId number,bookName varchar2(50),publishHouse varchar2(50));
--2、编写过程
--in表示这是一个输入参数,默认为in,out表示一个输出参数
create or replace procedure sp_pro7(spBookId in number,spBookName in varchar2,
spPublishHouse in varchar2) is
begin
insert into book values(spBookId,spBookName,spPublishHouse);
end;
/
--在Java中调用
select * from book;
/*
try{
//1、加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//2、得到连接
Connection ct = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL", "scott", "scott");
System.out.println("数据连接成功");
//3、创建CallableStatement
CallableStatement cs = ct.prepareCall("{call sp_pro7(?,?,?)}");
//4、给?赋值
cs.setInt(1, 1);
cs.setString(2, "超人");
cs.setString(3, "清华大学");
//6、执行
cs.execute();
//7、关闭
cs.close();
ct.close();
}catch(Exception e){
e.printStackTrace();
}
*/
/*
有返回值的存储过程(非列表)
案例:编写一个过程,可以输入雇员的编号,返回该雇员的姓名。
案例扩展:编写一个过程,可以输入雇员的编号,返回该雇员的姓名,工资,岗位。
*/
create table book(bookId number,bookName varchar2(50),publishHouse varchar2(50));
--1、编写过程
--in表示这是一个输入参数,默认为in,out表示一个输出参数
create or replace procedure sp_pro8(spBookId in number,
spBookName out varchar2,
spPublishHouse out varchar2) is
begin
select bookName,publishHouse into spBookName,spPublishHouse from book
where bookId=spBookId;
end;
/
--2、在Java中调用
select * from book;
select * from emp;
/*
try{
//1、加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//2、得到连接
Connection ct = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL", "scott", "scott");
System.out.println("数据连接成功");
//3、创建CallableStatement
CallableStatement cs = ct.prepareCall("{call sp_pro8(?,?,?)}");
//4、给?赋值
cs.setInt(1, 1);
//5、注册输出值
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
cs.registerOutParameter(3, oracle.jdbc.OracleTypes.VARCHAR);
//6、执行
cs.execute();
//7、接收
String a=cs.getString(2);
String b=cs.getString(3);
System.out.println("函数调用的结果是:"+a+"和"+b);
//7、关闭
cs.close();
ct.close();
}catch(Exception e){
e.printStackTrace();
}
*/
/*
有返回值的存储过程(列表【结果集】)
案例:编写一个过程,输入部门号,返回该部门所有的雇员信息。对该题的分析
由于Oracle存储过程没有返回值,它的所有返回值都是通过out参数来
替代的,列表同样也不例外,但由于是集合,所以不能用一般的参数,
必须使用package了,所以要分两部分:
1、建一个包,如下:
create or replace package testpackage as
TYPE test_cursor is ref cursor;
end testpackage;
2、建立存储过程。如下:
create or replace procedure testc
(myno in number,p_cursor out testpackage.test_cursor) is
begin
open p_cursor for select * from emp where deptno=no;
end testc ;
3、如何在Java程序中调用
*/
--1、创建一个包,在该包中,我定义类型为test_cursor,是个游标
create or replace package testpackage as
TYPE test_cursor is ref cursor;
end testpackage;
--2、
create or replace procedure sp_pro9
(spNo in number,p_cursor out testpackage.test_cursor) is
begin
open p_cursor for select * from emp where deptno=spNo;
end;
--3、Java程序调用
/*
try{
//1、加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//2、得到连接
Connection ct = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL", "scott", "scott");
System.out.println("数据连接成功");
//3、创建CallableStatement
CallableStatement cs = ct.prepareCall("{call sp_pro9(?,?)}");
//4、给?赋值
cs.setInt(1, 30);
//5、注册输出值
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
//6、执行
cs.execute();
//7、接收
ResultSet rs =(ResultSet)cs.getObject(2);
while(rs.next()){
System.out.println(rs.getInt(1)+"...."+rs.getString(2)+"...."+rs.getString(3)+"...."+rs.getInt(4));
}
//7、关闭
cs.close();
ct.close();
}catch(Exception e){
e.printStackTrace();
}
}
*/
/*
编写分页过程
要求大家编写一个存储过程,要求可以输入表名,每页显示记录数,当前页,
返回总记录数,总页数,和返回的结果集
*/
select t1.*,rownum rn from (select * from emp) t1;
select t1.*,rownum rn from (select * from emp) t1 where rownum<=10;
--在分页时,大家可以把下面的sql语句当作是一个模板使用
select * from
(select t1.*,rownum rn from (select * from emp) t1 where rownum<=10)
where rn>=6;
--开发一个包
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;
--计算myPageCount
if mod(myrows,pagesize)=0 then
myPageCount:=myrows/pagesize;
else
myPageCount:=myrows/pagesize+1;
end if;
--关闭游标
--close p_cursor;
end;
--使用Java测试
--需要薪水由低到高
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
||' order by sal) 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;
--计算myPageCount
if mod(myrows,pagesize)=0 then
myPageCount:=myrows/pagesize;
else
myPageCount:=myrows/pagesize+1;
end if;
--关闭游标
--close p_cursor;
end;
--Java测试
/*
try{
//1、加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//2、得到连接
Connection ct = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL", "scott", "scott");
System.out.println("数据连接成功");
//3、创建CallableStatement
CallableStatement cs = ct.prepareCall("{call fenye(?,?,?,?,?,?)}");
//4、给?赋值
cs.setString(1, "emp");
cs.setInt(2, 5);
cs.setInt(3, 1);
//5、注册输出值
cs.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER);
cs.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER);
cs.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);
//6、执行
cs.execute();
//7、接收
System.out.println(cs.getInt(4));
System.out.println(cs.getInt(5));
ResultSet rs =(ResultSet)cs.getObject(6);
while(rs.next()){
System.out.println(rs.getInt(1)+"...."+rs.getString(2)+"...."+rs.getString(3)+"...."+rs.getInt(6));
}
//7、关闭
cs.close();
ct.close();
}catch(Exception e){
e.printStackTrace();
}
*/
/*分页*/
--编写过程
create or replace procedure fenyePro2
( v_in_table in varchar2,v_in_pagesize in number,
v_in_pagenow in number, v_out_result out pack1.my_cursor,v_out_rows out number
v_out_pagecount out number) is
--定义变量
v_sql varchar2(2000);
v_start number;
v_end number;
begin
--执行代码
--回忆分页语句
--计算v-start 和 v_end 是多少
v_start:= v_in_pagesize*(v_in_pagenow-1)+1;
v_end:= v_in_pagesize*v_in_pagenow;
v_sql:= 'select t2.* from (select t1.* , rownum rn from (select * from ' || v_in_table || ')
t1 where rownum<=' || v_end || ') t2 where rn>= ' || v_start;
--打开游标,让游标指向结果集
open v_out_result for v_sql;
--查询共有多少条记录
select count(*) into v_out_rows from emp;
if mod(v_out_rows,v_in_pagesize)=0 then
v_out_pagecount:= v_out_rows/v_in_pagesize;
else
v_out_pagecount:= v_out_rows/v_in_pagesize+1;
end if;
end;
/*视图*/
视图是一个虚拟表,其内容由查询定义。同真实的表一样,视图包含一系列带有名称的列和行数据。但是,
视图并不在数据库中以存储的数据集形式存在。行和列数据来自定义视图的查询所引用的表,并且在引用视图时动态生成。
视图是Oracle的又一种数据对象,为了简化操作,提高安全,满足不同用户的查询需求。
视图不是一个真正存在的物理表,不存在于磁盘,它是根据别的表动态生成的。(可以只是别的表的一部分字段)
基本语法:
create view 视图名 as select语句[with read only]
create view empview as select empno,ename,job from emp;
不过需要进行权限分配,默认可以创建视图,可能因为角色被删除,该角色具有创建视图权限
SQL> grant create view to scott;即可
说明:如果我们创建视图的时候,我们带来with read only,则表示该视图只能读,而不能进行其他操作,如果没有带则表示可以进行其他操作。
如果创建视图的时候,不希望用户通过视图进行其他操作,则建议带上with read only
视图为什么可以简化我们查询的操作?
我们希望查询雇员的名字和部门的名称。--传统两张表
视图简化:
create or replace view myview as select emp.ename,dept.deptno,dept.dname from emp,dept where emp.deptno=dept.deptno with read only;
查询:select * from myview;即可。
删除视图:drop view myview;
视图与表的区别:
1、表需要占据磁盘空间,视图不需要
2、视图不能添加索引
3、使用视图可以简化 复杂查询
4、视图有利于提高安全性(比如不用用户查看不同视图)
--函数用于返回特定的数据,当建立函数时,在函数头部必须包含return子句,
--而在函数体内必须包含return语句返回的数据。
--我们可以使用create function 来建立函数,实际案例:
--函数案例
--输入雇员的姓名,返回该雇员的年薪
create function sp_fun(spName varchar2) return
number is yearSal number(7,2);
begin
--执行部分
select sal*12+nvl(comm,0)*12 into yearSal from emp where ename=spName;
return yearSal;
end;
/
--在sqlplus中调用函数
--sql>var income number
--sql>call annual_income('SCOTT') into:income;
--sql>print income
--同样我们可以在Java程序中调用该函数
--select annual_income('SCOTT') from dual;//这样
--可以通过rs.getInt(1)得到返回的结果
var income number;
call sp_fun('SCOTT') into:income;
--java程序调用函数
/*
try{
//1、加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//2、得到连接
Connection ct = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL", "scott", "hyd123HYD");
System.out.println("数据连接成功");
//3、创建CallableStatement
CallableStatement cs = ct.prepareCall("{?=call sp_fun(?)}");
//4、给?赋值
cs.setString(2, "SCOTT");
//5、给输出值加入类型
cs.registerOutParameter(1, java.sql.Types.FLOAT);
//6、执行
cs.execute();
//7、接收
int a=cs.getInt(1);
System.out.println("函数调用的结果是:"+a);
//7、关闭
cs.close();
ct.close();
}catch(Exception e){
e.printStackTrace();
}
*/
/*
包
包用于在逻辑上组合过程和函数,它由包规范和包体两部分组成。
1、我们可以使用create package来创建包:
实例:
create package sp_package is
procedure update_sal(name varchar2,newsal number);
function annual_income(name varchar2) return number;
end;
包的规范只包含了过程和函数的说明,但是没有过程和函数的实现代码,
包体用于实现包规范中的过程和函数。
2、建立包体可以使用 create package body 命令
*/
create package sp_package is
procedure update_sal(name varchar2,newsal number); --声明该包有一个过程和函数
function annual_income(name varchar2) return number;
end;
--建立包体
create or replace package body sp_package is
procedure update_sal(name varchar2,newsal number)
is
begin
update emp set sal=newsal where ename=name;
end;
function annual_income(name varchar2)
return number is
annual_salary number;
begin
select sal*12+nvl(comm,0) into annual_salary from emp where ename=name;
return annual_salary;
end;
end;
/*
如何调用包的过程或是函数
当调用包的过程或是函数时,在过程和函数前需要带有包名,如果要访问其他方案的包,
还需要在包名前加方案名。
如:SQL>call sp_package.update_sal('SCOTT',1500);
特别说明
包是pl/sql中非常重要的部分,我们在使用过程分页时,会更能体会
*/
call sp_package.update_sal('SCOTT',1500);
select * from emp where ename='SCOTT';
/*
触发器是指隐含的执行的存储过程。当定义触发器时,必须要指定触发的事件
和触发的操作,常用的触发事件包括insert,update,delete语句,而触发操作
实际上是一个pl/sql块。可以使用create trigger来建立触发器。
触发器内容多,一般不会主动去调用,就是指用户登录触发一件事情,或者用户动了另一张表
去修改另外一张表,我们就可以得到主外键,一个外键删除就把主键也删除,联动的触发。
*/
/*
在编写pl/sql程序时,可以定义变量和常量;
在pl/sql程序中包括有:
1、标量类型(scalar)
2、复合类型(composite)
3、参照类型(reference)
4、lob(large object)
标量(scalar)-常用类型
在编写pl/sql块时,如果要使用变量,需在定义部分定义变量。
pl/sql中定义变量和常量的语法如下:
identifier [constant] datatype [not null] [:=| default expr]
identifier:指定常量。需要指定它的初始值,且其值是不能改变的
datatype:数据类型
not null:指定变量值不能为null
:= 给变量或是常量指定初始值
default 用于指定初始值
expr 指定初始值的pl/sql表达式,可是文本值,其他变量,函数等
标量定义的案例
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)为例。
说明变量的使用,看看如何编写。
*/
declare
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=&no;
--计算所得税
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字符的话,就会有错误,为了降低pl/sql程序
的维护工作量,可以使用%type属性定义变量,这样它会按照数据库列来确定你定义的
变量的类型和长度,
我们看看这个怎么使用:
标识符名 表名.列名%type;
*/
select * from emp;
--7654,ename有6个字符,而只限制了5个,会报错(字符串缓冲区太小)
--怎么解决呢?
declare
c_tax_rate number(3,2):=0.03;
--用户名
v_ename emp.ename%type;--可变的,安全,不会有溢出
v_sal emp.sal%type;
v_tax_sal number(7,2);
begin
--执行
select ename,sal into v_ename,v_sal from emp where empno=&no;
--计算所得税
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记录成员时,
必须要加记录变量作为前缀(记录变量,记录成员)如下:
declare
type emp_record_type is record(
name emp.ename%type,
salary emp.sal%type,
title emp.job%type);
sp_record emp_record_type;
begin
select ename,sal,job into sp_record
from emp where empno=7788;
dbms_output.put_line('员工名:'|| emp_record.name);
end;
*/
declare --声明
--定义一个pl/sql记录类型emp_record_type
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);
dbms_output.put_line('薪水:'|| sp_record.salary);
dbms_output.put_line('工作:'|| sp_record.title);
end;
/*
复合类型--pl/sql表
相当于高级语言中的数组,但是需要注意的是在高级语言中数组的下标
不能为负数,而pl/sql是可以为负数的,并且表元素的下标没有限制。
示例如下:
declare
type sp_table_type is table of emp.ename%type
index by binary_integer;
sp_table sp_table_type;
begin
select ename into sp_table(0) from emp where empno=7788;
dbms_output.put_line('员工名:'|| sp_table(0));
end;
说明:
sp_table_type 是pl/sql表类型
emp.ename%type 指定了表的元素的类型和长度
sp_table 为pl/sql表变量
sp_table(0) 则表示下标为0的元素
*/
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;
--定义了sp_table变量,类型是sp_table_type
sp_table sp_table_type;
begin
select ename into sp_table(0) from emp where empno=7788;
dbms_output.put_line('员工名:'|| sp_table(0));
end;
--sp_table(0)这里下标可以为-1,sp_table(-1)
--问题,如果是select ename into sp_table(0) from emp;
--就会显示:实际返回的行数超出请求的行数
--问题:如果把where条件去掉会怎样呢?
--使用参照变量
/*
参照变量
是指用于存放数值指针的变量,通过使用参照变量,可以使得应用程序共享相同对象,
从而降低占用的空间。在编写pl/sql程序时,可以使用游标变量(ref cursor)和对象
类型变量(ref obj_type)两种参照变量类型
参照变量--ref cursor游标变量
使用游标时,当定义游标时不需要指定相应的select语句,但是当使用游标时(open)
需要指定Select语句,这样一个游标就与一个Select语句结合了。实例如下:
1、请使用pl/sql编写一个块,可以输入部门号,并显示该部门所有员工姓名和他的工资。
2、在1的基础上,如果某个员工的工资低于200元,就增加100元。
*/
declare
--定义游标(定义类型)sp_emp_cursor
type sp_emp_cursor is ref cursor;
--定义一个游标变量
test_cursor sp_emp_cursor;
--定义变量
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
--执行
--把test_cursor和一个select结合
--open test_cursor for select ename,sal from emp;
open test_cursor for select ename,sal from emp where deptno=&no;
--循环取出
loop
fetch test_cursor into v_ename,v_sal;
--判断是否test_cursor为空
exit when test_cursor%notfound;
dbms_output.put_line('名字:'||v_ename||'薪水:'||v_sal);
end loop;
end;
/*
1、掌握pl/sql的高级用法(能编写分页过程模块,下订单过程模块。。)
2、会处理Oracle常见的例外
3、会编写Oracle各种触发器
4、理解视图的概念并能灵活使用视图
Pl/sql的进阶---控制结构
在任何计算机语言(c,java,pascal)都有各种控制语句(条件语句,循环结构
顺序控制结构。。)在pl/sql中也存在这样的控制结构。
在本部分学习完毕后,希望大家达到:
1、使用各种if语句
2、使用循环语句
3、使用控制语句——goto语句和 null ;
条件分支语句
pl/sql中提供了三种条件语句 if--then,if--then--else,if--then--elseif--else
这里我们可以和Java语句进行一个比较
简单的条件判断 if--then
?编写一个过程,可以输入一个雇员名,如果该雇员的工资低于2000,就给该雇员增加10%
*/
create or replace procedure sp_pro4(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*1.1 where ename=spName;
end if;
end;
select * from emp;
exec sp_pro4('SCOTT');
/*
二重条件分支 if--then--else
?编写一个过程,可以输入一个雇员名,如果该雇员的补助不是0就在原来的基础上增加100,
如果补助为0就把补助设为200;
*/
create or replace procedure sp_pro5(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;
end;
/*
多重条件分支 if--then--elsif--else
?编写一个过程,可以输入一个雇员编号,如果该雇员的职位是PRESIDENT
就给他的工资增加1000,如果该雇员的职位是MANAGER就给他的工资增加500,
其他职位的雇员增加200.
*/
create or replace procedure sp_pro5(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 job=job+1000 where empno=spNo;
elsif v_job='MANAGER' then
update emp set job=job+500 where empno=spNo;
else
update emp set job=job+200 where empno=spNo;
end if;
end;
/*
循环语句-loop
是pl/sql中最简单的循环语句,这种循环语句以loop开头,以end loop结尾
这种循环至少被执行一次。
案例:现有一张表users,表结构包括用户id,用户名
请编写一个过程,可输入用户名,并循环添加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;
end;
/*
循环语句-while循环
基础循环至少要执行循环体一次,而对于while循环来说,只有条件为true
时,才会执行循环体语句,while循环以while,loop开始,以end loop结束
案例:现有一张表users,表结构包括用户id,用户名
请编写一个过程,可输入用户名,并循环添加10个用户到users表中,
用户编号从11开始添加
*/
create or replace procedure sp_pro7(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;
end;
/*
循环语句 ---for循环
基本for循环的基本结构如下
begin
for i in reverse 1..10 loop
insert into users values(i,'顺平');
end loop;
end;
/
我们可以看到控制变量i,在隐含中就在不停的增加
*/
/*
顺序控制语句 ---goto,null
1、goto语句
goto语句用于跳转到特定标号去执行语句,注意由于使用goto语句会增加
程序的复杂性,并使得应用程序可以读性变差,所以在做一般应用开发时,
建议大家不要使用goto语句。
基本语法如下:goto lable,其中lable是已经定义好的标号名,
*/
declare
i int :=1;
begin
loop
dbms_output.put_line('输出i='||i);
if i=10 then
goto end_loop;
end if;
i := i+1;
end loop;
<<end_loop>>
dbms_output.put_line('循环结束');
end;
/*
顺序控制语句 ---goto,null
1、null语句
null语句不会执行任何操作,并且会直接将控制传递到下一条语句,使用null
语句的主要好处是可以提高pl/sql的可读性。
*/
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;
else
null;
end if;
end;
/*
编写分页过程
分页是任何一个网站(bbs,网上商城,blog)都会使用到的技术,
因而学习pl/sql编程开发就一定要掌握该技术。
无返回值的存储过程
古人云:欲速则不达,为了让大家比较容易接受分页过程编写,我还是从
简单到复杂,循序渐进的给大家讲解,首先是掌握最简单的存储过程,无返回值的
存储过程。
案例:现有一张表book,表结构为书号,书名,出版社
请编写一个过程,可以向book表添加书,要求通过java程序调用该过程。
*/
--1、建表
create table book(bookId number,bookName varchar2(50),publishHouse varchar2(50));
--2、编写过程
--in表示这是一个输入参数,默认为in,out表示一个输出参数
create or replace procedure sp_pro7(spBookId in number,spBookName in varchar2,
spPublishHouse in varchar2) is
begin
insert into book values(spBookId,spBookName,spPublishHouse);
end;
/
--在Java中调用
select * from book;
/*
try{
//1、加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//2、得到连接
Connection ct = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL", "scott", "scott");
System.out.println("数据连接成功");
//3、创建CallableStatement
CallableStatement cs = ct.prepareCall("{call sp_pro7(?,?,?)}");
//4、给?赋值
cs.setInt(1, 1);
cs.setString(2, "超人");
cs.setString(3, "清华大学");
//6、执行
cs.execute();
//7、关闭
cs.close();
ct.close();
}catch(Exception e){
e.printStackTrace();
}
*/
/*
有返回值的存储过程(非列表)
案例:编写一个过程,可以输入雇员的编号,返回该雇员的姓名。
案例扩展:编写一个过程,可以输入雇员的编号,返回该雇员的姓名,工资,岗位。
*/
create table book(bookId number,bookName varchar2(50),publishHouse varchar2(50));
--1、编写过程
--in表示这是一个输入参数,默认为in,out表示一个输出参数
create or replace procedure sp_pro8(spBookId in number,
spBookName out varchar2,
spPublishHouse out varchar2) is
begin
select bookName,publishHouse into spBookName,spPublishHouse from book
where bookId=spBookId;
end;
/
--2、在Java中调用
select * from book;
select * from emp;
/*
try{
//1、加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//2、得到连接
Connection ct = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL", "scott", "scott");
System.out.println("数据连接成功");
//3、创建CallableStatement
CallableStatement cs = ct.prepareCall("{call sp_pro8(?,?,?)}");
//4、给?赋值
cs.setInt(1, 1);
//5、注册输出值
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
cs.registerOutParameter(3, oracle.jdbc.OracleTypes.VARCHAR);
//6、执行
cs.execute();
//7、接收
String a=cs.getString(2);
String b=cs.getString(3);
System.out.println("函数调用的结果是:"+a+"和"+b);
//7、关闭
cs.close();
ct.close();
}catch(Exception e){
e.printStackTrace();
}
*/
/*
有返回值的存储过程(列表【结果集】)
案例:编写一个过程,输入部门号,返回该部门所有的雇员信息。对该题的分析
由于Oracle存储过程没有返回值,它的所有返回值都是通过out参数来
替代的,列表同样也不例外,但由于是集合,所以不能用一般的参数,
必须使用package了,所以要分两部分:
1、建一个包,如下:
create or replace package testpackage as
TYPE test_cursor is ref cursor;
end testpackage;
2、建立存储过程。如下:
create or replace procedure testc
(myno in number,p_cursor out testpackage.test_cursor) is
begin
open p_cursor for select * from emp where deptno=no;
end testc ;
3、如何在Java程序中调用
*/
--1、创建一个包,在该包中,我定义类型为test_cursor,是个游标
create or replace package testpackage as
TYPE test_cursor is ref cursor;
end testpackage;
--2、
create or replace procedure sp_pro9
(spNo in number,p_cursor out testpackage.test_cursor) is
begin
open p_cursor for select * from emp where deptno=spNo;
end;
--3、Java程序调用
/*
try{
//1、加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//2、得到连接
Connection ct = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL", "scott", "scott");
System.out.println("数据连接成功");
//3、创建CallableStatement
CallableStatement cs = ct.prepareCall("{call sp_pro9(?,?)}");
//4、给?赋值
cs.setInt(1, 30);
//5、注册输出值
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
//6、执行
cs.execute();
//7、接收
ResultSet rs =(ResultSet)cs.getObject(2);
while(rs.next()){
System.out.println(rs.getInt(1)+"...."+rs.getString(2)+"...."+rs.getString(3)+"...."+rs.getInt(4));
}
//7、关闭
cs.close();
ct.close();
}catch(Exception e){
e.printStackTrace();
}
}
*/
/*
编写分页过程
要求大家编写一个存储过程,要求可以输入表名,每页显示记录数,当前页,
返回总记录数,总页数,和返回的结果集
*/
select t1.*,rownum rn from (select * from emp) t1;
select t1.*,rownum rn from (select * from emp) t1 where rownum<=10;
--在分页时,大家可以把下面的sql语句当作是一个模板使用
select * from
(select t1.*,rownum rn from (select * from emp) t1 where rownum<=10)
where rn>=6;
--开发一个包
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;
--计算myPageCount
if mod(myrows,pagesize)=0 then
myPageCount:=myrows/pagesize;
else
myPageCount:=myrows/pagesize+1;
end if;
--关闭游标
--close p_cursor;
end;
--使用Java测试
--需要薪水由低到高
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
||' order by sal) 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;
--计算myPageCount
if mod(myrows,pagesize)=0 then
myPageCount:=myrows/pagesize;
else
myPageCount:=myrows/pagesize+1;
end if;
--关闭游标
--close p_cursor;
end;
--Java测试
/*
try{
//1、加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//2、得到连接
Connection ct = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL", "scott", "scott");
System.out.println("数据连接成功");
//3、创建CallableStatement
CallableStatement cs = ct.prepareCall("{call fenye(?,?,?,?,?,?)}");
//4、给?赋值
cs.setString(1, "emp");
cs.setInt(2, 5);
cs.setInt(3, 1);
//5、注册输出值
cs.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER);
cs.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER);
cs.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);
//6、执行
cs.execute();
//7、接收
System.out.println(cs.getInt(4));
System.out.println(cs.getInt(5));
ResultSet rs =(ResultSet)cs.getObject(6);
while(rs.next()){
System.out.println(rs.getInt(1)+"...."+rs.getString(2)+"...."+rs.getString(3)+"...."+rs.getInt(6));
}
//7、关闭
cs.close();
ct.close();
}catch(Exception e){
e.printStackTrace();
}
*/
/*分页*/
--编写过程
create or replace procedure fenyePro2
( v_in_table in varchar2,v_in_pagesize in number,
v_in_pagenow in number, v_out_result out pack1.my_cursor,v_out_rows out number
v_out_pagecount out number) is
--定义变量
v_sql varchar2(2000);
v_start number;
v_end number;
begin
--执行代码
--回忆分页语句
--计算v-start 和 v_end 是多少
v_start:= v_in_pagesize*(v_in_pagenow-1)+1;
v_end:= v_in_pagesize*v_in_pagenow;
v_sql:= 'select t2.* from (select t1.* , rownum rn from (select * from ' || v_in_table || ')
t1 where rownum<=' || v_end || ') t2 where rn>= ' || v_start;
--打开游标,让游标指向结果集
open v_out_result for v_sql;
--查询共有多少条记录
select count(*) into v_out_rows from emp;
if mod(v_out_rows,v_in_pagesize)=0 then
v_out_pagecount:= v_out_rows/v_in_pagesize;
else
v_out_pagecount:= v_out_rows/v_in_pagesize+1;
end if;
end;
/*视图*/
视图是一个虚拟表,其内容由查询定义。同真实的表一样,视图包含一系列带有名称的列和行数据。但是,
视图并不在数据库中以存储的数据集形式存在。行和列数据来自定义视图的查询所引用的表,并且在引用视图时动态生成。
视图是Oracle的又一种数据对象,为了简化操作,提高安全,满足不同用户的查询需求。
视图不是一个真正存在的物理表,不存在于磁盘,它是根据别的表动态生成的。(可以只是别的表的一部分字段)
基本语法:
create view 视图名 as select语句[with read only]
create view empview as select empno,ename,job from emp;
不过需要进行权限分配,默认可以创建视图,可能因为角色被删除,该角色具有创建视图权限
SQL> grant create view to scott;即可
说明:如果我们创建视图的时候,我们带来with read only,则表示该视图只能读,而不能进行其他操作,如果没有带则表示可以进行其他操作。
如果创建视图的时候,不希望用户通过视图进行其他操作,则建议带上with read only
视图为什么可以简化我们查询的操作?
我们希望查询雇员的名字和部门的名称。--传统两张表
视图简化:
create or replace view myview as select emp.ename,dept.deptno,dept.dname from emp,dept where emp.deptno=dept.deptno with read only;
查询:select * from myview;即可。
删除视图:drop view myview;
视图与表的区别:
1、表需要占据磁盘空间,视图不需要
2、视图不能添加索引
3、使用视图可以简化 复杂查询
4、视图有利于提高安全性(比如不用用户查看不同视图)