Overview of stored procedures
Stored procedures are a type of subroutine that can complete some tasks and are stored in the database as schema objects. It is a named PL/SQL code block that supports receiving or not accepting parameters, and also supports parameter output. A stored procedure usually contains a definition part, an execution part, and an Exception part. It can be called by other subroutines and can also be reused.
Procedure definition
CREATE [OR REPLACE] PROCEDURE procedure_name
[(argument_name [IN | OUT | IN OUT] argument_type)]
AS | : Indicates that it is an input parameter and a default value can be specified. If the parameter type is omitted, the default is in type
OUT: Indicates an output parameter
IN OUT: Can be used as an input parameter or an output parameter to output the result
Procedure call
EXECUTE |CALL procedure_name [(argument_list )]
Example: Define a process that takes JOB as a parameter to query the maximum salary, minimum salary, and average salary of the JOB.
create or replace procedure query_sal(v_job in emp.job%type)
as
v_max_sal emp.sal%type;
v_avg_sal emp.sal%type;begin
select min(sal) into v_min_sal from emp where job = v_job;
select max(sal) into v_max_sal from emp where job = v_job;
select avg(sal) into v_avg_sal from emp where job = v_job;
dbms_output.put_line('This job is minimum salary is ' || v_min_sal);
dbms_output.put_line('This job is maximum salary is ' || v_max_sal);
dbms_output.put_line('This job is average salary is ' || v_avg_sal);
exception
when no_data_found then
dbms_output.put_line('Not Record Found');
end;
SQL> set serveroutput on
SQL> exec query_sal('SALESMAN');
This job is minimum salary is 1250
This job is maximum salary is 1600
PL/SQL procedure successfully completed.
Parameters and their delivery methods
When establishing a procedure, the parameters passed are optional. If the parameter options are omitted, the procedure will be a parameterless procedure (not specified when defining parameters, no parameters are required when calling). If the parameter option is specified, the process is a parameterized process (the parameter name, mode, and data type need to be specified when defining, and the corresponding parameter value needs to be given when adjusting). The parameters when defining are called formal parameters, and the parameters when calling are called formal parameters. is the actual parameter.
No parameter procedure
as
begin
end;
SQL> exec display_systime;
Current Time is 24-FEB-13
Parameterized procedures
You need to specify the name, mode, and data type of the parameters when defining them
Example: Define a process for adding records (all input parameters)
create or replace procedure add_emp
v_no in emp.empno%type,
v_name in emp.ename%type,
v_dept in emp.deptno%type default 20 —Default department number
)
as
begin
insert into emp (empno,ename,deptno) values(v_no,v_name,v_dept);
exception
when dup_val_on_index then
dbms_output.put_line('Record Exists');
end ;
SQL> exec add_emp(7369,'TEST',20); number, modify the record, and then return the modified results (name and salary).
create or replace procedure ed_emp
(
v_name out emp.ename%type,
v_sal out emp.sal%type)
begin
update emp set sal = sal + 100 where empno = v_no; select ename,sal into v_name,v_sal from emp where empno = v_no;
exception
when no_data_found then
dbms_output.put_line('Not Data Found' );
end;
/
Procedure created.
SQL>VARIABLE t_name varchar2(20);
SQL>VARIABLE t_sal number;
SQL> exec ed_emp(7369,:t_name,:t_sal);
PL/SQL procedure successfully completed.
SQL> print t_name
T_NAME
---------------------------------------- ----------------------------------------
SQL> print t_sal
T_SAL
----------
900
Example: Use of IN OUT type parameters
create or replace procedure comp
(num1 in out number,num2 in out number)
as
v1 number;
v2 number;
begin
v1 := num1 + num2;
v2 := num1 * num2;
num1 := v1;
num2 := v2;
end;
SQL> var v1 number
SQL> var v2 number
SQL> exec :v1 := 3
PL/SQL procedure successfully completed.
SQL> exec :v2 := 5
PL/SQL Procedure SuccessFully Completed.
sql & GT; Exec Comp (: v1,: v2);
SQL & GT; Print v1 v2
v1
----------------------------------------------------------------------------- ----
15
SQL> exec comp(:v1,:v2);
PL/SQL procedure successfully completed.
SQL> print v1 v2
using
----------
120
You can see that the in out type parameters are used as both input parameters and output parameters.
Transfer method of stored procedure parameters:
Transfer by position:
The actual parameters are passed to the formal parameters in order
EXECUTE ED_EMP(8000,'TEST2',20) ;
Pass by nameEXECUTE ED_EMP(v_name=>'ABCDE',v_dept=>10,v_no=>8003);
Mixed delivery
EXECUTE ED_EMP(8005,v_dept=>20,v_name=>' TEST5');
Process Management
View system process information
DBA_OBJECTS
DBA_PROCEDURES
DBA_SOURCE
Use desc procedure_name to view parameter information of stored procedures
SQL>desc ed_emp;
PROCEDURE ed_emp
Argument Name In/Out Default?
------- ----------------------- ----------------------- ---- ------------_No Number (4) in
v_name varchar2 (10) out
v_sal number (7,2) OUT
information from DBA_Objects
SQL & GT; Object_namec, Object_namec t_type , Status from dba_Objects Where Object_name = 'ED_EMP';
Owner Object_name Object_type Status
----------------------------------------------------------------------------------------- ----------------------------------- -------
SCOTT ED_EMP procedure_name,interface,authid from user_procedures;
OBJECT_NAME PROCEDURE_NAME INT AUTHID
------------------------------------------------ --------------- ---------------
DISPLAY_SAL
View the source code of the stored procedure
SQL>select line, text from user_source where name='ED_EMP';
LINE TEXT
--------------------------------------- -------------------------------------------------- ----
1 Procedure ED_EMP
2 (
3 v_no in Emp.empno%Type,
4 v_name out Emp.ename%Type,
5 v_sal out emp.sal%type
6)
7 as
8 begin
9 UPDATE emp SET sal=sal+100 WHERE empno=v_no;
10 SELECT ename,sal INTO v_name,v_sal FROM emp WHERE empno=v_no;
11 EXCEP TION
12 WHEN NO_DATA_FOUND THEN
13 DBMS_OUTPUT.PUT_LINE('NOT FOUND RECORD! ');
14 View error message
SHOW ERRORS
1. Syntax for creating a function
CREATE [ OR REPLACE ] FUNCTION function_name
(argument1 [mode1] datatype1,
argument2 [mode2] datetype2,
...)
RETURN datatype
IS | AS
[local_variable_declarations;...]
BEGIN
--actions;
RETURN expression;
END [function_name];
Some things to note when creating a function
1. When specifying the parameter data type (argument), its length cannot be specified
2. Return must be specified in the function header Clause, the function body must contain at least one return statement
3. You can specify in parameters, out parameters, and in out parameters
4. You can specify default values for parameters. Use the default keyword when specifying a default value. Such as arg1 varchar2 default 'SCOTT'
Advantages of using functions:
1. Increases the flexibility of the code and can complete some more complex tasks, as well as tasks that cannot be completed through SQL alone
2. You can use the function directly in the where sub Sentence to filter data
3. Can be used as a parameter of a stored procedure, which is a supplement to a stored procedure
Create a function without parameters
return varchar2
as
v_user varchar2( 20);
begin
select username into v_user from user_users;
return v_user;
end;
SQL> exec :v1 := get_user ;
PL/SQL procedure successfully completed.
SQL> print v1
V1
--------------------------------- -----
SCOTT
Use local variables to receive the return value of the function
5 end;
6 /
Current User: SCOTT
PL/SQL procedure successfully completed.
Call the function directly in the SQL statement
SQL> select get_user from dual;
---- --------------------------------
SCOTTUse dbms_output to call the function (this call is made as a parameter of the stored procedure Call)
SQL> set serveroutput on;
SQL> exec dbms_output.put_line('Current user: '||get_user);
Current user: SCOTT
Create a function with in parameters
create or replace function raise_sal(name in varchar2)
as
new_sal emp.sal%type;begin
select sal * 1.2 into new_sal from emp
where upper(ename) = upper(name);
return new_sal;
exception
when no_data_found then
raise_application_error(-20000,'Current Employee does not exists');
end;
SQL> select sal,raise_sal('SCOTT') from emp where ename='SCOTT';
---------- ------------------
3000 3600
SQL> select sal,raise_sal('SCOTTT') from emp where ename ='SCOTT';
RAISE_SAL", line 11
Create a function with out parameters
create or replace function get_info
(name varchar2,titile out varchar2)
return varchar2
as
deptname dept.dname%type;
select e.job, d.dname into titile,deptname
from emp e,dept d
where e.deptno = d.deptno
and upper(e.ename) = upper(name);
return deptname;
exception
when no_data_found then
raise_application_error( -20000,'Current Employee does not exists');
end;
/
Note that functions using out parameters cannot be called using SQL statements. Instead, variables must be defined to receive out parameters and the return value of the function.
The call is as follows
SQL> var job varchar2(20);
SQL> var dname varchar2(20);
SQL> exec :dname := get_info('scott',:job);
PL/SQL procedure successfully completed.
SQL> print dname job
DNAME
-------------------------------------------------
RESEARCH
JOB
-------------------------------------------------
ANALYST
SQL> select get_info('scott') from dual
2 ;
select get_info('scott') from dual
*
ERROR at line 1:
ORA-06553: PLS-306: wrong number or types of arguments in call to 'GET_INFO'
建立带有in out参数的函数
create or replace function comp
(num1 number,num2 in out number)
return number
as
v_result number(6);
v_remainder number;
begin
v_result := num1 * num2;
v_remainder := mod(num1,num2);
num2 := v_remainder;
return v_result;
exception
when zero_divide then
raise_application_error(-20000,'Divsion by zero');
end;
/
SQL> var n1 number
SQL> var n2 number
SQL> exec :n2 := 10;
PL/SQL procedure successfully completed.
SQL> exec :n1 := comp(16,:n2);
PL/SQL procedure successfully completed.
SQL> print n1 n2
N1
----------
160
N2
----------
6
函数的调用及限制
1.函数的调用(其具体调用方法参照上面的演示)
a.使用全局变量接收函数的返回值
b.使用本地变量接受函数的返回值
c.在SQL语句中直接调用函数
d.使用dbms_output调用函数
注：函数在调用的时候需要按位置指定参数，没有存储过程参数传递灵活必须具有execute 函数的权限
2.函数在SQL中调用的主要场合
由于函数必须要返回数据，因此只能作为表达式的一部分调用。此外函数可以在SQL语句的以下部分调用
a. select 命令的选择列表或子查询中
b. 条件表达式where, having子句中
c. connect by , start with ,order by 以及group by 子句中
d. insert 命令的values子句中
f. update 命令的set 子句中
3.函数在SQL中调用的限制
a. SQL语句中只能调用存储在服务器端的函数，而不能调用存储于客户端的函数
b. SQL语句中调用的函数只能带有输入参数IN，而不能带有输出参数OUT 以及输入输出参数IN OUT
c. SQL语句中调用的函数只能使用SQL支持的标准数据类型，不能使用PL/SQL特有的类型，如boolean,table,record等
d. SQL语句中调用的函数不能包含insert ,update 和delete 语句
创建一张表tb_emp
SQL> create table tb_emp as select * from emp;
Table created.
创建一个函数，用于删除tb_emp表中指定的empno号的雇员信息，并返回其薪资
SQL> create or replace function del_emp
2 (no number)
3 return number
4 as
5 v_sal emp.sal%type;
6 begin
7 select sal into v_sal from emp where empno = no;
8 delete tb_emp where empno = no;
9 return v_sal;
10 end;
11 /
Function created.
使用SQL语句调用时，收到了错误信息，在内部查询内不能完成DML操作
SQL> select del_emp(7788) from dual;
select del_emp(7788) from dual
*
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "SCOTT.DEL_EMP", line 8
使用exec执行时函数被成功执行
SQL> var n1 number
SQL> exec :n1 := del_emp(7788);
PL/SQL procedure successfully completed.
SQL> print n1
N1
----------
3000
函数的管理
函数使用了与存储过程相关的视图，可以从系统视图中获得函数的相关信息
DBA_OBJECTS
DBA_SOURCE
USER_OBJECTS
USER_SOURCE
查看函数的源码
SQL>select line,text from user_source where name='DEL_EMP' order by line
LINE TEXT
---------------------------------------------------------- ------------------------
1 function del_emp
2 (no number)
3 return number
4 as
5 v_sal emp.sal%type;
6 begin
7 select sal into v_sal from emp where empno = no;
8 delete tb_emp where empno = no; s selected.
View parameter information of the function
FUNCTION del_emp RETURNS NUMBER
Argument Name Type --------------------- ------ --------NO NO Function
---------------------------------- ------------------
Cannot be called as an expression The return keyword is not included to describe the return type. The header must contain the return keyword, and the PL/SQL block contains at least one valid return statement. You can return zero or more values through out, in out. Return an AND through the return statement. Values of the same type in the header declaration can also be used to return values in and in out. Stored procedures cannot be called in SQL statements. SQL statements can call functions. They are mostly used to complete specific operations in the database, such as deletion, update, insertion and other DML operations. Mostly used for specific data such as selection, etc.
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
