PL/SQL学习笔记一

 

PL/SQL学习笔记一

1、 PL/SQL的塊結構

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;

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

如下

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、 錯誤處理

/**錯誤處理開始**/

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、變量及類型

自帶基本類型

用戶自定義類型:

              Declare

       type t_StudentRecord is record (

           FirstName varchar2 ( 50 );

           LastName varchar2 ( 50 );

           CurrentCredits number ( 3 );

       );

定義 :v_Student t_StudentRecord ;

4、過程

1)這里先介紹一個錯誤過程，也是很多初學者都會犯的問題

錯誤過程 1:

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:

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:

create or replace procedure addNewColumn

as

    firstID int : = 10140 ;

    beginMark int : = 65 ;

    loopTime int : = 1 ;

begin

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

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)過程的基本結構:

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) 一個完整的過程

/*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) 執行存儲過程

       begin

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

end ;

       而不是像在SQL SERVER中的Call來調用

5、函數

       --

6、包

1) 寫的第一個包 , 花了不少的檢查時間 ( 有點類似于 JAVA 中的接口 )

表的結構如下圖 :

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 ;

 

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 ;

   

    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) 執行 :

    begin

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

end ;

3) 包內過程、方法可以重載

4) 包的初使化

         當第一次調用打包程序時，該包將進行初使化。也就是說將該包從硬盤讀入到內存并啟動調用的子程序的編譯代碼開始運行。這時，系統為該包中定義的所有變量分配內存單元。每個會話都有其打包變量的副本，以確保一包子程序的兩個對話使用不同的內存單元。

         在大多數情況下，初使化代碼要在包第一次初使化時運行。為了實現這種功能，我們可以在包體中所有對象之后加入一個初使化部分，其語法如下 :

         CREATE OR REPLACE PACEAGE BODY package_name (IS|AS)

                   . . .

         BEGIN

                   Initialization_code;

         END [package_name]

  

7、取當前系統時間

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

8、更改表結構

       ALTER TABLE STU MODIFY FIRST_NAME VARCHAR ( 100 );

       注: 一定是 MODIFY 而不是 sql server 中的 ALTER

本文出自：冯立彬的博客