pl/sql編程基礎

PL/SQL

1、過程、函數、觸發器是pl/sql編寫的

2、過程、函數、觸發器是存放在oracle數據庫中的

3、pl/sql是非常強大的過程化語言

4、過程、函數、觸發器可以在java程序中調用

pl/sql編寫能節省一點時間就是提高了性能（量大），java直接調用數據庫存放的過程，解析時間就節省下來了，提高了性能

模塊化的設計思想-----》存儲過程

網絡傳輸（java程序中編寫的sql語言），直接調用數據庫的過程節省了傳輸量

提高安全性（存儲過程避免了數據庫信息的泄漏）

缺點：

移植性不好

pl/sql編程基本單位是塊，通過塊可以編寫出過程、函數、觸發器、包

下面進行一個最基本的編程

案例：向某表中插入一條數據

create or repalce procedure 名稱 is :replace表示如果名稱已存在，就替換

begin

insert into test values ('xiaoming','redhat')

end;

/

create procedure創建存儲過程關鍵字

or repalce：表示如果名稱已存在，就替換

is：也是關鍵字

存儲過程定義的頭和begin之間是定義部分（定義變量常量等等），后面提到

begin：關鍵字

end：結束符

begin與end結束之間就是執行部分

上面一個簡單的存儲過程是向某表中添加一條數據，現在先創建一張表

SQL> create table names(name varchar2(20),password varchar2(30));

Table created.

然后通過編寫一個存儲過程向其中添加數據

SQL> create or replace procedure sp_pro1 is

  2  begin

  3  insert into names values ('xiaoming','redhat');

  4  end;

  5  /

Procedure created.

存儲過程已建立，該如何執行呢？使用關鍵字exec或者call，如下

SQL> exec sp_pro1;

PL/SQL procedure successfully completed.

然后查詢表，看是否添加了數據

SQL> select * from names;

NAME                 PASSWORD

-------------------- ------------------------------

xiaoming             redhat

當若編寫的過程有誤時，可以通過show error這條命令查看錯誤具體信息

pl/sql編程是由最小單位塊組成的，看看塊的組成部分

pl/sql塊由三個部分組成：定義部分、執行部分、例外處理部分

declare

  定義部分，定義常量、變量、游標、例外、復雜數據類型  

begin

執行部分，要執行的pl/sql語句和sql語句

exception

例外處理部分，處理運行的各種錯誤

定義部分是從declare開始的，可選

執行部分從begin開始，必選

例外處理部分從exception，可選

下面編寫一條最簡單的塊，輸出hello world

編寫之前打開系統的屏幕輸出信息，不然看不到效果

SQL> set serveroutput on;

SQL> begin

  2  dbms_output.put_line('hello world');

  3  end;

  4  /

hello world

PL/SQL procedure successfully completed.

最簡單的塊編程，只有執行部分，而且只輸出了一條信息hello world。

相關說明：dbms_output是oracle所提供的包（類似java的開發包），該包包含一些過程，put_line就是

dbms_output包的一個過程（包里面的一個過程）

實例2：包含定義部分和執行部分

SQL> declare 

  2  v_name varchar2(20);    改行表示定義的變量，變量名v_name，數據類型為varchar2

  3  begin

  4  select ename into v_name from emp where empno=&empno;  &由鍵盤輸出

  5  dbms_output.put_line(v_name);

  6  end;

  7  /

Enter value for empno: 7788

old   4: select ename into v_name from emp where empno=&empno;

new   4: select ename into v_name from emp where empno=7788;

SCOTT        輸出到屏幕的信息v_name

PL/SQL procedure successfully completed.

實例3：包含定義部分、執行部分和例外處理部分

為了避免pl/sql程序的運行錯誤，提高pl/sql的健壯性，應該對可能的錯誤進行處理

相關說明：oracle事先預定義了一些例外，no_data_found就是找不到數據的例外

如上述例子，如果輸入煩人不是emp表中的empno號碼，那么將會報錯，報錯該如何處理呢，這里就定義例外部分，交給他處理

SQL> declare 

  2  v_name varchar2(20);

  3  v_sal number(7,2);

  4  begin

  5  select ename,sal into v_name,v_sal from emp where empno=&empno;

  6  dbms_output.put_line(v_name||' '||v_sal);

  7  exception             定義例外關鍵字exception

  8  when no_data_found then    當查詢不到數據時，采取措施打印error

  9  dbms_output.put_line('error');

 10  end;

 11  /

Enter value for empno: 78     78并不在emp表中的empno好中

old   5: select ename,sal into v_name,v_sal from emp where empno=&empno;

new   5: select ename,sal into v_name,v_sal from emp where empno=78;

error       打印error

過程

過程用于執行特定的操作，當建立過程時，既可以指定輸入參數（in），也可以指定輸出參數（out）。通過在過程中使用輸入參數可以將數據傳遞帶執行部分；通過使用輸出參數，可以將執行部分的數據傳遞到應用環境。在sqlplus中使用create procedure 命令來建立過程

SQL> --過程編寫

SQL> create or replace procedure sp_pro1(name varchar2,pass varchar2) is 

  2  begin

  3  insert into names values (name,pass);

  4  end;

  5  /

Procedure created.

sp_pro1(name varchar2,pass varchar2)：這里面的參數相當于編程里面的形參，傳遞的數據

然后調用該存儲過程

SQL> exec sp_pro1('xiaobai','redhat');

PL/SQL procedure successfully completed.

然后查詢數據是否已經插入了

SQL> select * from names;

NAME                 PASSWORD

-------------------- ------------------------------

xiaoming             redhat

xiaobai              redhat

修改表emp的雇員為smith的薪水，編寫存儲過程實現

SQL> create or replace procedure sp_pro1(name varchar2,v_sal number) is

  2  begin

  3  update emp set sal=v_sal where ename=name;

  4  end;

  5  /

Procedure created.

調用存儲過程，傳遞形參數據

SQL> exec sp_pro1('SMITH',1200);

PL/SQL procedure successfully completed.

查詢改變結果

SQL> select ename,sal from emp where ename='SMITH';

ENAME             SAL

---------- ----------

SMITH            1200

過程用于執行特定的操作，當建立過程時，既可以指定輸入參數（in），也可以指定輸出參數（out）

sp_pro1(name varchar2,pass varchar2)這里面加入的參數默認是in，如果要輸出有返回值的存儲過程必須加上out，看下面例子

給定一個empno雇員號，返回雇員名，編寫一個存儲過程

SQL> create or replace procedure sp_pro1(spno in number,spname out varchar2) is

  2  begin

  3  select ename into spname from emp where empno=spno;

  4  end;

  5  /

Procedure created.

該如何調用了，這里就不能直接exec procedure_name這樣了，調用方式如下

SQL> declare

  2  v_name varchar(20);     定義一個變量，將存儲過程返回出來的值存入到這個變量中

  3  begin

  4  sp_pro1(7788,v_name);

  5  dbms_output.put_line(v_name);   打印變量的值，也就是返回出來的值

  6  end;

  7  /

SCOTT

PL/SQL procedure successfully completed.

什么情況下用exec調用，什么情況下用PLSQL調用存儲過程？

exec適合于調用存儲過程無返回值

plsql適合于調用存儲過程有返回值，不管多少個

函數

過程用于返回特定的數據，當建立函數時，在函數頭必須包含return字句，而在函數體內必須包含return語句

返回的數據，創建函數用create function

SQL> --函數案例

結構：create or replace function return ..is，看下面例子

SQL> create or replace function sp_fun1(name varchar2) return number is

  2  yearsal number(7,2);

  3  begin

  4  select sal*12 into yearsal from emp where ename=name;

  5  return yearsal;   定義需要返回的

  6  end;

  7  /

Function created.

函數創建完成，該如何調用呢？

SQL> declare 

  2  v_sal number(7,2);     定義一個變量用來接收函數返回的值

  3  begin

  4  v_sal:=sp_fun1('SMITH');  將函數返回的值賦值給v_sal，賦值為:=

  5  dbms_output.put_line(v_sal);

  6  end;

  7  /

14400

PL/SQL procedure successfully completed.