一：Oracle修改字段名、字段数据类型
语句： alter table tableName rename column oldCName to newCName; -- 修改字段名 alter table tableName modify (cloumnName 数据类型); -- 修改数据类型 例如：
1、创建表： CREATE TABLE Student( id varchar2(32) primary key, name varchar2(8) not null, age number ); 2、修改字段名： alter table Student rename name to StuName; 3、修改数据类型： alter table Student modify (id varchar2(64));
问题
数据库中某表字段为number类型，需要修改成varchar类型。
修改步骤
--备份表--create table xxtable_copy20171215 as select * from xxtable;--复制表结构成新表--create table xxtable_new as select * from xxtable where 1=2;--改变新表的数据结构--alter table xxtable_new modify (CANCELRENO varchar(25));--导入原数据--insert into xxtable_new select * from xxtable_copy20171215;--插入新数据--insert into xxtable_new (...) values (...);--将原表更名--alter table xxtable rename to xxtable_bak20171215;--将新表更名成原表名--alter table xxtable_new rename to xxtable;--删除第一次备份的表--drop table xxtable_copy20171215;
二：Oracle中给表添加主键、外键
1、创建表的同时创建主键约束
（1）无命名 create table student ( studentid int primary key not null, studentname varchar(8), age int);
（2）有命名 create table students ( studentid int , studentname varchar(8), age int, constraint yy primary key(studentid));
2、删除表中已有的主键约束
（1）无命名可用 SELECT * from user_cons_columns; 查找表中主键名称得student表中的主键名为SYS_C002715 alter table student drop constraint SYS_C002715;
（2）有命名 alter table students drop constraint yy;
3、向表中添加主键约束 alter table student add constraint pk_student primary key(studentid);
4、向表中添加外键约束 ALTER TABLE table_A ADD CONSTRAINT FK_name FOREIGN KEY(id) REFERENCES table_B(id);
三：自增量 sequence
-- Create sequence create sequence S_SNOWBALLING minvalue 1 maxvalue 9999999999999999999999999999 start with 3397 increment by 1 cache 20; select S_snowballing.nextval from dual
四：清空表
truncate table T_SBL_RollBack_PROJECTINFO
五：包创建和调用
创建包
create or replace package pck_snowballing is
--获取数据
type SnowballingList is record(
guid char(36),
loanGuid char(36),
custName nvarchar2(50), --客户姓名
Idcard nvarchar2(50), --客户身份证
bankcardNo nvarchar2(50), --客户银行卡号
BankMobile nvarchar2(50), --客户银行卡预留手机号
IsConversbl number --是否已经转换了数据，1：是，0：否
);
type sblTable is table of SnowballingList;
function GetSnowballingList(i_custName nvarchar2) return sblTable
PIPELINED;
end pck_snowballing;
create or replace package body pck_snowballing is
function GetSnowballingList(i_custName nvarchar2) return sblTable
PIPELINED is
Results SnowballingList;
sys_cur_contract sys_refcursor;
begin
open sys_cur_contract for
select (
case when t3.guid is null then t2.guid else t3.guid end) guid
,t1.loanguid
,t2.custname
,t2.idcard
,t3.bankcardno
,t3.bankmobile
,(case when t3.guid is null then 0 else 1 end) as IsConversbl from t_loanrecord t1
join t_custinfo t2
on t1.custguid=t2.guid
left join t_sbl_Userinfo t3
on t1.loanguid=t3.loanguid
where t1.status!=9
order by t1.createdate desc;
begin
loop
fetch sys_cur_contract
into Results ;
exit when sys_cur_contract%notfound;
pipe row(Results);
end loop;
end;
return;
end GetSnowballingList;
end pck_snowballing;
调用包
select * from table(pck_snowballing.GetSnowballingList(''))
共同学习，写下你的评论
评论加载中...
作者其他优质文章
