Oracle Install

system info

windonws 10
计算机名: DESKTOP-CT4OEIK

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

install info

C:\Program Files\Oracle
D:\app\
解释：
Oracle产品都会有一个path\to\Inventory\oui目录，这个目录主要用于记录本地安装了哪些Oracle产品（Oracle、weblogic等）。如果安装了Oracle，那么C盘就会有这样一个目录，下面有oui、logs、ContentsXML等，这里的C:\Program Files\Oracle就是配置文件中所说的ORACLE_HOME。
Oracle的应用目录主要在D:\app\carl\product\11.2.0\dbhome_1里。

安装完成，保存一下信息！

安装过程省略。。。
if necessary
ps：先根据此文档了解Oracle的一些概念。

config

玩好Oracle，必须知道几个配置文件的配置和作用：
D:\app\carl\product\11.2.0\dbhome_1\NETWORK\ADMIN
listener.ora
sqlnet.ora
tnsnames.ora
监听器日志D:\app\carl\diag\tnslsnr\DESKTOP-CT4OEIK\listener\trace\listener.log

上面是关于监听器的配置文件，它们管理着访问Oracle的规则，例如允许哪些ip访问等。

启动/关闭监听器
D:\app\carl\product\11.2.0\dbhome_1\BIN\LSNRCTL.EXT
用管理员权限来执行，输入help

listener.ora

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
# listener.ora Network Configuration File: D:\app\carl\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = Oracle8)
      (SID_NAME = ORCL)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = moc.oreh)
      (SID_NAME = localdb)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = DESKTOP-CT4OEIK)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = D:\app\carl\product\11.2.0\dbhome_1\log

GLOBAL_DBNAME和SID的区别可以去百度。一个是service，一个是id。在jdbc连接时url是有区别的。
本地sqlplus登陆数据库
sqlplus system/qazwsxedc123@moc.oreh
如果不指定想直接用户名密码进入一个db怎么办？在环境变量里设置一个oracle_sid=localdb。localdb是我创建的数据库的sid。

sqlnet.ora

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# sqlnet.ora Network Configuration File: D:\app\carl\product\11.2.0\dbhome_1\NETWORK\ADMIN\sqlnet.ora
# Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to 
# install "Software Only", this file wont exist and without the native 
# authentication, they will not be able to connect to the database on NT.

TCP.VALIDNODE_CHECKING = YES

NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME)

TCP.INVITED_NODES= (localhost, 127.0.0.1, 192.168.122.1, 192.168.122.130)

SQLNET.AUTHENTICATION_KERBEROS5_SERVICE = orcl

ADR_BASE = D:\app\carl\product\11.2.0\dbhome_1\log

这个文件很有意思，一般抓狂的问题都因为这里没弄对。

tnsnames.ora

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# tnsnames.ora Network Configuration File: D:\app\carl\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_LOCALDB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


MOC.OREH =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = DESKTOP-CT4OEIK)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = moc.oreh)
    )
  )

http://www.jellythink.com/archives/991
tnsnames.ora作用

demo

在linux中连接远程数据库测试
下载sqldeveloper
导入/home/hero/sqldeveloper/jdbc/lib/ojdbc8.jar

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
package moc.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

/**
 * Created by hero on 6/11/17.
 */
public class OracleDB {
    public static void main(String[] args) {
        Connection con = null;// 创建一个数据库连接
        PreparedStatement pre = null;// 创建预编译语句对象，一般都是用这个而不用Statement
        ResultSet result = null;// 创建一个结果集对象
        try
        {
            Class.forName("oracle.jdbc.driver.OracleDriver");// 加载Oracle驱动程序
            System.out.println("开始尝试连接数据库！OracleDriver");
            String url = "jdbc:oracle:thin:@192.168.122.1:1521/moc.oreh";
            String user = "root";// 用户名
            String password = "qazwsxedc123";// 你安装时选设置的密码
            con = DriverManager.getConnection(url, user, password);// 获取连接
            System.out.println("连接成功！");
            String sql = "select * from user_info";// 预编译语句，“？”代表参数
            pre = con.prepareStatement(sql);// 实例化预编译语句
            result = pre.executeQuery();// 执行查询，注意括号中不需要再加参数
            while (result.next())
                // 当结果集不为空时
                System.out.println("学号:" + result.getInt("id") + "姓名:"
                        + new String(result.getString("name").getBytes(), "utf8") +" age=" + result.getInt("age"));
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        finally
        {
            try
            {
                // 逐一将上面的几个对象关闭，因为不关闭的话会影响性能、并且占用资源
                // 注意关闭的顺序，最后使用的最先关闭
                if (result != null)
                    result.close();
                if (pre != null)
                    pre.close();
                if (con != null)
                    con.close();
                System.out.println("数据库连接已关闭！finally close");
            }
            catch (Exception e)
            {
                e.printStackTrace();
            }
        }
    }
}

使用Command Line - SQLcl 17.2连接远程db
下载并解压得到sqlcl；
在sqlcl下新建文件夹network/ADMIN/，并从sql-developer中复制tnsnames.ora，把host修改为远程ip地址;
登陆./sql system/oracle@192.168.1.11:1521:xe

Start Learn Oracle

oracle sql tutorial
教会你为止

PL/SQL 学习

<<Oracle 从入门到精通>>

Oracle脚本 oracle.bat

1
2
3
4
5
6
7
8
9
10
11
:: windowns 脚本

@echo off
echo start batch
echo ^|     ........   ^|

:: SQLPLUS ROOT/qazwsxedc123@COM @mysql.sql >d:\res.txt
SQLPLUS ROOT/qazwsxedc123@COM @mysql.sql

echo finished
pause

mysql.sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
-- PL/SQL file
SELECT * FROM STUDENT;

-- 开启在屏幕显示
SET SERVEROUTPUT ON;

DECLARE
V_RES NUMBER(8, 2) DEFAULT '';  --自定义变量
V_NAME VARCHAR2(10);
/*
  标量类型的变量
*/
V_NAME2 STUDENT.NAME%TYPE; -- 跟STUDENT表NAME字段同类型
V_ID NUMBER(4, 2);  
V_SEX CONSTANT CHAR(1) := '1';  -- CONSTANT 
/*
  复合类型的变量
*/
-- 1. 记录类型
TYPE TYPE_STUDENT_RECORD IS RECORD(  -- a.定义
  NAME STUDENT.NAME%TYPE,  -- 注意：逗号
  CLASS_ID STUDENT.CLASS_ID%TYPE
);
V_STUDENT_RESULT TYPE_STUDENT_RECORD;  -- b.声明
-- 1.2. %ROWTYPE
V_STUDENT STUDENT%ROWTYPE;
-- 2. PL/SQL索引表类型（关联数组）
TYPE TYPE_TABLE_STUDENT IS TABLE OF STUDENT%ROWTYPE  -- 定义
  INDEX BY BINARY_INTEGER;  -- 以数字为键值
TYPE TYPE_TABLE_NAME IS TABLE OF VARCHAR2(8)
  INDEX BY PLS_INTEGER;
V_TABLE_STUDENT TYPE_TABLE_STUDENT;  -- 声明
V_TABLE_NAME TYPE_TABLE_NAME;
TYPE TYPE_TABLE_STUDENT_NAME IS TABLE OF STUDENT.NAME%TYPE
  INDEX BY VARCHAR2(20);  -- 下标是string
V_TABLE_STUDENT_NAME TYPE_TABLE_STUDENT_NAME;
-- 3. VARRAY变长数组，适合较少数据使用
TYPE TYPE_VARRAY IS VARRAY(100) OF VARCHAR2(20);
V_VARRAY TYPE_VARRAY := TYPE_VARRAY('0','2','1');  -- 声明时必须初始化

BEGIN
  V_RES := 100/3;
  DBMS_OUTPUT.PUT_LINE('print on screen.' || V_RES);
  /*
  将结果存到变量中
  */
  SELECT NAME INTO V_NAME FROM STUDENT WHERE ID='2';  --抛异常后是不再往下执行的
  DBMS_OUTPUT.PUT_LINE('print on screen.' || V_NAME);

  SELECT NAME INTO V_NAME2 FROM STUDENT WHERE ID='1';
  DBMS_OUTPUT.PUT_LINE('print on screen.' || V_NAME2);

  SELECT NAME, CLASS_ID INTO V_STUDENT_RESULT FROM STUDENT WHERE ID='3';
  DBMS_OUTPUT.PUT_LINE('print on screen.' || V_STUDENT_RESULT.NAME || ', ' || V_STUDENT_RESULT.CLASS_ID);

  SELECT * INTO V_STUDENT FROM STUDENT WHERE ID='1';
  DBMS_OUTPUT.PUT_LINE('print on screen.' || V_STUDENT.NAME || ', ' || V_STUDENT.CLASS_ID);

  SELECT * INTO V_TABLE_STUDENT(3) FROM STUDENT WHERE ID='4';
  DBMS_OUTPUT.PUT_LINE('print on screen.' || V_TABLE_STUDENT(3).NAME);  -- 下标为3，空前两个都可以

  SELECT NAME INTO V_TABLE_STUDENT_NAME('ID4') FROM STUDENT WHERE ID='4';
  DBMS_OUTPUT.PUT_LINE('student name is : ' || V_TABLE_STUDENT_NAME('ID4'));
  
  V_VARRAY(1) := 'HELLO';
  V_VARRAY(3) := 'WORLD';
  DBMS_OUTPUT.PUT_LINE(V_VARRAY(1) || ', ' || V_VARRAY(2));
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('data not found.');
    WHEN TOO_MANY_ROWS THEN
      DBMS_OUTPUT.PUT_LINE('too many rows.');
END;
/
EXIT;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
-- PL/SQL 控制语句

-- 开启在屏幕显示
SET SERVEROUTPUT ON;

DECLARE
V_RES NUMERIC(10,3);
V_I NUMERIC(3) := 0;
BEGIN
  V_RES := 100/3;
  /*
    IF 条件语句
  */
  IF V_RES > 0 AND V_RES < 30 THEN
    DBMS_OUTPUT.PUT_LINE('result is less than 30.' || V_RES);
  ELSIF V_RES >=30 THEN
    DBMS_OUTPUT.PUT_LINE('result ' || V_RES || ' is bigger than 30');     
  END IF;
  /*
  CASE 条件语句
  */
  CASE V_RES
  WHEN 33 THEN
    DBMS_OUTPUT.PUT_LINE('HELLO');
  WHEN 33.333 THEN
    DBMS_OUTPUT.PUT_LINE('WORLD');
  END CASE;
  /*
  搜索式CASE语句
  */
  CASE
  WHEN V_RES > 33 THEN
    DBMS_OUTPUT.PUT_LINE('BIGGER');
  ELSE
    DBMS_OUTPUT.PUT_LINE('LITTLE');
  END CASE;
  /*
  基本的LOOP语句
  */
  <<BASIC_LOOP>>  -- 可选的loop标志
  LOOP
    IF V_I < 3 THEN
      DBMS_OUTPUT.PUT_LINE('I = ' || V_I);
      V_I := V_I + 1;
    ELSE
      EXIT BASIC_LOOP;
    END IF;
  END LOOP;
  /*
  使用 EXIT ... WHEN 退出循环
  */
  <<STOP_LOOP>>
  LOOP
    DBMS_OUTPUT.PUT_LINE('ADDING ' || V_I);
    V_I := V_I + 1;
    EXIT STOP_LOOP WHEN V_I > 4;
  END LOOP;
  /*
  WHILE ... LOOP
  */
  <<WHILE_LOOP>>
  WHILE V_I > 2
    LOOP
      DBMS_OUTPUT.PUT_LINE('WHILE ' || V_I);
      V_I := V_I - 1;
    END LOOP;
  /*
  FOR ... LOOP
  */
  --<<FOR_LOOP>>
  FOR INX IN
    REVERSE  -- 倒序
    7..9 LOOP
    DBMS_OUTPUT.PUT_LINE('INDEX = ' || INX);
    END LOOP;
END;
/
EXIT;