创建表空间与用户
/*
说明:若已经存在相应的用户和表空间,则需要先删除相应的用户和表空间
然后再全部重新建立
*/
--删除用户
drop user test cascade;
--删除表空间
drop tablespace test_data_temp including contents and datafiles;
drop tablespace test_data including contents and datafiles;
--创建临时表空间
create temporary tablespace test_data_temp tempfile 'test_data_temp.dbf'
size 100m autoextend on;
--创建表空间
create tablespace test_data logging datafile 'test_data.dbf'
size 100m autoextend on;
--创建用户并指定表空间
create user test
identified by test
default tablespace test_data
temporary tablespace test_data_temp
profile default;
--给用户授予角色权限
grant connect to test;
grant resource to test;
--给用户授予系统权限
grant unlimited tablespace to test;
--给用户授予管理员权限
grant dba to test;
exit;
创建表及修改表相关信息
--删除表
drop table t_persion;
--创建表
create table t_persion(
pid varchar2(32) not null,
pname varchar(32) not null,
age number,
sex char(1)
);
--修改表名
alter table t_persion rename to t_persion2;
--修改列名
alter table t_persion rename column pid to ppid;
--增改删表字段
alter table t_persion add (asd number);
alter table t_persion modify (asd char(1) default '0' not null);
alter table t_persion drop (asd);
--增删主键约束
alter table t_persion add constraint pk_t_persion primary key(pid);
alter table t_persion drop constraint pk_t_persion
--增删外键约束
alter table t_persion add constraint fk_t_persion foreign key (sex) references t_sex(sid);
alter table t_persion add constraint fk_t_persion foreign key (sex) references t_sex(sid) on delete cascade;--外键约束,级联删除
alter table t_persion drop constraint fk_t_persion;
--添加表注释
comment on table t_persion is '个人信息表';
--添加字段注释
comment on column t_persion.pid is 'id';
comment on column t_persion.pname is '姓名';
comment on column t_persion.age is '年龄';
comment on column t_persion.sex is '性别';
存储过程
无参+游标
--无参+游标
create or replace procedure demo1
as
cursor cur_data is -- //(游标:一个可以遍历的结果集)
select * from t_persion t;
begin
for cur_row in cur_data loop
dbms_output.put_line(cur_row.pid||':'||cur_row.pname);
end loop;
exception
when others then
dbms_output.put_line(sqlcode);
dbms_output.put_line(sqlerrm);
end;
insert into t_persion values('1','张三',18,1);
insert into t_persion values('2','李四',17,0);
call demo1();
入参
--入参
create or replace procedure demo2(
p_pid in test.t_persion.pid%type,
p_pname in varchar2,
p_age in number,
p_sex in char
)
is
begin
insert into t_persion(pid,pname,age,sex) values(p_pid,p_pname,p_age,p_sex);
exception
when others then
dbms_output.put_line(sqlcode);
dbms_output.put_line(sqlerrm);
end;
call demo2('3','王五',18,0);
入参+出参
--入参+出参
create or replace procedure demo3(
p_pid in test.t_persion.pid%type,
p_pname out varchar2
)
is
begin
select pname into p_pname from t_persion t where t.pid = p_pid;
delete t_persion t where t.pid = p_pid;
exception
when others then
dbms_output.put_line(sqlcode);
dbms_output.put_line(sqlerrm);
end;
--在sqlplus中调用
var pname varchar2(32);
call demo3(3,:pname);
print :pname;