PL/SQL学习笔记一

obeckham

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