数据库常用知识总结

一、sql窗口运行sql脚本start d:\aa.sql ; 编辑脚本edit d:\aa.sql; spool

     1、理解权限用例

sqlplus->system/login----->create user xiaoming identified by m123; ------->conn system/login------->grant connect to xiaoming;--------->grant resource to xiaoming;-----> create table test (userid varchar2(30),username varchar2(30));------> desc test;----->希望xiaoming用户可以去查询scott的emp表---->对象权限：select、inser、update、delete、all、create 、index、------》grant select on emp to xiaoming;----->conn scott/tiger;---->select * from scott.emp;方案------》set linesize 120;------>select * from scott.emp; 希望xiaoming用户可以去修改、删除、查询、添加scott的emp表---》grant update on emp to xiaoming;------》grant all on emp to xiaoming;//把对emp这张表的所有权限都交给了小明。-------》收回权限.scott希望收回xiaoming对emp表的查询权限。Revoke select on emp from xiaoming;l----->看一下当前的用户是谁？show user;------>切换conn scott/tiger;---->revoke select on scott.emp from xiaoming ;conn xiaoming/m123; select * from scott.emp;查不到了。

   2、对权限的维护

       希望xiaoming用户可以去查询scott的emp 表，还希望xiaoming可以把这个权限继续给别人。

       Grant select on emp to xiaoming with grant option 如果是对象权限，就加入with grant option;

   3、文件操作命令

        1）start和@ 说明：运行sql脚本 案例：sql>@ d:\a.sql 或者sql>start d:\a.sql 

        2）edit 说明：该命令可以编辑指定的sql脚本 案例：sql>edit d:\a.sql

         3）spool 说明：该命令可以将sql *plus屏幕上的内容输出到指定文件中去。案例：sql>spool d:\b.sql 并输入sql>spool off

              spool d:\bb.sql;

             select *from emp;

             spool off;

             显示页数--set pagesize 2;

   4、如果是系统权限

         System给xiaoming权限时：grant connect to xiaoming with admin option

？如果scott把xiaoming 对emp 表的查询权限回收，那么xioahong会怎么样？被回收了。

   Conn scott/m123;

   Revoke select on emp from xiaoming;

   Conn xiaohong/m123;

   Select * from scott.emp;

二、Oracle用户的管理

     1、Profile是口令限制，资源限制的命令集合。

           账户锁定：指定该账户登录时最多可以输入密码的次数，也可以指定用户锁定的时间，一般用dba身份去执行该命令

      ？指定scott这个用户最多只能尝试3次登录，锁定时间为2天，让我们看看怎么实现。(红字不可修改)

          Sql>create profile lock_account limit failed_login_attempts 3 password_lock_time 2;

          Sql>alter user scott profile lock_account;（指定小明用它）

      ？给账户（用户）解锁

          Sql>alter user scott account unlock;(conn system/login)

     2、终止口令：为了让用户定期修改密码可以使用终止口令的指令来完成。同样这个命令也需要dba身份来操作。

          案例：给前面创建的用户scott创建一个profile文件，要求该用户每隔10天要修改自家的登陆密码，宽限期为2天。看看怎                       么做？

                  Sql>create profile myprofile limit password_life_time 10password_grace_time 2;

                  Sql>alter user scott profile myprofile（分配给某个用户）

     3、口令历史：当发现新旧密码一样时，就提示用户重新输入密码。

例子：1、建立profile

         Sql>create profile password_history limit password_life_time 10 password_grace_time 2 password_reuse_time                                  10 Password_reuse_time //指定口令可重复即10天后就可以重用

           2、分配给用户。

           3、删除profile：sql>drop profile password_history [cascade]

三、目前主流数据库：微软：sql server 、access、瑞典：mysql 、IBM公司：db2、美国sybase公司：sybase、IBM公司：                                           informix、美国oracle、

     掌握：1、oracle表的管理（创建/维护）

                2、对oracle表的各种查询技巧

                3、学会创建新的oracle数据库

      表名和列名规则：必须以字母开头，长度不能超过30字符，不能使用oracle的保留字，只能使用如下字符A-Z,a-z,0-9,$,#等

      字符型：char 定长最大2000字符 varchar2() 变长 最大4000字符

      Clob(character large object) 字符型大对象最大4G

      数字型number 范围10的38次方、number（5,2）：表示一个小数有5位有效数，2位小数 、number（5）表示一个数5位整数

      日期类型：date 包含年月日时分秒 timestamp 时间戳：对date数据类型的扩展。银行项目会用到。Blob 二进制数据 可以存                            放图片、声音、声音 4G（保密性很高的情况下用、安全性）。一般情况下，数据库存放路径。

1) 添加一个字段Sql>alter table 表名 add(字段 字段值)；

2) Alter table student modify(xm varchar2(30));//修改字段长度

3) 删除一个字段sql>alter table student drop column sal;

4) 修改表的名字sql>rename student to stu;

5) 删除表sql>drop table student;

6) 表添加数据：所有字段都插入:insert into student values(‘A001’,’张三’,’男’,’01-5月-05’,10);oracle中的默认日期格式’DD-MON-YY’

7) 修改日期的默认格式：alter session set nls_date_format=’yyyy-mm-dd’;

insert into student values(1,'小明','男','1997-12-11'，2345.6,12);

插入部分字段Insert into student(xh,xm,sex) values(‘A003’,’’,’JOHN’,’女’);

插入空值：insert into student (xh,xm,sex,birthday)values(‘’,’’,’’,null);

select * from student where birthday is null;

select * from student where birthday is not null;

改一个字段：update student set sex=’’where xh=’’;

修改多个字段：update student set sex=’男’,birthday=’1764-5-6’where xh=’A001’;

修改含有null值的数据

删除数据：delete from student ;删除所有记录，表结构还在，写日志，可以恢复的，速度慢drop table student;删除表结构和数据 truncate table student ;删除表中对的所有记录，表结构还在，不写日志，无法找回删除的记录，速度快。

设置保存点savepoint 回滚：rollback to

Job职位 clerk职员 salesman销售 analyst分析师president总裁总经理 mgr该员工的上级编号 king没上级 hiredate雇佣的时间 sal 工资 comm 奖金 deptno 部门编号

Dept部门表 accounting财务部 research研发部门 sales 销售部 operations业务部

Loc 部门所在地点

Desc dept;查看表结构

如何取消重复行 select distinct deptno ,job from emp;

set timing on;操作显示时间 count(*) from users; 查询出一共有多少条记录。查询SMITH的薪水，工作，所在部门 select deptno ,job,sal from emp where ename=’SMITH’;oracle中的字段值区分大小写。字段大小写不区分。

Select sal*13 “年工资”,ename from emp;

如何处理null 值 ？使用nvl函数来处理

Select sal*13 +nvl(comm.,0)*13 “年工资”，ename，comm from emp;

如何连接字符串（||） select ename || ‘is a’||job from emp;

如何使用like操作符 %：表示任意0到多个字符 _:表示任意单个字符

*使用逻辑操作符号

查询工资高于500或是岗位为MANAGER的雇员，同时还要满足他们的姓名首写字母为大写的J

Select * from emp where (sal>500 or job=’MANAGER’) and ename like ‘J%’;

*使用Order by 字句 ？如何按照工资的从低到高的顺序显示 雇员的信息

Select * from emp order by sal;

Select * from emp sal order by sal desc; 从高到低

？按照部门号升序而雇员的工资按降序排列

Select * from emp order by deptno asc , sal desc;

？按照部门号升序而雇员的入职时间按降序排列 。

Select * from emp order by denptno asc ,hiredate desc;

*使用列的别名排序

Select ename,(sal+nvl(comm,0))*12 as “年薪” from emp order by “年薪”;

*分页查询 按雇员的ID号升序取出

Oracle表复杂查询***

数据分组---max , min , avg , sum , count

?如何显示所有员工中最高工资和最低工资

Select max(sal),min(sal) from emp;

如何显示所有员工中最高工资的员工姓名。

Select ename ,sal from emp where sal=(select max(sal) from);----结果

？显示所有员工的平均工资和工资总和

？计算共有多少员工

扩展要求：

？请显示工资最高的员工的名字，工作岗位。

Select * from emp where sal>(select avg(sal) from emp);

Group by 和having 子句

Group by 用于对查询结果分组统计，

Having字句用于限制分组显示结果。

？如何显示每个部门的平均工资和最高工资

select avg(sal),max(sal),deptno from emp group by deptno;

？显示每个部门的每种岗位的平均工资和最低工资

Select avg(sal),max (sal) ,deptno ,job from emp group by deptno,job;

？显示平均工资低于2000的部门号和它的平均工资

Select avg(sal),max(sal),deptno from emp group by deptno having avg(sal)>2000;

Select avg(sal),max(sal),deptno from emp group by deptno having avg(sal)>2000 order by avg(sal);

 

四、对数据分组的总结

1. 分组函数只能出现在选择列，having、order by 子句中。
2. 如果在select 语句中同时包含有group by,having,order by那么他们的顺序是 group by ， having ,order by分组在前排序在后
3. 在选择列中如果有列、表达式、和分组函数，那么这些列和表达式必须有一个group by 子句中，否则就会错。

     如 select deptno , avg(sal) , max(sal) from emp group by deptno having avg(sal)<2000; 这里deptno 就一定要出现在group by 中。

      Oracle 多表查询------多表查询

？显示雇员名，雇员工资及所在部门的名字【笛卡尔集】多表查询至少不能少于表的个数-1

Select a1.ename ,a1.sal , a2.dname from emp a1 ,dept a2 where a1.deptno=a2.deptno;

？如何显示部门号为10的部门名、员工名和工资

Select a1.dname， a2.ename ， a2.sal from dept a1, emp a2 where a1.deptno=a2.deptno and a1.deptno=10;

？显示各个员工的姓名，工资，及其工资的级别

select * from salgrade;

GRADE LOSAL HISAL

------ ---------- ----------

1 700 1200

2 1201 1400

3 1401 2000

4 2001 3000

5 3001 9999

Select a1.ename, a1.sal, a2.grade from emp a1 ,salgrade a2 where a1.sal between a2.losal and s2.hisal;

扩展要求：

？显示雇员名，雇员工资及所在部门的名字，并按部门排序。

Select a1.ename ,a2.dname , a1.sal from emp a1, dept a2 where a1.deptno=a2.deptnp order by a1.deptno;多表也可以排序

自连接 ？显示某个员工的上级领导的姓名 比如显示’ford’ 的上级。

Select worker.ename , boss.ename from emp worker,emp boss where worker.mgr=boss.empno and worker.ename=’ford’;

子查询 Select * from table where=(select …..select())

单行子查询

单行子查询是指只返回一行数据的子查询语句

请思考：如何显示已SMIth同一部门的所有员工？

思路：1、查询Smith的部门号

Select deptno from emp where ename=’smith’ ;

2、显示

Select * from emp where deptno = (Select deptno from emp where ename=’smith’);//数据库在执行sql是从左到右

多行子查询

多行子查询指返回多行数据的子查询

请思考：如何查询和部门10的工作相同的雇员的名字、岗位、工资、部门号

Select distinct job from emp where deptno =10

Select * from emp where job in (Select distinct job from emp where deptno =10);

在多行子查询中使用all操作符

请思考：如何显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号

Select ename , sal, deptno from emp where sal>all (select sal from emp where deptno=30);

扩展要求：想想还有没有别的查询方法。

Select max(sal) from emp where deptno=30

Select * from emp where sal>( Select max(sal) from emp where deptno=30);(效率要比上面的要快，比较次数比上面的少。)

在多行子查询中使用any操作符

请思考：如何显示工资比部门30的任意一个员工的工资高的员工的姓名、工资和部门号

Select sal from emp where deptno=30

Select ename, sal, dept from emp where sal>any (select sal from emp where deptno=30);

多列子查询（查询返回多个列数据的子查询语句）

请思考如何查询与Smith的部门和岗位完全相同的所有雇员

查询Smith的部门号，岗位

select deptno,job from emp where ename=’SMITH’;

select * from emp where (deptno,job)=(select deptno,job from emp where ename=’SMITH’;

在from子句中使用子查询

请思考：如何显示高于自己部门平均工资的员工的信息

1、查询出各个部门的平均工资和部门号select deptno , avg(sal) mysal from emp group by deptno;

DEPTNO MYSAL

------ ----------

30 1566.66667

20 2175

10 2916.66667

2、把上面的查询看作是一张 子表

Select a2.eanme, a2.sal, a2.deptno , a1.mysal from emp a2, (select deptno , avg(sal) mysal from emp group by deptno;) a1 where a2.depntno=a1.deptno and a2.sal>a1.mysal;

衡量一个程序员的水平：数据库知识，网络处理，程序优化

说明：当在from子句中使用子查询时，（内嵌视图），必须给子查询指定别名。给表取别名，不可以加as. 给列取别名可以加as.

Oracle分页查询（三种方式） 按雇员的id号升序

Rownum分页 select a1.*,rownum rn from (select * from emp ) a1;

显示rownum [oracle分配二分机制思想]

select a1.*,rownum rn from (select * from emp ) a1 where rownum<=10;

select * from (select a1.*,rownum rn from (select * from emp ) a1 where rownum<=10) where rn>=6;

几个查询的变化：

指定查询列，只需修改最里层对的信息。

select * from (select a1.*,rownum rn from (select ename , sal from emp ) a1 where rownum<=10) where rn>=6;

如何排序

select * from (select a1.*,rownum rn from (select ename , sal from emp order by sal desc) a1 where rownum<=10) where rn>=6;

显示4-9记录

select * from (select a1.*,rownum rn from (select ename , sal from emp order by sal desc) a1 where rownum<=9) where rn>=4;

用查询结果创建新表

这个命令是一种快捷的建表方法。 Create table my table (id, name, sal, job, deptno) as select empno, ename, sal, job, deptno from emp;

Desc myemp;

合并查询 有时在实际应用中，为了合并多个select语句的结果，可以使用集合操作符号 union ，union all ,intersect（交集查询）, minus(合并查询)

1. Union（很少用）

该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行。

Select ename , sal, job from emp where sal>2500 union select ename, sal, job from emp where job = ‘MANAGER’；

创建数据库有两种方法：

1. 通过Oracle提供的向导工具
2. 我们可以用手工步骤直接创建。

创建数据使用工具dbca[数据库配置助手]

package Testmyoracle;

import java.sql.*;

public class TestOra {

public static void main(String[] args) throws SQLException {

//1、加载驱动

try {

     Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

//2、得到连接

      Connection ct=DriverManager.getConnection("jdbc:odbc:testsp", "scott", "tiger");

/* 控制面板\所有控制面板项\管理工具--->数据源---->用户DSN-->添加Oracle in OraDb11g_home1 点击完成 oracle ODBC Driver Configuration Datasourcename:自己随便起名字。TNS Service Name:选择ORCL TestConnection*/   

     Statement sm=ct.createStatement();

     ResultSet rs=sm.executeQuery("select * from emp");

     while(rs.next()){

             System.out.println("用户名："+rs.getString(2));

     }

} catch (ClassNotFoundException e) {

e.printStackTrace();

   }

}

}

五、分页功能实现过程

int pagecount=0;//查询总页数

int rowcount=0;//共有几条记录

int pagesize=3;//每页显示几条记录

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”+i+”]</a>”);   

}

Int pagenow=request.getParameter(“pagenow”);

Int pagenow=1;

If(s_pageNow!=null){

Pagenow=integer.parseint(s_pagenow);

}

//接受pageNow

String s_pageNow=(String)request.getParameter(“pageNow”);

Int pageNow=1;

If(s_pageNow!=null){

   pageNow=Integer.parseInt(s_pageNow);

}

rs=sm.executeQuery(“select * from (select a1.*, rownum rn from (select * from emp) a1 where rownum<=”pageNow*pagesize+”) where rn>=”((pageNow-1)*pagesize+1) +” ”) ;

ageNow=1;pageNow=2

insert into emp values(9998,’小红’,’MANAGER’,7782,’11-11月-1988’,78.9,55.33,10);

使用to_date函数 如何插入带有日期的表，并按照年-月-日的格式插入？

insert into emp values(9998,’小红’,’MANAGER’,7782,to_date(’1988-12-12’,’yyyy-mm-dd’),78.9,55.33,10);或者to_date(’1988/12/12’,’yyyy/mm/dd’)

create table kkk(myid number(4),myname varchar2(50),mydept number(5);

insert into kkk(myid,myname,mydept) select empno,ename,deptno from emp where deptno=10;//数据行迁移，十号部门的信息

select * from kkk;

使用子查询更新数据 使用update语句更新数据时，既可以使用表达式或者数值修改数据，也可以使用子查询修改数据。

？希望员工scott的岗位、工资、补助、与Smith员工一样

Update emp set (job,sal,comm)=(select job,sal,comm from emp where ename=’SMITH’) where ename=’SCOTT;

ORACLE中事务处理：由一组相关的dml（增删改要么全部成功，要么全部失败）语句组成，

当执行事务操作时（dml语句），oracle会在被作用的表上加锁，防止其他用户改表表的结构，这里对我们用户来说是很重要。

Savepoint a1;

Delete from emp where empno=9996;

Savepoin a2;

Delete from emp where empno=9999;

Rollback to a2;

Commit //一旦提交，保存点不存在。提示： 从未创建保存点。当执行使用commit语句可以提交事务，会确认事务的变化、结束事务、删除保存点、释放锁，其他会话将可以查看到事务变化后的新数据。

Exit//自动提交。

Java程序中如何使用事务

在Java操作数据库时，为了保证数据的一致性，比如转账操作（1）从一个账户减掉10￥（2）在另一个账户上加入10￥，如何使用事务？

Ct.setAutoCommit(false);//不能默认提交

Statement sm=ct.createStatement();

Sm.executeUpdate(“update emp set sal=sal-100 where ename=’scott’”);

Sm.executeUpdate(“update emp set sal=sal+100 where ename=’smith”);

Ct.commit();

//如果发生异常，就回滚

Ct..rollback();//本身就会抛出异常。用友财务软件。

只读事务 设置只读事务set transaction read only;尽管其他会话可能会提交新的事务，但只读事务将不会取得最新数据的变化，从而可以保证取得特定时间点的数据信息。

假定机票代售点每天18点开始统计今天的销售情况。

Sql函数的使用 字符函数

Lower(char) upper(char) length(char) substr(char,m,n)

?将所有员工的名字按小写的方式显示

Select lower(ename) ,sal from emp;

?将所有员工的名字按大写的方式显示

Select upper(ename) from emp;

？显示正好为5个字符的员工的姓名

Select * from emp where length(ename)=5;

？显示所有员工姓名的前三个字符。

Select substr(ename,1,3) from emp;

1.完成首字母大写 Select upper(substr (ename,1,1)) from emp;

2.完成后面字母小写 Select lower(substr(ename,2,length(ename)-1)) from emp;

3.合并 select upper(substr(ename,1,1))||lower(substr(ename,2,length(ename)-1)) from emp;

？以首字母大写的方式显示所有员工的姓名

select upper(substr(ename,1,1))||lower(substr(ename,2,length(ename)-1)) from emp;

？以首字母小写的方式显示所有员工的姓名

select lower(substr(ename,1,1))||upper (substr(ename,2,length(ename)-1)) from emp;

replace(char1,search string ，replace string)

instr(char1,char2,[,n[,m]])取子串在字符串的位置

？显示所有员工的姓名，用”我是a”替换所有”A”

Select replace (ename, ”A”, ”我是a”) from emp;

Round(n,[m]) trunk(n,[m]) mod(m,n)

Floor(n) ceil(n)

日期函数

(1)sysdate:系统时间 select sysdate from dual;

(2)add_months(d,n)

查找已经入职8个月多的员工select * from emp from emp where sysdate>add_months(hiredate,8);

?显示满10年服务年限的员工的姓名和雇佣日期。

select * from emp from emp where sysdate>add_months(hiredate,12*10);

？对于每个员工，显示其加入公司的天数

Select sysdate-hiredate “入职天数” from emp

Select sysdate-hiredate “入职天数” from emp

Select trunk(sysdate-hirdate “入职天数” ，ename from emp;

?找出各月倒数第3天手雇佣的所有员工。

Select hiredate , last_day(hiredate) from emp;

Select hiredate ename from emp where last_day(hiredate)-2=hiredate;

转换函数to_char : 用于将字符串转换成date类型的数据

?日期是否可以显示时分秒

？薪水是否可以显示指定的货币符号

select ename， to_char(hiredate,’yyyy-mm-dd hh24:mi:ss’) from emp;

select ename,to_char(hiredate,’yyyy-mm-dd hh24:mi:ss’), to_char(sal,’L99,999.99’) from emp;//数字每三位用逗号隔开

？显示1980年入职的所有员工

Select * from emp where to_char(hiredate,’yyyy’)=1980;

？显示所有12月份入职的员工

Select * from emp where to_char(hiredate,’mm’)=12;

系统函数 sys_context

1)terminal 2)language 3)db_name 4)nls_date_format 5)session_user

6)current_schema 7)host

通过该函数，可以查询一些重要信息，比如你怎在使用那个数据库？

Select sys_context(‘userenv’,’db_name’) from dual;

Select sys_context(‘userenv’,’db_name’)from dual;

Select sys_context(‘userenv’,’language’)from dual;

Select sys_context(‘userenv’,’ current_schema’) from dual;

方案和用户的关系* 当你创建一个用户，oracle自动给你创建一个方案，

方案：表、视图、包、函数、触发器、存储过程、

Dba （数据库管理员） 安装和升级oracle数据库 建库，表空间，表，视图，索引 制定并实施备份与恢复计划 数据库权限管理，调优，故障排除 对于高级dba,要求能参加与项目开发，会编写sql语句，存储过程，规则，约束，包

管理员数据库的用户：sys和system sys董事长 system 总经理

Sys:拥有dba,sysdba,sysoper（系统操作员）角色或者权限，是oracle权限最高的用户。所有的数据字典的基表和视图都放在sys用户中，由数据库自己维护，任何用户不能手动更改。

 

Conn system/login

Conn system/login as sysdba

sysdba和sysoper权限区别图

 

Shutdown; //oracle例程已启动

Startup select * from emp;

Show parameter;//参数

数据库表的逻辑备份与恢复

Import

导出 分三种类型：导出表，导出方案，导出数据库 exp 命令该命令常用选项：userid: 用于指定执行导出操作的用户名，口令，连接字符串

Tables: 用于指定执行导出操作的表

Owner:用于指定执行导出操作的方案

Fully=y :指定执行导出操作的数据库

Inctype:指定执行导出操作的增量类型

Rows:用于指定执行导出操作是否要导出表中的数据

File:用于指定到文件

导出表 分两种：（1）exp userid=scott/tiger@myora tables=(emp,dept) file=d:\e1.dmp;

特别说明：在导入和导出的时候， 要到oracle目录的bin目录下。

（2）导出其它方案的表 D:\Administrator\product\11.2.0\dbhome_1\BIN

如果用户要导出其他方案的表，需要dba的权限或是exp_full_database 的权限，比如system 就可以导出scott的表 exp userid=system/manager@myoral tables=(scott.emp) file=d:\e2.dmp

exp userid=system/login@oral tables=(scott.emp) file=d:\e2.dmp

导出表的结构

（1）exp userid=system/manager@myoral tables=(scott.emp) file=d:\e2.dmp rows=n;//不要数据，要表结构。

（2）使用直接导出方式 exp userid=scott/tiger@orcl tables=(emp) file=d:\e3.dmp direct=y; 这种方式比默认的常规方式速度要快，当数据量大时，可以考虑使用这样的方法 这时需要数据库的字符集要与客户端字符集完全一致，否则会报错。。。。

导出方案 export 工具导出一个方案或者多个方案中的所有对象（表，索引，约束。。）和数据，并存放到文件中。

1. 导出自己的方案

Exp scott/tiger@orcl owner=scott file=d:\scott.dmp

(2) 导出其他方案

如果用户导出其它方案，则需要dba的权限或是exp_full_database 的权限，

Exp_full_databasede 权限，

例如system用户就可以导出任何方案 exp system/login@orcl owner=(system, scott) file=d:\system.dmp;

导出数据库 ：利用export导出所有数据库中的对象及数据，要求该用户具有dba的权限或者是exp_full_database权限expuserid=system/login@orcl full=y inctype=complete file=d:\\aaa.dmp

导入：3种方式

1. 导入自己表imp userid=scott/tiger@orcl tables=(emp) file=d:\xx.dmp
2. 导入表到其他用户 要求该用户具有dba的权限，或是imp_full_database

imp userid=system/login@orcl tables=(emp) file=d:\XX.dmp touser=scott

1. 导入表的结构 只导入表的结构而不导入数据

imp userid=scott/tiger@orcl tables=(emp) file=d:\xxx.dmp rows=n;

1. 导入数据 如果对象（如：比表 ） 已经存在可以只导入表的数据

imp userid=scott/tiger@orcl tables=(emp) file=d:\xx.dmp ignore=y;

导入方案

（1）导入自身方案 imp userid=system/login file=d:\xx.dmp;

(2)导入其他方案 要求该用户具有dba的权限

imp userid=system/login file=d:\xxx.dmp fromuer=system touser=scott;

(3)导入数据库

在默认情况下，当导入数据库时，会导入所有对象结构和数据，

Imp userid=system/manager full=y file=d:\xxx.dmp;

数据字典和动态性能视图

基表----->存放静态数据

动态视图----->动态数据

User_tables;当前用户-表 all_tables显示当前用户可以访问的所有表 dba_tables

Select username,password from dba_users 查询dba_users 可以显示所有数据库用户的详细信息； 查询数据字典视图dba_sys_privs,可以显示用户所具有的系统权限；

查询数据字典视图dba_tab_privs 可以显示用户具有的对象权限；查询数据字典视图dba_col_privs 可以显示用户具有的列权限；查询数据字典视图dba_role_privs 可以显示用户具有的角色；desc dba_role_privs;

Select * from dba_role_privs where GRANTEE=”scott”

角色-对应-权限1-1 或者1-多

//查询oracle中的所有的系统权限，一般是dba

Select * from system_privilege_map order by name;

//查询oracle中所有对象权限，一般是dba

Select distinct privilege from dba_tab_privs;

//查询数据库的表空间

Select tablespace_name from dba_tablespaces;

权限：对象权限 系统权限

Select * from dba_roles;oracle究竟有多少种角色。

一个角色包含的系统权限 select * from dba_sys_privs where grantee=’DBA’ 另外也可以这样查看select * from role_sys_privs where role=’connect’;

一个角色包含的对象权限 select * from dba_tab_privs where grantee=’connect’

如何查看某个用户，具有什么样的角色？

Select * from dba_role_privs where grantee=’用户’；

显示当前用户可以访问的所有数据字典视图

Select * from dict where comments like’%grant%’;

显示当前数据库的全称

Select * from global_name;

管理表空间和数据文件 表空间 存放 数据文件 表空间 区 段 块

表空间作用：（1）控制数据库占用的磁盘空间（2）dba 可以将不同数据类型部署到不同的位置，有利于提高i/o性能，同时利于备份和恢复等管理操作

Crate tablespace data01 datafile ‘d:\test\data01.dbf’ size 20m uniform size 128k

使用数据表空间

Create table mypart (deptno number(4),dname varchar2(14),loc varchar2(13)) tablespace data01;

1. 使表空间脱机 Alter tablespace users offline;
2. 使用表空间联机 alter tablespace users online;
3. 只读表空间 （当建立表空间时，表空间可以读写，如果不希望在该表空间上执行update，delete，insert操作，那么可以将表空间修改为只读alter tablespace 表空间名字 read only）

Select * from all_tables where tablespace_name=’表空间名’;//知道表空间名，显示该表空间包括的所有表

Select tablespace_name, table_name from user_tables where table_name=’EMP’;//知道表名，查看该表属于那个表空间

改变表空间的状态 使表空间可读写 alter tablespace 表空间名 read write；

删除表空间 drop tablespace ‘表空间’ including contents and datafiles;

说明：including contents 表示删除表空间时，删除该空间的所有数据库对象，而datafiles表示将数据库文件也删除。

扩展表空间三种方法：（1）增加数据文件 alter tablespace sp01 add datafile ‘d:\test\sp01.dbf’ size 20m (2)增加数据文件的大小 alter tablespace 表空间名 ‘d:\test\sp01.dbf’ resize 20m;注意：数据文件的大小不超过500m (3)设置文件的自动增长 alter tablespace表空间名 ‘d:\test\sp01.dbf’ autoextend on next 10m maxsize 500m;

移动数据文件步骤：1.确定数据文件所在的表空间 select tablespace_name from dba_data_files where file_name=’sp001.dbf’;------>2.使用表空间脱机 确保数据文件的一致性，将表空间转变为offline的状态。Alter tablespace sp01 offline;----->3.使用命令移动数据文件到指定的目标位置 host move d:\sp01.dbf c:\sp001.dbf ---->4.执行alter tablespace 命令 sql>alter tablespace sp01 rename datafile ‘d:\sp001.dbf’

To ‘c:\sp001.dbf’; ------->5.使表空间联机 sql>alter tablespace data01 online;

其他表空间：索引表空间 undo 表空间 临时表空间 非标准块的表空间

维护数据的完整性

约束：not null、 unique、 primary key、 foreign key、 check 五种。

Unique 唯一：不能重复，可以为空

Primary key(主键)：不能重复且不能为空。一张表最多只能有一个主键，但可以有多个unique约束。

Foreign key 外键：定义主表和从表之间的关系。

Check :用于强制行数据必须满足的条件，假定在sal列上定义了check约束，并要求sal列值在1000~2000之间如果不再，就会提示出错。

商店售货系统表设计案例

商品goods(商品号goodsId , 商品名goodsName， 单价unitprice ，商品类别category，供应商provider)；

客户customer （客户号customerId, 姓名name, 住址address， 电邮email ， 性别 sex, 身份证 CardId）;

购买purchase （客户号customerId, 商品号goodsId, 购买数量nums）;

请用sql语言完成下列功能：

1建表，在定义中要求声明：

1. 每个表的主外键；
2. 客户的姓名不能为空值；
3. 单价必须大于0，购买数量必须在1~30:；
4. 电邮不能重复；
5. 客户的性别必须是男 或者 女，默认是 男

Create table goods(goodsId char(0) primary key, goodsName varchar2(30), unitprice number(10,2) check(unitprice>0), category varchar2(8), provider varchar2(30));

CREATE table customer(customerId char(8) primary key,---主键 name varchar2(50) not null,---不为空 address varchar2(50), emaile varchar2(50) unique, sex char(2) default ‘男’ check (sex in(‘男’,’女’))， cardId char(18));

Create table purchase (customerId char(18) references customer(customerId),

goodsId char(8) references goods(goodsIs),

nums number(10) check (nums between 1 and 30));

商店售货系统表设计案例

如果在建表时忘记建立必要的约束，则可以在建表后使用alter table命令为表增加约束，但是注意：增加not null约束时，需要使用modify 选项，而增加其他四种约束使用add选项。

1. 每个表的主外码；
2. 客户的姓名不能为空值；-------增加商品名也不能为空
3. 单价必须大于0，购买数量必须在1到30之间；
4. 电邮不能够重复;-----增加身份也不重复
5. 客户的性别必须是 男或者女 ，默认是男
6. 增加客户的住址只能是’海淀’,’朝阳’,’东城’,’西城’,’通州’,’崇文’,

Alter table goods modify goodName not null;

Alter table customer add constraint cardunique unique(cardId);

Alter table customer add constraint addresscheck check(address in (‘东城’，’西城’))；

删除约束 alter table 表名 drop constraint 约束名称；

再删除主键约束的时候，可能有错误，比如：alter table 表名 drop primary key;

这是因为如果在两张表存在主从关系，那么再删除主表的主键约束，必须带上cascade 选项 比如：alter table 表名 drop primary key cascade;

显示约束信息 1.通过查询数据字典视图user_constraints,可以显当前用户所有的约束的信息。Select constraint_name,constraint_type,status,validated from user_constraints where table_name=’表名’；2.显示查询数据字典视图user_cons_columns,可以显示约束所对应的表列信息 select column_name, position from user_cons_columns where constrain_name=’约束’;

列级定义 如在department 表定义主键约束 create table department4 (dept_id number(2) constraint pk_department primary key, name varchar2(12),loc varchar2(12)); //在定义列的同时定义约束。

表级定义

表级定义是指在定义所有列后，再定义约束，这里需要注意：not null约束只能在列级上定义。以在建立employee2表时定义主键约束和外键约束为例：create table employee2 (emp_id number(4),name varchar2(15),dept_id number(2),

Constrain pk_employee primary key (emp_id),

Constrain fk_department foreign key (dept_id) references department4(dept_id));

 

索引缺点：1.占用空间大 2.更新比较麻烦

显示表的所有索引 先是create index nameindex on customer(name);

在同一张表上可以有多个索引，通过查询数据字典视图 dba_indexs和user_indexs, 可以显示索引信息。其中dba_indexs用于显示当前用户的索引信息：

Select index_name, index_type from user_indexs where table_name=’表名’；

显示索引列 通过查询数据字段视图user_ind_columns,可以显示索引所对应的列的信息 select table_name , column_name from user_ind_columns where index_name=’IND_ENAME’ ;

 

理解oracle的pl/sql概念

掌握pl/sql编程技术（包括编写过程、函数，触发器、、、、）

Java程序 数据库（过程，函数，触发器）

编写一个存储过程，该如何可以向某表中添加记录。

1.创建一个简单的表 create table mytest (name varchar2(30),passwd varchar2(30));

2.创建过程 create or replace procedure sp_prol is

begin

---执行部分

Insert into mytest values (‘韩顺平’，’m1234’);

End;

/

Replace:表示如果有sp_prol, 就替换

如何查看错误信息 ：show error ;

如何调用该过程

1. exec 过程名（参数值1，参数值2，、、、）；
2. cal l 过程名（参数值1，参数值2，、、、）；

exec sp_pro1;

select * from mytest;

用pl/sql创建过程中

create or replace procedure sp_pro2 is

begin

delete from mytest where name =’韩顺平’;

end;

/

Select * from mytest;

Exec sp_pro2;

重点：掌握sql语句的编写方法，还要掌握语法规则，可以使用变量和逻辑控制语句，编写分页存储过程模块、订单处理存储过程模块、转账存储过程模块

块（编程）-----过程 函数 触发器 包

编写规范

标识符号命名规范

1. 当定义变量时，建议用v_作为前缀v_sal
2. 当定义常量时，建议用c_作为前缀c_rate
3. 当定义游标时，建议用_cursor作为前缀emp_cursor;
4. 当定例外时，建议用e_作为前缀e_error

 

块由三部分构成：定义部分、执行部分、例外处理部分

Declear /*定义部分-----定义常量、变量、游标、例外、复杂数据类型*/

Begin /*执行部分-----要执行的pl/sql和sql语句*/

Exception /*例外处理部分-----处理运行的各种错误 */

End;

 

实例1-只包括执行部分的pl/sql块

Set severout put on ---打开输出选项

Begin

Dbms_output.put_line(‘hello’);

End;

相关说明：dbms_output是oracle所提供的包（类似java的开发包），该包包含一些过程，put_line就是dbms_output包的一个过程。

最简单的块

Begin

Dbms_output.put_line(‘hello,world’);

End;

/

Declare

V_ename varchar2(5);--定义字符串变量

Begin

Select 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 into v_ename from where empno=&aa;

Select ename, sal into v_ename, v_sal from where empno=&aa;//对应的顺序一致

--在控制台显示用户名

Dbms_output.put_line(‘用户名：’||v_ename || ‘工资：’ ||v_sal);

----异常处理

Exception

When no_data_found then

Dbms_output.put_line(‘朋友，你的编号输入有误！’);

End;

/

相关说明&地址符

实例3---包含定义部分、执行部分和例外处理部分

为了避免pl/sql程序的运行错误，应该对可能的错误进行处理，这个很有必要：

1. 如果输入不存在的雇员号,应当做例外处理.
2. 有时出现异常,希望用另外的逻辑处理

过程（存储过程）

实例4如下：

1. 请考虑编写一个过程，可以输入雇员名，工资 可修改雇员的工资
2. 如何调用过程有两种办法：
3. 如何在java程序中调用一个存储过程

---案例4

Create procedure sp_pro3(spName varchar2, newSal number) is

Begin—执行部分，根据用户名去修改工资

Update emp set sal =newSal whereename=spName;

End;

/

Exec sp_pro3(‘SCOTT’,4678);

Select * from emp;

如何用java调用？如何使用过程返回值？

CallableStatement cs = ct.prepareCall(“{call sp_pro3(？,?)}”);

Cs.setString(1,”SMITH”);

Cs.setInt(2,10);

 

----函数案例

---输入雇员的姓名，返回该雇员的年薪

Create function sp_fun2(spName varchar2) return number is yearSal number(7,2);

Begin –执行

Select sal * 12 +nvl(comm.,0)*12 into yearSal from where ename=spName;

Return yearSal;

End;

Show error;

在Java中调用该函数

Select annual_income(‘SCCOTT’) from dual;

可以通过rs.getInt(1)得到返回结果

 

Var abc_number;

Call sp_fun2(‘SCOTT’) into :abc

包：过程和函数

实例：

---创建包

----创建一个sp_package

---声明该包有一个过程update_sal

-----声明该包有有一个函数annual_income

create package sp_package is

Procedure update_sal(name varchar2,newsal number);

Function annual_income(name varchar2) return number;

End;

建立包体可以使用create package body 命令

-----给包sp_package 实现包体

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

Selecti sal*12+nvl(comm.,0) into annual_salary from emp

Where ename=name;

Return annual_salary;

End;

End;

/

Exec sp_package.update_sal(‘SCOTT’,120);

Select * from emp;

触发器：隐含执行的存储过程。Create trigger 来创建触发器

标量类型scalar

复合类型composite

参照类型(reference)

Lob(large object)

标量（scalar）--常用类型

Identifier:名称

Constant 常量

Datatype:数据类型

Not null : 指定变量不能为null

：= 给变量或是常量指定初始值

Default 用于指定初始值

Expr:指定初始值的表达式，可是文本值、其他变量、函数等

1. 定义一个变长字符串 v_name 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—使用标量

案例 以输入员工号，显示雇员姓名、工资、个人所得税（说率为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 empn=&no;

----计算所得税

V_tax_sal:=v_sal*c_tax_rate;

Dbms_output.put_line(‘姓名是：’||v_ename||’工资：’||v_sal||”交税”|| V_tax_sal);

End;

/

 

V_ename varchar2(5);变成v_ename emp.ename%type; 这样比较好！！

V_sal emp.sal%type;

复合变量（composite）---介绍

用于存放多个值得变量。主要包括：1.pq/sql记录2.pl/sql表3.嵌套表4.varray

类似与高级语言中的结构体

案例

----pl/sql记录实例

Declare

----定义一个记录类型emp_record_type,包含三个数据分别是name,salary,title

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 sq_record

From emp where empno=7788;

Dbms_output.put_line(‘员工名：’||sp_record.name ||’工资是：’||sp_record.salary);

End;

/

数组实例

Declare

--定义类型sp_table_type ,该类型用于存放emp.ename%type

----index by binary_integer 表示下标是整数

Type sp_table 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;

参照变量：游标标量ref cursor 和 对象类型变量ref obj_type

当时使用游标时（open时）需要指定select语句，这样一个游标就与一个select语句结合了。实例如下：

1. 请使用pl/sql编写一个块，可以输入部门号，并显示该部门所有员工姓名和他的工资。
2. 在1.基础上，如果某个员工的工资低于200元，就增加100元。

Declare

--定义游标类型

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 where deptno=&no;

--循环取出

Loop

Fetch test_cursor into v_ename, v_sal;

--判断工资高低，决定

--判断是否test——cursor为空

Exit when test_cursor%notfound;

Dbms_output.putline(‘名字’||v_ename||’工资’||v_sal);

End loop;

End;

 

Pl/sql的进阶

Oracle 的视图

Oracle 的触发器

掌握pl/sql的高级用法（能编写分页过程模块，下顶单过程。。。）

会处理oracle常见的例外

会编写oracle各种触发器

理解视图的概念并能灵活使用视图

If语句 循环语句 控制语句—goto和null;

Pl/sql 中提供了三种条件分支语句 if—then, if—then---else, if—then—elsif---else

简单条件判断if-then 编写一个过程，可以输入一个雇员名，如果该员工的工资低于2000，就给该雇员工资增加10%

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*10% Where ename=spName;

//sal=sal*1.1;

End if;

End;

/

Select * from emp；

Exec sp_pro6(‘SCOTT’);

Exec sp_pro6(‘FORD’);仍然是3000

二重条件分支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;

End;

多重条件分支 if—then –elsif—else

编写一个过程，可以输入一个雇员编号，如果该雇员的职位是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;

End;

/

Exec sp_pro6(7839);

Select * from emp;

循环loop end loop;结尾

案例：现有一张表users,表结构

请编写一个过程，可输入用户名，并循环添加10个用户到user表中，用户编号从1开始增加。

Create table users1(userNo number, userName varchar2(40));

 

Create or replace procedure sp_pro6(spName varchar2) is

---定义

V_num number:=1;

Begin

Loop

Insert into users1 values(v_num,spName);

--判断是否是要退出循环

Exit when v_num=10;

--自增

V_num:=v_num+1;

End loop;

End;

Desc users1;

Exec sp_pro6(‘你好’);

Select * from users1

循环语句-while循环 while loop开始，以end loop结束。

案例：现有一张表users,表结构如下

请，编写一个过程，可输入用户名，并循环添加10个用户到users表中，用户编号从11开始增加。

Create or replace procedure sp_pro6(spName varchar2) is

---定义:=11表示赋值

V_num number:=11;

Begin

While v_num number<=20 loop

--执行

Insert into users1values(v_num,spName);

V_num:=v_num+1;

End loop;

End;

/

For循环

Begin

For i in reverse 1..10 loop

Insert into users values(i,’顺平’);

End loop;

End;

/

顺序语句goto

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;

/

分页是任何一个网站(bbs,网上商城，blog)都会使用到的技术，因此学习pl/sql编程开发就一定要掌握该技术。

无返回值的存储

案例：现有一张表book，表结构

书号 书名 出版社

请编写一个过程，可以向book添加书，要求通过java程序调用该过程。

--建表book

Create table book (bookId number, bookName varchar2(50),publishhouse varchar2(50))

--编写过程

Create or replace sp_pro7(spBookId in number, spbokName in varchar2, sppublishHouse in varchar2) is

Begin

Insert into book values(spBookId,spbookName,sppublisHouse);

End;

--在Java中调用

创建CallableStatement调用存储过程。

CallableStatement cs=ct.prepareCall(“{call sp_pro7(?,?,?)}”);

Cs.setInt(1,10);

Cs.setString(2,”笑傲江湖”);

Cs.setString(3,”人民出版社”);

 

有返回值的存储过程（非列表）

案例：编写一个过程，可以输入雇员的编号，返回该雇员的姓名。

案例扩张：编写一个过程，可以输入雇员的编号，返回该雇员的姓名、工资、和岗位。

--有输入和输出的存储过程

Create or replace procedure sp_pro8(spno in number,spName out varchar2) is

Begin

Select ename into spName from emp where empno=spno;

End;

---在Java中输出 如何有返回值的过程

创建CallableStatement调用存储过程

CallableStatement cs = ct.prepareCall(“{call sp_pro8(?,?)}”);

Cs.setInt(1,7788);

Cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VAARCHAR);

//取出返回值要注意问号的顺序

String name=cs.getSring(2);

Syso(“7788的名字：”+name);

 

Create or replace procedure sp_pro8(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;

有返回值的存储过程（列表【结果集】）

案例：编写一个过程，输入部门号，返回该部门所有雇员信息。对该题分析如下：

由于oracle存储过程没有返回值，它的所有返回值都是通过out参数来替代的，列表也不列外，但由于是集合，所以不能用一般的参数，必须要用package了，所以要分两部分：

1. 建立一个包,再该包中，我定义类型test_cursor, 是个游标

create or replace testpackage as type test_cursor is ref cursor;

End testpackage;

/

1. 下面创建过程 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;

/

如何调用？

创建CallableStatement

CallableStatement cs=ct.prepareCall(”{call sp_pro9(?,?)}”);

给？赋值

Cs.setInt(1, 10);

Cs.registerOutParemeter(2,oracle.jdbc.OracleTypes.CUROR);

Cs.execute();

ResultSet rs=(ResultSet)cs.getObject(2);

While(rs.next()){

System.out.println(rs.getInt(1)+“ ” + rs.getSring(2));

}

编写分页过程

要求，编写一个存储过程，要求可以输入表名、每页显示记录数、当前页、返回总记录数，总页数，和返回结果集。

---orcacle的分页

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;

在分页时，大家可以把下面的sql语句当做一个模板使用

---开发一个包

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_sqlvarchar2(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 ||’;

End;’

----把游标和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(myprows,Pagesie)=0 then

myPageCount:=myrows/Pagesize;

else myPageCount:=myrows/Pagesize+1;

end if;

--关闭游标close p_cursor;

End;

/

Show error;

验证

 

 

Pl/sql例外处理

例外传递

案例，编写一个过程，可接受雇员的编号，并显示雇员的姓名

问题是，如果输入的雇员编号不存在，怎么去处理呢？

--例外案例

Declare

--定义

V_ename emp.ename%type;

Begin

 

Select ename into v_ename from emp where empno=&gno;

Dbms_out.put_line(‘名字’||v_ename);

Exception

When no_data_found then

Dbms_output.put_line(‘编号没有’);

End;

 

处理预定义

—case_not_fount

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

 

Cursor _already_open

预定义例外Dup_val_on_index 在唯一索引所对应的列上插入重复的值时，会隐含的触发例外dup_on_index例外

-begin

Insert into dept values(10,’公关部’，’北京’)；

-exception

When dup_val_on_index then

Dbms_outtput.put_line(‘在deptno列上不能出现重复值’);

End;

 

Invaild_vursor 当试图在不合法的游标 上执行操作时，会触发该例外

例如：试图从没有打开的游标提取数据，或是关闭没有打开的游标。则会触发该例外

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;

 

预定义例外invalid_number

Too_many_rows 当执行select into语句时，如果返回超过了一行，则会触发该例外。

处理自定义例外

？请编写一个pl/sql块，接收一个雇员的编号，并给该雇员工资增加1000元，如果该雇员不存在，请提示。

---自定义例外

-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

If sql%notfound then

Raise myex;

End if;

Exception

When myex then

Dbms_output.put_line(‘没有更新任何用户’)

End;

/

Exec ex _test(56);

视图与表的区别

1. 表需要占用磁盘空间，视图不需要
2. 视图不能添加索引
3. 使用视图可以简化 复杂查询

比如：学生选课系统

1. 视图有利于提高安全性

比如：不同用户可以查看不同视图

创建视图

Create view 视图名 as select 语句 【with read only】

创建或修改视图

Create or replace view 视图名 as select 语句【with read only】

删除视图

Drop view 视图名

当表结构过于复杂，请使用视图吧！

-----创建视图，把emp表sal<1000的雇员 映射到该视图

Create view myview as select * from emp where sal<1000;

---为简化操作，用一个视图解决 显示雇员编号，姓名，部门名称

Create view myview2 as select ?,? ,?, from emp,dept where emp.deptno=dept.deptno;

Create view myview2 as select emp.empno,emp.ename,dept.dname, from emp,dept where emp.deptno=dept.deptno;

 

在命令行方式运行SQLPlus即可解锁并修改密码：

sqlplus/nolog

SQL> conn sys as sysdba

输入口令:

已连接。

SQL> alter user scott identified by tiger account unlock;

用户已更改。

SQL> conn scott/tiger

已连接。

 

 

 

 

 

oracle连接不了 报ORA-12154:TNS:无法解析指定的连接标识符

 

首先到任务管理器---服务，把里面所有oracle的数据库和监听 给停掉。然后在 Net Configuration Assistant 里面把监听给删掉，然后在重建个监听。然后cmd里面 启动监听 lsnrctl start。

 

select * from dba_directories

create directory AUDIT_DPas 'D:\app\auditdb' ;

grant read,write on directory AUDIT_DP to AUDITUSER;

impdp AUDITUSER1/AUDITUSER1@ora11g_jn DIRECTORY=jndmp_dir DUMPFILE=AUDITUSER1_20160203.DMP REMAP_SCHEMA=AUDITUSER1:AUDITUSER1 remap_tablespace=KJHS_DATA:KJHS_DATA logfile=20160203.LOG

 

alter user audituser account unlock