Oracle 創建表,存儲過程

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   [結束]