MSSQL DB - EX10

--10/12
-- Northwind 컨텍스트로 이동
USE Northwind

--
SELECT * FROM Customers
SP_HELP Customers

--1. 생성 및 사용
CREATE PROCEDURE usp_getcustomers
AS
BEGIN
 SELECT
  customerid, companyname,
  contactname, contacttitle,
  country, phone, fax
 FROM
  Customers
END  

EXEC usp_getcustomers

--2. 전달인자
CREATE PROC usp_getcustomersbycountry
@country nvarchar (15) --전달인자
AS
BEGIN
 SELECT
  customerid, companyname,
  contactname, contacttitle,
  country, phone, fax
 FROM
  Customers
 WHERE
  country = @country
END

--EXEC usp_getcustomersbycountry 'usa'
EXEC usp_getcustomersbycountry @country = 'usa'

-- 전달인자 방향
CREATE PROCEDURE usp_getcustomerscount
@count int output
AS
BEGIN
 SELECT @count = COUNT(*) FROM customers
END

DECLARE @count int
EXEC usp_getcustomerscount @count OUTPUT
PRINT @count

-- 반환값 (단일 값만 반환 가능)
CREATE PROCEDURE usp_getcustomerscount2
AS
BEGIN
 DECLARE @count int
 SELECT @count = COUNT(*) FROM customers
 RETURN @count
END

DECLARE @count2 int
EXEC @count2 = usp_getcustomerscount2
PRINT @count2

-- 프로시저 변경
CREATE PROC usp_insertcustomer
@customerid nchar (5),
@companyname nvarchar
AS
BEGIN
 INSERT INTO customers (customerid, companyname)
 VALUES (@customerid, @companyname)
END

--

ALTER PROC usp_insertcustomer
@customerid nchar (5),
@companyname nvarchar (80) = 'AAAAA' -- default value (안넣은건 AAAAA로 셋팅)
AS
BEGIN
 INSERT INTO customers (customerid, companyname)
 VALUES (@customerid, @companyname)
END

EXEC usp_insertcustomer 'AAAAB'
EXEC usp_getcustomers

--

CREATE PROC usp_updatecustomer
@customerid nchar (5),
@companyname nvarchar (80)
AS
BEGIN
 UPDATE customers
 SET companyname = @companyname
 WHERE customerid = @customerid
END

EXEC usp_updatecustomer 'AAAAA', 'MODIFIED AAAAA'
EXEC usp_getcustomers

--

CREATE  PROC usp_deletcustomer
@customerID nchar (5)
AS
BEGIN
 DELETE FROM customers
 WHERE customerid = @customerID
END

EXEC usp_deletcustomer 'AAAAA'
EXEC usp_getcustomers
 
--프로시져 삭제
DROP PROC usp_getcustomerscount2
EXEC usp_getcustomerscount2

------------------------------------------------------------------

--FUNSION 생성
CREATE FUNCTION getcustomerscount()
RETURNS int
AS
BEGIN
 DECLARE @count int
 SELECT @count = COUNT(*) FROM customers
 return @count
END

SELECT dbo.getcustomerscount()

-- FUNCTION 전달인자
CREATE FUNCTION getsuppliername(@supplierid int)
RETURNS nvarchar (100)
AS
BEGIN
 DECLARE @companyname nvarchar (40)
 SELECT @companyname = companyname
 FROM suppliers
 WHERE supplierid = @supplierid
 
 RETURN @companyname
END

CREATE FUNCTION getcategoryname(@categoryid int)
RETURNS nvarchar (100)
AS
BEGIN
 DECLARE @categoryname nvarchar(15)
 SELECT @categoryname = categoryname
 FROM categories
 WHERE categoryid = @categoryid
 RETURN @categoryname
END

SELECT
 productid, productname,
 dbo.getcategoryname(categoryid),
 dbo.getsuppliername(supplierid)
FROM
 products

--------------------------------------------------- 

-- 10/13
CREATE FUNCTION newproducts()
RETURNS TABLE
AS
RETURN
(
 SELECT
  ProductID, ProductName,
  QuantityPerUnit, UnitPrice,
  c.CategoryName, s.CompanyName
 FROM
  Products p
  INNER JOIN Suppliers s
  ON p.SupplierID = s.SupplierID
  INNER JOIN Categories c
  ON p.CategoryID = c.CategoryID
)

SELECT * FROM dbo.newproducts()