Shulou(Shulou.com)06/01 Report--

1. Overview of stored procedures and functions stored procedures and stored functions: named PLSQL blocks stored in the database for all user programs to call to complete specific functions. A, whether the difference between a stored procedure and a stored function uses a return return value. (that is, the stored procedure does not return a value, the stored function returns a value) b, the program of the first stored procedure and function uses the stored procedure or function to realize the output 'Hello everyone'. -- create stored procedure CREATE OR REPLACE PROCEDURE first_proc IS BEGIN DBMS_OUTPUT.put_line ('my procedure'); DBMS_OUTPUT.put_line ('Hello universal'); END;-create function CREATE OR REPLACE FUNCTION first_func RETURN VARCHAR IS BEGIN DBMS_OUTPUT.put_line ('my function'); RETURN 'hello everyone'; END You can see that when the code is executed, the stored procedure FIRST_FUNC and the stored function FREST_PROC have been stored in the Functions and Procedures in the all objects on the left, respectively.

Call stored procedure BEGIN first_proc;END; call stored function BEGIN DBMS_OUTPUT.put_line (first_func); END 2. Creation of stored procedure a, syntax for creating stored procedure CREATE [OR REPLACE] PROCEDURE procedure_name [(argument1 [{IN | OUT | IN OUT}] type, argument2 [{IN | OUT | IN OUT}] type,...)] (IN is input parameter, OUT output parameter, IN | OUT is input parameter) {IS | AS} (IS | AS cannot be omitted even if there is no declaration part You can choose either IS or AS) declare part, type. Description of variables BEGIN execution part EXCEPTION optional exception error handling section END;b, code example: create a stored procedure CREATE OR REPLACE PROCEDURE proc1 (v_empno IN emp01.empno%TYPE) IS BEGIN for input parameters-delete the specified employee information DECLARE FROM emp01 WHERE empno = v_empno based on the employee number -- determine whether to delete IF SQL%NOTFOUND THEN successfully-RAISE_APPLICATION_ERROR between 20000 and-20999 (- 20008, 'specified deleted employee does not exist'); ELSE DBMS_OUTPUT. Put_line ('deleted successfully'); END In general, there is no need to add COMMIT or ROLLBACK to stored procedures or stored functions. Who calls who adds COMMIT or ROLLBACKc, creates stored procedures CREATE OR REPLACE PROCEDURE proc2 (v_deptno IN NUMBER, v_avgsal OUT NUMBER, v_cnt out NUMBER) ISBEGIN SELECT AVG (sal) with output parameters, count (*) INTO v_avgsal, v_cnt FROM emp WHERE deptno = v_deptno EXCEPTION WHEN NO_DATA_FOUND THEN dbms_output.put_line ('does not have this department'); WHEN OTHERS THEN dbms_output.put_line (SQLERRM); END;d, create a stored procedure CREATE OR REPLACE PROCEDURE proc3 (v_num1 IN OUT NUMBER, v_num2 IN OUT NUMBER) with input and output parameters AS v_temp NUMBER: = 0 ~ begin v_temp: = vannum 1; v_num1: = v_num2 V_num2: = IN temptress End; 3. Create a stored function, the syntax for creating stored function CREATE [OR REPLACE] FUNCTION function_name [argument1 [{IN | OUT | IN OUT}] type, argument2 [{IN | OUT | IN OUT}] type,...)] RETURN return_type {IS | AS} declaration part, type. Variable description BEGIN execution part, function body EXCEPTION optional exception error handling part END;b, creating a storage function with input parameters returns the total salary of the department according to the department number: CREATE OR REPLACE FUNCTION func1 (v_deptno IN NUMBER) RETURN NUMBERIS v_sumsal NUMBER;BEGIN SELECT SUM (SAL) INTO v_sumsal FROM emp WHERE deptno = v_deptno RETURN vainsumsaltExcession WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.put_line ('no department'); WHEN OTHERS THEN DBMS_OUTPUT.put_line (SQLERRM); END C. Create a storage function with output parameters to output the employee's name and salary according to the employee number, and return the employee's annual income: CREATE OR REPLACE FUNCTION func2 (v_empno IN emp.empno%TYPE, v_name OUT emp.ename%TYPE, v_sal OUT emp.sal%TYPE) RETURN NUMBERIS v_salsum NUMBER BEGIN SELECT ename, sal, (sal + nvl (comm, 0)) * 12 INTO v_name, v_sal, v_salsum FROM emp WHERE empno = voluptuous empno; RETURN vastsalsum excess WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.put_line ('without this employee'); WHEN OTHERS THEN DBMS_OUTPUT.put_line (SQLERRM); END D. Create a storage function with input and output parameters to find the sum of the squares of two numbers, and output the square create OR REPLACE FUNCTION func3 (N1 IN OUT NUMBER, N2 IN OUT NUMBER) RETURN NUMBERASBEGIN N1: = N1; N2: = N2; RETURN N1 = N2 4. Call and delete a stored procedure, call stored procedure method 1: ORACLE uses execute statement to call stored procedure: (belonging to SQLPlus command) EXEC [UTE] Procedure_name (parameter1, parameter2...) Method 2: call directly in PL/SQL code, such as BEGIN procedure_name (parameter1, parameter2...) END;b, different cases of calling stored procedures

Note: after the creation of stored procedures in section 2 of this topic, four stored procedures are saved in the Oracle database.

Call a stored procedure with no parameters: refer to the procedure name directly. Call a stored procedure with input parameters. Call a parameter procedure with output parameters. Call a stored procedure with input and output parameters. SQLPlus environment:-- call non-parameter stored procedure SQL > set serveroutput onSQL > exec first_proc my procedure Hello Everynetizable SQL procedure successfully completed-- call stored procedure SQL > exec proc1 with input parameters (1234); begin proc1 (1234); end;ORA-20008: specify that deleted employee does not exist ORA-06512: in "SCOTT.PROC1", line 9ORA-06512: in line 1PL/SQL environment:-- call stored procedure BEGINfirst_proc;END without parameter -- call stored procedure BEGINproc1 (1234) with input parameters; END;-- call stored procedure DECLAREv_avgsal NUMBER;v_count NUMBER;BEGINPROC2 (10, v_avgsal, v_count) with output parameters; DBMS_OUTPUT.put_line ('average salary:' | | v_avgsal); DBMS_OUTPUT.put_line ('Total number:' | | v_count); END;-- calls stored procedure with input and output parameters DECLAREv_n1 NUMBER: = 5 V_n2 NUMBER: = 10 switch BEGINPROC3 (v_n1, v_n2); DBMS_OUTPUT.put_line ('N1v_n2' | | v_n1); DBMS_OUTPUT.put_line ('N2v_n2' | | v_n2); END;c, the syntax format for deleting stored procedures is using the DROP PROCEDURE command, the syntax is as follows: DROP PROCEDURE [user.] Procedure_name5, call and deletion of storage function Note: section 3 of this topic stores 4 storage functions in the Oracle database after the creation of the storage function. A. Calling a stored function is the same as calling a stored function, which can be divided into:

1. Call the storage function with no parameters

2. Call the storage function with input parameters

3. Call the storage function with output parameters

4. Call the storage function with input and output parameters. PL.SQL environment:-- call the function BEGINdbms_output.put_line (first_func) with no parameters; END;-- calls the function BEGINdbms_output.put_line with input parameters ('payroll of the department:' | | func1 (& no)); END;-- calls the function with output parameters DECLAREv_name emp.ename%TYPE;v_sal emp.sal%TYPE;v_salsum NUMBER;BEGINv_salsum: = func2 (& no, v_name, v_sal) DBMS_OUTPUT.put_line ('name:' | | v_name); DBMS_OUTPUT.put_line ('salary:' | | v_sal); DBMS_OUTPUT.put_line ('Annual income:' | | v_salsum); END;-- calls the function DECLAREv_n1 NUMBER with input and output parameters: DECLAREv_n1 NUMBER: = 5 NUMBER: = 6 number;BEGINv_sum: = func3 (v_n1, v_n2) DBMS_OUTPUT.put_line ('N1 squared:'| v_n1); DBMS_OUTPUT.put_line ('N2 squared:'| | v_n2); DBMS_OUTPUT.put_line ('N1 and N2 squared sum:'| | v_sum); END;b, delete function procedure can use the DROP FUNCTION command, syntax is as follows: DROP FUNCTION [user.] Function_name6, additional description of parameter default values: both procedures and functions can use the DEFAULT keyword to specify default values for input parameters when declaring procedure or function parameters. Sample code: seek the annual income of the department. (storage function) CREATE OR REPLACE FUNCTION func2 (v_empno IN emp.empno%TYPE, v_name OUT emp.ename%TYPE, v_sal OUT emp.sal%TYPE) RETURN NUMBERIS v_salsum NUMBER;BEGIN SELECT ename, sal, (sal + nvl (comm, 0)) * 12 INTO v_name, v_sal, v_salsum FROM emp WHERE empno = vested empnos; RETURN v_salsum EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.put_line ('no employee'); WHEN OTHERS THEN DBMS_OUTPUT.put_line (SQLERRM); END; calls this stored function:-- call DECLARE v_totalsal NUMBER;BEGIN v_totalsal: = func5; DBMS_OUTPUT.put_line (v_totalsal); END

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.