Oracle 基础

Oracle 基础
－－－－－－－－－－－－－－－－

1.
sqlplus

1.1
连接数据库：
sqlplus "sys/pwd as sysdba"
sqlplus scott/tiger

conn scott/tiger

1.2
帮助：
help index
? SET

1.3
保存与加载SQL脚本
save c:\dept.sql

spool c:\dept.sql
spool off

@c:\dept.sql

edit c:\dept.sql

1.4
查询表结构：
DESC dept;

1.5
查询当前用户：
SELECT user FROM dual;

2
用户管理

OS 用户组：
ORA_DBA
SQL> conn sys/pwd as sysdba


SQL> CREATE USER userdb IDENTIFIED BY userdb;

用户已创建。

SQL> CREATE USER sysdb IDENTIFIED BY sysdb;

用户已创建。

SQL> ALTER user sysdb ACCOUNT UNLOCK;

用户已更改。

SQL> ALTER user userdb ACCOUNT UNLOCK;

用户已更改。

SQL> grant connect to userdb;

授权成功。

SQL> grant connect to sysdb;

授权成功。

SQL> grant sysdba to sysdb;

授权成功。

SQL> grant sysdba to userdb;

授权成功。



SQL> grant connect, resource, sysdba to userdb with admin option;

授权成功。

SQL> grant connect, resource, sysdba to sysdb with admin option;

授权成功。


SQL> revoke sysdba from userdb;

撤销成功。

SQL> revoke sysdba from sysdb;

撤销成功。

3.
表空间管理

SQL> CREATE TABLESPACE sysdb
  2  DATAFILE 'E:\app\oradata\bisoft\SYSDB.MDF' SIZE 50M;

表空间已创建。

SQL> CREATE TABLE test(id int primary key, name varchar2(20)) TABLESPACE sysdb;

表已创建。


4.
查询当前用户的所有表对象：
SQL> select table_name, tablespace_name from user_tables;

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TEST                           USERS
SALGRADE                       USERS
BONUS                          USERS
EMP                            USERS
DEPT                           USERS

5.
PL/SQL

SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL> l
  1  DECLARE
  2  x varchar2(50);
  3  BEGIN
  4  x:='Hello World!';
  5  DBMS_OUTPUT.PUT_LINE('x＝'||x);
  6* END;
SQL> /
x＝Hello World!

PL/SQL 过程已成功完成。

4  DECLARE
5     x varchar2(50);
6     y INTEGER:=100;
7     i number:=0;
8  BEGIN
9     -- 赋值
0     x:='Hello World!';
1     DBMS_OUTPUT.PUT_LINE('x＝'||x||' y='||y);
2     -- IF THEN ELSE, 注意ELSIF ，不是ELSEIF
3     IF y=10 THEN
4             x:='ok';
5     ELSIF y=100 THEN
6             x:='error';
7     ELSE
8             x:='none';
9     END IF;
0     DBMS_OUTPUT.PUT_LINE('x＝'||x||' y='||y);
1     -- CASE WHEN THEN ELSE
2     CASE
3             WHEN y=0 THEN
4                     x:='ok2';
5             WHEN y=100 THEN
6                     x:='error2';
7             ELSE
8                     x:='none';
9     END CASE;
0     DBMS_OUTPUT.PUT_LINE('x＝'||x||' y='||y);
1     -- LOOP, 基本循环
2     LOOP
3             i:=i+1;
4             IF i > 3 THEN
5                     EXIT;
6             END IF;
7             DBMS_OUTPUT.PUT_LINE('i＝'||i);
8     END LOOP;
9     -- WHILE, 条件循环
0     i:=0;
1     WHILE i <=3 LOOP
2             DBMS_OUTPUT.PUT_LINE('i＝'||i);
3             i:=i+1;
4     END LOOP;
5     -- FOR, 计数器可以不用声明, 反向输出reverse.
6     FOR k IN 1..5 LOOP
7             DBMS_OUTPUT.PUT_LINE('k＝'||k);
8     END LOOP;
9     FOR k IN REVERSE 1..5 LOOP
0             DBMS_OUTPUT.PUT_LINE('k＝'||k);
1     END LOOP;
2     -- GOTO, 模拟循环
3     i:=0;
4     -- 标签
5     <<repeat_loop>>
6     i:=i+1;
7     DBMS_OUTPUT.PUT_LINE('i＝'||i);
8     IF i<3 THEN
9             GOTO repeat_loop;
0     END IF;
1* END;
L> /

异常处理:

  1  DECLARE
  2  test varchar2(50);
  3  BEGIN
  4  SELECT dname INTO test FROM dept WHERE deptno=0;
  5  DBMS_OUTPUT.PUT_LINE(test);
  6  -- 系统异常 NO_DATA_FOUND
  7  EXCEPTION
  8     WHEN NO_DATA_FOUND THEN
  9             DBMS_OUTPUT.PUT_LINE('未查询到数据.');
 10* END;
SQL> /
未查询到数据.


  1  DECLARE
  2  test varchar2(50);
  3  e exception;
  4  BEGIN
  5     SELECT dname INTO test FROM dept WHERE deptno=10;
  6     IF test<>'XX部门' THEN
  7             RAISE e;
  8     END IF;
  9     DBMS_OUTPUT.PUT_LINE(test);
 10     -- 自定义异常 e
 11     EXCEPTION
 12             WHEN e THEN
 13                     DBMS_OUTPUT.PUT_LINE('未查询到数据XX部门.');
 14* END;
SQL> /
未查询到数据XX部门.

PL/SQL 过程已成功完成。

系统异常:
DUP_VALUE_ON_INDEX:
违反惟一约束

NO_DATA_FOUND:
查询数据没有返回值

TOO_MANY_ROWS:
查询语句返回了多行

VALUE_ERROR:
违反字段取值约束

ZERO_DIVIDE:
除零错误

...

5.2
记录

DECLARE
	TYPE myrecord IS RECORD(
	    id dept.deptno%TYPE,
	    name dept.dname%TYPE
	);
	myrecord_instance myrecord;
BEGIN
	SELECT deptno,dname INTO myrecord_instance FROM dept WHERE deptno=10;
	DBMS_OUTPUT.PUT_LINE('id= '||myrecord_instance.id||' name= '||myrecord_instance.name);
END;
/

SQL> /
id= 10 name= ACCOUNTING

PL/SQL 过程已成功完成。

-- 简洁定义,直接映射表中每个字段
DECLARE
	dept_record dept%ROWTYPE;
BEGIN
	SELECT * INTO dept_record FROM dept WHERE deptno=10;
	DBMS_OUTPUT.PUT_LINE('deptno= '||dept_record.deptno||' dname= '||dept_record.dname);
END;
/

5.3
游标

  1  DECLARE
  2     --  显示游标
  3     --  1 声明游标, 有参数
  4     CURSOR mycur(id dept.deptno%TYPE) IS
  5             SELECT * FROM dept WHERE deptno = id;
  6     dept_record dept%ROWTYPE;
  7  BEGIN
  8     --  2 打开游标
  9     OPEN mycur(10);
 10     --  3 使用游标
 11     FETCH mycur INTO dept_record; -- 取第一条
 12     WHILE mycur%FOUND LOOP
 13             DBMS_OUTPUT.PUT_LINE(dept_record.deptno||' '||dept_record.dname|
|' '||dept_record.loc);
 14             FETCH mycur INTO dept_record; -- 取下一条
 15     END LOOP;
 16     --  4 关闭游标
 17     CLOSE mycur;
 18* END;
SQL> /
10 ACCOUNTING NEW YORK

PL/SQL 过程已成功完成。

  1  DECLARE
  2     --  隐式游标
  3     --  1 声明游标
  4     CURSOR mycur(id dept.deptno%TYPE) IS
  5             SELECT * FROM dept WHERE deptno = id;
  6  BEGIN
  7     --  2 使用游标
  8     FOR tmpcur IN mycur(10) LOOP
  9             DBMS_OUTPUT.PUT_LINE(tmpcur.deptno||' '||tmpcur.dname||' '||tmpc
ur.loc);
 10     END LOOP;
 11* END;
SQL> /
10 ACCOUNTING NEW YORK

PL/SQL 过程已成功完成。

DECLARE
   --  使用游标更新数据
   CURSOR mycur(id dept.deptno%TYPE) IS
           SELECT dname FROM dept WHERE deptno = id FOR UPDATE;
   new_dname dept.dname%TYPE:='财务部';
   old_dname dept.dname%TYPE;
BEGIN
   OPEN mycur(10);
   FETCH mycur INTO old_dname;
   WHILE mycur%FOUND LOOP
	UPDATE dept SET dname=new_dname WHERE CURRENT OF mycur;
	DBMS_OUTPUT.PUT_LINE(old_dname||' -> '||new_dname);
	FETCH mycur INTO old_dname;
   END LOOP;
END;
/
ACCOUNTING -> 财务部

PL/SQL 过程已成功完成。

5.4
存储过程
参数:
IN
OUT
IN OUT

参数不能指定长度,变量必须指定长度.

查看编译存储过程错误:
SHOW　ERRORS PROCEDURE proc;

6
语句块必须以分号结束．

7

全局变量
VARIABLE SUM NUMBER;
在声明块之前定义
引用：
:SUM:=0;

8
查看源码：
select * from user_source;

9
函数

函数多了个返回值．
函数可以在SQL中使用．


10
大批量数据导入
alter table member nologging;

BEGIN 
for i in 1..1000000 loop
	insert /*+ append */ into member values(i, 36, 9000);
end loop;
commit;
for i in 1000001..2000000 loop
	insert /*+ append */ into member values(i, 25, 8000);
end loop;
commit;
for i in 2000001..3000000 loop
	insert /*+ append */ into member values(i, 36, 10000);
end loop;
commit;
for i in 3000001..4000000 loop
	insert /*+ append */ into member values(i, 20, 12000);
end loop;
commit;
for i in 4000001..5000000 loop
	insert /*+ append */ into member values(i, 40, 8500);
end loop;
commit;
for i in 5000001..6000000 loop
	insert /*+ append */ into member values(i, 24, 7000);
end loop;
commit;
for i in 6000001..7000000 loop
	insert /*+ append */ into member values(i, 38, 15000);
end loop;
commit;
for i in 7000001..8000000 loop
	insert /*+ append */ into member values(i, 21, 20000);
end loop;
commit;
for i in 8000001..9000000 loop
	insert /*+ append */ into member values(i, 39, 60000);
end loop;
commit;
for i in 9000001..10000000 loop
	insert /*+ append */ into member values(i, 20, 6000);
end loop;
commit;
END;
/

alter table member logging;

大约 10分钟左右

11
千万数据查询
行转列

select 
	sum(decode(rownum, 1, a, 0)) AS "A",
	sum(decode(rownum, 2, a, 0)) AS "B",
	sum(decode(rownum, 3, a, 0)) AS "C",
	sum(decode(rownum, 4, a, 0)) AS "D"
from 
(
select count(1) a from member where age > 35 and sal <  9999
union all
select count(1) a from member where age > 35 and sal >  9999
union all
select count(1) a from member where age < 35 and sal <  9999
union all
select count(1) a from member where age < 35 and sal >  9999
);