Plsql Sub-Program is Plsql block

Plsql plays, four variables, cursors, and collections are all for subsequent subroutines, including two classes, one is a function, one is a stored procedure,

1) Establishing and invoking the process

2) establishing and invoking functions

3) Management of Plsql sub-procedures

There are two kinds of call Out_time () in Sqlplus, but exec out_time

Process

The process is to perform a specific operation, to establish a specific process, the syntax is relatively simple,

create [or replace] procedure procedure_name

(argument1 [model] Datatype1,..)

is [as]

PL/SQL block;

can take parameter with parameter is in or out or INOUT with parameter to specify type

EG1) Create a process with no parameters

Create or replace procedure Out_time

Is

Begin

Dbms_output.put_line (Systimestamp);

End

EG2) with the parameter

Create or Replace procedure Add_employee (Eno number,name varchar2)

Is

E_integrity exception;

pragma exception_init (e_integrity,-2291);

Begin

Inter into EMP (empno,ename) values (eno,name);

Execute exec add_employee (1111, ' Mary ');

EG3) With out parameters

Create or replace procedure Query_employee

(Eno number,name out VARCHAR2)

Is

Begin

Select ename into name from EMP where Empno=eno;

var name varchar2 (Ten) with the output parameter must be defined in advance to define a variable to receive the output parameters

EXEC Query_employee (7788,:name);

Print name;

EG4) with in out parameter

Create or replace procedure compute

(Num1 in Out number)

EG5) passing variables and data to subroutines

Create or replace procedure Add_dept

(DNO number,dname varchar2 default null,loc varchar2 default null)

Is

Begin

INSERT INTO Dept values (DNO,DNAME,LOC);

Passing variables

exec add_dept (' Sales ', ' new Youk ');

EXEC add_dept (60);

EXEC add_dept (+, ' add ');

EXEC add_dept (dname=> ' sals ', dno=>50);

EXEC add_dept (dno=>60);

EXEC add_dept (70,dname=> ' dsldsf ');

"Maintenance of process functions" user_source;

After the process is established, Oracle stores the process name, source code, and its execution code in the data dictionary, and when the procedure is called, the application executes directly according to its execution code, without the need to re-parse the process code, so the subroutine is better than executing the direct SQL statement

Select text from user_source where name = ' add_dept ';

drop procedure add_dept;

Select Object_name, created,status from User_objects where object_type in (' PROCEDURE ', ' FUNCTION '); User_objects contains all the objects of the current user.

"Compiled troubleshooting" User_errors maintain this user's current error

Show errors procedure procedure_name;

Select Line| | ' /' | | Position, text from user_errors where name= ' xxxname ';

"There is a dependency between the function and the process"

dependencies have direct and indirect, indirect is GE fight cattle, directly you understand, which object is referred to, which object is called the referenced object, two methods to determine the relationship, data dictionary user_dependencies and Deptree ideptree the latter can determine the direct or indirect dependence, The only thing to note is that when the structure of the referenced object is modified, the dependent objects become invalid and need to recompile the storage objects

Alter procedure Add_employee Complie;

Alter VIEW dept10 Complie;

alter function Get_info Complie;

http://aklaus.blog.51cto.com/9724632/1950055 This explanation the use of substitution variables

"Function" is used to return specific data with the following syntax

Create or Replace function function_name

(argument1 [Model1] Datatype1,

argument2 [Model2] datatype2,

...)

return datatype

Is|as

Plsql Block;

Note that the function's head must have a return sentence, and there is at least one return statement in the function body.

EG1) The simplest function

sql> Create or Replace function Get_user
2 return VARCHAR2
3 is
4 V_user varchar2 (10);
5 begin
6 Select username into V_user from user_users;
7 return v_user;
8 End;
9/
sql> var v1 varchar2 (100);
Sql> Exec:v1:=get_user; Using alternative variables and invocation methods, there is ambiguity as to why Exec:v1:get_user shows the result after execution, possibly an environment variable problem
V1
---------
SCOTT

sql> Print V1
V1
---------
SCOTT

Sql> select Get_user from dual;
Get_user
--------------------------------------------------------------------------------
SCOTT
Sql> set serveroutput on;
sql> exec dbms_output.put_line (get_user);
SCOTT
PL/SQL procedure successfully completed

This is followed by a usage with the parameter in out. Note If an out parameter is taken, the variable must be defined to receive the output value of the out parameter, and the function cannot be called in the SQL statement.

"Restrictions on the use of functions"

The function must return data and be called only as part of the expression, which can be called using the following places:

Select command

where and having words

Connect by Startwith Order by and GROUP by

Insert Values in

In Update set

Functions that can only be called by a stored function (server-side) in an SQL statement can only have input parameters, and the function type cannot be a plsql specific data type such as a function called by a Boolean table record cannot contain an INSERT update DELETE statement

This article from "Yun Weibang" blog, declined reprint!

Plsql Sub-Program is Plsql block