CREATE TABLE STUDENT( ID NUMBER(10) NOT NULL , NAME VARCHAR2(10) , SEX CHAR(1), AGE Number(3), CREATIONDATE DATE, MODIFIEDDATE DATE, PRIMARY KEY (ID));
--查詢數(shù)據(jù) select * from student;
--插入數(shù)據(jù) 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';
--存儲過程例子(計算學(xué)生總?cè)藬?shù))
CREATE OR REPLACE PROCEDURE PRO_STUDENT AS S_TOTAL NUMBER(10); BEGIN SELECT COUNT(*) INTO S_TOTAL FROM STUDENT; DBMS_OUTPUT.PUT_LINE('學(xué)生人數(shù):'||S_TOTAL); END;
EXECUTE PRO_STUDENT;
--統(tǒng)計女孩人數(shù)(帶輸入?yún)?shù)) --注意變量定義不能與表中變量相同 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('學(xué)生人數(shù):'||S_TOTAL); END;