oracle学习总结--存储过程

1、存储过程的创建
CREATE OR REPLACE PROCEDURE [存储过程名][(输入、输出参数)] 
AS/IS
[变量、游标声明]
BEGIN
[执行体]
END;

create or replace procedure sayHello --无参数时()不需要写
as
begin
  dbms_output.put_line('say hello');
end;

AS|IS:不能省略 
在视图(VIEW)创建中只能用AS不能用IS；
在游标(CURSOR)声明中只能用IS不能用AS。

/*
create table TABLE1
(
  user_id        VARCHAR2(10),
  user_name      VARCHAR2(10),
  user_mail      VARCHAR2(10),
  user_mobile    VARCHAR2(10),
  user_telephone VARCHAR2(10)
);
*/

CREATE OR REPLACE PROCEDURE CREATEVIEWS IS
  V_SQL VARCHAR2(10240);
BEGIN
  V_SQL := 'CREATE OR REPLACE VIEW V_TEST AS --创建视图用AS 使用IS执行时报错
    SELECT T1.USER_ID,
           T1.USER_NAME, --用户姓名
           T1.USER_MAIL, --用户MAIL
           T1.USER_MOBILE, --用户手机
           T1.USER_TELEPHONE --用户电话
      FROM TABLE1 T1';
  EXECUTE IMMEDIATE V_SQL;
END CREATEVIEWS;
-------------------------------------------------------------------------
/*
CREATE SEQUENCE MYSEQUENCE MINVALUE 1
  MAXVALUE 999999999999999999999999999
  START WITH 1
  INCREMENT BY 1
  CACHE 20;
   
CREATE TABLE MYTABLE
  (DNO NUMBER(6) PRIMARY KEY,DNAME VARCHAR2(14),LOC VARCHAR2(13));

CREATE OR REPLACE PROCEDURE MAKE_DATA
AS
X NUMBER;
s_sql varchar2(1000);
BEGIN
  X:=0;
  dbms_output.put_line(X);
  WHILE X<9 LOOP
    X:=X+1;
    s_sql:='INSERT INTO MYTABLE VALUES(MYSEQUENCE.nextval,''namex'||X||''',''namex'||X||''')';
    dbms_output.put_line(s_sql);
    execute immediate s_sql; 
    dbms_output.put_line(s_sql);
  END LOOP;
  commit;
END MAKE_DATA;

exec MAKE_DATA(); --MYTABLE插入数据
*/

CREATE OR REPLACE PROCEDURE CURSOR_TEST
AS
r1 MYTABLE%ROWTYPE;
CURSOR cur IS SELECT * FROM MYTABLE;--创建游标只能是IS 使用AS编译报错
BEGIN
  OPEN cur;
  LOOP
    fetch cur into r1;
    exit when cur%notfound;
    dbms_output.put_line(r1.DNAME);  
  END LOOP;
  CLOSE cur;
END CURSOR_TEST;

2、存储过程的执行
   1. exec CURSOR_TEST();
   2. 
	 BEGIN
	 CURSOR_TEST();
	 END;

3、查询存储过程
--查看存储过程的sql语句
SELECT text
    FROM user_source
   WHERE NAME = 'COUNT_NUMBER'
ORDER BY line;

注意oracle存储过程名都是大写？？
你在oracle 创建对象的时候， 如果没有加双引号， 默认都会转成大写的。 
在用这些对象的时候， 是不区分大小写的。  但是你差的视图， 他以字符串的形式存的，当然就会区分大小写了，就跟你查询正常的表记录一样。 

create table mytable(a int)  ; 
create table "mytable" (a int) ; 
-- 会创建两张表， 上面的会转成大写，下面的不转。

ALL_SOURCE 视图和DBA_SOURCE 视图具有USER_SOURCE 数据字典视图中的所有列和一个附加的Owner 列(对象的所有者)。ALL_SOURCE 数据字典视图可用于显示用户能够访问的所有过程对象的源代码，而不管所有者是谁。DBA_SOURCE 数据字典视图列出了数据库中所有用户的源代码。

4、删除存储过程
drop PROCEDURE [存储过程名];
drop PROCEDURE CREATEVIEWS;

5、查看存储过程编译报错。。
1.查看存储过程编译报错，如果编译过程中有报错会记录到SYS.USER_ERRORS表中
每次编译 会更新表中的编译报错内容。没有记录说明编译通过。
select * from SYS.USER_ERRORS where NAME = upper('COUNT_NUMBER');
2.SQL DEVELOPER中可以在编译报错的存储过程上右击选中Edit

6、存储过程和函数的区别
相同点：1.创建语法结构相似，都可以携带多个传入参数和传出参数。
　　　　2.都是一次编译，多次执行。
不同点：1.存储过程定义关键字用procedure，函数定义用function。
　　　　2.存储过程中不能用return返回值，但函数中可以，而且函数中必须有return子句。
　　　　3.执行方式略有不同，存储过程的执行方式有两种（1.使用execute2.使用begin和end），
          函数除了存储过程的两种方式外，还可以当做表达式使用，例如放在select中（select f1() form dual;）。
总结：如果只有一个返回值，用存储函数，否则，一般用存储过程。

一个函数的例子：
create or replace function f1
return varchar--必须有返回值，且声明返回值类型时不需要加大小
as
  msg varchar(50);
begin
   msg := 'hello world';
   return msg;
end;

--执行函数方式1
select f1() from dual;
--执行函数方式2
set serveroutput on;
begin 
  dbms_output.put_line(f1());
end;

7、总结：
1.创建存储过程的关键字为procedure。
2.传参列表中的参数可以用in，out，in out修饰，参数类型一定不能写大小。列表中可以有多个输入输出参数。
3.存储过程中定义的参数列表不需要用declare声明，声明参数类型时需要写大小的一定要带上大小。
4.as可以用is替换。
5.调用带输出参数的过程必须要声明变量来接收输出参数值。
6.执行存储过程有两种方式，一种是使用execute，另一种是用begin和end包住。
存储过程虽然有很多优点，但是它却不能使用return返回值。当需要使用return返回值时，我们可以使用函数。