Company Database Employee Empno Ename Job MGR Hiredate Sal Comm DeptNo President

Question

Company Database Employee Empno Ename Job MGR Hiredate Sal Comm DeptNo President 17-Nov-81 10 7698 Blake 7782 Clark Mana ger 7839 09-Jun-812450 7839 02-Apr-81 10 20 30 30 7654 Martin Sa 7499 Allen Sales 7698 20-Feb-8116 Turner Sa 769808-Sep-8115 900James Clerk 7521 Ward Sa 7902 Ford 7369 Smith Clerk 7788Scott 7876 Adams Clerk 7934 Miller 7698 22-Feb-81 125 7566 03-Dec-81 3000 7902 17-Dec-8 7566109-Dec-8 20 20 20 10 7782 23-Jan-8213 Department DeptNo Dname 10 20 30 40 Research Sales Abu Dhabi rations Dubai 1. Write a PL/SQL statements that Create a stored procedure named ADD_JOB to insert a new job into the JOBS table. The procedure has 4 parameters that correspond to the job id, job title, min salary, and max_salary The job table description is as follows (job_ id number, job title varchare(20), min_sal number, max_sal number) Write a PL/SQl program (block) that read 4 variable from the keyboard and call the procedure ADD Job to insert a new job in the table Jobs

Explanation / Answer

Answer is as follows :

For the given scenario first of all we have to write create table statement for job tables as follows :

CREATE TABLE JOBS (

job_id NUMBER(10) NOT NULL,

job_title VARCHAR(20) NOT NULL,

min_sal NUMBER(7) NOT NULL,

max_sal NUMBER(10) NOT NULL,

PRIMARY KEY (job_id)

)

So for this created table JOBS the Stored Procedure for insertion is as follows :

CREATE OR REPLACE PROCEDURE ADD_JOB(

job_id IN JOB.job_id%TYPE,

job_title IN JOB.job_title%TYPE,

min_salary IN JOB.min_sal%TYPE,

max_salary IN JOB.max_sal%TYPE)

IS

BEGIN

INSERT INTO ADD_JOB("job_id", "job_title", "min_sal", "max_sal")

VALUES(job_id,, job_title, min_salary,max_salary)

COMMIT ;

END;

/

if there is any query please ask in comments...

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.