Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "the basic syntax of oracle stored procedures". Many people will encounter this dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Basic syntax of oracle stored procedures

1. Basic structure

CREATE OR REPLACE PROCEDURE stored procedure name

(

Parameter 1 IN NUMBER

Parameter 2 IN NUMBER

) IS

Variable 1 INTEGER: = 0

Variable 2 DATE

BEGIN

END stored procedure name

2.SELECT INTO STATEMENT

Save the results of a select query into variables. Multiple columns can be stored in multiple variables at the same time. There must be one.

Record, otherwise throw an exception (throw NO_DATA_FOUND if there is no record)

Example:

BEGIN

SELECT col1,col2 into variable 1, variable 2 FROM typestruct where xxx

EXCEPTION

WHEN NO_DATA_FOUND THEN

Xxxx

END

...

3.IF judgment

IF V_TEST=1 THEN

BEGIN

Do something

END

END IF

4.while cycle

WHILE V_TEST=1 LOOP

BEGIN

XXXX

END

END LOOP

5. Variable assignment

V_TEST: = 123

6. Use for in to use cursor

...

IS

CURSOR cur IS SELECT * FROM xxx

BEGIN

FOR cur_result in cur LOOP

BEGIN

V_SUM: = cur_result. Listed as 1+cur_result. Column 2

END

END LOOP

END

7. Cursor with parameters

CURSOR C_USER (C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID

OPEN C_USER (variable value)

LOOP

FETCH C_USER INTO V_NAME

EXIT FETCH C_USER%NOTFOUND

Do something

END LOOP

CLOSE C_USER

8. Use pl/sql developer debug

Establish a Test WINDOW after connecting to the database

Enter the code to call SP in the window, and F9 starts debug,CTRL+N step debugging

For a simple example, view the result through DBMS_OUTPUT

CREATE OR REPLACE PROCEDURE bb (lic_para IN VARCHAR2,out_para OUT VARCHAR2)

AS

Temp VARCHAR2 (100)

BEGIN

SELECT lic_no

INTO temp

FROM t_vehicle_info

WHERE lic_no = lic_para

Out_para:=temp

DBMS_OUTPUT.put_line (out_para)

END bb

The following is the call:

Begin

-- Call the procedure

Bb (lic_para = >: lic_para

Out_para = >: out_para)

End

You can type sqlplus "yonghuming/mima@dbname" on the command line and then debug the stored procedure. But it is best to use the TEST in the stored procedure in the pl/sql Developer tool to call, it can automatically generate the calling statement and have a column for you to input parameter values, including input parameters and output parameters, and return the results to the output parameters. As can be seen in the result bar, pl/sql Developer is stronger than TOAD. TOAD can also execute it by clicking EXECUTE Procedure after right-clicking on the stored procedure, but the result is there. I don't know. In pl/sql Developer, you can debug by pressing F9, and ctrl+N can trace it step by step.

That's all for the basic syntax of oracle stored procedures. Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.