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返回值时,我们可以使用函数。
oracle学习总结--存储过程
最新推荐文章于 2020-06-01 01:01:23 发布