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.其他赞略。