Oracle 基本操作

1. Oracle 10g安装：
全局数据库名：ORCL   SID:ORCL
OracleOraDb10g_home1TnsListerner:该服务启动数据库服务器的监听器，监听器接受来自客户端应用程序的连接请求，若监听器未启动，则客户端将无法连接到数据库服务器
OracleServiceOrcl:其中Orcl是数据库实例的SID，该服务启动系统标识符为Orcl的数据库实例。
OracleDBConsoleorcl:该服务启动OEM。
Oracle产品安装完成后，服务器和客户端都需要进行网络配置才能实现网络连接。
服务器端配置监听器listener.ora，客户端配置网络服务名tnsnames.ora。
服务器监听器文件listener.ora配置
    服务器端监听器配置信息包括监听协议、地址及其他相关信息。 配置信息保存在名为listener.ora的文件中。在安装服务器软件时自动配置一个监听器
客户端网络服务名tnsnames.ora文件配置
   客户端的网络服务名配置信息包括服务器地址、监听端口号和数据库SID等，与服务器的监听器建立连接。配置信息保存在名为tnsnames.ora的文件中
Oracle中的 Net Configuration Assistant和Net Manager工具都能用来配置监听器和网络服务名服务器监听器文件listener.ora配置
2. 创建数据库实例(可以通过DBCA创建数据库实例)
OEM:http://lsc:1158/em
3. 表空间
创建表空间：
CREATE TABLESPACE "JYSOFT"
DATAFILE 'D:\SOFTWARE\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\JYSOFT.DBF' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
create tablespace "test" datafile 'd:\test.dbf' size 100m autoextend on next 10m maxsize 1024m;
删除表空间：
drop tablespace jysoft including contents;
相应的数据文件oracle\product\10.2.0\oradata\orcl\jysoft.dbf手动删除
4. 用户
创建用户：
Create user jysoft identified by pass
Default tablespace jysoft;
删除用户：
Drop user jysoft cascade;
Alter user jysoft identified by pass;
5. 权限控制：
Grant resource,dba to jysoft;
Grant select on emp to jysoft;
Revoke select all emp from jysoft;
Revoke dba from jysoft;
6. 表
创建表：create table student
(
id number(4) not null,
stuno number(4) not null unique,
name varchar2(50),
score number(8,2),
birth date default sysdate,
pic blob,
remark clob
)
复制表：create table jysoft.emp as select * from scott.emp;
插入：insert into emp select * from scott.emp;
insert into student (id, stuno) values (2,2);
alter table student add (test number(4));
alter table student modify (test default 5555);
alter table student drop column test;
alter table student drop constraint pk_stu;

alter table student add (constraint stuno_uk unique(stuno));
alter table student add (constraint pk_stu primary key (ID));
alter table student add (constraint score_check check (score>=0 and score<=100));
drop table jysoft.emp;

7. 创建索引
create index birth_idx on student(birth);
drop index birth_idx;
8. 同义词：
create public synonym student for jysoft.student;
select * from student;
drop public synonym student;
9. 数据库链接：
create public database link jysoft_link connect to jysoft identified by pass using 'orcl';
select * from student@jysoft_link;
10. 创建序列器
create sequence student_id_s
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;
select student_id_s.nextval from dual;
drop sequence student_id_s;
11. 创建触发器(主键自增)
create or replace trigger student_id_t
before insert on student for each row
begin
select student_id_s.nextval into :new.id from dual;
end;
12. 创建视图
create view v_student as select stuno,name from student;
13. 系统命令
select rowid, rownum,t.* from student t;

SQL> variable srowid varchar2(20);
SQL> exec select rowid into :srowid from scott.emp t where empno=7369;
PL/SQL procedure successfully completed
srowid
---------
AAAMfPAAEAAAAAgAAA
SQL> select empno,ename from scott.emp t where rowid=:srowid;
EMPNO ENAME
----- ----------
7369 SMITH
srowid
---------
AAAMfPAAEAAAAAgAAA

insert into student(stuno,birth) values(2,TO_DATE('2010-01-01', 'YYYY-MM-DD'));
select stuno,to_char(birth,'yyyy-mm-dd hh:mm:ss') from student;

SQL> savepoint update1;
Savepoint created
SQL> update student set name='张三' where stuno = 1;
1 row updated
SQL> savepoint update2;
Savepoint created
SQL> update student set name='李四' where stuno = 2;
1 row updated
SQL> rollback to update2;
Rollback complete
SQL> commit;
Commit complete
张三没有更新，李四更新

SQL>SELECT orderno from order_master
Union/union all/intersect/minus
Select orderno from order_detail;
Intersect操作符只返回两个查询的公共行
Minus操作符返回从第一个积善余庆结果中排除第二个查询中出现的行
连接操作符：SQL>select ‘oracle’||’程序员’ from dual;
结果返回：oracle程序员
   函数    输入   输出
Initcap(char) Select initcap(‘hello’) from dual; Hello
Lower(char) Select lower(‘FUN’) from dual; fun
Upper(char) Select upper(‘sun’) from dual; SUN
Ltrim(char,set) Select ltrim( ‘xyzadams’,’xyz’) from dual; adams
Rtrim(char,set) Select rtrim(‘xyzadams’,’ams’) from dual; xyzad
Translate(char, from, to) Select translate(‘jack’,’j’ ,’b’) from dual; back
Replace(char, searchstring,[rep string]) Select replace(‘jack and jue’ ,’j’,’bl’) from dual; black and blue
Instr (char, m, n) Select instr (‘worldwide’,’d’) from dual; 5
Substr (char, m, n) Select substr(‘abcdefg’,3,2) from dual; cd
Concat (expr1, expr2) Select concat (‘Hello’,’ world’) from dual; Hello world

函  数  名 说    明
ADD_MONTHS（d,x） 返回日期d的月份加上x个月后的日期
SYSDATE 返回当前系统日期和时间
GREATEST(d1,d2) 比较两个日期d1和d2，返回其中较大的日期
LEAST(d1,d2) 比较两个日期d1和d2，返回其中较小的日期
LAST_DAY(d) 返回日期d所在月的最后一天的日期
MONTHS_BETWEEN(d1,d2) 返回两个日期d1和d2之间相差的月数
NEXT_DAY(d,day) 返回日期d后day所在的日期，day是指星期几
TO_DATE(d，’format’) 将日期型数据d,转换成以format指定形式的字符型数据
TO_CHAR(string,’format’) 将字符串string转换成以format指定形式的日期型数据

例 在指定日期上增加月份。
SQL>  SELECT ADD_MONTHS（’12-APR-07’,4）  FROM  DUAL;
例 比较两个日期，显示其中较大者的日期。
SQL> SELECT GREATEST（‘15-APR-07’，‘16-MAY-07’）  FROM  DUAL;
例 求某月的最后一天的日期。
  SQL> SELECT LAST_DAY（’10-AUG-07’）  FROM  DUAL;
例 求两个日期相差的月份数。
  SQL> SELECT MONTHS_BETWEEN（’20-JAN-07’，’13-MAY-07’）  FROM  DUAL;
例 将日期型数据转换为字符型数据。
  SQL> SELECT TO_CHAR（sysdate，‘DD-MONTH-YYYY’）  FROM  DUAL;
例 字符型数据转换为日期型数据。
  SQL> SELECT TO_DATE（’14-AUG-07’，‘DD-MONTH-YYYY’）  FROM  DUAL;
SELECT ADD_MONTHS(to_date('2010-01-01','yyyy-mm-dd'),4)  FROM  DUAL;

DBA用户权限操作内容：
select * from dba_tables where owner='SCOTT'; //区别大小定

 常用的主要参数
 LINESIZE和PAGESIZE
例 设置行宽为60，设置页的长度为30。
SQL>SET LINESIZE 60
SQL>SET PAGESIZE 30
 ECHO  设置在SQL*Plus的环境下执行命令文件时，命令是否显示有屏幕上
SQL> SET ECHO ON 命令本身显示在屏幕上。
SQL> SET ECHO OFF 命令本身不显示在屏幕上。
 PAUSE 设置在每页输出的开始处是否停止。
SQL> SET PAUSE ON 每页输出的开始处停止，按回车键后继续滚动
 TIME
SQL> SET TIME ON  表示在每个命令提示前显示当前时间。
 NUMFORMAT
SQL> SET NUMFORMAT 设置查询结果中显示数字的缺省格式。

SQL> select * from emp t where t.hiredate > to_date('&hdate','yyyy-mm-dd');
例  替换变量用于列名。
SELECT &Col_Name  FROM Employees;

例  替换变量用于表达式。
SQL>SELECT Name  FROM Employees WHERE  &var;

 双 & 符号替换变量
   重新使用某个变量并且不希望重新提示输入该值，可以使用双&符号变量 (&&)

  SQL>SELECT EmployeeID，Name，Address, &&Column
      FROM Employees
      ORDER BY &&Column DESC;

 VERIFY命令
SET VERIFY ON/OFF命令  设置是否显示执行替换的值，可以观察替换变量值前后的SQL语句。
设置值为ON，此功能可用，可以用来验证输入的值是否正确。
若设置值为OFF，该功能禁用。默认值为ON。

SQL>SET VERIFY ON
SQL>SELECT Name FROM Employees WHERE EmployeeID=&EMP_ID;
输入了EMP_ID的值后，系统显示该变量的新旧值。
如输入值000002,其输出结果如下：
Old 1：SELECT Name FROM Employees WHERE EmployeeCode=&EMP_ID;
New 1：SELECT Name FROM Employees WHERE EmployeeCode=000002;

前缀 范围
USER 用户拥有的视图
ALL 用户可访问的部分
DBA 数据库管理员视图
V$ 数据库运行参数


 *_TABLES : 用户创建的数据表
 *_INDEXES : 用户创建的索引
 *_OBJECTS : 用户创建的对象
 *_TAB_COLUMNS : 数据表的列信息

SQL> select t.tablespace_name,t.status,t.contents from dba_tablespaces t;//表空间
SQL> select t.file_name,t.tablespace_name,t.bytes from dba_data_files t;//数据文件
SQL> select t.username,t.password,t.created from dba_users t;//用户
SQL> select t.NAME,t.CREATED from V$database t;//数据库信息
SQL> select t.INSTANCE_NAME,t.HOST_NAME,t.VERSION from v$instance t;//实例信息
SQL> select * from v$version;
SQL> select * from v$controlfile;
PL/SQL查询数据库对象：正规表达式为：%通配符
V_$   v$?
select * from v_$version;
select * from v$version;

DICTIONARY（DICT）中可查到名称 //应如何使用？
列出DICT的结构： DESC DICT
查找控制文件的数据字典：
SQL> select * from dict where table_name like '%CONTROL%';
SQL> drop tablespace dmusertbs including contents and datafiles;

 命令行方式查看有关表空间信息借助数据字典视图或动态性能视图。如：V$TABLESPACE、DBA_TABLESPACES，USER_TABLESPACES, DBA_DATA_FILES等。
 作为system用户或一些其他有特权的用户登录，查询V$DATAFILE动态性能视图：


添加和移动控制文件
1) 修改参数文件initsid.ora的control_file参数
2) 正常关闭数据库
3) 将控制文件从当前位置移到新的位置
4) 启动数据库
5) 检查是否正确
6) 如正确，删除无用的旧控制文件
初始化参数文件：
 initsid.ora：初始化参数文件是一个ASCII文本文件，记录Oracle数据库运行时的一些重要参数，决定着数据库和实例的特性，如：共享池、高速缓存、重做日志缓存分配、后台进程的自动启动、控制文件的读取、为数据库指出归档日志的目标，自动联机回滚段等。

 服务器端二进制参数文件(SPFILE)，默认情况下使用服务器端参数文件启动实例，在Oracle9i中，初始化参数文件不仅可以在运行时修改，还可以通过scope选项决定修改过的参数值是只在本次运行中有效。
查看同义词：    DBA_SYNONYMS，ALL_SYNONYMS，USER_SYNONYMS视图
查看序列：      USER_SEQUENCES数据字典视图可查询序列的设置。

14. PL/SQL
[DECLARE]
  --declaration statements声明部分
BEGIN
---executable statements可执行部分
[EXCEPTION]
--exception statements异常处理部分
END

例1 用一个完整的PL/SQL块实现查询雇员号为7369的雇员信息。
declare
  p_sal number(7,0);
  p_name varchar2(10);
begin
  select sal,ename into p_sal,p_name
  from scott.emp
  where empno=7369;
  dbms_output.put_line('员工姓名:'||p_name||'      员工工资'||p_sal);
Exception
  when no_data_found then
  Dbms_Output.put_line('员工号不存在!');
end;

PL/SQL 语言的复合类型是用户定义的，常用的复合类型有属性、记录、表和数组。复合类型是标量类型的组合，使用这些数据类型可以拓宽应用范围。
（1）属性类型
属性用于引用数据库列的数据类型，以及表示表中一行的记录类型。属性类型有两种：
%TYPE  -  引用变量和数据库列的数据类型。
例:使用了％TYPE声明变量
  p_name scott.emp.ename%TYPE;

%ROWTYPE  -  提供表示表中一行的记录类型
例:使用％ROWTYPE声明变量
emp_ex emp%ROWTYPE;
该段代码声明了变量emp_ex,它可以用于存储从emp中提取的记录。

declare
  p_sal number(7,0);
  p_name scott.emp.ename%TYPE; --列类型
  emp_ex scott.emp %ROWTYPE; --行记录
begin
  select sal,ename into p_sal,p_name
  from scott.emp
  where empno=7369;
  dbms_output.put_line('员工姓名:'||p_name||'      员工工资'||p_sal);
  select * into emp_ex
  from scott.emp
  where empno=7369;
  dbms_output.put_line('员工信息:'||emp_ex.ename);
Exception
  when no_data_found then
  Dbms_Output.put_line('员工号不存在!');
end;
（2）记录类型
PL/SQL记录是由一组相关的记录成员组成的，通常用来表示对应数据库表中的一行。使用PL/SQL记录时应自定义记录类型和记录变量，也可以使用%ROWTYPE属性定义记录变量。引用记录成员时，必须要记录变量作为前缀。
 自定义记录类型和记录变量的语法：
 TYPE <记录类型名> IS RECORD（ 
 <数据项 1> <数据类型>[NOT NULL[:=<表达式 1>]],  
 <数据项 2> <数据类型>[NOT NULL[:=<表达式 2>]], 
 ……  
 <数据项 n> <数据类型>[NOT NULL[:=<表达式 n>]]）;
 <记录变量名>  <记录类型名>;
例 6-2 将雇员信息定义为记录类型如下：
  declare
  p_sal number(7,0);
  p_name scott.emp.ename%TYPE; --列类型
  emp_ex scott.emp %ROWTYPE; --行记录
  --定义记录类型
  type emp_record_type is record
  (
    v_ename scott.emp.ename%TYPE,
    v_job scott.emp.job%TYPE,
    v_sal scott.emp.sal%TYPE
  );
  --声明类型
  emp_result emp_record_type;
begin
  --属性类型测试
  select sal,ename into p_sal,p_name
  from scott.emp
  where empno=7369;
  dbms_output.put_line('员工姓名:'||p_name||'      员工工资'||p_sal);
  --行记录类型测试
  select * into emp_ex
  from scott.emp
  where empno=7369;
  dbms_output.put_line('员工信息:'||emp_ex.ename);
  --记录类型测试
  select t.ename,t.job,t.sal into emp_result
  from scott.emp t
  where t.empno=7369;
  dbms_output.put_line('员工姓名:'||emp_result.v_ename);
Exception
  when no_data_found then
  Dbms_Output.put_line('员工号不存在!');
end;
（3）表类型（不太明白）
表是一种复合数据类型，保存在数据缓冲区中的没有特别的存储次序的、可以离散存储的数据结构，它可以是一维的，也可以是二维的。语法：
TYPE <表类型名> IS TABLE OF <数据类型> INDEX BY BINARY_INTEGER;
<表变量名>        <表类型名>;
表类型名是用户定义的，数据类型是表中元素的数据类型，表中所有元素的数据类型是相同的，索引变量缺省为 BINARY_INTEGER（范围介于-231-1~231-1之间）类型的变量，用于指定索引表元素下标的数据类型。
例6.3 索引表类型的定义
SQL> DECLARE
  2     TYPE ename_table_type IS TABLE OF emp.ename%TYPE
  3     INDEX BY BINARY_INTEGER;
  4     Ename_table ename_table_type;
  5   BEGIN
  6     SELECT ename INTO ename_table(1) FROM emp
  7     WHERE empno=7902;
  8     Dbms_output.put_line('员工名：'|| ename_table(1));
  9   END;
10  /

（4）数组类型(不太会)
数组也是一种复合类型，与表不同的是声明了一个数组，就确定了数组中元素的数目。同时，数组存储时，其元素的次序是固定且连续的，而且索引变量从 1 开始一直到其定义的最大值为止。语法如下：
    TYPE <数组类型名> IS VARRAY  （<MAX_SIZE>）OF <数据类型>;
    <表变量名>        <表类型名>;
    数组类型名是用户定义的，数据类型是数组中元素的数据类型，所有数组元素的数据类型是一致的，MAX_SIZE 指明数组元素个数的最大值。
-- LYNN 创建于 2010-6-13
declare
  -- 这里是本地变量
  v_job emp.job%type;
  v_sal emp.sal%type;
  v_empno emp.empno%type :=7369;
begin
  -- 这里是测试语句
  select job,sal into v_job,v_sal from emp where empno=v_empno;
  if v_job='CLERK' THEN
     update emp set sal = v_sal + 200 where empno=v_empno;
  elsif v_job='SALESMAN' THEN
     update emp set sal = v_sal + 100 where empno=v_empno;
  else
     update emp set sal = v_sal + 500 where empno=v_empno;
  end if;
end;

-- LYNN 创建于 2010-6-13
declare
  -- 这里是本地变量
  x int :=100;
  y int;
begin
  -- 这里是测试语句
  loop
     x:=x+10;
  exit when x>1000;
  end loop;
  y:=x;
  dbms_output.put_line(y);
end;

-- LYNN 创建于 2010-6-13
declare
  -- 这里是本地变量
  x int :=100;
  y int :=0;
begin
  -- 这里是测试语句
  while x<=1000
  loop
    x:=x+10;
  end loop;
  y:=x;
  dbms_output.put_line(y);
end;

-- LYNN 创建于 2010-6-13
declare
  -- 这里是本地变量
  x int :=100;
  y int :=0;
begin
  -- 这里是测试语句
  for v_count in 1..10 loop
      x:=x+10;
  end loop;
  y:=x;
  dbms_output.put_line(y);
end;

-- LYNN 创建于 2010-6-13
declare
  v_empno emp.empno%type;
  v_ename emp.ename%type;
  v_salary emp.sal%type;
  -- 声明游标
  cursor c_emp is select t.empno,t.ename,t.sal from emp t ;
begin
  -- 打开游标
  open c_emp;
  --提取数据
  loop
    fetch c_emp into v_empno,v_ename,v_salary;
    exit when c_emp%notfound;
    dbms_output.put_line('empno:'||v_empno||'   ename:'||v_ename||'    salary:'||v_salary);
  end loop;
  --关闭游标
  close c_emp;
end;

2）编写程序。利用UPDATE语句和WHERE条件中的CURRENT OF子句。
-- LYNN 创建于 2010-6-13
declare
  new_sal number;
  -- 声明游标
  cursor salcur(depno number) is select t.sal from emp t where t.deptno=depno for update of sal;
begin
  for currentsal in salcur(20) loop
     new_sal := currentsal.sal;
     update emp set sal=1.1*new_sal where current of salcur;
  end loop;
  commit;
end;