11g 新特性之 plsql

  

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: