第一次接觸Oracle存儲過程

CREATE TABLE STUDENT(
    ID NUMBER(10)  NOT NULL ,
    NAME VARCHAR2(10) ,
    SEX CHAR(1),
    AGE Number(3),
    CREATIONDATE  DATE,
    MODIFIEDDATE DATE,
    PRIMARY KEY (ID));
    
 --查詢數據
select * from student;



 --插入數據
insert into student(id,name,sex,age,creationdate,modifieddate)
values(7023107001,'Liu','B',19,to_date('20160322','YYMMDD'),to_date('20160322','YYMMDD'));
insert into student(id,name,sex,age,creationdate,modifieddate)
values(7023107002,'Jack','B',19,to_date('20160322','YYMMDD'),to_date('20160322','YYMMDD'));
insert into student(id,name,sex,age,creationdate,modifieddate)
values(7023107003,'Robin','B',19,to_date('20160322','YYMMDD'),to_date('20160322','YYMMDD'));
insert into student(id,name,sex,age,creationdate,modifieddate)
values(7023107004,'Fuck','B',19,to_date('20160322','YYMMDD'),to_date('20160322','YYMMDD'));
insert into student(id,name,sex,age,creationdate,modifieddate)
values(7023107005,'Duck','B',19,to_date('20160322','YYMMDD'),to_date('20160322','YYMMDD'));
insert into student(id,name,sex,age,creationdate,modifieddate)
values(7023107006,'God','B',19,to_date('20160322','YYMMDD'),to_date('20160322','YYMMDD'));
insert into student(id,name,sex,age,creationdate,modifieddate)
values(7023107007,'Dog','B',19,to_date('20160322','YYMMDD'),to_date('20160322','YYMMDD'));
insert into student(id,name,sex,age,creationdate,modifieddate)
values(7023107008,'Bike','B',19,to_date('20160322','YYMMDD'),to_date('20160322','YYMMDD'));
insert into student(id,name,sex,age,creationdate,modifieddate)
values(7023107009,'Ella','B',19,to_date('20160322','YYMMDD'),to_date('20160322','YYMMDD'));
insert into student(id,name,sex,age,creationdate,modifieddate)
values(7023107010,'Make','B',19,to_date('20160322','YYMMDD'),to_date('20160322','YYMMDD'));
insert into student(id,name,sex,age,creationdate,modifieddate)
values(7023107011,'Cela','B',19,to_date('20160322','YYMMDD'),to_date('20160322','YYMMDD'));
insert into student(id,name,sex,age,creationdate,modifieddate)
values(7023107012,'MeLe','B',19,to_date('20160322','YYMMDD'),to_date('20160322','YYMMDD'));
insert into student(id,name,sex,age,creationdate,modifieddate)
values(7023107013,'LooK','B',19,to_date('20160322','YYMMDD'),to_date('20160322','YYMMDD'));
insert into student(id,name,sex,age,creationdate,modifieddate)
values(7023107014,'Jack','B',19,to_date('20160322','YYMMDD'),to_date('20160322','YYMMDD'));
insert into student(id,name,sex,age,creationdate,modifieddate)
values(7023107015,'EKK','B',19,to_date('20160322','YYMMDD'),to_date('20160322','YYMMDD'));
insert into student(id,name,sex,age,creationdate,modifieddate)
values(7023107016,'Jack','B',19,to_date('20160322','YYMMDD'),to_date('20160322','YYMMDD'));

select * from user_procedures where PROCEDURE_NAME='PRO_STUDENT';  

--存儲過程例子(計算學生總人數)

    CREATE OR REPLACE PROCEDURE PRO_STUDENT   
    AS  
    S_TOTAL NUMBER(10);   
    BEGIN  
     SELECT COUNT(*) INTO S_TOTAL FROM STUDENT;   
     DBMS_OUTPUT.PUT_LINE('學生人數：'||S_TOTAL);   
    END;  
    
    EXECUTE PRO_STUDENT;  

--統計女孩人數(帶輸入參數)
     --注意變量定義不能與表中變量相同
    CREATE OR REPLACE PROCEDURE PRO_GIRL_STUDENT(student_sex in CHAR)   
    AS  
    S_TOTAL NUMBER(10);   
    BEGIN  
     SELECT COUNT(*) INTO S_TOTAL FROM STUDENT s WHERE s.sex=student_sex;   
     DBMS_OUTPUT.PUT_LINE('學生人數：'||S_TOTAL);   
    END;  
    
    EXECUTE PRO_STUDENT;