oracle视频学习(三)

38度秋裤
于 2014-12-07 11:47:01 发布
阅读量643
点赞数
本文链接：https://blog.csdn.net/u010452908/article/details/41786473
版权
触发器是隐含的执行存储过程。当定义触发器时候，必须指定触发的事件
和触发的动作，而触发事件包括insert,update,delete语句，而触发器实际就
是一个Pl/sql块，可以使用create tigger来建立触发器
 
 触发器是非常有用的,可以维护数据库的安全和一致性,类似监听器功能

 在编写pl/sql程序时候,可以定义变量和常量;在pl/sql程序中包括有：
 1: 标量类型 scalar
 2: 符合类型 composite
 3: 参照类型 reference
 4: lob large object

标量scalar -----使用标量
 在定义好变量后,就可以使用这些变量,这里需要说明的是pl/sql块
为变量赋值不同于其它编程语言,需要在等号前加冒号(:=)

标量的定义
 1,定义一个变长字符串
 v_ename varchar2(10);
 
  定义一个小数 
 v_sal number(6,2);
   定义一个小数并且给一个初始值5.4 :=是pl/sql的赋值号
 v_sal number(6.2):=5.4;
 定义一个日期类型的数据
 v_hiredate date;
 定义一个布尔变量 不能为空 初始值是FALSE
 v_valid boolean not null default false;

 输入员工号 显示雇员姓名 工资 个人所得税 为例子,说明变量的使用
 
declare
c_tax_rate number(3,2):=0.03;
v_ename varchar
-----用户名
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*v_tax_rate; (///赋值的时候一定要是:=)
----输出
dbms_output.put_line('姓名是:'||v_name||'工资:'||v_sal||'交税')
end;
/
标量scalar---使用%type类型:
v_ename varchar2(5);
对于上面的pl/sql块有一个问题:就是如果员工的姓名超过了5个字符的话,就会有错误
为了降低pl/sql程序的维护工作量,可以确定使用%type属性定义标量,这样它会按照数据库
列来确定你定义的变量的类型和长度.
怎么使用:
标识符名 表名.列名%type;
v_ename emp.ename%type; (这个类型和表emp表的ename这个字段的数据类型一摸一样)
v_sal emp.sal%type;

符合变量 composite ---介绍
用于存放多个值的变量,主要包括这几种:
pl/sql 记录
pl/sql表
嵌套表
varray
 
复合类型---pl/sql记录
类似与高级语言中的结构体,需要特别注意的是,当应用pl/sql记录成员时候,必须要加
记录变量作为前缀(记录变量.记录成员)如下:
记录类型,结构体类型;
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_outoput.put_line('员工名:'||sp_record.name);
end;
)
符合类型-pl/sql表
相当于高级语言中的数组,但是需要注意的是在高级语言中数组的下标不能为
负数,而pl/sql是可以为负数的,并且表元素的下标没有限制
declare
---定义了一个pl/sql表类型sp_table_type，该类型是用于存放emp.ename%type
type sp_table_type is table of emp.ename%type
---表的下标是整数
index by binary_integer;
---定义了一个变量,是sp_table_type数据类型;
sp_table sp_table_type;
begin
//如果把where条件去掉会怎样? 使用参照变量
select ename into sp_table(0) frome 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的元素
 
参照变量介绍
参照变量是指用于存放数值指针的变量,通过使用参照变量,可以使得应用程序共享
相同的对象,从而降低占用的空间,在编写pl/sql程序时候,可以使用游标变量ref cursor
和对象类型变量ref obj_type两种参照变量类型;
游标就是一个指针来回的指来回的取值;
--------------------------
参照变量 --ref cursor游标变量
当使用游标的时候,当定义游标时候不需要指定相应的select语句,但是当使用游标时候(open)
需要指定select语句,这样一个游标就与一个select语句结合了:
请使用pl/sql编写一个块,可以输入部门号,并且显示该部门所有员工姓名和他的工资
在上要求基础上,如果某个员工的工资低于200元,就增加100;

declare
---定义一个游标类型sp_emp_cursor
type sp_emp_cursor is ref cursor;
---定义一个游标变量test_cursor 是sp_emp_cursor类型的
test_cursor sp_emp_cursor;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
----执行
----把test_cursor和一个select结合
----把游标指向select后的结果集
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;
---关闭游标
  close test_cursor;
end;
/

-----------------------------------
declare
---定义一个游标类型sp_emp_cursor
type sp_emp_cursor is ref cursor;
---定义一个游标变量test_cursor 是sp_emp_cursor类型的
test_cursor sp_emp_cursor;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
----执行
----把test_cursor和一个select结合
----把游标指向select后的结果集
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;
---关闭游标
  close test_cursor;
end;
/
 
 
  oracle的视图
  Oracle的视图 oracle的触发器

  讲oracle不讲视图是不可能的
  会编写oracle各种触发器

pl/sql 介绍
在任何计算机语言(c,java,pascal)都有各种控制语句
(条件语句,循环语句,顺序控制结构)在pl/sql中也存在这样的控制结构
 使用各种if语句
 使用循环语句
 使用控制语句 ---goto和null

 条件分支语句
 pl/sql提供了三种条件分支语句
if -then  
//if  -then -else 
//if --then --elseif ---else
简单的条件判断 if--then
编写一个过程,可以输入一个雇员名,如果该雇员的工资低于2000，就给该雇员工资增加10%
create or replace procedure sp_pro(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*10% where ename=spName;
end if; //如果没有end if 相对于上面的if后的语句没有结束,显然是错误的
end;

exec sp_pro('SCOTT');
-------------------------------------------

declare
---定义一个游标类型sp_emp_cursor
type sp_emp_cursor is ref cursor;
---定义一个游标变量test_cursor 是sp_emp_cursor类型的
test_cursor sp_emp_cursor;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
----执行
----把test_cursor和一个select结合
----把游标指向select后的结果集
open test_cursor for select ename,sal from emp where deptno=&no;
---循环取出数据
loop 
  fetch test_cursor into v_ename,v_sal //循环一次赋予变量一次值
  ----判断是否test_cursor为空,指向的地址为空,取不出值
  ----判断工资高低是否加工资;
  if v_ename<2000 then
  update emp set sal=sal+sal*10%;
  end if;
  exit when test_cursor%notfound;
  dbms_output.put_line('名字:'||v_ename||'工资:'||v_sal);
end loop;
---关闭游标
  close test_cursor;
end;
/
------------------------------
二重分支,if--then--else
编写一个过程,可以输入一个雇员名,如果该雇员的补助不是0就在原来的基础上增加
100,如果补助为0就把补助设为200;
 
create or replace procedure sp_pro(spName varchar2) is//参数只写类型就可以了
---定义
v_comm emp.comm%type;
begin
---执行
select comm into v_comm from emp where ename=spName;
---判断
if v_comm<>0 then  //pl/sql中不相等用<>表示
if v_sal<2000 then 
update emp set comm =comm+100 where ename=spName; 
else
update emp set comm=comm+200 where ename=spName;
end if; //如果没有end if 相对于上面的if后的语句没有结束,显然是错误的
end;

exec sp_pro('SCOTT');

-----------------------------------------------------------
多重条件分支if--then--elseif--else
编写一个过程,可以输入一个雇员标号,如果该雇员的职位是president就给他工资
增加1000,如果该雇员的职位是manager就给他工资增加500,其他的雇员工资增加200
create or replace procedure sp_pro(spNo number) is  //和字段的数据类型一致
---定义
v_job emp.job%type;
begin
---执行
select job into v_job from emp where empno=spo;
if v_job='PRESIDENT' then
update emp set sal=sal+1000 where empno=spNo;
elsif v_job='MANAGER' then  //看清楚是elsif而不是elseif
update emp set sal=sal+500 where empno=spNo;
else 
update emp set sal=sal+200 where empno=spNo;
end if; //当然有if一定要记得有endif;
end;
----------------------------------------------------
循环语句 --loop
 是pl/sql中最简单的循环语句,这种循环语句以loop开头,
以end loop结尾,这种循环至少会被执行一次;
 请编写一个过程,可以输入用户名,并且循环添加10个用户到
users表中
create or replace procedure sp_pro(spName varchar2) is 
--定义部分
v_num number:=1; //初始化值为1,赋值是:=表示赋值
begin
loop
  insert into users values(v_num,spName);
  --判断是否要退出循环
  exit when v_num=10;
  ---自增
  v_num:=v_num+1;
end loop;
end;
exec sp_pro('rufus'); //循环添加10条数据

create table users(userNo number(2),userName varchar2(10));
------------------------------------------------------------------
循环语句 -while循环
 基本循环(loop)至少要执行循环体一次,而对于while循环来说,只要条件true时候,
才会执行循环体语句,while循环while...loop开始,以end loop结束
 编写一个过程,可以输入用户名,并循环添加10个用户到users表中,用户编号从11开始
create or replace procedure sp_pro(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;
exec sp_pro('rufus'); //循环添加10条数据
create table users(userNo number(2),userName varchar2(10));
----------------------------------------------------------------------
循环语句 -for循环
 基本for循环的基本结构如下
 begin 
   for i in reverse 1..10 loop
   insert into users values(i,'rufus');
   end loop;
 end;
 /
----我们可以看到控制变量i，在隐含中就不停的增加
-------------------------------------------------------------
顺序控制语句 -goto,null
goto语句
 goto语句用于跳转到特定标号去执行语句,注意由于使用goto语句会增加
程序的复杂性,并且使得应用程序的可读性变差,所以少用;
基本语法如下goto lable，其中label是已经定义好的标号名
declare 
i int:=1;
begin 
 loop
 dbms_output.put_lien('输出i='||i);
 if i=10 then
 goto end_loop;
 end if;
 i:=i+1;
 end loop;
 <<end_loop>>
 dbms_output.put_line('循环结束');
 end;
 
 
  oracle将例外分为预定义例外,非预定义例外,自定义例外三种:
  1)编写一个过程,可以接受雇员的编号,并显示该雇员的姓名
    问题是,如果输入的雇员编号不存在,怎样去处理呢?
    declare
    v_ename emp.ename%type;
    begin
    select ename into v_ename from emp where empno=&no;
    dbms_output.put_line('name:'||v_ename); 
    exception
        when no_data_found then //认为很常见的异常,预定义
        dbms_output.put_line('编号没有');
    end;
 pl/sql为开发人员提供了二十多个例外;
 
 case_no_found  :多条件分支不全面
 cursor_already_open :游标已经打开异常
 dup_val_on_index :
 invalid_cursor:
 invalid_number: 输入的数据有错误
 no_date_found
 too_many_rows; 如果返回超过一行,则会触发例外;
 zero_divide; 当执行除数为0会触发例外;
 value_error:
视图:视图是一个虚拟表,其内容是由查询定义,同真实表的表是一样的
视图包括一系列带有名称的列和行数据,但是,视图并不是在数据库中以存储
的数据值集形式存在,行和列数据来自定义视图的查询所引用的表,并且在引用
视图时候动态生成;
emp dept
1) 如果要显示各个雇员的名字和他所在部门的名称,必须用两张表. emp,dept
2) 假设管理员创建了一个用户Rufus,可以查询sal<1000 有哪些雇员?
   视图就是从emp表中取出一些数据,从dept表中取出一些数据构成一张新的数据表
   使用视图可以简化 复杂查询  视图可以利于提高安全性;

 oracle视图
 crate view 视图名 as select 语句 [with read onyl]
 创建或修改视图
 create or replace view 视图名 as select 语句[ with read only]
 删除视图
 drop view 视图名
 create view myview as select from emp where sal<1000;
 select * from myview;
 

触发器的设计
1) 触发器概述,分类
2) 事务概述
3) 行级触发器工作原理
4) 行级触发器的设计应用
5) 语句级触发器的设计应用
6) 替代触发器应用
什么是事务:
事务是数据库中重要的机制,用于确保数据完整性和并发处理的能力,
它将一条/一组sql语句当做成一个逻辑上的单元,用于保障这些语句都
成功或是失败.
事务特性
事务具有acid四个特性 (酸的特性)
A atomicity原子性
c consistency一致性
I isolation 隔离性
d durability永久性
隔离性和并发处理的能力成反比
SQLPLUS " / as sysdba"
比如在Oracle中一个界面删除某张表的全部数据,用另一张表查看数据表中的数据全部都在
因为oracle中的操作不是自动提交的.
select * from scott.emp; //存在视图的概念,删除不提交,在自己的界面看到删除后的结果
只有真真提交后才完成对数据库的修改;
通过事务的隔离性来解决问题的,我参看的时候采取隔离,你就不能对数据进行更新操作;

还有三张票,你要卖两张,我也要卖两张.但是我仅仅是查看你是可以对数据表进行更新操作的
但是我做一个动作你就无法操作跟新了,等我提交后你才能进行更新操作:
select * from books for update;
此时对方是无法修改了,我没有操作完成,你是操作不了表的数据的,这就是事务隔离性;
这是采用了锁的机制的，当我在操作时候,我是可以对数据表进行更新操作的;
通过加锁就是为了解决并发操作出现些问题;
update books set book_name='aa' where booK_id ='0008';
commit;
对同样一个事务而言,一旦发生就永久了,成了具体的物理文件了,无法挽回了.
------------------------------------------------------------------------------------

  行级触发器,是针对增,删,改操作语句起作用的;
  参照完整性: 例如员工表,部门表: 很简单的每一个员工肯定对应一个部门,如果
  某个员工所对应的部门号在部门表中不存在,那么这个员工就毫无意义;
  怎样能实现这样一种的情况,部门表中的部门号变了,员工表中员工对应的部门号也相应改变，
  如果变了,不能级联改变,这数据表就没有意义了;

  //下面是一个行级触发器
  create or replace trigger del_deptid
  after delete on deptment
  for each row
  begin
  delete from emp where id=:old.id;
  end del_dept;
  /
  trigger created;
  我们来测试行级触发器
  delete from deptment where id='01';

   
  create or replace trigger del_deptid
  after delete on dept
  for each row
  begin
  delete from emp where empno=:old.empno;
  end del_deptid;
  /
--------------------------------------------------我们操作scott.emp表和scott.dept表
SQL> create or replace trigger del_deptno
  2  after delete on dept
  3  for each row
  4  begin
  5  delete from emp where deptno=:old.deptno;
  6  end;
  7  /
 
Trigger created

  
 create or replace trigger insert_dept
 after insert on department
 for each row 
 begin
 insert into emp (eid,ename,id) values('121','qwert',:new.id);
 end;
 /
---------------------------------------------------

:old.deptno;
:new.id
逻辑上的表,物理文件上的表.
(两个重要的内存表,随便理解表,视图虚表之类的)
        old    new
insert    -    yes
delete   yes    -
update   yes   yes
 
最重要的操作,对表做级联更新: (不同于插入和删除操作,这里是更新操作)
create or replace trigger update_dept
after update on deptment
for each row
begin
update emp set id=:new.id where id=:old.id;
end;
/
update deptment set id='yy' where id='10';
----------------------------------------------------
create or replace trigger books_delete
after delete on books
for each row
begin
if:old.books_id='0001' then
raise_application_error(-20000,'不能删除!'); //两个参数,一个为错误号,一个为错误消息
end if;
end;
/

application服务器的自定义实例raise_application_error
写的数值在区间:  -299999 ~ -20000

行级触发器,对于涉及到的每一行都会做检查的,因为它涉及到数据库的完整性问题
--------------------------------------------------------------------------
  下面看看语句级触发器问题:
  首先创建一个日志表:
  create talbe mylog(curr_user varchar2(100),curr_date date,act char(1));
  创建语句级的触发器:
  create or replace trigger dml_aa
  after insert or delete or update on aa
  begin
  if inserting then
  insert into mylog values(user,sysdate,'I');
  elsif deleting then
  insert into mylog values(user,sysdate,'D');
  else
  insert into mylog values(user,sysdate,'U');
  end if;
  end;

  save c:\trigger01.txt;

select myseq.nextval from dual;
insert into auto vlues(mysql.nextval,'sd');

利用触发器实现表的列的自增:
create or replace trigger set_no
before insert on auto
for each row
declare
sn number(5);
begin 
select mysql.nextval into en from dual;
:new.a:=sn;
end;
/
 
------------------------------------
 替换触发器的应用,注意替换触发器只能发生在视图上面:
 利用视图触发器可以解决多表更新的问题,功能很强大:
 
 create or replace trigger tr_v_e_d
 instead of insert on v_emp_dept
 for each row
 begin
 insert into department values(:new.id,:new.name);
 insert into emp(eid,ename,sex,id) values(:new.eid,:new.ename,:new.sex,:new.id);
 end;
 /
 
  pl/sql的进阶 --编写分页过程
   介绍:分页是任何一个网站(bbs,网上商城,blog)都会用到的

1) 无返回值的存储过程:
  eg：现有一张表book,表结构如下
 书号,书名,出版社:
 请编写一个过程,可以向book表中添加书,要求通过java程序调用该过程
 
  建表book
 create table book(bookId number,bookName varchar2(50),publishHouse varchar2(50));
 -----编写过程 
(in表示是往存储过程中输入的变量,如果不写in默认就是in)
(out表示是一个输出参数如果不写默认就是in)
 create or replace procedure sp_pro
(spBookId in number varchar2,spbookName varchar2,sppublishHouse in varchar2) is 
begin
 insert into book values(spBookId,spbookName,sppublishHouse);
end;
/
-----看看如何在Java如何调用;
///调用一个无返回值的过程
 
public class Test{
 public static void main(String[] args){
   try{
        //加载驱动
   Class.forName("oracle.jdbc.driver.OracleDriver");
   Connection ct=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL","scott","tiger"); //获得数据库连接
   //创建callablestatement
   CallableStatement cs =ct.prepareCall("{call sp_pro(?,?,?)}");
   cs.setInt(1,10);
   cs.setString(2,"prison break");
   cs.setString(3,"china publisher");
   //执行
   cs.execute();
   //关闭,最好在finally里面关闭
       }catch{
            e.printeStackTrace();
          }finally{
           //关闭各种打开的资源
            cs.close();
            ct.close();
         }
  }
}
-----------------------------------------------------------------------------------
  有返回值的存储过程(非列表)
 
1) 编写一个过程,可以输入雇员的编号,返回该雇员的姓名
2) 编写一个过程,可以输入雇员的编号,返回该雇员的姓名,工资,和岗位

eg: 有输入和输出的存储过程
 create or replace procedure sp_pro
(spno in number,spName out varchar2) is 
begin
select ename into spName from emp where empno=spno;//查出的值已经赋给变量spName了
end;
/

public class Test{
 public static void main(String[] args){
   try{
        //加载驱动
   Class.forName("oracle.jdbc.driver.OracleDriver");
   Connection ct=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL","scott","tiger"); //获得数据库连接
  //创建callablestatement
  //看看如何调用有返回值的过程
  CallableStatement cs =ct.prepareCall("{call sp_pro(?,?)}");
  //给一个?赋值
  cs.serInt(1,7788);
  //给二个?赋值
  cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);
  //执行
  cs.execute();
  //取出返回值
  String name =cs.getString(2);
  System.out.println(name);
   //执行
   cs.execute();
   //关闭,最好在finally里面关闭
       }catch{
            e.printeStackTrace();
          }finally{
           //关闭各种打开的资源
            cs.close();
            ct.close();
         }
  }
}
-----------------------------------------------------------------------------

create or replace procedure sp_pro
(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;
 
public class Test{
 public static void main(String[] args){
   try{
        //加载驱动
   Class.forName("oracle.jdbc.driver.OracleDriver");
   Connection ct=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL","scott","tiger"); //获得数据库连接
  //创建callablestatement
  //看看如何调用有返回值的过程
  CallableStatement cs =ct.prepareCall("{call sp_pro(?,?,?,?)}");
  //给一个?赋值
  cs.serInt(1,7788);
  //给二个?赋值
  cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);
  cs.registerOutParameter(3,oracle.jdbc.OracleTypes.DOUBLE);
  cs.registerOutParameter(4,oracle.jdbc.OracleTypes.VARCHAR);
  //执行
  cs.execute();
  //取出返回值
  String name =cs.getString(2);
  String job=cs.getString(4);
  System.out.println(name+" "+job);
   //执行
   cs.execute();
   //关闭,最好在finally里面关闭
       }catch{
            e.printeStackTrace();
          }finally{
           //关闭各种打开的资源
            cs.close();
            ct.close();
         }
  }
}
--------------------------------------------------------------------------------
有返回值的存储过程
 案例:编写有返回值的存储过程（列表|结果集）
  由于oracle存储过程没有返回值,它的返回值都是通过out参数来替代的,列表
同样也不例外,但由于是集合,所以不鞥用一般的参数,必须要用package了,所以
要分两个部分;

1) 建立一个包,在该包中定义了一个游标类型,这里定义的游标类型是test_cursor
create or replace package testpackage AS
TYPE test_cursor is ref cursor;
end testpackage;
2) 创建过程
create or replace procedure sp_pro
(spNo in number,p_cursor out testpackage.test_cursor)
begin
open p_cursor for select * from emp where deptno=spNo;
end;
3)如何在java中调用
public class Test{
 public static void main(String[] args){
   try{
        //加载驱动
   Class.forName("oracle.jdbc.driver.OracleDriver");
   Connection ct=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL","scott","tiger"); //获得数据库连接
   //创建callablestatement
   CallableStatement cs =ct.prepareCall("{call sp_pro(?,?)}");
   cs.setInt(1,10);
   cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR); //注册的类型是cursor
   //执行
   cs.execute();
   ResultSet rs=(ResultSet)cs.getObject(2);
   while(rs.next()){
    System.out.println(rs.getInt(1)+"  "+rs.getString(2));
   }
   //关闭,最好在finally里面关闭
       }catch{
            e.printeStackTrace();
          }finally{
           //关闭各种打开的资源
            cs.close();
            ct.close();
         }
  }
}
 
 
     在Java中操作数据库
     public class TestOra{
     public static void main(String[] args){
        try{
  //加载驱动
 Class.forName("sun.jdbc.odbc.JdbcodbcDriver");
  //得到连接,桥连接
 Connection ct=DriverManger.getConnection("jdbc:odbc:testsp","scott","tiger");
 //从下面开始就和其他操作是一样的
 Statement sm=ct.createStatement();
 ResultSet rs=sm.executeQuery("select * from emp");
 while(rs.next()){
  //用户名
 System.out.println("用户名:"+rs.getString(2));
             }
          }catch(Exception e){
              e.printStackTrace();
           }
     }
   }
odbc如何配置数据源:
 控制面板---->管理工具----->数据源----->odbc数据源管理器---->驱动程序
 data source name--->testsp
 tns service name--->myora1
-----------------------------------------------------------
   通过jdbc来连接数据库:
public class testOra{
  public static void main(String[] args){
     try{
       //1,加载驱动
       Class.forName("oracle.jdbc.driver.OracleDriver");
       //2,得到连接
       Connection ct=DriverManger.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL","scott","tiger");
 //从下面开始就和其他操作是一样的
 Statement sm=ct.createStatement();
 ResultSet rs=sm.executeQuery("select * from emp");
 while(rs.next()){
  //用户名
 System.out.println("用户名:"+rs.getString(2));
             }
          }catch(Exception e){
              e.printStackTrace();
           }
}
   }
--------------------------------------------------------
  写一个oracle分页实例:
<%@ page language="java" import="java.util.*,com.sql.*" pageEncoding="gbk"%>

<body>
<h2>oracle分页案例</h2>
<table>
<tr><td>用户名</td><td>薪水</td></tr>
<%
       //1,加载驱动
       Class.forName("oracle.jdbc.driver.OracleDriver");
       //2,得到连接
       Connection ct=DriverManger.getConnection              ("jdbc:oracle:thin:@localhost:1521:ORCL","scott","tiger");
       //从下面开始就和其他操作是一样的
       Statement sm=ct.createStatement();
       ResultSet rs=sm.executeQuery("select * from emp");
       //在循环之前打行标记
       while(rs.next()){
       out.println("<tr>");
        //用户名
       out.println("<td>:"+rs.getString(2)+"</td>");
        //薪水
       out.println("<td>:"+rs.getString(2)+"</td>");
       out.println("</tr>");
       }
%>
</table>
</body>
</html>

------------------------------------------------------分页查询语句;
select * from 
(select al.*,rownum rn from (select * from emp) a1 where rownum<=3)
where rn>=1;
//的查询总的记录条数
 int pageCount =0;
 int  rowCount =0;
 int  pageSize =0;
ResultSet rs=sm.executeQuery("select count(*) from emp");
if(rs.next()){
 rowCount =rs.getInt(1);
 if(rowCount%pageSize==0){
   pageCount=rowCount/pageSize;
  }else{
   pageCount=rowCount/pageSize+1;
   }
}
//打印总记录数
for(int i=1;i<=pageCount;i++){
  
  out.print("<a href=mytest.jsp?pageNow="+i+">["+i+"]</a>");
}
//接受pageNow
String s_pageNow=(String)request.getParameter("pageNow");
int pageNow=1;
if(s_pageNow!=null){
 pageNow =Integer.parseInt(s_pageNow);
}
select * from 
(select al.*,rownum rn from (select * from emp) a1 where rownum<="+pageNow*pageSize+")
where rn>="+((pageNow-1)*pageSize+1)+";
-------------------------------------------------------
  写一个oracle分页实例:
<%@ page language="java" import="java.util.*,com.sql.*" pageEncoding="gbk"%>

<body>
<h2>oracle分页案例</h2>
<table>
<tr><td>用户名</td><td>薪水</td></tr>
<%
       //1,加载驱动
       Class.forName("oracle.jdbc.driver.OracleDriver");
       //2,得到连接
       Connection ct=DriverManger.getConnection              ("jdbc:oracle:thin:@localhost:1521:ORCL","scott","tiger");
       //从下面开始就和其他操作是一样的
       Statement sm=ct.createStatement();
       ResultSet rs=sm.executeQuery("select * from emp");
       //在循环之前打行标记
       while(rs.next()){
       out.println("<tr>");
        //用户名
       out.println("<td>:"+rs.getString(2)+"</td>");
        //薪水
       out.println("<td>:"+rs.getString(6)+"</td>");
       out.println("</tr>");
       }
%>
</table>
</body>
</html>
 
 
 如果Scott用户被锁:
 用dba的身份为该用户解锁
 alter user scott account unlock;
 
 passw ----修改密码
 passw scott; ---用dba身份修改scott用户的密码
 select table_name from user_tables;
 ----查看当前用户下面所拥有的对象
 select * from tab;  (得到的结果和上句类似)
 desc inf;
 conn / as sysdba;   ---sys的身份
 conn / as sysoper;
 conn system/manager;
 conn sys/change_on_install;
 create user rufus identified by rufus_123;
 alter user rufus identified by rufus;
 
--查看当前用户被授予的系统权限
select * from user_sys_privs;
--查看当前用户被授予的对象权限
select * from user_tab_privs;

--查看当前用户被授予的角色
select * from user_role_privs;
---------------------------------------------------------------
  查询阶段记住的两个查询(内嵌表,视图)
  select * from (select a1.*,rownum rn from (select * from emp) a1 where 
  rownum<=10) where rn>=6;
  
  如何查看某员工的工资等级??
  select a1.ename,a2.grade from emp a1,salgrade a2 where a1.sal between a2.losal and a2.hisal;
  
  如何查看某员工的上级领导??
  select a1.ename,a2.ename from emp a1, emp a2 where a1.mgr =a2.empno;
  
  如何查看谁是最高的领导??
  select * from emp where mgr is (not) null;
  如何查看某员工的全年收入??
 
 
 
 编写分页过程
  完成分页存储过程
  可以输入表名,每页显示记录数,当前页
  返回总记录数,总页数,和返回的结果集合
  
oracle分页
select t1.* ,rownum rn from (select * from emp) t1
select t1.*,rownum rn from (select * from emp) t1 where rownum<=10;
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;

在分页的时候,可以把上面的sql语句当做模板使用
create or replace procedure pagi
(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(500);
----定义两个整数
v_begin number:=(pageNow-1)*pagesize+1;
v_end number:=pagesize* pageNow;
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;
v_sql:='select count(*) from '||tableName;
execute immdiate v_sql into myrows;
----计算myPageCount
if mod(myrows,pagesize)=0 then    //数学函数mod(m,n)
mypagecount :=myrows/pagesize;
else
mypagecount :=myrows/pagesize+1;
end if;
---关闭游标
---close p_cursor;
end;
---------------------------------------------------------------------
要Java测试分页过程;
public class Pagi{
 public static void main(String[] args){
   try{
    //加载驱动
   Class.forName("oracle.jdbc.driver.OracleDriver");
   Connection ct=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL","scott","tiger");
   //获得数据库连接
   CallableStatement cs =ct.prepareCall("{call pagi(?,?,?,?,?,?)}");
   //给?赋值
   cs.setString(1,emp);
   cs.setInt(2,5);
   cs.setInt(3,1);
   //后面三个是输出参数
    //注册总记录数
   cs.registerOutParameter(4,oralce.jdbc.OracleTypes.INTEGER);
    //注册总页数
   cs.registerOutParameter(5,oracle.jdbc.OracleTypes.INTEGER);
   //注册返回结果集
   cs.registerOutParameter(6,oracle.jdbc.OracleTypes.CURSOR);
   cs.execute();
   //取出总记录数
   int rowNum =cs.getInt(4); //中4是由该参数的位置决定的;
   int pageCount =cs.getInt(5);
   ResulSet rs=(ResultSet)cs.getObject(6);
   System.out.print("总行数="+rowNum);
   System.out.print("总页数="+pageCount);
   while(rs.next()){
      System.out.println("编号:"+rs.getInt(1)+" 姓名"+rs.getString(2)); 
     }
   }
   //关闭,最好在finally里面关闭
       }catch{
            e.printeStackTrace();
          }finally{
           //关闭各种打开的资源
            cs.close();
            ct.close();
         }
  }
}
----------------------------------------------------------------------
新的要求.薪水升序排列，取出6—10个人
改变最内层的查询语句;
v_sql:='select * from 
(select t1.*,rownum rn from (select * from '||tableName||' order by sal)t1 where rownum<='||v_end||')
 where rn>='||v_begin;
 

全局数据库名 和 sid的值是一样的
安装到最后所有的密码统一设置为oraleadmin 字母全部小写,最好不要
擅自更改;
oracle安装完成以后会自动在windows之中为用户注册若干个服务，但是
这种服务可以发现许多的是默认启动的,而这样如果配置为默认启动,则有可能
启动的速度很慢,所以建议修改为手工启动方式;
  但是在这几个服务之中,有以下两个服务是最重要的:
        监听服务: oracleoradb10g_home1TNSListener
数据库的实例服务: oracleserviceMLDN
 
以后如果有程序要操作数据库,或是一些远程的客户端要连接数据库则必须启动
此服务监听服务: oracleoradb10g_home1TNSListener
 数据库的实例服务:oracleserviceMLDN
 保证数据库的具体信息服务,每一个数据库有一个数据库实例(服务)
 命名规则:oracleServiceXXXX，其中XXX是配置的数据库(sid名称)
 监听问题:
 监听服务是oracle数据库之中使用最主要的一个服务,但是这个服务经常会出现错误,
包括以后在工作之中这个服务也会出现错误:
错误一: 注册表使了优化软件被删除了相关项;
      
  对于每一个系统服务实际上都会在注册表之中有所保存;
  把监听器全名复制,在注册表编辑器中查找oracleOraDb10g_home1TNSListener
   
错误一: 网络环境发生改变,在oracle里面,如果网络的环境出现了改变以后,有可能原始
配置的网络就无法正常的工作,此时必须进行手工的进行网络配置的修改;
  网络环境的改变最多的就是电脑名称的改变;
  【我的电脑】-->【属性】---->【计算机名】--->【更改主机名称】
  将新的主机名称改为teacher,这样一来实际计算机的网络名称就发生了改变,如果在oracle
11g 之中,这个问题可以帮助用户自动解决进行重新的配置,但是在oracle10g或者说是更早的版本
之中,这个问题只能手工解决.
  解决此问题,修改网络环境就可以了,
以后如果使用程序连接oracle 10g的话,则以上的配置有可能还是不鞥正常的访问
数据库,此时可以进入到第二步操作,进行数据库名称的注册;
  选择net manager,进入网络配置界面,
 

 序列sequence是序列号生成器,可以为表中的行自动生成序列号,产生一组等间隔的数值
(类型为数值),其主要作用是生成表的主键值,可以在插入语句中引用,也可以通过查询检查
当前的值,或使得序列增至下一个值;
 create sequence 序列名 [increment by n] [start with n] [{maxvalue/minvalue n/nomaxvalue}]
 [cycle|nocycle] [{cache n|nocache}];
 
 删除序列的语法是drop sequence 序列名;
 
 create sequence abc_seq increment by 1 start with 1 maxvalue 9999 nocyle nocache;
 如何使用自己创建的序列;
 调用nextval将生成序列中的下一个序列号,调用时候指出序列名,即为调用下面方式调用;
 序列名.nextval
----------------------------------------------------------------------------------------
 alter user scott account unlock;
 alter user scott identified by tiger;
 rownum 解决oracle中没有自动增长列的问题,为每次取出的行自动添加
 行号,rownum必须从数字1开始;
 oracle的体系结构:
 数据库的体系结构,oracle服务器提供开放的,全面和综合的信息管理,它
 由oracle数据库和oracle实例组成,一个数据库可以由多个实例使用,此时称为
 多节点数据库,但一个实例同时只能使用一个数据库;
 oracle服务器 =oracle 数据库+ oracle实例
 oracle运行的主要组件:
 用户进程--->服务器进程pga--->
 
 oracle实例是后台进程和内存结构的集合
 oracle实例分配系统全局区,启动后台进程
 实例:内存结构(sga) 共享池 // 数据缓冲区 // 日志缓冲区
 实例:后台进程:PMON,SMON,DBWR,LGWR,CKPT
 和后台进程交换文件:(实例和数据库交互)
 数据库(参数文件,口令文件,归档日志文件) --->数据文件,控制文件,日志文件
 sga 介绍:
 共享池:共享池是对sql,pl/sql程序进行语法分析编译,执行的内存区域
 共享池是由库缓存和数据字典缓存组成
 共享池的大小直接影响数据库的性能
 数据缓冲区
 用于存储从磁盘数据文件中读入的数据,所有用户共享
 服务器进程将读入的数据保存在数据缓存区中,当后续的请求需要这些数据时
 可以在内存中找到,不需要从磁盘中读取,提高了读取速度
 日志缓存区:
 日志记录数据库的所有修改信息,日志信息首先产生于日志缓冲区
 
 后台进程:
 pmon: 进程监控进程
 smon: 系统监控进程
 dbwr: 数据写入进程
  管理数据缓冲区,将最近使用过的块保留在内存中
  将修改后的缓冲区数据写入数据文件中
 lgwr: 日志写入进程
  负责将日志缓冲区中的日志数据写入日志文件
  系统有多个日志文件,该进程以循环的方式将数据写入文件
---------------------------------------------------------------------------------
创建表空间:
 
create  tablespace wh datafile 'E:\oracle\product\10.1.0\oradata\oral\wh01.dbf'
size 10 autoextend on maxsize 100m;
向表空间添加数组文件:
alter tablespace wh add datafile 'E:\oracle\product\10.1.0\iradata\orcl\wh02.dbf'
size 5m;
如何创建临时表空间
create temporary tablespace mytemp tempfile
'E:\oracle\product\10.1.0\oradata\orcl\mytemp01.dbf' size 5m;
数据文件不能跨表空间
但是数据段可以跨越数据文件,数据段也不能跨表空间存在;
删除表空间:
drop tablespace whos including contents and datafiles;
drop tablespace 表空间名 including contents and datafiles;
-----------------------------------------------------------------------------------
oracle数据库日常管理,备份,恢复,优化;
存储过程:(Oracle数据库的精华)
 
root = windows.adminstrator
 linux 的魅力就在它的图形化界面
 shotdown -h now  立刻进行关机
 shutdown -r now  现在重新启动计算机
 reboot           现在就重新启动计算机

启动
 startup mount /start up 启动实例,打开控制文件,打开数据文件

oracle 安全管理 oracle的安全管理体系
1)oracle管理数据库安全的特点
 操作系统----->用户和组
 在Oracle中------->用户和角色
 接受用户在服务器上连接上的一个请求,当检测到一个请求的时候,一旦连接成功
当Oracle安装成功以后,Oracle有一个默认的监听程序,

 理解oracle的pl/sql概念
 掌握pl/sql编程技术(包括编写过程,函数,触发器....)
 
 pl/sql是什么: 过程化语言;
 procedure language/sql 是oracle在标准的sql语言上的扩展,pl/sql不仅容许嵌入sql语言;
 还可以定义变量和常量,容许使用条件语句和循环语句,容许使用例外处理各种错误,这使得它的
 功能变得更加的强大;
 java所具有的逻辑判断功能全部具有;
 pl/sql是非常强大的数据库过程语言;
 pl/sql编写的过程,函数可以在Java程序中调用;

 学习必要性:
 提高应用程序的运行性能
 模块化的设计思想[分页的过程| 订单的过程|转账的过程--]
 减少网络传输

 写一个简单的存储过程:
 1：创建一个简单表
 2: 查看错误信息 show error;
 
SQL> create table mytest(name varchar2(30),passwd varchar2(30));
 
Table created
 
SQL> create or replace procedure sp_pro is
  2  begin
  3  insert into mytest values('rufus','m123');
  4  end;
 
 3:如何调用该过程
 exec 过程名(参数值1,参数值2,....);
 call 过程名(参数值1,参数值2,....);
 exec sp_pro;
    
create or replace sp_pro2 is
begin
----执行部分
delete from mytest where name="韩顺平";
end;
/

----调用过程
exec sp_pro2;
开发人员使用pl/sql编写应用模块时候,不仅需要掌握sql语句的编写方法,
还要掌握pl/sql语句及语法规则,pl/sql编程可以使用变量和逻辑控制语句
从而可以编写非常有用的功能模块,而且使用pl/sql编程,可以轻松完成非
常复杂的查询;

简单过程 块(编程)--->过程/函数/触发器/包
标志符号的命名规范;
1):当定义变量时候,建议使用V_ 作为前缀 v_sal;
2):当定义常量时,建议使用c_ 作为前缀 c_rate;
4):当定义例外时,建议使用 e_作为前缀 e_error;
3):当定义游标时,建议用_cursor 作为后缀 emp_cursor;

pl/sql块
块block是pl/sql的基本程序单元,编写pl/sql程序实际上就是pl/sql块,
要完成相对简单的应用功能,可能只需要编写一个pl/sql块,但是如果要想
实现复杂的功能,可能需要在一个pl/sql块中嵌套其他的pl/sql块;
块结构示意图
pl/sql块由三个部分构成:定义部分,执行部分,例外处理部分
如下所示:
declear
/* 定义部分----定义常量,变量,游标,例外,复杂数据类型*/
begin
/*执行部分---要执行的pl/sql语句和sql语句*/
exception
/*例外处理部分---处理运行的各种错误*/
special
定义部分是从declare开始的
该部分是可选的
执行部分是从begin开始的
该部分是必须的
例外处理部分是从exceptin开始的
该部分是可选的
set serveroutput off---关闭输出选项
set serveroutput on ---打开输出选项
begin 
  dbms_output.put_line('hello');
  end;
相关说明:
dbms_output是oracle所提供的包(类似Java的开发包),该包包含
一些过程,put_line就是dbms_output包的一个过程
 
declare
 v_ename varchar2(5);
begin
 select ename into v_ename from emp where empno=&no; //取出一个值赋值给变量
 dbms_output.put_line('雇员名:'||v_ename);
end;
/

会提示对话框,要输入员工号.
declare
 v_ename varchar2(5);
 v_sal number(7,2);
begin
 select ename,sal into v_ename,v_sal from emp where empno=&no; 
 dbms_output.put_line('雇员名:'||v_ename||'薪水是:'||v_sal);
end;
/

&表示要接受参数;
为了避免pl/sql程序的运行错误,提高pl/sql的健壮性,应该对可能的错误进行处理
相关说明,oracle事先预定义了一些例外,no_data_found就是找不到数据的例外;
declare
 v_ename varchar2(5);
 v_sal number(7,2);
begin
 select ename,sal into v_ename,v_sal from emp where empno=&no; 
 dbms_output.put_line('雇员名:'||v_ename||'薪水是:'||v_sal);
exception
when no_data_found then
dbms_output.put_line('你的输入不存在');
end;
/
---------------------------------------------
过程:
过程用于执行特定的操作,当建立过程时候,既可以指定输入参数in
也可以指定输出参数out，通过在过程中使用输入参数,可以将数据
传递到执行部分,通过使用输出参数,可以将执行部分地数据传递到应
用环境,在sqlplus中可以使用create procedure 命令来建立过程

--编写一个过程只要指明其数据类型就可以了
create procedure ssp_proce (spName varchar2, newSal number) is
begin
 ------执行部分
 update emp set sal=newSal where ename=spName;
end;

调用存储过程:
 call ssp_proce('SCOTT','4562');
 exec ssp_proce('SCOTT','4562');
如何在java程序中调用一个存储过程:
public staitc void main(String[] args){
 try{
//加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//得到连接
 Connection ct =DriverManger.getConnection("jdbc:oracle:thin:@localhost:152:1ORCL","scott","tiger");
//创建callableStatement;
CallableStatement cs =ct.prepareCall("{call ssp_proce(?,?)}");
cs.setString(1,"SMITH");
cs.setInt(2,10);
cs.execute();
cs.close();
ct.close();
     }catch(Exception e){
     e.printStackTrace();
  }
}

/如何使用过程返回值.
函数
函数用于返回特定的数据,当建立函数时,在函数头部必须包含return子句,而在
函数体内必须包含return语句返回的数据,我们可以使用
create function 来建立函数
create function annual_income(name varchar2)
return number is
annual_salary number(7,2);
begin
 select sal*12 +nvl(comm,0)*12 into annual_salary from emp where ename=name;
 return annual_salary;
end;
/

在sqlplus中调用函数
Function created
 
SQL> var income number ///var定义object类型的变量
SQL> call annual_income('SCOTT') into:income;
 
Method called
income
---------
54744

同样我们可以在java程序中调用该函数
select annual_income('SCOTT') from dual;
rs.getInt(1);
包
包用于在逻辑上组合过程和函数,它由包规范和包体两部分组成
我们可以使用create package命令来创建包

create or replace package sp_package is
 procedure update_sal(name varchar2,newsal number);
 function annual_income(name varchar2) return number;
end;
 
包的规范只包含了过程和函数的说明,但是没有过程和函数的实现代码,包体
用于实现包规范中的过程和函数
建立包体可以使用create package body命令
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;

exe sp_package.update_sal('SCOTT',120);