PL/SQL学习笔记一

1、 PL/SQL的塊結構

Sql代码

1. Declare  
2.         /*   
3. Declare section-PL/SQL variables,types,cursors,and local subprograms go here.   
4.         */   
5. Begin  
6.         /*   
7.                Executable section-procedural and SQL statements go here.   
8.                This is ths main section of the bloack and the only one that is required.   
9.         */   
10. Exception   
11.         /*   
12.                Exception-handling section-error-handing statements go here   
13. */   
14. End;  

Declare
        /*
Declare section-PL/SQL variables,types,cursors,and local subprograms go here.
        */
Begin
        /*
               Executable section-procedural and SQL statements go here.
               This is ths main section of the bloack and the only one that is required.
        */
Exception
        /*
               Exception-handling section-error-handing statements go here
*/
End;

注: 每個塊的結尾都要用分號結束
如下

Sql代码

1. declare  
2.     fmajor varchar(50):='Chemistary';   
3.     ffirstName varchar(50):='Fff';   
4.     flastName varchar(50):='LB';   
5. begin  
6.     update students    
7.     set major=fmajor   
8.     where first_name=ffirstName   
9.     and last_name=flastName;   
10.        
11.     if SQL%NOTFOUND THEN  
12.         insert into students   
13.         values(student_sequence.NEXTVAL,ffirstName,flastName,fmajor);   
14.     end if;   
15. end;   
16.    

declare
    fmajor varchar(50):='Chemistary';
    ffirstName varchar(50):='Fff';
    flastName varchar(50):='LB';
begin
    update students 
    set major=fmajor
    where first_name=ffirstName
    and last_name=flastName;
    
    if SQL%NOTFOUND THEN
        insert into students
        values(student_sequence.NEXTVAL,ffirstName,flastName,fmajor);
    end if;
end;
 

2、 錯誤處理

Sql代码

1. /**錯誤處理開始**/   
2. Declare  
3.     v_ErrorCode number;         --code for the error   
4.     v_ErrorMsg varchar2(200);   --Message text for the error   
5.     v_CurrentUser varchar2(20) --Current database user   
6.     v_Information varchar2(100) --Information about the error   
7. Begin  
8.     /*Code that processes some data here*/   
9.     
10. Exception   
11.     when others then  
12.        --Assign values to the log variables,using built-in function   
13.        v_ErrorCode:=SQLCODE;   
14.        v_ErrorMsg:=SQLERRM;   
15.        v_currentUser:=USER;   
16.        V_Information:='Error encountered on' || to_char(SYSDATE) || ' by database user' || v_CurrentUser;   
17.           
18.        --Insert the log message into log_table.   
19.        Insert into log_table(code,message,infro) values(v_ErrorCode,v_ErrorMsg,v_ErrorInfomation);   
20. end;   
21. /**錯誤處理結束**/  

/**錯誤處理開始**/
Declare
    v_ErrorCode number;         --code for the error
    v_ErrorMsg varchar2(200);   --Message text for the error
    v_CurrentUser varchar2(20) --Current database user
    v_Information varchar2(100) --Information about the error
Begin
    /*Code that processes some data here*/
 
Exception
    when others then
       --Assign values to the log variables,using built-in function
       v_ErrorCode:=SQLCODE;
       v_ErrorMsg:=SQLERRM;
       v_currentUser:=USER;
       V_Information:='Error encountered on' || to_char(SYSDATE) || ' by database user' || v_CurrentUser;
       
       --Insert the log message into log_table.
       Insert into log_table(code,message,infro) values(v_ErrorCode,v_ErrorMsg,v_ErrorInfomation);
end;
/**錯誤處理結束**/

3、變量及類型
自帶基本類型
用戶自定義類型:
           

Sql代码

1. Declare  
2.      type t_StudentRecord is record(   
3.          FirstName varchar2(50);   
4.          LastName varchar2(50);   
5.          CurrentCredits number(3);   
6.      );  

  Declare
       type t_StudentRecord is record(
           FirstName varchar2(50);
           LastName varchar2(50);
           CurrentCredits number(3);
       );

定義:v_Student t_StudentRecord;
4、過程
1)這里先介紹一個錯誤過程，也是很多初學者都會犯的問題
錯誤過程 1:

Sql代码

1. CREATE or replace PROCEDURE changOrInsert AS  
2. Declare              --<出錯誤之處。原因，受塊結構的影響>   
3.     fmajor students.major%type:='Chemistary';   
4.     ffirstName students.first_name%type:='Fff';   
5.     flastName students.last_name%type:='LB';   
6. begin  
7.     update students set major=fmajor where first_name=ffirstName and last_name=flastName;      
8. end changOrInsert;    

CREATE or replace PROCEDURE changOrInsert AS
Declare              --<出錯誤之處。原因，受塊結構的影響>
    fmajor students.major%type:='Chemistary';
    ffirstName students.first_name%type:='Fff';
    flastName students.last_name%type:='LB';
begin
    update students set major=fmajor where first_name=ffirstName and last_name=flastName;   
end changOrInsert;  

   
錯誤過程 2:

Sql代码

1. CREATE or replace PROCEDURE changOrInsert AS  
2.     fmajor students.major%type:='Chemistary';   
3.     ffirstName students.first_name%type:='Fff';   
4.     flastName students.last_name%type:='LB';   
5. begin  
6.     update students set major=fmajor where first_name=ffirstName and last_name=flastName;       
7.     if SQL%NOTFOUND THEN  
8.         insert into students   
9.         values(student_sequence.NEXTVAL,ffirstName,flastName,fmajor);   
10.     end if;   
11. exception    
12.     ;             --<問題處。原因: 受其它開發語言如JAVA的影響>   
13. end changOrInsert;  

CREATE or replace PROCEDURE changOrInsert AS
    fmajor students.major%type:='Chemistary';
    ffirstName students.first_name%type:='Fff';
    flastName students.last_name%type:='LB';
begin
    update students set major=fmajor where first_name=ffirstName and last_name=flastName;    
    if SQL%NOTFOUND THEN
        insert into students
        values(student_sequence.NEXTVAL,ffirstName,flastName,fmajor);
    end if;
exception 
    ;             --<問題處。原因: 受其它開發語言如JAVA的影響>
end changOrInsert;

錯誤過程3:

Sql代码

1. create or replace procedure addNewColumn   
2. as  
3.     firstID int:=10140;   
4.     beginMark int:=65;   
5.     loopTime int:=1;   
6. begin  

create or replace procedure addNewColumn
as
    firstID int:=10140;
    beginMark int:=65;
    loopTime int:=1;
begin

--仔細看下面這一條詔句。沒錯呀! 真的嗎?( 就錯在這里)

Java代码

1. alter table students add mark int;   
2. --execute immediate 'alter table students add mark1 int';( 這句才是對的)   
3.     loop   
4.        update students set mark=beginMark where id=firstID;   
5.        firstID:=firstID+1;   
6.        beginMark:=beginMark+1;   
7.        loopTime:=loopTime+1;   
8.        exit when loopTime>20;   
9.     end loop;   
10. end addNewColumn;  

alter table students add mark int;
--execute immediate 'alter table students add mark1 int';( 這句才是對的)
    loop
       update students set mark=beginMark where id=firstID;
       firstID:=firstID+1;
       beginMark:=beginMark+1;
       loopTime:=loopTime+1;
       exit when loopTime>20;
    end loop;
end addNewColumn;

2)過程的基本結構:

Sql代码

1. Create [or replace] procedure procedure_name [parameter_list] as  
2.     /*Declarative section is here*/   
3. Begin  
4.     /*Executable section is here*/    
5. Exception   
6.     /*Exception is here*/   
7. End [procedure_name];  

Create [or replace] procedure procedure_name [parameter_list] as
    /*Declarative section is here*/
Begin
    /*Executable section is here*/ 
Exception
    /*Exception is here*/
End [procedure_name];

3) 一個完整的過程

Sql代码

1. /*Create a Procedure*/   
2. CREATE or replace PROCEDURE changOrInsert(   
3.     fmajor students.major%type,   
4.     ffirstName students.first_name%type,   
5.     flastName students.last_name%type) AS  
6.        
7.     v_ErrorCode number;   
8.     v_ErrorMsg varchar(200);   
9.     v_currentUser varchar(50);   
10.     v_Information varchar(500);   
11. begin  
12.     update students set major=fmajor where first_name=ffirstName and last_name=flastName;   
13.        
14.     if SQL%NOTFOUND THEN     --if not found the record   
15.         insert into students --then insert a new one   
16.         values(student_sequence.NEXTVAL,ffirstName,flastName,fmajor);   
17.     end if;   
18. exception    
19.     --write exception into table log_table   
20.     when others then  
21.        --Assign values to the log variables,using built-in function   
22.        v_ErrorCode:=SQLCODE;   
23.        v_ErrorMsg:=SQLERRM;   
24.        v_currentUser:=USER;   
25.        v_Information:='Error encountered on' || to_char(SYSDATE) || ' by database user ' || v_CurrentUser;          
26.        --Insert the log message into log_table.   
27.        Insert into log_table(code,message,info) values(v_ErrorCode,v_ErrorMsg,v_Information);   
28. end changOrInsert;  

/*Create a Procedure*/
CREATE or replace PROCEDURE changOrInsert(
    fmajor students.major%type,
    ffirstName students.first_name%type,
    flastName students.last_name%type) AS
    
    v_ErrorCode number;
    v_ErrorMsg varchar(200);
    v_currentUser varchar(50);
    v_Information varchar(500);
begin
    update students set major=fmajor where first_name=ffirstName and last_name=flastName;
    
    if SQL%NOTFOUND THEN     --if not found the record
        insert into students --then insert a new one
        values(student_sequence.NEXTVAL,ffirstName,flastName,fmajor);
    end if;
exception 
    --write exception into table log_table
    when others then
       --Assign values to the log variables,using built-in function
       v_ErrorCode:=SQLCODE;
       v_ErrorMsg:=SQLERRM;
       v_currentUser:=USER;
       v_Information:='Error encountered on' || to_char(SYSDATE) || ' by database user ' || v_CurrentUser;       
       --Insert the log message into log_table.
       Insert into log_table(code,message,info) values(v_ErrorCode,v_ErrorMsg,v_Information);
end changOrInsert;

4) 執行存儲過程

Sql代码

1.        begin  
2.     changOrInsert('Feng','LiBin','Computer');   
3. end;  

       begin
    changOrInsert('Feng','LiBin','Computer');
end;

       而不是像在SQL SERVER中的Call來調用
5、函數
       --
6、包
1)寫的第一個包, 花了不少的檢查時間(有點類似于JAVA中的接口)
表的結構如下圖:

Sql代码

1. create or replace package manageStu as  
2.     
3.     procedure addStu(firstname students.first_name%type,   
4.                    lastname students.last_name%type,   
5.                    majorm students.major%type,   
6.                    current_credits students.current_credits%type,   
7.                    markm students.mark%type);   
8.     procedure delStu(stuID int);   
9.        
10.     procedure modifyStu(idm int,   
11.                      firstname students.first_name%type,   
12.                       lastname students.last_name%type,   
13.                       majorm students.major%type,   
14.                       current_creditsm students.current_credits%type,   
15.                       markm students.major%type);   
16.     
17. end manageStu;  

create or replace package manageStu as
 
    procedure addStu(firstname students.first_name%type,
                   lastname students.last_name%type,
                   majorm students.major%type,
                   current_credits students.current_credits%type,
                   markm students.mark%type);
    procedure delStu(stuID int);
    
    procedure modifyStu(idm int,
                     firstname students.first_name%type,
                      lastname students.last_name%type,
                      majorm students.major%type,
                      current_creditsm students.current_credits%type,
                      markm students.major%type);
 
end manageStu;

 

Sql代码

1. create or replace package body manageStu as  
2.     
3.     procedure addStu(firstname students.first_name%type,   
4.                    lastname students.last_name%type,   
5.                    majorm students.major%type,   
6.                    current_credits students.current_credits%type,   
7.                    markm students.mark%type)   
8.     as  
9.     begin  
10.        insert into students values(student_sequence.NEXTVAL,   
11.                                 firstname,   
12.                                 lastname,   
13.                                 majorm,   
14.                                 current_credits,   
15.                                 markm);   
16.     end addStu;  

create or replace package body manageStu as
 
    procedure addStu(firstname students.first_name%type,
                   lastname students.last_name%type,
                   majorm students.major%type,
                   current_credits students.current_credits%type,
                   markm students.mark%type)
    as
    begin
       insert into students values(student_sequence.NEXTVAL,
                                firstname,
                                lastname,
                                majorm,
                                current_credits,
                                markm);
    end addStu;

   

Sql代码

1. procedure delStu(stuID int)   
2. as  
3. begin  
4.    delete students where id=stuID;   
5. end delStu;   
6.   
7. procedure modifyStu(idm int,   
8.                  firstname students.first_name%type,   
9.                   lastname students.last_name%type,   
10.                   majorm students.major%type,   
11.                   current_creditsm students.current_credits%type,   
12.                   markm students.major%type)   
13. as  
14. begin  
15.    update students set    
16.    first_name=firstname,   
17.    last_name=lastname,   
18.    major=majorm,   
19.    current_credits=current_creditsm,   
20.    mark=markm    
21.    where id=idm;   
22. end modifyStu;   
23.   
24. manageStu;  

    procedure delStu(stuID int)
    as
    begin
       delete students where id=stuID;
    end delStu;
    
    procedure modifyStu(idm int,
                     firstname students.first_name%type,
                      lastname students.last_name%type,
                      majorm students.major%type,
                      current_creditsm students.current_credits%type,
                      markm students.major%type)
    as
    begin
       update students set 
       first_name=firstname,
       last_name=lastname,
       major=majorm,
       current_credits=current_creditsm,
       mark=markm 
       where id=idm;
    end modifyStu;
 
end manageStu;

2)執行:

Sql代码

1.     begin  
2.     manageStu.addstu('F1','LB','computer',12,90);   
3. end;  

    begin
    manageStu.addstu('F1','LB','computer',12,90);
end;

3) 包內過程、方法可以重載
4) 包的初使化
         當第一次調用打包程序時，該包將進行初使化。也就是說將該包從硬盤讀入到內存并啟動調用的子程序的編譯代碼開始運行。這時，系統為該包中定義的所有變量分配內存單元。每個會話都有其打包變量的副本，以確保一包子程序的兩個對話使用不同的內存單元。
         在大多數情況下，初使化代碼要在包第一次初使化時運行。為了實現這種功能，我們可以在包體中所有對象之后加入一個初使化部分，其語法如下:

Sql代码

1. CREATE OR REPLACE PACEAGE BODY package_name (IS|AS)   
2.           . . .   
3. BEGIN  
4.           Initialization_code;   
5. END [package_name]  

         CREATE OR REPLACE PACEAGE BODY package_name (IS|AS)
                   . . .
         BEGIN
                   Initialization_code;
         END [package_name]

  

7、取當前系統時間

Sql代码

1. SELECT TO_CHAR(SYSDATE,'SSSSS') FROM sys.dual;  

       SELECT TO_CHAR(SYSDATE,'SSSSS') FROM sys.dual;

8、更改表結構
 

Sql代码

1. ALTER TABLE STU MODIFY FIRST_NAME VARCHAR(100);