set serveroutput on

create or replace procedure check_salary (v_job_id  in employees.job_id%type,v_salary in employees.salary%type) is

declare

min_sal number;

max_sal number;

begin

select min(salary),max(salary) into min_sal,max_sal

from emloyees

where job_id = v_job_id;

if not (v_salary between min_sal and max_sal)

then

dbms_output.put_line('error');

--raise_application_error(22222,'Invalid salary <sal>. Salaries for job <jobid> must be between <min> and <max>');

end if;

end check_salary;

-----------------------------------------------------------------------------------------------------------------------------------------------------

 

create or replace trigger check_salary_trg 

before insert or update  on employees

for each row

declare

begin

-----here call procedure

end;

 

 

 

 

-------------------------------------------------------------------

practice in trigger

 but i reached to this point but i don't know this correct or not

 it gives me compilation error

if any one have solutions of practices in pl/sql of oracle 10g

can send to me

i want it necessary

 

  The rows in the JOBS table store a minimum and maximum salary allowed for different JOB_ID values. You are asked to write code to ensure that employees’ salaries fall in the range allowed for their job type, for insert and update operations.

a.  Write a procedure called CHECK_SALARY that accepts two parameters, one for an employee’s job ID string and the other for the salary. The procedure uses the job ID to determine the minimum and maximum salary for the specified job. If the salary parameter does not fall within the salary range of the job, inclusive of the minimum and maximum, then it should raise an application exception, with the message “Invalid salary <sal>. Salaries for job <jobid> must be between <min> and <max>”. Replace the various items in the message with values supplied by parameters and variables populated by queries.

b.  Create a trigger called CHECK_SALARY_TRG on the EMPLOYEES table that fires before an INSERT or UPDATE operation on each row. The trigger must call the CHECK_SALARY procedure to carry out the business logic. The trigger should pass the new job ID and salary to the procedure parameters.

تم تعديل 27 أبريل 201312 سنة بواسطة منمون علي

اعملي show errors لتكتشفي الخطأ ... 

اعملي show errors لتكتشفي الخطأ ... 

هوة اداني الارور ده

Warning: Procedure created with compilation errors.

Errors for PROCEDURE CHECK_SALARY:

 

LINE/COL ERROR 2/1 PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following: begin function package pragma procedure subty pe type use <an identifier> <a double-quoted delimited-identif ier> form current cursor external language The symbol "begin" was substituted for "DECLARE" to continue.

 

 

------------------------ الكود-------------------

 

set serveroutput on

create or replace procedure check_salary (v_job_id  in employees.job_id%type,v_salary in employees.salary%type) is

declare

min_sal number;

max_sal number;

begin

select min(salary),max(salary) into min_sal,max_sal

from emloyees

where job_id = v_job_id;

if not (v_salary between min_sal and max_sal)

then

dbms_output.put_line('error');

--raise_application_error(22222,'Invalid salary <sal>. Salaries for job <jobid> must be between <min> and <max>');

end if;

end check_salary;

end;

/

show error

تم تعديل 30 أبريل 201312 سنة بواسطة منمون علي

جربي اشطبي كلمة declare نهائياً أتوقع أن ال error سيختفي ...