Oracle Stored Procedure record in project-common syntax memo

Oracle Stored Procedure record in project-common syntax memo
Oracle Stored Procedure record in project-common syntax memo

 

You need to write an oracle stored procedure in the project. You need to collect a complex query (involving multiple tables) and insert the qualified records into the target table. In the original table, the date field is of the timestamp type, and the target table field is of the varchar2 type;

Some of the content is frequently used, so you can record it for search.

1. storage process format

Oracle stored procedures and functions can be implemented. Generally, stored procedures are used if there is no returned value. functions are more powerful than SQL Server. It is best to add a prefix such as V _ to the oracle variable definition. The variable names and field names in the query conditions cannot be repeated.

Createor replace procedure PROC_NAME (

V_interval in number default-3-PARAM_NAME_LIST

)

V_cnt number (4); -- defines variables and cursors

BEGIN

-- Business logic statement

ENDPROC_NAME

 

2. cursor definition and loop

Cursor CURSOR_NAME is

SELECT * from dual-SELECT statement;

Loop cursors can be implemented in multiple ways. The simplest for method can simplify code by avoiding defining variables and opening or closing cursors. However, if you need to access the number of cursor records, loop or while loop is required.

Forloop Syntax:

FOR curRow IN CURSOR_NAME -- curRow is the row record variable of the cursor

LOOP

-- The value of the curRow. cursor field is used directly (the definition of the variable is omitted)

ENDLOOP;

 

3. addition and subtraction of dates

The DATE type is a 7-byte fixed-width DATE/time data type. It always contains seven attributes, including: century, century which year, month, month which day, hour, minute, and second; TIMESTAMP type is very similar to DATE, in addition, decimal seconds and time zone are supported. The N value below can be negative.

 

? Use the NUMTODSINTERVAL built-in function to add hours, minutes, and seconds.

For example, DATE + NUMTODSINTERVAL (n, 'minute ')

 

? Add a simple number to increase the number of days.

For example, DATE + n

 

? Use the ADD_MONTHS built-in function to add months and years.

For example: ADD_MONTHS (DATE, n)

 

4. Convert the date type to a character

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

-- Year, month, day, hour, minute, second, and 6-digit millisecond;

Select to_char (systimestamp, 'yyyy-mm-dd hh24: mi: ss. ff1') from dual;

-- Year, month, day, hour, minute, second, and millisecond (the number of digits is determined by the number after ff, 1 ~ Between 9, ff3 indicates that three milliseconds are retained)

 

5. if exist substitution syntax

Oracel does not have the if exist Syntax of sqlserver and can only be transformed. We recommend that you use the following syntax:

V_cnt number (4); -- declare the variable;

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 cannot be blank, use the nvl function. For example, nvl (field_name, ''), the two single quotation marks must be described as spaces. If there are no characters, oracle is considered null.

 

7. debug the Stored Procedure

Debugging is generally required for complex services, which makes debugging easier for PLSQLDeveloper. Right-click the stored procedure you want to debug and click debug in the test window.