xml地图|网站地图|网站标签 [设为首页] [加入收藏]
【宝马娱乐在线】oracle中的procedure编写和使用详
分类:网络频道

1.创建/修改

执行存储过程时,execute和call的区别 

CREATE [OR REPLACE] PROCEDURE procedure_name[(parameter_list)]{IS|AS}[local_declarations]BEGINexecutable_statements[EXCEPTION exception_handlers]END [procedure_name];

EXEC is a sqlplus command that put its argument as an anonymous pl/sql block: 'EXEC xxx' is transformed to 'BEGIN xxx; END;'
So you can use it to call a procedure, or do any pl/sql
It is documented here: http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch12022.htm#i2697931
CALL is a SQL statement that calls a stored procedure It is the 'standard' way to call a procedure without doing pl/sql.
For example if you call a procedure from JDBC it uses CALL to call the procedure and return parameters.
It is documented here: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_4008.htm#BABDEHHG

a.parameter_list格式如下

Now about v$sql, I can see both:

 parameter_name1 [in | out | in out] type, parameter_name1 [in | out] type [,........]

执行存储过程
SQL> set serveroutput on
SQL> execute dbms_output.put_line('test string1')
test string1

in 是输入参数, 可以有默认值,默认值例子 emp_no in number:=7900

PL/SQL procedure successfully completed.

out 是输出参数,

SQL> call dbms_output.put_line('called');

调用完成。

SQL> select sql_text
  2    from v$session
  3    join v$mystat
  4   using (sid)
  5    join v$sql
  6      on (v$sql.sql_id = v$session.prev_sql_id)
  7   where rownum = 1;

SQL_TEXT
----------------------------------------------------------------

call dbms_output.put_line('called')

SQL>
SQL> exec dbms_output.put_line('executed');

PL/SQL 过程已成功完成。

SQL> select sql_text
  2    from v$session
  3    join v$mystat
  4   using (sid)
  5    join v$sql
  6      on (v$sql.sql_id = v$session.prev_sql_id)
  7   where rownum = 1;

SQL_TEXT
----------------------------------------------------------------

BEGIN dbms_output.put_line('executed'); END;

SQL>

b.AS/IS的区别

 宝马娱乐在线 1

在视图(VIEW)中只能用AS不能用IS

上面的测试sql,使用PL/SQL developer无法得到预期的结果
宝马娱乐在线 2

在游标(CURSOR)中只能用IS不能用AS

宝马娱乐在线 3

c.local_declarations格式如下:

 

loacal_var1 type(limit);

oracle中斜杠(/)的含义
斜杠就是让服务器执行前面所写的sql脚本。如果是普通的select语句,一个分号,就可以执行了。但是如果是存储过程,那么遇到分号,就不能马上执行了。这个时候,就需要通过斜杠(/)来执行。
set serveroutput on;
begin
dbms_output.put_line('Hello World!');
end;
/
执行运行结果:
Hello World!

如empname varchar2(20);

PL/SQL procedure successfully completed

d.输出变量赋值

 

oracle 变量赋值有两种一种是直接 := 还有就是 select into

dbms_output包主要用于调试pl/sql程序,或者在sql*plus命令中显示信息(displaying message)和报表,譬如我们可以写一个简单的匿名pl/sql程序块,而该块出于某种目的使用dbms_output包来显示一些信息。

游标参数

涉及到的知识点如下:
1、enable:在serveroutput on的情况下,用来使dbms_output生效(默认即打开)
2、disable:在serveroutput on的情况下,用来使dbms_output失效
3、put:将内容写到内存,等到put_line时一起输出
4、put_line:不用多说了,输出字符,清空buffer
5、new_line:作为一行的结束,可以理解为写入buffer时的换行符
6、get_line(value, index):获取缓冲区的单行信息
7、get_lines(array, index):以数组形式来获取缓冲区的多行信息

outcur OUT BASIC_CURSOR: OPEN outcur FOR SELECT col1,col2 FROM tablename

需要注意以下几点:
1、set serveroutput on:如果要在sqlplus中看到dbms_output的输出,则必须设置该参数值为on
2、每行能容纳的最大值是32767bytes
3、buffer的默认值是20000bytes,可设置的最小值为2000bytes,最大值为1000000bytes

e.示例

 例子一、put和new_line

create or replace procedure putNum(P_Date in date, P_year out varchar2) is v_num number(8) := 1; v_days number; v_date date; begin dbms_output.put_line('intput value:' || P_year); v_num := 1; v_days := 1; v_days := to_number(to_char(P_Date, 'dd')); -- to_char(sysdate-20,'dd') for i in 1 .. v_days Loop v_date := to_date('2011/11/' || to_char(i), 'yyyy/MM/dd'); --dbms_output.put_line(v_date); end loop; P_year := '2012'; end;
set serveroutput on;
begin
   dbms_output.put('a'); --写入buffer但不输出
   dbms_output.put('b'); --写入buffer但不输出
   dbms_output.new_line; --回车(换行),输出                              
   dbms_output.put_line('hello world!'); --输出并换行 
   dbms_output.put('d'); --写入buffer但不输出 
end;                                                     
/ 

2.调用

宝马娱乐在线 4

[EXECUTE]|[CALL] procedure_name[(parameter,…n)]

例子二、put_line

set serveroutput off;
create table t(a int, b int, c int);
insert into t values(111111,222222,333333);
insert into t values(444444,555555,666666);
insert into t values(777777,888888,999999);
commit;

create table tt(a int,b varchar2(100));

declare
   msg varchar2(120);                               
   cursor t_cur is select * from t order by a;      
   v_line varchar2(100);                            
   v_status integer := 0;                           
begin                                           
   dbms_output.enable;                              
   for i in t_cur loop                              
       msg := i.a || ',' || i.b || ',' || i.c;          
       dbms_output.put_line(msg); --put                   
   end loop;                                       

   dbms_output.get_line(v_line, v_status); --get          
   while v_status = 0 loop                         
       insert into tt values(v_status, v_line);        
       dbms_output.get_line(v_line, v_status);          
   end loop;                                       
end;                                            
/                                               

select * from tt;

执行结果如下:

a b


0 111111,222222,333333
0 444444,555555,666666
0 777777,888888,999999

注:使用get_line时不能用put_line输出,因为put_line之后会将buffer清空。(当然在serveroutput off的情况下put_line是不影响buffer的)。

例子三:put_lines

set serveroutput on;
declare
   v_data dbms_output.chararr;                          
   v_numlines number;                                   
begin                                               
   --enable the buffer first.                          
   dbms_output.enable(1000000);                         

   dbms_output.put_line('line one');                    
   dbms_output.put_line('line two');                    
   dbms_output.put_line('line three');                 

   v_numlines := 3;                                    
   dbms_output.get_lines(v_data, v_numlines);  --array, index        
   for v_counter in 1..v_numlines loop                 
       dbms_output.put_line(v_data(v_counter));            
   end loop;                                           
end;                                                
/

执行结果如下:

line one
line two
line three

http://www.cnblogs.com/linjiqin/archive/2013/06/24/3152647.html

 

在代码块 declare 的 begin/end 中不需要 [EXECUTE]|[CALL], 直接 procedure_name[(parameter,…n)]

一、过程 (存储过程)

过程是一个能执行某个特定操作的子程序。使用CREATE OR REPLACE创建或者替换保存在数据库中的一个子程序。
示例1:声明存储过程,该过程返回dept表行数

DECLARE
PROCEDURE getDeptCount
AS
deptCount INT;
BEGIN
SELECT COUNT(*) INTO deptCount FROM DEPT;
DBMS_OUTPUT.PUT_LINE('DEPT表的共有记录数:'||deptCount);
END getDeptCount;
BEGIN
getDeptCount[()];
END;

 

注意:此存储过程getDeptCount只在块运行时有效。
示例2:创建不带参数的存储过程,该过程返回dept表行数

CREATE OR REPLACE PROCEDURE getDeptCount
AS | IS
deptCount int;
BEGIN
SELECT COUNT(*) INTO deptCount FROM dept;
DBMS_OUTPUT.PUT_LINE('dept表共有'||deptCount||'行记录');
END [getDeptCount];

 

当我们创建的存储过程没有参数时,在存储过程名字后面不能有括号。在AS或者IS后至BEGIN之前是声明部分,存储过程中的声明不使用DECLARE关键字。同匿名PL/SQL块一样,EXCEPTION和声明部分都是可选的。
当我们创建的过程带有错误时,我们可以通过SELECT * FROM USER_ERRORS查看,或者使用SHOW ERRORS [ PROCEDURE Proc_Name]查看。
使用以下代码可以执行存储过程:

BEGIN
getDeptCount;
END;
以上存储过程还可以通过以下代码来简化调用:
EXEC getDeptCount[;] 
CALL  getDeptCount();

 

注意:

  • 并不是所有的存储过程都可以用这种方式来调用
  • 定义无参存储过程时,存储过程名后不能加()
  • 在块中或是通过EXEC调用存储过程时可以省略()
  • 通过CALL调用无参存储过程必须加上()

示例3:创建带有输入参数的存储过程,该过程通过员工编号打印工资额

CREATE OR REPLACE PROCEDURE getSalaryByEmpNo(eNo NUMBER)  --参数的数据类型不能指定长度
AS
salary emp.sal%TYPE;
BEGIN
SELECT SAL INTO salary  FROM EMP WHERE EMPNO=eNo;
DBMS_OUTPUT.PUT_LINE(eNo||'号员工的工资为'||salary);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('没有找到该编号的员工');
END;

 

当定义的存储过程含有参数时,参数的数据类型不能指定长度。参数还有输入和输出之分,本例中没有指定,默认情况为输入参数,也可显示的指定某个参数是输入参数,如(eNo IN NUMBER)。同示例1不同,该例中加入了异常处理。同示例1类似可以使用下面的两种方式调用存储过程:
BEGIN
getSalaryByEmpNo(7788);
END;
或者
EXEC getSalaryByEmpNo(7788);  或者
CALL getSalaryByEmpNo(7788);
但是如果传给一个存储过程的参数是变量时,必须使用BEGIN  END块,如下:

DECLARE
no emp.empNo%TYPE;
BEGIN
no:=7788;
getSalaryByEmpNo(no);
END;

 

如果某个包中含有常量,也可以通过如下的方式调用:
EXEC getSalaryByEmpNo(ConstantPackage.no);
但这种方式不能再使用CALL调用。
示例4:创建含有输入和输出参数的存储过程,该过程通过员工编号查找工资额,工资额以输出参数返回

CREATE OR REPLACE PROCEDURE getSalaryByEmpNo(eNo IN NUMBER,salary OUT NUMBER)
AS
BEGIN
SELECT SAL INTO salary  FROM EMP WHERE EMPNO=eNo;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('没有找到该编号的员工');
END;

 

当过程中含有输出参数时,调用时必须通过BEGIN  END块,不能通过EXEC或CALL调用。如:

DECLARE
salary NUMBER(7,2);
BEGIN
getSalaryByEmpNo(7788,salary);
DBMS_OUTPUT.PUT_LINE(salary);
END;

 

示例5:创建参数类型既是输入参数也是输出参数的过程

CREATE OR REPLACE PROCEDURE getSalaryByEmpNo(noSalary IN OUT NUMBER)
AS
BEGIN
SELECT SAL INTO noSalary FROM EMP WHERE EMPNO=noSalary;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('没有找到该编号的员工');
END;

 

调用如下:

DECLARE
no NUMBER(7,2);
BEGIN
no:=7788;
getSalaryByEmpNo(no);
DBMS_OUTPUT.PUT_LINE(no);
END;

 

示例6:创建带有默认值的过程

CREATE OR REPLACE PROCEDURE addEmp
(
empNo NUMBER,
eName VARCHAR2,
job  VARCHAR2 :='CLERK',
mgr  NUMBER,
hiredate DATE  DEFAULT SYSDATE,
sal  NUMBER  DEFAULT 1000,
comm  NUMBER  DEFAULT 0,
deptNo NUMBER  DEFAULT 30
)
AS
BEGIN
INSERT INTO emp VALUES(empNo,eName,job,mgr,hiredate,sal,comm,deptNo);
END;

 

调用如下:

EXEC addEmp(7776,'zhangsan','CODER',7788,'06-1月-2000',2000,0,10);  --没有使用默认值
EXEC addEmp(7777,'lisi','CODER',7788,'06-1月-2000',2000,NULL,10);  --可以使用NULL值
EXEC addEmp(7778,'wangwu',mgr=>7788);  --使用默认值
EXEC addEmp(mgr=>7788,empNo=>7779,eName=>'sunliu');  --更改参数顺序

 

示例7:使用NOCOPY编译提示
当参数是大型数据结构时,如集合、记录和对象实例,把它们的内容全部拷贝给形参会降低执行速度,消耗大量内存。为了防止这样的情况发生,我们可以使用 NOCOPY提示来让编译器按引用传递方式给IN OUT模式的参数。

DECLARE
TYPE DeptList IS TABLE OF VARCHAR2(10);
dList  DeptList:=DeptList('CORESUN','CORESUN','CORESUN','CORESUN');
PROCEDURE My_Proc(d IN OUT NOCOPY DeptList)
AS...

 

注意:NOCOPY只是一个提示,而不是指令。即使有时候我们使用了NOCOPY,但编译器有可能仍然会进行值拷贝。通常情况下NOCOPY是可以成功的。

本文由宝马娱乐在线发布于网络频道,转载请注明出处:【宝马娱乐在线】oracle中的procedure编写和使用详

上一篇:oracle中 procedure(存储过程)和function(函数)本质区别 下一篇:没有了
猜你喜欢
热门排行
精彩图文