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;