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;
--结束END--
本文标题: 第一次接触Oracle存储过程
本文链接: https://www.lsjlt.com/news/37560.html(转载时请注明来源链接)
有问题或投稿请发送至:
邮箱/279061341@qq.com QQ/279061341
0