Oracle database stored procedures and storage functions

Oracle database stored procedures and stored functions are stored in the database for all user programs to call the subroutine called stored procedures, stored functions. The stored procedure does not return values. Stored functions return values to CREATE stored procedures use the create procedure command to CREATE stored procedures and functions. Syntax: create [or replace] PROCEDURE process name (parameter list) as plsql subroutine body; Stored PROCEDURE example: for the specified employee's salary of 10% yuan on the basis of the original salary/* for the specified employee's salary of 10% yuan on the basis of the original salary, and print the salary before and after the salary */SQL> create or replace procedure raiseSalary (empid in number) as pSal emp. sal % type; -- save the current employee salary begin -- query the employee's salary select sal into pSal from emp where empno = empid; -- update emp set sal = sal * 1.1 where empno = empid; -- print the salary dbms_output.put_line ('employee No.: '| empid | 'salary increase pre' | Psal | 'post-paid raise '| psal * 1.1); end; 1/Procedure created -- stored Procedure call -- Method 1 SQL> set serveroutput onSQL> exec raisesalary (7369); employee ID: 7369 method 2 set serveroutput onbegin raisesalary (800); end;/PL/SQL procedure successfully completed storage Function (Function) is a named stored program, it can contain parameters and return a calculated value. The structure of a function is similar to that of a process, but a RETURN clause must be provided to RETURN the function value. Function Description specifies the function name, result value type, and parameter type. Syntax for creating a storage FUNCTION: CREATE [or replace] FUNCTION name (parameter list) return function value type ASPLSQL subroutine; example: query the annual income of an employee. SQL>/** // * query the total income of a certain employee */create or replace function queryEmpSalary (empid in number) return number as pSal number; -- Define the variable to save the employee's salary pComm number; -- Define the variable to save the employee's bonus begin select sal, comm into psal, pcomm from emp where empno = empid; return psal * 12 + nvl (pcomm, 0); end;/Function created l Function call SQL> declare v_sal number; begin v_sal: = queryEmpSalary (7934 ); dbms_output.put_line ('salary is: '| v_sal); end;/sa Lary is: 15600 PL/SQL procedure successfully completed SQL> begin dbms_output.put_line ('salary is: '| queryEmpSalary (7934); end;/salary is: 15600 PL/SQL procedure successfully completed trigger database trigger is a PL/SQL program associated with the table and stored. When a specific data operation statement (Insert, update, delete) is issued on a specified table, Oracle automatically executes the sequence of statements defined in the trigger. The trigger type statement-Level Trigger is executed once before or after the specified operation statement operation, regardless of the number of rows affected by this statement. EACH record of a ROW-Level Trigger statement is triggered. Use old and new pseudo-record variables in row-level triggers to identify the status of values. CREATE [or REPLACE] TRIGGER name {BEFORE | AFTER} {DELETE | INSERT | UPDATE [OF column name]} ON table name [for each row [WHEN (condition)] PLSQL block Example 1: restrict non-working hours to insert data to the database SQL> create or replace trigger securityEmp before insert on emp declare begin if to_char (sysdate, 'day') in ('thurs ', 'saturday', 'sunday') or to_number (to_char (sysdate, 'hh24') not between 8 and 18 then raise_application_error (-20001, 'data cannot be inserted during non-working hours. '); End if; end;/Trigger created Trigger statement and false record variable value Trigger statement: old: newInsert all fields are empty (null) the data to be inserted is updated. The value of the previous row is updated. The value is deleted. All fields in the previous row are empty (null). Example 2: confirm data (check that the modified sal value in the emp table is not lower than the original value) SQL> create or replace trigger checkSal before update of sal on emp for each row declare begin if: new. sal <: old. sal then raise_application_error (-20001, 'salary after the Update'); end if; end;/Trigger created after running: SQL> update emp set sal = 260 where empno = 7499; update emp set sal = 260 where empno = 7499 ORA-20001: updated salary ratio before update small ORA-06512: In "SCOTT. CHECKSAL ", line 4ORA-04088: trigger 'Scott. CHECKSAL 'trigger summary trigger during execution can be used for • data validation • Implementation of complex security checks • audit, query triggers, processes, and functions such as data operations performed on a table. • Select * from user_triggers; • Select * from user_source;