oracle加工存储过程,oracle存储过程---创建存储过程语句

一、创建存储过程语句

语法:  CREATE OR REPLACE PROCEDURE testname( argument1 TYPE1, ....  ) AS BEGIN ...... END   testname

例子:

CREATE OR REPLACE PROCEDUREtest_name(

arg1VARCHAR2,arg2 number,arg3 number)AS

BEGIN

insert intotest_for_insert(

STACID, LOANNO, SYSTID, PARA1, PARA2

)values(1, arg1, 'wld', arg2, arg3

);

dbms_output.put_line('work!');END test_name;

右键‘测试’，输入参数

二、存储过程使用游标

游标就像循环里面的指针

语法：定义 :  CURSOR  point  IS SELECT number FROM test_table;

使用：FOR  test_point  IN  point LOOP

.................................

END LOOP;

create or replace procedure test1 (sys in varchar2)isv_sys test_table.SYSTID%TYPE;

v_argnumber(10,2);CURSOR table_cursor IS

SELECT SYSTID, NUMBER fromtest_table;begin

for test_cursor intable_cursor LOOPif sys = 'TEST' thendbms_output.put_line('work');end if;ENDLOOP;end test1;

三、给变量赋值

语法  :   SELECT  a.number, a INTO varible1 FROM test_table a;

例子 ：

create or replace procedure test_pro(sys in varchar2) isv_sys test_table.SYSTID%TYPE;

v_varible1number(10,2);

v_varible2number(10,2);CURSOR test_cursor IS

SELECT SYSTID, NUMBER

fromtest_table;BEGIN

for v_cursor intest_cursor LOOPif sys = 'wld' then

select t.SYSTID, nvl(sum(t.var1+t.var2),0)intov_sys, v_varible1from test_table t where t.NUMBER = v_cursor.NUMBER;

dbms_output.put_line('SYS :' || v_sys || 'v_varible1 :' ||v_varible1 );end if;ENDLOOP;END test_pro;

四、 插入表格

语法: INSERT INTO table1 ( arg1, arg2 .....) SELECT varible1, varible2 ..... FROM table2;

例子：

create or replace procedure test3 (sys in varchar2)isv_sys test_table.SYSTID%TYPE;

v_argnumber(10,2);begin

INSERT INTOtable1

(

arg1, arg2, ....

)selectvarible1, varible2,...fromtable2;end test3;

------------- ------------- 谢谢大佬打赏 ------------- -----------