SQLServerでテーブルの作成、データ挿入、PROCEDUREの作成操作方法
1.テーブルを作成(table create)
CREATE TABLE employee(
id INTEGER NOT NULL PRIMARY KEY,
first_name VARCHAR(10),
last_name VARCHAR(10),
salary DECIMAL(10,2),
start_Date DATETIME,
region VARCHAR(10),
city VARCHAR(20),
managerid INTEGER
);
2.データの挿入(insert)
1> INSERT INTO employee VALUES (1, '二郎’ , '山田’, 20,’2015-03-22′,’関東’,’川崎’,3);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (2, '太郎’, '佐藤’,30,’2013-07-21′,’西日本’,’福岡’,4);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (3, '太郎’ , '金沢’, 40,’2011-12-01′,’関東’,’埼玉’,5);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (4, '太郎’ , '鈴木’, 50,’2016-03-03′,’西日本’,’東京’,6);
2> GO
(1 rows affected)
3.テーブルのクエリー(select)
select * from employee;
4.ストアド プロシージャの作成
CREATE PROCEDURE InstOrUpdateTran
@FirstName nVarChar(50),
@LastName nVarChar(25),
@Salary Money
AS
IF EXISTS(SELECT * From Employee Where First_name = @FirstName)
UPDATE Employee SET Last_NAME = @LastName, Salary = @Salary
WHERE First_name = @FirstName
ELSE
INSERT INTO Employee (ID,First_Name, Last_Name, Salary)
SELECT 99, @FirstName, @LastName, @Salary
5.テーブルの削除
drop table employee;