一些oracle的小知识

--1.字母全排列
WITH t AS(
SELECT 'A' C1 FROM dual
UNION ALL
SELECT 'B' FROM dual
UNION ALL
SELECT 'C' FROM dual
UNION ALL
SELECT 'D' FROM dual
)
SELECT SUBSTR(SYS_CONNECT_BY_PATH(C1,','),2)
FROM t
WHERE LEVEL=2
CONNECT BY NOCYCLE PRIOR C1!=C1

--level=？ 表示显示？个字段的排列 如=2 则显示 AB,AC,AD,BA,BC,BD 如=4 则显示 ABCD,ACBD,ACDB,以此类推

在1-9中，计算某些列相加的和为10, 数字不能出现重复 如1+9，1+4+5
--思路一
WITH table_name AS(
SELECT 10 c1 FROM dual
UNION ALL
SELECT 4 FROM dual
UNION ALL
SELECT 7 FROM dual
)
SELECT POWER(10,SUM(LOG(10,c1))) FROM table_name
--思路二
WITH t AS
(
SELECT ROWNUM AS NUM FROM dual CONNECT BY ROWNUM<10
)
SELECT * FROM(
SELECT SUBSTR(sys_connect_by_path(NUM,'+'),2) rec FROM t
CONNECT BY NUM>PRIOR num
)
WHERE dbms_aw.eval_number(rec)=10

--存储过程中使用游标
CREATE OR REPLACE PROCEDURE WY_TEST_CURSOR
AS
v_data NUMBER(10,0);
CURSOR cur IS SELECT * FROM sys_user;--定义游标
BEGIN
SELECT COUNT(*) INTO v_data FROM Sys_User;
dbms_output.put_line('总行数'||v_data);
FOR temp IN cur LOOP
dbms_output.put_line('用户名：'|| temp.LOGIN_ID);--LOGIN_ID表中的字段名
END LOOP;
END WY_TEST_CURSOR;

--存储过程返回结果集
CREATE OR REPLACE PROCEDURE WY_TEST_CURSOR(p_cur OUT Sys_Refcursor)
AS
BEGIN
OPEN p_cur FOR SELECT * FROM sys_user;
END WY_TEST_CURSOR;

--存储过程返回单个值
CREATE OR REPLACE PROCEDURE WY_TEST_CURSOR(p_count OUT NUMBER)
AS
BEGIN
SELECT COUNT(*) INTO p_count FROM Sys_User;
END WY_TEST_CURSOR;

----存储过程返回多个值（不同类型）
CREATE OR REPLACE PROCEDURE WY_TEST_CURSOR(p_count OUT NUMBER,p_data OUT VARCHAR2)
AS
BEGIN
p_data:='我看见，我征服';
SELECT COUNT(*) INTO p_count FROM Sys_User;
END WY_TEST_CURSOR;

--存储过程返回结果集+值
CREATE OR REPLACE PROCEDURE WY_TEST_CURSOR(p_cur OUT Sys_Refcursor,p_data OUT VARCHAR2)
AS
BEGIN
p_data:='我看见，我征服';
OPEN p_cur FOR SELECT * FROM sys_user;
END WY_TEST_CURSOR;

--存过程返回多个结果集
CREATE OR REPLACE PROCEDURE WY_TEST_CURSOR(p_cur OUT Sys_Refcursor,p_data OUT Sys_Refcursor)
AS
BEGIN
OPEN p_data FOR SELECT * FROM PROJECT_SCHEDULE;
OPEN p_cur FOR SELECT * FROM sys_user;
END WY_TEST_CURSOR;

--以表更新表 对应SQL SERVER 里面的UPDATE FROM 命令
update sys_user a set domain=(select ref_text from pro_ref b where a.status_flag=b.ref_code and b.ref_type='STATUS_FLAG')
where exists (select 1 from pro_ref b where a.status_flag=b.ref_code and b.ref_type='STATUS_FLAG');
--PS:如果不写WHERE 子句，那么A表中不存在于B表中的数据将被更新为NULL值

--pl/sql 更新SQL FOR UPDATE
例 select * from tams_training_profiles for update