Oracle基础

幸好，资料找回来了，不然亏大发了。

oracle --  Class.forName("oracle.jdbc.driver.OracleDriver");DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "tiger")
使用数据源的方式  添加数据源ORCL Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); Connection conn = DriverManager.getConnection("jdbc:odbc:ORCL","scott","tiger");
mysql  --
try {
   Class.forName("com.mysql.jdbc.Driver");
  } catch (ClassNotFoundException e) {
   e.printStackTrace();
  }
  
  try {
   Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/mydata?user=root&password=root");
  } catch (SQLException e) {
   System.out.println("SQLException:" + e.getMessage());
   System.out.println("SQLState:" + e.getSQLState());
   System.out.println("VendorError:" + e.getErrorCode());
  }

tomcat
JAVA_HOME
F:\Program Files\Java\jdk1.5.0_16
以下的 J2EE 应用程序已经配置, 并且可用下面列出的 URL 来访问。数据库配置文件已经安装到 C:\oracle\product\10.1.0,同时其他选定的安装组件也已经安装到 C:\oracle\product\10.1.0\db_1。Ultra Search URL:http://PC-20100527ZEGL:5620/ultrasearchUltra Search 管理工具 URL:http://PC-20100527ZEGL:5620/ultrasearch/adminiSQL*Plus URL:http://PC-20100527ZEGL:5560/isqlplusEnteprise Manager 10g Database Control URL:http://PC-20100527ZEGL:5500/em

######system默认的密码manager

conn 用户名/密码  该命令来切换用户
用system登陆oracle ,passw 修改密码
spool 可以将sql*plus屏幕上的内容输出到指定文件中去 sql>soppl d:\a.sql 并输入 sql>spool off
linesize   设置显示行的宽度，默认是80个字符 sql>show linesize  sql>set linesize 90
pagesize   分页    set pagesize 8;打印报表服务
create user root identifies by root;    必须是管理员权限才能创建用户，如果删除的用户创建了表，则需要带一个参数cascade
grant connect(角色) to root;     该命令赋予connect角色可登陆oracle
grant resource(角色) to root;     该命令赋予root用户可建表的权限。
##.希望root用户可以访问emp表
##.希望root用户可以scott中emp表进行查找。
grant select on emp to root;                   grant all on emp to root;     把emp表所有的权限给root
      select * from scott.emp;
######收回用户的权限(系统权限不级联回收,对象权限级联回收)
revoke  select on emp from root;
权限的维护
希望root用户可以把select权限继续给其他用户
##对象权限(传递)
grant select on emp to root with grant option;
##系统权限(传递)
grant connect to root with admin option;

？？如果scott把root对emp表的权限回收，那hejian会有怎么样？被收回
######使用profile管理用户口令(dba身份操作)
 #账户锁定
 创建profile文件
 create profile lock_account limit failed_login_attempts 3 password_lock_time 2
 删除profile文件
 drop profile lock_account cascade 
 alter user root profile lock_account;      //root用户登陆失败3次则锁定账号2天
 #账户解锁
 alter user root account unlock;
 #终止口令
 create profile myprofile limit password_life_time 10 password_grace_time 2;
 alter user root profile myprofile;   //每隔10天修改登陆密码，宽限期为2天 否则阻止登陆
 #口令历史
 create profile password_history limit password_life_time 10 password_grace_time 2 password_reuse_time 10
 password_reuse_time 10    //指定可重用时间，即10天后可重用

添加一个字段
 alter table student add(classId number(2))
修改字段的长度
 alter table student modify(xm varchar2(20))
修改字段的类型或者名字(不能有数据)
 alter table student modify(xm char(30))
 alter table student rename column sname to name;
删除一个字段
 alter table student drop column sal;
修改表的名字
 rename student to stu;
oracle默认的日期格式'DD-MON-YY'
 insert into student values(2,'hejian','02-7月-1990')
改日期的默认格式
 alter session set nls_date_format='yyyy-mm-dd'
 insert into student values(2,'hejian','1990-07-02');
##savepoint aa;
##rollback to aa;    可以恢复保存aa时保存点的数据，必须在commit之前才能，否则保存点会删除

truncate table student;//删除表数据，表结构还在，但不记录日志，不可回滚操作

#set timing on;//打开时间开关
#distinct
 select distinct sid from student;
使用列的别名
 select ename "姓名",sal*12 "年薪" from emp;
使用nvl函数来处理null值
 select ename "姓名",sal*12+nvl(comm,0) "年薪" from emp;   //comm为空值取0否则取其本身的值
查找1980.1.1后入职的员工
 select ename,hiredate from emp where hiredate > '1-1月-1982';
查找工资在2000到2500之间的员工
 select ename,sal from emp where sal between 2000 and 2500;
#like操作符
S开头的名字员工
 select ename from emp where ename like'S%';
第三个字符时O的员工
 select ename from emp where ename like'__O%'
#where中用in关键字
 select * from emp where empno in (7844,8917,8932);
#使用isnull的操作符
 select * from emp where mgr is null;
#使用逻辑操作符
？工资高于500或岗位为manager的雇员且姓名首写字母为J
 select * from emp where (sal > 500 or job = 'MANAGER') and ename like 'J%';
#使用order by
部门号升序而雇员的工资降序
 select * from emp order by deptno asc ,sal desc;
使用列的别名
 select ename,(sal+nvl(comm,0))*12 "年薪" from emp order by "年薪" ;

##查询语句里面有分组函数，则查询的列都必须是分组
 select ename ,max(sal) from emp;  //乂
？查询最高工资和最低工资的姓名和工资
 select ename ,sal from emp where sal in ((select max(sal) from emp),(select min(sal) from emp));
？高于平均工资
 select ename,sal from emp where sal > (select avg(sal) from emp);
#group by 和having子句
每个部门的平均工资和最高工资
 select avg(sal),max(sal),deptno from emp group by deptno;
每个部门的每种职位的平均工资和最低工资
 select deptno,job,avg(sal),min(sal) from emp group by deptno,job;
平均工资高于2000的部门号和平均工资
  select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;
##########
分组函数只能出现在选择列、having、orderby子句中
select语句中包含groupby，having，orderby，则顺序是groupby，having，orderby
##########

 

#######多表查询(原则是笛卡尔积)
？smith员工所在的部门名称
 select ename,dname from emp a1,dept a2 where a1.deptno=a2.deptno and ename = 'SMITH';
？员工的工资级别
 select ename,sal,grade from emp a1,salgrade a2 where a1.sal between a2.losal and a2.hisal;
######自连接(同一张表的链接)
？员工FORD的上级名字
 select worker.ename,boss.ename from emp worker,emp boss where worker.mgr = boss.empno and worker.ename='FORD';
######子查询
？查询SMITH同在一个部门的所有员工
 select * from emp where deptno = (select deptno from emp where ename='SMITH');
？查询10号部门工作岗位相同
 select * from emp where job in(select distinct job from emp where deptno = 10);
######多行子查询使用all操作符
？30号部门所有工资都高的员工
  select * from emp where sal > all(select sal from emp where deptno = 30); 
? 查询与smith的部门和岗位完全相同的所有雇员
  select * from emp where (deptno,job) = (select deptno,job from emp where ename='SMITH');
? 查询高于【自己部门】平均工资的员工信息
 select a1.deptno,a1.ename,a2.avg_sal from emp a1,(select deptno,avg(sal) avg_sal from emp group by deptno) a2 where a1.deptno=a2.deptno and a1.sal > a2.avg_sal;
######分页查询  有三种方式
#rownum分页的方式 这种方法比较容易理解
？查询6到10号的信息分下列3步执行

 
① select a1.*,rownum rn from (select * from emp) a1;
 
 RN EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
---------- ----- ---------- --------- ----- ----------- --------- --------- ------
 1  7369 SMITH      CLERK      7902 1980-12-17     800.00               20
 2  7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30
 3  7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30
 4  7566 JONES      MANAGER    7839 1981-4-2      2975.00               20
 5  7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30
 6  7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30
 7  7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10
 8  7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20
 9  7839 KING       PRESIDENT       1981-11-17    5000.00               10
10  7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00    30
11  7876 ADAMS      CLERK      7788 1987-5-23     1100.00              20
12  7900 JAMES      CLERK      7698 1981-12-3      950.00              30
13  7902 FORD       ANALYST    7566 1981-12-3     3000.00              20
14  7934 MILLER     CLERK      7782 1982-1-23     1300.00              10

② select rownum rn,a1.* from (select * from emp) a1 where  rownum<=10;     //备注:后面不能写rownum>=6，否则查不出任何信息，可能是oracle内置的机制

 RN EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
---------- ----- ---------- --------- ----- ----------- --------- --------- ------
1  7369 SMITH      CLERK      7902 1980-12-17     800.00               20
2  7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30
3  7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30
4  7566 JONES      MANAGER    7839 1981-4-2      2975.00               20
5  7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30
6  7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30
7  7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10
8  7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20
9  7839 KING       PRESIDENT       1981-11-17    5000.00               10
10 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30

③ select * from (select rownum rn,a1.* from (select * from emp) a1 where  rownum<=10) where rn>=6;//可用公式来用

 
 RN EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
---------- ----- ---------- --------- ----- ----------- --------- --------- ------
6  7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30
7  7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10
8  7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20
9  7839 KING       PRESIDENT       1981-11-17    5000.00               10
10 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30

这两种不好理解
  #根据ROWID来分
  #根据分析函数来分

######用查询结果创建新表
 create table mytable(id,name,sal,job,deptno) as select empno,ename,sal,job,deptno from emp;

######合并查询
union关键字(并集)
  select ename,sal,job from emp where sal >2500 union select ename,sal,job from emp where job='MANAGER';
union all(并集但不去掉重复行)

intersect(交集)

minus(差集)

SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
  2                    ENAME VARCHAR2(10),
  3                    JOB VARCHAR2(9),
  4                    MGR NUMBER(4),
  5                    HIREDATE DATE,
  6                    SAL NUMBER(7, 2),
  7                    COMM NUMBER(7, 2),
  8                    DEPTNO NUMBER(2));

SQL> INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK',    7902, TO_DATE('17-2-1980', 'DD-MM-YYYY'), 800, NULL, 20);

###### oracle函数

 #字符函数
  lower(char)  , upper(char) ,  length(char)  ,  substr(char,m,n) , replace(char,str1,str2) , chr(65)  , ascii('A')
#######oracle判断字段是否全是数字
select * from where regexp_like(字段名字,'^[0-9]+$');
? 首字母大写
 select upper(substr(ename,1,1))||lower(substr(ename,2,length(ename)-1)) from emp2;
 #数学函数
 round(n,[m])四舍五入  trunc(n,[m])截取到小数字m位   mod(m,n)求余 floor(n) 返回小于或等于n的最大整数即向下取整   ceil(n)大于或等于n的最小整数即向上取整
 #日期函数    默认日期格式是dd-mon-yy 即12-7月-90
 sysdate   返回系统时间
 add_months(d,n)   在d的时间上加上n个月
 last_day(d) 返回指定d日期所在月份的最后一天
? 查找8个月以前入职的员工
 select * from emp where sysdate > add_months(hiredate,8);
? 显示满10年服务年限的员工的姓名和受雇日期
 select * from emp where sysdate >= add_months(hiredate,120);
? 显示员工的入职天数
 select ceil(sysdate-hiredate) "入职天数" from emp;
? 各月倒数第三天受雇的所有员工
 select hiredate,ename from emp2 where hiredate = last_day(hiredate)-2;
 HIREDATE    ENAME
 ----------- ----------
 1981-9-28   MARTIN
 #转换函数
 to_char(date,'YYYY-MM-DD HH:MI:SS')

 select to_char(sysdate,'yy-mm-dd hh24:mi:ss')from dual;
 
 TO_CHAR(SYSDATE,'YY-MM-DDHH:MM
 ------------------------------
 10-10-03 07:10:14
  

 select to_char(99.22,'L9999.9999') from dual;  其中L代表本地货币
 TO_CHAR(99.22,'$9,999.9999')
 ---------------------------
    $99.2200

? 1980年入职的员工
 select * from emp2 where to_char(hiredate,'yyyy')=1980;
? 12月份入职的员工
 select * from emp2 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','language') from dual;  //当前使用的语言
 select sys_context('USERENV','current_schema') from dual; //方案
 ......
###### show parameter; //显示初始化参数

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

userid 用于指定执行导出操作的用户名，口令，连接字符串
tables 用于指定执行导出操作的表
owner 用于指定执行导出操作的方案
full=y 用于指定执行导出操作的数据库
inctype 用于指定执行导出操作的增量类型
rows 用于指定执行导出操作是否要导出表中的数据
file 用于指定导出文件名

 ##导入导出须在oracle安装的db_1下的bin目录下
     #导出自己的表
  exp userid=scott/tiger@orcl tables=(emp2) file=d:\emp2.dmp
 #导出其他方案的表
  exp userid=system/manager@orcl tables=(scott.emp2) file=d:\scott.emp2.dmp
 #导出表的结构不导出数据
  exp userid=scott/tiger@orcl tables=(emp2) file=d:\emp2.dmp rows=n
 #使用直接导出方式
  exp userid=scott/tiger@orcl tables=(emp2) file=d:\emp2.dmp direct=y
 ##导出方案
 #导出自己的方案
  exp scott/tiger@orcl owner=scott file=d:\scott.dmp
 #导出其他的方案(具有dba的权限或exp_full_database的权限)
  exp system/manager@orcl owner=(system,scott) file=d:\ss.dmp
 ##导出数据库(具有dba的权限或exp_full_database的权限)
  exp userid=system/manager@orcl full=y inctype=complete file=d:\db.dmp
######导入表
 
 userid 用于指定执行导出操作的用户名，口令，连接字符串
 tables 用于指定执行导出操作的表
 fromuser 用于指定源用户
 touser 用于指定目标用户
 full=y 用于指定执行导出操作的数据库
 inctype 用于指定执行导出操作的增量类型
 rows 用于指定执行导出操作是否要导出表中的数据
 file 用于指定导出文件名
 ignore 如果表存在，则只导入数据
  ##导入表
 #导入自己的表
  imp userid=scott/tiger@orcl tables=(emp2) file=d:\emp2.dmp 
 #导入自身的方案 
  imp userid=scott/tiger@orcl file=d:\scott.dmp
 #导入其他方案
  imp userid=system/manager file=d:\ss.dmp fromuser=system touser=scott
 #导入数据库
  imp userid=system/manager@orcl full=y file=d:\db.dmp

 

######数据字典
 #user_tables  显示当前用户所拥有的所有表
 select table_name from user_tables;
 #all_tables 当前用户可以访问的所有表
 select table_name from all_tables;
 #dba_tables  所有方案拥有的数据库表 必须拥有dba角色或者有select any table 系统权限

 #通过dba_users 可以显示所有数据库用户的详细信息
 select * from dba_users   (须具有dba的权限)
 #通过查询数据字典视图dba_sys_privs,显示角色具有的系统权限
 #通过查询数据字典视图dba_tab_privs，显示角色具有的对象权限
 #通过查询数据字典dba_col_privs,显示用户具有的列权限
 #通过查询数据库字典视图dba_role_privs,显示用户具有的角色

######查询oracle所有角色，一般是dba
 select * from dba_roles;
###查询oracle所有的系统权限，一般是dba
 select * from system_privilege_map by name;
###查询oracle所有对象权限，一般是dba
 select distinct privilege from dba_tab_privs;
###查询数据库的表空间
 select tablespace_name from dba_tablespaces;
###一个角色包含的系统权限
 select * from dba_sys_privs where grantee='CONNECT'
 或select * from role_sys_privs where role='CONNECT';
###一个角色包含的对象权限
 select * from dba_tab_privs where grantee='角色名';
###用户拥有的角色
 select * from dba_role_privs where grantee='SCOTT';
###显示当前用户可以访问的所有数据字典视图
 select * from dict where comments like '%grant%';
###显示数据库全名
 select * from global_name; 

######表空间
 ##创建表空间
 create tablespace hj001 datafile 'd:\hj001.dbf' size 20M uniform size 128K;
 ##使用表空间
 create table aaa(id number(2)) tablespace hj001;
 ##把表空间分配给用户
 alter user root default tablespace hj001;
 ##改变表空间的状态
  #使表空间脱机
 alter tablespace 表空间名 offline；
  #使表空间联机
 alter tablespace 表空间名 online；
  #让表空间只读
 alter tablesapce 表空间名 read only;
  #表空间可读可写
 alter tablespace 表空间名 read write;
 ##显示表空间所有的表
 select * from all_tables where tablespace_name='表空间名'
 ##查看表属于哪个表空间
 select tablespace_name,table_name from user_tables where table_name='EMP';

 ##删除表空
 drop tablespace '表空间' include contents and datafiles;
 ##扩展表空
  #增加数据文件
 alter tablespace hj001 add datafile 'd:\hj001.1.dbf' size 20M;
  #增加数据文件的大小
 alter tablespace hj001 'd:\hj001.dbf' resize 20M;   //文件大小不要超过500M
  #设置文件自增长
 alter tablespace hj001 'd:\hj001.dbf' autoextend on next 10M maxsize 500M;

 ##移动数据文件[以hj001为例]{假如磁盘损坏等原因需移动数据文件}
  #确定数据文件所在的表空间
 select tablespace_name from dba_data_files where file_name='d:\hj001.dbf';
  #使表空间脱机
 alter tablespace hj001 offline;
  #使用命令移动数据文件到指定的目标位置
 host move d:\hj001.dbf e:\hj001.dbf
  #移动数据执行alter tablespace命令
 alter tablespace hj001 rename datafile 'd:\hj001.dbf' to 'e:\hj001.dbf';
  #使表空间联机
 alter tablespace hj001 online;

 ##显示表空间信息
 select tablespace_name from dba_tablespaces;
 ##显示表空间包含的数据文件
 select file_name,bytes from dba_data_files where tablespace_name='表空间名';

######角色
  ##创建自定义角色【创建时没有任何权限】
 create role myrole not identified  //角色不需要验证。
 create role myrole identified by root  //角色需要验证
  ##删除角色
 drop role myrole;

#######存储过程
  ##编写一个存储过程，该过程可以向表添加记录
   #创建一个示例表
 create table test(name varchar2(20));
   #创建存储过程
 create [or replace] procedure hj_prol is
        begin--执行部分
 insert into test values('HJ');
 end;
 /
   #调用过程
 ①exec 过程名(参数1，参数2....)
 ②call 过程名(参数1，参数2....)
  ##查看错误
 show error;

##########  PL/SQL
  ##简单分类
        |------过程(存储过程)          
        |
        |------函数
  块(编程)------|
        |------触发器
        |           
        |------包
  ##编写规范
   #注释
 单行注释--
 多行注释/*.....*/
   #标志符号的命名规范
 当定义变量时，建议用V_作为前缀 v_sal
 当定义常量时，建议用c_作为前缀 c_rate
 当定义游标时，建议用_cursor作为后缀 emp_cursor;
 当定义例外时，建议用e_作为前缀 e_error

  ##块
 PL/SQL块由三个部分组成:定义部分、执行部分、例外处理部分
 declear
 /*定义部分-----定义常量、变量、游标、例外、复杂数据类型*/
 begin
 /*执行部分-----要执行的PL/SQL语句和sql语句*/
 exception
 /*例外处理部分-----处理各种错误*/

   #实例1最简单的块
 set serveroutput on     //打开输出选项
 begin
 dbms_output.put_line('Hello,World');
 end;
 /
 Hello,World             //输出Hello,World
   #实例2
 
 --实例2-包含定义部分和执行部分的pl/sql块
 declare
      v_ename varchar2(5);
 begin
      select ename into v_ename from emp where empno=&no;
  --控制台输出
      dbms_output.put_line('雇员名'||v_ename);
 end;
 /
 
   #实例3
 --实例3-包含定义部分、执行部分和例外处理
 declare
     v_ename varchar2(5);
 begin
     select ename into v_ename from emp where empno=&no;
     dbms_output.put_line('雇员名'||v_ename);
 exception
 when no_data_found then
 dbms_output.put_line('编号不存在');
 end;
 /
  ##过程
       --实例
 create or replace procedure hj_pro(name varchar2,nsal number) is
 begin
         update emp2 set sal = nsal where ename = name;
 end;
 call hj_pro('SCOTT',5555);
  ##函数 
 
 --函数案例
 --输入雇员的姓名，返回年薪
 create function hj_fun(name varchar2) return number is yearSal number(7,2);
 begin
  select sal*12+nvl(comm,0)*12 into yearSal from emp2 where ename=name;
  return yearSal;
 end;
 /

 SQL> var income number;
 SQL> call hj_fun('SCOTT') into:income;
 
 income
 ---------
 53340

 

  ##触发器(实现自增)
 #建立数据
 create table test(id number(4) primary key,
  name varchar2(20));

 #创建自动增长序列
  CREATE SEQUENCE users_Sequence  
   INCREMENT BY 1   -- 每次加几个    
       START WITH 1     -- 从1开始计数    
       NOMAXVALUE       -- 不设置最大值    
        NOCYCLE          -- 一直累加，不循环    
       CACHE 10; 
 #创建触发器
 CREATE TRIGGER users_Increase BEFORE  
 insert ON  users FOR EACH ROW  
 begin  
 select users_Sequence.nextval into:New.id from dual;  
 end; 

 #提交
 commit;

 

 

  ##包
 --创建包
 --创建一个包hj_package
 --声明该包有一个过程update_sal
 --声明该包有一个函数annual_income
 create package hj_package is
        procedure update_sal(name varchar2,nsal number);
        function annual_income(name varchar2) return number;
 end;
 --给包hj_package实现包体
 create package body hj_package is
 procedure update_sal(name varchar2,nsal number) is
  begin
   update emp2 set sal=nsal where ename=name;
  end;
 function annual_income(name varchar2) return number is annual_salary number;
  begin
   select sal*12+nvl(comm,0)*12 into annual_salary from emp2 where ename=name;
   return annual_salary;
  end;
 end;
       --调用包内的过程或函数
 call 包名.过程名或函数名  如: call hj_package.update_sal('SCOTT',5556);
  ##标量
   #标量赋值是用 :=来表示如   v_tax:=0.03
 --数日员工号显示雇员的姓名、工资、个人所得税(税率0.03.0
 declare
 v_ename varchar2(5);
 v_sal number(7,2);
 v_tax number(7,2); 
 c_tax_rate number(3,2):= 0.03;
 begin
            select ename,sal into v_ename,v_sal from emp where empno=&no;
            v_tax:=v_sal*c_tax_rate;
 dbms_output.put_line('姓名:'||v_ename||' 工资:'||v_sal||' 税收:'||v_tax);
 end;     
   #解决varchar2(5)溢出问题使用%type
 declare
 v_ename emm.ename%type;
 v_sal number(7,2);
 v_tax number(7,2); 
 c_tax_rate number(3,2):= 0.03;
 begin
            select ename,sal into v_ename,v_sal from emp where empno=&no;
            v_tax:=v_sal*c_tax_rate;
 dbms_output.put_line('姓名:'||v_ename||' 工资:'||v_sal||' 税收:'||v_tax);
 end;       
   #复合变量(composite)
 用于存放多个值的变量，主要包括①pl/sql记录②pl/sql表③嵌套表④varray

 --pl/sql记录实例(类似高级语言的结构体)
 

 declare
 --定义一个pl/sql记录类型emp_record_type,该类型包含name,salary,title三个数据
 type emp_record_type is record(
 name emp2.ename%type,
 salary emp2.sal%type,
 title emp2.job%type);
 --定义一个hj_record变量，变量的类型是emp_record_type -- v_name varchar2(5)
 hj_record emp_record_type;
 begin
  select ename,sal,job into hj_record from emp2 where empno=7900;
  dbms_output.put_line('姓名:'|| hj_record.name);
 end;
 /

 --pl/sql表实例 (相当于高级语言的数组，但下标可为负数)

 declare
 --定义pl/sql表类型 hj_table_type，该类型存放emp2.ename%type
 type hj_table_type is table of emp2.ename%type index by binary_integer;
 --定义hj_table变量，类型是hj_table_type
 hj_table hj_table_type;
 begin
          select ename into hj_table(0) from emp2 where empno=7900;
          dbms_output.put_line('姓名：'||hj_table(0));
 end;
 /

   #参照变量(cursor游标变量)

 --使用pl/sql编写块，输入部门号显示所有雇员的姓名和薪水
 declare
 v_ename emp2.ename%type;
 v_sal emp2.sal%type;
 --定义游标类型
 type hj_emp_cursor is ref cursor;
 --定义游标变量
 test_cursor hj_emp_cursor;
 begin
 --把test_cursor 和一个select结合
               open test_cursor for select ename,sal from emp2 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;

 --编写一个过程，输入雇员名，如果工资低于2000，则增加10%
 create procedure hj_pro1(name varchar2) is
 v_sal emp2.sal%type;
 begin
       select sal into v_sal from emp2 where ename=(name);
       if v_sal < 2000 then
       update emp2 set sal=sal*1.1;
       end if;
 end;
 
 call hj_pro1('SCOTT');

  ##pl/sql的进阶 控制结构
   #多重条件分支
 create procedure hj_pro2(no number) is
 v_sal emp2.sal%type;
 v_job emp2.job%type;
 begin
       select job into v_job from emp2 where empno=no;
       if v_job='PRESDIENT' then
          update emp2 set sal=sal+1000 where empno=no;
       elsif v_job='MANAGER' then
          update emp2 set sal=sal+500 where empno=no;
       else
          update emp2 set sal=sal+100 where empno=no; 
       end if;
 end;
   #循环语句

    -loop

 --编写过程，输入用户名，循环添加10个用户到users表中，编号1号开始
 create or replace procedure hj_pro2(name varchar2) is
 v_num number:=1;
 begin
       loop
                 insert into users values(v_num,name);
                 exit when v_num=10;
                 v_num:=v_num+1;
       end loop;
 end;
 /

   -while

 create or replace procedure hj_pro3(name varchar2) is
 v_num number:=11;
 begin
       while v_num<=20 loop
                 insert into users values(v_num,name);
                 v_num:=v_num+1;
       end loop;
 end;

 
  #有返回值的存储过程
 --输入员工的编号返回员工的姓名
 create or replace procedure hj_pro4 (inno in number, outputName out varchar2) is
 begin
        select ename into outputName from emp2 where empno=inno;
 end;
 
 java调用的过程

  Connection conn = null;
  CallableStatement cstmt = null;
  try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
   conn = DriverManager.getConnection(
     "jdbc:oracle:thin:@127.0.0.1:1521:ORCL", "scott", "tiger");
   cstmt = conn.prepareCall("{call hj_pro4(?,?)}");
   cstmt.setInt(1, 7788);
   cstmt.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
   cstmt.execute();
   System.out.println(cstmt.getString(2));
  } catch (ClassNotFoundException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }finally{
   try {
    conn.close();
    cstmt.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }
 --返回结果集
 

 create or replace package testpack as
 type test_cursor is ref cursor;
 end;
 create procedure hj_pro5(inno in number,v_cursor out testpack.test_cursor) is
 begin
        open v_cursor for select * from emp2 where deptno=inno;
 end;

 java调用的过程

  Connection conn = null;
  CallableStatement cstmt = null;
  ResultSet = null;
  try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
   conn = DriverManager.getConnection(
     "jdbc:oracle:thin:@127.0.0.1:1521:ORCL", "scott", "tiger");
   cstmt = conn.prepareCall("{call hj_pro4(?,?)}");
   cstmt.setInt(1, 7788);
   cstmt.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
   cstmt.execute();
   rs = (ResultSet) cstmt.getObject(2);
   while(rs.next()){
    System.out.println("EMPNO:"+rs.getString("EMPNO"));
   }
  } catch (ClassNotFoundException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }finally{
   try {
    conn.close();
    cstmt.close();
    rs.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }

 --分页
 create or replace procedure hj_pro6(tableName in varchar2,
 pSize in number,
 pNow in number,
 myrows out number,
 pagecounts out number,
 v_cursor out testpack.test_cursor) is
 v_sql varchar2(1000);
 v_start number:=(pNow-1)*pSize+1;
 v_end number:=pNow*pSize;
 begin
       v_sql:='select * from (select a1.*,rownum rn from (select * from '||tableName||') a1 where rownum <='||v_end||') where rn >='||v_start;
       open v_cursor for v_sql;
       v_sql:='select count(*) from '||tableName;
       execute immediate v_sql into myrows;
       pagecounts:=(myrows-1)/pSize+1;
 end;
 java调用的过程

  Connection conn = null;
  CallableStatement cstmt = null;
  ResultSet = null;
  try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
   conn = DriverManager.getConnection(
     "jdbc:oracle:thin:@127.0.0.1:1521:ORCL", "scott", "tiger");
   cstmt = conn.prepareCall("{call hj_pro6(?,?,?,?,?,?)}");
   cstmt.setString(1, "EMP2");
   cstmt.setInt(2, 5);
   cstmt.setInt(3, 2);
   
   cstmt.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER);
   cstmt.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER);
   cstmt.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);
   cstmt.execute();
   System.out.println("共"+cstmt.getInt(5)+"页");
   System.out.println("共"+cstmt.getInt(4)+"条记录");
   rs = (ResultSet) cstmt.getObject(6);
   while(rs.next()){
    System.out.println("EMPNO:"+rs.getString("EMPNO") +"姓名:" + rs.getString("ENAME"));
   }
  } catch (ClassNotFoundException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }finally{
   try {
    conn.close();
    cstmt.close();
    rs.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }

   #例外处理
 case_not_found
 cursor_already_open
 dup_val_on_index     //插入重复值
 invalid_cursor
 invalid_number      //输入数字有误
 no_data_found
 too_many_rows
 zero_divide
 value_error          //超过了容量等等
   #自定义例外处理
 --自定义例外
 create or replace procedure hj_pro7(no number) is
 myexcep exception;
 begin
         update emp2 set sal=sal+1000 where empno=no;
         if
                sql%notfound then
                raise myexcep;
         end if;
         exception
             when myexcep then
             dbms_output.put_line('无用户可更新');                                     
 end;

 

 

 

 

 

 

 

 

 

 

 

 

sqlplus sys/tiger as sysdba
alter user scott account unlock;
grant create table,create view to scott;
ename = ename||'$' →ename = ename$

 

 

选择是控制的字段 is null; 比如成绩：score is null;
不是空值 is not null；
in ('king','divad');
function: substr(,,),chr(65),ascii('A'),round(23.444,2);
to_char(num,'$99,999.9999');
to_char(date,'YYYY-MM-DD HH:MI:SS');
to_number('$1,234.00','$9,999.99');
nul(num,0);avg(),count(),sum();

DML:UPDATE ,SELECT ;DCL:GRANT TO ; DDL:CREATE ;
出现在select里的字段没有出现在组函数里就必须出现在group by 里面。
过滤后having 分组；
判断字段是否符合过滤后，如果过滤只有单一的值就=号，如果有多个就写in；
求部门平均薪水的等级
select deptno,avg_sal grade from
(select deptno,avg(sal) avg_sal from emp group by deptno) t
join salgrade s on(t.avg_sal between s.losal and s.hisal);
求部门平均的薪水等级
select deptno,avg(grade) from
  (select deptno,ename,grade from emp join salgrade s on (emp.sal between s.losal and hisal)) t
group by deptno;

雇员中哪些是经理
select ename from emp where empno in(select distinct mgr from emp);
不用组函数求最高薪水
select distinct sal from emp where sal not in
  (select distinct e1.sal from emp e1 join emp e2 on (e1.sal < e2.sal));
平均薪水最高的部门编号
select deptno,avg_sal from
  (select avg(sal) avg_sal,deptno from emp group by deptno)
where avg_sal =
  (select max(avg_sal) from
     (select avg(sal) avg_sal,deptno from emp group by deptno));
平均薪水最高的部门名称
select dname from dept where deptno =
(
select deptno from
  (select avg(sal) avg_sal,deptno from emp group by deptno)
where avg_sal =
  (select max(avg_sal) from
     (select avg(sal) avg_sal,deptno from emp group by deptno))
);
平均薪水的等级最低的部门的部门名称
select dname,t1.deptno,grade,avg_sal from
  (
  select deptno,grade,avg_sal from
    (select deptno,avg(sal) avg_sal from emp group by deptno) t
  join salgrade s on (t.avg_sal between s.losal and s.hisal)
  ) t1
join dept on (t1.deptno = dept.deptno)
where t1.grade =
(
  select min(grade) from
  (
    select deptno,grade,avg_sal from
      (select deptno,avg(sal) avg_sal from emp group by deptno) t
    join salgrade s on (t.avg_sal between s.losal and s.hisal)
  )
)

比普通员工最高薪水还要高的经理人名称
select ename from emp
  where empno in(select distinct mgr from emp where mgr is not null)
and
  sal >
 (
 select max(sal) from emp where empno not in
   (select distinct mgr from emp where mgr is not null))
);

为数据库建立用户
 1-- backup scott
       exp
 2--create user
      create user hejian identified by hejian default tablespace users quota 10M on users       
      grant create session ,create table,create view to zhengping
3-- import the data
      imp    

create table stu(id number(6),
  name varchar(10),
  email varchar2(50) unique)
insert into stu values('b',null),
insert into stu values('b',null);不出错，因为null不认为是相同。

create table stu(id number(6),
  name varchar(10),
  class number(4) references class(id),   //参考class表,也可写在表尾
  //constraint stu_class_fk foreign key(class) references class(id),
  email varchar2(50) unique,
  constraint stu_name_emali_uni unique(name,email)  //放在最后
  )
create table class(
  id number(4) primary key,   //被参考值必须是主键
  ......
  )

说的是插入的名字和email的组合不能相同，
若再插入和上次一样的值则出错。

insert into stu (name,email) values('b',null),
insert into stu (name,email) values('b',null);出错

create sequence seq;
select seq.nextval from dual;

 

 

PL_SQL:

declare
 v_name number :=0;
begin
 v_name :=2/v_name;
 dbms_output.put_line(v_name);
exception
 when others then
   dbms_output.put_line('error');
end;

--变量声明，使用%type属性
v_empno number(4);
v_empno emp.empno%type;

--record变量类型
declare
type type_record_dept is record  //type打头声明一种新的类型
 (
   deptno dept.deptno%type, 
     dname dept.dname%type,
     loc dept.loc%type
 );
 v_temp type_record_dept;
begin
 v_temp.deptno :=50;
 v_temp.dname :='aaaa';
 dbms_output.put_line(v_temp.deptno|| ''||v_temp.dname);
end;

--使用%rowtype声明record变量
declare
 v_temp dept%rowtype;
begin
 v_temp.deptno :=50;
 v_temp.dname :='aaaa';
 dbms_output.put_line(v_temp.deptno|| ''||v_temp.dname);
end;
--Table 变量类型
 type type_table_emp_empno is table of emp.empno%type index by binary_integer;
 v_empnos type_table_emp_empno;
begin
 v_empnos(0) :=7369;
 v_empnos(2) :=7893;
 v_empnos(-1) := 9999;
dbms_output.put_line(v_empnos(-1));
end;    //相当于Java的数组，不过数组中间是0，两边是正负的数；

--PLSQL语句的运用
PLSQL的select语句必须和into使用并且返回且只返回一条记录

declare
 v_ename emp.ename%type;
 v_sal emp.sal%type;
begin
 select ename,sal into v_name,v_sal from emp where depno = 20;
 dbms_output.put_line(v_name||''||v_sal);
end;

 

begin
 execute immediate 'create table t (nnn varchar2(20) default''aaa'')';
end;

--if语句
declare
 v_sal emp.sal%type;
begin
 select sal into v_sal from emp
  where empno = 50;
 if(v_sal <1200) then
  dbms_output.put_line('low');
 elsif(v_sal <2000) then    //语法中elsif 中间没有e
  dbms_output.put_line('middle');
 else
  dbms_output.put_line('high');
 end if;
end;

--循环
declare
 i binary_integer :=1;
begin
 loop
  dbms_output.put_line(i);
  i :=i+1;
  exit when (i>=11);
 end loop;
end;

begin
 for k in 1 ..10 loop
  dbms_output.put_line(k);
 end loop;

 for k in reverse 1..10 loop
  dbms_output.put_line(k);
 end loop;
end;

--游标
declare
 cursor c is
  select * from emp;
 v_emp c%rowtype;
begin
 open c;
 loop 
 fetch c into v_emp;
 exit when (c%notfound);
   dbms_output.put_line(v_emp.ename);
 end loop;
 close c;
end;

declare
 cursor c is
  select * from emp;
begin

 for v_emp in c loop
   dbms_output.put_line(v_emp.ename);
 end loop;
end;

declare
 cursor c
 is
   select * from emp2 for update;
 --v_temp c%rowtype;
begin
 for v_temp in c loop
   if(v_temp.sal<2000) then
     update emp2 set sal = sal*2 where current of c;
   elsif(v_temp.sal = 5000) then
     delete from emp2 where current of c;
   end if;
 end loop;
 commit;
end;

--存储过程
如果以后还想执行此程序，则可存储为procedure p 但是没有编译，要运行p
可用exec p； 或者 begin p；
create or replace procedure p
is
 cursor c
 is
   select * from emp2 for update;
 --v_temp c%rowtype;
begin
 for v_temp in c loop
   if(v_temp.sal<2000) then
     update emp2 set sal = sal*2 where current of c;
   elsif(v_temp.sal = 5000) then
     delete from emp2 where current of c;
   end if;
 end loop;
 commit;
end;

注意：在Oracle中，如果存储过程中有语法错误，这个过程依然会创建。
create or replace procedure p
 (v_a in number,v_b number,v_ret out number,v_temp in out number)
      ↓
   默认是in   表示的是输入变量

is
begin
 if(v_a  > v_b) then
   v_ret :=v_a;
 else
   v_ret :=v_b;
 end if;
 v_temp :=v_temp + 1;
end;

调用过程
declare
 v_a number := 3;
 v_b number := 4;
 v_ret number ;
 v_temp number :=5;
begin
 p(v_a,v_b,v_ret,v_temp);
 dbms_output.put_line(v_ret);
 dbms_output.put_line(v_temp);
end;

--函数
create or replace function sal_tax
 (v_sal number)
 return number
is
begin
 if(v_sal < 2000) then
  return 0.10;
 elsif(v_sal < 2750) then
  return 0.15;
 else
  return 0.20;
 end if;
end;

select lower(ename),sal_tax(sal) from emp

--触发器
  //触发器必须依附在一张表上

create table emp2_log
 (
   uname varchar2(20),
   action varchar2(10),
   atime date
);

create or replace trigger trig
 after insert or delete or update on emp2 for each row
begin
 if inserting then
  insert into emp2_log values(USER,'insert',sysdate);
 elsif updating then
  insert into emp2_log values(USER,'update',sysdate);
 elsif deleting then
  insert into emp2_log values(USER,'delete',sysdate);
 end if;
end;

create or replace trigger trig
 after update on dept
 for each row
begin
 update emp set deptno = :NEW.deptno where deptno = :OLD.deptno;
end;
则可以执行
 set serveroutput on;
 update dept set deptno = 99 where deptno = 10;

--树状结构的存储与展示
create table article
(
 id number primary key,
 cont varchar2(4000),
 pid number,
 isleaf number(1),      --0代表非叶子节点，1代表叶子节点
 alevel number(2)
);

insert into article values(1,'蚂蚁大战大象',0,0,0);
insert into article values(2,'大象被打趴下了',1,0,1);
insert into article values(3,'蚂蚁也不好过',2,1,2);
insert into article values(4,'瞎说',2,0,2);
insert into article values(5,'没有瞎说',4,1,3);
insert into article values(6,'怎么可能',1,0,1);
insert into article values(7,'怎么没有可能',6,1,2);
insert into article values(8,'可能性很大',6,1,2);
insert into article values(9,'大象进医院了',2,0,2);
insert into article values(10,'护士是蚂蚁',9,1,3);
commit;

create or replace procedure p(v_pid article.pid%type , v_level binary_integer) is
  cursor c is select * from article where pid = v_pid;
  v_preStr varchar2(1024) :='';
begin
  for i in 0 ..v_level loop
 v_preStr :=v_preStr || '####';
  end loop;
  for v_article in c loop
    dbms_output.put_line(v_preStr || v_article.cont);
    if(v_article.isleaf = 0) then
      p(v_article.id,v_level+1);
    end if;
  end loop;
end;

 

###手动解决乱码问题(只能解决post提交方式的乱码)

package com.hj.drp.util.filter;

import java.io.IOException;

import javax.servlet.Filter;
import javax.servlet.FilterChain;
import javax.servlet.FilterConfig;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;

public class CharsetEncodingFilter implements Filter {

 private String encoding;
 public void destroy() {
  // TODO Auto-generated method stub

 }

 public void doFilter(ServletRequest servletRequest, ServletResponse servletResponse,
   FilterChain filterChain) throws IOException, ServletException {
  //设置字符集
  //只对post起作用
  servletRequest.setCharacterEncoding(this.encoding);
  filterChain.doFilter(servletRequest, servletResponse);
 }

 public void init(FilterConfig filterConfig) throws ServletException {
  // TODO Auto-generated method stub
  this.encoding = filterConfig.getInitParameter("encoding");
 }

}

 web.xml配置
 <filter>
  <filter-name>CharsetEncodingFilter</filter-name>
  <filter-class>
   com.hj.drp.util.filter.CharsetEncodingFilter
  </filter-class>
  <init-param>
   <param-name>encoding</param-name>
   <param-value>GBK</param-value>
  </init-param>
 </filter>

 <filter-mapping>
  <filter-name>CharsetEncodingFilter</filter-name>
  <url-pattern>*.jsp</url-pattern>
 </filter-mapping>
 <filter-mapping>
  <filter-name>CharsetEncodingFilter</filter-name>
  <url-pattern>/servlet/*</url-pattern>
 </filter-mapping>

###手动解决get提交乱码问题
修改tomact的server.xml中的
  <Connector port="80" maxHttpHeaderSize="8192" URIEncoding="GB18030"
               maxThreads="150" minSpareThreads="25" maxSpareThreads="75"
               enableLookups="false" redirectPort="8443" acceptCount="100"
               connectionTimeout="20000" disableUploadTimeout="true"/>

在程序中使用java.net.URLEncoder.encode(//此处添加乱码中文);

 

 

 

 

 

###404和500错误处理
 在web.xml中加
 <error-page>
  <error-code>404</error-code>
  <location>/error.jsp</location>
 </error-page>
 
 <error-page>
  <error-code>500</error-code>
  <location>/error.jsp</location>
 </error-page>

 error.jsp代码
 <%@ page language="java" contentType="text/html; charset=GB18030" pageEncoding="GB18030"%>
 <%
  Integer errorCode = (Integer)request.getAttribute("javax.servlet.error.status_code");
  //System.out.println("http_error=========" + errorCode);
  if (errorCode == 404) {
   response.sendRedirect(request.getContextPath() + "/404.html"); 
  }else if (errorCode == 500) {
   response.sendRedirect(request.getContextPath() + "/500.html");
  }
 %>

 

###在web.xml使用全局属性
 <context-param>
  <param-name>pageSize</param-name>
  <param-value>8</param-value>
 </context-param>
 ##在servlet取
 Int pageSize = Integer.parseInt(request.getSession().getServletConfig().getInitParameter("pageSize"));
 ##在jsp中取值
 pageSize = <%=this.getServletContext.getInitParameter("pageSize") %>

 

 

 

 

 

以下的 J2EE 应用程序已经配置, 并且可用下面列出的 URL 来访问。数据库配置文件已经安装到 E:\oracle\product\10.1.0,同时其他选定的安装组件也已经安装到 E:\oracle\product\10.1.0\Db_1。Ultra Search URL:http://PC-20091227UYNA:5620/ultrasearchUltra Search 管理工具 URL:http://PC-20091227UYNA:5620/ultrasearch/adminiSQL*Plus URL:http://PC-20091227UYNA:5560/isqlplusEnteprise Manager 10g Database Control URL:http://PC-20091227UYNA:5500/em

6个运行级别
 
# 0 - 停机（千万不要把initdefault设置为0 ）
# 1 - 单用户模式
# 2 - 多用户，但是没有NFS
# 3 - 完全多用户模式
# 4 - 没有用到
# 5 - X11
# 6 - 重新启动（千万不要把initdefault设置为6 ）
# 对各个运行级的详细解释：
0 为停机，机器关闭。
1 为单用户模式，就像Win9x下的安全模式类似。
2 为多用户模式，但是没有NFS支持。
3 为完整的多用户模式，是标准的运行级。
 
5 就是X11，进到X Window系统了，图形界面。
6 为重启，运行init 6机器就会重启。

Linux  command
touch    //建立文件
service ipstables stop   //关闭防火墙
more  filename     //查看文本的内容
grep是linux下用于搜索输出内容中所含内容的一个命令
chkconfig    //查看服务  
vsftp ：允许root用户上传修改两个文件
gzip    //Linux下解压缩gz的命令 -d 解压
tar -xvf   //解压.tar的文件命令
ps -ef   //查看进程
rpm      //相当于windows下的添加删除   -qa 卸载   -ivh  安装

评论

1 楼 tojaoomy 2012-05-21  

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format