程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> PL/SQL之--存儲過程，plsql存儲過程

PL/SQL之--存儲過程，plsql存儲過程

編輯：Oracle教程

PL/SQL之--存儲過程，plsql存儲過程

一、存儲過程

　　存儲過程是一組為了完成特定功能的SQL 語句集，經編譯後存儲在數據庫中，用戶通過指定存儲過程的名字並給出參數（如果該存儲過程帶有參數）來執行它。oracle可以把PL/SQL程序儲存在數據庫中，並可以在任何地方來運行它。存儲過程被稱為PL/SQL子程序，是被命名的PL/SQL快，存儲在數據庫，通過輸入、輸出參數與調用者交換信息。oracle存儲過程不返回數據。

　　語法：

　　create or replace procudure 存儲過名稱( 　
　　　　參數名稱　　輸入輸出類型　　參數類型，　　　
　　　　參數名稱　　輸入輸出類型　　參數類型　
　　) 　　
　　is
　　begin
　　　　處理語句;
　　　　exceeption;
　　　　　　異常處理語句;
　　end 存儲過名稱;

　　輸出輸出類型有如下三種：　　

IN 定義一個輸入參數變量，用於傳遞參數給存儲過程，存儲過程無法改變參數值，該參數可以是常量、或是有值的變量。
OUT 定義一個輸出參數變量，用於從存儲過程獲取數據，該參數必須是一個變量，該變量是否有值不重要。
IN OUT 定義一個輸入、輸出參數變量，兼有以上兩者的功能，該參數必須是一個變量，該變量必須有值。

　　輸出輸出參數類型一般不聲明長度，因為對於IN參數，其寬度是由外部決定。對於OUT 和IN OUT 參數，其寬度是由存儲過程內部決定。對於沒有說明輸入輸出類型的參數，默認為IN類型。

二、示例

　　以下代碼person表結構如下：

DROP TABLE person ;
CREATE TABLE person (
id NUMBER(11) NOT NULL ,
username VARCHAR2(255 ) NULL ,
age NUMBER(11) NULL ,
password VARCHAR2(255) NULL ,
PRIMARY KEY (id)
)
INSERT INTO person  VALUES ('1', '張三', '100', 'zhang123');
INSERT INTO person  VALUES ('2', '李四', '20', 'lisi123');
INSERT INTO person  VALUES ('3', '王五', '20', 'wang123');
INSERT INTO person  VALUES ('4', '趙六', '20', 'zhao123');

　　1、查詢一個（in、out）

create or replace procedure pro_person_getbyid(
       p_id in number,
       p_username out varchar2,
       p_age out number,
       p_password out varchar2
)
is
begin
  select username, age, password into p_username, p_age, p_password from person where id = p_id;
end pro_person_getbyid;
-- 調用代碼 --------------
declare
    v_id number;
    v_username varchar2(255);
    v_age number;
    v_password varchar2(255);
begin
    v_id := 1;
    pro_person_getbyid(v_id, v_username, v_age, v_password);
    dbms_output.put_line('username:'||v_username||' age:'||v_age||' password:'||v_password);
end;

　　2、查詢一個（in、out）使用rowtype

create or replace procedure pro_person_getrow(
       p_id in number,
       p_row out person%rowtype, -- rowtype類型變量
       p_count out number -- 標記是否找到記錄
)
is
begin
  select * into p_row from person where id = p_id;
  p_count := SQL%ROWCOUNT;
  exception
    when no_data_found then
      p_count := 0;
end pro_person_getrow;
-- 調用--------------
declare
    v_id number := 28;
    v_row person%rowtype;
    v_count number;
begin
  pro_person_getrow(v_id, v_row, v_count);
  dbms_output.put_line(v_count);
  dbms_output.put_line('id:'||v_row.id||' username:'||v_row.username||' age:'||v_row.age||' password:'||v_row.password);
end;

　　3、添加記錄（in、out）

create or replace procedure pro_person_insert(
       p_id number,
       p_username varchar2,
       p_age number,
       p_password varchar2,
       p_count out number -- 是否添加成功
)
is
begin
   insert into person (id, username, age, password) values(p_id, p_username, p_age, p_password);
   p_count := SQL%ROWCOUNT;  -- SQL%ROWCOUNT為 隱式游標的屬性
   commit;
   exception
     when others then
     p_count := 0; -- 失敗
end pro_person_insert;

-- 調用procedure
declare
  v_id number := 28;
  v_username varchar2(255) := 'xiaoli';
  v_age number := 19;
  v_password varchar2(255) := 'xiao123';
  v_count number;
begin
  pro_person_insert(p_id  => v_id, p_username  => v_username, p_age => v_age, p_password => v_password, p_count => v_count);
 --  pro_person_insert(v_id , v_username, v_age, v_password, v_count);
  dbms_output.put_line('影響行數'||v_count);
end;

　　4、更新（in、out）

create or replace procedure pro_person_update(
       p_id number,
       p_age number,
       p_password varchar2,
       p_count out number
)
is
begin
  update person set age = p_age, password = p_password where id = p_id;
  p_count := SQL%ROWCOUNT;
  commit;
  exception
    when no_data_found then   
      p_count := 0;
    when others then
      p_count := -1;
end pro_person_update;
-- 調用---------------------
declare
    v_id number := 28;
    v_age number := 19;
    v_password varchar2(255) := 'password';
    v_count number;
begin
  pro_person_update(v_id, v_age, v_password, v_count);
    dbms_output.put_line('影響行數'||v_count);
end;

　　5、刪除（in、out）

create or replace procedure pro_person_delete(
       p_id number,
       p_count out number
)
is
begin
  delete from person where id = p_id;
  p_count := SQL%ROWCOUNT;
  commit;
  exception
    when no_data_found then   
      p_count := 0;
    when others then
      p_count := -1;    
end pro_person_delete;
-- 調用----------------
declare
    v_id number := 28;
    v_count number;
begin
  pro_person_delete(v_id, v_count);
  dbms_output.put_line('影響行數'||v_count);
end;

　　6、查詢所有（in、out）使用sys_refcursor

create or replace procedure pro_person_findall2( 
       p_cursor out sys_refcursor -- 輸出參數為包類型
)
is
begin 
  open p_cursor for
  select *  from person;  
  exception
  when others then
    DBMS_OUTPUT.PUT_LINE('獲取信息發生錯誤');
end pro_person_findall2;

----調用---------------------------------------------------
declare
    c_cursor sys_refcursor;
    r_person person%rowtype;
begin
  pro_person_findall2(c_cursor);
  --2、打開游標
--  open c_cursor; --此處不需要顯示地打開游標，因為調用存儲過程的時候返回的游標已經打開了
  --3、提取數據
  loop
    fetch c_cursor 
    into r_person;
    exit when c_cursor%notfound; -- 下面沒有數據的時候，退出
    dbms_output.put_line('id:'||r_person.id);
    dbms_output.put_line('username:'||r_person.username);
    dbms_output.put_line('age:'||r_person.age); 
  end loop; 
end;

　　7、查詢所有（in、out）使用自定義類型查詢

-- 創建一個包類型
create or replace package pkg_const as
  type r_cursor is ref cursor;
end  pkg_const;

-- 創建存儲過程，
create or replace procedure pro_person_findall( 
       p_cursor out pkg_const.r_cursor -- 輸出參數為包類型
)
is
begin 
  open p_cursor for
  select *  from person;  
  exception
  when others then
    DBMS_OUTPUT.PUT_LINE('獲取信息發生錯誤');
end pro_person_findall;

----調用------------------------------------
declare
    c_cursor pkg_const.r_cursor;
    r_person person%rowtype;
begin
  pro_person_findall(c_cursor);
  --2、打開游標
--  open c_cursor;
  --3、提取數據
  loop
    fetch c_cursor 
    into r_person;
    exit when c_cursor%notfound; -- 下面沒有數據的時候，退出
    dbms_output.put_line('id:'||r_person.id);
    dbms_output.put_line('username:'||r_person.username);
    dbms_output.put_line('age:'||r_person.age); 
  end loop; 
end;

三、存儲過程其他語句

　　查看存儲過程

DESCRIBE 存儲過程名;

　　刪除存儲過程

DROP PROCEDURE 存儲過程名;

oracle pl/sql 怎通過存儲過程看語句

右鍵點表 view 再點右下角 view sql 就能看了或者edit都可以

怎在PL/SQL Developer 中調用存儲過程？

看你存儲過程裡是否有參數了

正常的話，在左邊列表找到procedure裡找到你寫的那存儲過程名，看上邊有紅叉沒，沒紅叉就是編譯成功，有紅叉就是還有問題，需要改

然後右鍵點這個存儲過程，（中文版選擇測試按鈕，英文版選擇test按鈕），然後下邊有輸入參數的地方，輸入，點上邊的齒輪即可

還有種方法，就是開一個sql窗口，寫以下代碼
begin
存儲過程名;
end;

然後點齒輪運行，注意兩個分號不可少