1.首先創建一個CUSTOMER 表:
create table customer(
customerID varchar2(10) primary key,
customerName varchar2(20),
custoemrSex varchar2(8),
custoemrAge int );
2.插入四行數據
insert into customer values('a001','jack','男',22);
insert into customer values('a002','mono','女',20);
insert into customer values('a003','tank','女,32);
insert into customer values('a004,'lialue','男',26);
3.查詢表裏數據
select * from customer --查詢此表所有記錄.
select * from custoemr where customerSex='女'; --得到性別為女的信息.
4. 創建存儲過程 (當輸入ID號,顯示出名字,性別,年齡,)
create or replace procedure getCustomer(v_id varchar)
as
v_name customer.customerName%type; --v_name和custoemrName數據類一樣
v_sex custoemr.customerSex %type;
v_age custoemr.customerAge %type;
Begin
select customerName,customerSex.customerAge into v_name,v_sex,v_age from
customer where customerID=v_id;
dbms_output.put_line('信息是:'||v_name||' '||v_sex||' '||v_age);
exception
when NO_DATA_FOUND then
dbms_output.put_line('你輸入的ID號不存在');
end;
5.調用存儲過程
execute getCoustomer('a001'); --查詢以上ID為a001的數據.
備註:
1.創建Create or replace procedure 過程名(參數). or replace:如果存在相同過程名,則替換原有的過程名。
2.語法:Create or replace procedure 過程名[(參數)可選項]
AS [聲明部分]
begin [開始]
exception 異常
end [結束]