Oracle Stored Procedure (2)

Select myfunc (parameter 1, parameter 2...) to dual; -- some business logic can be executed.
I. Differences between functions and stored procedures in Oracle:
A: The function must have a return value, but the process does not.
B: The function can be executed independently. The process must be executed through execute.
C: functions can be embedded into SQL statements for execution.
In fact, we can write complex queries into functions and then call these functions in the stored procedure.
Ii. How to Create a stored procedure:
A: Format
Create or replace procedure <porcedure_name>
[(Parameter name parameter type and description,...)] --- note that no return value is returned.
Is
[Variable Declaration]
Begin
[Process processing]; ---------- NULL;
Exception
When Exception name then
End;
Note: The parameter is passed by value by default. It is in or out or in out. These features are the same as functions.
B: Example 1:
Create or replace procedure mypro ---- create or replace proc mypro error cannot be abbreviated
(A in INT: = 0, B in INT: = 0)
Is
C INT: = 0;
Begin
C: = A + B;
Dbms_output.put_line ('C is value' | C );
End;
Run:
Execute mypro (10, 20); --- in SQL Server, you do not need to reset the arc when executing the stored procedure. Do not tune the semicolon.
Exec mypro (10, 20); -- can be abbreviated
C: Example 2:
If a function contains a SELECT statement, the SELECT statement must have an into statement.
Create or replace procedure mypro1
(A int: = 0, B INT: = 0)
Is
C INT: = 0;
Begin
Select empno + A + B into C from EMP where ename = 'Ford ';
Dbms_output.put_line ('C is values' | C );
End;
Run:
Execute mypro1 (10, 20)

D: What if a result set is returned in a process? You must use the cursor! Use a cursor to process this result set.
Create or replace procedure Test
(
Varempname EMP. ename % Type
)
Is begin ------ An error is reported. The cause of the error is not an into clause.
Select * from EMP where ename like '%' | varempname | '% ';
End;

We cannot use into for this program, because there is no type in Oracle to accept a result set. At this time, we can declare the cursor object to accept it.

PL/SQL cursor:
A: Category:
1: Implicit cursor: The cursor generated because it is not explicitly declared by the user. You cannot see the keyword cursor.
2: Display cursor: The cursor explicitly declared by the cursor keyword.

B: What is implicit cursor:
1: When to generate:
It is generated in the execution of any legal SQL statement (DML---INSERT update Delete DQL-----SELECT). It does not necessarily store data. It may also store the number of rows affected by the record set.
If the SELECT statement is executed, the cursor will store data. If the insert update Delete statement is executed, the number of rows affected by the record will be stored.
C: What is the name of an implicit cursor:
The name is SQL.
What are SQL cursor variables?
Purpose: return the cursor information involved in the last SQL statement execution. Because an implicit cursor is generated every time an SQL statement is executed, the currently executed SQL statement is the current implicit cursor.
SQL % found
SQL % notfound
SQL % rowcount
SQL % isopen
D: Implicit cursor example:

Create Table student basic info table
(
Stuid int,
Stuname varchar2 (20)
)

Alter table student basic info table add constraint pk_stuid primary key (stuid)

Declare
Num INT: = 0;
Begin
Num: = & num;
Delete from student basic information table where stuid = num;
If SQL % notfound then
Dbms_output.put_line ('no data in this row is discovered ');
Else
Dbms_output.put_line ('data is found and deleted, and the number of affected rows is: '| SQL % rowcount );
End if;
End;

E: Example of a display cursor:
1: how to define a display cursor
Declare cursor <cursor_name> is [SELECT statement];
Declare cursor mycur is select empno, ename, job from Scott. EMP;
2: how to open the cursor:
Open <cursor_name>;
Open mycur;
3: How to read data through a cursor
Fetch <cursor_name> into <variable_list>
4: how to close the cursor:
Close <cursor_name>;
Close mycur;
Note: in Oracle, you do not need to display the destroy cursor, because in Oracle, many items are written in Java. Oracle will automatically destroy the cursor.
5. Example:

Declare
Cursor mycur is select empno, ename, job from EMP;
Vempno EMP. empno % type;
Vename EMP. ename % type;
Vjob EMP. Job % type;
Begin
Open mycur;
Fetch mycur into vempno, vename, vjob;
Dbms_output.put_line ('I found you! '| Mycur % rowcount | 'row ');
Dbms_output.put_line ('data read is '| vempno | ''| vename |'' | vjob );
Close mycur;
End;

Because only one entry is read-only, we need to traverse it:
Declare
Cursor mycur is select empno, ename, job from EMP;
Vempno EMP. empno % type;
Vename EMP. ename % type;
Vjob EMP. Job % type;
Begin
Open mycur;
Loop
Fetch mycur into vempno, vename, vjob;
Exit when mycur % notfound;
If mycur % found then
Dbms_output.put_line ('data read is '| vempno | ''| vename |'' | vjob );
End if;
End loop;
Dbms_output.put_line ('I found you! '| Mycur % rowcount | 'row ');
Close mycur;
End;
6: Normally, when reading table data, we need to query it dynamically. So can we add parameters to the cursor in Oracle? Yes!
1): how to define a cursor with parameters:
Declare cursor <cursor_name> (parameter name parameter type description) is select XXXXX from bbbbb where AAA == ??? And CCC = ???;
2) Example:
A cursor is a set. There are two ways to read data.
Method 1: Open fetch close
Method 2: For 1, but the for loop is used at the beginning of the loop, it is equivalent to executing open and processing the loop, it is equivalent to executing fetch,
When the loop is exited, close is executed.
Declare
Cursor query (vname varchar) is select empno, ename, job from EMP where ename like '%' | vname | '% ';

Begin
For line in query ('A ')
Loop
Dbms_output.put_line (line. empno | ''| line. ename |'' | line. Job );
End loop;
End;

Dynamic Input:
Declare
Cursor query (vname varchar) is select empno, ename, job from EMP where ename like '%' | vname | '% ';

Name1 varchar (10 );
Begin
Name1: = upper ('& name1 ');
For line in query (name1)
Loop
Dbms_output.put_line (line. empno | ''| line. ename |'' | line. Job );
End loop;
End;

From: http://hi.baidu.com/qingcao_xiaohei/blog/item/f0d1b5997aca62016f068cae.html