oracle--determine if an object exists (not completed)

First, the system table:

1. User_tables: Store information of all the tables under the user;

2. Dba_tables: Store All the information of the table under Administrator's permission;

3. All_tables: Stores information for all tables.

Second, determine whether the object exists

1. Judgment table

We can only determine if the current table exists by using SELECT COUNT (*), return 1 for existence, and 0 for non-existence, for example:
SELECT COUNT (*) from user_tables WHERE table_name = ' CODE_BMDM '; (this method can also be used in SQL)
It is important to note that table names (or other object names) must all be uppercase, except for special characters (there are special characters such as spaces between table names), otherwise the query is not available. The User_tables (all tables under the user) can also be replaced with dba_tables (all tables under Administrator privileges) or all_tables (all tables)

2. Declare
4. V_exists number;
6. Begin
8. --1, Task type TASK_TYPE_CD build Table ...
10. Select COUNT (*) into v_exists from user_tables where table_name = ' EDW_T99_TASK_TYPE_CD ';
12. if v_exists > 0 Then
14. Execute immediate ' drop table edw_t99_task_type_cd ';
16. End if;
18. Execute Immediate '
20. CREATE TABLE Edw_t99_task_type_cd
22. (
24. CODE_CD VARCHAR2 () PRIMARY KEY,
26. Code_desc VARCHAR2 (100)
28. )‘;
30. Execute immediate ' comment on table edw_t99_task_type_cd is 'task type ';
32. Execute immediate ' comment on column edw_t99_task_type_cd.  CODE_CD is ' 'code ';
34. Execute immediate ' comment on column edw_t99_task_type_cd.  Code_desc is ' 'code description ';
38. --2, buy Product code BUY_TYPE_CD build Table ...
40. Select COUNT (*) into v_exists from user_tables where table_name = ' EDW_T99_BUY_TYPE_CD ';
42. if v_exists > 0 Then
44. Execute immediate ' drop table edw_t99_buy_type_cd ';
46. End if;
48. Execute Immediate '
50. CREATE TABLE Edw_t99_buy_type_cd
52. (
54. CODE_CD VARCHAR2 () PRIMARY KEY,
56. Code_desc VARCHAR2 (100)
58. )‘;
60. Execute immediate ' comment on table edw_t99_buy_type_cd is 'buy product code ';
62. Execute immediate ' comment on column edw_t99_buy_type_cd.  CODE_CD is ' 'code ';
64. Execute immediate ' comment on column edw_t99_buy_type_cd.  Code_desc is ' 'code description ';
68. End
70. /

oracle--determine if an object exists (not completed)