First contact with Oracle stored procedures

CREATE TABLE STUDENT (
ID Number (Ten) is not NULL,
NAME VARCHAR2 (10),
SEX CHAR (1),
Age Number (3),
CreationDate DATE,
MODIFIEDDATE DATE,
PRIMARY KEY (ID));

--Querying data
SELECT * from student;



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

--Stored Procedure example (total number of students calculated)

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;

--statistics on the number of girls (with input parameters)
--Note that variable definitions cannot be the same as variables in a 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;

First contact with Oracle stored procedures