Simple example of stored procedures 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 ', ' Zhangsan ');


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 the v_total from TestTable;
Dbms_output.put_line (' Total number: ' | | V_total);
End
--Prepare
--line to Scott unlock: Alter user Scott account unlock;
--The stored procedure should be under Scott's user. And give Scott a code.
---alter user Scott identified by Tiger;
---to execute under orders.
EXECUTE Test_count;
----execute in SQL in QL/SPL
Begin
--Call the procedure
Test_count;
End



Create or replace procedure Test_list
As
---is with a cursor
CURSOR test_cursor is select t.id1,t.name from TestTable t;
Begin
For Test_record in Test_cursor loop---traverse the cursor, printing out
Dbms_output.put_line (test_record.id1| | Test_record.name);
End LOOP;
test_count;--simultaneously executes another stored procedure (test_list contains stored procedures test_count)
End
-----Execute Stored Procedures Test_list
Begin
Test_list;
End;
---parameters for stored procedures
---in defines an input parameter variable that is used to pass parameters to the stored procedure
--out defines an output parameter variable that is used to fetch data from a stored procedure
Define an input and OUTPUT parameter variable---in out, with both functions
--These three parameters can only describe the type, do not need to specify length such as VARCHAR2 (a), defaul can not write, but as a programmer it is best to write.

---to 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 Stored Procedures
Begin
Test_param (' 1 ');
End

Default ' 0 '




---to 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 Stored Procedures
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:= ' 0571-' | | P_phonenumber;
End

----
DECLARE
P_phonenumber VARCHAR2 (32);
BEGIN
p_phonenumber:= ' 26731092 ';
Test_paraminout (P_phonenumber);
Dbms_output. Put_Line (' New phone number: ' | | P_phonenumber);
End;
-----The SQL command, query the source code for the current user's stored procedure or function.
-----can be obtained by querying the User_source data dictionary view. The structure of the User_source is as follows:

Sql> DESCRIBE User_source;
Name Type Nullable Default Comments
---- -------------- -------- ------- ------------------------------------------------------------------------------ -------------------------------
Name VARCHAR2 Y name of the object
Type VARCHAR2 Y type of the object: "Type", "Type Body", "PROCEDURE", "FUNCTION",
"PACKAGE", "PACKAGE body" or "JAVA SOURCE"
Line number Y line number of that line of source
TEXT VARCHAR2 (4000) Y Source text
Sql>
---query out the definition statement of a stored procedure
Select text from User_source WHERE name= ' Test_count ';
----query test_paraminout parameters for stored procedures
Sql> DESCRIBE test_paraminout;
Parameter Type Mode Default?
------------- -------- ------ --------
P_phonenumber VARCHAR2 in Out
Sql>
---to see if the current stored procedure is in the correct state.
---valid is correct, invalid indicates that the stored procedure is invalid or needs to be recompiled
SELECT STATUS from user_objects WHERE object_name= ' Test_count ';
-----If you want to check the dependencies of a stored procedure or function, you can determine by querying the data dictionary user_denpendencies that the table is structured as follows:
Sql> DESCRIBE user_dependencies;
Name Type Nullable Default Comments
-------------------- ------------- -------- ------- ----------------------------------------------------------
Name VARCHAR2 name of the object
Type VARCHAR2 Y type of the object
Referenced_owner VARCHAR2 Y owner of referenced object (remote OWNER if remote object)
Referenced_name VARCHAR2 () Y NAME of referenced object
Referenced_type VARCHAR2 Y TYPE of referenced object
Referenced_link_name VARCHAR2 (128) Y NAME of Dblink if is a remote object
SchemaID number Y
Dependency_type VARCHAR2 (4) Y
Sql>
---query stored procedures test_count dependencies
SELECT Referenced_name,referenced_type from user_dependencies WHERE name= ' Test_count ';