Procedure For Get String And insert , In Oracle [closed]

I Have Two Tables
first :

CREATE TABLE z_names (ID number,
                  NAME VARCHAR2(200))

Second:

 CREATE TABLE Z_FNAME 
         ("FAMILY" VARCHAR2(200 BYTE), 
      "ID" NUMBER, 
      "NAME" VARCHAR2(200 BYTE), 
      "NAME_ID" NUMBER
            )

How Write Procedure for Insert Into Second Table By String ,With This Conditions :
1- Example Of input String : nam1;fam1,nam2;fam2 => nam1 Insert Into NAME Column And fam1 Into FAMILY
2-ID Generated By Trigger I Have Writed Before
3-NAME_ID Comes From FIRST table

Answer

With a table you previously created (I remember that question so I reused it, as well as the sequence), you’d split input string into rows and fetch names, somehow; I chose regular expressions, presuming that names consist of only one word.

Table with names (inserted previously):

SQL> select * From z_names;

        ID NAME
---------- --------------------
         1 john
         2 jim
         3 jack

Procedure expects that strings you’re passing as parameters contain name which is already inserted into the z_names table.

SQL> create or replace procedure p_test (par_string in varchar2) is
  2  begin
  3    insert into z_fname (family, id, name, name_id)
  4    with temp as
  5      (select regexp_substr(par_string, '[^,]+', 1, level) nf
  6       from dual
  7       connect by level <= regexp_count(par_string, ',') + 1
  8      )
  9    select regexp_substr(t.nf, 'w+', 1, 2) family,
 10           z_names_seq.nextval id,
 11           regexp_substr(t.nf, 'w+', 1, 1) name,
 12           n.id
 13    from temp t join z_names n on n.name = regexp_substr(t.nf, 'w+', 1, 1);
 14  end;
 15  /

Procedure created.

Testing:

SQL> exec p_test('john;Little,jack;Foot,jim;Bigfoot');

PL/SQL procedure successfully completed.

SQL> select * from z_fname;

FAMILY             ID NAME                    NAME_ID
---------- ---------- -------------------- ----------
Little             10 john                          1
Bigfoot            11 jim                           2
Foot               12 jack                          3

SQL>

---

However, from my point of view, that’s somewhat awkward approach. I don’t know what tables you use in this exercise represent, but I’d expect them to be related to each other (via referential integrity constraint). It also means that z_fname most probably isn’t normalized – you would store only the foreign key constraint value which points to the master table (z_names), not store both name AND id.