SQL->>>>

1.

Code:

1. Introduction to the upper function in Oracle
2. Upper ()
3. Converts lowercase letters in a string to uppercase letters.
4. Syntax upper (string)
5. String: string that converts lowercase letters to uppercase letters.
6. If the function is successfully executed, a string after converting lowercase letters to uppercase letters is returned. If an error occurs, an empty string ("") is returned ("").
7. If the value of the string parameter is null, the upper () function returns NULL.
8. For example:
9. SQL> select upper ('this is a test') from dual;
10. Upper ('thisisa
11. ----------------------------
12. This is a test

2.

Code:

1. Dual is a virtual table used to form the select syntax rule. Oracle ensures that there will always be only one record in dual. We can use it to do a lot of things, as shown below:
2. Http://soft.chinabyte.com/database/201/11444701.shtml

3.

Code:

1. Oracle Stored Procedure
2. Http://blog.stnn.cc/sky_drop/Efp_Bl_1002777062.aspx [this blog looks like a book]

4.

Code:

1. Write a stored procedure
3. The stored procedure is written in Oracle's PL/SQL programming language. Let's write a stored procedure that does nothing, we can compile and run it without worrying about any damage to the database.
5. In notepad, write down:
7. Create or replace procedure skeleton
9. Is
11. Begin
13. NULL;
15. End;
17. Save the file as skeleton. SQL.
19. Let's traverse the stored procedure in a row:
21. 1 create or replace procedure skeleton
23. 2 is
25. 3 begin
27. 4 null;
29. 5 end;
31. Row 1:
33. Create or replace procedure is an SQL statement that notifies the Oracle database to create a stored procedure called skeleton, and overwrites it if it exists;
35. Row 2:
37. The is keyword indicates that a PL/SQL body will be followed.
39. Row 3:
41. The begin keyword indicates the start of the PL/SQL body.
43. Row 4:
45. The null PL/SQL statement indicates that nothing is done. This statement cannot be deleted because at least one sentence is required in the PL/SQL body;
47. Row 5:
49. The end keyword indicates the end of the PL/SQL body.

5.

Code:

1. Oracle replace and translate Functions
2. Replace: String-level replacement
3. For example, select Replace ('acdd', 'cd', 'ef ') from dual; → aefd [overall]
4. Translate: character-level replacement
5. For example, select translate ('acdd', 'cd', 'ef ') from dual; → aeff [corresponding]
6.  

7. Practice:

   How can I determine whether a string is a number?

   Solution: Convert first: Because to_string cannot be empty, we use # instead.

   Select translate ('abc123', '#1234567890.', '#') from dual; → ABC

   # In from_string is replaced by # in to_string, but there is no # character in char. Therefore, this step avoids the rule that to_string must not be empty. Then, the numbers and decimal points are converted to null, so the original string contains only three ABC characters.

   After the conversion, use nvl2 to determine:

   Select nvl2 (translate ('abc123', '#1234567890.', '#'), 'string', 'number') from dual; → string

   The role of nvl2 is: nvl2 (expr1, expr2, expr3)-> expr1 is not null, returns expr2; is null, returns expr3. In this way, we can determine whether a string is a number! Solution!

   Http://oracle.chinaitlab.com/exploiture/808365.html

8.  

6.

Code:

1. /** Oracle custom type */
2. Use the create type variable as table of type and create type variable as object (
3. Field 1 Type 1,
4. Field 2 type 2
5. );
6. And the type variable is table of type and the type variable is record (
7. Field 1 Type 1,
8. Field 2 type 2
9. );
10. The difference is that "as" is used after "CREATE,IfDirectly use type followed by is
11. Create is an object, and type is a record.
12. Http://xxciof.blog.163.com/blog/static/79781327200961721245149/

7.

Code:

1. /*** Role of index by binary_integer in Oracle */
2. /*
3. For example, the statement "type numbers is table of number index by binary_integer" adds "index by binary_integer", and the subscript of the numbers type is auto-increment. When the numbers type inserts an element, no Initialization is required, and no extend space is required each time.
4. If the statement "index by binary_integer" is not provided, you must display the initialization of the pair. Extend is required for every time an element is inserted into a numbers table.
5. */
6. Http://oracle.chinaitlab.com/exploiture/836063.html

8.

Code:

1. // Oracle exist
2. The biggest function is to speed up the query of tables with a large amount of data.
3. Select department_id
4. From orders ments d
5. Where exists
6. (Select * from employees e
7. Where D. department_id
8. = E. department_id );
9. This method is applicable when the Department table is small and the Employees table is large.
10. In turn, the query speed will be seriously affected.
12. Select department_id
13. From orders ments D, employees e
14. Where D. department_id
15. = E. department_id;
18. In addition:
19. Http://www.cnblogs.com/yf520gn/archive/2009/01/12/1374359.html

9_20110511

Code:

1. // Oracle rownum
2. However, if you want to use rownum> 10, you need to use nested statements to convert Mr. rownum into and then query it.
3. Select *
4. From (selet rownum as RN, T1. * from a where ...)
5. Where rn> 10
7. This is usually the case when the result set is paged in the code.
9. Look here:
10. Http://bbs.linuxpk.com/thread-10595-1-1.html

10

Code:

1. // Oracle nvl (ARG, value) Method
2. Equivalent to (ARG = NULL )? Value: Arg;