장바구니 번호 생성 함수
create or replace function fn_create_cart_no(
p_date in date, p_mid in pc03.member.mem_id%type)
return varchar2
as
v_cnt number:=0; --해당일자의 자료수(행의 수)
v_cart_no pc03.cart.cart_no%type; -- pc03 계정의 cart 테이블의 cart_no와 같은 타입이라는 뜻
v_mid pc03.member.mem_id%type;
begin
-- select (데이터) into (데이터를 받을 변수) main 쿼리일때만 into 사용. subquery일 때는 안씀
-- 하나의 변수에 여러개의 데이터를 넣을 수 없으니까 cursor 사용
-- cursor를 실행하기 위해 반복문 사용
select count(*) into v_cnt
from pc03.cart
where to_date(substr(cart_no,1,8)) = p_date;
if v_cnt = 0 then -- 오늘날짜가 없을 때. 즉 최초로 들어왔을 때
v_cart_no := to_char(p_date,'YYYYMMDD') || trim('00001'); -- 자리를 맞춰주기 위해 자동으로 중간에 공백을 넣을 수 있기 때문에 뒤에 붙이는 애들은 공백 제거해줘야함
else
select max(cart_no) into v_cart_no
from pc03.cart
where to_date(substr(cart_no,1,8)) = p_date;
select distinct cart_member into v_mid
from pc03.cart
where cart_no = v_cart_no;
if p_mid != v_mid then
v_cart_no := v_cart_no + 1;
end if;
end if;
return v_cart_no;
end;
23년 4월 8일 장바구니 번호 조회
select fn_create_cart_no(to_date('20230408'),'m001') from dual;
구매했을 때 cart 테이블에 저장
-- 2023 04 08 a001회원이 p201000001 상품을 5개 구입 -> cart에 저장
insert into pc03.cart values('a001',
fn_create_cart_no(to_date('20230408'),'a001'),
'P201000001',
5);
2023년 5월 매출액이 가장 많은 상품 5개의 납품거래처의 거래처번호, 거래처명, 주소 조회
select c.cid
from(
select a.cart_prod as cid,
sum(cart_qty) as csum
from pc03.cart a , pc03.prod b
where a.cart_prod = b.prod_id
and a.cart_no like '202305%'
group by a.cart_prod
order by csum desc)c
where rownum<=5;
from - where - group by - having - select - order by 라서
group by는 select 전, 즉 alias 처리 전이라 alias(cid)를 못쓰지만 order by에는 alias(csum) 사용 가능
declare
cursor cur_buyer_5 is
select c.cid as ccid
from(
select a.cart_prod as cid,
sum(cart_qty) as csum
from pc03.cart a , pc03.prod b
where a.cart_prod = b.prod_id
and a.cart_no like '202305%'
group by a.cart_prod
order by csum desc)c
where rownum<=5;
v_bid pc03.buyer.buyer_id%type;
v_name pc03.buyer.buyer_name%type;
v_baddr varchar2(200);
begin
for rec in cur_buyer_5 loop
select b.buyer_id, b.buyer_name, b.buyer_add1||' '|| b.buyer_add2
into v_bid, v_name, v_baddr
from pc03.buyer b, pc03.prod p
where b.buyer_id = rec.ccid;
dbms_output.put_line(v_bid||' '||v_name|| ' '||v_baddr);
end loop;
end;