VIEWS AND MACRO PRACTICALS

VIEWS PRACTICALS
===================
creating:
CREATE VIEW <VIEWNAME> AS(SELECT QUERY <where condition>
<with check option>)
replacing
REPLACE  VIEW <VIEWNAME> AS(SELECT QUERY <where condition>
<with check option>)
droping a view:DROP VIEW <VIEWNAME>
calling a view: SELECT * FROM <VIEWNAME>
updating a view: UPDATE <VIEWNAME>
SET COLNAME=<VALUE>WHERE CONDITION
========================================
DROP VIEW V1
CREATE MODIFIABLE VIEW
=======================
CREATE  VIEW V1 AS (SEL * FROM PARTY)
SHOW VIEW V1
HELP VIEW V1
SEL * FROM V1--CALLING A VIEW
UPDATE V1 SET PARTYINCOME=30000 WHERE PARTYID=3--VIEW UPDATION
SEL * FROM PARTY WHERE PARTYID=3--TABLE VERIFICATION
READONLY VIEW (JOIN VIEW)
===========================
REPLACE VIEW V1 AS (SEL E.*,D.DEPTID AS D_DEPTID,D.DEPTNAME FROM EMP E INNER JOIN DEPT D
ON E.DEPTID=D.DEPTID)
SHOW VIEW V1
SEL * FROM V1
READONLY VIEW (AGGREGATE VIEW)
===========================
REPLACE VIEW V1 AS (SEL SUM(PARTYINCOME) AS SINCOME,
AVG(PARTYINCOME) AS AVINCOME FROM
PARTY)
SHOW VIEW V1
SEL * FROM V1
VIEW 'WITH CHECK OPTION'
=========================
IT WORKS LIKE CHECK CONSTRAINT ON A VIEW.
SEL * FROM PARTY1
REPLACE VIEW V1 AS (SEL * FROM PARTY1 WHERE PARTYINCOME>300 WITH CHECK OPTION)
SHOW VIEW V1
SEL * FROM V1
UPDATE V1 SET PARTYINCOME=100 WHERE PARTYID=3--FAILS
UPDATE V1 SET PARTYINCOME=500 WHERE PARTYID=3--SUCCESS
=================================================================
MACRO PRACTICALS:
-------------------------------
creating:
CREATE MACRO <MACRONAME>(<PARAMS>) AS (SQL QUERIES;);
replacing:
REPLACE MACRO <MACRONAME>(<PARAMS>) AS (SQL QUERIES;);
droping: DROP MACRO <MACRONAME>
viewing: SHOW MACRO <MACRONAME>
executing a macro:
EXEC <MACRONAME>(<PARAMS>) OR EXECUTE <MACRONAME>(<PARAMS>)
drop macro mc
simple macro
==========
create macro mc as
(
delete from party5;
delete from party1;
ins src(20,'ggg',40000);
sel * from party;
)
sel * from dbc.tables where databasename='vinayaka' and tablekind='M'
show macro mc
exec mc
===============
macro with input parameters
=============================
replace macro mc(id integer,nm varchar(30))
as
(
ins src(:id,:nm,70000);
ins tgt(:id,:nm,80000);
)
show macro mc
exec mc(56,'ppp')
sel * from src;
sel * from tgt
note: colon indicates the paramter or variable value (not a column value)