oracle学习.txt

关于oracle的详细资料!! 一定对你学习oracle有用的

[第九章　复杂查询]


一、连接查询：
问：Smith在哪里工作？
答：SQL> select loc from dept, emp where ename = 'SMITH' and emp.deptno = dept.deptno;

二、集合查询[请参考PowerPiont教程：SQL讲稿.ppt P29,30,31]
集合操作是将多个基表的查询结果作UNION运算。
交操作: Intersect
差操作：MINUS


三、子查询(Subqueries):
子查询是在where子句中包含的查询语句，是由系列简单构成的复杂查询。

问：谁与smith在同一部门工作？
答：SQL> select deptno from emp where ename = 'SMITH';
SQL> select ename from emp where deptno = 20;

将两个语句合起来：Select ename from emp where deptno = ( select deptno from emp where ename = 'SMITH');


[第十章　Oracle 权限设置]

一、权限分类：
系统权限：系统规定用户使用数据库的权限。（系统权限是对用户而言)。

实体权限：某种权限用户对其它用户的表或视图的存取权限。（是针对表或视图而言的）。

二、系统权限管理：
1、系统权限分类：
DBA: 拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构。

RESOURCE:拥有Resource权限的用户只可以创建实体，不可以创建数据库结构。

CONNECT:拥有Connect权限的用户只可以登录Oracle，不可以创建实体，不可以创建数据库结构。

对于普通用户：授予connect, resource权限。
对于DBA管理用户：授予connect，resource, dba权限。

2、系统权限授权命令：
[系统权限只能由DBA用户授出：sys, system(最开始只能是这两个用户)]
授权命令：SQL> grant connect, resource, dba to 用户名1 [,用户名2]...;

[普通用户通过授权可以具有与system相同的用户权限，但永远不能达到与sys用户相同的权限，system用户的权限也可以被回收。]

例：
SQL> connect system/manager
SQL> Create user user50 identified by user50;
SQL> grant connect, resource to user50;

查询用户拥有哪里权限：
SQL> select * from dba_role_privs;
SQL> select * from dba_sys_privs;
SQL> select * from role_sys_privs;

删除用户：SQL> drop user 用户名 cascade; 	//加上cascade则将用户连同其创建的东西全部删除

3、系统权限传递：
增加WITH ADMIN OPTION选项，则得到的权限可以传递。

SQL> grant connect, resorce to user50 with admin option; 	//可以传递所获权限。

4、系统权限回收：系统权限只能由DBA用户回收
命令：SQL> Revoke connect, resource from user50;

系统权限无级联，即A授予B权限，B授予C权限，如果A收回B的权限，C的权限不受影响；系统权限可以跨用户回收，即A可以直接收回C用户的权限。

三、实体权限管理
1、实体权限分类：select, update, insert, alter, index, delete, all		//all包括所有权限
execute		//执行存储过程权限

user01:
SQL> grant select, update, insert on product to user02;
SQL> grant all on product to user02;

user02:
SQL> select * from user01.product;

// 此时user02查user_tables，不包括user01.product这个表，但如果查all_tables则可以查到，因为他可以访问。


3. 将表的操作权限授予全体用户：
SQL> grant all on product to public;		// public表示是所有的用户，这里的all权限不包括drop。

[实体权限数据字典]:
SQL> select owner, table_name from all_tables;	// 用户可以查询的表
SQL> select table_name from user_tables;		// 用户创建的表
SQL> select grantor, table_schema, table_name, privilege from all_tab_privs;	// 获权可以存取的表（被授权的）
SQL> select grantee, owner, table_name, privilege from user_tab_privs;			// 授出权限的表(授出的权限)

4. DBA用户可以操作全体用户的任意基表(无需授权，包括删除)：
DBA用户：
SQL> Create table stud02.product(
	id number(10),
	name varchar2(20));
SQL> drop table stud02.emp;

SQL> create table stud02.employee
	as
	select * from scott.emp;
	
5. 实体权限传递(with grant option)：
user01:

SQL> grant select, update on product to user02 with grant option;	// user02得到权限，并可以传递。

6. 实体权限回收：
user01:
SQL>Revoke select, update on product from user02;		//传递的权限将全部丢失。


四、同义词(Synonym):

1、创建私有同义词：
语法：SQL> create synonym 同义词名 for 代替项;

user01:
SQL> grant select, upate on product to user02;

user02:
SQL> Create synonym product for user01.product;

SQL> select * from product;		// 这里的product即user01.product。

同义词数据字典：
SQL> select synonym_name, owner, table_name from all_synonyms;
SQL> select synonym_name, table_name from user_synonyms;

2、DBA可以创建公共同义词(Public Synonym)：公共同义词全体用户可以存取
语法：SQL> create public synonym 公共同义词名 for 代替项;

SCOTT:
SQL> grant select on payment to public;

SYSTEM:
SQL> create public synonym payment for scott.payment;


3. 删除同义词：
User:SQL> drop synonym 私有同义词名;

DBA: SQL> drop public synonym 公共同义词名;


[PL/SQL程序设计]

一、PL/SQL概述
PL/SQL块结构：
Declare
	-- 变量定义部分
Begin
	-- 可执行语句
Exception
		-- 例外处理：对于程序运行中的错误信息、警告信息的说明
End;
.		// 以.号结束程序编写

二、PL/SQL语言的特点（参考PowerPoint教程）
1. PL/SQL中可以定义变量，变量有其作用范围。
2. PL/SQL是以块的方式设计，块中可以嵌套子块，子块可以位于块中任何部分。
3. 

x. PL/SQL是以块为单位，SQL语句以语句为单位。

7. 减少对Oracle核心的访问，降低网络负载。

三、PL/SQL与SQL语言(参考PowerPoint教程）
1. PL/SQL语句不能使用DDL语句。
可以使用的SQL语句：Insert, Update, Delete, [select into], commit, rollback, savepoint

...

四、PL/SQL基础
1. 变量的使用
2. 单行注释：--
　 多行注释：/*			*/

3. 数据类型：
(1)布尔型: Boolean(TRUE, FALSE, NULL）
(2)数字型：NUMBER
INT = INTEGER

(3) 字符型，基本上没有变化
(4) 日期型
(5) 二进制数据：raw, blob

4. 数据定义：
	变量名 数据类型
	变量名 数据类型:=初始值

5. 变量赋值：变量 := 值;

[*]%TYPE:数据类型匹配

s1 char(20);
s2 s1%TYPE;	// 定义s2变量，其类型与s1完全匹配。

v_sal emp.sal%type; 	// v_sal变量的类型与emp表中的sal字段的数据类型完全匹配，%TYPE最经常使用的方法

[*] %ROWTYPE: 行类型，用于存储数据库基表的一条记录。
定义方法：变量 基表名%rowtype; 

例：
SQL> set serveroutput on 	// 使能屏幕打印函数的输出

Declare	// 如果PL/SQL程序中没有定义变量，那么Declare可以省略
	v_empno emp.empno%type := &empno;
	r emp%rowtype;
Begin
	select * into r from emp where empno = v_empno;
	dbms_output.put_line('姓名'|| r.ename||'工资'||r.sal||'日期'||r.hiredate);		
	//　r.字段名：表示某一列的值；dbms_output.putline()是一个屏幕打印函数
End;


[*] 记录类型Record:
	record:
Declare
	v_empno emp.empno%type := &empno;
	// 定义record类型
	type r_emp is record(
		v1 emp.ename%type,
		v2 emp.job%type,
		v3 emp.hiredate%type);
	
	r r_emp;			-- 定义变量r为record类型r_emp
Begin
	select ename, job, hiredate into r from emp where empno = v_empno;
	dbms_output.put_line('姓名:'|| r.v1||'职务:'||r.v2||'工作时间:'||r.v3);
End;

[*] Table类型：类似于C语言中的结构类型数组：
定义方法：TYPE [table_emp] is Table of [emp.ename%type] index by binary_integer;  // []内为用户可以修改的部分

使用：mytable = table_emp;
	mytable(0) := 'SCOTT';
	...
例：
Declare 
	v_empno emp.empno%type := &empno;
	type t_emp is table of emp.ename%type index by binary_integer;
	
	t t_emp;
Begin
	select ename into t(10) from emp where empno = v_empno;
	dbms_output.put_line('编码为'||v_empno||'的员工是'||t(10));
End;

五、条件控制语句：条件判断语句
1. IF-THEN语句：
IF 条件成立 THEN 
	可执行语句;　
END IF; 

Declare
       v_empno emp.empno%type :=&empno;
       v_ename emp.ename%type;
       v_sal emp.sal%type;
    Begin
      select sal,ename into v_sal,v_ename from emp
      where empno=v_empno;
      if v_sal<2000 then Begin
                  update emp set sal=sal+100
                  where empno=v_empno;
       dbms_output.put_line('员工'||v_ename||'工资已经修改!');
                                       End;
      End if;
    End;
    

2. IF-THEN-ELSE语句

IF 条件成立 THEN  
	执行语句1;
ELSE 
	执行语句2;
END IF;

Declare
       v_empno emp.empno%type :=&empno;
       v_ename emp.ename%type;
       v_sal emp.sal%type;
Begin
      select sal,ename into v_sal,v_ename from emp
      where empno=v_empno;
      if v_sal<2000 then Begin
                  update emp set sal=sal+100
                  where empno=v_empno;
       dbms_output.put_line('员工'||v_ename||'工资已经修改!');
                                       End;
      Else dbms_output.put_line('员工'||v_ename||'的工资已经超过规定值,不予更新!');
      End if;
End;
		
3. IF-THEN-ELSIF语句
     IF       条件1成立  THEN  执行语句1;
     ELSIF 条件2成立  THEN   执行语句2；
     ELSIF 条件3成立  THEN   执行语句3;
     ... 
     END  IF;
     
Declare
       v_empno emp.empno%type :=&empno;
       v_ename emp.ename%type;
       v_sal emp.sal%type;
    Begin
      select sal,ename into v_sal,v_ename from emp
      where empno=v_empno;
       if v_sal<2000 then Begin
                  update emp set sal=sal+100
                  where empno=v_empno;
        dbms_output.put_line('员工'||v_ename||'工资已经修改!');
                                       End;
        elsif v_sal<2500 then Begin
                  update emp set sal=sal+50
                  where empno=v_empno;
        dbms_output.put_line('员工'||v_ename||'工资已经修改!');
                                       End;
        elsif v_sal<3000 then Begin
                  update emp set sal=sal+10
                  where empno=v_empno;
        dbms_output.put_line('员工'||v_ename||'工资已经修改!');
                                       End;
        else dbms_output.put_line('员工'||v_ename||'的工资已经超过规定值,不予更新!');
        End if;
    End;
	
六、循环语句：
1. 基本循环：
LOOP
       执行语句；
EXIT WHEN 条件成立；
END LOOP;

例：
Declare
         v_deptno emp.deptno%type:=&deptno;
         i number(2):=0;
     Begin
         loop
           i := i +1;
           insert into emp(empno,hiredate,deptno)
           values(i+7200,sysdate,v_deptno);
           dbms_output.put_line('i的当前值为:'||i);
          exit when i=10;
          end loop;
    End;

2. WHILE循环:

WHILE 条件成立 
LOOP 执行语句；
END LOOP;

Declare
         v_deptno emp.deptno%type:=&deptno;
         i number(2):=0;
     Begin
         while i  < 10   loop
           i := i +1;
           insert into emp(empno,hiredate,deptno)
           values(i+7200,sysdate,v_deptno);
           dbms_output.put_line('i的当前值为:'||i);
       --   exit when i=10;
          end loop;
    End;
    
3. FOR循环:
FOR 计数器 IN 低界..高界 
LOOP 执行语句;
END LOOP;

例：
Declare
         v_deptno emp.deptno%type:=&deptno;
         i number(2):=0;
     Begin
         while i  < 10   loop
           i := i +1;
           insert into emp(empno,hiredate,deptno)
           values(i+7200,sy