pl/sql編程基礎(chǔ)

PL/SQL

1、過(guò)程、函數(shù)、觸發(fā)器是pl/sql編寫(xiě)的

2、過(guò)程、函數(shù)、觸發(fā)器是存放在oracle數(shù)據(jù)庫(kù)中的

3、pl/sql是非常強(qiáng)大的過(guò)程化語(yǔ)言

4、過(guò)程、函數(shù)、觸發(fā)器可以在java程序中調(diào)用

pl/sql編寫(xiě)能節(jié)省一點(diǎn)時(shí)間就是提高了性能（量大），java直接調(diào)用數(shù)據(jù)庫(kù)存放的過(guò)程，解析時(shí)間就節(jié)省下來(lái)了，提高了性能

模塊化的設(shè)計(jì)思想-----》存儲(chǔ)過(guò)程

網(wǎng)絡(luò)傳輸（java程序中編寫(xiě)的sql語(yǔ)言），直接調(diào)用數(shù)據(jù)庫(kù)的過(guò)程節(jié)省了傳輸量

提高安全性（存儲(chǔ)過(guò)程避免了數(shù)據(jù)庫(kù)信息的泄漏）

缺點(diǎn)：

移植性不好

pl/sql編程基本單位是塊，通過(guò)塊可以編寫(xiě)出過(guò)程、函數(shù)、觸發(fā)器、包

下面進(jìn)行一個(gè)最基本的編程

案例：向某表中插入一條數(shù)據(jù)

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

begin

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

end;

/

create procedure創(chuàng)建存儲(chǔ)過(guò)程關(guān)鍵字

or repalce：表示如果名稱(chēng)已存在，就替換

is：也是關(guān)鍵字

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

begin：關(guān)鍵字

end：結(jié)束符

begin與end結(jié)束之間就是執(zhí)行部分

上面一個(gè)簡(jiǎn)單的存儲(chǔ)過(guò)程是向某表中添加一條數(shù)據(jù)，現(xiàn)在先創(chuàng)建一張表

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

Table created.

然后通過(guò)編寫(xiě)一個(gè)存儲(chǔ)過(guò)程向其中添加數(shù)據(jù)

SQL> create or replace procedure sp_pro1 is

  2  begin

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

  4  end;

  5  /

Procedure created.

存儲(chǔ)過(guò)程已建立，該如何執(zhí)行呢？使用關(guān)鍵字exec或者call，如下

SQL> exec sp_pro1;

PL/SQL procedure successfully completed.

然后查詢表，看是否添加了數(shù)據(jù)

SQL> select * from names;

NAME                 PASSWORD

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

xiaoming             redhat

當(dāng)若編寫(xiě)的過(guò)程有誤時(shí)，可以通過(guò)show error這條命令查看錯(cuò)誤具體信息

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

pl/sql塊由三個(gè)部分組成：定義部分、執(zhí)行部分、例外處理部分

declare

  定義部分，定義常量、變量、游標(biāo)、例外、復(fù)雜數(shù)據(jù)類(lèi)型  

begin

執(zhí)行部分，要執(zhí)行的pl/sql語(yǔ)句和sql語(yǔ)句

exception

例外處理部分，處理運(yùn)行的各種錯(cuò)誤

定義部分是從declare開(kāi)始的，可選

執(zhí)行部分從begin開(kāi)始，必選

例外處理部分從exception，可選

下面編寫(xiě)一條最簡(jiǎn)單的塊，輸出hello world

編寫(xiě)之前打開(kāi)系統(tǒng)的屏幕輸出信息，不然看不到效果

SQL> set serveroutput on;

SQL> begin

  2  dbms_output.put_line('hello world');

  3  end;

  4  /

hello world

PL/SQL procedure successfully completed.

最簡(jiǎn)單的塊編程，只有執(zhí)行部分，而且只輸出了一條信息hello world。

相關(guān)說(shuō)明：dbms_output是oracle所提供的包（類(lèi)似java的開(kāi)發(fā)包），該包包含一些過(guò)程，put_line就是

dbms_output包的一個(gè)過(guò)程（包里面的一個(gè)過(guò)程）

實(shí)例2：包含定義部分和執(zhí)行部分

SQL> declare 

  2  v_name varchar2(20);    改行表示定義的變量，變量名v_name，數(shù)據(jù)類(lèi)型為varchar2

  3  begin

  4  select ename into v_name from emp where empno=&empno;  &由鍵盤(pán)輸出

  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.

實(shí)例3：包含定義部分、執(zhí)行部分和例外處理部分

為了避免pl/sql程序的運(yùn)行錯(cuò)誤，提高pl/sql的健壯性，應(yīng)該對(duì)可能的錯(cuò)誤進(jìn)行處理

相關(guān)說(shuō)明：oracle事先預(yù)定義了一些例外，no_data_found就是找不到數(shù)據(jù)的例外

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

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             定義例外關(guān)鍵字exception

  8  when no_data_found then    當(dāng)查詢不到數(shù)據(jù)時(shí)，采取措施打印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

過(guò)程

過(guò)程用于執(zhí)行特定的操作，當(dāng)建立過(guò)程時(shí)，既可以指定輸入?yún)?shù)（in），也可以指定輸出參數(shù)（out）。通過(guò)在過(guò)程中使用輸入?yún)?shù)可以將數(shù)據(jù)傳遞帶執(zhí)行部分；通過(guò)使用輸出參數(shù)，可以將執(zhí)行部分的數(shù)據(jù)傳遞到應(yīng)用環(huán)境。在sqlplus中使用create procedure 命令來(lái)建立過(guò)程

SQL> --過(guò)程編寫(xiě)

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)：這里面的參數(shù)相當(dāng)于編程里面的形參，傳遞的數(shù)據(jù)

然后調(diào)用該存儲(chǔ)過(guò)程

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

PL/SQL procedure successfully completed.

然后查詢數(shù)據(jù)是否已經(jīng)插入了

SQL> select * from names;

NAME                 PASSWORD

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

xiaoming             redhat

xiaobai              redhat

修改表emp的雇員為smith的薪水，編寫(xiě)存儲(chǔ)過(guò)程實(shí)現(xiàn)

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.

調(diào)用存儲(chǔ)過(guò)程，傳遞形參數(shù)據(jù)

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

PL/SQL procedure successfully completed.

查詢改變結(jié)果

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

ENAME             SAL

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

SMITH            1200

過(guò)程用于執(zhí)行特定的操作，當(dāng)建立過(guò)程時(shí)，既可以指定輸入?yún)?shù)（in），也可以指定輸出參數(shù)（out）

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

給定一個(gè)empno雇員號(hào)，返回雇員名，編寫(xiě)一個(gè)存儲(chǔ)過(guò)程

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.

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

SQL> declare

  2  v_name varchar(20);     定義一個(gè)變量，將存儲(chǔ)過(guò)程返回出來(lái)的值存入到這個(gè)變量中

  3  begin

  4  sp_pro1(7788,v_name);

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

  6  end;

  7  /

SCOTT

PL/SQL procedure successfully completed.

什么情況下用exec調(diào)用，什么情況下用PLSQL調(diào)用存儲(chǔ)過(guò)程？

exec適合于調(diào)用存儲(chǔ)過(guò)程無(wú)返回值

plsql適合于調(diào)用存儲(chǔ)過(guò)程有返回值，不管多少個(gè)

函數(shù)

過(guò)程用于返回特定的數(shù)據(jù)，當(dāng)建立函數(shù)時(shí)，在函數(shù)頭必須包含return字句，而在函數(shù)體內(nèi)必須包含return語(yǔ)句

返回的數(shù)據(jù)，創(chuàng)建函數(shù)用create function

SQL> --函數(shù)案例

結(jié)構(gòu)：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.

函數(shù)創(chuàng)建完成，該如何調(diào)用呢？

SQL> declare 

  2  v_sal number(7,2);     定義一個(gè)變量用來(lái)接收函數(shù)返回的值

  3  begin

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

  5  dbms_output.put_line(v_sal);

  6  end;

  7  /

14400

PL/SQL procedure successfully completed.