A simple example of the stored procedure in Oracle

--- Create a table
Create table TESTTABLE
(
Id1 VARCHAR2 (12 ),
Name VARCHAR2 (32)
)
Select t. id1, t. name from TESTTABLE t
Insert into TESTTABLE (ID1, NAME)
Values ('1', 'hangsan ');


Insert into TESTTABLE (ID1, NAME)
Values ('2', 'lisi ');


Insert into TESTTABLE (ID1, NAME)
Values ('3', 'wangwu ');


Insert into TESTTABLE (ID1, NAME)
Values ('4', 'xiaoliu ');


Insert into TESTTABLE (ID1, NAME)
Values ('5', 'laowu ');
--- Create a stored procedure
Create or replace procedure test_count
As
V_total number (1 );
Begin
Select count (*) into v_total from TESTTABLE;
DBMS_OUTPUT.put_line ('Total number: '| v_total );
End;
-- Preparation
-- Wire scott unlock: alter user scott account unlock;
-- The stored procedure should be stored under the scott user. Grant scott a password.
--- Alter user scott identified by tiger;
--- Run the following command:
EXECUTE test_count;
---- Run SQL in ql/spl
Begin
-- Call the procedure
Test_count;
End;



Create or replace procedure TEST_LIST
AS
--- Cursor
CURSOR test_cursor IS select t. id1, t. name from TESTTABLE t;
Begin
For Test_record IN test_cursor loop --- traverse the cursor and print it out.
DBMS_OUTPUT.put_line (Test_record.id1 | Test_record.name );
End loop;
Test_count; -- execute another stored procedure at the same time (TEST_LIST contains the Stored Procedure test_count)
End;
----- Execute the Stored Procedure TEST_LIST
Begin
TEST_LIST;
END;
--- Stored procedure parameters
--- IN defines an input parameter variable for passing parameters to the stored procedure
-- OUT defines an output parameter variable for retrieving data from the stored procedure.
--- In out defines an input and output parameter variable, which has the functions of both of the preceding two.
-- The three parameters can only describe the type, and do not need to describe the specific length such as varchar2 (12). defaul can be left empty, but it is best to write it as a programmer.

--- Create a stored procedure with Parameters
Create or replace procedure test_param (p_id1 in VARCHAR2 default '0 ')
As v_name varchar2 (32 );
Begin
Select t. name into v_name from TESTTABLE t where t. id1 = p_id1;
DBMS_OUTPUT.put_line ('name: '| v_name );
End;
---- Execute the Stored Procedure
Begin
Test_param ('1 ');
End;

Default '0'




--- Create a stored procedure with Parameters
Create or replace procedure test_paramout (v_name OUT VARCHAR2)
As
Begin
Select name into v_name from TESTTABLE where id1 = '1 ';
DBMS_OUTPUT.put_line ('name: '| v_name );
End;
---- Execute the Stored Procedure
DECLARE
V_name VARCHAR2 (32 );
BEGIN
Test_paramout (v_name );
DBMS_OUTPUT.PUT_LINE ('name: '| v_name );
END;
------- IN OUT
--- Create a stored procedure
Create or replace procedure test_paramINOUT (p_phonenumber in out varchar2)
As
Begin
P_phonenumber: = '1970-'| p_phonenumber;
End;

----
DECLARE
P_phonenumber VARCHAR2 (32 );
BEGIN
P_phonenumber: = '000000 ';
Test_paramINOUT (p_phonenumber );
DBMS_OUTPUT.PUT_LINE ('new phone number: '| p_phonenumber );
END;
----- Run the SQL command to query the source code of the stored procedure or function of the current user,
----- You can query the data dictionary view of USER_SOURCE. The USER_SOURCE structure is as follows:

SQL> DESCRIBE USER_SOURCE;
Name Type Nullable Default Comments
---- -------------- -------- ------- Begin ----------------------------------------------------------------------------------------------------------------------------------------------
NAME VARCHAR2 (30) Y Name of the object
TYPE VARCHAR2 (12) Y Type of the object: "TYPE", "type body", "PROCEDURE", "FUNCTION ",
"PACKAGE", "package body" or "java source"
Line number y Line number of this line of source
TEXT VARCHAR2 (4000) Y Source text
SQL>
--- Query the Definition Statement of the stored procedure
Select text from user_source where name = 'test _ count ';
---- Query the parameters of the stored procedure test_paramINOUT
SQL> DESCRIBE test_paramINOUT;
Parameter Type Mode Default?
-----------------------------------
P_PHONENUMBER VARCHAR2 IN OUT
SQL>
--- Check whether the status of the current stored procedure is correct,
--- The VALID is correct. INVALID indicates that the stored procedure is INVALID or needs to be re-compiled.
Select status from USER_OBJECTS WHERE OBJECT_NAME = 'test _ count ';
----- To check the dependence of stored procedures or functions, You can query the data dictionary USER_DENPENDENCIES. The table structure is as follows:
SQL> DESCRIBE USER_DEPENDENCIES;
Name Type Nullable Default Comments
----------------------------------------------------------------------------------------------------------
NAME VARCHAR2 (30) Name of the object
TYPE VARCHAR2 (17) Y Type of the object
REFERENCED_OWNER VARCHAR2 (30) Y Owner of referenced object (remote owner if remote object)
REFERENCED_NAME VARCHAR2 (64) Y Name of referenced object
REFERENCED_TYPE VARCHAR2 (17) Y Type of referenced object
REFERENCED_LINK_NAME VARCHAR2 (128) Y Name of dblink if this is a remote object
SCHEMAID NUMBER Y
DEPENDENCY_TYPE VARCHAR2 (4) Y
SQL>
--- Query the dependency of the stored procedure TEST_COUNT
SELECT REFERENCED_NAME, REFERENCED_TYPE FROM USER_DEPENDENCIES where name = 'test _ count ';