Oracle stored procedure record in project-common syntax memo

Oracle stored procedure record in project-common syntax memo

An Oracle stored procedure needs to be written in a project that collects the contents of a complex query (involving multiple tables) and then inserts the qualifying records into the target table. Where the date field of one of the original tables is the timestamp type, the field of the target table is the VARCHAR2 type;

Some of these things are very common, so make a note to find them.

1, the format of the stored procedure

Oracle stored procedures and functions can be implemented, generally no return value, the use of stored procedures, the function is more powerful than SQL Server. Oracle variable definitions are best prefixed with v_, where variable names and field names cannot be duplicated in query conditions.

Createor REPLACE PROCEDURE Proc_name (

V_interval in number Default-3–param_name_list

) as

V_CNT number (4); --Defining variables and cursors

BEGIN

--Business Logic statement

Endproc_name

2. Cursor definition and looping

Cursor cursor_name is

SELECT * from Dual–select statement;

There are many ways to loop a cursor, the simplest for way, to avoid defining variables and to open and close cursors, can simplify a lot of code, but if you need to access the number of cursor records, you need loops or while loops.

Forloop Syntax:

For Currow in cursor_name--Currow is the row record variable for the cursor

LOOP

--Directly through the Currow. Cursor field value (omitting the definition of a variable)

Endloop;

3, the date of the addition and subtraction calculation

The date type is a 7-byte fixed-width date/time data type. It always contains 7 attributes, including: The Century, the century, which year, month, month, which day, hour, minute and second; The timestamp type is very similar to date, except that it also supports fractional seconds and time zones. The following n values can be negative.

? Use Numtodsinterval built-in functions to increase hours, minutes, and seconds.

For example: Date+numtodsinterval (n, ' minute ')

? Add a simple number to increase the day.

For example: Date+n

? Use the add_months built-in function to increase the month and year.

For example: Add_months (date,n)

4. Date type turns into characters

Select To_char (Systimestamp, ' yyyy-mm-dd hh24:mi:ssxff ') time1 from dual;

--month and day, and 6 milliseconds;

Select To_char (Systimestamp, ' Yyyy-mm-dd hh24:mi:ss.ff1 ') from dual;

--Day of the month and seconds and milliseconds (digits are determined by the number behind FF, 1~9, FF3 means reserved three-bit milliseconds)

5. If exist substitution syntax

Oracel does not have SQL Server's if exist syntax and can only be deformed, it is recommended to use the following syntax:

V_CNT number (4); --declaring variables;

SelectCount (*) into v_cnt from dual where exists (SELECT statement);

Example:

Declare

V_CNT number;

Begin

Select COUNT (*) into v_cnt from dual

where exists (SELECT * from Table_namewhere col_name=1);

If v_cnt = 0 Then

Dbms_output.put_line (' No record, write your business code here ');

endif

End

6, null value

If the field is not allowed to be empty, use the NVL function, such as: NVL (Field_name, "), the two single quotes that need to be described are directly spaces, and if there are no characters, Oracle is also considered null.

7. Debugging Stored Procedures

Complex business generally need to debug, feel Plsqldeveloper debugging more convenient. Right-click the stored procedure you want to debug and step through the test window.

Oracle stored procedure record in project-common syntax memo