[ Pobierz całość w formacie PDF ]
.put_line('Not exists'); if the //displays message that person doesn't existswhen change_grade thendbms_output.put_line('Changing grade not allowed!'); //if not in losal and hisalend; //end of procedure/___________________________________________________________________________Addcustom procedure is used to adding a new customers.Filling in table customer.Also exceptions are included.create or replace procedure Addcustom( //creating a procedure, with given parametersCname IN customer.cname%type, //%cname means, that it will be the sam type asCsurname IN customer.csurname%type, in `parent' tableCadress IN customer.cadress%type,Cphone IN customer.cphone%type)ASthesame exception; //declaration of exceptionscounter integer;Cid customer.cid%type; //cid - customer index , the same type as in customer tableBEGINselect count(*) into counter from customer c where c.csurname=Csurname;if counter != 0 //if counter != it means that user with the same surname existsthen raise thesame; that's not allowed so the error is raisedend if;select NVL(max(customer.cid)+1,1) into Cid //adds index, increasing by 1 for new customerfrom customer;insert into customer //new row is filled by given valuesvalues(Cid,Cname,Csurname,Cadress,Cphone);exceptionwhen thesame thendbms_output.put_line('Warning! The same surname exists!!'); //error raises when the samesurnameend; //end of procedure/___________________________________________________________________________Twolowest procedure displays 2 lowest salaries of all employees.As a parameter while executing we give an number.create or replace procedure twolowest(param NUMBER) //creating a procedureAScursor salaries is //declaring a cursor, which returns all rows from staff ordered by salselect sname,ssurname,salary from staff order by salary ; //cursor datamy_salaries salaries%rowtype;counter INTEGER;no_workers exception;BEGINselect count(*) into counter from staff;if counter=0 then raise no_workers; //checks if there are any workerselseDBMS_output.put_line('LOWEST EARNINGS:');open salaries;fetch salaries into my_salaries; //takes firs row from cursorDBMS_output.put_line('1:'||my_salaries.sname||' '||my_salaries.ssurname||' '||my_salaries.salary||'.'); //displays first rowfetch salaries into my_salaries;DBMS_output.put_line('2:'||my_salaries.sname||' '||my_salaries.ssurname||' '||my_salaries.salary||'.'); //takes second row from cursorclose salaries; //close cursorEND IF;EXCEPTION //definition of exceptionswhen no_workers thenDBMS_output.put_line('YOU SHOULD HAVE AT LEAST 2 STAFF WORKERS IN DataBase');END;/ [ Pobierz całość w formacie PDF ]