SqlAnyWhere相关示例SQL语句

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