Common Oracle function instances

1. Set linesize 100; set the length

2. Set pagesize 30; set the number of entries displayed on each page

3. Run em a. SQL to open notepad.

4. @ A: run the code in file a. You can specify the file path @ D: a.txt.

5. the conn user name/password is used to connect to the database based on the user name and password. If the super Administrator (sys) is connected, the as sysdba should be added;

6. Show user; displays the currently connected user

7. Select * From tab; all tables under the current user are obtained.

8. DESC temp; view the table structure

9. // continue executing the previous query statement

Clear SCR; clear screen

Character Functions

10. Select upper ('coolszy ') from dual; Convert lowercase letters into uppercase letters, and dual into a virtual table.

11. Select lower ('kuka ') from dual; converts uppercase letters to lowercase letters.

12. Select initcap ('kuka ') from dual; uppercase letters

13. Select Concat ('hello', 'World') from dual; connection string, but none | use select Concat ('hello', 'World') from dual;

14. Select substr ('hello', 1, 3) from dual; truncate the string

15. Select length ('hello') from dual; Evaluate the string length

16. Select Replace ('hello', 'l', 'x') from dual; replace string

17. Select substr ('hello',-) from dual; the last three digits are intercepted.

Numeric Functions

18. Select round (789.536) from dual; rounding, rounding

19. Select round (789.536, 2) from dual; retain two decimal places

20. Select round (789.536,-1) from dual; round the integer

21. Select trunc (789.536) from dual; remove decimal places, but do not carry

22. Select trunc (789.536, 2) from dual;

23. Select trunc (789.536,-2) from dual;

24. Select Mod (10%) from dual; returns the result of 3.

Date Functions

25. Select sysdate from dual; returns the current date.

26. Select months_between (sysdate, '16-June-08') from dual; return the number of months

27. Select add_months (sysdate, 4) from dual; add the number of months to the date

28. Select next_day (sysdate, 'monday') from dual; Calculate the next Monday

29. Select last_day (sysdate) from dual; Calculate the last day of the month.

Conversion functions

30. Select to_char (sysdate, 'yyyy') year, to_char (sysdate, 'mm'), to_char (sysdate, 'dd') from dual;

31. Select to_char (sysdate, 'yyyy-mm-dd') from dual;

32. Select to_char (sysdate, 'fmyyyy-mm-dd') from dual; cancel the 0

33. Select to_char ('20140901', '20160901') from dual; split money 9 representation format

34. Select to_char ('000000', 'l99, 100') from dual; Add the coin symbol

35. Select to_number ('123') * to_number ('2') from dual;

36. Select to_date ('2017-07-04 ', 'yyyy-mm-dd') from dual;

Common functions

37. Select nvl (null, 0) from dual; if it is null, use 0 instead.

38. Select decode (, 'content is 1', 2, 'content is 2', 3, 'content is 3') from dual; similar to switch... case...

Transaction Processing

39. Commit; Submit the transaction

40. rollback; roll back the transaction

41. Select rownum from table; the row number is displayed before no column

42. Drop table name cascade Constraint

On Delete casecade when the content in the parent table is deleted, the content in the child table is also deleted.

43. DESC table name display table structure

44. create user [username] identified by [Password] to create a new user

45. grant permission 1. Permission 2... to grant the User Creation permission

Ex: grant create session to [username] at this time, you can only connect to the database

Grant connect, resource to [username]. The permission can meet the requirements.

46. Alter user [username] identified by [Password] Change User Password

47. Alter user [username] password expired prompt for changing the password upon next login

48. Alter user [username] account lock to lock the user

49. Alter user [username] account unlock lock user

50. Grant select, delete on Scott. EMP to [username] give the user two permissions for the EMP table under Scott.

51. Revoke select, delete on Scott. Emo from [username] revoke permissions