test11.txt

orale培训教材包括了所有的sql说明和实例

--本试验目的是：什么时候需要给数据类型指定长度或精度


--*******************************************************************************
--varchar2类型
--为过程的参数指定长度
create or replace procedure test11(name varchar2(100)) is
begin
null;
end;
/

--参数的类型指定了长度
--结果如下------------------------------------------------------------------------
SQL> create or replace procedure test11(name varchar2(100)) is
  2  begin
  3  null;
  4  end;
  5  /

Warning: Procedure created with compilation errors.

SQL> show err
Errors for PROCEDURE test11:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/30     PLS-00103: Encountered the symbol "(" when expecting one of the
         following:
         := . ) , @ % default character
         The symbol ":=" was substituted for "(" to continue.



--参数的类型没有指定长度
create or replace procedure test11(name varchar2) is
begin
null;
end;
/
SQL> create or replace procedure test11(name varchar2) is
  2  begin
  3  null;
  4  end;
  5  /

Procedure created.


--*******************************************************************************
--number类型
--参数的类型指定了长度
create or replace procedure test11(id number(10)) is
begin
null;
end;

--结果如下------------------------------------------------------------------------
SQL> create or replace procedure test11(id number(10)) is
  2  begin
  3  null;
  4  end;
  5  /

Warning: Procedure created with compilation errors.

SQL> show err
Errors for PROCEDURE test11:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/26     PLS-00103: Encountered the symbol "(" when expecting one of the
         following:
         := . ) , @ % default character
         The symbol ":=" was substituted for "(" to continue.

--参数的类型没有指定长度

create or replace procedure test11(id number) is
begin
null;
end;

SQL> create or replace procedure test11(id number) is
  2  begin
  3  null;
  4  end;
  5  /

Procedure created.


--##################################################################################
--函数
--##################################################################################

drop procedure test11;
--删除已有过程
--参数的类型指定了长度
create or replace function test11(id number(10)) return number is
begin
return 0;
end;
/
--结果如下-------------------------
SQL> create or replace function test11(id number(10)) return number is
  2  begin
  3  return 0;
  4  end;
  5  /

Warning: Function created with compilation errors.

SQL> show err
Errors for FUNCTION TEST11:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/26     PLS-00103: Encountered the symbol "(" when expecting one of the
         following:
         := . ) , @ % default character
         The symbol ":=" was substituted for "(" to continue.



--参数的类型没有指定长度------------------------------
create or replace function test11(id number) return number is
begin
return 0;
end;
/
--结果如下-------------------------
SQL> create or replace function test11(id number) return number is
  2  begin
  3  return 0;
  4  end;
  5  /

Function created.

--返回值的类型指定了长度
create or replace function test11(id number) return number(10) is
begin
return 0;
end;
/
--结果如下-------------------------
SQL> create or replace function test11(id number) return number(10) is
  2  begin
  3  return 0;
  4  end;
  5  /

Warning: Function created with compilation errors.

SQL> show error
Errors for FUNCTION TEST11:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/41     PLS-00103: Encountered the symbol "(" when expecting one of the
         following:
         . @ % ; is authid deterministic parallel_enable as character


--返回值的类型没有指定长度,函数，过程中的varchar2型局部变量也没指定长度。
create or replace function test11(id number) return number is
var_test varchar2;
--varchar2型局部变量没指定长度
begin
    return 0;
end;
--结果如下-------------------------
SQL> create or replace function test11(id number) return number is
  2  var_test varchar2;
  3  begin
  4  return 0;
  5  end;
  6  /

Warning: Function created with compilation errors.

SQL> show err
Errors for FUNCTION TEST11:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/5      PLS-00215: String length constraints must be in range (1 ..
         32767)

2/5      PL/SQL: Item ignored



总结：
在做为参数和返回值的数据类型，不能指定长度和精度。
在函数和存储过程里面，varchar2型局部变量必须指定长度，其他类型指不指定都可以。




--##################函数和过程的调用################################

--本试验的目的是：函数和存储过过程在相互调用的限制
create table test10 
(
name varchar2(100)
);
/
--创建存储过程
create or replace procedure test12 is
begin
insert into test10(name) values('John');
commit;
end;
/


--函数调用含DML存储的过程
create or replace function test13 return number is
begin
    test12;
    --调用含DML存储的过程：test12
    return 0;
end;
/


--在select 语句中调用含有dml的函数
SQL> select test13 from dual;
select test13 from dual
       *
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "SCOTT.TEST12", line 3
ORA-06512: at "SCOTT.TEST13", line 3
ORA-06512: at line 1

--在存储过程中，调用带有dml语句的函数
create or replace procedure test14 is
    vid number;
begin
    vid:=test13;
    dbms_output.put_line(vid);
end;
/
--执行存储过程test14
SQL> set serveroutput on
SQL> exec test14
0

PL/SQL procedure successfully completed.

SQL> select * from test10;

NAME
-----------------------------------------------------------------
John


总结：
函数和存储过程可以相互调用，
但是如果函数中调用了带有insert/delete/update的存储过程和这样的语句，
这样的函数将不能在select语句中被调用。oracle建议函数中不使用dml。
而在过程中使用dml.这样，可以使用户定义函数如同oracle内置函数一般使用。



----------------SELECT INTO----
--使用select into 为变量赋值,返回多条纪录
DECLARE
	TOTAL_SALARY NUMBER(10,2);
	TOTAL_COMMISSION NUMBER(10,2);
BEGIN

	SELECT SUM(SAL),SUM(SAL *0.1)
	INTO TOTAL_SALARY,TOTAL_COMMISSION
	FROM EMP
	--WHERE DEPTNO =10
    GROUP BY DEPTNO;
	dbms_output.put_line('toatal salary is ' || TOTAL_SALARY);
	dbms_output.put_line('toatal commission is ' || TOTAL_COMMISSION);
END;


ERROR 位于第 1 行:
ORA-01422: 实际返回的行数超出请求的行数
ORA-06512: 在line 6

update scott.sm_emp set salary=100;