oracle存储过程和sqlserver存储过程

oracle存储过程：

声明：

外部参数 只有数据类型没有大小

Procedure Pro_Insert_Stuff_Check( v_id VARCHAR2,                                                   
                                  v_card_id VARCHAR2,  
                                  v_direction_flag CHAR,
                                  v_is_pass CHAR,
                                  v_check_date DATE,
                                  v_stuff_name VARCHAR2,
                                  v_stuff_id VARCHAR2,
                                  v_device_id VARCHAR2,
                                  v_photo_path VARCHAR2,
                                  v_airport_code Varchar2,
                                  v_group_code Varchar2); 

主体：

 Procedure Pro_Insert_Stuff_Check(v_id VARCHAR2,                                                   
                                  v_card_id VARCHAR2,  
                                  v_direction_flag CHAR,
                                  v_is_pass CHAR,
                                  v_check_date DATE,
                                  v_stuff_name VARCHAR2,
                                  v_stuff_id VARCHAR2,
                                  v_device_id VARCHAR2,
                                  v_photo_path VARCHAR2,
                                  v_airport_code           Varchar2,
                                  v_group_code              Varchar2) AS
  recordnum NUMBER;
  nation VARCHAR2(60);
  sex VARCHAR2(2);	
  birthday DATE;	
  address VARCHAR2(100);
  idnumber VARCHAR2(64);				  
  Begin  
    SELECT COUNT(*) INTO recordnum FROM TB_AIRPORT_STUFF  WHERE APSTF_ID = v_card_id;
    IF recordnum > 0 THEN 
      select APSTF_NATION, APSTF_SEX, APSTF_BIRTHDAY, APSTF_ADDRESS, APSTF_ID_NUMBER 
      into nation, sex, birthday, address, idnumber from TB_AIRPORT_STUFF where APSTF_ID = v_card_id;
      
      insert into TB_STUFF_CHECK(GROUP_CODE,AIRPORT_CODE,STC_ID, STC_CARD_ID, STC_DIRECTION_FLAG, STC_IS_PASS, STC_CHECK_DATE, STC_STUFF_NAME, STC_STUFF_ID, STC_CARD_NATION, STC_CARD_GENDER, STC_CARD_BIRTHDAY, STC_CARD_ADDRESS, STC_CARD_NUMBER, STC_DEVICE_ID, STC_PHOTO_PATH) 
			values(v_group_code,v_airport_code,v_id, v_card_id, v_direction_flag, v_is_pass, v_check_date, v_stuff_name, v_stuff_id, nation, sex, birthday, address, idnumber, v_device_id, v_photo_path);
    ELSE
      insert into TB_STUFF_CHECK(GROUP_CODE,AIRPORT_CODE,STC_ID, STC_CARD_ID, STC_DIRECTION_FLAG, STC_IS_PASS, STC_CHECK_DATE, STC_STUFF_NAME, STC_STUFF_ID, STC_DEVICE_ID, STC_PHOTO_PATH) 
			values(v_group_code,v_airport_code,v_id, v_card_id, v_direction_flag, v_is_pass, v_check_date, v_stuff_name, v_stuff_id, v_device_id, v_photo_path);              
    END IF;
  End Pro_Insert_Stuff_Check;

内部参数：需要类型及大小

游标：游标分为隐式游标 (select into)和显式游标，显式游标又分为静态游标和动态游标

显式游标：显式游标的写法会在存储过程中定义Cursor ,并且一般都有固定的四个步骤：声明游标 、打开游标 、提取数据 、关闭游标 。显式游标打开后，必须显式地关闭。游标一旦关闭，游标占用的资源就被释放，游标变成无效，必须重新打开才能使用。 
隐式游标：隐式游标其实默认已经打开游标，并在执行完成后关闭游标释放资源。

遍历游标的三种方式：

for循环：

create or replace package body test_lx is
Procedure pro_lx_test 
AS
--游标的定义
Cursor test_lx_cursor is
select * from test;
cur test_lx_cursor%rowtype;   -- 声明一个变量，变量的类型为游标s_cur中的一行数据类型
BEGIN
    -- for循环
    for cur in test_lx_cursor loop
    exit when test_lx_cursor%notfound;
    dbms_output.put_line('for循环数据是:'||cur.id||'_'||cur.name);
    end loop;
    -- fetch循环
    OPEN test_lx_cursor;
      LOOP 
        FETCH test_lx_cursor INTO CUR;
        EXIT WHEN test_lx_cursor%NOTFOUND;
        dbms_output.put_line('FETCH循环数据是:'||cur.id||'_'||cur.name);
      END LOOP;
      CLOSE test_lx_cursor;
      
      --While 循环
      OPEN test_lx_cursor;--必须要明确的打开和关闭游标
        FETCH test_lx_cursor INTO CUR;
        WHILE test_lx_cursor%FOUND LOOP  
          --循环体
          dbms_output.put_line('while循环数据是:'||cur.id||'_'||cur.name); 
          FETCH test_lx_cursor INTO CUR;       
        END LOOP;
      CLOSE test_lx_cursor;
END;

end test_lx;

输出：

for循环数据是:1_litltiu
for循环数据是:2_ghsthst
for循环数据是:3_fvadrfgaf
FETCH循环数据是:1_litltiu
FETCH循环数据是:2_ghsthst
FETCH循环数据是:3_fvadrfgaf
while循环数据是:1_litltiu
while循环数据是:2_ghsthst
while循环数据是:3_fvadrfgaf

for循环：自动打开游标和关闭游标

变量赋值：：=

return：退出整个程序

exit：退出for循环

goto xxxx：跳转到标记所在位置

带有返回值的存储过程：

procedure pro_test_op(returnvalue out varchar2, data out sys_refcursor)
  AS
  begin   
  open data for select * from test;  
  select t.name into returnvalue from test t where id = '1';
  end ;

结果：

cursor：静态定义

sys_refcursor：动态关联结果集的临时对象。即在运行的时候动态决定执行查询。

sqlserver存储过程：

--创建无参的存储过程

--1.创建无参存储过程
if (exists (select * from sys.objects where name = 'getAllBooks'))
    drop proc proc_get_student
go
create procedure getAllBooks
as
select * from books;
--调用,执行存储过程
exec getAllBooks;

--创建有参数的存储过程 

if (exists (select * from sys.objects where name = 'searchBooks1'))
    drop proc searchBooks1
go
create proc searchBooks1(
    @bookID int,
    @bookAuth varchar(20)
)
as
    --要求book_id和book_Auth列与输入参数相等
    select * from books where book_id=@bookID and book_auth=@bookAuth;
exec searchBooks1 1,'金庸'

 --创建有返回值的存储过程

if (exists (select * from sys.objects where name = 'getBookId'))
    drop proc getBookId
go
create proc getBookId(
    @bookAuth varchar(20),
    @bookId int output 
)
as
    select @bookId=book_id from books where book_auth=@bookAuth

declare @id int 
exec getBookId '孔子',@id output
select @id as bookId;

--游标的使用

DECLARE @varCursor Cursor --声明游标变量

DECLARE cursor_fruit CURSOR FOR --创建游标

SELECT f_name,f_price FROM fruits;

OPEN cursor_fruit --打开游标

SET @varCursor=cursor_fruit --为游标变量赋值

FETCH NEXT FROM @varCursor --从游标变量中读取值

WHILE @@FETCH_STATUS=0 --判断FETCH语句是否执行成功

BEGIN

FETCH NEXT FROM @varCursor --读取游标变量中的数据

END

CLOSE @varCursor --关闭游标

DEALLOCATE @varCursor; --释放游标