SLTechnology News&Howtos › Database ›

First contact with Oracle stored procedure

Shulou Source: shulou.com Published: 2022-06-01 16:29:02 09月29日 Update

CREATE TABLE STUDENT(

ID NUMBER(10) NOT NULL ,

NAME VARCHAR2(10) ,

SEX CHAR(1),

AGE Number(3),

CREATIONDATE DATE,

MODIFIEDDATE DATE,

PRIMARY KEY (ID));

--Query data

select * from student;

--Insert data

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';

--Storage process example (count total number of students)

CREATE OR REPLACE PROCEDURE PRO_STUDENT

AS

S_TOTAL NUMBER(10);

BEGIN

SELECT COUNT(*) INTO S_TOTAL FROM STUDENT;

DBMS_OUTPUT.PUT_LINE ('Number of students: '||S_TOTAL);

END;

EXECUTE PRO_STUDENT;

--Counting the number of girls (with input parameters)

--Note that variable definitions cannot be the same as variables in the table

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 ('Number of students: '||S_TOTAL);

END;

EXECUTE PRO_STUDENT;