1.建立一张表并指定默认系统空间
drop table TESTTB;
create table TESTTB
(
ID VARCHAR2(30),
NAME VARCHAR2(30)
)tablespace SYSTEM
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited );
2.select username from dba_users;(查询用户名)
select tablespace_name from dba_tablespaces;(查询用户表空间)
select username,default_tablespace from dba_users order by username;(根据用户名,查询用户表空间)
select username,default_tablespace from dba_users where username='OCBC_ZJW';(根据指定用户名,查询用户表空间)
select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
from dba_tablespaces t, dba_data_files d
where t.tablespace_name = d.tablespace_name
group by t.tablespace_name;(查询表空间的大小)
3.建立存储过程:
select * from TESTTB;
/*第一个存储过程*/(有参数的存储过程)
create or replace procedure TESTA(param1 in VARCHAR2,param2 in VARCHAR2) as
begin
insert into TESTTB(ID,NAME) values (param1,param2);
end TESTA;
指定用户执行存储过程权限:
grant execute on TESTA to ocbc_zjw;
grant update on TESTTB to ocbc_zjw;
/*第二个存储过程*/
create or replace procedure prm_example
is datenow date;
begin
select sysdate into datenow from dual;
dbms_output.put_line(datenow);
end;
/*第三个存储过程*/(参数并有返回值的存储过程)
create or replace procedure TESTC (param1 in VARCHAR2,vname out VARCHAR2)
as
begin
select name into vname from TESTTB where id=param1;
end;
/*第四个存储过程*/(into的使用)
create or replace procedure prm_example
is
n_number number;
begin
select count(*) into n_number from emp;
dbms_output.put_line(n_number);
end;
/*查询一个字段*/(复杂的存储过程)
create or replace procedure prc_example
(de in varchar,name out varchar,App_Code out varchar,error_Msg out varchar)
as
begin
select dname into name from dept where deptno=de;
exception when others then
App_Code:=-1;
error_Msg:='未找到数据';
end;
/*声明变量*/
declare
de varchar(10);
ab varchar(10);
appcode varchar(20);
ermg varchar(20);
begin
de:=10;
prc_example(de,ab,appcode,ermg);
dbms_output.put_line(ermg);
end;
create or replace procedure prm_example(do1 in number ,dn1 in varchar,eo1 in number,en1 in varchar ,App_Code out varchar,error_Msg out varchar)
as
begin
insert into dept(deptno,dname) values (do1,dn1);
insert into emp(empno,ename,deptno) values(eo1,en1,do1);
commit;
exception when others then
App_Code:=-1;
error_Msg:='插入失败';
end;