DM表以外ddl语句示例

下面是些DM7&DM8中，比较常用的DDL语句示例，如下：

--1.table

create table test (c1 int primary key, c2 varchar(20));
insert into test values(1, 'aaa');
commit;

 

--2.index

create index test_idx1 on test(c1);
drop index test_idx1;

 

--3.view

create view test_view as select c1,c2 from test;
drop view test_view;

 

--4.trigger

create or replace trigger test_tri
after
insert
on test
for each row
begin
print 'insert 1 row';
end;

drop trigger test_tri;

 

--5.function

create or replace function test_fun(a int, b int) return int as
s int;
begin
s:=a+b;
return s;
exception
when others then null;
end;
/

drop function test_fun;

select test_fun(10, 20);

 

--6.user

create user user01 identified by user123456;

drop user user01;

--7.procedure

create or replace procedure test_proc(a in out int) as
b int:=10;
begin
a:=a+b;
print a;
exception
when others then null;
end;
/

drop procedure test_proc;

call test_proc(11);

 

--8.sequence

create sequence test_seq increment by 10;

drop sequence test_seq;

select test_seq.nextval;
select test_seq.currtval;

 

--9.type

create or replace type test_type as object(

name varchar2(10)
not final;
/

create table test2(c1 int, c2 test_type);
insert into test2 values(1, test_type('QQQ'));
commit;

drop type test_type;

--10.synonym

create synonym sysdba.s1 for sysdba.test;

drop synonym sysdba.s1; --piblic权限

 

--11.tablespace

create tablespace test_ts datafile 'c:\dmdbms\bin\SDB\test_ts.dbf' size 128;

drop tablespace test_ts;

 

--12.role

grant user02 to user01;

revoke user02 from user01;

 

--13.schema

create schema sch01;

drop schema sch01;

 

--14.grant/revoke

grant create table, create view to user01 with admin option;

revoke create table from user01;

--15.comment

comment on table test is '表以外ddl-功能测试表';
comment on column test.c2 'sysdba.test.c2';

 

--16.package

ceate table test03 (id int identity, name varchar(100), city varchar(100));
insert into test03(name, city) values('zhang3', 'wuhan');
insert into test03(name, city) values('li4', 'beijing');
insert into test03(name, city) values('wang5', 'shanghai');

create or replace package test_pg as
e_no exception;
pcount int;
pcur cursor;
procedure addp(pname varchar(100), pcity varchar(100));
procedure delp(pname varchar(100), pcity varchar(100));
procedure delp(pid int);

function get_pcount return int;
procedure plist;
end test_pg;

create or replace package body test_pg as
procedure addp(pname varchar(100), pcity varchar(100)) as
begin
insert into test03(name, city) values(pname, pcity);
end addp;

procedure addp(pname varchar(100), pcity varchar(100)) as
begin
insert into test03(name, city) values(pname, pcity);
end addp;

procedure delp(pname varchar(100), pcity varchar(100)) as
begin
delete from test03 where name like pname and city like pcity;
pcount = pcount - SQL%ROWCOUNT;
end delp;

procedure delp(pname varchar(100), pcity varchar(100)) as
begin
delete from test03 where id = pid;
pcount = pcount - SQL%ROWCOUNT;
end delp;

function get_pcount return int as
begin
return pcount;
end get_pcount;

procedure plist as
declare
v_id int;
v_name varchar(100);
v_city varchar(100);
begin
if pcount = 0 then
raise e_no;
end if;
open pcur for select id, name city from test03;
loop
fetch pcur into into v_id, v_name, v_city;
exit when pcur%NOTFOUND;
print ('No.' || (cast (v_id as varchar(100))) ||' '|| v_name ||'来自'||v_city);
end loop;

close pcur;

end plist;

begin
select count(*) int pcount from test03;
end test_pg;

alter package test_pg compile;

call test_pg.addp('chen6', 'nanjing');
call test_pg.delp('chen6', 'nanjing');
call test_pg.delp(4);

 

 

 

 

--17.其他赞略。