Oracle Study Notes 12 subroutines (stored procedures, custom functions) and packages, oracle Study Notes

Oracle Study Notes 12 subroutines (stored procedures, custom functions) and packages, oracle Study Notes
Subroutine: A named PL/SQL block, which is compiled and stored in a database. Subprograms: 1. declaration Part 2. executable part 3. exception Handling (optional) subprograms: 1. process-execute some operations 2. function-execute the operation and return the value

 

Advantages of subprograms:

Modular decomposition of programs into logic module reusability can be called by any number of programs maintainability simplifies maintenance operation security by setting permissions, making data more secure stored procedures are subprograms used to complete specific tasks. For example:

 

A ticket purchase process can be divided into multiple sub-processes.

Create a stored procedure

Syntax of the creation process:

CREATE [OR REPLACE] PROCEDURE   <procedure name> [(<parameter list>)]IS|AS   <local variable declaration>BEGIN   <executable statements>[EXCEPTION   <exception handlers>]END;

 

Create or replace procedure find_emp (emp_no NUMBER) AS empname VARCHAR2 (20); begin select ename INTO empname from emp where empno = emp_no; employee ('employee name is '| empname ); exception when NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('employee id not found '); END find_emp;

 

 

We can also:

-- Query the User-Defined Stored PROCEDURE select distinct name from user_source where type = 'Procedure '; -- view the source code of the stored PROCEDURE definition (PL/SQL statement) select text from user_source where name = 'P _ test'; -- if an error is defined, view the cause of the error. Show error procedure stored procedure name -- delete stored procedure Drop procedure stored procedure name;

 

 

Three Modes of process parameters: IN1. it is used to accept the value of the calling program. 2. the default parameter mode OUT1. is used to return an IN OUT1. value to the calling program to accept the value of the calling program and return an updated value to the calling program.

-- Count the number of employees who meet the specified salary: stored procedure with input parameter in

Create or replace procedure p_total_sal (var_sal in int) is Var_count int; Begin Select count (*) into var_count from emp where sal> var_sal; Dbms_output.put_line ('the total number of employees meeting the requirements is: '| var_count); Exception When others then Dbms_output.put_line ('unknown error'); End;

 

-- Define a stored procedure to return the total number of employees of a specified department: stored procedure with the returned value out

Create or replace procedure p_get_emp (var_deptno int, var_total out int) as Var_n int; Begin Select count (*) into var_n from emp where deptno = var_deptno; Var_total: = var_n; -- The total number is returned by the End parameter;

 

-- Use Declare Var_s int; Begin p_get_emp (10, var_s); Dbms_output.put_line ('Return value '| var_s); End;

 

-- Defines a stored procedure through which a result set (cursor) can be returned ).

Create or replace procedure p_get_datas(mycur out sys_refcursor) is Begin    Open mycur for select * from emp where deptno = 10;End;

 

 

-- Call: Declare Var_cur sys_refcursor; -- receive parameter Row emp % rowtype; Begin p_get_datas (var_cur); -- you do not need to open it again, because Loop Fetch var_cur into row has been enabled in the stored procedure; Exit when var_cur % notfound; Dbms_output.put_line (row. ename | ''| row. job); End loop; End;

-- Input and Output Parameters

-- Create or replace procedure p_get_sal (var_n in out int) isBegin Select sal into var_n from emp where empno = var_n); End;

 

-- Call: Declare Var_s int; Begin Var_s: = & n; p_get_sal (var_s); Dbms_output.put_line ('his salary is '| var_s); End;

 

 

Use of Stored Procedures

 

-- Use of Stored Procedures

1 command method: execute stored procedure name;

2. In PL/SQL: Use the stored procedure name directly.

 

The method of passing parameters when calling a stored procedure.
1. Transmission by location.

Swap (num1, num2 );
2. Pass by name.

Swap (p2 => num2, p1 => num1 );

(P1 and p2 are the parameter names when the stored procedure is defined)

 

Grant the execution permission of the process to other users:

Grant execute on find_emp to martin; grant execute on swap to public; (all database users)

 

 

A function is a PL/SQL subroutine that can return values. Syntax for creating a function:

CREATE [OR REPLACE] FUNCTION　　<function name> [(param1,param2)]RETURN <datatype>  IS|AS  [local declarations]BEGIN　　Executable Statements;　　RETURN result;　　EXCEPTION　　　　Exception handlers;END;

 

Restrictions on defining functions: functions can only accept IN parameters, however, the in out or OUT parameter parameters cannot be the return type of PL/SQL functions or the database access function.

Create a function

Create or replace function fun_hello RETURN VARCHAR2ISBEGIN RETURN 'Friend, hello '; END;

Call a function from an SQL statement:

SELECT fun_hello FROM DUAL;

 

 

Example

Create or replace function item_price_range (price NUMBER) RETURN VARCHAR2 AS min_price NUMBER; max_price NUMBER; begin select max (ITEMRATE), MIN (ITEMRATE) INTO max_price, min_price FROM itemfile; IF price> = min_price AND price <= max_price then return 'the unit price entered is between the lowest price AND the highest price'; else return 'out of range '; end if; END;

 

DECLARE  P NUMBER := 300;  MSG VARCHAR2(200);BEGIN  MSG := item_price_range(300);  DBMS_OUTPUT.PUT_LINE(MSG);END;

 

 

Comparison of procedures and functions

Stopover	Letter count
Run as a PL/SQL statement	Called as part of an expression
The RETURN clause is not included in the Specification Description.	The RETURN clause must be included in the Specification Description.
No value is returned.	A single value must be returned
It can contain RETURN statements, but unlike functions, it cannot be used to RETURN values.	Must contain at least one RETURN Statement

 

A package encapsulates objects such as related procedures, functions, variables, cursors, and exceptions. A package consists of a specification and a subject.

 

 

Package specification

CREATE [OR REPLACE]  PACKAGE   package_name IS|AS[Public item declarations][Subprogram specification]END [package_name];

Package subject

CREATE [OR REPLACE] PACKAGE BODY package_name IS|AS[Private item declarations][Subprogram bodies][BEGINInitialization]END [package_name];

 

 

Example

CREATE OR REPLACE PACKAGE pack_meIS  PROCEDURE order_proc (orno VARCHAR2);  FUNCTION order_fun(ornos VARCHAR2) RETURN VARCHAR2;END pack_me;

 

 

CREATE OR REPLACE PACKAGE BODY pack_me AS  PROCEDURE order_proc (orno VARCHAR2) IS    stat CHAR(1);  BEGIN    SELECT ostatus INTO stat FROM order_master    WHERE orderno = orno;    ……  END order_proc;  FUNCTION order_fun(ornos VARCHAR2)  RETURN VARCHAR2  IS    icode   VARCHAR2(5);    ocode   VARCHAR2(5);  BEGIN  ……  END order_fun;END pack_me;

 

Advantages of the package

• Modular
• Easier Application Design
• Information Hiding
• New feature (the process can be overloaded and public variables or cursors can be defined)
• Better performance

The definition of the cursor in the package is divided into two parts: the cursor specification and the cursor subject. When declaring a cursor specification in the packet specification, you must use the RETURN clause to specify the RETURN type of the cursor. The data type specified by the RETURN clause can be: 1. reference the record type defined by the table with the % ROWTYPE attribute. 2. Record type defined by the programmer

CREATE OR REPLACE PACKAGE cur_pack IS  CURSOR ord_cur(vcode VARCHAR2)    RETURN order_master%ROWTYPE;  PROCEDURE ord_pro(vcode VARCHAR2);END cur_pack;

 

 

Create or replace package body cur_pack as cursor ord_cur (vcode VARCHAR2) RETURN order_master % rowtype is select * FROM order_master where vencode = vcode; PROCEDURE ord_pro (vcode VARCHAR2) IS or_rec order_master % ROWTYPE; begin open ord_cur (vcode); loop fetch ord_cur INTO or_rec; exit when ord_cur % NOTFOUND; values ('Return value: '| or_rec.orderno); end loop; END ord_pro; END cur_pack;

 

 

The USER_OBJECTS view contains information about user-created subprograms and packages.

SELECT object_name, object_type　　FROM USER_OBJECTS　　WHERE object_type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY');

 

 

The USER_SOURCE view stores the source code of subroutines and packages.

SELECT line, text FROM USER_SOURCE WHERE NAME='TEST';