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;
亿速云「云服务器」，即开即用、新一代英特尔至强铂金CPU、三副本存储NVMe SSD云盘，价格低至29元/月。点击查看>>
