PL/SQL 程式設計簡介,plsql程式設計簡介
①PL/SQL 程式設計簡介
PL/SQL是一種進階資料庫程式設計語言,該語言專門用於在各種環境下對ORACLE資料庫進行訪問。由於該語言整合於資料庫伺服器中,
所以PL/SQL代碼可以對資料進行快速高效的處理
在PL/SQL中可以使用的SQL語句有:
INSERT,UPDATE,DELETE,SELECT …INTO,COMMIT,ROLLBACK,SAVEPOINT。
提示:在PL/SQL中只能用SQL語句中的DML 部分,不能用DDL 部分,如果要在PL/SQL中使用DDL(如CREATE table 等)的話,只能以動態方式來使用
②PL/SQL塊結構和組成元素
PL/SQL程式由三個塊組成,即聲明部分、執行部分、異常處理部分
PL/SQL塊的結構如下:
DECLARE
/* 聲明部分: 在此聲明PL/SQL用到的變數,類型及遊標,以及局部的預存程序和函數*/
BEGIN
/* 執行部分: 過程及SQL 陳述式, 即程式的主要部分*/
EXCEPTION
/* 執行異常部分: 錯誤處理*/
END;
其中執行部分是必須的。
PL/SQL塊可以分為三類:
1. 無名塊:動態構造,只能執行一次。
2. 子程式:儲存在資料庫中的預存程序、函數及包等。當在資料庫上建立好後可以在其它程式中調用它們。
3. 觸發器:當資料庫發生操作時,會觸發一些事件,從而自動執行相應的程式。
③標識符
PL/SQL程式設計中的標識符定義與SQL 的標識符定義的要求相同。要求和限制有:
標識符名不能超過30字元;
第一個字元必須為字母;
不分大小寫;
不能用’-‘(減號);
不能是SQL保留字。
提示: 一般不要把變數名聲明與表中欄位名完全一樣,如果這樣可能得到不正確的結果
建議的命名方法:
標識符 命名規則 例子
程式變數 V_name V_name
程式常量 C_Name C_company_name
遊標變數 Name_cursor Emp_cursor
異常標識 E_name E_too_many
表類型 Name_table_type Emp_record_type
表 Name_table Emp
記錄類型 Name_record Emp_record
替代變數 P_name P_sal
綁定變數 G_name G_year_sal
④變數類型
除了CHAR、VARCHAR2、BINARY_INTEGER、NUMBER(p,s)、LONG、DATE、BOOLEAN、ROWID、ROWID這些
還有複合類型
ORACLE 在PL/SQL 中除了提供象前面介紹的各種類型外,還提供一種稱為複合類型的類型---記錄和表
1)記錄類型
記錄類型是把邏輯相關的資料作為一個單中繼存放區起來,稱作PL/SQL RECORD 的域(FIELD),其作用是存放互不相同但邏輯相關的資訊。
定義記錄類型文法如下:
TYPE record_type IS RECORD(
Field1 type1 [NOT NULL] [:= exp1 ],
Field2 type2 [NOT NULL] [:= exp2 ],
. . . . . .
Fieldn typen [NOT NULL] [:= expn ] ) ;
例:
1 自訂記錄類型
declare
--定義一個記錄類型
type customer_type is record(
v_cust_name varchar2(20),
v_cust_id number(10));
--聲明自訂記錄類型的變數
v_customer_type customer_type;
begin
v_customer_type.v_cust_name := '劉德華';
v_customer_type.v_cust_id := 1001;
dbms_output.put_line(v_customer_type.v_cust_name||','||v_customer_type.v_cust_id);
end;
2 自訂記錄類型
declare
--定義一個記錄類型
type emp_record is record(
v_name varchar2(25),
v_email varchar2(25),
v_salary number(8, 2),
v_job_id varchar2(10));
--聲明自訂記錄類型的變數
v_emp_record emp_record;
begin
--通過 select ... into ... 語句為變數賦值
select last_name, email, salary, job_id into v_emp_record
from employees
where employee_id = 186;
-- 列印變數的值
dbms_output.put_line(v_emp_record.v_name || ', ' || v_emp_record.v_email || ', ' ||
v_emp_record.v_salary || ', ' || v_emp_record.v_job_id);
end;
4. 使用 %type 定義變數,動態擷取資料的宣告類型
declare
--定義一個記錄類型
type emp_record is record(
v_name employees.last_name%type,
v_email employees.email%type,
v_salary employees.salary%type,
v_job_id employees.job_id%type);
--聲明自訂記錄類型的變數
v_emp_record emp_record;
begin
--通過 select ... into ... 語句為變數賦值
select last_name, email, salary, job_id into v_emp_record
from employees
where employee_id = 186;
-- 列印變數的值
dbms_output.put_line(v_emp_record.v_name || ', ' || v_emp_record.v_email || ', ' ||
v_emp_record.v_salary || ', ' || v_emp_record.v_job_id);
end;
5. 使用 %rowtype
declare
--聲明一個記錄類型的變數
v_emp_record employees%rowtype;
begin
--通過 select ... into ... 語句為變數賦值
select * into v_emp_record
from employees
where employee_id = 186;
-- 列印變數的值
dbms_output.put_line(v_emp_record.last_name || ', ' || v_emp_record.email || ', ' ||
v_emp_record.salary || ', ' || v_emp_record.job_id || ', ' ||
v_emp_record.hire_date);
end;
2)PL/SQL 表(巢狀表格)
PL/SQL 程式可使用巢狀表格類型建立具有一個或多個列和無限行的變數, 這很像資料庫中的表. 聲明巢狀表格類型的一般文法如下:
TYPE type_name IS TABLE OF
{datatype | {variable | table.column} % type | table%rowtype};
INDEX BY BINARY_INTEGER ;
文法說明如下:
--table_name 建立的表類型名稱。
--IS TABLE 表示建立的是表類型。
--datatype 可以是任何合法的PL/SQL資料類型,例如varchar2。
--INDEX BY BINARY_INTEGER 指定系統建立一個主鍵索引,用於參考資料表類型變數中的特定行。
declare
type my_record_type is record(
v_id varchar2(32),
v_name varchar2(100)
);
type my_table_type is table of my_record_type
index by BINARY_INTEGER;
begin
dbms_output.put_line('11111111111111');
end;
方法 描述
EXISTS(n):Return TRUE if the nth element in a PL/SQL table exists;
COUNT:Returns the number of elements that a PL/SQL table currently contains;
FIRST\LAST:Return the first and last (smallest and lastest) index numbers in a PL/SQL table. Returns NULL if the PL/SQL table is empty.
PRIOR(n):Returns the index number that precedes index n in a PL/SQL table;
NEXT(N):Returns the index number that succeeds index n in a PL/SQL table;
TRIM:TRIM removes one element from the end of a PL/SQL table.
TRIM(n):removes n element from the end of a PL/SQL table.
DELETE:DELETE removes all elements from a PL/SQL table.
DELETE(n):removes the nth elements from a PL/SQL table.
DELETE(m, n):removes all elements in the range m to n from a PL/SQL table.
⑤變數賦值
variable := expression;
variable 是一個PL/SQL變數, expression 是一個PL/SQL 運算式.
⑥字元及數字運算特點
空值加數字仍是空值:NULL + < 數字> = NULL
空值加(串連)字元,結果為字元:NULL || <字串> = < 字串>
⑦可轉換的類型賦值
CHAR 轉換為NUMBER:
使用TO_NUMBER 函數來完成字元到數位轉換,如:
v_total := TO_NUMBER(‘100.0’) + sal;
NUMBER 轉換為CHAR:
使用TO_CHAR函數可以實現數字到字元的轉換,如:
v_comm := TO_CHAR(‘123.45’) || ’元’ ;
字元轉換為日期:
使用TO_DATE函數可以實現字元到日期的轉換,如:
v_date := TO_DATE('2001.07.03','yyyy.mm.dd');
日期轉換為字元
使用TO_CHAR函數可以實現日期到字元的轉換,如:
v_to_day := TO_CHAR(SYSDATE, 'yyyy.mm.dd hh24:mi:ss') ;
⑧注釋
在PL/SQL裡,可以使用兩種符號來寫注釋,即:
使用雙‘-‘ ( 減號) 加註釋:PL/SQL允許用–-來寫注釋,它的作用範圍是只能在一行有效
使用/* */ 來加一行或多行注釋:它的作用範圍在多行有效
提示:被解釋存放在資料庫中的PL/SQL 程式,一般系統自動將程式頭部的注釋去掉。
只有在PROCEDURE 之後的注釋才被保留;另外程式中的空行也自動被去掉。