Oracle

1、Oracle 12C版本

C:\Users\HuaWei>sqlplus /nolog
SQL> conn sys/orcl as sysdba
SQL> select username,account_status from dba_users where username='C##PE_ZHJT';
SQL> alter user C##PE_ZHJT account unlock;

创建表空间、用户、授权

create temporary tablespace haiguans_temp tempfile 'haiguans_temp.dbf' SIZE 50m autoextend on NEXT 50m maxsize 20480m EXTENT management LOCAL;

create tablespace haiguans_data LOGGING datafile 'haiguans_data.dbf' SIZE 50m AUTOEXTEND on NEXT 50m maxsize 20480m EXTENT management local;

create user HaiGuan_RSGL identified by HaiGuan_RSGL default tablespace haiguans_data TEMPORARY tablespace haiguans_temp;

grant connect,resource,dba to HaiGuan_RSGL;

（1）创建用户：create user c##test identified by c##test;

（2）用户授权：grant create session,connect,resource to c##test;

grant dba to c##test;

GRANT EXP_FULL_DATABASE,IMP_FULL_DATABASE TO C##Test;

导出：EXP c##test/c##test@127.0.0.1:1521/ORCL FILE='H:/PE_TEST.DMP'

导入：IMP c##test/c##test@127.0.0.1:1521/ORCL FILE='H:/PE_TEST.DMP' FULL=Y

（3）删除用户：DROP USER c##test CASCADE;

（3）Oracle登录方式

<1>运行-sqlplus->请输入用户名:scott->输入口令:scott（推荐登录）
<2>cmd->C:\Users>sqlplus scott/scott（密码明文显示不安全）
<3>pl/sql developer工具
2、sqlplus显示数据格式设置,(1)SET LINESIZE 300,(2)SET PAGESIZE 30

1、查询编码：select userenv('language') from dual

2、授权 grant connect, resource to test

3、复制表结构与数据

create table NEW_EMP as select * from EMP

4、创建索引

普通索引 create index emp_index on emp(empno,ename,mgr)

唯一索引 create unique index emp_index on emp(empno,ename,mgr)

3、查看用户:SHOW user
查看表结构:DESC emp
4、查看当前登录的用户的表:select table_name from user_tables;
5、去除重复数据： select distinct job from emp;
6、修改字段长度：ALTER TABLE PEAU29 MODIFY PEAU2905 NVARCHAR2(500)

7、删除字段：ALTER TABLE PEAU31 DROP COLUMN PEAU3106;

8、存储过程

（1）简单加法计算存储过程
CREATE OR REPLACE PROCEDURE proc_sum1(
      num1 NUMBER,
      num2 NUMBER
)
AS
BEGIN 
   dbms_output.put_line(num1 + num2);
END;
--调用存储过程（分开执行）
BEGIN
     proc_sum1(5,6);
END;

（2）简单的加法计算存储过程
CREATE OR REPLACE PROCEDURE pro_sum2(
      num1    NUMBER := 2,
      num2    NUMBER :=3
)
AS
    res NUMBER(5,2);
BEGIN
    res := num1 + num2;
    dbms_output.put_line(res);
END;
---调用存储过程
BEGIN
    pro_sum2(5,4);
END;

（3）简单的加法计算存储过程

CREATE OR REPLACE PROCEDURE pro_sum3(
     num1 NUMBER,
     num2 NUMBER,
     res OUT NUMBER
)
AS
BEGIN
     res := num1 + num2;
END;

--存储过程调用
DECLARE
      r NUMBER(3);
BEGIN 
    pro_sum3(5,3,r);
    dbms_output.put_line(r);
END;

（4）

CREATE OR REPLACE procedure testTime  
is  
      n_start   number;  
      n_end     number; 
      samplenum number;
      use_time  number;
begin  
    n_start:=dbms_utility.get_time;  
    select count(*) into samplenum from emp; 
    n_end:=dbms_utility.get_time;  
    use_time:=   n_end   -   n_start;  
    dbms_output.put_line('This statement cost  '|| use_time ||' miliseconds');  
end;  

--调用存储过程
BEGIN
     testTime();        
END;

存储过程学习：

（1）https://www.cnblogs.com/zhao123/p/3911537.html

（2）http://www.cnblogs.com/liliu/archive/2011/06/22/2087546.html

(9)多行数据合并

<select id="queryDate" parameterType="string" resultType="java.util.LinkedHashMap">
	SELECT 
               P29.PEAU2900 AS 主键,P29.PEAU2901 AS 年度,P29.PEAU2902 AS 项目名称,P29.PEAU2903 AS 项目描述,P29.B0101 AS 创建部门,P29.PEAU2904 AS 创建时间,
	       P.PJZT AS 评价主体,
	       P29.PEAU2907 AS 得分,
	      (CASE
		   WHEN P29.PEAU2906 = 0 THEN
		       '否'
		   WHEN P29.PEAU2906 = 1 THEN
		           '是'
		END) AS 是否纳入考核
		FROM   PEAU29 P29
		LEFT   JOIN (SELECT 
		                   X.PEAU3101, X.PEAU2900, WM_CONCAT(Y.A0101) AS PJZT
		             FROM   PEAU31 X
		             LEFT   JOIN A01 Y ON  X.A00 = Y.A00
		             GROUP  BY X.PEAU3101, X.PEAU2900) P
		       ON    P29.PEAU2900 = P.PEAU2900 AND P29.PEAU2901 = P.PEAU3101
		WHERE  1 = 1
		<if test="ND!=null and ND!=''">  
		    AND P29.PEAU2901 = #{ND}
		</if> 
		ORDER BY P29.PEAU2904 ASC
</select>

　（10）存储过程

CREATE OR REPLACE PROCEDURE PEA_PRO_SCORE
(
  ND NUMBER
) AS
BEGIN
     UPDATE PEAU29 A
     SET    A.PEAU2907 =
            (SELECT B.SCORE
             FROM   (SELECT A.PEAU2900,
                            SUM(A.PEAU3102 * B.PEAU3002) / SUM(B.PEAU3002) AS SCORE
                     FROM   PEAU31 A
                     INNER  JOIN PEAU30 B
                     ON     A.PEAU3101 = B.PEAU3001 AND A.A00 = B.A00
                     WHERE  PEAU3004 IN ('1', '2') AND PEAU3101 = ND
                     GROUP  BY A.PEAU2900) B
             WHERE  A.PEAU2900 = B.PEAU2900);
END;

private JdbcOperations jdbcTemplate;
	/**
	 * 总分计算
	 * @return
	 */
	@Action
	public String SumScore() {
		HttpServletRequest request = ActionContext.getActionContext().getHttpServletRequest();
		String ND = request.getParameter("ND");                   // 年度
		String CallSQL = "CALL PEA_PRO_SCORE('" + ND + "') ";
		this.jdbcTemplate.update(CallSQL);
		return "{success:true}";
	}

　　第五章控制结构

5.1、条件控制

比较运算符

（1）AND运算符允许将两个比较组合成一个。只有当两条单独的语句为true时，比较语句才为true。也可将AND 运算符与BETWEEN运算符一起使用，来限制下限值和上限值。

BEGIN
IF 1 = 1 AND 2 = 2 THEN
dbms_output.put_line('True.');
END IF;
END;
/

返回下列输出：True

（2）BETWEEN运算符允许检查某个变量值是否在相同数据类型的两个值之间。BETWEEN也是个包含式运算符。“包含”意味着匹配包含边界值，边界值不能为null。BETWEEN运算符还要求下限值要在上限值之前。

BEGIN
IF 1 BETWEEN 1 AND 3 THEN
DBMS_OUTPUT.PUT_LINE('In the range');
END IF;
END;
/

输出：In the range

（3）IN运算符允许检查某个变量值是否在一组逗号的值中，并且该变量值通常被称为查找值，因为它是将一个单独标量值与列表中的值比较。

=ANY和=SOME运算符与IN运算符的行为相同。该逻辑询问是否在右操作数的集合中找到左操作数。对集合的匹配过程使用或逻辑，实现短路求值，即只要有一个满足条件的存在，就结束操作。

BEGIN

IF 1 IN (1, 2, 3) THEN
DBMS_OUTPUT.PUT_LINE('In the set.');
END IF;
END;
/

输出：In the set.

（4）IS EMPTY运算符允许检查变长数组或表集合变量是否为空。‘空’意味着构造的集合没有任何默认元素。这意味着对集合中的元素，没有给System Global Area（SGA）分配空间。如果没有分配元素空间，IS EMPTY比较返回true，而如果至少有一个元素被分配空间，则返回false。如果没有显示构造初始化集合，则会引发PLS-00306异常。

　DECLARE

TYPE LIST IS TABLE OF INTEGER;
A LIST := LIST();
BEGIN
IF A IS EMPTY THEN
DBMS_OUTPUT.PUT_LINE('"a" is empty.');
END IF;
END;
/

输出："a" is empty.

（5）IS NULL运算符允许检查某个变量值是否是null。NVL()内置函数可用于给任意布尔变量或表达式赋显示的true或false。

DECLARE

VAR BOOLEAN;
BEGIN
IF VAR IS NULL THEN
DBMS_OUTPUT.PUT_LINE('It is null.');
END IF;
END;
/

输出：It is null.

（6）IS A SET运算符允许检查某个变量是否变为长数组或集合变量

DECLARE
TYPE LIST IS TABLE OF INTEGER;
A LIST := LIST();
BEGIN
IF A IS A SET THEN
DBMS_OUTPUT.PUT_LINE('"a" is a set.');
END IF;
END;
/

输出："a" is a set.

（7)LIKE运算符允许检查一个变量值是否是另一个值得一部分。

BEGIN

IF 'Str%' LIKE 'String' THEN
DBMS_OUTPUT.PUT_LINE('Match');
END IF;
END;
/

（8）MEMBER OF是逻辑比较运算符。它允许检查某个元素是否是集合的成员。

DECLARE

TYPE LIST IS TABLE OF VARCHAR2(10);
N VARCHAR2(10) := 'One';
A LIST := LIST('One', 'Two', 'Three');
BEGIN
IF N MEMBER OF A THEN
DBMS_OUTPUT.PUT_LINE('"n" is a set.');
END IF;
END;

（9）NOT是逻辑非运算符。允许检查表达式相反的布尔状态（假定不为null）

BEGIN

IF NOT FALSE THEN
DBMS_OUTPUT.PUT_LINE('True.');
END IF;
END;

（10）OR将两个比较合成一个。

BEGIN

IF 1 = 1 OR 1 = 2 THEN
DBMS_OUTPUT.PUT_LINE('True');
END IF;
END;

（11）SUBMULTISET 运算符检查某个变长数组或者集合是否为镜像的数据类型的子集。

DECLARE

TYPE LIST IS TABLE OF INTEGER;
A LIST := LIST(1, 2, 3);
B LIST := LIST(1, 2, 3, 4);
BEGIN
IF A SUBMULTISET B THEN
DBMS_OUTPUT.PUT_LINE('Valid Subset.');
END IF;
END;
/

DECLARE
  LV_SELECTOR CHAR := 0;
BEGIN
  CASE LV_SELECTOR
    WHEN 0 THEN
      DBMS_OUTPUT.PUT_LINE('Case 0!');
    WHEN 1 THEN
      DBMS_OUTPUT.PUT_LINE('Case 1!');
    ELSE
      DBMS_OUTPUT.PUT_LINE('No match!');
  END CASE;
END;
/
--Case 0!
--CHAR NCHAR VARCHAR2
BEGIN
  CASE
    WHEN 1 = 2 THEN
      DBMS_OUTPUT.PUT_LINE('Case [1=2]');
    WHEN 2 = 2 AND 'Something' = 'Something' THEN
      DBMS_OUTPUT.PUT_LINE('Case [2=2]');
    ELSE
      DBMS_OUTPUT.PUT_LINE('No match');
  END CASE;
END;
/
--Case [2=2]
--查询版本
SELECT * FROM v$version;
--NVL函数
DECLARE
  CONDITION BOOLEAN;
BEGIN
  IF NOT NVL(CONDITION, TRUE) THEN
    DBMS_OUTPUT.PUT_LINE('It''s FALSE!');
  END IF;
END;
/
--It's FALSE!
--NVL(CONDITION, TRUE) 结果为空

DECLARE LV_SELECTOR CHAR := 0;BEGIN CASE LV_SELECTOR WHEN 0 THEN DBMS_OUTPUT.PUT_LINE('Case 0!'); WHEN 1 THEN DBMS_OUTPUT.PUT_LINE('Case 1!'); ELSE DBMS_OUTPUT.PUT_LINE('No match!'); END CASE;END;/--Case 0!--CHAR NCHAR VARCHAR2BEGIN CASE WHEN 1 = 2 THEN DBMS_OUTPUT.PUT_LINE('Case [1=2]'); WHEN 2 = 2 AND 'Something' = 'Something' THEN DBMS_OUTPUT.PUT_LINE('Case [2=2]'); ELSE DBMS_OUTPUT.PUT_LINE('No match'); END CASE;END;/--Case [2=2]--查询版本SELECT * FROM v$version;--NVL函数DECLARE CONDITION BOOLEAN;BEGIN IF NOT NVL(CONDITION, TRUE) THEN DBMS_OUTPUT.PUT_LINE('It''s FALSE!'); END IF;END;/--It's FALSE!--NVL(CONDITION, TRUE) 结果为空

SQL 生成6位随机数并MD5加密输出
DECLARE @i INT=0;
DECLARE @j INT; 
DECLARE @qnum INT = 300;   --生成随机数的数量 
　　SET NOCOUNT ON
　　CREATE TABLE #temp_Table(num INT) 
　　WHILE(@i<@qnum) 
　　BEGIN  
 　　   SELECT @j = cast( floor(rand()*(999999-100000)+100000) as int)    
  　　  IF(NOT EXISTS(SELECT num FROM #temp_Table WHERE num=@j ))   
  　　   BEGIN    
   　　　　INSERT #temp_Table (num) VALUES (@j)     
   　　　SET @i+=1;   
  　　END 
END
SELECT DISTINCT num, dbo.f_MD5(num) FROM #temp_Table 
DROP TABLE #temp_Table


---两种生成6位随机数的SQL

SELECT  CAST(( 1 + RAND() ) * 100000 AS INT)

select left( abs(checksum(newid())) ,6)

　　统计某用户下表的总数：select count(*) from user_tables

DECLARE
CURRENT INTEGER;
TYPE X IS TABLE OF VARCHAR2(6);
Y X := X('One', 'Two', 'Three', 'Four', 'Five');
BEGIN
Y.DELETE(2);
Y.DELETE(4, 5);

CURRENT := Y.FIRST;

WHILE (CURRENT <= Y.LAST) LOOP
DBMS_OUTPUT.PUT_LINE('Index [' || CURRENT || '] Value [' || Y(CURRENT) || ']');

CURRENT := Y.NEXT(CURRENT);
END LOOP;
END;
/

运行结果：

Index [1] Value [One]
Index [3] Value [Three]

posted @ 2019-05-06 00:01 清-华-园阅读(239) 评论(0) 编辑收藏举报

刷新页面返回顶部

清-华-园

Oracle

公告