oracle学习总结--存储过程

1、存储过程的创建CREATE OR REPLACE PROCEDURE [存储过程名][(输入、输出参数)] AS/IS[变量、游标声明]BEGIN[执行体]END;create or replace procedure sayHello --无参数时()不需要写asbegin  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_DATAASX 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_TESTASr1 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中可以在编译报错的存储过程上右击选中Edit6、存储过程和函数的区别相同点：1.创建语法结构相似，都可以携带多个传入参数和传出参数。　　　　2.都是一次编译，多次执行。不同点：1.存储过程定义关键字用procedure，函数定义用function。　　　　2.存储过程中不能用return返回值，但函数中可以，而且函数中必须有return子句。　　　　3.执行方式略有不同，存储过程的执行方式有两种（1.使用execute2.使用begin和end），          函数除了存储过程的两种方式外，还可以当做表达式使用，例如放在select中（select f1() form dual;）。总结：如果只有一个返回值，用存储函数，否则，一般用存储过程。一个函数的例子：create or replace function f1return varchar--必须有返回值，且声明返回值类型时不需要加大小as  msg varchar(50);begin   msg := 'hello world';   return msg;end;--执行函数方式1select f1() from dual;--执行函数方式2set 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返回值时，我们可以使用函数。