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