Oracle常用sql

Oracle不像Sqlserver，并没有提供l默认约束，但提供了默认值，效果一样。
--------------------------- 在建表时设置默认约束---------------------------

create table t1 (tname varchar2(20) default 'yang');

--------------------------- 在修改表时设置默认约束---------------------------

alter table t1 modify (tname varchar2(20) default 'yang');

--------------------------- 创建序列---------------------------
create sequence ZHPT_P_USER_SEQ
minvalue 1
maxvalue 999999999
start with 86283141
increment by -3026
nocache
cycle
order;
--------------------------- 创建存储过程---------------------------

CREATE OR REPLACE PROCEDURE create_table
IS
v_Cursor NUMBER;--定义游标
v_CreateString VARCHAR2(100);--这个变量存放创建表的SQL语句。
BEGIN
v_Cursor := DBMS_SQL.OPEN_CURSOR;--打开游标
v_CreateString := 'CREATE TABLE tp (id int,name varchar2(20))';--创建表的SQL语句。
DBMS_SQL.PARSE(v_Cursor, v_CreateString, DBMS_SQL.V7);执行建表的SQL语句
DBMS_SQL.CLOSE_CURSOR(v_Cursor);--关闭游标
END create_table;

--------------------------- 执行存储过程---------------------------

execute create_table;

---------------------------创建函数执行从A表到B表一条一条导入数据并验证---------------------------

create or replace function cux_user_imp(code out VARCHAR2) RETURN VARCHAR2 is
/*
作者：ocean
主要功能：数据导入测试
创建日期：2015年11月26日
*/
cux_a varchar2(10); --姓名变量
cux_i number;
cux_user p_user_temp%rowtype;
cursor cux_user_cr is
select * from p_user_temp p where p.xb = '1';
begin

delete from p_user;
commit;


cux_i := 1;

for cux_user in cux_user_cr loop

cux_a := cux_user.name;

if cux_a = 'lxx' then
dbms_output.put_line(cux_a);

elsif cux_a <> 'lxx' then

insert into p_user
(p_user.id,
p_user.name,
p_user.age,
p_user.tel,
p_user.csri,
p_user.xb)
values
(cux_user.id,
cux_user.name,
cux_user.age,
cux_user.tel,
cux_user.csri,
cux_user.xb
);
cux_i := cux_i + 1;
dbms_output.put_line(to_char(cux_i) || ' ' || cux_user.name);
commit;
end if;

end loop;

return 'ok';

end;

--------------------------- 简单主键定义如下并设置为自增(只取出主键定义)---------------------------

typeid int not null primary key identity(1,1),

--------------------------- 表和序列的关系，是通过业务逻辑的SQL语句来维护---------------------------

即：insert into table_name (column_name) values (seq_name.nextval);

--------------------------- 向表中加时间字段默认值为系统时间---------------------------

insert into table(j) values(to_date('2000-11-26 00:04:47','yyyy-mm-dd hh24:mi:ss'));
create table test
(id int,
starttime date default sysdate not null );
插入测试数据：
insert into test (id) values (1);
commit;

--------------------------- 创建表的时候调用序列 需要写一个触发器---------------------------

create or replace trigger 你的表名_tri
before insert on 你用到的表
for each row
DECLARE
BEGIN
SELECT 你的序列名.nextval into :new.id from dual;
end test_oo_tri;

--------------------------- A表数据导入B表，如果长度不够截取---------------------------

insert into test_PP(ID,NAME) select o.id,SUBSTR(o.name,0,3) from test_oo o;

insert into pUser p (p.名,p.性别,p.年龄,p.电话) select substr(u.姓名,1,20),u.性别,u.年龄,u.电话 from User u
说明：截取函数oracle中是substr，sqlserver中是substring
不管大于不大于20长度全截取。

--------------------------- A表数据导入B表---------------------------

insert into ZHPT_P_USER select * from PUSER
按字段插入
Insert into B (字段1，字段2，字段3) select 字段1，字段2，字段3 from A；

---------------------------给一个表循环插入1000条数据---------------------------

declare
idx number(22) := 8;
val varchar2(22) := 'test';
begin
loop
idx := idx + 1;
insert into abook2 values (idx, val || TO_CHAR( idx ) );
exit when idx > 1000;
end loop;
end;

--------------------------- 查询表中某个字段的值---------------------------

select * from puser where XM='王茜红统计'

--------------------------- 查询字段备注---------------------------

select * from user_tab_comments where comments like '%处室%'

--------------------------- 插入---------------------------

Insert into table_name 字段 1，字段 2、、字段 n values (字段值 1，字段值 2、、字段值 n )；

INSERT INTO lxx VALUES(10,'ACCOUNTING','NEW YORK');

--------------------------- 循环插入---------------------------
begin
for i in 1..10 loop
insert into table_name values (...);
end loop;
end;

--------------------------- 序列---------------------------

AA3349_BFA001_SEQUENCE

sql:

1)-- Create sequence
create sequence AA3349_BFA001_SEQUENCE
minvalue 1
maxvalue 999999999999999999999999999
start with 81
increment by 1
cache 20;

--------------------------- 修改表 字段 结构---------------------------
alter table AA3350
add constraint BFA001 primary key (BFA001)
using index
tablespace FSSR
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);

-- Add comments to the table
comment on table AA3350
is '文件审核表';
-- Add comments to the columns
comment on column AA3350.bfa303
is '文号';
comment on column AA3350.faf006
is '审核日期';
comment on column AA3350.faf007
is '审核人';
comment on column AA3350.faf008
is '审核说明';
comment on column AA3350.bfa314
is '修订状态';
comment on column AA3350.bfa001
is '序号';

-------------------------- 导入、导出--------------------------

导入：imp 数据库名/数据库密码@服务 file=D:名称.dmp full=y
导出：exp 数据库名/数据库密码@服务 file=D:名称.dmp

---------------------------建立表空间--------------------------

CREATE TABLESPACE "FSSR"

LOGGING

DATAFILE 'D:oracleproduct10.2.0oradataorclFSSR.ora' SIZE 500M

AUTOEXTEND ON NEXT 100M

MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT AUTO

------------------------ 更新字段-------------------------

update 表名 set 字段名='更新数据'