14_yl
--
set serveroutput on;
drop table yliopilased;
create table yliopilased as select * from lepikult.yliopilased;
create or replace package immatrikuleerimine IS procedure
kandidaadi_immatrikuleerimine (mitu IN NUMBER);
end immatrikuleerimine;
/
create or replace package BODY immatrikuleerimine IS function idnew return
number is tmpvar number;
BEGIN
SELECT MAX(ID) + 1 INTO tmpvar FROM yliopilased;
RETURN tmpvar;
END idnew;
FUNCTION maxval (rowsfrom NUMBER) RETURN NUMBER IS tmpvar NUMBER;
rowsto NUMBER;
BEGIN
rowsto := rowsfrom + 1;
select min(punkte) INTO tmpvar FROM (
SELECT (
matemaatika + voorkeel + emakeel) as punkte
FROM
scott.kandidaadid
ORDER BY
punkte DESC)
WHERE
ROWNUM < rowsto;
RETURN tmpvar;
END maxval;
PROCEDURE kandidaadi_immatrikuleerimine (mitu IN NUMBER) IS CURSOR imat_kandaadi
IS
select
eesnimi,
perenimi,
(matemaatika + voorkeel + emakeel) as punktid
FROM
scott.kandidaadid
ORDER BY
punktid DESC;
annaabi.ee 
