Oracle儲存過程乾貨（一）：儲存過程基礎

/ SQLplus 中，設定 set serveroutput on 才能顯示輸出結果 /

--- 匿名的 PLSQL( 儲存過程 ) 塊，不儲存在資料庫中

begin
null;
end;
/

begin
dbms_output.put('hi ');    /*put是不換行的輸出*/
dbms_output.put_line('hello world');   /*put_line是換行的輸出*/
end;
/

--- 命名的儲存過程，儲存在資料庫中

create or replace procedure hello_procedure as
begin
null;
end hello_procedure;
/

--- 儲存過程裡面可以直接執行 DML

create table test (id number,name varchar(20));
begin
insert into test values(1,'zmh');
commit;
end;
/
 
begin
insert into test values(2,'qyt');
commit;
end;
/

--- 儲存過程中不能直接執行 select ，如果要執行 select ，必須跟上 into 字句

begin
select * from test;
end;
/

PLS-00428: 在此 SELECT 語句中缺少 INTO 子句

這個地方，我的理解是在儲存過程中執行 select 語句，需要先把資料賦值到事先宣告好的變數中，然後透過變數輸出出來，一個列需要對應一個變數。

declare
v_name varchar2(20);
begin
select name into v_name from test where id=1;
dbms_output.put_line(v_name);
end;
/

---select...into 只能賦值一行，不能賦值多行，如果賦值了不存在的行也會報錯 ( 但可以處理 )

意思就是儲存過程中， select 只能查一行 , 如下就會報錯 , 提示超出請求行數：

declare
v_name varchar2(20);
begin
select name into v_name from test;
dbms_output.put_line(v_name);
end;
/

ORA-01422: 實際返回的行數超出請求的行數

因此需要對 select 語句加 where 條件： where rownum<=1 或者 where id=1 。

下面是賦值了不存在的行的報錯，及解決辦法：

declare
v_name varchar2(20);
begin
select name into v_name from test where id=3;
dbms_output.put_line(v_name);
end;
/

ORA-01403: 未找到任何資料

解決的小技巧是，寫一個 union ，實際情況 union 空，將返回的值透過 max 函式篩選：

declare
v_name varchar2(20);
begin
select max(name) into v_name from (
select name from test where id=3
union
select null from dual);
dbms_output.put_line(v_name);
end;
/

---select...into 多個列

雖然 select into 不能多個行，但是可以多個列：

declare
v_id number;
v_name varchar2(20);
begin
select id,name into v_id,v_name from test where id=1;
dbms_output.put_line(v_id||','||v_name);
end;
/

--- 儲存過程裡面不能直接執行 DDL ，如果要執行 DDL ，可以使用動態 SQL(execute immediate)

begin
alter table test add(tel varchar2(12));
end;
/

PLS-00103: 出現符號 "ALTER" 在需要下列之一時 ....

解決辦法；

begin
execute immediate 'alter table test add(tel varchar2(12))';
end;
/

--- 動態 SQL 裡面不僅可以 DDL ， DML 和 select...into 都可以