SQL statements related to SQL Anywhere examples

1. Create a table
2. Create a view
3. Create a function
4. Create a stored procedure
5. Create a trigger
6. If... else
======================================
-- 1. Create a table
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 a view
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 a function
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 a stored procedure
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
Sqlstate '200 ';
Declare curthiscust cursor
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 a trigger
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 '201312 ';

// Declare a cursor for table new_name
Declare new1 cursor
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