Example of Oracle Stored Procedure writing

Example of the stored PROCEDURE written in the Oracle stored PROCEDURE summary project: www.2cto.com Oracle stored PROCEDURE basic syntax Stored PROCEDURE 1 create or replace procedure stored PROCEDURE name 2 IS/AS 3 BEGIN 4 NULL; 5 EXCEIPTION; 6 END; 1. create a stored procedure, which can be followed by is or as: create or replace procedure PRO_COMPLAIN_TEMPLATE as 2. Define variables. % TYPE and % ROWTYPE are used here. For more information, see http://www.bkjia.com/database/201211/168564.html A cursor is defined. The TEM_INSTANCE TEMPLATE_CUR % rowtype type must be defined after the CURSOR definition: writable INTEGER; CURRENT_MAX_ID limit % TYPE; CURSOR TEMPLATE_CUR is select TEMPLET_NAME, TEMPLET_CONTENT FROM SYS_COMPLAINT_TEMPLET; TEM_INSTANCE TEMPLATE_CUR % ROWTYPE; 3. begin start block: begin 4. This insert statement uses DBLINK and DBMS_OUTPUT.put_line ('print info') for information output: insert into MEMO_TEMPLET (TEMPLET_ID, TEMPLET_CONTENT, TEMPLET_TYPE, TEM PLET_MEMO) (SELECT * FROM MEMO_TEMPLET @ COMPANY); select count (*) INTO convert FROM SYS_COMPLAINT_TEMPLET; IF (NEED_DO_FOR_ZL> 0) THEN DBMS_OUTPUT.put_line ('print info '); select max (TEMPLET_ID) + 1 INTO CURRENT_MAX_ID FROM MEMO_TEMPLET; values ('memo _ templet max id' | CURRENT_MAX_ID); FOR TEM_INSTANCE IN nested loop insert into values (TEMPLET_ID, TEMPLET_CONTENT, TEMPLET_TYPE, TEMPLET_MEMO) VALUES (CURRENT_MAX_ID, TEM_INSTANCE.TEMPLET_CONTENT, '7', NULL); CURRENT_MAX_ID: = CURRENT_MAX_ID + 1; DBMS_OUTPUT.put_line (Limit); end loop; end if; COMMIT; www.2cto.com 4. exception block. Use when others then. Use raise to display the error message: exception when others then DBMS_OUTPUT.put_line ('sqlcode: '| sqlcode); raise; ROLLBACK; IF TEMPLATE_CUR % isopen then close TEMPLATE_CUR; EN D if; DBMS_OUTPUT.put_line ('print info'); end; Note: about the use of cursors, if you use a for loop to traverse the cursor in this example, you do not need to use open/close cursor to open or close the cursor explicitly. Here, you will handle it yourself, if fetch into is used, you need to explicitly switch the cursor. If you want to execute an update operation, use the implicit cursor SQL attributes % found, % notfound, % rowcount, % isopen: Observe the execution of the update statement. You can also use it to determine the cursor status, for example, IF TEMPLATE_CUR % isopen then.