How to use Oracle stored procedures

1. Definition

Process: (1) Accept 0 or more parameters as input (input) or output (outputs), or both as input and output (INOUT).

(2) Unlike functions, stored procedures do not return a value.

(3) Stored procedures cannot be used directly by SQL statements and can only be invoked through the Execute command or within the PL/SQL program block.

Process Example 1:

CREATE OR REPLACE PROCEDURE insertdept (

P_deptno Dept.deptno%type,

P_dname Dept.dname%type,

P_loc Dept.loc%type) as

Not using keyword declare in procedures and functions, instead of keyword is or as.

BEGIN

INSERT into DEPT1 (Deptno,dname,loc)

VALUES (P_deptno,p_dname,p_loc);

End Insertdept;

Run the process:

EXEC insertdept (' it_dept ', ' beijinig '); Or

BEGIN

Insertdept (, ' hr_dept ', ' Shanghai ');

End;

Process Example 2: User connection registration record

Create a login record table

CREATE table Logtable (userid VARCHAR2 (), logdate date);

Establishment process

CREATE OR REPLACE PROCEDURE logexecution is

BEGIN

INSERT into Logtable (userid, Logdate) VALUES (USER, sysdate);

End;

Execution process

exec logexecution;

The procedure call itself is a pl/sql statement, and a function call is invoked as part of an expression.