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<length(caridlist)) loop 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