Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

How to write oracle pl/sql template code

2025-01-29 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

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

Oracle pl/sql template code how to write, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

Select * from scott.emp

= for loop =

Declare

Empno NUMBER (4)

Ename VARCHAR2 (10 BYTE)

Sal NUMBER (7 dint 2)

Begin

For REC in

(select empno, ename, sal from scott.emp)

Loop

Begin

Empno: = REC.empno

Ename: = REC.ename

Sal: = REC.sal

/ *-- start writing a detail-- * /

If NVL (empno,0) 0 then

Begin

Dbms_output.put_line ('record:' | | empno | |''| ename | |''| | sal)

End

End if

/ *-- start writing a detail-- * /

End

End loop

End

/

-- = cursor with parameters =-

DECLARE

Dept_code emp.deptno%TYPE;-declare three column type variables

Emp_code emp.empno%TYPE

Emp_name emp.ename%TYPE

CURSOR emp_cur (deptparam NUMBER) IS

SELECT empno, ename FROM EMP WHERE deptno = deptparam;-- declare that the cursor is displayed

BEGIN

Dept_code: = & department number;-- ask the user to enter the department number you want to view

OPEN emp_cur (dept_code);-- Open the cursor

LOOP

-- endless cycle

FETCH emp_cur

INTO emp_code, emp_name;-- extract the cursor value assigned to the variable declared above

EXIT WHEN emp_cur%NOTFOUND;-exit the loop if there is no data in the cursor

DBMS_OUTPUT.PUT_LINE (emp_code | |'| | emp_name);-- output query

END LOOP

CLOSE emp_cur;-closes the cursor

END

-- = REF cursor =--

ACCEPT tab FROMPT: what information would you like to see? Employee (E) or department information (D):';-- use the ACCEPT command to pop up a dialog box for the user to enter data

DECLARE

TYPE refcur_t IS REF CURSOR;-declare the REF cursor type

Refcur refcur_t;-variables that declare the type of REF cursors

Pid NUMBER

P_name VARCHAR2 (100)

Selection VARCHAR2 (1): = UPPER (SUBSTR ('& tab', 1,1));-- intercepts the string entered by the user and converts it to uppercase

BEGIN

IF selection ='E' THEN

-- if you enter 'eBay, open the refcurr cursor and query the employee table to assign a value to the cursor

OPEN refcur FOR

SELECT EMPNO ID, ENAME NAME FROM EMP

DBMS_OUTPUT.PUT_LINE ('= employee information =')

ELSIF selection ='D' THEN

Open the department table if the input is' Downs

OPEN refcur FOR

SELECT deptno id, dname name FROM DEPT

DBMS_OUTPUT.PUT_LINE ('= department information =')

ELSE

-- otherwise, return to the end

DBMS_OUTPUT.PUT_LINE ('Please enter employee information (E) or department information (D)')

RETURN

END IF

FETCH refcur

INTO pid, pairname;-- extract lines

WHILE refcur%FOUND LOOP

DBMS_OUTPUT.PUT_LINE ('#'| | pid | |':'| | p_name)

FETCH refcur

INTO pid, p_name

END LOOP

CLOSE refcur;-closes the cursor

END

-- = dynamic SQL=--

VARIABLE maxsal NUMBER;-declare variables

EXECUTE: maxsal: = 2500;-- execute references and assign values to variables

DECLARE

R_emp EMP%ROWTYPE;-declare a row type variable

TYPE c_type IS REF CURSOR;-declares the REF cursor type

Cur cantilever type;-- declare variables of REF cursor type

P_salary NUMBER;-declare a scalar variable

BEGIN

P_salary: =: maxsal;-- reference variable

-- use the user statement to pass the referenced value to'1' in the dynamic SQL statement 'SAL >: 1'

OPEN cur FOR 'SELECT * FROM EMP WHERE SAL >: 1 ORDER BY SAL DESC'

USING p_salary

DBMS_OUTPUT.PUT_LINE ('salary is greater than' | p_salary | | 'employees are:')

LOOP

FETCH cur

INTO r_emp

EXIT WHEN cur%NOTFOUND

DBMS_OUTPUT.PUT_LINE ('number:' | | r_emp.empno | | 'name:' | | r_emp.ename | |

'salary:'| | r_emp.sal)

END LOOP

CLOSE cur;-closes the cursor

END

-- example:

CREATE OR REPLACE PROCEDURE x_ne_change

AS

CURSOR cur_new

IS

SELECT int_id, omc_id | |':'| | msc_id | |':'| | bsc_id AS related_id

Omc_id, msc_id, bsc_id, ne_name, cell_id, lac, freq_band

Trx_count, tch, sdcch, gprs_enabled, gprs_trx, dedicated_pdch

Max_pdch, device_type, software_version, dumpfre_type, site_no

Cell_no, rac, ncc, bcc, sms_cb_used, bsc_omc_int_id, omc_int_id

TIMESTAMP

FROM appuser.K_C_CELL

WHERE TIMESTAMP = '2004-04-23 8' AND cell_id

< 2000; CURSOR cur_old (c_no NUMBER) IS SELECT int_id, omc_id || ':' || msc_id || ':' || bsc_id AS related_id, omc_id, msc_id, bsc_id, ne_name, cell_id, lac, freq_band, trx_count, tch, sdcch, gprs_enabled, gprs_trx, dedicated_pdch, max_pdch, device_type, software_version, dumpfre_type, site_no, cell_no, rac, ncc, bcc, sms_cb_used, bsc_omc_int_id, omc_int_id, TIMESTAMP FROM appuser.K_C_CELL WHERE TIMESTAMP = '2004-04-21 6' AND cell_id = c_no; BEGIN FOR v_new IN cur_new LOOP FOR v_old IN cur_old (v_new.cell_id) LOOP BEGIN IF v_new.related_id ; v_old.related_id THEN INSERT INTO TEST_NE_CHANGE (omc_id, omc_int_id, ne_id, old_value, now_value, modify_item, modify_time ) VALUES (v_new.omc_id, v_new.omc_int_id, v_new.cell_id, v_old.related_id, v_new.related_id, 'related_id', v_new.TIMESTAMP ); COMMIT; END IF; IF v_new.tch ; v_old.tch THEN INSERT INTO TEST_NE_CHANGE (omc_id, omc_int_id, ne_id, old_value, now_value, modify_item, modify_time ) VALUES (v_new.omc_id, v_new.omc_int_id, v_new.cell_id, v_old.tch, v_new.tch, 'TCH', v_new.TIMESTAMP ); COMMIT; END IF; END; END LOOP; END LOOP; END; --- 多行数据提取 declare cursor cur_tsalary is select employeeid,positionid from tsalary whererownum < 10; type rec_tsalary isrecord( employeeid tsalary.employeeid%type, positionid tsalary.positionid%type); type all_rec_tsalary_type istableof rec_tsalary; all_rec_tsalary all_rec_tsalary_type; begin --一次处理所有 fetch cur_tsalary bulkcollectinto all_rec_tsalary limit5; for i in1..all_rec_tsalary.countloop dbms_output.put_line(all_rec_tsalary(i).employeeid||all_rec_tsalary(i).positionid); endloop; --使用limit分批提出大量数据 open cur_tsalary; loop fetch cur_tsalary bulkcollectinto all_rec_tsalary limit5; for i in1..all_rec_tsalary.countloop dbms_output.put_line(all_rec_tsalary(i).employeeid||all_rec_tsalary(i).positionid); endloop; exitwhen cur_tsalary%notfound; endloop; close cur_tsalary; end; -- 例六:%NotFound BEGIN DELETE FROM Rs_Employees Where HeTongId = ' WL-090001 ' ; if sql % Notfound then Dbms_Output.put_line( ' 没有找到要删除的记录 ' ); else Dbms_Output.put_line( ' 已删除记录 ' ); end if ; END ; -- 例七:%RowCount,查询记录行数 Declare v_name Rs_Employees.Name % type; BEGIN SELECT Name Into v_Name FROM Rs_Employees Where HeTongId = ' WL-090010 ' ; if sql % RowCount >

0 Then

Dbms_Output.put_line ('rows have been selected from the table, Name is:' | | v_Name)

Else

Dbms_Output.put_line ('No rows selected from the table')

End if

END

Demonstrate the following code again

BEGIN

DELETE FROM Rs_Employees

Where HeTongId

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.

Views: 293

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report