2023.02.15

<코드>

declare
    flag number;
begin
    for i in 2..1000
    loop
        flag :=0;
        for j in 2..i-1
        loop
            if mod(i,j)=0 then flag :=1; goto inprime;
            end if;
        end loop;
        <<inprime>>
        if flag=0 then
        	insert into prime(serial_no) values(i);
            dbms_output.put_line(i);
        end if;
    end loop;
end;
/

declare
    i number :=2;
    j number :=2;
begin
    loop
        j := 2;
        loop
            exit when( ( mod(i, j) = 0 )or ( j = i ) );
            j := j + 1;
        end loop;
        if( j = i )then
          dbms_output.put_line(i||'   ');
         -- insert into prime(serial_no) values(i);
        end if;
        i := i + 1;
        exit when i = 1000;
    end loop;
end;
/

<결과>

 

=========================

create or replace function 함수명(매개변수1,...,매개변수n)

return 반환값데이터타입;

is

    변수선언

begin

    실행문(s);

     return 반환값; =>반환값은 위의 반환값 데이터와 같다.

end;

/

<코드>

create or replace function salary_total(d_id number)
return number
is
    total number;
begin
    select sum(salary) into total 
    from employees where department_id=d_id;
    return total;
end;
/
select department_name, department_id, salary_total(department_id) from departments;

<결과>

27개가 나온다.

<코드>

부서명, 부서장, 부서인원, 부서 월급 합계

--부서별 인원수
create or replace function countMember(de_id number)
return number
is
    countm number;
begin
    select count(department_id) into countm
    from employees where department_id=de_id;
    return countm;
end;
/
--부서장
create or replace function getCaption(m_id number)
return varchar2
is
    cap employees.emp_name%type;
    --cap varchar2(50);->이렇게도 가능
begin 
    select a.emp_name into cap
    from employees a, departments b
    where (m_id=b.manager_id)
    and a.employee_id=m_id;
    return cap;
end;
/
--부서별 월급 합계
create or replace function salary_total(d_id number)
return number
is
    total number;
begin
    select sum(salary) into total 
    from employees where department_id=d_id;
    return total;
end;
/
select department_name,getCaption(manager_id),countMember(department_id) 인원수,
       salary_total(department_id)
from departments;

<결과>

27개 나온다.

<코드>

상위부서

create or replace function getParent(pid number)
return varchar2
is
    par departments.department_name%type;
begin
    select department_name
    into par
    from departments
    where department_id=pid;
    return par;
end;
/
select department_id,department_name,getCaption(manager_id),getParent(parent_id) from departments;

<결과>

27개 나온다.

=======================================================

create or replace procedure 프로시저명(매개변수1,...,매개변수n)

is

    변수선언

begin

     실행문(s);

end;

/

<코드>

create or replace procedure PrimeNumber
is
    flag number;
begin
    for i in 2..1000
    loop
        flag :=0;
        for j in 2..i-1
        loop
            if mod(i,j)=0 then flag :=1; goto inprime;
            end if;
        end loop;
        <<inprime>>
        if flag=0 then
            insert into prime values(i);
            dbms_output.put_line(i);
        end if;
    end loop;
end;
/
exec PrimeNumber;
select * from prime;

<결과>

168개 나온다.

<코드>

create or replace procedure printout(p1 varchar2)
is
begin
    dbms_output.put_line(p1);
end;
/

<결과>

prinout으로 대신할수 있다.

<코드>

create or replace procedure gugu
is
begin
    for i in 2..9
    loop
        for j in 1..9
        loop
            dbms_output.put_line(i||'x'||j||'='||(i*j));
        end loop;
    end loop;
end;
/

====================================================

매개변수1 in/out/in out    타입명

in : 매개변수가 프로시저내에 값을 전달만 하는 역할(into procedure)

out : 매개변수가 프로시저 밖에 값을 전달만 하는 역할(out of procedure)

in out : 매개변수가 값을 양방향으로 전달하는 역할 (into/out of procedure)

<코드>

create or replace procedure inoutp(a in number, b out number, c in out number)
is
    x number;
    y number;
    z number;
begin
    printout('a='||a||',b='||b||',c='||c);
    x:=a*10;
    y:=b*10;
    z:=c*10;
    printout('a*10='||x||',b*10='||y||',c*10='||z);
end;
/

declare
    i number :=1;
    j number :=2;
    k number :=3;
begin
    printout('i='||i||',j='||j||',k='||k);
    inoutp(i,j,k);
    printout('i='||i||',j='||j||',k='||k);
end;
/

<결과>

<코드>

create or replace procedure inoutp(a in number, b out number, c in out number)
is
    x number;
    y number;
    z number;
begin
    printout('a='||a||',b='||b||',c='||c);
   -- x:=a*10;
    b:=b*10;
    c:=c*10;
    printout('a*10='||a||',b*10='||b||',c*10='||c);
end;
/

declare
    i number :=1;
    j number :=2;
    k number :=3;
begin
    printout('i='||i||',j='||j||',k='||k);
    inoutp(i,j,k);
    printout('i='||i||',j='||j||',k='||k);
end;
/

<결과>

a는 in이라 나오지 않는다.

<코드>

create or replace procedure dan99( i in number, j in number, m out number)
is
begin
    m:=i*j;
end;
/
declare
    k number;
begin
    for i in 2..9
    loop
        for j in 1..9
        loop
            dan99(i,j,k);
            printout(i||'x'||j||'='||k);
        end loop;
    end loop;
end;
/

<결과>

구구단 결과가 나온다

===========================

<알수없는코드로구만...>

drop table gugudan;
create table gugudan(
    content varchar2(100)
);
select * from gugudan;
create or replace procedure gugudan
is
begin
    for i in 2..9
    loop
        for j in 1..9
        loop
            insert into gugudan values (i||'x'||j||'='||(i*j));
        end loop;
    end loop;
end;
/

왜지.. 왜 임티가 안나오지... ㅠㅠㅠㅠ