標籤：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;
第一次接觸Oracle預存程序
