Neoview SQL Reference Manual (R2.3)

-- create a procedure
create procedure T100_io_nn(IN IN1 numeric(9,3), OUT OUT2 numeric(9,3))
external name 't100.T100_io_nn' language java parameter style java no sql
external path ‘/usr/roberta/regress/catman’
grant execute on procedure t100_io_nn to “role_user1”;
-- showddl
SHOWDDL SCH.T100_IO_NN, PRIVILEGES
-- showddl output
CREATE PROCEDURE SCH.T100_IO_NN
(
IN IN1 NUMERIC(9,3)
, OUT OUT2 NUMERIC(9,3)
)
EXTERNAL NAME 't100.T100_io_nn (java.math.BigDecimal,java.math.BigDecimal[])'
EXTERNAL PATH '/usr/roberta/regress/udr'
LANGUAGE JAVA
PARAMETER STYLE JAVA
NO SQL
NOT DETERMINISTIC
ISOLATE
;
GRANT EXECUTE ON PROCEDURE SCH.T100_IO_NN TO “ROLE_USER1”;
• SHOWDDL on several objects with PRIVILEGES option:
-- add some schema level privileges
Grant select on schema sch to “role_reader”;
-- create some tables and their dependent objects:
create table empl
(empl_no int not null primary key,
empl_name char(50) not null,
empl_address varchar(50) not null,
dept_no int not null,
salary numeric (10,2) default 1000
)
hash partition by (empl_no)
max table size 2000;
create index empl_dept_no on empl (dept_no)
hash partition by (dept_no);
create mv empl_mv
refresh on request
initialize on refresh
store by (dept_no)
hash partition by (dept_no)
as select max(salary) as salary, dept_no
from empl
group by dept_no;
alter table empl add constraint empl_salary_check check (salary >= 1000);
alter table empl add constraint empl_dept_no_check check (dept_no > 0);
alter table empl add constraint empl_unique unique (empl_name, empl_address);
create table dept
( dept_no int not null primary key,
dept_name varchar (20) not null )
hash partition by (dept_no);
CREATE TRIGGER neo.sch.dept_trig AFTER UPDATE
ON neo.sch.empl
REFERENCING OLD AS oldrow, NEW AS newrow
FOR EACH ROW
WHEN (oldrow.dept_no <> newrow.dept_no)
INSERT INTO neo.sch.dept VALUES (oldrow.empl_no, 'new dept');
grant select on table empl to public;
grant delete on table empl to “role_user1” with grant option;
grant references (c2) on table empl to “role_user4”;
-- perform showddl on EMPL
SHOWDDL EMPL, PRIVILEGES
-- Schema level privileges
-- GRANT SELECT ON SCHEMA SCH TO “ROLE.READER”;
CREATE TABLE SCH.EMPL
(
EMPL_NO INT NO DEFAULT -- NOT NULL
, EMPL_NAME CHAR(50) CHARACTER SET ISO88591 NO DEFAULT -- NOT NULL
SHOWDDL Statement 177