Bo's Oracle Station

查看: 2116|回复: 0

第52次:2014-10-23

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2014-10-24 17:10:21 | 显示全部楼层 |阅读模式
2014-10-23-plsql.sql
  1. SELECT last_name, job_id
  2. FROM   employees
  3. WHERE  job_id =
  4.                 (SELECT job_id
  5.                  FROM   employees
  6.                  WHERE  last_name = 'Haas');


  7. SELECT employee_id, last_name, job_id, salary
  8. FROM   employees
  9. WHERE  salary < any
  10.                     (SELECT salary
  11.                      FROM   employees
  12.                      WHERE  job_id = 'IT_PROG')
  13. AND    job_id <> 'IT_PROG';

  14. SELECT * FROM departments
  15. WHERE NOT EXISTS
  16. (SELECT * FROM employees
  17. WHERE employees.department_id=departments.department_id);


  18. SELECT * FROM departments
  19. WHERE  not EXISTS
  20. (SELECT 'X'  FROM employees
  21. WHERE employees.department_id=departments.department_id);


  22.   
  23. SELECT * FROM departments
  24. WHERE  department_id  not   in
  25. (SELECT       employees.department_id   FROM employees
  26. WHERE employees.department_id=departments.department_id);



  27. select  employee_id, last_name, salary  
  28. from employees e  
  29. where  salary > ( select  avg(salary)  from employees a  where a.department_id=e.department_id
  30.                              group by  a.department_id ) ;
  31.                              

  32. select   employee_id, last_name, salary  
  33. from employees e  , ( select  avg(salary) avgsal  , department_id  from  employees group by department_id) a
  34. where e.department_id=a.department_id
  35.   and e.salary > a.avgsal;




  36. SELECT emp.last_name
  37. FROM   employees emp
  38. WHERE  emp.employee_id NOT IN
  39.                            (SELECT mgr.manager_id
  40.                             FROM   employees mgr);




复制代码

2014-10-23-plsqlB.sql
  1. select  * from job_history  order by 1,2;

  2. select   employee_id, job_id from employees where employee_id  in (101,176,200);

  3. SELECT employee_id, job_id
  4. FROM   employees
  5. minus
  6. SELECT   employee_id , job_id
  7. FROM   job_history  ;




  8. SELECT   employee_id  , job_id xxx
  9. FROM   job_history  
  10. minus
  11. SELECT employee_id, job_id  yyy
  12. FROM   employees  order by  xxx;

复制代码

2014-10-23-plsqlC.sql
  1. create table t05109_a ( a  number default 999) ;

  2. select  * from user_tab_columns tc where tc.TABLE_NAME='T05109_A';

  3. insert into  t05109_a values ( default);

  4. select  * from t05109_a;

  5. alter table t05109_a   modify ( a default 888 ) ;

  6. insert into  t05109_a values ( default);

  7. insert into t05109_a values (&s_1);

  8. insert into t05109_a values (&&s_1);

复制代码

2014-10-23-plsqlD.sql
  1. alter table employees add ( department_name varchar2(30));

  2. select  * from employees ;

  3. update employees e set department_name=(select department_name from departments d
  4.    where e.department_id=d.department_id);
  5.    
  6.    
  7.    create table t05109_b( a number , b varchar2(20)) ;
  8.    
  9.    create table t05109_c ( c number , d varchar2(20)) ;
  10.    
  11.    insert into  t05109_b values ( 1 ,'A');
  12.    insert into t05109_c values (2,'B');
  13.    insert into t05109_b values (2,'B');
  14.    
  15.    
  16.    
  17.    select * from t05109_b;
  18.    
  19.    delete from t05109_b  where   a=( select c from t05109_c where   t05109_c.d=t05109_b.b ) ;
  20.    
  21.    select  bytes/1024/1024  from user_segments  s where s.segment_name='TBIG';
  22.    
  23.    select   * from v$session
  24.      where sid in ( select blocking_session from v$session);
  25.      
  26.      select  * from v$session where blocking_session is not null;
  27.      
  28.      alter system kill session '141,261' immediate;
复制代码

2014-10-23-plsqlE.sql
  1. select * from user_segments  order by 1;

  2. select  * from user_objects o where  o.OBJECT_NAME='T05110';

  3. create table t05110_b(   a varchar2(20),   b  blob   ) ;
  4. select  * from t05110_b;

  5. create table t05110_c ( a varchar2(20),  b bfile ) ;

  6. insert into t05110_c  values ( 'A', BFILENAME ('DIR1', 'a.jpg'));


  7. select  * from  t05110_c;

  8. select  * from t05110_b;





  9. CREATE OR REPLACE PROCEDURE proc1  (p1 VARCHAR2   , p2 varchar2) AS
  10. v_f BFILE;
  11. v_b blob;
  12.   BEGIN
  13.   INSERT INTO     t05110_b   values(p1 ,EMPTY_BLOB ()) RETURN  b  into v_b;
  14.   v_f := BFILENAME ('DIR1', p2);
  15.    DBMS_LOB.FILEOPEN  (v_f, DBMS_LOB.FILE_READONLY);
  16.    DBMS_LOB.LOADFROMFILE (v_b, v_f,

  17.    DBMS_LOB.GETLENGTH (v_f));
  18.    DBMS_LOB.FILECLOSE (v_f);
  19.    commit;
  20.    end;
  21.    
  22.    
  23.    
  24.    begin
  25.        proc1('A','a.jpg');
  26.        end;
复制代码



回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-29 23:12 , Processed in 0.036944 second(s), 24 queries .

快速回复 返回顶部 返回列表