1.创建表
2.创建视图
3.创建函数
4.创建存储过程
5.创建触发器
6.IF...ELSE
================================
--1.创建表
CREATE TABLE Orders (
order_num INTEGER NOT NULL PRIMARY KEY,
date_ordered DATE,
name CHAR(80)
);
CREATE TABLE Order_item (
order_num INTEGER NOT NULL,
item_num SMALLINT NOT NULL,
PRIMARY KEY (order_num, item_num),
-- When an order is deleted, delete all of its
-- items.
FOREIGN KEY (order_num)
REFERENCES Orders (order_num)
ON DELETE CASCADE
)
--2.创建视图
CREATE VIEW emp_dept
AS SELECT emp_lname, emp_fname, dept_name
FROM Employee JOIN Department
ON Employee.dept_id = Department.dept_id
--3.创建函数
CREATE FUNCTION fullname (
firstname CHAR(30),
lastname CHAR(30) )
RETURNS CHAR(61)
BEGIN
DECLARE name CHAR(61);
SET name = firstname || ' ' || lastname;
RETURN (name);
END
--4.创建存储过程
CREATE PROCEDURE
CustomerProducts( IN customer_id
INTEGER DEFAULT NULL )
RESULT ( product_id INTEGER,
quantity_ordered INTEGER )
BEGIN
IF customer_id IS NULL THEN
RETURN;
ELSE
SELECT product.id,
sum( sales_order_items.quantity )
FROM product,
sales_order_items,
sales_order
WHERE sales_order.cust_id = customer_id
AND sales_order.id = sales_order_items.id
AND sales_order_items.prod_id = product.id
GROUP BY product.id;
END IF;
END
-----------
CREATE PROCEDURE TopCustomer (OUT TopCompany CHAR(35), OUT TopValue INT)
BEGIN
DECLARE err_notfound EXCEPTION FOR
SQLSTATE '02000';
DECLARE curThisCust CURSOR FOR
SELECT company_name, CAST(
sum(sales_order_items.quantity *
product.unit_price) AS INTEGER) VALUE
FROM customer
LEFT OUTER JOIN sales_order
LEFT OUTER JOIN sales_order_items
LEFT OUTER JOIN product
GROUP BY company_name;
DECLARE ThisValue INT;
DECLARE ThisCompany CHAR(35);
SET TopValue = 0;
OPEN curThisCust;
CustomerLoop:
LOOP
FETCH NEXT curThisCust
INTO ThisCompany, ThisValue;
IF SQLSTATE = err_notfound THEN
LEAVE CustomerLoop;
END IF;
IF ThisValue > TopValue THEN
SET TopValue = ThisValue;
SET TopCompany = ThisCompany;
END IF;
END LOOP CustomerLoop;
CLOSE curThisCust;
END
----------
CREATE PROCEDURE OrderCount (IN customer_ID INT,
OUT Orders INT)
BEGIN
SELECT COUNT(DBA.sales_order.id)
INTO Orders
FROM DBA.customer
KEY LEFT OUTER JOIN "DBA".sales_order
WHERE DBA.customer.id = customer_ID;
END
--5.创建触发器
CREATE TRIGGER tr_manager
BEFORE UPDATE OF dept_head_id
ON department
REFERENCING OLD AS old_dept NEW AS new_dept
FOR EACH ROW
BEGIN
UPDATE employee
SET employee.manager_id=new_dept.dept_head_id
WHERE employee.dept_id=old_dept.dept_id
END
The next example, which is more complex,
deals with a statement-level trigger.
First, create a table as follows:
CREATE TABLE "DBA"."t0"
(
"id" integer NOT NULL,
"times" timestamp NULL DEFAULT current timestamp,
"remarks" text NULL,
PRIMARY KEY ("id")
)
Next, create a statement-level trigger for this table:
create trigger DBA."insert-st" after insert order 4 on
DBA.t0
referencing new as new_name
for each statement
begin
declare @id1 integer;
declare @times1 timestamp;
declare @remarks1 long varchar;
declare @err_notfound exception for sqlstate value '02000';
//declare a cursor for table new_name
declare new1 cursor for
select id,times,remarks from
new_name;
open new1;
//Open the cursor, and get the value
LoopGetRow:
loop
fetch next new1
into @id1, @times1,@remarks1;
if sqlstate = @err_notfound then
leave LoopGetRow
end if;
//print the value or for other use
Print (@remarks1);
end loop LoopGetRow;
close new1
end
--6.IF...ELSE
/* Transact-SQL IF statement */
IF @v1 = 0
PRINT '0'
ELSE IF @v1 = 1
PRINT '1'
ELSE
PRINT 'other'
/* Watcom-SQL IF statement */
IF v1 = 0 THEN
PRINT '0'
ELSEIF v1 = 1 THEN
PRINT '1'
ELSE
PRINT 'other'
END IF