Create a data table in oracle

Use Oracle tools, or connect the NetBeans IDE to the database and use the SQL editor of The IDE. NetBeans 7.0 currently only provides Beta or development versions, which can improve the connection to the Oracle database. Learn how to connect NetBeans IDE To the Oracle database and how to create users in the database

Use the selected tool to create the following users:

Username phpuser
Phpuserpw
System permission CREATE TABLE

The Code is as follows:	Copy code
CREATE VIEW CREATE SEQUENCE CREATE TRIGGER

Role (Oracle Database 10.x) CONNECT

The Code is as follows:	Copy code
RESOURCE

The following is a set of SQL commands used to create the user. These commands assume that the database has USERS and TEMP tablespaces.

 
 
  
  
  
 
   
 
    
The Code is as follows:
 
    
Copy code
 
   
 
   
 
    
drop user phpuser cascade;

create user phpuser identified by phpuserpw;

grant connect, resource to phpuser;

alter user phpuser default tablespace users temporary tablespace temp account unlock;
 
   
 
  
 
 
Design the structure of the Sample Database

To arrange and store all required data, you need to use two tables:

• One is the wishers table, used to store the name and password of the registered user
• The other is the wishes table, which is used to store the wishes

The wishers table contains three fields:

1. Id-the unique ID of Xu Ke. This field is used as the primary key.
2. Name
3. Password

The wishes table contains four fields:

1. Id-the unique ID of the wish. This field is used as the primary key.
2. Wisher_id-ID of the wishing region. This field is used as a foreign key
3. Description
4. Due_date-Date of the request's wish

These tables are associated with each other by the ID of the specified table. All fields except the due_date in the wishes table are required. Create an Oracle database architecture

1. Log on to the database as the created user. If you connect using NetBeans IDE, use the new user's name and password to create a connection. Make sure that the selected architecture has the same name as the user. (See the connection to Oracle DB section in the "connect to Oracle Database" tutorial .)
2. To create a wishers table, run the following SQL query:

      
      
     
       
       
    
        
    
         
   The Code is as follows:
    
         
   Copy code
    
        
    
        
    
         
   create table wishers (
    id number not null,
    name varchar2(50) unique not null,
    password varchar2(50) not null,
    constraint wishers_pk primary key(id)
   );
    
    
        
    
       
      
      
   To create a wishes table, run the following SQL query. Note that a foreign key will be created to associate your wish with Xu Ke.

3.    
      
     
       
       
    
        
    
         
   The Code is as follows:
    
         
   Copy code
    
        
    
        
    
         
   create table wishes (
    id number not null,
    wisher_id number not null,
    description varchar2(255) not null,
    due_date date,
    constraint wishes_pk primary key(id),
    constraint wishes_fk1 foreign key(wisher_id) references wishers(id)
   );
    
    
        
    
       
      
      
   Verify that the new table is added to the database. If you use NetBeans IDE to connect to the database, go to jdbc: oracle: thin: @ localhost: 1521: XE [PHPUSER on phpuser] in the "service" window to connect to the node. The new table is listed on the "table" node. (If these tables are not displayed, right-click the connection and select "refresh ".)
       

Note: you can download a set of SQL commands here to create an Oracle database table. Add sequence and trigger to add ID valueWhen using the Oracle database, you must specify a sequence to add value. To add value when adding new members to the table, add a trigger.

1. To add a sequence for the wisher table, run the following SQL command:

      
      
     
       
       
    
        
    
         
   The Code is as follows:
    
         
   Copy code
    
        
    
        
    
         
   create sequence wishers_id_seq start with 1 increment by 1;
    
    
        
    
       
      
      
   To trigger a sequence in the ID column of the wishers table when a new sequence is added, run the following SQL command:

2.    
      
     
       
       
    
        
    
         
   The Code is as follows:
    
         
   Copy code
    
        
    
        
    
         
   Create or replace trigger wishers_insert
   Before insert on wishers
   For each row
   Begin
   Select wishers_id_seq.nextval into: new. id from dual;
   End;
    
        
    
       
      
      
   /

3. Add a sequence for the wishes table.

      
      
     
       
       
    
        
    
         
   The Code is as follows:
    
         
   Copy code
    
        
    
        
    
         
   create sequence wishes_id_seq start with 1 increment by 1;
    
    
        
    
       
      
      
   Add a trigger to run the sequence in the ID column of the wishes table when adding a new wish.

4.    
      
     
       
       
    
        
    
         
   The Code is as follows:
    
         
   Copy code
    
        
    
        
    
         
   Create or replace trigger wishes_insert
   Before insert on wishes
   For each row
   Begin
   Select wishes_id_seq.nextval into: new. id from dual;
   End;
    
        
    
       
      
      
   /

Note: you can download a set of SQL commands here to create an Oracle database table, including sequences and triggers. Input Test DataTo test the application, you need to use some data in the database. The following example shows how to add two referers and four wishes.

1. Add a Xu Ke named Tom with the tomcat password.

      
      
     
       
       
    
        
    
         
   The Code is as follows:
    
         
   Copy code
    
        
    
        
    
         
   insert into wishers (name, password) values ('Tom','tomcat');
    
    
        
    
       
      
      
   Add a project named Jerry and the password is jerrymouse.

2.    
      
     
       
       
    
        
    
         
   The Code is as follows:
    
         
   Copy code
    
        
    
        
    
         
   insert into wishers (name, password) values ('Jerry', 'jerrymouse');
   commit;
    
    
        
    
       
      
      
   Add a wish.

3.    
      
     
       
       
    
        
    
         
   The Code is as follows:
    
         
   Copy code
    
        
    
        
    
         
   insert into wishes (wisher_id, description, due_date) 
    values (1, 'Sausage', to_date('2008-04-01', 'YYYY-MM-DD');
   
   insert into wishes (wisher_id, description) 
    values (1, 'Icecream');
   
   
   insert into wishes (wisher_id, description, due_date) values (2, 'Cheese', to_date('2008-05-01', 'YYYY-MM-DD'));
   
   insert into wishes (wisher_id, description)
    values (2, 'Candle');
   commit;
    
    
        
    
       
      
      
   Verify that test data is added. If you use NetBeans IDE To view test data, right-click the relevant table and select "View data" from the context menu ".