Oracle基础

tojaoomy

浏览: 94176 次
性别:
来自: 上海

最近访客更多访客>>

dkl187788

mosoft

liujingjing5

binxinyuwuhen

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

Oracle

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 安装

_oracle.rar (14.3 KB)
下载次数: 5

0
顶

2
踩

分享到：

免费的数据库建模工具 | 手把手教你写批处理

2012-03-03 17:27
浏览 1252
评论(1)
分类:数据库
查看更多

1 楼 tojaoomy 2012-05-21

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

发表评论

您还没有登录,请您登录后再发表评论

最近访客更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论

Oracle基础

评论

发表评论

相关推荐

最近访客 更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论

Oracle基础

评论

发表评论

相关推荐

丢失更新

最近访客更多访客>>