oracle数据库造数,oracle 建表 造数据 存储过程 创建数据库的远程连接 以及一列的多行合并...

create table carinfo(

carId number(8),--ID

carName varchar2(15),--Name

flag number(3)--标志

);

--主键

alter table carinfo

add constraint carid primary key (CARID);

--造数据

declare

maxrecords constant int:=20;

i int :=1;

begin

for i in 1..maxrecords loop

insert into carinfo(carId,carname,flag)

values(i,'car'||i,1);

end loop;

dbms_output.put_line(' 成功录入数据！ ');

commit;

end;

--测试

select * from carinfo;

create table taskinfo(

taskId number(10) primary key,

taskName varchar(15),

taskDetail varchar2(20)

);

create table cartask(

carId number(8),

taskId number(10),

taskTime timestamp,

primary key(carId,taskId),

foreign key(carId) references carinfo(carid),

foreign key(taskId) references taskinfo(taskId)

);

declare

maxrecords constant int:=10;

i int :=1;

begin

for i in 1..maxrecords loop

insert into taskinfo(taskId,taskname,taskdetail)

values(i,'任务'||i,'good');

end loop;

dbms_output.put_line(' 成功录入数据！ ');

commit;

end;

commit;

select * from taskinfo;

select taskId,taskName from taskinfo

--存储过程 派车

--caridlist 11,22,33

--v_taskid 10

create or replace procedure addCarTask(caridlist in string,v_taskid in number, ret out number)

is

v_end integer;

v_start integer;

v_carid number;

v_time number;

v_flag integer;

begin

v_end:=1;

v_start:=1;

v_time:=1;

savepoint p1;

ret:=0;

while(v_end

select instr(caridlist,',',v_time) into v_end from dual;

v_carid:=to_number(substr(caridlist,v_start,v_end-v_start+1));

select count(1) into v_flag from carinfo where flag=1 and carid=v_carid;

if(v_flag=0) then

rollback to savepoint p1;

ret:=v_carid;

return;

else

update carinfo set flag=2 where carid=v_carid;

insert into cartask(carid,taskid,tasktime) values(v_carid,v_taskid,sysdate);

end if;

v_time:=v_time+1;

end loop;

commit;

Exception

when others then

rollback;

end addCarTask;

/

--创建数据库的远程连接

create public database link db_link_133

connect to hr identified by hr using

'(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.130.35.133)(PORT = 1521))

)

(CONNECT_DATA =

(SID = tuhao)

(SERVER = DEDICATED)

)

)'

select count(1) from hr.employees;

select count(1) from employees@db_link_133;

-- 有两列(roleid，operid)数据要生成 1 a,b,c

-- 2 a,c

-- roleid operid

-- 1 a

-- 1 b

-- 1 c

-- 2 a

-- 2 c

select substr(result,2)

from (select roleid,max(sys_connect_by_path(operid,',')) result

from(select operid,roleid,rn,lead(rn) over(partition by roleid order by rn) rn1

from (select operid,roleid,rownum rn

from operinfo

)

)

start with rn1 is null

connect by rn1 = prior rn

group by roleid

) t