课程第11次（2017-03-19星期日晚上）

botang

上完1Z0-051第3章：单行函数
上完1Z0-051第4章：转换函数/nvl/nvl2/nullif/coalesce以及decode和case
1Z0-051共12章（上完5章），1Z0-052共19章（上完3章），1Z0-053共21章（上完0章）
总共上完全部52章中的8章

pl/sql developper里的内容：

1. SELECT employee_id, last_name, department_id
   
2. FROM   employees
   
3. WHERE  lower(last_name) = 'higgins';
   
4. 
   
5. create index myindx on employees ( lower(last_name)  );
   
6. 
   
7. 
   
8. 
   
9. create table t05103_a( a varchar2(4)  ) ;
   
10. 
    
11. SELECT employee_id, last_name, department_id
    
12. FROM   employees
    
13. WHERE  lower(last_name) = 'higgins';
    
14. 
    
15. create index myindx on employees ( lower(last_name)  );
    
16. 
    
17. 
    
18. 
    
19. create table t05103_a( a varchar2(4)  ) ;
    
20. 
    
21. 
    
22. select  substr('HelloWorld' , -5,1 ) from dual;
    
23. 
    
24. ----
    
25. 
    
26. select  trunc(45.926,-2)  from dual;
    
27. 
    
28. ---
    
29. 
    
30. select  sysdate from dual;
    
31. ---
    
32. 
    
33. select
    
34.   to_char( to_Date ('27-OCT-95','DD-MON-RR'), 'YYYY-MM-DD')
    
35. 
    
36. from dual;
    
37. 
    
38. 
    
39. SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS,
    
40.     months_between( sysdate,  hire_date)  months_sal
    
41.    
    
42. FROM   employees
    
43. WHERE  department_id = 90;
    
44. 
    
45. select to_char( add_months(sysdate ,12 ) ,'YYYY-MM-DD:HH24:MI:SS') from dual;
    
46. 
    
47. select  next_day(sysdate,7)  from dual;
    
48. 
    
49. select  round ( to_date('2017-07-01','YYYY-MM-DD'),'YEAR'   )  from dual;
    
50. 
    
51. 
    
52. select  to_char(sysdate,'fmYYYY-MM-DD:HH24:MI:SS  "The number of day"  ddspth')
    
53.   from dual;
    
54.   
    
55.   SELECT TO_CHAR(salary, '$999,999,999,999.00') SALARY
    
56. FROM   employees
    
57. WHERE  last_name = 'Ernst';
    
58. 
    
59. 
    
60. SELECT TO_CHAR(salary, 'L999,999,999,999.00') SALARY
    
61. FROM   employees
    
62. WHERE  last_name = 'Ernst';
    
63. 
    
64. ---
    
65. select  to_number ( '$6,000.00', 'L999,999,999,999.00')   from dual;
    
66. ----
    
67. SELECT         TO_CHAR(    123456.78     ,'999G999D99',
    
68.         'NLS_NUMERIC_CHARACTERS = ''-#'' '   )
    
69.         "Formatted Salary"
    
70. FROM employees;
    
71. 
    
72. ---
    
73. 
    
74. select  * from employees
    
75.   where employee_id not in (
    
76.   select  nvl(manager_id,0)  from employees );
    
77.   
    
78.   
    
79.   select   employee_id, salary*(1+ nvl(e.commission_pct,0))*12
    
80.    from employees  e ;
    
81.    ---
    
82.    
    
83.    SELECT last_name, job_id, salary,
    
84.        CASE job_id WHEN 'IT_PROG'  THEN  1.10*salary
    
85.                    WHEN 'ST_CLERK' THEN  1.15*salary
    
86.                    WHEN 'SA_REP'   THEN  1.20*salary
    
87.        ELSE      salary END     "REVISED_SALARY"
    
88. FROM   employees;
    
89. 
    
90. SELECT last_name, job_id, salary,
    
91.       decode ( job_id ,'IT_PROG' , 1.10*salary,
    
92.                     'ST_CLERK' , 1.15*salary,
    
93.                    'SA_REP' ,1.20*salary,  salary )   "REVISED_SALARY"
    
94. FROM   employees;
    
95. 
    
96. 
    
97. 
    
98. SELECT last_name, job_id, salary,
    
99.        CASE  
    
100.                   WHEN job_id='IT_PROG'  THEN  1.10*salary
     
101.                     when last_name ='Hunold' then 2*salary
     
102.                    WHEN job_id='ST_CLERK' THEN  1.15*salary
     
103.                    WHEN job_id='SA_REP'   THEN  1.20*salary
     
104.        ELSE      salary END     "REVISED_SALARY"
     
105. FROM   employees;
     
106. ---
     
107. select   department_id, count(commission_pct) from employees  group by department_id ;
     
108. 
     
109. select   employee_id , hire_date  from employees
     
110. order by 2  ;
     
111. 
     
112.     Y2001  Y2003 Y2005 Y2007
     
113.     3           12      15        6
     
114.    
     
115.     ---
     
116.    
     
117.    
     
118.     select          sum(   decode( to_char(hire_date, 'YYYY'), 2001,  1,0)  )          Y2001,
     
119.                          sum(   decode( to_char(hire_date, 'YYYY'),  2003,  1,0)  )          Y2003,
     
120.                        sum(   decode( to_char(hire_date, 'YYYY'),  2005,  1,0)  )             Y2005,
     
121.                      sum(   decode( to_char(hire_date, 'YYYY'),  2007,  1,0)  )             Y2007
     
122.                      
     
123.                      from employees;
     
124.          
     

复制代码

sqlplus里的内容：

1. SQL> startup
   
2. ORACLE instance started.
   
3. 
   
4. Total System Global Area 6680915968 bytes
   
5. Fixed Size                    2213936 bytes
   
6. Variable Size                 3556771792 bytes
   
7. Database Buffers         3087007744 bytes
   
8. Redo Buffers                   34922496 bytes
   
9. Database mounted.
   
10. Database opened.
    
11. SQL> select  * from dual;
    
12. 
    
13. D
    
14. -
    
15. X
    
16. 
    
17. SQL> conn hr/oracle_4U
    
18. Connected.
    
19. SQL> insert into t05103_a values ('中国');
    
20. insert into t05103_a values ('中国')
    
21.                              *
    
22. ERROR at line 1:
    
23. ORA-12899: value too large for column "HR"."T05103_A"."A" (actual: 6, maximum: 4)
    
24. 
    
25. 
    
26. SQL> alter table t05103_a modify ( a  varchar2(6))  ;
    
27. 
    
28. Table altered.
    
29. 
    
30. SQL> insert into t05103_a values ('中国');
    
31. 
    
32. 1 row created.
    
33. 
    
34. SQL> commit;
    
35. 
    
36. Commit complete.
    
37. 
    
38. SQL> select  length(a)  from t05103_a;
    
39. 
    
40. LENGTH(A)
    
41. ----------
    
42.          2
    
43. 
    
44. SQL> select  lengthb(a)  from t05103_a;
    
45. 
    
46. LENGTHB(A)
    
47. ----------
    
48.          6
    
49. 
    
50. SQL> select  sysdate from dual;
    
51. 
    
52. SYSDATE
    
53. ------------------
    
54. 19-MAR-17
    
55. 
    
56. SQL> select  to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS')   from dual;
    
57. 
    
58. TO_CHAR(SYSDATE,'YY
    
59. -------------------
    
60. 2017-03-19:20:03:21
    
61. 
    
62. SQL> exit
    
63. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    
64. With the Partitioning, Automatic Storage Management, OLAP, Data Mining
    
65. and Real Application Testing options
    
66. [oracle@station90 ~]$ sqlplus /nolog
    
67. 
    
68. SQL*Plus: Release 11.2.0.1.0 Production on Fri Mar 19 20:05:30 1999
    
69. 
    
70. Copyright (c) 1982, 2009, Oracle.  All rights reserved.
    
71. 
    
72. SQL> conn hr/oracle_4U
    
73. Connected.
    
74. SQL>  select  to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS')   from dual;
    
75. 
    
76. TO_CHAR(SYSDATE,'YY
    
77. -------------------
    
78. 1999-03-19:20:05:49
    
79. 
    
80. SQL> exit
    
81. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    
82. With the Partitioning, Automatic Storage Management, OLAP, Data Mining
    
83. and Real Application Testing options
    
84. [oracle@station90 ~]$ sqlplus /nolog
    
85. 
    
86. SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 19 20:08:36 2017
    
87. 
    
88. Copyright (c) 1982, 2009, Oracle.  All rights reserved.
    
89. 
    
90. SQL> conn hr/oracle_$U
    
91. ERROR:
    
92. ORA-01017: invalid username/password; logon denied
    
93. 
    
94. 
    
95. SQL> conn hr/oracle_4U
    
96. Connected.
    
97. SQL> select  sysdate from dual;
    
98. 
    
99. SYSDATE
    
100. ------------------
     
101. 19-MAR-17
     
102. 
     
103. SQL>  select  to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS')   from dual;
     
104. 
     
105. TO_CHAR(SYSDATE,'YY
     
106. -------------------
     
107. 2017-03-19:20:08:53
     
108. 
     
109. SQL> select  next_day(sysdate,7)  from dual;
     
110. 
     
111. NEXT_DAY(SYSDATE,7
     
112. ------------------
     
113. 25-MAR-17
     
114. 
     
115. SQL> select  to_char(next_day(sysdate,7),'YYYY-MM-DD:HH24:MI:SS')   from dual;
     
116. 
     
117. TO_CHAR(NEXT_DAY(SY
     
118. -------------------
     
119. 2017-03-25:20:26:05
     
120. 
     
121. SQL> alter session set nls_territory='australia';
     
122. 
     
123. Session altered.
     
124. 
     
125. SQL> select  to_char(next_day(sysdate,7),'YYYY-MM-DD:HH24:MI:SS')   from dual;
     
126. 
     
127. TO_CHAR(NEXT_DAY(SY
     
128. -------------------
     
129. 2017-03-26:20:27:03
     
130. 
     
131. SQL> select  to_char(next_day(sysdate,'Sunday'),'YYYY-MM-DD:HH24:MI:SS')  from dual;
     
132. 
     
133. TO_CHAR(NEXT_DAY(SY
     
134. -------------------
     
135. 2017-03-26:20:29:11
     
136. 
     
137. SQL> select  to_char(sysdate,'YYYY-MONTH-DD:HH24:MI:SS')  from dual;
     
138. 
     
139. TO_CHAR(SYSDATE,'YYYY-MONTH-DD:HH24:MI:SS')
     
140. -----------------------------------------------------
     
141. 2017-MARCH    -19:20:51:46
     
142. 
     
143. SQL> alter session set nls_language='simplified chinese';
     
144. 
     
145. Session altered.
     
146. 
     
147. SQL> select  to_char(sysdate,'YYYY-MONTH-DD:HH24:MI:SS')  from dual;
     
148. 
     
149. TO_CHAR(SYSDATE,'YYYY-MON
     
150. -------------------------
     
151. 2017-3月 -19:20:53:19
     
152. 
     
153. SQL> alter session set nls_language='korean';
     
154. 
     
155. Session altered.
     
156. 
     
157. SQL> select  to_char(sysdate,'YYYY-MONTH-DD:HH24:MI:SS')  from dual;
     
158. 
     
159. TO_CHAR(SYSDATE,'YYYY-MON
     
160. -------------------------
     
161. 2017-3월 -19:20:53:34
     
162. 
     
163. SQL> alter session set nls_language='german';
     
164. 
     
165. Session altered.
     
166. 
     
167. SQL> select  to_char(sysdate,'YYYY-MONTH-DD:HH24:MI:SS')  from dual;
     
168. 
     
169. TO_CHAR(SYSDATE,'YYYY-MONTH-DD:HH24:MI:SS')
     
170. -----------------------------------------------------
     
171. 2017-MÄRZ     -19:20:54:16
     
172. 
     
173. SQL>  alter session set nls_language='italian' ;
     
174. 
     
175. Session altered.
     
176. 
     
177. SQL> select  to_char(sysdate,'YYYY-MONTH-DD:HH24:MI:SS')  from dual;
     
178. 
     
179. TO_CHAR(SYSDATE,'YYYY-MONTH-DD:HH24:MI:SS')
     
180. -----------------------------------------------------
     
181. 2017-MARZO    -19:20:55:58
     
182. 
     
183. SQL> alter session set nls_territory='australia';
     
184. 
     
185. Session altered.
     
186. 
     
187. SQL> select  to_char(sysdate,'fmYYYY-MM-DD:HH24:MI:SS  D')  from dual;
     
188. 
     
189. TO_CHAR(SYSDATE,'FMYYY
     
190. ----------------------
     
191. 2017-3-19:20:58:32  7
     
192. 
     
193. SQL>
     

复制代码