第52次：2014-10-23

botang

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. 
   
8. 
   
9. SELECT employee_id, last_name, job_id, salary
   
10. FROM   employees
    
11. WHERE  salary < any
    
12.                     (SELECT salary
    
13.                      FROM   employees
    
14.                      WHERE  job_id = 'IT_PROG')
    
15. AND    job_id <> 'IT_PROG';
    
16. 
    
17. SELECT * FROM departments
    
18. WHERE NOT EXISTS
    
19. (SELECT * FROM employees
    
20. WHERE employees.department_id=departments.department_id);
    
21. 
    
22. 
    
23. SELECT * FROM departments
    
24. WHERE  not EXISTS
    
25. (SELECT 'X'  FROM employees
    
26. WHERE employees.department_id=departments.department_id);
    
27. 
    
28. 
    
29.   
    
30. SELECT * FROM departments
    
31. WHERE  department_id  not   in
    
32. (SELECT       employees.department_id   FROM employees
    
33. WHERE employees.department_id=departments.department_id);
    
34. 
    
35. 
    
36. 
    
37. select  employee_id, last_name, salary  
    
38. from employees e  
    
39. where  salary > ( select  avg(salary)  from employees a  where a.department_id=e.department_id
    
40.                              group by  a.department_id ) ;
    
41.                              
    
42. 
    
43. select   employee_id, last_name, salary  
    
44. from employees e  , ( select  avg(salary) avgsal  , department_id  from  employees group by department_id) a
    
45. where e.department_id=a.department_id
    
46.   and e.salary > a.avgsal;
    
47. 
    
48. 
    
49. 
    
50. 
    
51. SELECT emp.last_name
    
52. FROM   employees emp
    
53. WHERE  emp.employee_id NOT IN
    
54.                            (SELECT mgr.manager_id
    
55.                             FROM   employees mgr);
    
56. 
    
57. 
    
58. 
    
59. 
    

复制代码

2014-10-23-plsqlB.sql

1. select  * from job_history  order by 1,2;
   
2. 
   
3. select   employee_id, job_id from employees where employee_id  in (101,176,200);
   
4. 
   
5. SELECT employee_id, job_id
   
6. FROM   employees
   
7. minus
   
8. SELECT   employee_id , job_id
   
9. FROM   job_history  ;
   
10. 
    
11. 
    
12. 
    
13. 
    
14. SELECT   employee_id  , job_id xxx
    
15. FROM   job_history  
    
16. minus
    
17. SELECT employee_id, job_id  yyy
    
18. FROM   employees  order by  xxx;
    
19. 
    

复制代码

2014-10-23-plsqlC.sql

1. create table t05109_a ( a  number default 999) ;
   
2. 
   
3. select  * from user_tab_columns tc where tc.TABLE_NAME='T05109_A';
   
4. 
   
5. insert into  t05109_a values ( default);
   
6. 
   
7. select  * from t05109_a;
   
8. 
   
9. alter table t05109_a   modify ( a default 888 ) ;
   
10. 
    
11. insert into  t05109_a values ( default);
    
12. 
    
13. insert into t05109_a values (&s_1);
    
14. 
    
15. insert into t05109_a values (&&s_1);
    
16. 
    

复制代码

2014-10-23-plsqlD.sql

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

复制代码

2014-10-23-plsqlE.sql

1. select * from user_segments  order by 1;
   
2. 
   
3. select  * from user_objects o where  o.OBJECT_NAME='T05110';
   
4. 
   
5. create table t05110_b(   a varchar2(20),   b  blob   ) ;
   
6. select  * from t05110_b;
   
7. 
   
8. create table t05110_c ( a varchar2(20),  b bfile ) ;
   
9. 
   
10. insert into t05110_c  values ( 'A', BFILENAME ('DIR1', 'a.jpg'));
    
11. 
    
12. 
    
13. select  * from  t05110_c;
    
14. 
    
15. select  * from t05110_b;
    
16. 
    
17. 
    
18. 
    
19. 
    
20. 
    
21. CREATE OR REPLACE PROCEDURE proc1  (p1 VARCHAR2   , p2 varchar2) AS
    
22. v_f BFILE;
    
23. v_b blob;
    
24.   BEGIN
    
25.   INSERT INTO     t05110_b   values(p1 ,EMPTY_BLOB ()) RETURN  b  into v_b;
    
26.   v_f := BFILENAME ('DIR1', p2);
    
27.    DBMS_LOB.FILEOPEN  (v_f, DBMS_LOB.FILE_READONLY);
    
28.    DBMS_LOB.LOADFROMFILE (v_b, v_f,
    
29. 
    
30.    DBMS_LOB.GETLENGTH (v_f));
    
31.    DBMS_LOB.FILECLOSE (v_f);
    
32.    commit;
    
33.    end;
    
34.    
    
35.    
    
36.    
    
37.    begin
    
38.        proc1('A','a.jpg');
    
39.        end;

复制代码