rollup 합계, sql function, view, procedure

 

-with rollup -
2개 이상의 컬럼을 이용하여 그룹을 짖는 경우 
 그룹별 중갑 합계 (집계함수에 대해서)를 표시
마지막 행에는 전체 합계가 표시됨
SELECT prod_lgu, prod_name, sum(cart_qty) total
FROM prod
LEFT OUTER join cart
	ON prod_id = cart_prod
	AND substr(cart_no,1,4)='2005'
GROUP BY prod_lgu, prod_name with rollup 

-GROUP BY rollup-
SELECT prod_lgu, prod_name, sum(cart_qty) total
FROM prod
LEFT OUTER join cart
	ON prod_id = cart_prod
	AND substr(cart_no,1,4)='2005'
GROUP BY rollup(prod_lgu, prod_name );






db에서 최대한 연산을 다한다.

function 함수 작성법

Delimiter//

--사용자 정의 함수
create function defGetLproNm(paramGu VARCHAR(10)
--리턴타입 정의
RETURNS VARCHAR(50)
--함수기능 시작
BEGIN
--함수기능종료
END//
-- 함수 전체 구문 종료
Delimiter;


사용법
함수명(매개변수)


/* view =가상테이블
자주 사용되거나 sql구문이 긴 경우 조회목적으로만 사용
미리 객체화 시켜서 테이블 처럼 사용하는 방식
조회만 가능하며 입력/ 수정/삭제가 되지 않습니다
입력수정삭제가 되는 경우도 있지만 VIEW 사용목적에 맞지 않다*/



저장 프로시저 Stored Procedure; sp )
프로그램 영역이 아닌 db 자체에서 SQL구문을 관리하고자 할때 사용
데이터베이스에 sql 구문을 객체화 하여 호출방식으로만 사용하는 방식




Delimiter //

-- 사용자 정의 함수
create function udfGetLprodNm(paramGu VARCHAR(10))
-- 리턴타입 정의
RETURNS VARCHAR(50)
-- 함수기능 시작
BEGIN
	
	-- 반환할 값을 저장할 변수 선언
	DECLARE result VARCHAR(50);
	-- 상품 분류 명을 추출하는 sql 구문 작성
	

	SELECT lprod_nm INTO result
	FROM lprod 
	WHERE lprod_gu = paramGu;

	IF RESULT IS NULL THEN 
		SET RESULT = 'NULL';
	END IF;	
	-- 반환하기
	RETURN result;
-- 함수기능종료
END //
-- 함수 전체 구문 종료
Delimiter ;




view
delimiter //

create or REPLACE VIEW viewGetBuyerAll AS

SELECT buyer_id, buyer_name, nvl(SUM(cart_qty*prod_sale),0) total
FROM buyer
LEFT OUTER JOIN prod
	ON prod_buyer= buyer_id
LEFT OUTER JOIN cart
	ON prod_id = cart_prod
	AND LEFT(cart_no,4)=2005
GROUP BY buyer_id, buyer_name;

	
delimter;	
실행할때 SELECT *
from view이름; 



프로시저
delimiter //



CREATE PROCEDURE spGetBuyerAll()
BEGIN
SELECT *
from viewgetbuyerall;
END //

delimiter ;

실행 할때  CALL 프로시저 명()