Oracle Database Fundamentals-Stored procedures and functions

I. Stored procedures and Functions

Stored procedures: Encapsulation of a module
Functions: Functions are almost the same as stored procedures

Difference:
function must return a value via the return keyword
Stored procedures:
Return value is not required

Parameters:
INPUT type parameter
Output Type parameters
Input (output) type parameters

When to use a stored procedure, when to use a function
In general, a function is used when there is only one return value.
When there is no return value or multiple return values are required, the stored procedure

two. First look at the SQL Basics

First look at the contents of the table for example:

Here's how to get started:

Start by building a new SQL Window:

DECLARE  --declaring Variables  /*reference variable--a column record type variable in the table below the current user--a row in the table below the current user*/I Number; A STUDENT. SNAME%TYPE;--reference variable, refers to the sname column in the student tableB STUDENT%ROWTYPE;--record type variable, the current user in the table below a row, according to additional criteria to select which rowBEGIN  --StartI:= 107; SELECTS.sname intoA fromSTUDENT SWHERES.sno=I; Dbms_output. Put_Line ('The result of the query, the value of a is:' ||A);--concatenation of strings in Sol | |  SELECT *  intoB fromSTUDENT SWHERES.sno=I; Dbms_output. Put_Line ('The result of the query, the value of B is:' ||B.sname);--Take a column of B in the form of a b.~END;--End

iii. cursors (cursor)

Learn to use cursors to traverse student names in student tables

--cursors (which can be thought of as collections, which contain a record-type variable), have four properties,/*ISOPEN--whether the cursor opens NotFound--bolean value, returns TRUE or Falsefound--bolean value, returns TRUE or Falserowcount--the number of rows of records that have been fetched, equivalent to a few lines of change to output a few */DECLARESTU STUDENT%ROWTYPE; CURSORStus is    SELECT *  fromSTUDENT;BEGIN  OPENStus; LOOPFETCHStus intoSTU; EXIT  whenStus%NOTFOUND; Dbms_output. Put_Line (STU.  SNAME); ENDLOOP; CLOSEStus; END;

iv. Stored Procedures (procedure)

Enter the class number in the student table to output the student's name

The first step: Create a new SQL Window, compile a SQL statement, put the compiled statements into the procedures file directory (this is a query of the stored procedure)

CREATE OR REPLACE PROCEDUREHanqi (SCLAinch  Number) as  CURSORStus is    SELECT *  fromSTUDENT SWHERES.class=Scla; STU STUDENT%ROWTYPE;BEGIN  OPENStus; LOOPFETCHStus intoSTU; EXIT  whenStus%NOTFOUND; Dbms_output. Put_Line (STU.  SNAME); ENDLOOP; CLOSEStus;END;

Second step, re-establish a test Window, call the stored procedure

--declare--Local variables here integer;        begin  -- Test Statements  here Hanqi (95031); -- in the Test window, a direct call is possible. End;


The output information is:

Third, take a look at the command window, or you can call the stored procedure

Connected toOracleDatabase11g Express Edition Release11.2.0.2.0Connected asTest@XESQL> Select *  fromstudent; SNO SNAME SSEX sbirthday CLASS--- ------ ---- ----------- ----------108Zenghua Male1977/9/1         95033 theKuanming Male1975/Ten/2        95031107Wang Liju1976/1/ at        95033101Li June Man1976/2/ -        95033109Wang Fang Men1975/2/Ten        95031103Contacts male1974/6/3         950316rows Selectedsql> SetServeroutput on; SQL> ExecuteHanqi (95031); Kuanming Wang Fang Contacts pl/SqlprocedureSuccessfully completed

v. Functions (function)

Functions are constructed in a similar way to stored procedures

CREATE OR REPLACE FUNCTION inch  Number inch  Number RETURN  Number  as  number;    BEGIN   C:=+  B;   RETURN C; END;

Calling functions directly:

six, Output type parameters (can be used as a stored procedure return value, can be one, or can be multiple)

New SQL Window:

CREATE OR REPLACE PROCEDUREHANQI3 (SCLAinch  Number, VARI out Number) asBEGIN  UPDATESTUDENT SSETS.ssex= 'female' WHERES.class=Scla; SELECT COUNT(*) intoVARI fromSTUDENT SWHERES.class=Scla;END;

New Test Window:

--DECLARE--Local variables here INTEGER;       BEGIN  -- Test Statements  here HANQI3 (95031, I);  Dbms_output. Put_Line (' modified number of records:'| | I); -- The output type parameter is lowered in the text window and printed END;

The result of the execution is:

You can have a return value for a stored procedure by using the output type parameter

Oracle Database Fundamentals-Stored procedures and functions