1.?? 簡介

1.1 ??? 結構

DECLARE -- 定義

BEGIN? -- 執行部分

EXCEPTION? -- 例外處理

END;? -- 結束

?

set serveroutput on;
DECLARE v_ename VARCHAR2( 5 );
BEGIN
SELECT ename INTO v_ename FROM emp WHERE empno=&no;
dbms_output.put_line( '1í?±??:' || v_ename);
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line( 'please input correct employees no!' );
END;
/

?

1.2 ??? 塊分類

<<outer>> <<inner>>

1.3 ??? 子程序

1.1.1 ????? 過程

執行特定的操作

? CREATE or replace PROCEDURE update_sal(nameVARCHAR2,newsal NUMBER)
IS
BEGIN
???? UPDATE emp SET sal=newsal WHERE lower(ename)=lower(name);
END;
/

調用：

exec update_sal( 'SMITH' , 10 )
call update_sal( 'SMITH' , 800 )

1.1.2 ????? 函數

返回特定數據

CREATE or replace FUNCTION annual_income(nameVARCHAR2)
RETURNNUMBERIS
?????? annual_salary NUMBER( 7 , 2 );
BEGIN
SELECT sal* 12 + nvl(comm, 0 )? into annual_salary FROM emp WHERE lower(ename)=lower(name);
RETURN annual_salary;
END;
/

調用 :

SQL> VAR income NUMBER

SQL> CALL annual_income('scott') INTO : income;

調用完成。

SQL> print income

??? INCOME

----------

???? 36000

1.1.3 ????? 包

邏輯組合相關的過程和函數

-- 包規范
CREATE or replace PACKAGE emp_pkg IS
? PROCEDURE update_sal(nameVARCHAR2, newsal NUMBER);
? FUNCTION annual_income(nameVARCHAR2) RETURNNUMBER;
END;

-- 包體
CREATE or replace PACKAGE BODY emp_pkg IS
?????? PROCEDURE update_sal(nameVARCHAR2, newsal NUMBER)
?????? IS
?????? BEGIN
??????????? UPDATE emp SET sal = newsal WHERE lower(ename) = lower(name);
?????? END;
??????
?????? FUNCTION annual_income(nameVARCHAR2) RETURNNUMBER
?????? IS
???????? annual_salary NUMBER( 7 , 2 );
?????? BEGIN
??????????? SELECT sal * 12 + nvl(comm, 0 ) into annual_salary FROM emp WHERE lower(ename) = lower(name);
??????????? RETURN annual_salary;
?????? END;
END;
/

調用：

SQL> call emp_pkg .update_sal('SMITH',1500);

Or

SQL> VAR income NUMBER

SQL> CALL emp_pkg . annual_income('scott') INTO : income;

調用完成。

SQL> print income

??? INCOME

----------

???? 36000

1.4 ??? 觸發器

???? 是隱含執行的存儲過程。

create or replace trigger update_cascade
?afterupdateof deptno on dept
?foreachrow
begin
???? update emp set deptno=:new.deptno
???? where deptno=:old.deptno;
end;
/????

2.?? 定義并使用變量

1.5 ??? 標量變量

1.1.4 ????? 特殊變量說明

LONG(32760 字節 ) 與 VARCHAR2(32767 字節 ) 類似，定義變長的字符串

LONG RAW 用于定義變長的二進制數據 (32760 字節 )

BINARY_INTEGER 定義整數，范圍為： -2147483647~2147483647 ( 非表列使用 )

BOOLEAN 為 TRUE/FALSE/NULL ( 非表列使用 )

BINARY_FLOAT/BINARY_DOUBLE ： ORACLE10 所有

1.1.5 ????? 定義使用

Identifier [CONSTANT] datatype [not null] [:= | default expr]

例如： v_valid BOOLEAN NOT NULL DEFAULT FALSE;

C_tax_rate CONSTANT NUMBER(3,2):=0.03;

V_ename emp.ename%TYPE;

1.6 ??? 復合變量

1.1.6 ????? Pl/sql 記錄

類似于高級語言中的結構

DECLARE
TYPE emp_record_type ISRECORD(
??? name emp.ename%TYPE,
??? salary emp.sal%TYPE,
? title emp.job%TYPE
);
emp_record emp_record_type;
BEGIN
???? SELECT ename,sal,job INTO emp_record FROM emp WHERE empno= 7788 ;
???? dbms_output.put_line( ' 雇員名 ' ||emp_record.name);
end;
/????

1.1.7 ????? Pl/sql 表

類似于高級語言中的數組，下標可以為負 , 個數無限制。

DECLARE
TYPE ename_table_type ISTABLEOF emp.ename%TYPE
???? INDEXBYBINARY_INTEGER;
???? ename_table ename_table_type;
BEGIN
???? SELECT ename? INTO ename_table(- 1 ) FROM emp WHERE empno= 7788 ;
???? dbms_output.put_line( ' 雇員名 ' ||ename_table(- 1 ));
end;
/????

1.1.8 ????? 嵌套表

類似于高級語言中的數組，下標不可以為負，個數無限制。

CREATE OR REPLACE TYPE emp_type? ASOBJECT(
?????? nameVARCHAR2( 10 ),
?????? salary NUMBER( 6 , 2 ),
?????? hiredate DATE
);
/
CREATEORREPLACETYPE emp_array ISTABLEOF emp_type;
/

CREATEORREPLACEtable department(
?????? deptno NUMBER( 2 ),
?????? dname VARCHAR2( 10 ),
?????? employee emp_array??????
)nestedtable employee storeas employee;

?

1.1.9 ????? VARRAY

VARRAY 類似于嵌套表，它可以作為表列和對象類型屬性的數據類型，個數有限制。

CREATE OR REPLACE TYPE article_type? ASOBJECT(
?????? title VARCHAR2( 30 ),
?????? pubdate DATE
);
/
CREATEORREPLACETYPE article_array ISVARRAY( 20 ) OF article_type;
/

CREATEORREPLACEtable author(
?????? idNUMBER( 6 ),
?????? nameVARCHAR2( 10 ),
? ?????article article_array??????
);

1.7 ??? 參照變量

用于存放數值指針的變量。使得應用程序共享相同對象，從而降低占用空間。

1.1.10 REF CURSOR

游標變量

DECLARE
? TYPE c1 ISREFCURSOR;
? emp_cursor c1;
? v_ename emp.ename%TYPE;
? v_sal?? emp.sal%TYPE;
BEGIN
? OPEN emp_cursor FOR
??? SELECT ename, sal FROM emp ; --WHERE deptno = 10;
? LOOP
??? FETCH emp_cursor
????? INTO v_ename, v_sal;
??? EXITWHEN emp_cursor%NOTFOUND;
??? dbms_output.put_line(v_ename);
? ENDLOOP;
? CLOSE emp_cursor;
END;
/

1.1.11 REF obj_type

為了共享相同對象，可以用 ref 引用對象類型。

CREATE OR REPLACE TYPE home_type AS OBJECT(

?????? street VARCHAR2(50),city VARCHAR2(20),

?????? state VARCHAR2(20),zipcode VARCHAR2(6),

?????? owner VARCHAR2(10)

);

/

CREATE TABLE homes OF home_type;

INSERT INTO homes VALUES(' 呼倫北路 12 號 ',' 呼和浩特 ',' 內蒙 ','010010',' 馬鳴 ');

INSERT INTO homes VALUES(' 呼倫北路 13 號 ',' 呼和浩特 ',' 內蒙 ','010010',' 秦斌 ');

CREATE TABLE person(

?????? id NUMBER(6) PRIMARY KEY,

?????? name VARCHAR2(10), addr REF home_type

);

INSERT INTO? person SELECT 1,' 馬鳴 ',ref(p) FROM homes p WHERE p.owner=' 馬鳴 ';

1.8 ??? LOB 變量

內部 : CLOB 、 BLOB 、 NCLOB ；外部： BFILE 。

1.9 ??? 非 PL/SQL 變量

1.1.12 使用 sql*plus 變量

var namevarchar2( 10 )
begin
???? select ename into :namefrom emp
???? where empno= 7788 ;
end;
/
print name??

?

1.1.13 使用 procedure Builder 變量

.createcharname length 10
begin
???? select ename into :namefrom emp
???? where empno= 7788 ;
end;
/
text_to.put_line(:name);??

1.1.14 使用 pro*c/c++ 變量

char name[ 10 ];
execsqlexecute
begin
???? select ename into :namefrom emp
???? where empno= 7788 ;
end-exec;
printf( ' 雇員名： %s\n' ,name);