Oracle stored procedure writing format

Create a stored procedure

CREATE OR REPLACE PROCEDURE stored procedure name

(parameter 1 (input parameter) IN NUMBER

Parameter 2 (output parameter) OUT DATE

Parameter 3 (input, output) IN OUT NUMBER)

AS

Variable 1 VARCHAR2

Variable 2 NUMBER

Variable 3 DATE

BEGIN

Variable 1: = TO_CHAR (sysdate-1, 'yyyymmdd')

Variable 2: =

Variable 3: =

Select first_name,phone_number into parameter 2, parameter 3 from employees where employee_id= parameter 1

Update employees set salary=salary+ parameter 3 where first_name= parameter 1

Commit

Dbms_output.put_line (Parameter 1 | | 'salary has been increased' | | Parameter 2 | |'!')

Insert into MFUND.T_TRADEACCO_FEE_DETAIL (manager_code,tacode) SELECT D. managerless code, D. tacode

FROM xnsale.T_FAFS_TRADEACCO_FEE_DETAIL@xnsale D WHERE D.SHARE_DATE = 'variable 1'

Commit

END stored procedure name

=

Execute stored procedure

Declare

Parameter 1 number

Parameter 2 employees.first_name%type

Parameter 3 employees.phone_number%type

Begin

Parameter 1: VRV 102

Stored procedure name (parameter 1, parameter 2, parameter 3)

Dbms_output.put_line ('employee number is' | Parameter 1 | 'employee name is;' | Parameter 2 | |', contact number is:'| | Parameter 3)

End

TO_CHAR (sysdate, 'yyyy-mm-dd')

TO_CHAR (sysdate,'yyyy-mm-dd hh34:mi:ss')

Version V1.0 / 20200706

From "ITPUB blog", link: http://blog.itpub.net/69976145/viewspace-2702702/, if you need to reprint, please indicate the source, otherwise legal liability will be investigated.

Http://www.ytwcbyy.com/ of Yantai Medical University Gastrointestinal Hospital