ORACLE-related syntax-subprograms and packages (package, function, procedure)

ORACLE-related syntax-subprograms and packages (package, function, procedure) Summary: subprograms are named PL/SQL blocks that can contain parameters and can be called as needed at any time. PL/SQL has two types of subprograms: process and function. A process is used to execute a specific task. A function is used to execute the task and return values. A package encapsulates related types, variables, constants, cursors, exceptions, processes, and functions. A package consists of a package specification and a package body. A package specification is a package interface that contains public objects and their types. The package subject implements the cursor and subroutine in the package specification. The declaration in the package body is only used in the package. The definition of the midstream object in the package is divided into two parts: the cursor specification and the cursor subject. Syntax and example: 1. syntax for creating a stored procedure:

CREATE [OR REPLACE] PROCEDURE procedure_name[(parameter_list)]{IS|AS}[local_declarations]BEGINexecutable_statements[EXCEPTIONexception_handlers]END [procedure_name];

 

Procedure_name indicates the process name. Parameter_list is the parameter list. Local_declarations is a partial declaration. Executable_statements is an executable statement. Exception_handlers is an exception handling program. Example 1: demonstrate the creation process (assign a default value to the IN parameter IN the parameter list, and do not assign the default value to the OUT and in out parameters)

Create or replace procedure find_emp (emp_no in number := 7900) asempname varchar2 (20); beginselect ename into empname from emp where empno = emp_no; dbms_output.put_line ('employee name is '| empname); exceptionwhen no_data_found thendbms_output.put_line ('employee number not found'); end find_emp;

 

Call process: EXECUTE procudure_name (parameters_list); you can also call it in the process, directly write procudure_name without writing EXECUTE. Example 2: demonstrate the process of creating an OUT Parameter

create or replace procedure test(value1 varchar2,value2 out number)isidentity number;beginselect sal into identity from emp where empno=value1;if identity<2000 thenvalue2:=1000;elsevalue2:=500;end if;end;

 

Call the process with the OUT parameter:

declarevalue2 number;begintest('7900',value2);dbms_output.put_line(value2);end;

 

Example 3: Create an in out Parameter

create or replace procedure swap(p1 in out number,p2 in out number)isv_temp number;beginv_temp:=p1;p1:=p2;p2:=v_temp;end;

 

Call the process with the in out parameter:

declarenum1 number:=100;num2 number:=200;beginswap(num1,num2);dbms_output.put_line('num1= '||num1);dbms_output.put_line('num2= '||num2);end;

 

Example 4: grant the execution permission of the process to another user

GRANT EXECUTE ON find_emp TO scott;GRANT EXECUTE ON swap TO PUBLIC;

 

Grant the execution permission of the find_emp process to scott, and grant the execution permission of the swap process to all database users. Delete process Syntax:

DROP PROCEDURE procudure_name;

 

2. the syntax of the function definition function is as follows:

CREATE [OR REPLACE] FUNCTION function_name[(parameter_list)]RETURN datatype{IS|AS}[local_declarations]BEGINexecutable_statements[EXCEPTIONexception_handlers]END [function_name];

 

Function_name is the name of the function. Parameter_list is the parameter list. Local_declarations is a partial declaration. Executable_statements is an executable statement. Exception_handlers is an exception handling program. Note when using functions: the format parameter must only use the database type, rather than the PL/SQL type. The return type of the function must also be the database type. Functions cannot be executed independently. They can only be called using SQL statements or PL/SQL blocks. Example 5: demonstrate how to create a function

Create or replace function fun_helloreturn varchar2 isbeginreturn 'Friend, hello '; end;

 

Call function: select fun_hello from dual; function authorization: For details, see Example 4. Delete function: drop function function_name process and function difference process FUNCTION as PL/SQL statement execution as part of the expression call in the specification does not contain the RETURN clause must contain the RETURN clause in the specification does not RETURN any value must be A single returned value can contain a RETURN statement, however, unlike a function, it cannot be used to RETURN values. It must contain at least one RETURN statement. 3. the syntax of the package creation specification is as follows:

CREATE [OR REPLACE] PACKAGE package_nameIS|AS[Public type and item declarations][Subprogram specifications]END [package_name];

 

Package_name indicates the package name. Public type and item declarations are declared types, constants, variables, exceptions, and cursors. Subprogram specifications declare PL/SQL subprograms. Example 6: demonstrate creating a package specification

create or replace package pack_op isprocedure pro_print_ename(id number);procedure pro_print_sal(id number);function fun_re_date(id number) return date;end;

 

Syntax for creating a package subject:

CREATE [OR REPLACE] PACKAGE BODY package_nameIS|AS[Public type and item declarations][Subprogram bodies][BEGINInitialization_statements]END [package_name];

 

Package_name indicates the package name. Public type and item declarations are declared types, constants, variables, exceptions, and cursors. Subprogram bodies is a common and private PL/SQL subroutine defined. Example 7: Create a package subject

Create or replace package body pack_op isprocedure pro_print_ename (id number) isname emp. ename % type; beginselect ename into name from emp where empno = id; dbms_output.put_line ('employee name: '| name); end pro_print_ename; procedure pro_print_sal (id number) issalary emp. sal % type; beginselect sal into salary from emp where empno = id; dbms_output.put_line ('employee salary: '| salary); end pro_print_sal; function fun_re_date (id number) return date isbedate emp. hiredate % type; beginselect hiredate into bedate from emp where empno = id; return bedate; end fun_re_date; end pack_op;

 

Example 8: Call the process and function exec pack_op.pro_print_ename (7900); exec pack_op.pro_print_sal (7900); select pack_op.fun_re_date (7900) from dual; Example 9: demonstrate how to create a package with a cursor in a package

create or replace package pack_emp iscursor cur_emp return emp%rowtype;procedure pro_cur;end pack_emp;

 

Create a package subject

Create or replace package body pack_emp iscursor empty return emp % rowtype isselect * from emp; procedure empty emp % rowtype; beginopen empty; loopfetch into rec_emp; exit when cur_emp % notfound; if rec_emp.sal <1000 thendbms_output.put_line ('employee salary: '| rec_emp.sal |', you must redouble your efforts to raise your salary '); elsif rec_emp.sal> = 1000 and rec_emp.sal <2000 thendbms_output.put_line ('employee salary: '| rec_emp.sal |'. Generally, you need a department manager to do this '); elsedbms_output.put_line ('employee salary: '| rec_emp.sal |', the salary is good, and a general manager is required. '); end if; end loop; end pro_cur; end pack_emp;

 

Call the procedure in the package to call the cursor exec pack_emp.pro_cur in the package; Example 10: The stored procedure returns the subroutine package of the cursor (this package returns the r_cur cursor)

CREATE OR REPLACE package SCOTT.pk_wtistype mytype is ref cursor;procedure p_wt(mycs out mytype);end;CREATE OR REPLACE package BODY SCOTT.pk_wtisprocedure p_wt(mycs out mytype)isr_cur mytype;beginopen r_cur for select * from emp;mycs:=r_cur;end p_wt;end pk_wt;

 

Query Information about processes, functions, and packages: USER_OBJECTS data dictionary View

column object_name format a18select object_name,object_type from user_objects where object_type in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY');