Use of stored procedures in oracle, oracle stored procedures

Use of stored procedures in oracle, oracle stored procedures

Stored Procedure

When I first came into contact with the database, I felt that the stored procedure was very difficult, but after reading the examples I gave you, you can easily master the creation and use of stored procedures.

Stored procedures are stored in large database systems. After the first compilation in the database, the stored procedures do not need to be re-compiled. You can specify the name of the stored procedure and provide parameters for execution.

Example:

Query the scores of students in the computer system, and list the students' names, course names, and scores.

Create procedure student_grade1

As

Select sname, cname, grade

From student s join SC on s. sno = SC. cno

Join course c on c, cno = SC. cno

Where sdept = 'computer system ';

Queries the test status of a specified department student, and lists the name, department, course name, and score.

Create procedure student_grade2

@ Dept char (20) -- Parameter

As

Select sname, sdept, cname, grade

From student s, SC, course c

Where s. sno = SC. cno and c. cno = SC. cno

And sdept = @ dept

Query the scores of a student or course, and list the Student name, course name, and score.

Create procedure student_grade3

@ Stu_name char (10), @ course_name char (20)

As

Select sname, cname, grade

From student s join SC on s. sno = SC. cno

Join course c on c. cno = SC. cno

Where

Sname = @ stu_name and

Cname = @ course_name

Exec

Exec student_grade3

You can enter a specified student and a course

Query the test scores of a certain course for a student. The default course is a database.

Create procedure student_grade4

@ Stu_name char (10 ),

@ Course_name char (20) = 'database'

As

Select sname, cname, grade

From student s join SC on s. sno = SC. cno

Join course c on c. cno = SC. cno

Where

Sname = @ stu_name and

Cname = @ course_name

Query the specified column to indicate the students older than the specified age.

Create procedure student_grade5

@ Sex char (2) = 'male ',

@ Age int = 20,

@ Dept char (20) = 'computer'

As

Select * from student

Where sex = @ sex and age <@ age

And sdept = @ sdept

Calculate the sum of two numbers

Create procedure sum

@ Var1 int, var2 int, var3 int output

As

Var3 = var1 + var2

Calculate the average score of the course, and return the statistical result with the output parameter.

Create procedure avggrade

@ Cname char (20 ),

@ Avg_grade int output

As

Select @ avg_grade = avggrade

From SC join course c on c. cno = SC. cno

Where cname = @ cname

Add 2 points to the credits of the specified course

Create procedure udgrade

@ Cname char (20)

As

Update course set credit = credit + 2

Where cname = @ cname