Simple example of stored procedures in Oracle

Source: Internet
Author: User

---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 ';


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.