Oracle's stored procedures

"Replace" "Cursor" "Loop" "Fetch"

In Oracle, several linked processes can be grouped together to form a package.

Https://www.2cto.com/database/201610/559389.html

Cursor definition: Cursor [cursor name] is [SQL statement]

Open the Query window in PL SQL, enter the process name in the interpreter, hold down CTRL and click on the process name to bring up the source code.

Stored Procedure Statements

CREATE [OR REPLACE] PROCEDURE stored procedure name [(Parameter [in| Out| In out] data type ...)

{As|is}

[Description section]

BEGIN

Executable section

[EXCEPTION

Error Handling section]

END [procedure name];

To delete a stored procedure syntax:

DROP PROCEDURE stored procedure name;

---will have multiple data in a field, separated by commas, and output separately.

SELECT regexp_substr (' 1101,yokohama,japan,1.5.105 ', ' [^,]+ ', 1, level) as output, ' content ' from dual CONNECT by level <= Le Ngth (' 1101,yokohama,japan,1.5.105 ')-length (REPLACE (' 1101,yokohama,japan,1.5.105 ', ', ')) + 1; Result:     1101     Yokohama     Japan     1.5.105

　　

Multiple cursors can be written in the PL SQL loop to define multiple loops, learning for loops, similar to

cursor [cursor name] is [SQL statement]

for [loop name] in [cursor name] loop---traverse cursor, print out

Dbms_output.put_line ();
END LOOP;

The SQL statement execution in the stored procedure is added "execute immediate":

In a nutshell, you create a table in a stored procedure table_a and then you insert the other data into the table_a with insert into, but because Table_a does not exist when you create the process, the process shows a compile error because table_ A does not necessarily cause the process to fail, so it cannot be compiled successfully, and when the INSERT INTO statement is added to execute immediate, Oracle will no longer ignore the existence of this object, so it can be compiled and executed successfully.

----parameters in a stored procedure

Parameters for---stored procedure
---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
---in out defines an input and output parameter variable, both of which function
--These three parameters can only describe the type, do not need to specify the length such as VARCHAR2 (three), Defaul can not write, but as a programmer is best to write.
Example:---Creating 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 to V_name from TestTable t where T.id1=p_id1; Dbms_output.put_line (' Name: ' | | V_name); End

　　

----Executing stored procedures
Begin
Test_param (' 1 ');
End

Default ' 0 '

Oracle's stored procedures