Oracle培训

1、 ORACLE概要文件（profile）
1)概要文件
我们创建了一个用户，为其授予权限，那么就可以该用户就可以使用。如果一个用户疯狂地使用资源，那么就会造成数据库资源的缺乏或者是一个不会法的使用者疯狂地对一个用户的密码进行破解，那么很可能会造成信息的泄露与丢失。
为此，我们可以在Oracle中使用概要文件(profile)来进行资源的限制与密码相关特性的设置。当我们创建一个用户时，如果不指定相应的概要文件，那么，Oracle将为其指定一个默认的概要文件。
如下：
select profile  from dba_users  where username = 'TEST';
2) profile参数详解     


 SQL> select * from dba_profiles;


3）举例设置profile 。
目的：   SESSIONS_PER_USER：指定限制用户的并发会话的数目（此处设置为1）






select * from dba_profiles where resource_name='SESSIONS_PER_USER';


指定开启资源限制。该改变对密码资源无效，密码资源总是可用。
alter system set resource_limit=true;




drop profile test;
drop profile test cascade;






create profile test
limit
SESSIONS_PER_USER 1;






drop user test cascade
create user test identified by oracle 
default tablespace SYSTEM
  temporary tablespace TEMP ;






grant create table to test;
grant select any table to test;
grant create session to test;




alter user test profile test;


验证登录报限制。






2、默认的scott用户是被锁定的，先解锁就能登陆上了。 使用下面的语句解锁scott:
 alter user scott account unlock;
解锁之后可能会要求你该密码：
alter user scott identified by tiger;
锁定用户的方法：
alter user test account lock;
3、 用到with admin option 和with grant option区别：
相同点：
两个都可以既可以赋予user 权限时使用，也可以在赋予role 时用。
两个受赋予者，都可以把权限或者role 再赋予other users。
不同点：
- with admin option 只能在赋予 system privilege 的时使用
- with grant option 只能在赋予 object privilege 的时使用
- 撤消带有admin option 的system privileges 时，连带的权限将保留
- 撤消带有grant option 的object privileges 时，连带的权限也将撤消


4、匿名块：
Drop table oracle_edu;
create table oracle_edu(
Enterprise    varchar(30),
education     varchar(30)
);
comment on column oracle_edu.education
  is '培训科目';


insert into oracle_edu values ('css','oracle');
insert into oracle_edu values ('css','java');
insert into oracle_edu values ('css','php');
insert into oracle_edu values ('css','asp');
insert into oracle_edu values ('css','jsp');
commit;




select * from oracle_edu;




declare
  cursor c1 is select Enterprise,education from oracle_edu where enterprise='css';
  n1 number;
begin
     for v1 IN c1 loop
         dbms_output.put_line(v1.enterprise || ',' || v1.education );
         n1 := c1%rowcount;
     end loop;
     dbms_output.put_line(n1);
end;


5、存储过程：


执行动态SQL：


create or replace procedure p_sqltest( jg in varchar2 , m in number )
is
v1 number;
sqltext  varchar2(200);
begin
  sqltext := ' create table ' || jg || '( n number)' ;
  dbms_output.put_line(sqltext);
  execute immediate sqltext ;  


  sqltext := 'insert into ' || jg || ' values ( ' || m || ' )';
  dbms_output.put_line(sqltext);
  execute immediate sqltext ;  
  commit;
  
  sqltext := 'select count(1) from ' ||  jg ;
  dbms_output.put_line(sqltext);
  execute immediate sqltext into v1 ;  
  dbms_output.put_line(v1);
  
  /*sqltext := 'drop table ' || jg || ' purge ';
  dbms_output.put_line(sqltext);
  execute immediate sqltext ;  */
end p_sqltest;


---------------------
create or replace procedure proc_test_wang
(
    table_name in varchar2,   
    field1 in varchar2,         
    datatype1 in varchar2,     
    field2 in varchar2,          
    datatype2 in varchar2       
) as 
    str_sql varchar2(500);
begin 
    str_sql:=' create table '||table_name||' (' ||field1|| '  ' ||datatype1|| ' , '||field2||' '||datatype2||' ) ';
    execute immediate str_sql;   
    exception 
        when others then 
            null;
end ;


-------------------
create table dinya_test(
id  number,
name varchar2(30)
)
create or replace procedure proc_in
(
    id in number,                                
    name in varchar2                             
) as 
    str_sql varchar2(500);
begin 
    str_sql:=' insert into dinya_test values(:1,:2) ';
    execute immediate str_sql using id,name; 
    commit;
    exception 
        when others then 
            null;
end ;










6、高级dml操作 
 insert 将一张表中的数据，分别插入到多张表中。
（1）
drop table e1;
drop table e2;
create table e1 as select empno,ename,deptno from  scott.emp where 1>2;
create table e2 as select empno,job,deptno from  scott.emp  where 1>2; 


insert  all
  into e1 values(empno,ename,deptno)
   into e2 values(empno,job,deptno)
   select empno,ename,deptno,job from scott.emp where deptno=20;
   
select  * from e1;
select  * from e2;


（2）
drop table e1;
drop table e2;
create table e1 as select empno,ename,deptno from  scott.emp where 1>2;
create table e2 as select empno,job,deptno from  scott.emp  where 1>2; 
insert  first 
        when deptno=20 then
             into e1 values(empno,ename,deptno)
        when deptno=30 then
             into e2 values(empno,job,deptno)
        select empno,ename,deptno,job from scott.emp where deptno in (20,30);
        
select  * from e1;
select  * from e2;


7、创建主键、外键
主键:
(1)创建的时候指定；
create table t1(
  mail char(8) primary key,
  name char(8)
)
(2)表已经创建后再指定：
drop table t1;
create table t1(
  mail char(8) ,
  name char(8)
)
alter table t1 add constraint pk_t1_mail primary key (mail);




外键：
drop table dept_1;
drop table emp_1;
create table dept_1 as select * from scott.dept;
create table emp_1 as select * from scott.emp;


alter table dept_1 add constraint pk_dept_1 primary key (deptno);
alter table emp_1 add constraint fk_emp_1 foreign key (deptno)references dept_1(deptno) ;


alter table emp_1 add constraint fk_emp_1 foreign key (deptno) references dept_1(deptno) on  delete set null;
p被删除，f变null.


alter table dept_1 drop constraint pk_dept_1  ;
alter table emp_1 drop constraint fk_emp_1  ;










8、计算各个部门的员工，平均工资，部门总工资，各部门人数，以及平均工资小于2000的部门。


select deptno,avg(sal),count(1),sum(sal) from scott.emp
group by deptno
having avg(sal)<2000




----------------
union all  与 union


drop table t_1;
drop table t_2;
create table t_1  as select empno,ename from scott.emp where sal>1500;
create table t_2  as select empno,ename from scott.emp where sal>1200;


select * from t_1
union all
select * from t_2;


select * from t_1
union
select * from t_2;


---------------------


drop table t_1;
drop table t_2;
create table t_1  as select empno,ename from scott.emp where sal>1500;
create table t_2  as select empno,ename from scott.emp where sal>1200;
select * from t_2
minus
select * from t_1;


----------------------


drop table t_1;
drop table t_2;
create table t_1  as select empno,ename from scott.emp where sal>1500;
create table t_2  as select empno,ename from scott.emp where sal>1200;


select * from t_2
intersect
select * from t_1;




9、查询role的权限的表
谁的对象，谁来授权对象给role.




显示所有角色
select * from dba_roles;


显示角色具有的系统权限
select privilege, admin_option from role_sys_privs where role='角色名';




显示角色具有的对象权限
select * from dba_tab_rpivs;


--解锁用户的同时修改密码；
SQL> alter user scott account unlock identified by tiger;


非dba角色的用户，必须具有grant any role 权限才转授角色。
SQL> grant grant any role to lisi;






角色可以授予给角色
SQL> create role myrole2 not identified;






SQL> grant myrole1 to myrole2;






SQL> grant update on scott.emp to myrole2;




10、通过回退段闪回历史数据




conn system/oracle;




SQL> create table www (id int , name varchar(30));


Table created.


SQL> insert into www values (1,'guanyu');






SQL> commit;




SQL> select dbms_flashback.get_system_change_number() from dual;


DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()
-----------------------------------------
                                  1844606


 update www set name='liubei' where id=1;
 commit;


Commit complete.


SQL> select * from www;


        ID NAME
---------- ------------------------------
         1 liubei


execute dbms_flashback.enable_at_system_change_number(1940395);


PL/SQL procedure successfully completed.


SQL> select * from www;


        ID NAME
---------- ------------------------------
         1 guanyu










11、使用exceptions表，处理违反约束的记录
SQL> create table t1 as select * from emp;
SQL> alter table t1 add constraint t1_pk primary key (empno) disable;
SQL> update t1 set empno=7900 where empno=7902 ;
SQL> select * from t1 where empno=7900;


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7900 JAMES      CLERK           7698 03-DEC-81        950
        30


      7900 FORD       ANALYST         7566 03-DEC-81       3000
        20








SQL> create table exceptions ( row_id rowid,owner varchar(30), table_name varchar(39),constraint varchar(30) );






SQL> alter table t1 enable constraint t1_pk exceptions into exceptions;
alter table t1 enable constraint t1_pk exceptions into exceptions
*
ERROR at line 1:
ORA-02437: cannot validate (SCOTT.T1_PK) - primary key violated




SQL> select * from EXCEPTIONS;


ROW_ID             OWNER
------------------ ------------------------------
TABLE_NAME                              CONSTRAINT
--------------------------------------- ------------------------------
AAARpZAAEAAAABEAAM SCOTT
T1                                      T1_PK


AAARpZAAEAAAABEAAL SCOTT
T1                                      T1_PK






SQL> select * from exceptions where row_id='AAARpZAAEAAAABEAAM';


ROW_ID             OWNER
------------------ ------------------------------
TABLE_NAME                              CONSTRAINT
--------------------------------------- ------------------------------
AAARpZAAEAAAABEAAM SCOTT
T1                                      T1_PK