In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
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.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.