This day I will introduce the 11G plsql:
Step1:
Let us create one sequence first:
create sequence dept_seq start with 101;
Now I had viewed the sequence had created in left side: SEQENCE
Step2:
Create one procedure:
create or replace procedure add_dept(
p_name IN dept.dname%TYPE,
P_loc IN dept.loc%TYPE ) IS
BEGIN
INSERT INTO DEPT(DEPTNO,DNAME,LOC)
VALUES (dept_seq.nextval,P_NAME,P_LOC);
END add_dept;
/
Now I had viewed the procedure had created in left side: procedure
Step3:
Let us do a test for this procedure:
1: Passing parameters using the positional notation.
execute add_dept ('training',2500)
2: Passing parameter using the named notation.
execute add_dept (p_loc=>2400,p_name=>'EDUCATION')
Attention:
Before 11g, Only position notation can used, after 11g, the named notation can be used:
SQL> execute add_dept ('t','shp');
begin add_dept ('t','shp'); end;
ORA-01438: 值大于为此列指定的允许精度
ORA-06512: 在 "SCOTT.ADD_DEPT", line 5
ORA-06512: 在 line 2
SQL> DESC DEPT;
名称 是否为空? 类型
----------------------------------------- -------- -------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
the length of deptno is number(2), so the root cause is this, we start the sequece with 101,
we recreated the sequence with 01 is okay:
1: drop sequence dept_seq
2: create sequence dept_seq start with 01;
Step4:
Let us open a command window to do this test: