Read data from user and insert them to table : Console Prompt Read « PL SQL « Oracle PL / SQL

1. Oracle PL / SQL
2. PL SQL
3. Console Prompt Read

Read data from user and insert them to table

--Creating the employee table.  
CREATE TABLE employee   
(emp_id           INTEGER,  
 emp_name      VARCHAR2(32),    
 supervised_by      INTEGER,    
 pay_rate               NUMBER(9,2),    
 pay_type              CHAR,    
 emp_dept_id                 INTEGER);  
        
ALTER TABLE employee    
ADD CONSTRAINT pk_emp primary key (emp_id);

insert into employee values (1,'Jack Chen',3,100.50, H, 3);
insert into employee values (2,'Jason Lee',1,300.50, H, 3);
insert into employee values (3,'Ala Wang',5,200.50, H, 3);

-- Creating the department table. 
CREATE TABLE department 
(dept_id           INTEGER,  
 dept_name     VARCHAR2(32));    
        
ALTER TABLE department  
    ADD CONSTRAINT PRIMARY KEY (dept_id);


insert into department values (1,'Computer');
insert into department values (2,'Publish');
insert into department values (3,'Management');


-- Creating the emp_dept table.   
CREATE TABLE emp_dept
(emp_id           INTEGER,   
 dept_id            INTEGER, 
 CONSTRAINT unq_1 unique (emp_id, dept_id));

insert into emp_dept values(1,3);
insert into emp_dept values(2,3);
insert into emp_dept values(3,3);

select * from employee;
select * from department;
select * from emp_dept;



-- Inserting records with PL/SQL code.    
DECLARE               
    i_dept_id  INTEGER, 
    i_dept_name,    
BEGIN   
    INSERT into department values (&i_dept_id,'&dept_name');  
END;    
        
COMMIT;               
        
DECLARE             
    i_id   INTEGER; 
    e_id  INTEGER;  
    i_name  VARCHAR2(32);   
    i_super  INTEGER;   
    i_rate    NUMBER(9,2);  
    i_type   CHAR;  
    i_emp_dept INTEGER; 
    e_emp_dept INTEGER; 
BEGIN   
    e_id:=12; 
    e_emp_dept:=12;    
        
    INSERT into employee values (e_id, '&i_name',&i_super,&i_rate,'&i_type',e_emp_dept);    
END;    
/



select * from employee;
select * from department;
select * from emp_dept;


drop table employee;
drop table department;
drop table emp_dept;
           
       

Related examples in the same category