我有两个表
first:
CREATE TABLE z_names (ID number,
NAME VARCHAR2(200))
第二:
CREATE TABLE Z_FNAME
("FAMILY" VARCHAR2(200 BYTE),
"ID" NUMBER,
"NAME" VARCHAR2(200 BYTE),
"NAME_ID" NUMBER
)
如何写插入第二表的过程输入字符串示例:nam1;fam1,nam2;fam2=>nam1插入到NAMEColumn And fam1 IntoFAMILY
2 -ID由我以前写过的触发器生成
3-NAME_ID来自FIRST表
使用您之前创建的表(我记得这个问题,所以我重用了它,以及序列),您将输入字符串分成行并获取名称,以某种方式;我选择了正则表达式,假设名字只包含一个单词。
已插入的表名:
SQL> select * From z_names;
ID NAME
---------- --------------------
1 john
2 jim
3 jack
过程期望作为参数传递的字符串包含已经插入到z_names
表中的name
。
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.
测试:
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>
然而,从我的角度来看,这是一个有点尴尬的方法。我不知道您在这个练习中使用的表表示什么,但我希望它们彼此相关(通过引用完整性约束)。这也意味着z_fname
很可能没有规范化-您将只存储指向主表(z_names
)的外键约束值,而不是同时存储name
和id
。
这只是一个模板,你应该检查它并根据需要进行修改(这就是我能做的所有数据,而不是你给我的):
CREATE OR REPLACE PROCEDURE some_name
(name_in IN varchar2, fam_in IN varchar2)
IS
max_id_names number; --variable for max id from z_names and new name
max_id_fam number; --variable for max id from Z_FNAME
BEGIN
--1. Find max id from "ID Generated By Trigger I Have Writed Before" and SET IT TO max_id_names
INSERT INTO z_names
(
max_id_names,
NAME
)
VALUES
(
:max_id_names, --you will find it
name_in -- IN param
);
--2. Find max id from "ID Generated By Trigger I Have Writed Before" and SET IT TO max_id_fam
INSERT INTO Z_FNAME
(
FAMILY,
ID,
NAME,
NAME_ID
)
VALUES
(
fam_in, -- IN param
:max_id_fam, -- you will find it
name_in, --IN param
:max_id_names --you already have it because you inserted the names
);
END;
/