我现在有一个存储过程
CREATE OR REPLACE Procedure p_insert_tb_user
(
id in Number,
name in Nvarchar2,
pwd in Nvarchar2
)
as
BEGIN
insert into tb_users values (id,name,pwd);
commit;
End;我的id是自增序列,我在调用的时候写exec p_insert_tb_user(null,'admin','admin')会报错,写exec p_insert_tb_user(userIdentity.nextval,'admin','admin')也会报错,请问应该要怎么写id这列的值呢?谢谢各位了!
CREATE OR REPLACE Procedure p_insert_tb_user
(
id in Number,
name in Nvarchar2,
pwd in Nvarchar2
)
as
BEGIN
insert into tb_users values (id,name,pwd);
commit;
End;我的id是自增序列,我在调用的时候写exec p_insert_tb_user(null,'admin','admin')会报错,写exec p_insert_tb_user(userIdentity.nextval,'admin','admin')也会报错,请问应该要怎么写id这列的值呢?谢谢各位了!
userIdentity.nextval的时候报:在此上下文中不允许表,视图或序列引用 'userIdentity.NEXTVAL'
CREATE OR REPLACE PROCEDURE p_insert_tb_user(NAME IN NVARCHAR2, pwd IN NVARCHAR2) AS
BEGIN
INSERT INTO tb_users VALUES (userIdentity.nextval, NAME, pwd);
COMMIT;
END;
declare
v_id number;
begin
select userIdentity.nextval into v_id from dual;
exec p_insert_tb_user(v_id,'admin','admin')
end;
2:
修改存储过程如下:
CREATE OR REPLACE Procedure p_insert_tb_user
(
name in Nvarchar2,
pwd in Nvarchar2
)
as
BEGIN
insert into tb_users values (userIdentity.nextval ,name,pwd);
commit;
End;
PL/SQL procedure successfully completed
SQL>
CREATE TABLE tb_users(
id Number,
name Nvarchar2(10),
pwd Nvarchar2(10)
);--create sequence
CREATE SEQUENCE test_sequence3
increment by 1 -- 每次递增1
start with 1 -- 从1开始
nomaxvalue -- 没有最大值
minvalue 1 -- 最小值=1
NOCYCLE -- 不循环
; --create procedure
CREATE OR REPLACE Procedure p_insert_tb_user(
name in Nvarchar2,
pwd in Nvarchar2
)
AS
BEGIN
INSERT INTO tb_users VALUES (test_sequence3.nextval ,name,pwd);
COMMIT;
End;--desc procedure
DECLARE
v_id NUMBER;
BEGIN
SELECT test_sequence3.nextval INTO v_id FROM dual;
p_insert_tb_user('admin','admin');
END;--select
SELECT * FROM tb_users;
ID NAME PWD
---------- -------------------- --------------------
1 admin admin