--本试验目的是：什么时候需要给数据类型指定长度或精度
--*******************************************************************************
--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;
