κ΅μ¬ κΈ°λ₯μλ κ°μ κ³νμ μ‘°ν, λ°°μ μ
μΆλ ₯, μ±μ μ
μΆλ ₯, μΆκ²° κ΄λ¦¬κ° μλ€.
ν΄λΉ κΈ°λ₯μ νμν ansi-sqlμ μμ±νμκ³ , μ΄λ₯Ό κΈ°λ°μΌλ‘ ν΄λΉ κΈ°λ₯μ ꡬννλ pl/sqlλ μμ±νμλ€.
μλλ μμ±λ μ½λ μ€ μΌλΆμ΄λ€.
-- κ³Όμ λ²νΈ μ
λ ₯
-- κ΅μ‘μ μ 보 μΆλ ₯
-- (λ²νΈ) (κ΅μ‘μλ²νΈ) κ΅μ‘μμ΄λ¦ μ νλ²νΈ λ±λ‘μΌ μλ£|μ€λμ¬λΆ
-- ex) κ³Όμ λ²νΈ = 2
select * from tblcourse; -- κ³Όμ
select * from tblcourselist; -- μκ° κ³Όμ λͺ©λ‘
select * from tblstudent; -- κ΅μ‘μ
--select studentseq from tblCourseList where courseseq = 2; -- 2λ² κ³Όμ μ λ£λ νμ λ²νΈ
select
studentseq as κ΅μ‘μλ²νΈ, name as κ΅μ‘μμ΄λ¦, phone as μ νλ²νΈ, registerdate as λ±λ‘μΌ, compldropstatus as μλ£μ€λνλ½μ¬λΆ
from tblstudent
where processseq = 2;
create or replace procedure procStudentList(
pCourse number
)
is
cursor vcursor
is
select
studentseq as κ΅μ‘μλ²νΈ, name as κ΅μ‘μμ΄λ¦, phone as μ νλ²νΈ, registerdate as λ±λ‘μΌ, compldropstatus as μλ£μ€λνλ½μ¬λΆ
from tblstudent
where processseq = 2;
vrow (select
studentseq as κ΅μ‘μλ²νΈ, name as κ΅μ‘μμ΄λ¦, phone as μ νλ²νΈ, registerdate as λ±λ‘μΌ, compldropstatus as μλ£μ€λνλ½μ¬λΆ
from tblstudent
where processseq = 2)%rowtype;
begin
dbms_output.put_line('κ΅μ‘μλ²νΈ' || ' ' || 'κ΅μ‘μμ΄λ¦' || ' ' || 'μ νλ²νΈ' || ' ' || 'λ±λ‘μΌ' || ' ' || 'μλ£μ€λνλ½μ¬λΆ');
open vcursor;
loop
fetch vcursor into vrow;
exit when vcursor%notfound;
dbms_output.put_line(' ' || vrow.studentseq || ' ' || vrow.name || ' ' || vrow.tel);
end loop;
close vcursor;
end;
begin
procStudentList(2);
end;